Difference between revisions of "Default orderBy research 2/17/2011"
| (7 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | While debugging some unit | + | '''Problem: '''While debugging some unit tests (testRead, testUpdate) I have noticed that the error message is the following:''"Doesn't match the serialized representation"''.<br/> |
| + | |||
| + | '''Investigation:''' | ||
I checked the data and in xml file and the one I get when running the composed query in PostgreSQL. They are identical.<br/> | I checked the data and in xml file and the one I get when running the composed query in PostgreSQL. They are identical.<br/> | ||
| − | I | + | I ran the same tests in MySQL: I've got the same data as in PostgreSQL. The only difference is the order of rows, so I figured it has something to do with default ordering in PostgreSQL:<br/> |
| − | + | The null value sorts higher than any other value. In other words, with '''ascending''' sort order, <br>'''null values''' sort at the '''end''', and with descending sort order, null values sort at the beginning.<br/>If not specified, '''<tt>ASC</tt>''' is assumed by '''default'''.<br/> | |
And in the MySQL:<br/> | And in the MySQL:<br/> | ||
| − | When doing an ORDER BY, '''NULL values''' are presented '''first''' if you do ORDER BY ... ASC ('''default''') and last if you do ORDER BY ... DESC. <br/> | + | When doing an ORDER BY, '''NULL values''' are presented '''first''' if you do ORDER BY ... ASC ('''default''') <br>and last if you do ORDER BY ... DESC. <br/> |
| − | This explains why testUpdates gives the specified message, and I guess we have the same issue when we are trying to read '''without order by''' clause | + | This explains why testUpdates gives the specified message, and I guess we have the same issue when we are trying to read '''without order by''' clause. |
| − | + | '''Solution:''' Every time we have ORDER BY we specify '''nulls first''' (this option is supported in PostgreSQL 8.3 and later). | |
Latest revision as of 12:20, 26 March 2011
Problem: While debugging some unit tests (testRead, testUpdate) I have noticed that the error message is the following:"Doesn't match the serialized representation".
Investigation:
I checked the data and in xml file and the one I get when running the composed query in PostgreSQL. They are identical.
I ran the same tests in MySQL: I've got the same data as in PostgreSQL. The only difference is the order of rows, so I figured it has something to do with default ordering in PostgreSQL:
The null value sorts higher than any other value. In other words, with ascending sort order,
null values sort at the end, and with descending sort order, null values sort at the beginning.
If not specified, ASC is assumed by default.
And in the MySQL:
When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC (default)
and last if you do ORDER BY ... DESC.
This explains why testUpdates gives the specified message, and I guess we have the same issue when we are trying to read without order by clause.
Solution: Every time we have ORDER BY we specify nulls first (this option is supported in PostgreSQL 8.3 and later).
