 | |
bk commit into 5.0 tree (aivanov:1.1928)
mailing.database.mysql-internals mailing.database.mysql-internals
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
bk commit into 5.0 tree (aivanov:1.1928) -
09-01-2005
, 09:14 AM
|
bit_expr IN_SYM in_subselect @@ -4309,7 +4311,11 @@ bit_expr BETWEEN_SYM bit_expr AND_SYM predicate { $$= new Item_func_between($1,$3,$5); } bit_expr not BETWEEN_SYM bit_expr AND_SYM predicate - { $$= negate_expression(YYTHD, new Item_func_between($1,$4,$6)); } |
+ Item_func_between *item= new Item_func_between($1,$4,$6);
+ item->negate();
+ $$= item;
+ }
Quote:
|
bit_expr SOUNDS_SYM LIKE bit_expr { $$= new Item_func_eq(new Item_func_soundex($1), |
@@ -4375,12 +4381,6 @@
Quote:
|
ANY_SYM { $$ = 0; } ; |
- {
- $5->push_front($1);
- Item_func_in *item= new Item_func_in(*$5);
- item->negate();
- $$= item;
- }
interval_expr:
INTERVAL_SYM expr { $$=$2; }
;
--- 1.43/mysql-test/r/join_outer.result 2005-09-01 14:46:48 +04:00
+++ 1.44/mysql-test/r/join_outer.result 2005-09-01 18:17:20 +04:00
@@ -200,7 +200,7 @@
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
-INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
+INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
CREATE TABLE t2 (
idAssignatura int(11) DEFAULT '0' NOT NULL,
Grup int(11) DEFAULT '0' NOT NULL,
@@ -842,7 +842,66 @@
id text_id text_data
1 0 0-SV
2 10 10-SV
-DROP TABLE invoice, text_table;
+DROP TABLE t1, t2;
+CREATE TABLE t0 (a0 int PRIMARY KEY);
+CREATE TABLE t1 (a1 int PRIMARY KEY);
+CREATE TABLE t2 (a2 int);
+CREATE TABLE t3 (a3 int);
+INSERT INTO t0 VALUES (1);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1), (2);
+INSERT INTO t3 VALUES (1), (2);
+SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
+a1 a2
+1 NULL
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
+a1 a2 a3
+1 NULL NULL
+EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2
+SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
+a0 a1 a2 a3
+1 1 NULL NULL
+EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 system PRIMARY NULL NULL NULL 1
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2
+INSERT INTO t0 VALUES (0);
+INSERT INTO t1 VALUES (0);
+SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+a0 a1 a2 a3
+1 1 NULL NULL
+EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 const PRIMARY PRIMARY 4 const 1 Using index
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2
+drop table t1,t2;
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 (a int, b int);
+insert into t2 values (1,1), (2,2);
+select * from t2 right join t1 on t2.a=t1.a;
+a b a b
+1 1 1 1
+2 2 2 2
+NULL NULL 3 3
+select straight_join * from t2 right join t1 on t2.a=t1.a;
+a b a b
+1 1 1 1
+2 2 2 2
+NULL NULL 3 3
+DROP TABLE t0,t1,t2,t3;
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
@@ -888,5 +947,190 @@
bbbbb
Warnings:
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
+select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
+group_concat(t1.b,t2.c)
+aaaaa
+bbbbb
+Warnings:
+Warning 1260 2 line(s) were cut by GROUP_CONCAT()
+select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
+group_concat(t1.b,t2.c)
+aaaaa
+bbbbb
+Warnings:
+Warning 1260 2 line(s) were cut by GROUP_CONCAT()
drop table t1, t2;
set group_concat_max_len=default;
+create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key (gid,x,y));
+insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
+create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key (gid,id,x,y), key id (id));
+insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
+create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key (id,set_id));
+insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');
+explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
+left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
+and t1.gid =1 and t2.gid =1 and t3.set_id =1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 10 const,const,const 1
+1 SIMPLE t2 const PRIMARY,id PRIMARY 14 const,const,const,const 1 Using index
+1 SIMPLE t3 const PRIMARY PRIMARY 3 const,const 1
+drop tables t1,t2,t3;
+CREATE TABLE t1 (EMPNUM INT, GRP INT);
+INSERT INTO t1 VALUES (0, 10);
+INSERT INTO t1 VALUES (2, 30);
+CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
+INSERT INTO t2 VALUES (0, 'KERI');
+INSERT INTO t2 VALUES (9, 'BARRY');
+CREATE VIEW v1 AS
+SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
+FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
+SELECT * FROM v1;
+EMPNUM NAME GRP
+0 KERI 10
+9 BARRY NULL
+SELECT * FROM v1 WHERE EMPNUM < 10;
+EMPNUM NAME GRP
+0 KERI 10
+9 BARRY NULL
+DROP TABLE t1,t2;
+CREATE TABLE t1 (c11 int);
+CREATE TABLE t2 (c21 int);
+INSERT INTO t1 VALUES (30), (40), (50);
+INSERT INTO t2 VALUES (300), (400), (500);
+SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
+c11 c21
+40 NULL
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
+INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
+a b a b
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
+a b a b
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
+a b a b
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
+a b a b
+2 1 NULL NULL
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+8 7 NULL NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
+a b a b
+3 2 3 0
+4 3 4 1
+6 5 6 4
+7 8 7 5
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
+DROP TABLE t1,t2;
--- 1.33/mysql-test/t/join_outer.test 2005-09-01 14:46:48 +04:00
+++ 1.34/mysql-test/t/join_outer.test 2005-09-01 18:16:36 +04:00
@@ -135,7 +135,7 @@
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
-INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
+INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
CREATE TABLE t2 (
idAssignatura int(11) DEFAULT '0' NOT NULL,
@@ -586,7 +586,177 @@
PRIMARY KEY (text_id,language_id)
);
-INSERT INTO text_table VALUES("0", "EN", "0-EN");
-INSERT INTO text_table VALUES("0", "SV", "0-SV");
-INSERT INTO text_table VALUES("10", "EN", "10-EN");
-INSERT INTO text_table VALUES("10", "SV", "10-SV");
+INSERT INTO t2 VALUES("0", "EN", "0-EN");
+INSERT INTO t2 VALUES("0", "SV", "0-SV");
+INSERT INTO t2 VALUES("10", "EN", "10-EN");
+INSERT INTO t2 VALUES("10", "SV", "10-SV");
+
+SELECT t1.id, t1.text_id, t2.text_data
+ FROM t1 LEFT JOIN t2
+ ON t1.text_id = t2.text_id
+ AND t2.language_id = 'SV'
+ WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
+
+DROP TABLE t1, t2;
+
+# Test for bug #5896
+
+CREATE TABLE t0 (a0 int PRIMARY KEY);
+CREATE TABLE t1 (a1 int PRIMARY KEY);
+CREATE TABLE t2 (a2 int);
+CREATE TABLE t3 (a3 int);
+INSERT INTO t0 VALUES (1);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1), (2);
+INSERT INTO t3 VALUES (1), (2);
+
+SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
+SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
+EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
+SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
+EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
+
+INSERT INTO t0 VALUES (0);
+INSERT INTO t1 VALUES (0);
+SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+
+# Test for BUG#4480
+drop table t1,t2;
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 (a int, b int);
+insert into t2 values (1,1), (2,2);
+
+select * from t2 right join t1 on t2.a=t1.a;
+select straight_join * from t2 right join t1 on t2.a=t1.a;
+
+DROP TABLE t0,t1,t2,t3;
+
+#
+# Test for bug #9017: left join mistakingly converted to inner join
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
+INSERT INTO t2 VALUES (1,2), (2,2);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+ WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
+
+DROP TABLE t1,t2;
+
+# Bug #8681: Bad warning message when group_concat() exceeds max length
+set group_concat_max_len=5;
+create table t1 (a int, b varchar(20));
+create table t2 (a int, c varchar(20));
+insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
+insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
+select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
+select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
+select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
+select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
+drop table t1, t2;
+set group_concat_max_len=default;
+
+# End of 4.1 tests
+
+#
+# BUG#10162 - ON is merged with WHERE, left join is convered to a regular join
+#
+create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key (gid,x,y));
+insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
+create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key (gid,id,x,y), key id (id));
+insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
+create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key (id,set_id));
+insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');
+explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
+left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
+and t1.gid =1 and t2.gid =1 and t3.set_id =1;
+drop tables t1,t2,t3;
+
+#
+# Test for bug #9938: invalid conversion from outer join to inner join
+# for queries containing indirect reference in WHERE clause
+#
+
+CREATE TABLE t1 (EMPNUM INT, GRP INT);
+INSERT INTO t1 VALUES (0, 10);
+INSERT INTO t1 VALUES (2, 30);
+
+CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
+INSERT INTO t2 VALUES (0, 'KERI');
+INSERT INTO t2 VALUES (9, 'BARRY');
+
+CREATE VIEW v1 AS
+SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
+ FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
+
+SELECT * FROM v1;
+SELECT * FROM v1 WHERE EMPNUM < 10;
+
+DROP TABLE t1,t2;
+
+#
+# Test for bug #11285: false Item_equal on expression in outer join
+#
+
+CREATE TABLE t1 (c11 int);
+CREATE TABLE t2 (c21 int);
+INSERT INTO t1 VALUES (30), (40), (50);
+INSERT INTO t2 VALUES (300), (400), (500);
+SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
+DROP TABLE t1, t2;
+
+#
+# Test for bugs
+# #12101: erroneously applied outer join elimination in case of WHERE NOT BETWEEN
+# #12102: erroneously missing outer join elimination in case of WHERE IN/IF
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+
+INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
+INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);
+
+DROP TABLE t1,t2;
+
--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.