path: root/mysql-test/t/table_elim.test

#
# Table elimination (MWL#17) tests
#
--disable_warnings
drop table if exists t0, t1, t2, t3, t4, t5, t6;
drop view if exists v1, v2;
--enable_warnings

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);

--echo # This will be  eliminated:
explain select t1.a from t1 left join t2 on t2.a=t1.a;
explain extended select t1.a from t1 left join t2 on t2.a=t1.a;

select t1.a from t1 left join t2 on t2.a=t1.a;

--echo # 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;

--echo # 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;

--echo # 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;

--echo # 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;

--echo # 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;

--echo # 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;

--echo # 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;

--echo # Elimination with aggregate functions
explain select count(*) from t1 left join t2 on t2.a=t1.a;
explain select count(1) from t1 left join t2 on t2.a=t1.a;
explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a;

--echo This must not use elimination:
explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;

drop table t0, t1, t2, t3;

# This will stand for elim_facts
create table t0 ( id integer, primary key (id));

# Attribute1, non-versioned
create table t1 (
  id integer,
  attr1 integer,
  primary key (id),
  key (attr1)
);

# Attribute2, time-versioned
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);

--echo This should use one table:
explain select id from v1 where id=2;
--echo This should use one table:
explain extended select id from v1 where id in (1,2,3,4);
--echo This should use facts and a1 tables:
explain extended select id from v1 where attr1 between 12 and 14;
--echo This should use facts, a2 and its subquery:
explain extended select id from v1 where attr2 between 12 and 14;

# Repeat for v2: 

--echo This should use one table:
explain select id from v2 where id=2;
--echo This should use one table:
explain extended select id from v2 where id in (1,2,3,4);
--echo This should use facts and a1 tables:
explain extended select id from v2 where attr1 between 12 and 14;
--echo This should use facts, a2 and its subquery:
explain extended select id from v2 where attr2 between 12 and 14;

drop view v1, v2;
drop table t0, t1, t2;

#
# Tests for the code that uses t.keypartX=func(t.keypartY) equalities to
# make table elimination inferences
#
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;

--echo 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;

--echo 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;

--echo 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;

--echo 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;

drop table t1, t2;
#
# Check that equality propagation is taken into account
#
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;

explain 
select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col;

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;

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;

drop table t1, t2, t3;

--echo # 
--echo # Check things that look like functional dependencies but really are not
--echo # 

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');

-- echo this must not use table elimination:
explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; 

-- echo this must not use table elimination:
explain select t1.* from t1 left join t2 on t2.a=t1.a collate 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');
-- echo this must not use table elimination:
explain select t1.* from t1 left join t2 on t2.a=1;
-- echo this must not use table elimination:
explain select t1.* from t1 left join t2 on t2.a=t1.a;

drop table t1, t2;
# partial unique keys do not work at the moment, although they are able to
# provide one-match guarantees:
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;

drop table t1, t2;

--echo #
--echo # check UPDATE/DELETE that look like they could be eliminated
--echo #
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;
select * from t2;

delete from t2;
insert into t2 select * from t1;

delete t2 from t1 left join t2 using (a);
select * from t1;
select * from t2;
drop table t1, t2;

--echo #
--echo # Tests with various edge-case ON expressions
--echo #
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;
explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b;
explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a;

explain select t1.a from t1 left join t2 on t2.pk between 10 and 20;
explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5;
explain select t1.a from t1 left join t2 on t2.pk between 10 and 10;

explain select t1.a from t1 left join t2 on t2.pk in (10);
explain select t1.a from t1 left join t2 on t2.pk in (t1.a);

explain select t1.a from t1 left join t2 on TRUE;

explain select t1.a from t1 left join t3 on t3.pk1=t1.a and t3.pk2 IS NULL;

drop table t1,t2,t3;

--echo #
--echo # Multi-equality tests 
--echo #
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;

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;

--echo #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;

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) 
;

explain 
select t1.* 
from t1 left join t2 on t2.pk=3 or t2.pk= 4;

explain 
select t1.* 
from t1 left join t2 on t2.pk=3 or t2.pk= 3;

explain 
select t1.* 
from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3);

drop table t1, t2;

--echo #
--echo # LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB
--echo #

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;

DROP TABLE t1, t2;


--echo #
--echo # LPBUG#524025 Running RQG outer_join test leads to crash
--echo #

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;

drop table t0,t1,t2,t3,t4,t5,t6;

--echo #
--echo # BUG#675118: Elimination of a table results in an invalid execution plan
--echo #
CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ;

CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ;
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 ;
--echo # ^^ The above must not produce a QEP of t3,t5,t2,t4
--echo #    as that violates the "no interleaving of outer join nests" rule.

DROP TABLE t1,t2,t3,t4,t5;

--echo #
--echo # BUG#997747: Assertion `join->best_read < ((double)1.79..5e+308L)' 
--echo #             failed in greedy_search with LEFT JOINs and unique keys  
--echo #
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 ;

DROP TABLE t1,t2,t3,t4,t5;

--echo #
--echo # BUG#884184: Wrong result with RIGHT JOIN + derived_merge
--echo #
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)) ;
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 );
EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );

drop view v1;
DROP TABLE t1,t2,t3;

--echo #
--echo # MDEV-5344: LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section
--echo #
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;
drop table t1,t2,t3;

--echo #
--echo # BUG#919878: Assertion `!eliminated_tables...
--echo #
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
  );

DROP TABLE t1,t2;

--echo #
--echo # MDEV-4840: Wrong result (missing rows) on LEFT JOIN with InnoDB tables
--echo #
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;
SELECT t1.alpha3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code  = t3.code ) ON t1.alpha3 = t3.code;

DROP TABLE t1, t2, t3;

SET optimizer_switch=@save_optimizer_switch;