[Commits] Rev 2878: Test case backport for: Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for null-safe operator <=> NULL in file:///home/psergey/dev2/5.3-fix-subq/

Sergey Petrunya psergey at askmonty.org
Fri Jan 14 15:38:43 EET 2011


At file:///home/psergey/dev2/5.3-fix-subq/

------------------------------------------------------------
revno: 2878
revision-id: psergey at askmonty.org-20110114133841-d6eoz33fgwy0wd9h
parent: psergey at askmonty.org-20110114111311-2nf700zwq2sxrmiq
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-fix-subq
timestamp: Fri 2011-01-14 16:38:41 +0300
message:
  Test case backport for: Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for null-safe operator <=> NULL
=== added file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/icp_tests.inc	2011-01-14 13:38:41 +0000
@@ -0,0 +1,29 @@
+--echo #
+--echo # Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
+--echo #             null-safe operator <=> NULL
+--echo #
+
+CREATE TABLE t1(
+  c1 DATE NOT NULL,
+  c2 DATE NULL,
+  c3 DATETIME,
+  c4 TIMESTAMP,
+  PRIMARY KEY(c1),
+  UNIQUE(c2)
+);
+
+--echo
+INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
+INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
+INSERT INTO t1 VALUES('2008-01-01', NULL        , '2008-01-02', '2008-01-03');
+INSERT INTO t1 VALUES('2008-01-17', NULL        , NULL        , '2009-01-29');
+INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
+
+--echo
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
+--echo
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
+
+--echo
+DROP TABLE t1;
+

=== added file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_icp.result	2011-01-14 13:38:41 +0000
@@ -0,0 +1,33 @@
+set @save_storage_engine= @@storage_engine;
+set storage_engine=InnoDB;
+#
+# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
+#             null-safe operator <=> NULL
+#
+CREATE TABLE t1(
+c1 DATE NOT NULL,
+c2 DATE NULL,
+c3 DATETIME,
+c4 TIMESTAMP,
+PRIMARY KEY(c1),
+UNIQUE(c2)
+);
+
+INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
+INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
+INSERT INTO t1 VALUES('2008-01-01', NULL        , '2008-01-02', '2008-01-03');
+INSERT INTO t1 VALUES('2008-01-17', NULL        , NULL        , '2009-01-29');
+INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
+
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
+c1	c2	c3	c4
+2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
+2008-01-17	NULL	NULL	2009-01-29 00:00:00
+
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
+c1	c2	c3	c4
+2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
+2008-01-17	NULL	NULL	2009-01-29 00:00:00
+
+DROP TABLE t1;
+set storage_engine= @save_storage_engine;

=== added file 'mysql-test/r/maria_icp.result'
--- a/mysql-test/r/maria_icp.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/maria_icp.result	2011-01-14 13:38:41 +0000
@@ -0,0 +1,33 @@
+set @save_storage_engine= @@storage_engine;
+set storage_engine=Maria;
+#
+# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
+#             null-safe operator <=> NULL
+#
+CREATE TABLE t1(
+c1 DATE NOT NULL,
+c2 DATE NULL,
+c3 DATETIME,
+c4 TIMESTAMP,
+PRIMARY KEY(c1),
+UNIQUE(c2)
+);
+
+INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
+INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
+INSERT INTO t1 VALUES('2008-01-01', NULL        , '2008-01-02', '2008-01-03');
+INSERT INTO t1 VALUES('2008-01-17', NULL        , NULL        , '2009-01-29');
+INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
+
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
+c1	c2	c3	c4
+2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
+2008-01-17	NULL	NULL	2009-01-29 00:00:00
+
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
+c1	c2	c3	c4
+2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
+2008-01-17	NULL	NULL	2009-01-29 00:00:00
+
+DROP TABLE t1;
+set storage_engine= @save_storage_engine;

=== added file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/myisam_icp.result	2011-01-14 13:38:41 +0000
@@ -0,0 +1,30 @@
+#
+# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
+#             null-safe operator <=> NULL
+#
+CREATE TABLE t1(
+c1 DATE NOT NULL,
+c2 DATE NULL,
+c3 DATETIME,
+c4 TIMESTAMP,
+PRIMARY KEY(c1),
+UNIQUE(c2)
+);
+
+INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
+INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
+INSERT INTO t1 VALUES('2008-01-01', NULL        , '2008-01-02', '2008-01-03');
+INSERT INTO t1 VALUES('2008-01-17', NULL        , NULL        , '2009-01-29');
+INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
+
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
+c1	c2	c3	c4
+2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
+2008-01-17	NULL	NULL	2009-01-29 00:00:00
+
+SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
+c1	c2	c3	c4
+2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
+2008-01-17	NULL	NULL	2009-01-29 00:00:00
+
+DROP TABLE t1;

=== added file 'mysql-test/t/innodb_icp.test'
--- a/mysql-test/t/innodb_icp.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_icp.test	2011-01-14 13:38:41 +0000
@@ -0,0 +1,13 @@
+#
+# ICP/InnoDB tests (Index Condition Pushdown)
+#
+
+--source include/have_innodb.inc
+
+set @save_storage_engine= @@storage_engine;
+set storage_engine=InnoDB;
+
+--source include/icp_tests.inc
+
+set storage_engine= @save_storage_engine;
+

=== added file 'mysql-test/t/maria_icp.test'
--- a/mysql-test/t/maria_icp.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/maria_icp.test	2011-01-14 13:38:41 +0000
@@ -0,0 +1,13 @@
+#
+# ICP/Maria tests (Index Condition Pushdown)
+#
+
+--source include/have_maria.inc
+
+set @save_storage_engine= @@storage_engine;
+set storage_engine=Maria;
+
+--source include/icp_tests.inc
+
+set storage_engine= @save_storage_engine;
+

=== added file 'mysql-test/t/myisam_icp.test'
--- a/mysql-test/t/myisam_icp.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/myisam_icp.test	2011-01-14 13:38:41 +0000
@@ -0,0 +1,6 @@
+#
+# ICP/MyISAM tests (Index Condition Pushdown)
+#
+
+--source include/icp_tests.inc
+



More information about the commits mailing list