MySQL Bugs: #49078: MySQL orders NULLS differently than JavaDB and Postgres (first rather than last)

Bug #49078	MySQL orders NULLS differently than JavaDB and Postgres (first rather than last)
Submitted:	24 Nov 2009 21:31	Modified:	30 Nov 2009 14:54
Reporter:	Patrick Crews	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL orders NULL values differently than JavaDB and Postgres, placing them first rather than last.

While the SQL standard does not specify an explicit sort order for NULLs, this should be documented / perhaps we should have a NULLS first / NULLS last syntax.

How to repeat:
MTR test case:
#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */
#/* Server1: JavaDB Version N/A */

#/*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off' */;
#/*!50400 SET SESSION optimizer_use_mrr = 'disable' */;
#/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
#/*!50400 SET SESSION join_cache_level = 1 */;
#/*!50400 SET SESSION debug = 'd,optimizer_no_icp' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (5,NULL);
INSERT INTO `C` VALUES (11,0);
INSERT INTO `C` VALUES (15,0);
INSERT INTO `C` VALUES (16,1);
INSERT INTO `C` VALUES (1,2);
INSERT INTO `C` VALUES (20,2);
INSERT INTO `C` VALUES (3,3);
INSERT INTO `C` VALUES (7,3);
INSERT INTO `C` VALUES (14,3);
INSERT INTO `C` VALUES (12,5);
INSERT INTO `C` VALUES (18,5);
INSERT INTO `C` VALUES (19,6);
INSERT INTO `C` VALUES (8,8);
INSERT INTO `C` VALUES (9,8);
INSERT INTO `C` VALUES (2,9);
INSERT INTO `C` VALUES (4,9);
INSERT INTO `C` VALUES (6,9);
INSERT INTO `C` VALUES (17,9);
INSERT INTO `C` VALUES (10,53);
INSERT INTO `C` VALUES (13,166);

 
SELECT table2 .`pk` field2  
FROM C table1  LEFT  JOIN C table2  ON table1 .`int_key`  = table2 .`pk`  
ORDER  BY field2  
LIMIT  2   ;

SELECT table2 .`pk` field2
FROM C table1  LEFT  JOIN C table2  ON table1 .`int_key`  = table2 .`pk`
ORDER  BY field2;

SELECT table2 .`pk` field2
FROM C table1  LEFT  JOIN C table2  ON table1 .`int_key`  = table2 .`pk`
ORDER  BY field2 DESC;

#/* Diff: */

#/* --- /tmp//randgen20026-1259078567-server0.dump	2009-11-24 11:02:47.000000000 -0500
# +++ /tmp//randgen20026-1259078567-server1.dump	2009-11-24 11:02:47.000000000 -0500
# @@ -1,2 +1,2 @@
# -NULL
# -NULL
# +1
# +2 */

DROP TABLE C;
#/* End of test case for query 0 */

Correction, this behavior is documented:
http://dev.mysql.com/doc/refman/5.1/en/working-with-null.html

IMHO it is not impressive to see that other RDBMS are used (and this is the 20th time (at least) I see it I believe) as reference for MySQL correctness/consistency.  
.
Also 'other RDBMS' referred seems always to be Postgresql and JavaDB. What about MS SQL server, Oracle, DB2 etc.? Referencing an obscure RDBMS as JavaDB really makes me laugh. 
.
Refer to *standards* and *de facto market leaders* - and not obscure software.

Maybe not the appropriate forum for this, but anyway:
1) The point of three-way comparison of MySQL/JavaDB/Postgres is not the assumption that Postgres or JavaDB is more correct than MySQL, but that if one of the three differs from the others, there is a possibility of a bug that need investigation.

2) JavaDB is not that obscure. It's Sun's brand of Apache Derby (http://db.apache.org/derby) and bundled in the Java JDK.

3) Postgres and JavaDB are both open source, pretty standards compliant and good candidates for this kind of comparison, especially in a distributed community where licenses for commercial databases could be a significant cost.

Our behaviour is documented, and conforms with
the standard, and is like the behaviour of one
of the other major DBMSs.