diff options
Diffstat (limited to 'mysql-test/t/innodb_mysql.test')
| -rw-r--r-- | mysql-test/t/innodb_mysql.test | 32 |
1 files changed, 32 insertions, 0 deletions
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 6796840f2d2..2be53b58a39 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -67,6 +67,38 @@ where drop table t1, t2; # +# Bug#17212: results not sorted correctly by ORDER BY when using index +# (repeatable only w/innodb because of index props) +# +CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB; +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB; +CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), + UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB; + +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; +INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; + +INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t2 SELECT a + 1, b FROM t2; +DELETE FROM t2 WHERE a = 1 AND b < 2; + +INSERT INTO t3 VALUES (1,1,1),(2,1,2); +INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; +INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; + +# demonstrate a problem when a must-use-sort table flag +# (sort_by_table=1) is being neglected. +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE + t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) + ORDER BY t1.b LIMIT 2; + +# demonstrate the problem described in the bug report +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE + t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) + ORDER BY t1.b LIMIT 5; +DROP TABLE t1, t2, t3; +# # Bug #12882 min/max inconsistent on empty table # |