diff options
author | unknown <timour@askmonty.org> | 2011-07-06 17:27:38 +0300 |
---|---|---|
committer | unknown <timour@askmonty.org> | 2011-07-06 17:27:38 +0300 |
commit | db36ce1de0c81b59b8fb5feea1942c52264491cd (patch) | |
tree | a423eafbfec42c8494f9f30e9178518f549671ee /mysql-test/r/subselect4.result | |
parent | 271d9c21e6b2f2bab2dc8ecb6d0f985031e0bb43 (diff) | |
parent | 59784abeadc4014f357e44ae507c62dc93c641b3 (diff) | |
download | mariadb-git-db36ce1de0c81b59b8fb5feea1942c52264491cd.tar.gz |
Merge the fix for bug lp:802979
Diffstat (limited to 'mysql-test/r/subselect4.result')
-rw-r--r-- | mysql-test/r/subselect4.result | 50 |
1 files changed, 48 insertions, 2 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index de4f386f831..8735a75b45d 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -733,7 +733,7 @@ WHERE f3 = ( SELECT t1.f3 FROM t1 WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY t1 ref f3 f3 5 const 0 Using index condition 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 test.t1.f10 1 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary @@ -748,7 +748,7 @@ WHERE f3 = ( SELECT f3 FROM t1 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY t1 ref f3 f3 5 const 0 Using index condition 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 10 test.t1.f10,test.t1.f10 1 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary @@ -1738,4 +1738,50 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary drop table t1, t2, t3; +# +# LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table +# +CREATE TABLE t1 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t1 VALUES (1,0),(5,0); +CREATE TABLE t2 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t2 VALUES (1,0),(5,0); +CREATE TABLE t3 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t3 VALUES (1,0),(5,0); +CREATE TABLE t4 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t4 VALUES (1,0),(5,0); +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) +AND t2.f2 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) +AND t2.f2 = t1.f1; +ERROR 21000: Subquery returns more than 1 row +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) +AND t2.f2 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) +AND t2.f2 = t1.f1; +f1 f2 f1 f2 +drop table t1,t2,t3,t4; set optimizer_switch=@subselect4_tmp; |