diff options
50 files changed, 1418 insertions, 485 deletions
diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 0946dc8f809..9d337a1ed34 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -172,6 +172,10 @@ a 0 2 DROP TABLE t1; +create table t1 (a int); +delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; +delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; +drop table t1; CREATE TABLE t1 (a int not null,b int not null); CREATE TABLE t2 (a int not null, b int not null, primary key (a,b)); CREATE TABLE t3 (a int not null, b int not null, primary key (a,b)); diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 675e0faa1a0..aef29ef9525 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -572,14 +572,14 @@ COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') DROP TABLE t1,t2; select * from (select group_concat('c') from DUAL) t; group_concat('c') -NULL +c create table t1 ( a int not null default 0); select * from (select group_concat(a) from t1) t2; group_concat(a) NULL select group_concat('x') UNION ALL select 1; group_concat('x') -NULL +x 1 drop table t1; CREATE TABLE t1 (id int, a varchar(9)); diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index c7d95a4a446..3838f04e20d 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -847,6 +847,22 @@ EXPLAIN SELECT MAX(b) FROM t1; 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; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(2,3); +SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; +(SELECT COUNT(DISTINCT t1.b)) +0 +2 +SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; +(SELECT COUNT(DISTINCT 12)) +1 +1 +SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12), +COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2), +GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2); +AVG(2) BIT_AND(2) BIT_OR(2) BIT_XOR(2) COUNT(*) COUNT(12) COUNT(DISTINCT 12) MIN(2) MAX(2) STD(2) VARIANCE(2) SUM(2) GROUP_CONCAT(2) GROUP_CONCAT(DISTINCT 2) +2.00000 2 2 2 1 1 1 2 2 0.00000 0.00000 2 2 2 +DROP TABLE t1; create table t2 (ff double); insert into t2 values (2.2); select cast(sum(distinct ff) as decimal(5,2)) from t2; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 940ca71ebbc..f99847a679a 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1113,4 +1113,39 @@ conv("18383815659218730760",10,10) + 0 select "18383815659218730760" + 0; "18383815659218730760" + 0 1.8383815659219e+19 +CREATE TABLE t1 (code varchar(10)); +INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13'); +SELECT ASCII(code), code FROM t1 WHERE code='A12'; +ASCII(code) code +97 a12 +65 A12 +SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65; +ASCII(code) code +65 A12 +INSERT INTO t1 VALUES ('a12 '), ('A12 '); +SELECT LENGTH(code), code FROM t1 WHERE code='A12'; +LENGTH(code) code +3 a12 +3 A12 +4 a12 +5 A12 +SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5; +LENGTH(code) code +5 A12 +ALTER TABLE t1 ADD INDEX (code); +CREATE TABLE t2 (id varchar(10) PRIMARY KEY); +INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14'); +SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id +WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00'); +code id +A12 a12 +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON code=id +WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref code code 13 const 3 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 12 const 1 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (`test`.`t2`.`id` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5)) +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index cfb675652f2..2e4f03a17d9 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -840,39 +840,38 @@ drop table t1; create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); -select f1 from t1 where f1 between "2006-1-1" and 20060101; +select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date); f1 2006-01-01 -select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; +select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 -select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; +select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 -select f2 from t1 where f2 between "12:1:2" and "12:2:2"; +select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 -select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; +select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 -select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); f3 2006-01-01 12:01:01 -select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); f3 2006-01-01 12:01:01 -select f1 from t1 where "2006-1-1" between f1 and f3; +select f1 from t1 where cast("2006-1-1" as date) between f1 and f3; f1 2006-01-01 -select f1 from t1 where "2006-1-1" between date(f1) and date(f3); +select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3); f1 2006-01-01 -select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date); f1 Warnings: -Warning 1292 Incorrect date value: 'zzz' for column 'f1' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: 'zzz' -Warning 1292 Truncated incorrect DOUBLE value: 'zzz' +Warning 1292 Truncated incorrect datetime value: 'zzz' +Warning 1292 Truncated incorrect datetime value: 'zzz' select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); f1 2006-01-01 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 709d60b963f..80988bd8047 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -807,8 +807,8 @@ explain SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 where t2.b=v1.a GROUP BY t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index b b 2 NULL 10 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 1 test.t2.b 1 +1 SIMPLE t2 index b b 2 NULL 10 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 1 test.t2.b 1 SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 where t2.b=v1.a GROUP BY t2.b; a b real_b diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result index bf7c250cbcf..41f00fd2ad8 100644 --- a/mysql-test/r/heap_hash.result +++ b/mysql-test/r/heap_hash.result @@ -354,7 +354,7 @@ t3 1 a 2 b NULL 13 NULL NULL HASH explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref heap_idx heap_idx 22 const 7 Using where -1 SIMPLE t3 ref a a 44 const,const 7 Using where +1 SIMPLE t3 ref a a 44 func,const 7 Using where drop table t1, t2, t3; create temporary table t1 ( a int, index (a) ) engine=memory; insert into t1 values (1),(2),(3),(4),(5); diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 077fa0f2376..5d355f1b8db 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -353,7 +353,7 @@ mysql test explain select * from v0; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY # ALL NULL NULL NULL NULL 2 +1 SIMPLE # ALL NULL NULL NULL NULL 2 create view v1 (c) as select table_name from information_schema.tables where table_name="v1"; select * from v1; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 98e03239567..fc38a38eb36 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -124,7 +124,7 @@ min(7) NULL select min(7) from DUAL; min(7) -NULL +7 explain select min(7) from t2m join t1m; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away @@ -139,7 +139,7 @@ max(7) NULL select max(7) from DUAL; max(7) -NULL +7 explain select max(7) from t2m join t1m; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away @@ -178,7 +178,7 @@ min(7) NULL select min(7) from DUAL; min(7) -NULL +7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 @@ -194,7 +194,7 @@ max(7) NULL select max(7) from DUAL; max(7) -NULL +7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 030c1976e20..169b5286fbe 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -62,10 +62,10 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,values(`test`.`t1`.`a`) AS `VALUES(a)` from `test`.`t1` explain extended select * from t1 where values(a); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where values(`test`.`t1`.`a`) DROP TABLE t1; create table t1(a int primary key, b int); insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5); @@ -197,3 +197,25 @@ PRIMARY KEY (a) ) ENGINE=MyISAM; INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ; DROP TABLE t1; +CREATE TABLE t1 +( +a BIGINT UNSIGNED, +b BIGINT UNSIGNED, +PRIMARY KEY (a) +); +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 1 +INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 2 +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 2 +DROP TABLE t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 0a309371725..4d58d035f21 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1126,7 +1126,7 @@ a b a b 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 t2 ALL PRIMARY NULL NULL NULL 4 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 diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 993426a02fc..f9e211c8186 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -611,7 +611,7 @@ C NULL EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a int(11) NOT NULL); diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 684d216f856..96289305da1 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -949,24 +949,24 @@ COUNT(*) Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: '20050328 invalid' -Warning 1292 Truncated incorrect DOUBLE value: '20050328 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' -Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 4141e6061d2..a7074b9a9ed 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -750,13 +750,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index EXPLAIN SELECT a,b FROM t1 WHERE a < 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index EXPLAIN SELECT a,b FROM v1 WHERE a < 2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT a,b FROM t1 WHERE a < 2 and b=3; a b 1 3 @@ -799,13 +799,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from v1 where a in (3,4) and b in (1,2,3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from t1 where a between 3 and 4 and b between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index explain select * from v1 where a between 3 and 4 and b between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index drop view v1; drop table t1; create table t3 (a int); diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 3028f42de1a..75258af552b 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -181,3 +181,128 @@ SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NUL select row(NULL,1)=(2,0); row(NULL,1)=(2,0) 0 +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3); +EXPLAIN SELECT * FROM t1 WHERE a=3 AND b=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 Using index +EXPLAIN SELECT * FROM t1 WHERE (a,b)=(3,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 8 const,const 1 Using index +SELECT * FROM t1 WHERE a=3 and b=2; +a b +3 2 +SELECT * FROM t1 WHERE (a,b)=(3,2); +a b +3 2 +CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); +INSERT INTO t2 VALUES +(1,1,2), (3,1,3), (1,2,2), (4,4,2), +(1,1,1), (3,1,1), (1,2,1); +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 8 test.t1.a,test.t1.b 1 Using index +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 8 test.t1.a,test.t1.b 1 Using index +SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b=t2.b; +a b a b c +1 1 1 1 1 +1 1 1 1 2 +1 2 1 2 1 +1 2 1 2 2 +3 1 3 1 1 +3 1 3 1 3 +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); +a b a b c +1 1 1 1 1 +1 1 1 1 2 +1 2 1 2 1 +1 2 1 2 2 +3 1 3 1 1 +3 1 3 1 3 +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 5 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 5 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b; +a b a b c +1 1 1 1 2 +1 1 3 1 3 +1 2 1 2 2 +1 1 1 1 1 +1 1 3 1 1 +1 2 1 2 1 +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); +a b a b c +1 2 1 1 1 +1 2 1 1 2 +1 2 1 2 1 +1 2 1 2 2 +3 2 3 1 1 +3 2 3 1 3 +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); +a b a b c +1 2 1 1 1 +1 2 1 1 2 +3 2 3 1 1 +3 2 3 1 3 +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 index NULL PRIMARY 12 NULL 7 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t1`.`a` - 1) = (`test`.`t2`.`a` - 1)) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) +SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); +a b a b c +1 2 1 1 2 +3 2 3 1 3 +1 2 1 1 1 +3 2 3 1 1 +EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 8 const,const 1 Using index +EXPLAIN SELECT * FROM t2 WHERE (a,b)=(3,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 8 const,const 1 Using index +SELECT * FROM t2 WHERE a=3 and b=2; +a b c +SELECT * FROM t2 WHERE (a,b)=(3,2); +a b c +EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a AND t2.b=2 AND t2.c=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 12 test.t1.a,const,const 1 Using index +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 12 test.t1.a,const,const 1 Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 2) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) +SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); +a b a b c +1 1 1 2 1 +1 2 1 2 1 +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 6 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 12 test.t1.a,const,const 1 Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 2) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) +SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); +a b a b c +1 1 1 2 1 +1 2 1 2 1 +DROP TABLE t1,t2; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index fb40b52475b..5fdc7275fd7 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5379,5 +5379,103 @@ Procedure sql_mode Create Procedure bug21416 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`() show create procedure bug21416 drop procedure bug21416| +drop table if exists t3| +drop database if exists mysqltest1| +create table t3 (a int)| +insert into t3 (a) values (1), (2)| +create database mysqltest1| +use mysqltest1| +drop database mysqltest1| +select database()| +database() +NULL +select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| +a +1 +use test| +drop table t3| +DROP PROCEDURE IF EXISTS bug16899_p1| +DROP FUNCTION IF EXISTS bug16899_f1| +CREATE DEFINER=1234567890abcdefGHIKL@localhost PROCEDURE bug16899_p1() +BEGIN +SET @a = 1; +END| +ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16) +CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY +FUNCTION bug16899_f1() RETURNS INT +BEGIN +RETURN 1; +END| +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) +drop procedure if exists bug21416| +create procedure bug21416() show create procedure bug21416| +call bug21416()| +Procedure sql_mode Create Procedure +bug21416 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`() +show create procedure bug21416 +drop procedure bug21416| +CREATE TABLE t3 ( +Member_ID varchar(15) NOT NULL, +PRIMARY KEY (Member_ID) +)| +CREATE TABLE t4 ( +ID int(10) unsigned NOT NULL auto_increment, +Member_ID varchar(15) NOT NULL default '', +Action varchar(12) NOT NULL, +Action_Date datetime NOT NULL, +Track varchar(15) default NULL, +User varchar(12) default NULL, +Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update +CURRENT_TIMESTAMP, +PRIMARY KEY (ID), +KEY Action (Action), +KEY Action_Date (Action_Date) +)| +INSERT INTO t3(Member_ID) VALUES +('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')| +INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES +('111111', 'Disenrolled', '2006-03-01', 'CAD' ), +('111111', 'Enrolled', '2006-03-01', 'CAD' ), +('111111', 'Disenrolled', '2006-07-03', 'CAD' ), +('222222', 'Enrolled', '2006-03-07', 'CAD' ), +('222222', 'Enrolled', '2006-03-07', 'CHF' ), +('222222', 'Disenrolled', '2006-08-02', 'CHF' ), +('333333', 'Enrolled', '2006-03-01', 'CAD' ), +('333333', 'Disenrolled', '2006-03-01', 'CAD' ), +('444444', 'Enrolled', '2006-03-01', 'CAD' ), +('555555', 'Disenrolled', '2006-03-01', 'CAD' ), +('555555', 'Enrolled', '2006-07-21', 'CAD' ), +('555555', 'Disenrolled', '2006-03-01', 'CHF' ), +('666666', 'Enrolled', '2006-02-09', 'CAD' ), +('666666', 'Enrolled', '2006-05-12', 'CHF' ), +('666666', 'Disenrolled', '2006-06-01', 'CAD' )| +DROP FUNCTION IF EXISTS bug21493| +Warnings: +Note 1305 FUNCTION bug21493 does not exist +CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45) +BEGIN +DECLARE tracks VARCHAR(45); +SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4 +WHERE Member_ID=paramMember AND Action='Enrolled' AND +(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4 +WHERE Member_ID=paramMember GROUP BY Track); +RETURN tracks; +END| +SELECT bug21493('111111')| +bug21493('111111') +NULL +SELECT bug21493('222222')| +bug21493('222222') +CAD +SELECT bug21493(Member_ID) FROM t3| +bug21493(Member_ID) +NULL +CAD +CAD +CAD +CAD +CHF +DROP FUNCTION bug21493| +DROP TABLE t3,t4| End of 5.0 tests drop table t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 141f9ed0f4a..ba16bb9410b 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1011,7 +1011,7 @@ INSERT INTO t1 VALUES (1); UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); select * from t1; i -1 +2 drop table t1; CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; @@ -1203,7 +1203,7 @@ UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t); ERROR 42S22: Unknown column 't.i' in 'field list' select * from t1; i -1 +3 drop table t1; CREATE TABLE t1 ( id int(11) default NULL @@ -2937,6 +2937,35 @@ retailerID statusID changed 0048 1 2006-01-06 12:37:50 0059 1 2006-01-06 12:37:50 drop table t1; +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'); +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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 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; +a a b +10 3 35989 +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, t2.b 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 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, t2.b LIMIT 1) WHERE t1.a = 10; +a a b +10 1 359 +drop table t1,t2; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); @@ -3368,3 +3397,28 @@ ORDER BY t1.t DESC LIMIT 1); i1 i2 t i1 i2 t 24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40 DROP TABLE t1, t2; +CREATE TABLE t1 (i INT); +(SELECT i FROM t1) UNION (SELECT i FROM t1); +i +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS +( +(SELECT i FROM t1) UNION +(SELECT i FROM t1) +); +i +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); +i +explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) +from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12)) +from t1' at line 1 +explain select * from t1 where not exists +((select t11.i from t1 t11) union (select t12.i from t1 t12)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +4 UNION t12 system NULL NULL NULL NULL 0 const row not found +NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL +DROP TABLE t1; diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 3f0b9f4c13c..12ce742eab4 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -27,12 +27,12 @@ INSERT INTO t1 VALUES ( "2000-1-2" ); INSERT INTO t1 VALUES ( "2000-1-3" ); INSERT INTO t1 VALUES ( "2000-1-4" ); INSERT INTO t1 VALUES ( "2000-1-5" ); -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND "2000-1-4"; +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); datum 2000-01-02 2000-01-03 2000-01-04 -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; datum DROP TABLE t1; CREATE TABLE t1 ( @@ -104,3 +104,9 @@ SELECT * FROM t1; y 0000 DROP TABLE t1; +create table t1(start_date date, end_date date); +insert into t1 values ('2000-01-01','2000-01-02'); +select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date; +1 +1 +drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 58a217f4b14..05870d23ed6 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1365,7 +1365,7 @@ drop table t1, t2; (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)); avg(1) -NULL +1.0000 select _utf8'12' union select _latin1'12345'; 12 12 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 7e14874a3e6..5d009ed7840 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -49,7 +49,7 @@ select v1.b from v1; ERROR 42S22: Unknown column 'v1.b' in 'field list' explain extended select c from v1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1` create algorithm=temptable view v2 (c) as select b+1 from t1; @@ -83,7 +83,7 @@ c 12 explain extended select c from v3; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1` create algorithm=temptable view v4 (c) as select c+1 from v2; @@ -376,7 +376,7 @@ c 30 explain extended select * from v1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3) update v1 set c=c+1; @@ -1391,9 +1391,9 @@ a a b 4 NULL NULL explain extended select * from t3 left join v3 on (t3.a = v3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 create view v1 (a) as select a from t1; @@ -1406,9 +1406,9 @@ a a b 4 NULL NULL explain extended select * from t3 left join v4 on (t3.a = v4.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; @@ -2321,12 +2321,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v1 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index -1 PRIMARY t2 ref a a 10 const,test.t1.b 2 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v2 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where DROP VIEW v1,v2; DROP TABLE t1,t2,t3; create table t1 (f1 int); @@ -2409,7 +2409,7 @@ insert into t1 values (1),(2); create view v1 as select * from t1; explain select id from v1 order by id; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index drop view v1; drop table t1; create table t1(f1 int, f2 int); @@ -2480,7 +2480,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT MAX(a) FROM v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away SELECT MIN(a) FROM t1; MIN(a) 0 @@ -2492,7 +2492,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT MIN(a) FROM v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (x varchar(10)); @@ -2586,13 +2586,13 @@ INSERT INTO t1 VALUES (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; +SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); id td 2 2005-01-02 3 2005-01-02 4 2005-01-03 5 2005-01-04 -SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; +SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); id td 2 2005-01-02 3 2005-01-02 @@ -2916,4 +2916,23 @@ DROP FUNCTION f1; DROP FUNCTION f2; DROP VIEW v1, v2; DROP TABLE t1; +CREATE TABLE t1 (s1 int); +CREATE VIEW v1 AS SELECT * FROM t1; +EXPLAIN SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +INSERT INTO t1 VALUES (1), (3), (2); +EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +DROP VIEW v1; +DROP TABLE t1; End of 5.0 tests. diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 30e9d1010ba..f325d1ed7b6 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -105,7 +105,7 @@ ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for tabl grant select on mysqltest.t1 to mysqltest_1@localhost; explain select c from mysqltest.v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v1; ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' explain select c from mysqltest.v2; @@ -125,7 +125,7 @@ ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for tabl grant show view on mysqltest.* to mysqltest_1@localhost; explain select c from mysqltest.v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` @@ -138,7 +138,7 @@ View Create View v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` explain select c from mysqltest.v3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found show create view mysqltest.v3; View Create View v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 140cdccc218..aebee0b0c6f 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -670,6 +670,11 @@ alter table t1 max_rows=100000000000; show create table t1; drop table t1; +# +# Bug#21772: can not name a column 'upgrade' when create a table +# +create table t1 (upgrade int); +drop table t1; # End of 5.0 tests diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 677ffaa2860..865e1746fd3 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -153,6 +153,16 @@ DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1; SELECT * FROM t1; DROP TABLE t1; +# +# Bug #21392: multi-table delete with alias table name fails with +# 1003: Incorrect table name +# + +create table t1 (a int); +delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; +delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; +drop table t1; + # End of 4.1 tests # diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 3a62b9feb3a..78393ee1104 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -573,6 +573,19 @@ INSERT INTO t1 VALUES SELECT MAX(b) FROM t1; EXPLAIN SELECT MAX(b) FROM t1; DROP TABLE t1; +# +# Bug #16792 query with subselect, join, and group not returning proper values +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(2,3); + +SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; +SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; +# an attempt to test all aggregate function with no table. +SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12), + COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2), + GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2); +DROP TABLE t1; # End of 4.1 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 8753db0ebe1..45415882ac7 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -753,4 +753,31 @@ select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)); select conv("18383815659218730760",10,10) + 0; select "18383815659218730760" + 0; +# +# Bug #21698: substitution of a string field for a constant under a function +# + +CREATE TABLE t1 (code varchar(10)); +INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13'); + +SELECT ASCII(code), code FROM t1 WHERE code='A12'; +SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65; + +INSERT INTO t1 VALUES ('a12 '), ('A12 '); + +SELECT LENGTH(code), code FROM t1 WHERE code='A12'; +SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5; + +ALTER TABLE t1 ADD INDEX (code); +CREATE TABLE t2 (id varchar(10) PRIMARY KEY); +INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14'); + +SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id + WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00'); +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON code=id + WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00'); + +DROP TABLE t1,t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 7ca1b3e5433..08d3ff14ca1 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -419,20 +419,20 @@ drop table t1; # # Bug#16377 result of DATE/TIME functions were compared as strings which # can lead to a wrong result. -# +# Now wrong dates should be compared only with CAST() create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); -select f1 from t1 where f1 between "2006-1-1" and 20060101; -select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; -select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; -select f2 from t1 where f2 between "12:1:2" and "12:2:2"; -select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; -select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; -select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; -select f1 from t1 where "2006-1-1" between f1 and f3; -select f1 from t1 where "2006-1-1" between date(f1) and date(f3); -select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date); +select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date); +select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date); +select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time); +select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time); +select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); +select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); +select f1 from t1 where cast("2006-1-1" as date) between f1 and f3; +select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3); +select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date); select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); drop table t1; diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index eda768be1bc..b3813864464 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -115,4 +115,27 @@ INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ; DROP TABLE t1; +# +# Bug#21555: incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES +# + + # End of 4.1 tests +CREATE TABLE t1 +( + a BIGINT UNSIGNED, + b BIGINT UNSIGNED, + PRIMARY KEY (a) +); + +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index 6301cc0f584..63c611e6be6 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -92,3 +92,50 @@ SELECT ROW(NULL,10) <=> ROW(3,NULL); # SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NULL,1) = ROW(2,2,1) as `0`, ROW(1,NULL,1) = ROW(1,2,2) as `0`, ROW(1,NULL,1) = ROW(1,2,1) as `null` ; select row(NULL,1)=(2,0); + +# +# Bug #16081: row equalities are to be used for query optimizations +# + +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3); + +EXPLAIN SELECT * FROM t1 WHERE a=3 AND b=2; +EXPLAIN SELECT * FROM t1 WHERE (a,b)=(3,2); +SELECT * FROM t1 WHERE a=3 and b=2; +SELECT * FROM t1 WHERE (a,b)=(3,2); + +CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); +INSERT INTO t2 VALUES + (1,1,2), (3,1,3), (1,2,2), (4,4,2), + (1,1,1), (3,1,1), (1,2,1); + +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); +SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b=t2.b; +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); + +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2; +EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); +SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b; +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); + +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); +SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); + +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); +SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); + +EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2; +EXPLAIN SELECT * FROM t2 WHERE (a,b)=(3,2); +SELECT * FROM t2 WHERE a=3 and b=2; +SELECT * FROM t2 WHERE (a,b)=(3,2); + +EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a AND t2.b=2 AND t2.c=1; +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); +SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); + +EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); +SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); + +DROP TABLE t1,t2; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index bd480436d8c..32f9bf24703 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -6295,6 +6295,73 @@ create procedure bug21416() show create procedure bug21416| call bug21416()| drop procedure bug21416| +# +# BUG#21493: Crash on the second call of a procedure containing +# a select statement that uses an IN aggregating subquery +# + +CREATE TABLE t3 ( + Member_ID varchar(15) NOT NULL, + PRIMARY KEY (Member_ID) +)| + +CREATE TABLE t4 ( + ID int(10) unsigned NOT NULL auto_increment, + Member_ID varchar(15) NOT NULL default '', + Action varchar(12) NOT NULL, + Action_Date datetime NOT NULL, + Track varchar(15) default NULL, + User varchar(12) default NULL, + Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update + CURRENT_TIMESTAMP, + PRIMARY KEY (ID), + KEY Action (Action), + KEY Action_Date (Action_Date) +)| + + +INSERT INTO t3(Member_ID) VALUES + ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')| + +INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES + ('111111', 'Disenrolled', '2006-03-01', 'CAD' ), + ('111111', 'Enrolled', '2006-03-01', 'CAD' ), + ('111111', 'Disenrolled', '2006-07-03', 'CAD' ), + ('222222', 'Enrolled', '2006-03-07', 'CAD' ), + ('222222', 'Enrolled', '2006-03-07', 'CHF' ), + ('222222', 'Disenrolled', '2006-08-02', 'CHF' ), + ('333333', 'Enrolled', '2006-03-01', 'CAD' ), + ('333333', 'Disenrolled', '2006-03-01', 'CAD' ), + ('444444', 'Enrolled', '2006-03-01', 'CAD' ), + ('555555', 'Disenrolled', '2006-03-01', 'CAD' ), + ('555555', 'Enrolled', '2006-07-21', 'CAD' ), + ('555555', 'Disenrolled', '2006-03-01', 'CHF' ), + ('666666', 'Enrolled', '2006-02-09', 'CAD' ), + ('666666', 'Enrolled', '2006-05-12', 'CHF' ), + ('666666', 'Disenrolled', '2006-06-01', 'CAD' )| + +#--disable_warnings +DROP FUNCTION IF EXISTS bug21493| +#--enable_warnings + +CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45) +BEGIN +DECLARE tracks VARCHAR(45); +SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4 + WHERE Member_ID=paramMember AND Action='Enrolled' AND + (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4 + WHERE Member_ID=paramMember GROUP BY Track); +RETURN tracks; +END| + +SELECT bug21493('111111')| +SELECT bug21493('222222')| + +SELECT bug21493(Member_ID) FROM t3| + +DROP FUNCTION bug21493| +DROP TABLE t3,t4| + --echo End of 5.0 tests # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index ed122e9ff5a..a4e535ac418 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1892,6 +1892,32 @@ select * from t1 r1 group by r2.retailerId); drop table t1; +# +# Bug #21180: Subselect with index for both WHERE and ORDER BY +# produces empty result +# +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'); + +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; +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; + +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, t2.b LIMIT 1) WHERE t1.a = 10; +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; + +drop table t1,t2; + # End of 4.1 tests # @@ -2280,3 +2306,29 @@ SELECT * FROM t1,t2 ORDER BY t1.t DESC LIMIT 1); DROP TABLE t1, t2; + +# +# Bug#14654 : Cannot select from the same table twice within a UNION +# statement +# +CREATE TABLE t1 (i INT); + +(SELECT i FROM t1) UNION (SELECT i FROM t1); +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS + ( + (SELECT i FROM t1) UNION + (SELECT i FROM t1) + ); + +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); + +#TODO:not supported +--error 1064 +explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) + from t1; +#supported +explain select * from t1 where not exists + ((select t11.i from t1 t11) union (select t12.i from t1 t12)); + +DROP TABLE t1; diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 78bdd9b8a80..c6050753943 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -36,8 +36,8 @@ INSERT INTO t1 VALUES ( "2000-1-2" ); INSERT INTO t1 VALUES ( "2000-1-3" ); INSERT INTO t1 VALUES ( "2000-1-4" ); INSERT INTO t1 VALUES ( "2000-1-5" ); -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND "2000-1-4"; -SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); +SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; DROP TABLE t1; # @@ -115,4 +115,11 @@ INSERT INTO t1 VALUES ('abc'); SELECT * FROM t1; DROP TABLE t1; +# +# Bug#21677: Wrong result when comparing a DATE and a DATETIME in BETWEEN +# +create table t1(start_date date, end_date date); +insert into t1 values ('2000-01-01','2000-01-02'); +select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date; +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index ee9c3b2ed45..7d1ddccba83 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2454,8 +2454,8 @@ INSERT INTO t1 VALUES CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; -SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04'; +SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); +SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); DROP VIEW v1; DROP TABLE t1; @@ -2838,5 +2838,21 @@ DROP FUNCTION f2; DROP VIEW v1, v2; DROP TABLE t1; +# +# Bug #5500: wrong select_type in EXPLAIN output for queries over views +# + +CREATE TABLE t1 (s1 int); +CREATE VIEW v1 AS SELECT * FROM t1; + +EXPLAIN SELECT * FROM t1; +EXPLAIN SELECT * FROM v1; +INSERT INTO t1 VALUES (1), (3), (2); + +EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); + +DROP VIEW v1; +DROP TABLE t1; --echo End of 5.0 tests. diff --git a/sql/item.cc b/sql/item.cc index 577eff3e5f6..62292a38245 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3840,13 +3840,48 @@ Item_equal *Item_field::find_item_equal(COND_EQUAL *cond_equal) /* + Check whether a field can be substituted by an equal item + + SYNOPSIS + equal_fields_propagator() + arg - *arg != NULL <-> the field is in the context where + substitution for an equal item is valid + + DESCRIPTION + The function checks whether a substitution of the field + occurrence for an equal item is valid. + + NOTES + The following statement is not always true: + x=y => F(x)=F(x/y). + This means substitution of an item for an equal item not always + yields an equavalent condition. + Here's an example: + 'a'='a ' + (LENGTH('a')=1) != (LENGTH('a ')=2) + Such a substitution is surely valid if either the substituted + field is not of a STRING type or if it is an argument of + a comparison predicate. + + RETURN + TRUE substitution is valid + FALSE otherwise +*/ + +bool Item_field::subst_argument_checker(byte **arg) +{ + return (result_type() != STRING_RESULT) || (*arg); +} + + +/* Set a pointer to the multiple equality the field reference belongs to (if any) SYNOPSIS equal_fields_propagator() - arg - reference to list of multiple equalities where - the field (this object) is to be looked for + arg - reference to list of multiple equalities where + the field (this object) is to be looked for DESCRIPTION The function looks for a multiple equality containing the field item @@ -3858,7 +3893,7 @@ Item_equal *Item_field::find_item_equal(COND_EQUAL *cond_equal) NOTES This function is supposed to be called as a callback parameter in calls - of the transform method. + of the compile method. RETURN VALUES pointer to the replacing constant item, if the field item was substituted diff --git a/sql/item.h b/sql/item.h index f5a5e6e4fd9..af075c34869 100644 --- a/sql/item.h +++ b/sql/item.h @@ -439,7 +439,19 @@ public: }; -typedef bool (Item::*Item_processor)(byte *arg); +typedef bool (Item::*Item_processor) (byte *arg); +/* + Analyzer function + SYNOPSIS + argp in/out IN: Analysis parameter + OUT: Parameter to be passed to the transformer + + RETURN + TRUE Invoke the transformer + FALSE Don't do it + +*/ +typedef bool (Item::*Item_analyzer) (byte **argp); typedef Item* (Item::*Item_transformer) (byte *arg); typedef void (*Cond_traverser) (const Item *item, void *arg); @@ -776,6 +788,30 @@ public: virtual Item* transform(Item_transformer transformer, byte *arg); + /* + This function performs a generic "compilation" of the Item tree. + The process of compilation is assumed to go as follows: + + compile() + { + if (this->*some_analyzer(...)) + { + compile children if any; + this->*some_transformer(...); + } + } + + i.e. analysis is performed top-down while transformation is done + bottom-up. + */ + virtual Item* compile(Item_analyzer analyzer, byte **arg_p, + Item_transformer transformer, byte *arg_t) + { + if ((this->*analyzer) (arg_p)) + return ((this->*transformer) (arg_t)); + return 0; + } + virtual void traverse_cond(Cond_traverser traverser, void *arg, traverse_order order) { @@ -813,6 +849,12 @@ public: */ virtual bool check_partition_func_processor(byte *bool_arg) { *(bool *)bool_arg= FALSE; return 0; } + virtual bool subst_argument_checker(byte **arg) + { + if (*arg) + *arg= NULL; + return TRUE; + } virtual Item *equal_fields_propagator(byte * arg) { return this; } virtual bool set_no_const_sub(byte *arg) { return FALSE; } @@ -1315,6 +1357,7 @@ public: return field->can_be_compared_as_longlong(); } Item_equal *find_item_equal(COND_EQUAL *cond_equal); + bool subst_argument_checker(byte **arg); Item *equal_fields_propagator(byte *arg); bool set_no_const_sub(byte *arg); Item *replace_equal_field(byte *arg); @@ -2222,7 +2265,11 @@ public: { return Item_field::save_in_field(field_arg, no_conversions); } - table_map used_tables() const { return (table_map)0L; } + /* + We use RAND_TABLE_BIT to prevent Item_insert_value from + being treated as a constant and precalculated before execution + */ + table_map used_tables() const { return RAND_TABLE_BIT; } bool walk(Item_processor processor, bool walk_subquery, byte *args) { diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index fd0b348ac85..0aa6d432966 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -77,131 +77,14 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems) This function aggregates result types from the array of items. Found type supposed to be used later for comparison of values of these items. Aggregation itself is performed by the item_cmp_type() function. - - NOTES - Aggregation rules: - If there are DATE/TIME fields/functions in the list and no string - fields/functions in the list then: - The INT_RESULT type will be used for aggregation instead of original - result type of any DATE/TIME field/function in the list - All constant items in the list will be converted to a DATE/TIME using - found field or result field of found function. - - Implementation notes: - The code is equivalent to: - 1. Check the list for presence of a STRING field/function. - Collect the is_const flag. - 2. Get a Field* object to use for type coercion - 3. Perform type conversion. - 1 and 2 are implemented in 2 loops. The first searches for a DATE/TIME - field/function and checks presence of a STRING field/function. - The second loop works only if a DATE/TIME field/function is found. - It checks presence of a STRING field/function in the rest of the list. - - TODO - 1) The current implementation can produce false comparison results for - expressions like: - date_time_field BETWEEN string_field_with_dates AND string_constant - if the string_constant will omit some of leading zeroes. - In order to fully implement correct comparison of DATE/TIME the new - DATETIME_RESULT result type should be introduced and agg_cmp_type() - should return the DATE/TIME field used for the conversion. Later - this field can be used by comparison functions like Item_func_between to - convert string values to ints on the fly and thus return correct results. - This modification will affect functions BETWEEN, IN and CASE. - - 2) If in the list a DATE field/function and a DATETIME field/function - are present in the list then the first found field/function will be - used for conversion. This may lead to wrong results and probably should - be fixed. */ static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems) { uint i; - Item::Type res= (Item::Type)0; - /* Used only for date/time fields, max_length = 19 */ - char buff[20]; - uchar null_byte; - Field *field= NULL; - - /* - Do not convert items while creating a or showing a view in order - to store/display the original query in these cases. - */ - if (thd->lex->sql_command != SQLCOM_CREATE_VIEW && - thd->lex->sql_command != SQLCOM_SHOW_CREATE) - { - /* Search for date/time fields/functions */ - for (i= 0; i < nitems; i++) - { - if (!items[i]->result_as_longlong()) - { - /* Do not convert anything if a string field/function is present */ - if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT) - { - i= nitems; - break; - } - continue; - } - if ((res= items[i]->real_item()->type()) == Item::FIELD_ITEM && - items[i]->result_type() != INT_RESULT) - { - field= ((Item_field *)items[i]->real_item())->field; - break; - } - else if (res == Item::FUNC_ITEM) - { - field= items[i]->tmp_table_field_from_field_type(0,0); - if (field) - field->move_field(buff, &null_byte, 0); - break; - } - } - } - if (field) - { - /* Check the rest of the list for presence of a string field/function. */ - for (i++ ; i < nitems; i++) - { - if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT && - !items[i]->result_as_longlong()) - { - if (res == Item::FUNC_ITEM) - delete field; - field= 0; - break; - } - } - } - /* - If the first item is a date/time function then its result should be - compared as int - */ - if (field) - /* Suppose we are comparing dates */ - type[0]= INT_RESULT; - else - type[0]= items[0]->result_type(); - - for (i= 0; i < nitems ; i++) - { - Item_result result= items[i]->result_type(); - /* - Use INT_RESULT as result type for DATE/TIME fields/functions and - for constants successfully converted to DATE/TIME - */ - if (field && - ((!items[i]->const_item() && items[i]->result_as_longlong()) || - (items[i]->const_item() && convert_constant_item(thd, field, - &items[i])))) - result= INT_RESULT; - type[0]= item_cmp_type(type[0], result); - } - - if (res == Item::FUNC_ITEM && field) - delete field; + type[0]= items[0]->result_type(); + for (i= 1 ; i < nitems ; i++) + type[0]= item_cmp_type(type[0], items[i]->result_type()); } @@ -1235,10 +1118,32 @@ void Item_func_between::fix_length_and_dec() if (!args[0] || !args[1] || !args[2]) return; agg_cmp_type(thd, &cmp_type, args, 3); - args[0]->cmp_context= args[1]->cmp_context= args[2]->cmp_context= cmp_type; + if (cmp_type == STRING_RESULT && + agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV, 1)) + return; - if (cmp_type == STRING_RESULT) - agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV, 1); + /* + Make a special case of compare with date/time and longlong fields. + They are compared as integers, so for const item this time-consuming + conversion can be done only once, not for every single comparison + */ + if (args[0]->type() == FIELD_ITEM && + thd->lex->sql_command != SQLCOM_CREATE_VIEW && + thd->lex->sql_command != SQLCOM_SHOW_CREATE) + { + Field *field=((Item_field*) args[0])->field; + if (field->can_be_compared_as_longlong()) + { + /* + The following can't be recoded with || as convert_constant_item + changes the argument + */ + if (convert_constant_item(thd, field,&args[1])) + cmp_type=INT_RESULT; // Works for all types. + if (convert_constant_item(thd, field,&args[2])) + cmp_type=INT_RESULT; // Works for all types. + } + } } @@ -2762,16 +2667,16 @@ bool Item_cond::walk(Item_processor processor, bool walk_subquery, byte *arg) SYNOPSIS transform() - transformer the transformer callback function to be applied to the nodes - of the tree of the object - arg parameter to be passed to the transformer + transformer the transformer callback function to be applied to the nodes + of the tree of the object + arg parameter to be passed to the transformer DESCRIPTION - The function recursively applies the transform method with the - same transformer to each member item of the condition list. + The function recursively applies the transform method to each + member item of the condition list. If the call of the method for a member item returns a new item the old item is substituted for a new one. - After this the transform method is applied to the root node + After this the transformer is applied to the root node of the Item_cond object. RETURN VALUES @@ -2802,6 +2707,55 @@ Item *Item_cond::transform(Item_transformer transformer, byte *arg) return Item_func::transform(transformer, arg); } + +/* + Compile Item_cond object with a processor and a transformer callback functions + + SYNOPSIS + compile() + analyzer the analyzer callback function to be applied to the nodes + of the tree of the object + arg_p in/out parameter to be passed to the analyzer + transformer the transformer callback function to be applied to the nodes + of the tree of the object + arg_t parameter to be passed to the transformer + + DESCRIPTION + First the function applies the analyzer to the root node of + the Item_func object. Then if the analyzer succeeeds (returns TRUE) + the function recursively applies the compile method to member + item of the condition list. + If the call of the method for a member item returns a new item + the old item is substituted for a new one. + After this the transformer is applied to the root node + of the Item_cond object. + + RETURN VALUES + Item returned as the result of transformation of the root node +*/ + +Item *Item_cond::compile(Item_analyzer analyzer, byte **arg_p, + Item_transformer transformer, byte *arg_t) +{ + if (!(this->*analyzer)(arg_p)) + return 0; + + List_iterator<Item> li(list); + Item *item; + while ((item= li++)) + { + /* + The same parameter value of arg_p must be passed + to analyze any argument of the condition formula. + */ + byte *arg_v= *arg_p; + Item *new_item= item->compile(analyzer, &arg_v, transformer, arg_t); + if (new_item && new_item != item) + li.replace(new_item); + } + return Item_func::transform(transformer, arg_t); +} + void Item_cond::traverse_cond(Cond_traverser traverser, void *arg, traverse_order order) { diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 04462e05e9f..a9149ade097 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -241,6 +241,7 @@ public: Item *neg_transformer(THD *thd); virtual Item *negated_item(); bool check_partition_func_processor(byte *bool_arg) { return 0;} + bool subst_argument_checker(byte **arg) { return TRUE; } }; class Item_func_not :public Item_bool_func @@ -1186,6 +1187,9 @@ public: void traverse_cond(Cond_traverser, void *arg, traverse_order order); void neg_arguments(THD *thd); bool check_partition_func_processor(byte *bool_arg) { return 0;} + bool subst_argument_checker(byte **arg) { return TRUE; } + Item *compile(Item_analyzer analyzer, byte **arg_p, + Item_transformer transformer, byte *arg_t); }; diff --git a/sql/item_func.cc b/sql/item_func.cc index e6b673c9014..4a069e662f9 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -236,22 +236,21 @@ void Item_func::traverse_cond(Cond_traverser traverser, } - /* Transform an Item_func object with a transformer callback function SYNOPSIS transform() - transformer the transformer callback function to be applied to the nodes - of the tree of the object - argument parameter to be passed to the transformer + transformer the transformer callback function to be applied to the nodes + of the tree of the object + argument parameter to be passed to the transformer DESCRIPTION - The function recursively applies the transform method with the - same transformer to each argument the function. - If the call of the method for a member item returns a new item + The function recursively applies the transform method to each + argument of the Item_func node. + If the call of the method for an argument item returns a new item the old item is substituted for a new one. - After this the transform method is applied to the root node + After this the transformer is applied to the root node of the Item_func object. RETURN VALUES @@ -285,6 +284,55 @@ Item *Item_func::transform(Item_transformer transformer, byte *argument) } +/* + Compile Item_func object with a processor and a transformer callback functions + + SYNOPSIS + compile() + analyzer the analyzer callback function to be applied to the nodes + of the tree of the object + arg_p in/out parameter to be passed to the processor + transformer the transformer callback function to be applied to the nodes + of the tree of the object + arg_t parameter to be passed to the transformer + + DESCRIPTION + First the function applies the analyzer to the root node of + the Item_func object. Then if the analizer succeeeds (returns TRUE) + the function recursively applies the compile method to each argument + of the Item_func node. + If the call of the method for an argument item returns a new item + the old item is substituted for a new one. + After this the transformer is applied to the root node + of the Item_func object. + + RETURN VALUES + Item returned as the result of transformation of the root node +*/ + +Item *Item_func::compile(Item_analyzer analyzer, byte **arg_p, + Item_transformer transformer, byte *arg_t) +{ + if (!(this->*analyzer)(arg_p)) + return 0; + if (arg_count) + { + Item **arg,**arg_end; + for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++) + { + /* + The same parameter value of arg_p must be passed + to analyze any argument of the condition formula. + */ + byte *arg_v= *arg_p; + Item *new_item= (*arg)->compile(analyzer, &arg_v, transformer, arg_t); + if (new_item && *arg != new_item) + current_thd->change_item_tree(arg, new_item); + } + } + return (this->*transformer)(arg_t); +} + /* See comments in Item_cmp_func::split_sum_func() */ void Item_func::split_sum_func(THD *thd, Item **ref_pointer_array, diff --git a/sql/item_func.h b/sql/item_func.h index 6a4d3376e70..df9f533a2c3 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -187,6 +187,8 @@ public: } bool walk(Item_processor processor, bool walk_subquery, byte *arg); Item *transform(Item_transformer transformer, byte *arg); + Item* compile(Item_analyzer analyzer, byte **arg_p, + Item_transformer transformer, byte *arg_t); void traverse_cond(Cond_traverser traverser, void * arg, traverse_order order); bool is_expensive_processor(byte *arg); diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 9ee3c3e2e03..f232aca3811 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -427,6 +427,7 @@ void view_store_options(THD *thd, st_table_list *table, String *buff); #define TL_OPTION_UPDATING 1 #define TL_OPTION_FORCE_INDEX 2 #define TL_OPTION_IGNORE_LEAVES 4 +#define TL_OPTION_ALIAS 8 /* Some portable defines */ diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 2f8fda3122d..13ffed4293e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -8139,7 +8139,6 @@ end: DBUG_RETURN(result); } - /* Get the next record with a different prefix. @@ -10791,7 +10790,6 @@ static void print_ror_scans_arr(TABLE *table, const char *msg, DBUG_VOID_RETURN; } - /***************************************************************************** ** Print a quick range for debugging ** TODO: diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 93ec48d69f2..7d6305594fd 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -234,7 +234,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Type of range for the key part for this field will be returned in range_fl. */ - if ((outer_tables & table->map) || + if (table->file->inited || (outer_tables & table->map) || !find_key_for_maxmin(0, &ref, item_field->field, conds, &range_fl, &prefix_len)) { @@ -321,7 +321,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Type of range for the key part for this field will be returned in range_fl. */ - if ((outer_tables & table->map) || + if (table->file->inited || (outer_tables & table->map) || !find_key_for_maxmin(1, &ref, item_field->field, conds, &range_fl, &prefix_len)) { diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 4c91ba6d8ff..ca006faf6e7 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -375,6 +375,7 @@ cleanup: */ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) { + Item *fake_conds= 0; SELECT_LEX *select_lex= &thd->lex->select_lex; DBUG_ENTER("mysql_prepare_delete"); @@ -400,7 +401,7 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) DBUG_RETURN(TRUE); } } - select_lex->fix_prepare_information(thd, conds); + select_lex->fix_prepare_information(thd, conds, &fake_conds); DBUG_RETURN(FALSE); } diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index ce237899b3a..1e7349456e4 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -975,7 +975,7 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, update_non_unique_table_error(table_list, "INSERT", duplicate); DBUG_RETURN(TRUE); } - select_lex->fix_prepare_information(thd, &fake_conds); + select_lex->fix_prepare_information(thd, &fake_conds, &fake_conds); select_lex->first_execution= 0; } if (duplic == DUP_UPDATE || duplic == DUP_REPLACE) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 0028c41c4a2..3f3fde03bce 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2170,15 +2170,25 @@ static void fix_prepare_info_in_table_list(THD *thd, TABLE_LIST *tbl) /* - fix some structures at the end of preparation + Save WHERE/HAVING/ON clauses and replace them with disposable copies SYNOPSIS st_select_lex::fix_prepare_information - thd thread handler - conds pointer on conditions which will be used for execution statement + thd thread handler + conds in/out pointer to WHERE condition to be met at execution + having_conds in/out pointer to HAVING condition to be met at execution + + DESCRIPTION + The passed WHERE and HAVING are to be saved for the future executions. + This function saves it, and returns a copy which can be thrashed during + this execution of the statement. By saving/thrashing here we mean only + AND/OR trees. + The function also calls fix_prepare_info_in_table_list that saves all + ON expressions. */ -void st_select_lex::fix_prepare_information(THD *thd, Item **conds) +void st_select_lex::fix_prepare_information(THD *thd, Item **conds, + Item **having_conds) { if (!thd->stmt_arena->is_conventional() && first_execution) { @@ -2188,6 +2198,11 @@ void st_select_lex::fix_prepare_information(THD *thd, Item **conds) prep_where= *conds; *conds= where= prep_where->copy_andor_structure(thd); } + if (*having_conds) + { + prep_having= *having_conds; + *having_conds= having= prep_having->copy_andor_structure(thd); + } fix_prepare_info_in_table_list(thd, (TABLE_LIST *)table_list.first); } } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f92d10758b9..a2806a03369 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -682,7 +682,7 @@ public: void print(THD *thd, String *str); static void print_order(String *str, ORDER *order); void print_limit(THD *thd, String *str); - void fix_prepare_information(THD *thd, Item **conds); + void fix_prepare_information(THD *thd, Item **conds, Item **having_conds); /* Destroy the used execution plan (JOIN) of this subtree (this SELECT_LEX and all nested SELECT_LEXes and SELECT_LEX_UNITs). diff --git a/sql/sql_list.h b/sql/sql_list.h index 05f589a2c23..070fd1e3d9a 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -94,9 +94,9 @@ public: inline base_list() { empty(); } inline base_list(const base_list &tmp) :Sql_alloc() { - elements=tmp.elements; - first=tmp.first; - last=tmp.last; + elements= tmp.elements; + first= tmp.first; + last= elements ? tmp.last : &first; } inline base_list(bool error) { } inline bool push_back(void *info) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 96f2092bc25..94594f9c0a7 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -6274,6 +6274,7 @@ bool add_to_list(THD *thd, SQL_LIST &list,Item *item,bool asc) table_options A set of the following bits: TL_OPTION_UPDATING Table will be updated TL_OPTION_FORCE_INDEX Force usage of index + TL_OPTION_ALIAS an alias in multi table DELETE lock_type How table should be locked use_index List of indexed used in USE INDEX ignore_index List of indexed used in IGNORE INDEX @@ -6302,7 +6303,8 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, if (!table) DBUG_RETURN(0); // End of memory alias_str= alias ? alias->str : table->table.str; - if (check_table_name(table->table.str, table->table.length)) + if (!test(table_options & TL_OPTION_ALIAS) && + check_table_name(table->table.str, table->table.length)) { my_error(ER_WRONG_TABLE_NAME, MYF(0), table->table.str); DBUG_RETURN(0); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a280b290019..8b8bf9e38d8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -382,12 +382,14 @@ JOIN::prepare(Item ***rref_pointer_array, if ((res= subselect->select_transformer(this)) != Item_subselect::RES_OK) { - select_lex->fix_prepare_information(thd, &conds); + select_lex->fix_prepare_information(thd, &conds, &having); DBUG_RETURN((res == Item_subselect::RES_ERROR)); } } } + select_lex->fix_prepare_information(thd, &conds, &having); + if (having && having->with_sum_func) having->split_sum_func2(thd, ref_pointer_array, all_fields, &having, TRUE); @@ -492,7 +494,6 @@ JOIN::prepare(Item ***rref_pointer_array, if (alloc_func_list()) goto err; - select_lex->fix_prepare_information(thd, &conds); DBUG_RETURN(0); // All OK err: @@ -608,7 +609,6 @@ JOIN::optimize() build_bitmap_for_nested_joins(join_list, 0); sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0; - sel->prep_having= having ? having->copy_andor_structure(thd) : 0; if (arena) thd->restore_active_arena(arena, &backup); @@ -1327,7 +1327,7 @@ JOIN::exec() } (void) result->prepare2(); // Currently, this cannot fail. - if (!tables_list) + if (!tables_list && (tables || !select_lex->with_sum_func)) { // Only test of functions if (select_options & SELECT_DESCRIBE) select_describe(this, FALSE, FALSE, FALSE, @@ -1367,7 +1367,12 @@ JOIN::exec() thd->examined_row_count= 0; DBUG_VOID_RETURN; } - thd->limit_found_rows= thd->examined_row_count= 0; + /* + don't reset the found rows count if there're no tables + as FOUND_ROWS() may be called. + */ + if (tables) + thd->limit_found_rows= thd->examined_row_count= 0; if (zero_result_cause) { @@ -1406,7 +1411,8 @@ JOIN::exec() having= tmp_having; select_describe(this, need_tmp, order != 0 && !skip_sort_order, - select_distinct); + select_distinct, + !tables ? "No tables used" : NullS); DBUG_VOID_RETURN; } @@ -6409,29 +6415,30 @@ finish: /* - Check whether an item is a simple equality predicate and if so - create/find a multiple equality for this predicate + Check whether an equality can be used to build multiple equalities SYNOPSIS - check_equality() - item item to check - cond_equal multiple equalities that must hold together with the predicate + check_simple_equality() + left_item left term of the quality to be checked + right_item right term of the equality to be checked + item equality item if the equality originates from a condition + predicate, 0 if the equality is the result of row elimination + cond_equal multiple equalities that must hold together with the equality DESCRIPTION - This function first checks whether an item is a simple equality i.e. - the one that equates a field with another field or a constant - (item=constant_item or item=field_item). - If this is the case the function looks a for a multiple equality + This function first checks whether the equality (left_item=right_item) + is a simple equality i.e. the one that equates a field with another field + or a constant (field=field_item or field=const_item). + If this is the case the function looks for a multiple equality in the lists referenced directly or indirectly by cond_equal inferring the given simple equality. If it doesn't find any, it builds a multiple equality that covers the predicate, i.e. the predicate can be inferred - from it. + from this multiple equality. The built multiple equality could be obtained in such a way: create a binary multiple equality equivalent to the predicate, then merge it, if possible, with one of old multiple equalities. This guarantees that the set of multiple equalities covering equality - predicates will - be minimal. + predicates will be minimal. EXAMPLE For the where condition @@ -6449,7 +6456,7 @@ finish: and will transform *cond_equal into (ptr(CE),[Item_equal(f,e)]). NOTES - Now only fields that have the same type defintions (verified by + Now only fields that have the same type definitions (verified by the Field::eq_def method) are placed to the same multiple equalities. Because of this some equality predicates are not eliminated and can be used in the constant propagation procedure. @@ -6482,177 +6489,289 @@ finish: copying would be much more complicated. RETURN - TRUE - if the predicate is a simple equality predicate - FALSE - otherwise + TRUE if the predicate is a simple equality predicate to be used + for building multiple equalities + FALSE otherwise */ -static bool check_equality(Item *item, COND_EQUAL *cond_equal) +static bool check_simple_equality(Item *left_item, Item *right_item, + Item *item, COND_EQUAL *cond_equal) { - if (item->type() == Item::FUNC_ITEM && - ((Item_func*) item)->functype() == Item_func::EQ_FUNC) + if (left_item->type() == Item::REF_ITEM && + ((Item_ref*)left_item)->ref_type() == Item_ref::VIEW_REF) { - Item *left_item= ((Item_func*) item)->arguments()[0]; - Item *right_item= ((Item_func*) item)->arguments()[1]; + if (((Item_ref*)left_item)->depended_from) + return FALSE; + left_item= left_item->real_item(); + } + if (right_item->type() == Item::REF_ITEM && + ((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF) + { + if (((Item_ref*)right_item)->depended_from) + return FALSE; + right_item= right_item->real_item(); + } + if (left_item->type() == Item::FIELD_ITEM && + right_item->type() == Item::FIELD_ITEM && + !((Item_field*)left_item)->depended_from && + !((Item_field*)right_item)->depended_from) + { + /* The predicate the form field1=field2 is processed */ + + Field *left_field= ((Item_field*) left_item)->field; + Field *right_field= ((Item_field*) right_item)->field; + + if (!left_field->eq_def(right_field)) + return FALSE; + + /* Search for multiple equalities containing field1 and/or field2 */ + bool left_copyfl, right_copyfl; + Item_equal *left_item_equal= + find_item_equal(cond_equal, left_field, &left_copyfl); + Item_equal *right_item_equal= + find_item_equal(cond_equal, right_field, &right_copyfl); - if (left_item->type() == Item::REF_ITEM && - ((Item_ref*)left_item)->ref_type() == Item_ref::VIEW_REF) + /* As (NULL=NULL) != TRUE we can't just remove the predicate f=f */ + if (left_field->eq(right_field)) /* f = f */ + return (!(left_field->maybe_null() && !left_item_equal)); + + if (left_item_equal && left_item_equal == right_item_equal) { - if (((Item_ref*)left_item)->depended_from) - return FALSE; - left_item= left_item->real_item(); + /* + The equality predicate is inference of one of the existing + multiple equalities, i.e the condition is already covered + by upper level equalities + */ + return TRUE; } - if (right_item->type() == Item::REF_ITEM && - ((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF) + + /* Copy the found multiple equalities at the current level if needed */ + if (left_copyfl) { - if (((Item_ref*)right_item)->depended_from) - return FALSE; - right_item= right_item->real_item(); + /* left_item_equal of an upper level contains left_item */ + left_item_equal= new Item_equal(left_item_equal); + cond_equal->current_level.push_back(left_item_equal); } - if (left_item->type() == Item::FIELD_ITEM && - right_item->type() == Item::FIELD_ITEM && - !((Item_field*)left_item)->depended_from && - !((Item_field*)right_item)->depended_from) + if (right_copyfl) { - /* The predicate the form field1=field2 is processed */ + /* right_item_equal of an upper level contains right_item */ + right_item_equal= new Item_equal(right_item_equal); + cond_equal->current_level.push_back(right_item_equal); + } - Field *left_field= ((Item_field*) left_item)->field; - Field *right_field= ((Item_field*) right_item)->field; + if (left_item_equal) + { + /* left item was found in the current or one of the upper levels */ + if (! right_item_equal) + left_item_equal->add((Item_field *) right_item); + else + { + /* Merge two multiple equalities forming a new one */ + left_item_equal->merge(right_item_equal); + /* Remove the merged multiple equality from the list */ + List_iterator<Item_equal> li(cond_equal->current_level); + while ((li++) != right_item_equal); + li.remove(); + } + } + else + { + /* left item was not found neither the current nor in upper levels */ + if (right_item_equal) + right_item_equal->add((Item_field *) left_item); + else + { + /* None of the fields was found in multiple equalities */ + Item_equal *item= new Item_equal((Item_field *) left_item, + (Item_field *) right_item); + cond_equal->current_level.push_back(item); + } + } + return TRUE; + } - if (!left_field->eq_def(right_field)) - return FALSE; + { + /* The predicate of the form field=const/const=field is processed */ + Item *const_item= 0; + Item_field *field_item= 0; + if (left_item->type() == Item::FIELD_ITEM && + !((Item_field*)left_item)->depended_from && + right_item->const_item()) + { + field_item= (Item_field*) left_item; + const_item= right_item; + } + else if (right_item->type() == Item::FIELD_ITEM && + !((Item_field*)right_item)->depended_from && + left_item->const_item()) + { + field_item= (Item_field*) right_item; + const_item= left_item; + } - if (left_field->eq(right_field)) /* f = f */ - return TRUE; - - /* Search for multiple equalities containing field1 and/or field2 */ - bool left_copyfl, right_copyfl; - Item_equal *left_item_equal= - find_item_equal(cond_equal, left_field, &left_copyfl); - Item_equal *right_item_equal= - find_item_equal(cond_equal, right_field, &right_copyfl); + if (const_item && + field_item->result_type() == const_item->result_type()) + { + bool copyfl; - if (left_item_equal && left_item_equal == right_item_equal) + if (field_item->result_type() == STRING_RESULT) { - /* - The equality predicate is inference of one of the existing - multiple equalities, i.e the condition is already covered - by upper level equalities - */ - return TRUE; + CHARSET_INFO *cs= ((Field_str*) field_item->field)->charset(); + if (!item) + { + Item_func_eq *eq_item; + if ((eq_item= new Item_func_eq(left_item, right_item))) + return FALSE; + eq_item->set_cmp_func(); + eq_item->quick_fix_field(); + item= eq_item; + } + if ((cs != ((Item_func *) item)->compare_collation()) || + !cs->coll->propagate(cs, 0, 0)) + return FALSE; } - - /* Copy the found multiple equalities at the current level if needed */ - if (left_copyfl) + + Item_equal *item_equal = find_item_equal(cond_equal, + field_item->field, ©fl); + if (copyfl) { - /* left_item_equal of an upper level contains left_item */ - left_item_equal= new Item_equal(left_item_equal); - cond_equal->current_level.push_back(left_item_equal); + item_equal= new Item_equal(item_equal); + cond_equal->current_level.push_back(item_equal); } - if (right_copyfl) + if (item_equal) { - /* right_item_equal of an upper level contains right_item */ - right_item_equal= new Item_equal(right_item_equal); - cond_equal->current_level.push_back(right_item_equal); - } - - if (left_item_equal) - { - /* left item was found in the current or one of the upper levels */ - if (! right_item_equal) - left_item_equal->add((Item_field *) right_item); - else - { - /* Merge two multiple equalities forming a new one */ - left_item_equal->merge(right_item_equal); - /* Remove the merged multiple equality from the list */ - List_iterator<Item_equal> li(cond_equal->current_level); - while ((li++) != right_item_equal); - li.remove(); - } + /* + The flag cond_false will be set to 1 after this, if item_equal + already contains a constant and its value is not equal to + the value of const_item. + */ + item_equal->add(const_item); } else - { - /* left item was not found neither the current nor in upper levels */ - if (right_item_equal) - right_item_equal->add((Item_field *) left_item); - else - { - /* None of the fields was found in multiple equalities */ - Item_equal *item= new Item_equal((Item_field *) left_item, - (Item_field *) right_item); - cond_equal->current_level.push_back(item); - } + { + item_equal= new Item_equal(const_item, field_item); + cond_equal->current_level.push_back(item_equal); } return TRUE; } + } + return FALSE; +} - { - /* The predicate of the form field=const/const=field is processed */ - Item *const_item= 0; - Item_field *field_item= 0; - if (left_item->type() == Item::FIELD_ITEM && - !((Item_field*)left_item)->depended_from && - right_item->const_item()) - { - field_item= (Item_field*) left_item; - const_item= right_item; - } - else if (right_item->type() == Item::FIELD_ITEM && - !((Item_field*)right_item)->depended_from && - left_item->const_item()) - { - field_item= (Item_field*) right_item; - const_item= left_item; - } - if (const_item && - field_item->result_type() == const_item->result_type()) - { - bool copyfl; +/* + Convert row equalities into a conjunction of regular equalities - if (field_item->result_type() == STRING_RESULT) - { - CHARSET_INFO *cs= ((Field_str*) field_item->field)->charset(); - if ((cs != ((Item_cond *) item)->compare_collation()) || - !cs->coll->propagate(cs, 0, 0)) - return FALSE; - } + SYNOPSIS + check_row_equality() + left_row left term of the row equality to be processed + right_row right term of the row equality to be processed + cond_equal multiple equalities that must hold together with the predicate + eq_list results of conversions of row equalities that are not simple + enough to form multiple equalities - Item_equal *item_equal = find_item_equal(cond_equal, - field_item->field, ©fl); - if (copyfl) - { - item_equal= new Item_equal(item_equal); - cond_equal->current_level.push_back(item_equal); - } - if (item_equal) - { - /* - The flag cond_false will be set to 1 after this, if item_equal - already contains a constant and its value is not equal to - the value of const_item. - */ - item_equal->add(const_item); - } - else - { - item_equal= new Item_equal(const_item, field_item); - cond_equal->current_level.push_back(item_equal); - } - return TRUE; - } + DESCRIPTION + The function converts a row equality of the form (E1,...,En)=(E'1,...,E'n) + into a list of equalities E1=E'1,...,En=E'n. For each of these equalities + Ei=E'i the function checks whether it is a simple equality or a row equality. + If it is a simple equality it is used to expand multiple equalities of + cond_equal. If it is a row equality it converted to a sequence of equalities + between row elements. If Ei=E'i is neither a simple equality nor a row + equality the item for this predicate is added to eq_list. + + RETURN + TRUE if conversion has succeeded (no fatal error) + FALSE otherwise +*/ + +static bool check_row_equality(Item *left_row, Item_row *right_row, + COND_EQUAL *cond_equal, List<Item>* eq_list) +{ + uint n= left_row->cols(); + for (uint i= 0 ; i < n; i++) + { + bool is_converted; + Item *left_item= left_row->el(i); + Item *right_item= right_row->el(i); + if (left_item->type() == Item::ROW_ITEM && + right_item->type() == Item::ROW_ITEM) + is_converted= check_row_equality((Item_row *) left_item, + (Item_row *) right_item, + cond_equal, eq_list); + else + is_converted= check_simple_equality(left_item, right_item, 0, cond_equal); + + if (!is_converted) + { + Item_func_eq *eq_item; + if (!(eq_item= new Item_func_eq(left_item, right_item))) + return FALSE; + eq_item->set_cmp_func(); + eq_item->quick_fix_field(); + eq_list->push_back(eq_item); } } + return TRUE; +} + + +/* + Eliminate row equalities and form multiple equalities predicates + + SYNOPSIS + check_equality() + item predicate to process + cond_equal multiple equalities that must hold together with the predicate + eq_list results of conversions of row equalities that are not simple + enough to form multiple equalities + + DESCRIPTION + This function checks whether the item is a simple equality + i.e. the one that equates a field with another field or a constant + (field=field_item or field=constant_item), or, a row equality. + For a simple equality the function looks for a multiple equality + in the lists referenced directly or indirectly by cond_equal inferring + the given simple equality. If it doesn't find any, it builds/expands + multiple equality that covers the predicate. + Row equalities are eliminated substituted for conjunctive regular equalities + which are treated in the same way as original equality predicates. + + RETURN + TRUE if re-writing rules have been applied + FALSE otherwise, i.e. + if the predicate is not an equality, + or, if the equality is neither a simple one nor a row equality, + or, if the procedure fails by a fatal error. +*/ + +static bool check_equality(Item *item, COND_EQUAL *cond_equal, + List<Item> *eq_list) +{ + if (item->type() == Item::FUNC_ITEM && + ((Item_func*) item)->functype() == Item_func::EQ_FUNC) + { + Item *left_item= ((Item_func*) item)->arguments()[0]; + Item *right_item= ((Item_func*) item)->arguments()[1]; + + if (left_item->type() == Item::ROW_ITEM && + right_item->type() == Item::ROW_ITEM) + return check_row_equality((Item_row *) left_item, + (Item_row *) right_item, + cond_equal, eq_list); + else + return check_simple_equality(left_item, right_item, item, cond_equal); + } return FALSE; } + /* Replace all equality predicates in a condition by multiple equality items SYNOPSIS build_equal_items_for_cond() - cond condition(expression) where to make replacement - inherited path to all inherited multiple equality items + cond condition(expression) where to make replacement + inherited path to all inherited multiple equality items DESCRIPTION At each 'and' level the function detects items for equality predicates @@ -6666,7 +6785,9 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) The function also traverses the cond tree and and for each field reference sets a pointer to the multiple equality item containing the field, if there is any. If this multiple equality equates fields to a constant the - function replace the field reference by the constant. + function replaces the field reference by the constant in the cases + when the field is not of a string type or when the field reference is + just an argument of a comparison predicate. The function also determines the maximum number of members in equality lists of each Item_cond_and object assigning it to cond_equal->max_members of this object and updating accordingly @@ -6717,10 +6838,12 @@ static COND *build_equal_items_for_cond(COND *cond, Item_equal *item_equal; uint members; COND_EQUAL cond_equal; + COND *new_cond; cond_equal.upper_levels= inherited; if (cond->type() == Item::COND_ITEM) { + List<Item> eq_list; bool and_level= ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC; List<Item> *args= ((Item_cond*) cond)->argument_list(); @@ -6743,7 +6866,7 @@ static COND *build_equal_items_for_cond(COND *cond, structure here because it's restored before each re-execution of any prepared statement/stored procedure. */ - if (check_equality(item, &cond_equal)) + if (check_equality(item, &cond_equal, &eq_list)) li.remove(); } @@ -6790,10 +6913,14 @@ static COND *build_equal_items_for_cond(COND *cond, } } if (and_level) + { + args->concat(&eq_list); args->concat((List<Item> *)&cond_equal.current_level); + } } else if (cond->type() == Item::FUNC_ITEM) { + List<Item> eq_list; /* If an equality predicate forms the whole and level, we call it standalone equality and it's processed here. @@ -6804,19 +6931,57 @@ static COND *build_equal_items_for_cond(COND *cond, for WHERE a=b AND c=d AND (b=c OR d=5) b=c is replaced by =(a,b,c,d). */ - if (check_equality(cond, &cond_equal) && - (item_equal= cond_equal.current_level.pop())) + if (check_equality(cond, &cond_equal, &eq_list)) { - item_equal->fix_length_and_dec(); - item_equal->update_used_tables(); - return item_equal; + int n= cond_equal.current_level.elements + eq_list.elements; + if (n == 0) + return new Item_int((longlong) 1,1); + else if (n == 1) + { + if ((item_equal= cond_equal.current_level.pop())) + { + item_equal->fix_length_and_dec(); + item_equal->update_used_tables(); + return item_equal; + } + else + return eq_list.pop(); + } + else + { + /* + Here a new AND level must be created. It can happen only + when a row equality is processed as a standalone predicate. + */ + Item_cond_and *and_cond= new Item_cond_and(eq_list); + and_cond->quick_fix_field(); + List<Item> *args= and_cond->argument_list(); + List_iterator_fast<Item_equal> it(cond_equal.current_level); + while ((item_equal= it++)) + { + item_equal->fix_length_and_dec(); + item_equal->update_used_tables(); + members= item_equal->members(); + if (cond_equal.max_members < members) + cond_equal.max_members= members; + } + and_cond->cond_equal= cond_equal; + args->concat((List<Item> *)&cond_equal.current_level); + + return and_cond; + } } /* For each field reference in cond, not from equal item predicates, set a pointer to the multiple equality it belongs to (if there is any) + as soon the field is not of a string type or the field reference is + an argument of a comparison predicate. */ - cond= cond->transform(&Item::equal_fields_propagator, - (byte *) inherited); + byte *is_subst_valid= (byte *) 1; + cond= cond->compile(&Item::subst_argument_checker, + &is_subst_valid, + &Item::equal_fields_propagator, + (byte *) inherited); cond->update_used_tables(); } return cond; @@ -7096,7 +7261,7 @@ static Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, /* Substitute every field reference in a condition by the best equal field - and eliminate all multiplle equality predicates + and eliminate all multiple equality predicates SYNOPSIS substitute_for_best_equal_field() @@ -9049,10 +9214,11 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, keyinfo->rec_per_key=0; keyinfo->algorithm= HA_KEY_ALG_UNDEF; keyinfo->name= (char*) "group_key"; - for (; group ; group=group->next,key_part_info++) + ORDER *cur_group= group; + for (; cur_group ; cur_group= cur_group->next, key_part_info++) { - Field *field=(*group->item)->get_tmp_table_field(); - bool maybe_null=(*group->item)->maybe_null; + Field *field=(*cur_group->item)->get_tmp_table_field(); + bool maybe_null=(*cur_group->item)->maybe_null; key_part_info->null_bit=0; key_part_info->field= field; key_part_info->offset= field->offset(); @@ -9065,8 +9231,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, 0 : FIELDFLAG_BINARY; if (!using_unique_constraint) { - group->buff=(char*) group_buff; - if (!(group->field= field->new_key_field(thd->mem_root,table, + cur_group->buff=(char*) group_buff; + if (!(cur_group->field= field->new_key_field(thd->mem_root,table, (char*) group_buff + test(maybe_null), field->null_ptr, @@ -9084,12 +9250,12 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, key_part_info->null_bit=field->null_bit; key_part_info->null_offset= (uint) (field->null_ptr - (uchar*) table->record[0]); - group->buff++; // Pointer to field data + cur_group->buff++; // Pointer to field data group_buff++; // Skipp null flag } /* In GROUP BY 'a' and 'a ' are equal for VARCHAR fields */ key_part_info->key_part_flag|= HA_END_SPACE_ARE_EQUAL; - group_buff+= group->field->pack_length(); + group_buff+= cur_group->field->pack_length(); } keyinfo->key_length+= key_part_info->length; } @@ -9700,9 +9866,13 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) table->file->ha_index_init(0, 0); } /* Set up select_end */ - join->join_tab[join->tables-1].next_select= setup_end_select_func(join); + Next_select_func end_select= setup_end_select_func(join); + if (join->tables) + { + join->join_tab[join->tables-1].next_select= end_select; - join_tab=join->join_tab+join->const_tables; + join_tab=join->join_tab+join->const_tables; + } join->send_records=0; if (join->tables == join->const_tables) { @@ -9712,7 +9882,6 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) */ if (!join->conds || join->conds->val_int()) { - Next_select_func end_select= join->join_tab[join->tables-1].next_select; error= (*end_select)(join,join_tab,0); if (error == NESTED_LOOP_OK || error == NESTED_LOOP_QUERY_LIMIT) error= (*end_select)(join,join_tab,1); @@ -9726,6 +9895,8 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) } else { + DBUG_ASSERT(join->tables); + DBUG_ASSERT(join_tab); error= sub_select(join,join_tab,0); if (error == NESTED_LOOP_OK || error == NESTED_LOOP_NO_MORE_ROWS) error= sub_select(join,join_tab,1); @@ -14371,9 +14542,12 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, item_list.push_back(new Item_string(table_name_buffer, len, cs)); } else - item_list.push_back(new Item_string(table->alias, - strlen(table->alias), + { + TABLE_LIST *tab=table->pos_in_table_list; + item_list.push_back(new Item_string(tab->alias, + strlen(tab->alias), cs)); + } /* "partitions" column */ if (join->thd->lex->describe & DESCRIBE_PARTITIONS) { @@ -14602,8 +14776,8 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only uint8 uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN); sl->type= (((&thd->lex->select_lex)==sl)? - ((thd->lex->all_selects_list != sl) ? - primary_key_name : "SIMPLE"): + (sl->first_inner_unit() || sl->next_select() ? + "PRIMARY" : "SIMPLE"): ((sl == first)? ((sl->linkage == DERIVED_TABLE_TYPE) ? "DERIVED": diff --git a/sql/sql_update.cc b/sql/sql_update.cc index b62e80edfab..3130220515a 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -725,6 +725,7 @@ err: bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list, Item **conds, uint order_num, ORDER *order) { + Item *fake_conds= 0; TABLE *table= table_list->table; TABLE_LIST tables; List<Item> all_fields; @@ -764,7 +765,7 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list, DBUG_RETURN(TRUE); } } - select_lex->fix_prepare_information(thd, conds); + select_lex->fix_prepare_information(thd, conds, &fake_conds); DBUG_RETURN(FALSE); } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d4e0c5ed907..4aab923d8c0 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -797,8 +797,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); predicate bit_expr bit_term bit_factor value_expr term factor table_wild simple_expr udf_expr expr_or_default set_expr_or_default interval_expr - param_marker singlerow_subselect singlerow_subselect_init - exists_subselect exists_subselect_init geometry_function + param_marker geometry_function signed_literal now_or_signed_literal opt_escape sp_opt_default simple_ident_nospvar simple_ident_q @@ -864,7 +863,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %type <variable> internal_variable_name -%type <select_lex> in_subselect in_subselect_init +%type <select_lex> subselect subselect_init get_select_lex %type <boolfunc2creator> comp_op @@ -5564,12 +5563,14 @@ select_paren: yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } - if (sel->linkage == UNION_TYPE && - !sel->master_unit()->first_select()->braces) - { - yyerror(ER(ER_SYNTAX_ERROR)); - YYABORT; - } + if (sel->linkage == UNION_TYPE && + !sel->master_unit()->first_select()->braces && + sel->master_unit()->first_select()->linkage == + UNION_TYPE) + { + yyerror(ER(ER_SYNTAX_ERROR)); + YYABORT; + } /* select in braces, can't contain global parameters */ if (sel->master_unit()->fake_select_lex) sel->master_unit()->global_parameters= @@ -5827,37 +5828,37 @@ bool_pri: | bool_pri EQUAL_SYM predicate { $$= new Item_func_equal($1,$3); } | bool_pri comp_op predicate %prec EQ { $$= (*$2)(0)->create($1,$3); } - | bool_pri comp_op all_or_any in_subselect %prec EQ - { $$= all_any_subquery_creator($1, $2, $3, $4); } + | bool_pri comp_op all_or_any '(' subselect ')' %prec EQ + { $$= all_any_subquery_creator($1, $2, $3, $5); } | predicate ; predicate: - bit_expr IN_SYM '(' expr_list ')' + bit_expr IN_SYM '(' subselect ')' + { $$= new Item_in_subselect($1, $4); } + | bit_expr not IN_SYM '(' subselect ')' + { $$= negate_expression(YYTHD, new Item_in_subselect($1, $5)); } + | bit_expr IN_SYM '(' expr ')' + { + $$= new Item_func_eq($1, $4); + } + | bit_expr IN_SYM '(' expr ',' expr_list ')' { - if ($4->elements == 1) - $$= new Item_func_eq($1, $4->head()); - else - { - $4->push_front($1); - $$= new Item_func_in(*$4); - } + $6->push_front($4); + $6->push_front($1); + $$= new Item_func_in(*$6); } - | bit_expr not IN_SYM '(' expr_list ')' + | bit_expr not IN_SYM '(' expr ')' { - if ($5->elements == 1) - $$= new Item_func_ne($1, $5->head()); - else - { - $5->push_front($1); - Item_func_in *item = new Item_func_in(*$5); + $$= new Item_func_ne($1, $5); + } + | bit_expr not IN_SYM '(' expr ',' expr_list ')' + { + $7->push_front($5); + $7->push_front($1); + Item_func_in *item = new Item_func_in(*$7); item->negate(); $$= item; - } } - | bit_expr IN_SYM in_subselect - { $$= new Item_in_subselect($1, $3); } - | bit_expr not IN_SYM in_subselect - { $$= negate_expression(YYTHD, new Item_in_subselect($1, $4)); } | 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 @@ -5979,6 +5980,10 @@ simple_expr: | '-' simple_expr %prec NEG { $$= new Item_func_neg($2); } | '~' simple_expr %prec NEG { $$= new Item_func_bit_neg($2); } | not2 simple_expr %prec NEG { $$= negate_expression(YYTHD, $2); } + | '(' subselect ')' + { + $$= new Item_singlerow_subselect($2); + } | '(' expr ')' { $$= $2; } | '(' expr ',' expr_list ')' { @@ -5990,8 +5995,10 @@ simple_expr: $5->push_front($3); $$= new Item_row(*$5); } - | EXISTS exists_subselect { $$= $2; } - | singlerow_subselect { $$= $1; } + | EXISTS '(' subselect ')' + { + $$= new Item_exists_subselect($3); + } | '{' ident expr '}' { $$= $3; } | MATCH ident_list_arg AGAINST '(' bit_expr fulltext_options ')' { $2->push_front($5); @@ -8022,14 +8029,17 @@ table_wild_one: ident opt_wild opt_table_alias { if (!Select->add_table_to_list(YYTHD, new Table_ident($1), $3, - TL_OPTION_UPDATING, Lex->lock_option)) + TL_OPTION_UPDATING | + TL_OPTION_ALIAS, Lex->lock_option)) YYABORT; } | ident '.' ident opt_wild opt_table_alias { if (!Select->add_table_to_list(YYTHD, new Table_ident(YYTHD, $1, $3, 0), - $5, TL_OPTION_UPDATING, + $5, + TL_OPTION_UPDATING | + TL_OPTION_ALIAS, Lex->lock_option)) YYABORT; } @@ -9297,6 +9307,7 @@ keyword: | UNICODE_SYM {} | UNINSTALL_SYM {} | XA_SYM {} + | UPGRADE_SYM {} ; /* @@ -10657,49 +10668,38 @@ union_option: | ALL { $$=0; } ; -singlerow_subselect: - subselect_start singlerow_subselect_init - subselect_end - { - $$= $2; - }; - -singlerow_subselect_init: - select_init2 - { - $$= new Item_singlerow_subselect(Lex->current_select-> - master_unit()->first_select()); - }; - -exists_subselect: - subselect_start exists_subselect_init - subselect_end - { - $$= $2; - }; - -exists_subselect_init: - select_init2 - { - $$= new Item_exists_subselect(Lex->current_select->master_unit()-> - first_select()); - }; - -in_subselect: - subselect_start in_subselect_init - subselect_end - { - $$= $2; - }; +subselect: + SELECT_SYM subselect_start subselect_init subselect_end + { + $$= $3; + } + | '(' subselect_start subselect ')' + { + LEX *lex= Lex; + THD *thd= YYTHD; + /* + note that a local variable can't be used for + $3 as it's used in local variable construction + and some compilers can't guarnatee the order + in which the local variables are initialized. + */ + List_iterator<Item> it($3->item_list); + Item *item; + /* + we must fill the items list for the "derived table". + */ + while ((item= it++)) + add_item_to_list(thd, item); + } + union_clause subselect_end { $$= $3; }; -in_subselect_init: +subselect_init: select_init2 { $$= Lex->current_select->master_unit()->first_select(); }; subselect_start: - '(' SELECT_SYM { LEX *lex=Lex; if (!lex->expr_allows_subselect) @@ -10707,12 +10707,18 @@ subselect_start: yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } + /* + we are making a "derived table" for the parenthesis + as we need to have a lex level to fit the union + after the parenthesis, e.g. + (SELECT .. ) UNION ... becomes + SELECT * FROM ((SELECT ...) UNION ...) + */ if (mysql_new_select(Lex, 1)) YYABORT; }; subselect_end: - ')' { LEX *lex=Lex; lex->pop_context(); |