summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/table_elim.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/table_elim.result')
-rw-r--r--mysql-test/main/table_elim.result1033
1 files changed, 1033 insertions, 0 deletions
diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
new file mode 100644
index 00000000..70a949fe
--- /dev/null
+++ b/mysql-test/main/table_elim.result
@@ -0,0 +1,1033 @@
+drop table if exists t0, t1, t2, t3, t4, t5, t6;
+drop view if exists v1, v2;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3);
+create table t0 as select * from t1;
+create table t2 (a int primary key, b int)
+as select a, a as b from t1 where a in (1,2);
+create table t3 (a int primary key, b int)
+as select a, a as b from t1 where a in (1,3);
+# This will be eliminated:
+explain select t1.a from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain extended select t1.a from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
+select t1.a from t1 left join t2 on t2.a=t1.a;
+a
+0
+1
+2
+3
+# This will not be eliminated as t2.b is in in select list:
+explain select * from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+# This will not be eliminated as t2.b is in in order list:
+explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+# This will not be eliminated as t2.b is in group list:
+explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+# This will not be eliminated as t2.b is in the WHERE
+explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+# Elimination of multiple tables:
+explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+# Elimination of multiple tables (2):
+explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+# Elimination when done within an outer join nest:
+explain extended
+select t0.*
+from
+t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
+t3.a=t1.a) on t0.a=t1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on(`test`.`t1`.`a` = `test`.`t0`.`a`) where 1
+# Elimination with aggregate functions
+explain select count(*) from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain select count(1) from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
+This must not use elimination:
+explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index
+drop table t0, t1, t2, t3;
+create table t0 ( id integer, primary key (id));
+create table t1 (
+id integer,
+attr1 integer,
+primary key (id),
+key (attr1)
+);
+create table t2 (
+id integer,
+attr2 integer,
+fromdate date,
+primary key (id, fromdate),
+key (attr2,fromdate)
+);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t0 select A.id + 10*B.id from t0 A, t0 B where B.id > 0;
+insert into t1 select id, id from t0;
+insert into t2 select id, id, date_add('2009-06-22', interval id day) from t0;
+insert into t2 select id, id+1, date_add('2008-06-22', interval id day) from t0;
+create view v1 as
+select
+f.id, a1.attr1, a2.attr2
+from
+t0 f
+left join t1 a1 on a1.id=f.id
+left join t2 a2 on a2.id=f.id and
+a2.fromdate=(select MAX(fromdate) from
+t2 where id=a2.id);
+create view v2 as
+select
+f.id, a1.attr1, a2.attr2
+from
+t0 f
+left join t1 a1 on a1.id=f.id
+left join t2 a2 on a2.id=f.id and
+a2.fromdate=(select MAX(fromdate) from
+t2 where id=f.id);
+This should use one table:
+explain select id from v1 where id=2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index
+This should use one table:
+explain extended select id from v1 where id in (1,2,3,4);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
+Warnings:
+Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
+Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` where `f`.`id` in (1,2,3,4)
+This should use facts and a1 tables:
+explain extended select id from v1 where attr1 between 12 and 14;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index
+Warnings:
+Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
+Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where `f`.`id` = `a1`.`id` and `a1`.`attr1` between 12 and 14
+This should use facts, a2 and its subquery:
+explain extended select id from v1 where attr2 between 12 and 14;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 4 100.00 Using index condition; Using where
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index
+3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index
+Warnings:
+Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
+Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`a2`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `a2`.`id`))
+This should use one table:
+explain select id from v2 where id=2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index
+This should use one table:
+explain extended select id from v2 where id in (1,2,3,4);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
+Warnings:
+Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
+Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` where `f`.`id` in (1,2,3,4)
+This should use facts and a1 tables:
+explain extended select id from v2 where attr1 between 12 and 14;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index
+Warnings:
+Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
+Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where `f`.`id` = `a1`.`id` and `a1`.`attr1` between 12 and 14
+This should use facts, a2 and its subquery:
+explain extended select id from v2 where attr2 between 12 and 14;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 4 100.00 Using index condition
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index
+3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index
+Warnings:
+Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
+Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`f`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `f`.`id`))
+drop view v1, v2;
+drop table t0, t1, t2;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3);
+create table t2 (pk1 int, pk2 int, pk3 int, col int, primary key(pk1, pk2, pk3));
+insert into t2 select a,a,a,a from t1;
+This must use only t1:
+explain select t1.* from t1 left join t2 on t2.pk1=t1.a and
+t2.pk2=t2.pk1+1 and
+t2.pk3=t2.pk2+1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+This must use only t1:
+explain select t1.* from t1 left join t2 on t2.pk1=t1.a and
+t2.pk3=t2.pk1+1 and
+t2.pk2=t2.pk3+1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+This must use both:
+explain select t1.* from t1 left join t2 on t2.pk1=t1.a and
+t2.pk3=t2.pk1+1 and
+t2.pk2=t2.pk3+t2.col;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+This must use only t1:
+explain select t1.* from t1 left join t2 on t2.pk2=t1.a and
+t2.pk1=t2.pk2+1 and
+t2.pk3=t2.pk1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+drop table t1, t2;
+create table t1 (pk int primary key, col int);
+insert into t1 values (1,1),(2,2);
+create table t2 like t1;
+insert into t2 select * from t1;
+create table t3 like t1;
+insert into t3 select * from t1;
+explain
+select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+explain
+select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 Using where
+explain select t1.*
+from
+t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col)
+on t2.col=t1.col or t2.col=t1.col;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+explain select t1.*, t2.*
+from
+t1 left join
+(t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col)
+on t2.pk=t1.col or t2.pk=t1.col;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 Using where
+drop table t1, t2, t3;
+#
+# Check things that look like functional dependencies but really are not
+#
+create table t1 (a char(10) character set latin1 collate latin1_general_ci primary key);
+insert into t1 values ('foo');
+insert into t1 values ('bar');
+create table t2 (a char(10) character set latin1 collate latin1_general_cs primary key);
+insert into t2 values ('foo');
+insert into t2 values ('FOO');
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index
+1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index
+Warnings:
+Note 1105 Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`a` of collation `latin1_general_cs` = "'foo' collate latin1_general_ci" of collation `latin1_general_ci`
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1105 Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`a` of collation `latin1_general_cs` = "`t1`.`a` collate latin1_general_ci" of collation `latin1_general_ci`
+drop table t1,t2;
+create table t1 (a int primary key);
+insert into t1 values (1),(2);
+create table t2 (a char(10) primary key);
+insert into t2 values ('1'),('1.0');
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index
+Warnings:
+Note 1105 Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`a` of type `char` = "1" of type `int`
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1105 Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`a` of type `char` = "`t1`.`a`" of type `int`
+drop table t1, t2;
+create table t1 (a char(10) primary key);
+insert into t1 values ('foo'),('bar');
+create table t2 (a char(10), unique key(a(2)));
+insert into t2 values
+('foo'),('bar'),('boo'),('car'),('coo'),('par'),('doo'),('tar');
+explain select t1.* from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index
+1 SIMPLE t2 eq_ref a a 3 test.t1.a 1 Using where
+drop table t1, t2;
+#
+# check UPDATE/DELETE that look like they could be eliminated
+#
+create table t1 (a int primary key, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 like t1;
+insert into t2 select * from t1;
+update t1 left join t2 using (a) set t2.a=t2.a+100;
+select * from t1;
+a b
+1 1
+2 2
+3 3
+select * from t2;
+a b
+101 1
+102 2
+103 3
+delete from t2;
+insert into t2 select * from t1;
+delete t2 from t1 left join t2 using (a);
+select * from t1;
+a b
+1 1
+2 2
+3 3
+select * from t2;
+a b
+drop table t1, t2;
+#
+# Tests with various edge-case ON expressions
+#
+create table t1 (a int, b int, c int, d int);
+insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3);
+create table t2 (pk int primary key, b int)
+as select a as pk, a as b from t1 where a in (1,2);
+create table t3 (pk1 int, pk2 int, b int, unique(pk1,pk2));
+insert into t3 select a as pk1, a as pk2, a as b from t1 where a in (1,3);
+explain select t1.a from t1 left join t2 on t2.pk=t1.a and t2.b<t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where
+explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where
+explain select t1.a from t1 left join t2 on t2.pk between 10 and 20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index
+explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
+explain select t1.a from t1 left join t2 on t2.pk between 10 and 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain select t1.a from t1 left join t2 on t2.pk in (10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain select t1.a from t1 left join t2 on t2.pk in (t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain select t1.a from t1 left join t2 on TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using where; Using index
+explain select t1.a from t1 left join t3 on t3.pk1=t1.a and t3.pk2 IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+drop table t1,t2,t3;
+#
+# Multi-equality tests
+#
+create table t1 (a int, b int, c int, d int);
+insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3);
+create table t2 (pk int primary key, b int, c int);
+insert into t2 select a,a,a from t1 where a in (1,2);
+explain
+select t1.*
+from t1 left join t2 on t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b
+where t1.d=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+explain
+select t1.*
+from
+t1
+left join
+t2
+on (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) or
+(t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b)
+where t1.d=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+#This can't be eliminated:
+explain
+select t1.*
+from
+t1
+left join
+t2
+on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or
+(t2.pk=t2.c and t1.a=t1.b and t2.c=t1.b)
+where t1.d=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where
+explain
+select t1.*
+from
+t1
+left join
+t2
+on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or
+(t2.pk=t2.c and t2.c=t1.b)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain
+select t1.*
+from t1 left join t2 on t2.pk=3 or t2.pk= 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
+explain
+select t1.*
+from t1 left join t2 on t2.pk=3 or t2.pk= 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+explain
+select t1.*
+from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where
+drop table t1, t2;
+#
+# LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB
+#
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_int_nokey` int(11) DEFAULT NULL,
+`col_int_key` int(11) DEFAULT NULL,
+`col_date_key` date DEFAULT NULL,
+`col_date_nokey` date DEFAULT NULL,
+`col_time_key` time DEFAULT NULL,
+`col_time_nokey` time DEFAULT NULL,
+`col_datetime_key` datetime DEFAULT NULL,
+`col_datetime_nokey` datetime DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `col_int_key` (`col_int_key`),
+KEY `col_date_key` (`col_date_key`),
+KEY `col_time_key` (`col_time_key`),
+KEY `col_datetime_key` (`col_datetime_key`),
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+);
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t1 VALUES
+(10,7,8,NULL,NULL,'01:27:35','01:27:35','2002-02-26 06:14:37','2002-02-26 06:14:37','v','v'),
+(11,1,9,'2006-06-14','2006-06-14','19:48:31','19:48:31','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r');
+INSERT INTO t2 SELECT * FROM t1;
+SELECT table2.col_int_key AS field1
+FROM (
+t2 AS table1
+RIGHT OUTER JOIN
+(
+( t1 AS table2 STRAIGHT_JOIN
+t1 AS table3 ON (
+(table3.col_varchar_nokey = table2.col_varchar_key ) AND
+(table3.pk = table2.col_int_key))
+)
+) ON
+(
+(table3.col_varchar_key = table2.col_varchar_key) OR
+(table3.col_int_key = table2.pk)
+)
+)
+HAVING field1 < 216;
+field1
+DROP TABLE t1, t2;
+#
+# LPBUG#524025 Running RQG outer_join test leads to crash
+#
+CREATE TABLE t0 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t1 (
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_10_latin1 varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (5,5,1,'t'), (NULL,NULL,2,'y');
+CREATE TABLE t2 (
+col_int int(11) DEFAULT NULL
+);
+INSERT INTO t2 VALUES (8), (4);
+CREATE TABLE t3 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1),(8);
+CREATE TABLE t4 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL,
+col_int int(11) DEFAULT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t4 VALUES (1,'o',1), (2,'w',2);
+CREATE TABLE t5 (
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) DEFAULT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5 VALUES ('k','a','z',1,2),('x','a','w',2,7);
+CREATE TABLE t6 (
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL
+);
+INSERT INTO t6 VALUES (6,1),(8,3);
+SELECT
+table3.col_int AS field1,
+table1.col_int AS field2,
+table1.col_int_key AS field3,
+table1.pk AS field4,
+table1.col_int AS field5,
+table2.col_int AS field6
+FROM
+t1 AS table1
+LEFT OUTER JOIN
+t4 AS table2
+LEFT JOIN t6 AS table3
+RIGHT JOIN t3 AS table4
+LEFT JOIN t5 AS table5 ON table4.pk = table5.pk
+LEFT JOIN t0 AS table6 ON table5.col_int_key = table6.pk
+ON table3.col_int_key = table5.pk
+ON table2.col_varchar_1024_latin1_key = table5.col_varchar_10_utf8_key
+LEFT JOIN t6 AS table7 ON table2.pk = table7.col_int
+ON table1.col_varchar_10_latin1 = table5.col_varchar_1024_latin1_key
+LEFT JOIN t2 AS table8 ON table3.col_int = table8.col_int
+WHERE
+table1.col_int_key < table2.pk
+HAVING
+field4 != 6;
+field1 field2 field3 field4 field5 field6
+drop table t0,t1,t2,t3,t4,t5,t6;
+#
+# BUG#675118: Elimination of a table results in an invalid execution plan
+#
+CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ;
+CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ;
+Warnings:
+Note 1071 Specified key was too long; max key length is 1000 bytes
+INSERT IGNORE INTO t2 VALUES ('xcddwntkbxyorzdv'),
+('cnxxcddwntkbxyor'),('r'),('r'), ('did'),('I'),('when'),
+('hczkfqjeggivdvac'),('e'),('okay'),('up');
+CREATE TABLE t3 (f4 varchar(1024), f1 int(11), f2 int(11)) ;
+INSERT IGNORE INTO t3 VALUES ('f','4','0'),('n','5','-996540416');
+CREATE TABLE t4 (f1 int(11), f3 varchar(10)) ;
+INSERT IGNORE INTO t4 VALUES ('8','n'),('9','nwzcerzsgx'),('10','c');
+CREATE TABLE t5 (f5 int(11), KEY (f5)) ;
+EXPLAIN
+SELECT t3.f2
+FROM t2
+LEFT JOIN t3
+LEFT JOIN t4
+LEFT JOIN t1 ON t4.f1 = t1.f1
+JOIN t5 ON t4.f3 ON t3.f1 = t5.f5 ON t2.f4 = t3.f4
+WHERE t3.f2 ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t5 ref f5 f5 5 test.t3.f1 2 Using where; Using index
+1 SIMPLE t4 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL f4 NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join)
+# ^^ The above must not produce a QEP of t3,t5,t2,t4
+# as that violates the "no interleaving of outer join nests" rule.
+DROP TABLE t1,t2,t3,t4,t5;
+#
+# BUG#997747: Assertion `join->best_read < ((double)1.79..5e+308L)'
+# failed in greedy_search with LEFT JOINs and unique keys
+#
+CREATE TABLE t1 (a1 INT);
+CREATE TABLE t2 (b1 INT);
+CREATE TABLE t3 (c1 INT, UNIQUE KEY(c1));
+CREATE TABLE t4 (d1 INT, UNIQUE KEY(d1));
+CREATE TABLE t5 (e1 INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (2),(3);
+INSERT INTO t3 VALUES (3),(4);
+INSERT INTO t4 VALUES (4),(5);
+INSERT INTO t5 VALUES (5),(6);
+SELECT a1 FROM t1 LEFT JOIN t2 LEFT JOIN t3 LEFT JOIN t4
+ON c1 = d1 ON d1 = b1 ON a1 = b1
+LEFT JOIN t5 ON a1 = e1 ;
+a1
+1
+2
+DROP TABLE t1,t2,t3,t4,t5;
+#
+# BUG#884184: Wrong result with RIGHT JOIN + derived_merge
+#
+CREATE TABLE t1 (a int(11), b varchar(1)) ;
+INSERT IGNORE INTO t1 VALUES (0,'g');
+CREATE TABLE t3 ( a varchar(1)) ;
+INSERT IGNORE INTO t3 VALUES ('g');
+CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (9), (10);
+create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0;
+SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
+a b
+NULL NULL
+EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 system NULL NULL NULL NULL 1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+drop view v1;
+DROP TABLE t1,t2,t3;
+#
+# MDEV-5344: LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section
+#
+create table t1 (
+id int(10) unsigned NOT NULL DEFAULT '0',
+v int(10) unsigned DEFAULT '0',
+PRIMARY KEY (id)
+);
+create table t2 (
+id int(10) unsigned NOT NULL DEFAULT '0',
+PRIMARY KEY (id)
+) ;
+create table t3 (
+id int(10) unsigned NOT NULL DEFAULT '0',
+v int(10) unsigned DEFAULT '0',
+PRIMARY KEY (id)
+);
+insert into t1 values (1, 10), (2, 10);
+insert into t2 values (1), (2);
+insert into t3 values (1, 20);
+insert into t1
+select t2.id, 5 from t2 LEFT OUTER JOIN t3 ON t2.id = t3.id
+on duplicate key update t1.v = t3.v;
+select * from t1;
+id v
+1 20
+2 NULL
+drop table t1,t2,t3;
+#
+# BUG#919878: Assertion `!eliminated_tables...
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2
+( b INT, UNIQUE INDEX(b) );
+INSERT INTO t2 VALUES (1),(2);
+EXPLAIN EXTENDED
+SELECT * FROM t2
+WHERE b IN (
+SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL b 5 NULL 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(/* select#2 */ select sum(1) from dual where 1 having <cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1)))))
+DROP TABLE t1,t2;
+#
+# MDEV-4840: Wrong result (missing rows) on LEFT JOIN with InnoDB tables
+#
+CREATE TABLE t1 (alpha3 VARCHAR(3));
+INSERT INTO t1 VALUES ('USA'),('CAN');
+CREATE TABLE t2 ( t3_code VARCHAR(3), name VARCHAR(64));
+INSERT INTO t2 VALUES ('USA','Austin'),('USA','Boston');
+CREATE TABLE t3 ( code VARCHAR(3), name VARCHAR(64), PRIMARY KEY (code), UNIQUE KEY (name));
+INSERT INTO t3 VALUES ('CAN','Canada'),('USA','United States');
+SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code;
+alpha3 t3_code name code name
+USA USA Austin USA United States
+USA USA Boston USA United States
+CAN NULL NULL NULL NULL
+SELECT t1.alpha3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code;
+alpha3
+USA
+USA
+CAN
+DROP TABLE t1, t2, t3;
+SET optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-7893: table_elimination works wrong with on computed expression and compound unique key
+# (just a testcase)
+CREATE TABLE t1 (
+PostID int(10) unsigned NOT NULL
+) DEFAULT CHARSET=utf8;
+INSERT INTO t1 (PostID) VALUES (1), (2);
+CREATE TABLE t2 (
+VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
+EntityID int(10) unsigned NOT NULL,
+UserID int(10) unsigned NOT NULL,
+UNIQUE KEY EntityID (EntityID,UserID)
+) DEFAULT CHARSET=utf8;
+INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30);
+SELECT t1.*, T.Voted as Voted
+FROM
+t1 LEFT JOIN (
+SELECT 1 AS Voted, EntityID
+FROM t2
+WHERE t2.UserID = '20' ) AS T
+ON T.EntityID = t1.PostID
+WHERE t1.PostID='1'
+LIMIT 1;
+PostID Voted
+1 NULL
+DROP TABLE t1,t2;
+#
+# MDEV-26278: Table elimination does not work across derived tables
+#
+create table t1 (a int, b int);
+insert into t1 select seq, seq+10 from seq_1_to_10;
+create table t11 (
+a int not null,
+b int,
+key(a)
+);
+insert into t11 select A.seq, A.seq+B.seq
+from
+seq_1_to_10 A,
+seq_1_to_100 B;
+create table t12 (
+pk int primary key,
+col1 int
+);
+insert into t12 select seq, seq from seq_1_to_1000;
+create view v2b as
+select t11.a as a, count(*) as b
+from t11 left join t12 on t12.pk=t11.b
+group by t11.a;
+# The whole v2b is eliminated
+explain select t1.* from t1 left join v2b on v2b.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+# Check format JSON as well
+explain format=JSON select t1.* from t1 left join v2b on t1.a=v2b.a;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "const_condition": "1",
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100
+ }
+ }
+ ]
+ }
+}
+# Elimination of a whole subquery
+explain select t1.* from t1 left join
+(select t11.a as a, count(*) as b
+from t11 left join t12 on t12.pk=t11.b
+group by t11.a) v2b on v2b.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+# In this case v2b cannot be eliminated (since v2b.b is not unique)!
+explain select t1.* from t1 left join v2b on t1.a=v2b.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY <derived2> ref key0 key0 8 test.t1.a 10 Using where
+2 DERIVED t11 ALL NULL NULL NULL NULL 1000 Using temporary; Using filesort
+# Check format JSON as well
+explain format=JSON select t1.* from t1 left join v2b on t1.a=v2b.b;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "const_condition": "1",
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "8",
+ "used_key_parts": ["b"],
+ "ref": ["test.t1.a"],
+ "rows": 10,
+ "filtered": 100,
+ "attached_condition": "trigcond(t1.a = v2b.b and trigcond(t1.a is not null))",
+ "materialized": {
+ "query_block": {
+ "select_id": 2,
+ "const_condition": "1",
+ "filesort": {
+ "sort_key": "t11.a",
+ "temporary_table": {
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t11",
+ "access_type": "ALL",
+ "rows": 1000,
+ "filtered": 100
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+ }
+ ]
+ }
+}
+create view v2c as
+select t11.a as a, max(t12.col1) as b
+from t11 left join t12 on t12.pk=t11.b
+group by t11.a;
+# The whole v2c is eliminated
+explain select t1.* from t1 left join v2c on v2c.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+# Check format JSON as well
+explain format=JSON select t1.* from t1 left join v2c on v2c.a=t1.a;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "const_condition": "1",
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100
+ }
+ }
+ ]
+ }
+}
+# In this case v2c cannot be eliminated (since v2c.b is not unique)!
+explain select t1.* from t1 left join v2c on t1.a=v2c.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 Using where
+2 DERIVED t11 ALL NULL NULL NULL NULL 1000 Using temporary; Using filesort
+2 DERIVED t12 eq_ref PRIMARY PRIMARY 4 test.t11.b 1 Using where
+# Check format JSON as well
+explain format=JSON select t1.* from t1 left join v2c on t1.a=v2c.b;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "const_condition": "1",
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "5",
+ "used_key_parts": ["b"],
+ "ref": ["test.t1.a"],
+ "rows": 10,
+ "filtered": 100,
+ "attached_condition": "trigcond(trigcond(t1.a is not null))",
+ "materialized": {
+ "query_block": {
+ "select_id": 2,
+ "const_condition": "1",
+ "filesort": {
+ "sort_key": "t11.a",
+ "temporary_table": {
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t11",
+ "access_type": "ALL",
+ "rows": 1000,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t12",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["pk"],
+ "ref": ["test.t11.b"],
+ "rows": 1,
+ "filtered": 100,
+ "attached_condition": "trigcond(trigcond(t11.b is not null))"
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+ }
+ ]
+ }
+}
+# Create a view with multiple fields in the GROUP BY clause:
+create view v2d as
+select t11.a as a, t11.b as b, max(t12.col1) as max_col1
+from t11 left join t12 on t12.pk=t11.b
+group by t11.a, t11.b;
+# This one must not be eliminated since only one of the GROUP BY fields is bound:
+explain select t1.* from t1 left join v2d on v2d.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 Using where
+2 DERIVED t11 ALL a NULL NULL NULL 1000 Using temporary; Using filesort
+2 DERIVED t12 eq_ref PRIMARY PRIMARY 4 test.t11.b 1 Using where
+# This must be eliminated since both fields are bound:
+explain select t1.* from t1 left join v2d on v2d.a=t1.a and v2d.b=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+create table t13 (dt date, b int);
+# Function year() in the GROUP BY list prevents treating this field
+# as a unique key
+create view v2e as
+select year(t13.dt) as yyy, max(t12.col1) as max_col1
+from t13 join t12 on t12.pk=t13.b
+group by yyy;
+# No elimination here since function year() is used
+explain select t1.* from t1 left join v2e on v2e.yyy=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 Using where
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+create table t2 (a int, b int, c int);
+insert into t2 select A.seq, B.seq, 123 from seq_1_to_3 A, seq_1_to_3 B;
+# No elimination here since not all fields of the derived table's
+# GROUP BY are on the SELECT list so D.a is not unique
+explain select t1.* from t1 left join
+(select a, count(*) as cnt from t2 group by a, b) D on D.a=t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 Using where
+2 DERIVED t2 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+# Still no elimination 'cause field D.b is just an alias for t2.a
+explain select t1.* from t1 left join
+(select a, a as b, count(*) as cnt from t2 group by a, b) D on D.a=t1.a and D.b=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY <derived2> ref key0 key0 10 test.t1.a,test.t1.b 2 Using where
+2 DERIVED t2 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+Warnings:
+Warning 1052 Column 'b' in group statement is ambiguous
+# Now both a and b fields are on the SELECT list and they are bound to t1
+# so derived D must be eliminated
+explain select t1.* from t1 left join
+(select a as a1, b as b1, count(*) as cnt from t2 group by a, b) D
+on D.a1=t1.a and D.b1=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+# Different order of fields in GROUP BY and SELECT lists
+# must not hamper the elimination
+explain select t1.* from t1 left join
+(select count(*) as cnt, b, a from t2 group by a, b) D on D.a=t1.a and D.b=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+drop view v2b, v2c, v2d, v2e;
+drop table t1, t11, t12, t13, t2;
+#
+# End of MDEV-26278: Table elimination does not work across derived tables
+#
+#
+# MDEV-28881: Server crashes in Dep_analysis_context::create_table_value/
+# check_func_dependency
+#
+CREATE TABLE t1 (a1 int, a2 int);
+INSERT INTO t1 VALUES (0,276),(5,277),(NULL,278);
+CREATE TABLE t2 ( a1 int, a2 int, KEY a2 (a2));
+INSERT INTO t2 VALUES (11,NULL),(185,0);
+SELECT t1.* FROM t1 LEFT JOIN
+( SELECT * FROM (SELECT t2.a1 AS a1, min(t2.a2) AS a2 FROM t2
+WHERE t2.a2 <> NULL
+GROUP BY t2.a1) dt
+) dt2 ON dt2.a2 = t1.a2;
+a1 a2
+0 276
+5 277
+NULL 278
+DROP TABLE t1, t2;
+#
+# MDEV-30007: SIGSEGV in st_select_lex_unit::is_derived_eliminated,
+# runtime error: member access within null pointer of type
+# 'struct TABLE' in st_select_lex_unit::is_derived_eliminated()
+#
+CREATE VIEW v AS SELECT 1 AS a;
+SELECT ROUND ((SELECT 1 FROM v)) FROM v GROUP BY ROUND ((SELECT 1 FROM v));
+ROUND ((SELECT 1 FROM v))
+1
+EXPLAIN
+SELECT ROUND ((SELECT 1 FROM v)) FROM v GROUP BY ROUND ((SELECT 1 FROM v));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived4> system NULL NULL NULL NULL 1
+4 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
+2 SUBQUERY <derived5> system NULL NULL NULL NULL 1
+5 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
+DROP VIEW v;
+#
+# End of 10.10 tests
+#
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-23 12:58:28 +0000