diff options
Diffstat (limited to 'mysql-test/main/subselect_no_exists_to_in.result')
| -rw-r--r-- | mysql-test/main/subselect_no_exists_to_in.result | 12 |
1 files changed, 8 insertions, 4 deletions
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 6463aa4e4e2..8b1438db530 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -3106,13 +3106,17 @@ create table t1(a int, primary key (a)); insert into t1 values (10); create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); +insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 SUBQUERY t2 range b b 40 NULL 2 Using where +2 SUBQUERY t2 range b b 40 NULL 3 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3124,7 +3128,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 3 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3711,7 +3715,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index -2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 6 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -4979,7 +4983,7 @@ UNIQUE KEY b (b,c), KEY c (c), KEY b_2 (b) ); -INSERT INTO t3 VALUES (1,1,1), (2,32,1); +INSERT INTO t3 VALUES (1,1,1), (2,32,1), (3,33,1), (4,34,2); explain SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; id select_type table type possible_keys key key_len ref rows Extra |