diff options
| -rw-r--r-- | mysql-test/r/subselect_innodb.result | 60 | ||||
| -rw-r--r-- | mysql-test/t/subselect_innodb.test | 58 | ||||
| -rw-r--r-- | sql/sql_select.cc | 7 | ||||
| -rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 1 |
4 files changed, 125 insertions, 1 deletions
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index ab623ad6a28..40e167e87db 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -254,3 +254,63 @@ SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); a b 2011-05-13 0 DROP TABLE t1; +# +# Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE +# +CREATE TABLE t1 (a INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (0); +CREATE TABLE t2 (d BINARY(2), PRIMARY KEY (d(1)), UNIQUE KEY (d)) ENGINE=INNODB; +SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); +1 +1 +EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY,d d 2 func 1 Using where +3 DEPENDENT SUBQUERY t2 index NULL d 2 NULL 1 Using where; Using index +DROP TABLE t2; +CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; +INSERT INTO t2 VALUES (1, 1); +SELECT 1 FROM t1 +WHERE a != (SELECT 1 FROM t2 WHERE a <=> b OR a > '' AND 6 = 7 ORDER BY b, c); +1 +DROP TABLE t1, t2; +# +# Bug #13639204 64111: CRASH ON SELECT SUBQUERY WITH NON UNIQUE +# INDEX +# +CREATE TABLE t1 ( +id int +) ENGINE=InnoDB; +INSERT INTO t1 (id) VALUES (11); +CREATE TABLE t2 ( +t1_id int, +position int, +KEY t1_id (t1_id), +KEY t1_id_position (t1_id,position) +) ENGINE=InnoDB; +EXPLAIN SELECT +(SELECT position FROM t2 +WHERE t2.t1_id = t1.id +ORDER BY t2.t1_id , t2.position +LIMIT 10,1 +) AS maxkey +FROM t1 +LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ref t1_id,t1_id_position t1_id_position 5 test.t1.id 1 Using where +SELECT +(SELECT position FROM t2 +WHERE t2.t1_id = t1.id +ORDER BY t2.t1_id , t2.position +LIMIT 10,1 +) AS maxkey +FROM t1 +LIMIT 1; +maxkey +NULL +DROP TABLE t1,t2; +End of 5.1 tests diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 73491417e0c..ffefc3a2800 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -247,3 +247,61 @@ CREATE TABLE t1(a date, b int, unique(b), unique(a), key(b)) engine=innodb; INSERT INTO t1 VALUES ('2011-05-13', 0); SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); DROP TABLE t1; + + +--echo # +--echo # Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE +--echo # + +CREATE TABLE t1 (a INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (0); +CREATE TABLE t2 (d BINARY(2), PRIMARY KEY (d(1)), UNIQUE KEY (d)) ENGINE=INNODB; + +SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); + +EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); + +DROP TABLE t2; + +CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; +INSERT INTO t2 VALUES (1, 1); + +SELECT 1 FROM t1 +WHERE a != (SELECT 1 FROM t2 WHERE a <=> b OR a > '' AND 6 = 7 ORDER BY b, c); + +DROP TABLE t1, t2; + +--echo # +--echo # Bug #13639204 64111: CRASH ON SELECT SUBQUERY WITH NON UNIQUE +--echo # INDEX +--echo # +CREATE TABLE t1 ( +id int +) ENGINE=InnoDB; +INSERT INTO t1 (id) VALUES (11); + +CREATE TABLE t2 ( +t1_id int, +position int, +KEY t1_id (t1_id), +KEY t1_id_position (t1_id,position) +) ENGINE=InnoDB; + +let $query=SELECT +(SELECT position FROM t2 +WHERE t2.t1_id = t1.id +ORDER BY t2.t1_id , t2.position +LIMIT 10,1 +) AS maxkey +FROM t1 +LIMIT 1; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t1,t2; + +--echo End of 5.1 tests + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 50ae8916707..5d5a576c3b0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5890,6 +5890,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, } keyuse++; } while (keyuse->table == table && keyuse->key == key); + DBUG_ASSERT(length > 0 && keyparts != 0); } /* not ftkey */ /* set up fieldref */ @@ -13698,6 +13699,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, DBUG_ENTER("test_if_skip_sort_order"); LINT_INIT(ref_key_parts); + /* Check that we are always called with first non-const table */ + DBUG_ASSERT(tab == tab->join->join_tab + tab->join->const_tables); + /* Keys disabled by ALTER TABLE ... DISABLE KEYS should have already been taken into account. @@ -13779,7 +13783,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, while (keyuse->key != new_ref_key && keyuse->table == tab->table) keyuse++; if (create_ref_for_key(tab->join, tab, keyuse, - tab->join->const_table_map)) + (tab->join->const_table_map | + OUTER_REF_TABLE_BIT))) DBUG_RETURN(0); pick_table_access_method(tab); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index f162c472362..9625fdab8e9 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -5837,6 +5837,7 @@ ha_innobase::index_read( DBUG_ENTER("index_read"); ut_a(prebuilt->trx == thd_to_trx(user_thd)); + ut_ad(key_len != 0 || find_flag != HA_READ_KEY_EXACT); ha_statistic_increment(&SSV::ha_read_key_count); |