"Fossies" - the Fresh Open Source Software Archive  

Source code changes of the file "mysql-test/r/subselect_exists2in.result" between
mariadb-10.2.21.tar.gz and mariadb-10.2.22.tar.gz

About: MariaDB is a relational database management system (a drop-in replacement for MySQL). Stable (GA) release (10.2).

subselect_exists2in.result  (mariadb-10.2.21):subselect_exists2in.result  (mariadb-10.2.22)
skipping to change at line 333 skipping to change at line 333
INSERT INTO t3 VALUES (4),(5); INSERT INTO t3 VALUES (4),(5);
SET optimizer_switch='exists_to_in=on,subquery_cache=off,materialization=on,in_t o_exists=off,semijoin=off'; SET optimizer_switch='exists_to_in=on,subquery_cache=off,materialization=on,in_t o_exists=off,semijoin=off';
explain extended explain extended
SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1; SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1;
id select_type table type possible_keys key key_len ref r ows filtered Extra id select_type table type possible_keys key key_len ref r ows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 1 1 00.00 2 SUBQUERY t2 system NULL NULL NULL NULL 1 1 00.00
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 1 00.00 Using where 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 1 00.00 Using where
Warnings: Warnings:
Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELEC T #2 Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELEC T #2
Note 1003 select (select 1 from dual where !(1 is not null and <in_optimize r>(1,1 in ( <materialize> (select `test`.`t3`.`c` from `test`.`t3` where `test`. `t3`.`c` is not null ), <primary_index_lookup>(1 in <temporary table> on distinc t_key where 1 = `<subquery3>`.`c`))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` Note 1003 select (select 1 from dual where !(1 is not null and <in_optimize r>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where 1 = `<subquery3>`.`c`))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FRO M t3 WHERE c = b ) )` from `test`.`t1`
SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1; SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1;
( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )
1 1
1 1
SET optimizer_switch='exists_to_in=on,subquery_cache=off'; SET optimizer_switch='exists_to_in=on,subquery_cache=off';
explain extended explain extended
SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1; SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1;
id select_type table type possible_keys key key_len ref r ows filtered Extra id select_type table type possible_keys key key_len ref r ows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 1 1 00.00 2 SUBQUERY t2 system NULL NULL NULL NULL 1 1 00.00
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 1 00.00 Using where 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 1 00.00 Using where
Warnings: Warnings:
Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELEC T #2 Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELEC T #2
Note 1003 select (select 1 from dual where !(1 is not null and <in_optimize r>(1,1 in ( <materialize> (select `test`.`t3`.`c` from `test`.`t3` where `test`. `t3`.`c` is not null ), <primary_index_lookup>(1 in <temporary table> on distinc t_key where 1 = `<subquery3>`.`c`))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` Note 1003 select (select 1 from dual where !(1 is not null and <in_optimize r>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where 1 = `<subquery3>`.`c`))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FRO M t3 WHERE c = b ) )` from `test`.`t1`
SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1; SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1;
( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )
1 1
1 1
SET optimizer_switch='exists_to_in=off,subquery_cache=off'; SET optimizer_switch='exists_to_in=off,subquery_cache=off';
explain extended explain extended
SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1; SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FR OM t1;
id select_type table type possible_keys key key_len ref r ows filtered Extra id select_type table type possible_keys key key_len ref r ows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 SUBQUERY t2 system NULL NULL NULL NULL 1 1 00.00 2 SUBQUERY t2 system NULL NULL NULL NULL 1 1 00.00
 End of changes. 2 change blocks. 
2 lines changed or deleted 2 lines changed or added

Home  |  About  |  Features  |  All  |  Newest  |  Dox  |  Diffs  |  RSS Feeds  |  Screenshots  |  Comments  |  Imprint  |  Privacy  |  HTTP(S)