diff options
38 files changed, 1187 insertions, 216 deletions
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 41d15551eea..75c35daf2bf 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3012,6 +3012,7 @@ language = $path_language character-sets-dir = $path_charsetsdir basedir = $path_my_basedir server_id = $server_id +shutdown-delay = 10 skip-stack-trace skip-innodb skip-ndbcluster diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index eef380c8f52..c75e37fa0a4 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -128,3 +128,6 @@ f1 f2 if(f1, 40.0, 5.00) 0 0 5.00 1 1 40.00 drop table t1; +select if(0, 18446744073709551610, 18446744073709551610); +if(0, 18446744073709551610, 18446744073709551610) +18446744073709551610 diff --git a/mysql-test/r/im_daemon_life_cycle.result b/mysql-test/r/im_daemon_life_cycle.result index d80a34f8427..1a0741e7caf 100644 --- a/mysql-test/r/im_daemon_life_cycle.result +++ b/mysql-test/r/im_daemon_life_cycle.result @@ -21,6 +21,6 @@ Success: the process was restarted. Success: server is ready to accept connection on socket. SHOW INSTANCE STATUS mysqld1; instance_name status version -mysqld1 online VERSION +mysqld1 STATE VERSION STOP INSTANCE mysqld2; Success: the process has been stopped. diff --git a/mysql-test/r/init_file.result b/mysql-test/r/init_file.result index 1569f2c3d68..6394014f3e5 100644 --- a/mysql-test/r/init_file.result +++ b/mysql-test/r/init_file.result @@ -1,3 +1,10 @@ +INSERT INTO init_file.startup VALUES ( NOW() ); +SELECT * INTO @X FROM init_file.startup limit 0,1; +SELECT * INTO @Y FROM init_file.startup limit 1,1; +SELECT YEAR(@X)-YEAR(@Y); +YEAR(@X)-YEAR(@Y) +0 +DROP DATABASE init_file; ok end of 4.1 tests select * from t1; diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result index f362187cd14..6090dfdf737 100644 --- a/mysql-test/r/sp-vars.result +++ b/mysql-test/r/sp-vars.result @@ -431,17 +431,17 @@ SELECT HEX(v10); END| CALL p1(); HEX(v1) -01 +1 HEX(v2) -00 +0 HEX(v3) -05 +5 HEX(v4) 5555555555555555 HEX(v5) -07 +7 HEX(v6) -0000000000000005 +5 HEX(v7) 80 HEX(v8) @@ -748,12 +748,60 @@ HEX(b) b = 0 b = FALSE b IS FALSE b = 1 b = TRUE b IS TRUE 1 0 0 0 1 1 1 call p2(); HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE -00 1 1 1 0 0 0 +0 1 1 1 0 0 0 HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE -01 0 0 1 1 1 0 +1 0 0 0 1 1 1 DROP TABLE t1; DROP PROCEDURE p1; DROP PROCEDURE p2; +DROP TABLE IF EXISTS table_12976_a; +DROP TABLE IF EXISTS table_12976_b; +DROP PROCEDURE IF EXISTS proc_12976_a; +DROP PROCEDURE IF EXISTS proc_12976_b; +CREATE TABLE table_12976_a (val bit(1)); +CREATE TABLE table_12976_b( +appname varchar(15), +emailperm bit not null default 1, +phoneperm bit not null default 0); +insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0'); +CREATE PROCEDURE proc_12976_a() +BEGIN +declare localvar bit(1); +SELECT val INTO localvar FROM table_12976_a; +SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a; +END|| +CREATE PROCEDURE proc_12976_b( +name varchar(15), +out ep bit, +out msg varchar(10)) +BEGIN +SELECT emailperm into ep FROM table_12976_b where (appname = name); +IF ep is true THEN +SET msg = 'True'; +ELSE +SET msg = 'False'; +END IF; +END|| +INSERT table_12976_a VALUES (0); +call proc_12976_a(); +coalesce(localvar, 1)+1 coalesce(val, 1)+1 +1 1 +UPDATE table_12976_a set val=1; +call proc_12976_a(); +coalesce(localvar, 1)+1 coalesce(val, 1)+1 +2 2 +call proc_12976_b('A', @ep, @msg); +select @ep, @msg; +@ep @msg +1 True +call proc_12976_b('B', @ep, @msg); +select @ep, @msg; +@ep @msg +0 False +DROP TABLE table_12976_a; +DROP TABLE table_12976_b; +DROP PROCEDURE proc_12976_a; +DROP PROCEDURE proc_12976_b; --------------------------------------------------------------- BUG#9572 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 7177b4e4432..8ba52e81f35 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: -Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4` select * from t3 where exists (select * from t2 where t2.b=t3.a); a @@ -313,8 +313,8 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Warnings: -Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 -Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row @@ -330,9 +330,9 @@ patient_uq clinic_uq explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 Using where; Using index +2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index Warnings: -Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column 'a' in field list is ambiguous @@ -868,7 +868,7 @@ explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Warnings: -Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1` select (select a+1) from t1; @@ -1741,9 +1741,9 @@ Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `tes explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 Using where; Using index +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index Warnings: -Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); @@ -2279,7 +2279,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where Warnings: -Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)) drop table t1; CREATE TABLE t1 (t1_a int); @@ -3095,7 +3095,7 @@ SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; ERROR 21000: Subquery returns more than 1 row -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a @@ -3103,11 +3103,11 @@ a 4 1 3 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); ERROR 21000: Subquery returns more than 1 row -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a @@ -3115,7 +3115,7 @@ a 1 3 4 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); ERROR 21000: Subquery returns more than 1 row @@ -3683,16 +3683,16 @@ CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); CREATE TABLE t2 (c int); INSERT INTO t1 VALUES ('aa', 1); INSERT INTO t2 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 -UNION +UNION SELECT c from t2 WHERE c=t1.c); id c aa 1 INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 -UNION +UNION SELECT c from t2 WHERE c=t1.c); id c aa 1 @@ -3702,9 +3702,9 @@ dd 1 INSERT INTO t2 VALUES (2); CREATE TABLE t3 (c int); INSERT INTO t3 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 -UNION +UNION SELECT c from t2 WHERE c=t1.c); id c aa 1 @@ -3712,45 +3712,116 @@ bb 2 cc 3 dd 1 DROP TABLE t1,t2,t3; -CREATE TABLE t1(f1 int); -CREATE TABLE t2(f2 int, f21 int, f3 timestamp); -INSERT INTO t1 VALUES (1),(1),(2),(2); -INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11"); -SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1; -sq +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t1xt2; +CREATE TABLE t1 ( +id_1 int(5) NOT NULL, +t varchar(4) DEFAULT NULL +); +CREATE TABLE t2 ( +id_2 int(5) NOT NULL, +t varchar(4) DEFAULT NULL +); +CREATE TABLE t1xt2 ( +id_1 int(5) NOT NULL, +id_2 int(5) NOT NULL +); +INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); +INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa'); +INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4); +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +1 2 +3 4 -SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2; -tt +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))); +id_1 +1 2 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)))); +id_1 +1 2 -PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1'; -EXECUTE stmt1; -sq +3 +4 +insert INTO t1xt2 VALUES (1, 12); +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 2 +3 4 -EXECUTE stmt1; -sq +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 2 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +2 +3 +4 +insert INTO t1xt2 VALUES (2, 12); +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +1 +2 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +1 +2 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +1 +2 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +3 4 -DEALLOCATE PREPARE stmt1; -SELECT f2, AVG(f21), -(SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test -FROM t2 GROUP BY f2; -f2 AVG(f21) test -1 1.0000 2004-02-29 11:11:11 -2 2.0000 2004-02-29 11:11:11 -DROP TABLE t1,t2; -CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); -INSERT INTO t1 VALUES -(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), -(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), -(3,2,'k'), (3,1,'l'), (1,9,'m'); -SELECT a, MAX(b), -(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test -FROM t1 GROUP BY a; -a MAX(b) test -1 9 m -2 3 h -3 4 i DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t1xt2; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 50b41e1352f..34bcf37f091 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -834,14 +834,16 @@ show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` drop view v1; -create table tü (cü char); -create view vü as select cü from tü; -insert into vü values ('ü'); -select * from vü; -cü -ü -drop view vü; -drop table tü; +set names utf8; +create table tü (cü char); +create view vü as select cü from tü; +insert into vü values ('ü'); +select * from vü; +cü +ü +drop view vü; +drop table tü; +set names latin1; create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); create view v1(c) as select a+1 from t1 where b >= 4; @@ -3035,7 +3037,7 @@ View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` DROP VIEW v1; CREATE TABLE t1 (mydate DATETIME); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); CREATE VIEW v1 AS SELECT mydate from t1; SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; @@ -3135,4 +3137,139 @@ code COUNT(DISTINCT country) 100 2 DROP VIEW v1; DROP TABLE t1; +DROP VIEW IF EXISTS v1; +SELECT * FROM (SELECT 1) AS t; +1 +1 +CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t; +ERROR HY000: View's SELECT contains a subquery in the FROM clause +# Previously the following would fail. +SELECT * FROM (SELECT 1) AS t; +1 +1 +drop view if exists view_24532_a; +drop view if exists view_24532_b; +drop table if exists table_24532; +create table table_24532 ( +a int, +b bigint, +c int(4), +d bigint(48) +); +create view view_24532_a as +select +a IS TRUE, +a IS NOT TRUE, +a IS FALSE, +a IS NOT FALSE, +a IS UNKNOWN, +a IS NOT UNKNOWN, +a is NULL, +a IS NOT NULL, +ISNULL(a), +b IS TRUE, +b IS NOT TRUE, +b IS FALSE, +b IS NOT FALSE, +b IS UNKNOWN, +b IS NOT UNKNOWN, +b is NULL, +b IS NOT NULL, +ISNULL(b), +c IS TRUE, +c IS NOT TRUE, +c IS FALSE, +c IS NOT FALSE, +c IS UNKNOWN, +c IS NOT UNKNOWN, +c is NULL, +c IS NOT NULL, +ISNULL(c), +d IS TRUE, +d IS NOT TRUE, +d IS FALSE, +d IS NOT FALSE, +d IS UNKNOWN, +d IS NOT UNKNOWN, +d is NULL, +d IS NOT NULL, +ISNULL(d) +from table_24532; +describe view_24532_a; +Field Type Null Key Default Extra +a IS TRUE int(1) NO 0 +a IS NOT TRUE int(1) NO 0 +a IS FALSE int(1) NO 0 +a IS NOT FALSE int(1) NO 0 +a IS UNKNOWN int(1) NO 0 +a IS NOT UNKNOWN int(1) NO 0 +a is NULL int(1) NO 0 +a IS NOT NULL int(1) NO 0 +ISNULL(a) int(1) NO 0 +b IS TRUE int(1) NO 0 +b IS NOT TRUE int(1) NO 0 +b IS FALSE int(1) NO 0 +b IS NOT FALSE int(1) NO 0 +b IS UNKNOWN int(1) NO 0 +b IS NOT UNKNOWN int(1) NO 0 +b is NULL int(1) NO 0 +b IS NOT NULL int(1) NO 0 +ISNULL(b) int(1) NO 0 +c IS TRUE int(1) NO 0 +c IS NOT TRUE int(1) NO 0 +c IS FALSE int(1) NO 0 +c IS NOT FALSE int(1) NO 0 +c IS UNKNOWN int(1) NO 0 +c IS NOT UNKNOWN int(1) NO 0 +c is NULL int(1) NO 0 +c IS NOT NULL int(1) NO 0 +ISNULL(c) int(1) NO 0 +d IS TRUE int(1) NO 0 +d IS NOT TRUE int(1) NO 0 +d IS FALSE int(1) NO 0 +d IS NOT FALSE int(1) NO 0 +d IS UNKNOWN int(1) NO 0 +d IS NOT UNKNOWN int(1) NO 0 +d is NULL int(1) NO 0 +d IS NOT NULL int(1) NO 0 +ISNULL(d) int(1) NO 0 +create view view_24532_b as +select +a IS TRUE, +if(ifnull(a, 0), 1, 0) as old_istrue, +a IS NOT TRUE, +if(ifnull(a, 0), 0, 1) as old_isnottrue, +a IS FALSE, +if(ifnull(a, 1), 0, 1) as old_isfalse, +a IS NOT FALSE, +if(ifnull(a, 1), 1, 0) as old_isnotfalse +from table_24532; +describe view_24532_b; +Field Type Null Key Default Extra +a IS TRUE int(1) NO 0 +old_istrue int(1) NO 0 +a IS NOT TRUE int(1) NO 0 +old_isnottrue int(1) NO 0 +a IS FALSE int(1) NO 0 +old_isfalse int(1) NO 0 +a IS NOT FALSE int(1) NO 0 +old_isnotfalse int(1) NO 0 +show create view view_24532_b; +View Create View +view_24532_b CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_24532_b` AS select (`table_24532`.`a` is true) AS `a IS TRUE`,if(ifnull(`table_24532`.`a`,0),1,0) AS `old_istrue`,(`table_24532`.`a` is not true) AS `a IS NOT TRUE`,if(ifnull(`table_24532`.`a`,0),0,1) AS `old_isnottrue`,(`table_24532`.`a` is false) AS `a IS FALSE`,if(ifnull(`table_24532`.`a`,1),0,1) AS `old_isfalse`,(`table_24532`.`a` is not false) AS `a IS NOT FALSE`,if(ifnull(`table_24532`.`a`,1),1,0) AS `old_isnotfalse` from `table_24532` +insert into table_24532 values (0, 0, 0, 0); +select * from view_24532_b; +a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse +0 0 1 1 1 1 0 0 +update table_24532 set a=1; +select * from view_24532_b; +a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse +1 1 0 0 0 0 1 1 +update table_24532 set a=NULL; +select * from view_24532_b; +a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse +0 0 1 1 0 0 1 1 +drop view view_24532_a; +drop view view_24532_b; +drop table table_24532; End of 5.0 tests. diff --git a/mysql-test/std_data/init_file.dat b/mysql-test/std_data/init_file.dat index 814e968eb31..cb8e0778438 100644 --- a/mysql-test/std_data/init_file.dat +++ b/mysql-test/std_data/init_file.dat @@ -27,3 +27,12 @@ insert into t2 values (11), (13); drop procedure p1; drop function f1; drop view v1; + +# +# Bug#23240 --init-file statements with NOW() reports '1970-01-01 11:00:00'as the date time +# +CREATE DATABASE IF NOT EXISTS init_file; +CREATE TABLE IF NOT EXISTS init_file.startup ( startdate DATETIME ); +INSERT INTO init_file.startup VALUES ( NOW() ); + + diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 102c517dd46..df56165950f 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -10,6 +10,5 @@ # ############################################################################## -im_daemon_life_cycle : Bug#24415 see note: [19 Dec 23:17] Trudy Pelzer ndb_load : Bug#17233 user_limits : Bug#23921 random failure of user_limits.test diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index beaa371f847..5373ca3fec6 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -97,3 +97,14 @@ create table t1 (f1 int, f2 int); insert into t1 values(1,1),(0,0); select f1, f2, if(f1, 40.0, 5.00) from t1 group by f1 order by f2; drop table t1; + +# +# Bug#24532 (The return data type of IS TRUE is different from similar +# operations) +# +# IF(x, unsigned, unsigned) should be unsigned. +# + +select if(0, 18446744073709551610, 18446744073709551610); + + diff --git a/mysql-test/t/im_daemon_life_cycle.imtest b/mysql-test/t/im_daemon_life_cycle.imtest index acd615809f3..0ecc3f18f40 100644 --- a/mysql-test/t/im_daemon_life_cycle.imtest +++ b/mysql-test/t/im_daemon_life_cycle.imtest @@ -23,15 +23,20 @@ # - wait for IM-main to start accepting connections before continue test # case; # +# NOTE: timeout is 55 seconds. Timeout should be more than shutdown-delay +# specified for managed MySQL instance. Now shutdown-delay is 10 seconds +# (set in mysql-test-run.pl). So, 55 seconds should be enough to make 5 +# attempts. +# ########################################################################### --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Main-test: starting... --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Killing IM-main... ---exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 55 im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Waiting for IM-main to start accepting connections... ---exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 55 im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Main-test: done. @@ -58,23 +63,23 @@ START INSTANCE mysqld2; --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: waiting to start... ---exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 30 started im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 55 started im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: started. # 2. Restart IM-main; --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Killing IM-main... ---exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 55 im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Waiting for IM-main to start accepting connections... ---exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 55 im_daemon_life_cycle # 3. Issue some statement -- connection should be re-established. --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Checking that IM-main processing commands... ---replace_column 3 VERSION +--replace_column 2 STATE 3 VERSION SHOW INSTANCE STATUS mysqld1; # 4. Stop mysqld2, because it will not be stopped by IM, as it is nonguarded. @@ -85,7 +90,7 @@ SHOW INSTANCE STATUS mysqld1; STOP INSTANCE mysqld2; --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: waiting to stop... ---exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 30 stopped im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 55 stopped im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: stopped. ########################################################################### diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test index 31a6ef5a541..7c580afadda 100644 --- a/mysql-test/t/init_file.test +++ b/mysql-test/t/init_file.test @@ -6,6 +6,15 @@ # mysql-test/t/init_file-master.opt for the actual test # +# +# Bug#23240 --init-file statements with NOW() reports '1970-01-01 11:00:00'as the date time +# +INSERT INTO init_file.startup VALUES ( NOW() ); +SELECT * INTO @X FROM init_file.startup limit 0,1; +SELECT * INTO @Y FROM init_file.startup limit 1,1; +SELECT YEAR(@X)-YEAR(@Y); +DROP DATABASE init_file; + --echo ok --echo end of 4.1 tests # diff --git a/mysql-test/t/kill_n_check.sh b/mysql-test/t/kill_n_check.sh index 96c402a638c..6f2a0825dcd 100755 --- a/mysql-test/t/kill_n_check.sh +++ b/mysql-test/t/kill_n_check.sh @@ -53,7 +53,7 @@ pid_path="$1" expected_result="$2" total_timeout="$3" test_id="$4" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" log_debug "-- $basename: starting --" log_debug "pid_path: '$pid_path'" diff --git a/mysql-test/t/log.sh b/mysql-test/t/log.sh index 29cf8d3e1a3..33ef6d6701f 100755 --- a/mysql-test/t/log.sh +++ b/mysql-test/t/log.sh @@ -17,7 +17,7 @@ if [ $# -lt 2 ]; then fi test_id="$1" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" shift diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test index 7cf92dc5d0d..0014dc1f6af 100644 --- a/mysql-test/t/sp-vars.test +++ b/mysql-test/t/sp-vars.test @@ -500,8 +500,6 @@ DROP PROCEDURE p1; # # Test case for BUG#12976: Boolean values reversed in stored procedures? # -# TODO: test case failed. -# ########################################################################### --echo @@ -566,13 +564,8 @@ BEGIN END| delimiter ;| -# The expected and correct result. - call p1(); -# The wrong result. Note that only hex(vb) works, but is printed with two -# digits for some reason in this case. - call p2(); # @@ -583,6 +576,64 @@ DROP TABLE t1; DROP PROCEDURE p1; DROP PROCEDURE p2; +# Additional tests for Bug#12976 + +--disable_warnings +DROP TABLE IF EXISTS table_12976_a; +DROP TABLE IF EXISTS table_12976_b; +DROP PROCEDURE IF EXISTS proc_12976_a; +DROP PROCEDURE IF EXISTS proc_12976_b; +--enable_warnings + +CREATE TABLE table_12976_a (val bit(1)); + +CREATE TABLE table_12976_b( + appname varchar(15), + emailperm bit not null default 1, + phoneperm bit not null default 0); + +insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0'); + +delimiter ||; +CREATE PROCEDURE proc_12976_a() +BEGIN + declare localvar bit(1); + SELECT val INTO localvar FROM table_12976_a; + SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a; +END|| + +CREATE PROCEDURE proc_12976_b( + name varchar(15), + out ep bit, + out msg varchar(10)) +BEGIN + SELECT emailperm into ep FROM table_12976_b where (appname = name); + IF ep is true THEN + SET msg = 'True'; + ELSE + SET msg = 'False'; + END IF; +END|| + +delimiter ;|| + +INSERT table_12976_a VALUES (0); +call proc_12976_a(); +UPDATE table_12976_a set val=1; +call proc_12976_a(); + +call proc_12976_b('A', @ep, @msg); +select @ep, @msg; + +call proc_12976_b('B', @ep, @msg); +select @ep, @msg; + +DROP TABLE table_12976_a; +DROP TABLE table_12976_b; +DROP PROCEDURE proc_12976_a; +DROP PROCEDURE proc_12976_b; + + ########################################################################### # # Test case for BUG#9572: Stored procedures: variable type declarations diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 11cec291d8c..927adfec231 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2002,7 +2002,7 @@ EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; DROP TABLE t1; # -# Bug 24653: sorting by expressions containing subselects +# Bug 24653: sorting by expressions containing subselects # that return more than one row # @@ -2014,12 +2014,12 @@ INSERT INTO t2 VALUES (2,1), (1,3), (2,1), (4,4), (2,2), (1,4); SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); ---error 1242 -SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); -SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; ---error 1242 +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; +--error 1242 SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; - + SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); --error 1242 SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); @@ -2036,28 +2036,28 @@ SELECT a FROM t1 GROUP BY a SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; ---error 1242 +--error 1242 SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); --error 1242 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); --error 1242 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); -DROP TABLE t1,t2; +DROP TABLE t1,t2; # End of 4.1 tests @@ -2571,7 +2571,7 @@ DROP TABLE t1,t2; # # Bug #25219: EXIST subquery with UNION over a mix of # correlated and uncorrelated selects -# +# CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); CREATE TABLE t2 (c int); @@ -2579,52 +2579,125 @@ CREATE TABLE t2 (c int); INSERT INTO t1 VALUES ('aa', 1); INSERT INTO t2 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 - UNION + UNION SELECT c from t2 WHERE c=t1.c); INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 - UNION + UNION SELECT c from t2 WHERE c=t1.c); INSERT INTO t2 VALUES (2); CREATE TABLE t3 (c int); INSERT INTO t3 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 - UNION + UNION SELECT c from t2 WHERE c=t1.c); -DROP TABLE t1,t2,t3; -# -# Bug#23800: Outer fields in correlated subqueries is used in a temporary -# table created for sorting. -# -CREATE TABLE t1(f1 int); -CREATE TABLE t2(f2 int, f21 int, f3 timestamp); -INSERT INTO t1 VALUES (1),(1),(2),(2); -INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11"); -SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1; -SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2; -PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1'; -EXECUTE stmt1; -EXECUTE stmt1; -DEALLOCATE PREPARE stmt1; -SELECT f2, AVG(f21), - (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test - FROM t2 GROUP BY f2; -DROP TABLE t1,t2; -CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); -INSERT INTO t1 VALUES - (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), - (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), - (3,2,'k'), (3,1,'l'), (1,9,'m'); -SELECT a, MAX(b), - (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test - FROM t1 GROUP BY a; -DROP TABLE t1; +DROP TABLE t1,t2,t3; + +# +# Bug#21904 (parser problem when using IN with a double "(())") +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t1xt2; +--enable_warnings + +CREATE TABLE t1 ( + id_1 int(5) NOT NULL, + t varchar(4) DEFAULT NULL +); + +CREATE TABLE t2 ( + id_2 int(5) NOT NULL, + t varchar(4) DEFAULT NULL +); + +CREATE TABLE t1xt2 ( + id_1 int(5) NOT NULL, + id_2 int(5) NOT NULL +); + +INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); + +INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa'); + +INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4); + +# subselect returns 0 rows + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)))); + +insert INTO t1xt2 VALUES (1, 12); + +# subselect returns 1 row + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +insert INTO t1xt2 VALUES (2, 12); + +# subselect returns more than 1 row + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t1xt2; + diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 33e381af476..90a3c6a1e2d 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -748,12 +748,14 @@ drop view v1; # # VIEWs with national characters # -create table tü (cü char); -create view vü as select cü from tü; -insert into vü values ('ü'); -select * from vü; -drop view vü; -drop table tü; +set names utf8; +create table tü (cü char); +create view vü as select cü from tü; +insert into vü values ('ü'); +select * from vü; +drop view vü; +drop table tü; +set names latin1; # # problem with used_tables() of outer reference resolved in VIEW @@ -2991,7 +2993,7 @@ DROP VIEW v1; # CREATE TABLE t1 (mydate DATETIME); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); CREATE VIEW v1 AS SELECT mydate from t1; @@ -3039,7 +3041,7 @@ drop view v1; drop table t1; # -# Bug#26209: queries with GROUP BY and ORDER BY using views +# Bug#26209: queries with GROUP BY and ORDER BY using views # CREATE TABLE t1 ( @@ -3058,4 +3060,107 @@ SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); DROP VIEW v1; DROP TABLE t1; +# +# BUG#25897: Some queries are no longer possible after a CREATE VIEW +# fails +# +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +let $query = SELECT * FROM (SELECT 1) AS t; + +eval $query; +--error ER_VIEW_SELECT_DERIVED +eval CREATE VIEW v1 AS $query; +--echo # Previously the following would fail. +eval $query; + +# +# Bug#24532: The return data type of IS TRUE is different from similar +# operations +# + +--disable_warnings +drop view if exists view_24532_a; +drop view if exists view_24532_b; +drop table if exists table_24532; +--enable_warnings + +create table table_24532 ( + a int, + b bigint, + c int(4), + d bigint(48) +); + +create view view_24532_a as +select + a IS TRUE, + a IS NOT TRUE, + a IS FALSE, + a IS NOT FALSE, + a IS UNKNOWN, + a IS NOT UNKNOWN, + a is NULL, + a IS NOT NULL, + ISNULL(a), + b IS TRUE, + b IS NOT TRUE, + b IS FALSE, + b IS NOT FALSE, + b IS UNKNOWN, + b IS NOT UNKNOWN, + b is NULL, + b IS NOT NULL, + ISNULL(b), + c IS TRUE, + c IS NOT TRUE, + c IS FALSE, + c IS NOT FALSE, + c IS UNKNOWN, + c IS NOT UNKNOWN, + c is NULL, + c IS NOT NULL, + ISNULL(c), + d IS TRUE, + d IS NOT TRUE, + d IS FALSE, + d IS NOT FALSE, + d IS UNKNOWN, + d IS NOT UNKNOWN, + d is NULL, + d IS NOT NULL, + ISNULL(d) +from table_24532; + +describe view_24532_a; + +create view view_24532_b as +select + a IS TRUE, + if(ifnull(a, 0), 1, 0) as old_istrue, + a IS NOT TRUE, + if(ifnull(a, 0), 0, 1) as old_isnottrue, + a IS FALSE, + if(ifnull(a, 1), 0, 1) as old_isfalse, + a IS NOT FALSE, + if(ifnull(a, 1), 1, 0) as old_isnotfalse +from table_24532; + +describe view_24532_b; + +show create view view_24532_b; + +insert into table_24532 values (0, 0, 0, 0); +select * from view_24532_b; +update table_24532 set a=1; +select * from view_24532_b; +update table_24532 set a=NULL; +select * from view_24532_b; + +drop view view_24532_a; +drop view view_24532_b; +drop table table_24532; + --echo End of 5.0 tests. diff --git a/mysql-test/t/wait_for_process.sh b/mysql-test/t/wait_for_process.sh index 4c2d89cfea6..2143ab2002f 100755 --- a/mysql-test/t/wait_for_process.sh +++ b/mysql-test/t/wait_for_process.sh @@ -63,7 +63,7 @@ pid_path="$1" total_attempts="$2" event="$3" test_id="$4" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" log_debug "-- $basename: starting --" log_debug "pid_path: '$pid_path'" diff --git a/mysql-test/t/wait_for_socket.sh b/mysql-test/t/wait_for_socket.sh index 1bce74dfd3a..8c17c8ac0ac 100755 --- a/mysql-test/t/wait_for_socket.sh +++ b/mysql-test/t/wait_for_socket.sh @@ -30,7 +30,7 @@ password="$4" db="$5" total_timeout="$6" test_id="$7" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" log_debug "-- $basename: starting --" log_debug "client_exe: '$client_exe'" diff --git a/server-tools/instance-manager/instance_options.cc b/server-tools/instance-manager/instance_options.cc index 3affb43bd25..d4ca2ad570f 100644 --- a/server-tools/instance-manager/instance_options.cc +++ b/server-tools/instance-manager/instance_options.cc @@ -508,7 +508,7 @@ int Instance_options::add_option(const char* option) {"--pid-file=", 11, &mysqld_pid_file, SAVE_WHOLE_AND_ADD}, {"--mysqld-path=", 14, &mysqld_path, SAVE_VALUE}, {"--nonguarded", 9, &nonguarded, SAVE_WHOLE}, - {"--shutdown_delay", 9, &shutdown_delay, SAVE_VALUE}, + {"--shutdown-delay", 9, &shutdown_delay, SAVE_VALUE}, {NULL, 0, NULL, 0} }; struct selected_options_st *selected_options; diff --git a/server-tools/instance-manager/listener.cc b/server-tools/instance-manager/listener.cc index 15583b233fb..a055d98656c 100644 --- a/server-tools/instance-manager/listener.cc +++ b/server-tools/instance-manager/listener.cc @@ -210,10 +210,13 @@ void Listener_thread::run() return; err: + log_error("Listener: failed to initialize. Initiate shutdown..."); + // we have to close the ip sockets in case of error for (i= 0; i < num_sockets; i++) closesocket(sockets[i]); + thread_registry.set_error_status(); thread_registry.unregister_thread(&thread_info); thread_registry.request_shutdown(); my_thread_end(); diff --git a/server-tools/instance-manager/manager.cc b/server-tools/instance-manager/manager.cc index 30b6aea8519..cadb7a4aa0c 100644 --- a/server-tools/instance-manager/manager.cc +++ b/server-tools/instance-manager/manager.cc @@ -114,6 +114,9 @@ void stop_all(Guardian_thread *guardian, Thread_registry *registry) pthread_cond_signal(&guardian->COND_guardian); /* stop all threads */ registry->deliver_shutdown(); + + /* Set error status in the thread registry. */ + registry->set_error_status(); } /* @@ -123,7 +126,7 @@ void stop_all(Guardian_thread *guardian, Thread_registry *registry) architecture. */ -void manager(const Options &options) +int manager(const Options &options) { Thread_registry thread_registry; /* @@ -145,10 +148,10 @@ void manager(const Options &options) instance_map.guardian= &guardian_thread; if (instance_map.init() || user_map.init()) - return; + return 1; if (user_map.load(options.password_file_name)) - return; + return 1; /* write Instance Manager pid file */ @@ -157,7 +160,7 @@ void manager(const Options &options) (int) manager_pid); if (create_pid_file(options.pid_file_name, manager_pid)) - return; + return 1; /* Initialize signals and alarm-infrastructure. @@ -301,5 +304,6 @@ err: end_thr_alarm(1); /* don't pthread_exit to kill all threads who did not shut down in time */ #endif -} + return thread_registry.get_error_status() ? 1 : 0; +} diff --git a/server-tools/instance-manager/manager.h b/server-tools/instance-manager/manager.h index 0d459d9cee1..22af0d39115 100644 --- a/server-tools/instance-manager/manager.h +++ b/server-tools/instance-manager/manager.h @@ -18,7 +18,7 @@ struct Options; -void manager(const Options &options); +int manager(const Options &options); int create_pid_file(const char *pid_file_name, int pid); diff --git a/server-tools/instance-manager/mysqlmanager.cc b/server-tools/instance-manager/mysqlmanager.cc index 66fc0b68ac4..5a442e2e49a 100644 --- a/server-tools/instance-manager/mysqlmanager.cc +++ b/server-tools/instance-manager/mysqlmanager.cc @@ -110,8 +110,7 @@ int main(int argc, char *argv[]) else #endif - manager(options); - return_value= 0; + return_value= manager(options); err: options.cleanup(); @@ -254,26 +253,23 @@ static void daemonize(const char *log_file_name) enum { CHILD_OK= 0, CHILD_NEED_RESPAWN, CHILD_EXIT_ANGEL }; static volatile sig_atomic_t child_status= CHILD_OK; +static volatile sig_atomic_t child_exit_code= 0; /* - Signal handler for SIGCHLD: reap child, analyze child exit status, and set + Signal handler for SIGCHLD: reap child, analyze child exit code, and set child_status appropriately. */ void reap_child(int __attribute__((unused)) signo) { - int child_exit_status; - /* As we have only one child, no need to cycle waitpid */ - if (waitpid(0, &child_exit_status, WNOHANG) > 0) + /* NOTE: As we have only one child, no need to cycle waitpid(). */ + + int exit_code; + + if (waitpid(0, &exit_code, WNOHANG) > 0) { - if (WIFSIGNALED(child_exit_status)) - child_status= CHILD_NEED_RESPAWN; - else - /* - As reap_child is not called for SIGSTOP, we should be here only - if the child exited normally. - */ - child_status= CHILD_EXIT_ANGEL; + child_exit_code= exit_code; + child_status= exit_code ? CHILD_NEED_RESPAWN : CHILD_EXIT_ANGEL; } } @@ -353,10 +349,16 @@ spawn: else if (child_status == CHILD_NEED_RESPAWN) { child_status= CHILD_OK; - log_error("angel(): mysqlmanager exited abnormally: respawning..."); + log_error("angel(): mysqlmanager exited abnormally (exit code: %d):" + "respawning...", + (int) child_exit_code); sleep(1); /* don't respawn too fast */ goto spawn; } + + /* Delete IM-angel pid file. */ + my_delete(Options::angel_pid_file_name, MYF(0)); + /* mysqlmanager successfully exited, let's silently evaporate If we return to main we fall into the manager() function, so let's diff --git a/server-tools/instance-manager/thread_registry.cc b/server-tools/instance-manager/thread_registry.cc index e061bb3384b..5bb4206982e 100644 --- a/server-tools/instance-manager/thread_registry.cc +++ b/server-tools/instance-manager/thread_registry.cc @@ -53,6 +53,7 @@ Thread_info::Thread_info(pthread_t thread_id_arg) : Thread_registry::Thread_registry() : shutdown_in_progress(false) ,sigwait_thread_pid(pthread_self()) + ,error_status(FALSE) { pthread_mutex_init(&LOCK_thread_registry, 0); pthread_cond_init(&COND_thread_registry_is_empty, 0); @@ -243,3 +244,23 @@ void Thread_registry::request_shutdown() { pthread_kill(sigwait_thread_pid, SIGTERM); } + + +int Thread_registry::get_error_status() +{ + int ret_error_status; + + pthread_mutex_lock(&LOCK_thread_registry); + ret_error_status= error_status; + pthread_mutex_unlock(&LOCK_thread_registry); + + return ret_error_status; +} + + +void Thread_registry::set_error_status() +{ + pthread_mutex_lock(&LOCK_thread_registry); + error_status= TRUE; + pthread_mutex_unlock(&LOCK_thread_registry); +} diff --git a/server-tools/instance-manager/thread_registry.h b/server-tools/instance-manager/thread_registry.h index 694fa57378e..f6de49cd9ad 100644 --- a/server-tools/instance-manager/thread_registry.h +++ b/server-tools/instance-manager/thread_registry.h @@ -97,12 +97,16 @@ public: pthread_mutex_t *mutex); int cond_timedwait(Thread_info *info, pthread_cond_t *cond, pthread_mutex_t *mutex, struct timespec *wait_time); + int get_error_status(); + void set_error_status(); + private: Thread_info head; bool shutdown_in_progress; pthread_mutex_t LOCK_thread_registry; pthread_cond_t COND_thread_registry_is_empty; pthread_t sigwait_thread_pid; + bool error_status; }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index e0d74cc7547..edae81b8e2d 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -793,6 +793,59 @@ int Arg_comparator::compare_e_row() } +void Item_func_truth::fix_length_and_dec() +{ + maybe_null= 0; + null_value= 0; + decimals= 0; + max_length= 1; +} + + +void Item_func_truth::print(String *str) +{ + str->append('('); + args[0]->print(str); + str->append(STRING_WITH_LEN(" is ")); + if (! affirmative) + str->append(STRING_WITH_LEN("not ")); + if (value) + str->append(STRING_WITH_LEN("true")); + else + str->append(STRING_WITH_LEN("false")); + str->append(')'); +} + + +bool Item_func_truth::val_bool() +{ + bool val= args[0]->val_bool(); + if (args[0]->null_value) + { + /* + NULL val IS {TRUE, FALSE} --> FALSE + NULL val IS NOT {TRUE, FALSE} --> TRUE + */ + return (! affirmative); + } + + if (affirmative) + { + /* {TRUE, FALSE} val IS {TRUE, FALSE} value */ + return (val == value); + } + + /* {TRUE, FALSE} val IS NOT {TRUE, FALSE} value */ + return (val != value); +} + + +longlong Item_func_truth::val_int() +{ + return (val_bool() ? 1 : 0); +} + + bool Item_in_optimizer::fix_left(THD *thd, Item **ref) { if (!args[0]->fixed && args[0]->fix_fields(thd, args) || @@ -1529,6 +1582,7 @@ Item_func_if::fix_length_and_dec() { maybe_null=args[1]->maybe_null || args[2]->maybe_null; decimals= max(args[1]->decimals, args[2]->decimals); + unsigned_flag=args[1]->unsigned_flag && args[2]->unsigned_flag; enum Item_result arg1_type=args[1]->result_type(); enum Item_result arg2_type=args[2]->result_type(); @@ -1558,12 +1612,20 @@ Item_func_if::fix_length_and_dec() collation.set(&my_charset_bin); // Number } } - max_length= - (cached_result_type == DECIMAL_RESULT || cached_result_type == INT_RESULT) ? - (max(args[1]->max_length - args[1]->decimals, - args[2]->max_length - args[2]->decimals) + decimals + - (unsigned_flag ? 0 : 1) ) : - max(args[1]->max_length, args[2]->max_length); + + if ((cached_result_type == DECIMAL_RESULT ) + || (cached_result_type == INT_RESULT)) + { + int len1= args[1]->max_length - args[1]->decimals + - (args[1]->unsigned_flag ? 0 : 1); + + int len2= args[2]->max_length - args[2]->decimals + - (args[2]->unsigned_flag ? 0 : 1); + + max_length=max(len1, len2) + decimals + (unsigned_flag ? 0 : 1); + } + else + max_length= max(args[1]->max_length, args[2]->max_length); } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 47f67d0392f..319c6c2a36f 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -101,6 +101,92 @@ public: uint decimal_precision() const { return 1; } }; + +/** + Abstract Item class, to represent <code>X IS [NOT] (TRUE | FALSE)</code> + boolean predicates. +*/ + +class Item_func_truth : public Item_bool_func +{ +public: + virtual bool val_bool(); + virtual longlong val_int(); + virtual void fix_length_and_dec(); + virtual void print(String *str); + +protected: + Item_func_truth(Item *a, bool a_value, bool a_affirmative) + : Item_bool_func(a), value(a_value), affirmative(a_affirmative) + {} + + ~Item_func_truth() + {} +private: + /** + True for <code>X IS [NOT] TRUE</code>, + false for <code>X IS [NOT] FALSE</code> predicates. + */ + const bool value; + /** + True for <code>X IS Y</code>, false for <code>X IS NOT Y</code> predicates. + */ + const bool affirmative; +}; + + +/** + This Item represents a <code>X IS TRUE</code> boolean predicate. +*/ + +class Item_func_istrue : public Item_func_truth +{ +public: + Item_func_istrue(Item *a) : Item_func_truth(a, true, true) {} + ~Item_func_istrue() {} + virtual const char* func_name() const { return "istrue"; } +}; + + +/** + This Item represents a <code>X IS NOT TRUE</code> boolean predicate. +*/ + +class Item_func_isnottrue : public Item_func_truth +{ +public: + Item_func_isnottrue(Item *a) : Item_func_truth(a, true, false) {} + ~Item_func_isnottrue() {} + virtual const char* func_name() const { return "isnottrue"; } +}; + + +/** + This Item represents a <code>X IS FALSE</code> boolean predicate. +*/ + +class Item_func_isfalse : public Item_func_truth +{ +public: + Item_func_isfalse(Item *a) : Item_func_truth(a, false, true) {} + ~Item_func_isfalse() {} + virtual const char* func_name() const { return "isfalse"; } +}; + + +/** + This Item represents a <code>X IS NOT FALSE</code> boolean predicate. +*/ + +class Item_func_isnotfalse : public Item_func_truth +{ +public: + Item_func_isnotfalse(Item *a) : Item_func_truth(a, false, false) {} + ~Item_func_isnotfalse() {} + virtual const char* func_name() const { return "isnotfalse"; } +}; + + class Item_cache; #define UNKNOWN ((my_bool)-1) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 20d9ed8affa..12ae0c026eb 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -51,6 +51,10 @@ Item_subselect::Item_subselect(): void Item_subselect::init(st_select_lex *select_lex, select_subselect *result) { + /* + Please see Item_singlerow_subselect::invalidate_and_restore_select_lex(), + which depends on alterations to the parse tree implemented here. + */ DBUG_ENTER("Item_subselect::init"); DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex)); @@ -91,6 +95,12 @@ void Item_subselect::init(st_select_lex *select_lex, DBUG_VOID_RETURN; } +st_select_lex * +Item_subselect::get_select_lex() +{ + return unit->first_select(); +} + void Item_subselect::cleanup() { DBUG_ENTER("Item_subselect::cleanup"); @@ -268,6 +278,26 @@ Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex) DBUG_VOID_RETURN; } +st_select_lex * +Item_singlerow_subselect::invalidate_and_restore_select_lex() +{ + DBUG_ENTER("Item_singlerow_subselect::invalidate_and_restore_select_lex"); + st_select_lex *result= get_select_lex(); + + DBUG_ASSERT(result); + + /* + This code restore the parse tree in it's state before the execution of + Item_singlerow_subselect::Item_singlerow_subselect(), + and in particular decouples this object from the SELECT_LEX, + so that the SELECT_LEX can be used with a different flavor + or Item_subselect instead, as part of query rewriting. + */ + unit->item= NULL; + + DBUG_RETURN(result); +} + Item_maxmin_subselect::Item_maxmin_subselect(THD *thd_param, Item_subselect *parent, st_select_lex *select_lex, diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 485a896c1c7..6b605e96432 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -126,6 +126,12 @@ public: virtual void reset_value_registration() {} enum_parsing_place place() { return parsing_place; } + /** + Get the SELECT_LEX structure associated with this Item. + @return the SELECT_LEX structure associated with this Item + */ + st_select_lex* get_select_lex(); + friend class select_subselect; friend class Item_in_optimizer; friend bool Item_field::fix_fields(THD *, Item **); @@ -169,6 +175,20 @@ public: bool null_inside(); void bring_value(); + /** + This method is used to implement a special case of semantic tree + rewriting, mandated by a SQL:2003 exception in the specification. + The only caller of this method is handle_sql2003_note184_exception(), + see the code there for more details. + Note that this method breaks the object internal integrity, by + removing it's association with the corresponding SELECT_LEX, + making this object orphan from the parse tree. + No other method, beside the destructor, should be called on this + object, as it is now invalid. + @return the SELECT_LEX structure that was given in the constructor. + */ + st_select_lex* invalidate_and_restore_select_lex(); + friend class select_singlerow_subselect; }; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 24bdd2db5b6..71460a67934 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -36,6 +36,7 @@ Item_result sp_map_result_type(enum enum_field_types type) { switch (type) { + case MYSQL_TYPE_BIT: case MYSQL_TYPE_TINY: case MYSQL_TYPE_SHORT: case MYSQL_TYPE_LONG: @@ -58,6 +59,7 @@ Item::Type sp_map_item_type(enum enum_field_types type) { switch (type) { + case MYSQL_TYPE_BIT: case MYSQL_TYPE_TINY: case MYSQL_TYPE_SHORT: case MYSQL_TYPE_LONG: diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 630d135be48..26133c6b66e 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1210,6 +1210,13 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, int4store(key + key_length, thd->server_id); int4store(key + key_length + 4, thd->variables.pseudo_thread_id); + /* + Unless requested otherwise, try to resolve this table in the list + of temporary tables of this thread. In MySQL temporary tables + are always thread-local and "shadow" possible base tables with the + same name. This block implements the behaviour. + TODO: move this block into a separate function. + */ if (!table_list->skip_temporary) { for (table= thd->temporary_tables; table ; table=table->next) @@ -1218,6 +1225,12 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, !memcmp(table->s->table_cache_key, key, key_length + TMP_TABLE_KEY_EXTRA)) { + /* + We're trying to use the same temporary table twice in a query. + Right now we don't support this because a temporary table + is always represented by only one TABLE object in THD, and + it can not be cloned. Emit an error for an unsupported behaviour. + */ if (table->query_id == thd->query_id || thd->prelocked_mode && table->query_id) { @@ -1233,6 +1246,13 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, } } + /* + The table is not temporary - if we're in pre-locked or LOCK TABLES + mode, let's try to find the requested table in the list of pre-opened + and locked tables. If the table is not there, return an error - we can't + open not pre-opened tables in pre-locked/LOCK TABLES mode. + TODO: move this block into a separate function. + */ if (!(flags & MYSQL_OPEN_IGNORE_LOCKED_TABLES) && (thd->locked_tables || thd->prelocked_mode)) { // Using table locks @@ -1304,7 +1324,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, goto reset; } /* - is it view? + Is this table a view and not a base table? (it is work around to allow to open view with locked tables, real fix will be made after definition cache will be made) */ @@ -1338,8 +1358,32 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, DBUG_RETURN(0); } + /* + Non pre-locked/LOCK TABLES mode, and the table is not temporary: + this is the normal use case. + Now we should: + - try to find the table in the table cache. + - if one of the discovered TABLE instances is name-locked + (table->s->version == 0) or some thread has started FLUSH TABLES + (refresh_version > table->s->version), back off -- we have to wait + until no one holds a name lock on the table. + - if there is no such TABLE in the name cache, read the table definition + and insert it into the cache. + We perform all of the above under LOCK_open which currently protects + the open cache (also known as table cache) and table definitions stored + on disk. + */ + VOID(pthread_mutex_lock(&LOCK_open)); + /* + If it's the first table from a list of tables used in a query, + remember refresh_version (the version of open_cache state). + If the version changes while we're opening the remaining tables, + we will have to back off, close all the tables opened-so-far, + and try to reopen them. + Note: refresh_version is currently changed only during FLUSH TABLES. + */ if (!thd->open_tables) thd->version=refresh_version; else if ((thd->version != refresh_version) && @@ -1356,12 +1400,39 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, if (thd->handler_tables) mysql_ha_flush(thd, (TABLE_LIST*) NULL, MYSQL_HA_REOPEN_ON_USAGE, TRUE); + /* + Actually try to find the table in the open_cache. + The cache may contain several "TABLE" instances for the same + physical table. The instances that are currently "in use" by + some thread have their "in_use" member != NULL. + There is no good reason for having more than one entry in the + hash for the same physical table, except that we use this as + an implicit "pending locks queue" - see + wait_for_locked_table_names for details. + */ for (table= (TABLE*) hash_first(&open_cache, (byte*) key, key_length, &state); table && table->in_use ; table= (TABLE*) hash_next(&open_cache, (byte*) key, key_length, &state)) { + /* + Normally, table->s->version contains the value of + refresh_version from the moment when this table was + (re-)opened and added to the cache. + If since then we did (or just started) FLUSH TABLES + statement, refresh_version has been increased. + For "name-locked" TABLE instances, table->s->version is set + to 0 (see lock_table_name for details). + In case there is a pending FLUSH TABLES or a name lock, we + need to back off and re-start opening tables. + If we do not back off now, we may dead lock in case of lock + order mismatch with some other thread: + c1: name lock t1; -- sort of exclusive lock + c2: open t2; -- sort of shared lock + c1: name lock t2; -- blocks + c2: open t1; -- blocks + */ if (table->s->version != refresh_version) { DBUG_PRINT("note", @@ -1375,16 +1446,35 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, } /* - There is a refresh in progress for this table - Wait until the table is freed or the thread is killed. + Back off, part 1: mark the table as "unused" for the + purpose of name-locking by setting table->db_stat to 0. Do + that only for the tables in this thread that have an old + table->s->version (this is an optimization (?)). + table->db_stat == 0 signals wait_for_locked_table_names + that the tables in question are not used any more. See + table_is_used call for details. */ close_old_data_files(thd,thd->open_tables,0,0); + /* + Back-off part 2: try to avoid "busy waiting" on the table: + if the table is in use by some other thread, we suspend + and wait till the operation is complete: when any + operation that juggles with table->s->version completes, + it broadcasts COND_refresh condition variable. + */ if (table->in_use != thd) + { wait_for_refresh(thd); + /* wait_for_refresh will unlock LOCK_open for us */ + } else { VOID(pthread_mutex_unlock(&LOCK_open)); } + /* + There is a refresh in progress for this table. + Signal the caller that it has to try again. + */ if (refresh) *refresh=1; DBUG_RETURN(0); @@ -1392,6 +1482,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, } if (table) { + /* Unlink the table from "unused_tables" list. */ if (table == unused_tables) { // First unused unused_tables=unused_tables->next; // Remove from link @@ -1404,6 +1495,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, } else { + /* Insert a new TABLE instance into the open cache */ TABLE_SHARE *share; int error; /* Free cache if too big */ @@ -2145,6 +2237,10 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags) } } + /* + For every table in the list of tables to open, try to find or open + a table. + */ for (tables= *start; tables ;tables= tables->next_global) { /* @@ -2159,6 +2255,12 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags) goto process_view_routines; continue; } + /* + If this TABLE_LIST object is a placeholder for an information_schema + table, create a temporary table to represent the information_schema + table in the query. Do not fill it yet - will be filled during + execution. + */ if (tables->schema_table) { if (!mysql_schema_table(thd, thd->lex, tables)) @@ -2166,7 +2268,11 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags) DBUG_RETURN(-1); } (*counter)++; - + + /* + Not a placeholder: must be a base table or a view, and the table is + not opened yet. Try to open the table. + */ if (!tables->table && !(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, flags))) { @@ -2273,7 +2379,7 @@ process_view_routines: { /* Serious error during reading stored routines from mysql.proc table. - Something's wrong with the table or its contents, and an error has + Something is wrong with the table or its contents, and an error has been emitted; we must abort. */ result= -1; diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc index 8e6846fdcd2..d06ac7824fd 100644 --- a/sql/sql_cache.cc +++ b/sql/sql_cache.cc @@ -1765,8 +1765,18 @@ void Query_cache::free_cache() { DBUG_ENTER("Query_cache::free_cache"); if (query_cache_size > 0) - { flush_cache(); + /* + There may be two free_cache() calls in progress, because we + release 'structure_guard_mutex' in flush_cache(). When the second + flush_cache() wakes up from the wait on 'COND_flush_finished', the + first call to free_cache() has done its job. So we have to test + 'query_cache_size > 0' the second time to see if the cache wasn't + reset by other thread, or if it was reset and was re-enabled then. + If the cache was reset, then we have nothing to do here. + */ + if (query_cache_size > 0) + { #ifndef DBUG_OFF if (bins[0].free_blocks == 0) { @@ -1808,6 +1818,12 @@ void Query_cache::free_cache() flush_in_progress flag and releases the lock, so other threads may proceed skipping the cache as if it is disabled. Concurrent flushes are performed in turn. + + After flush_cache() call, the cache is flushed, all the freed + memory is accumulated in bin[0], and the 'structure_guard_mutex' + is locked. However, since we could release the mutex during + execution, the rest of the cache state could have been changed, + and should not be relied on. */ void Query_cache::flush_cache() diff --git a/sql/sql_class.cc b/sql/sql_class.cc index ab9bda44f26..cef9257c235 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -319,6 +319,7 @@ void THD::init(void) void THD::init_for_queries() { + set_time(); ha_enable_transaction(this,TRUE); reset_root_defaults(mem_root, variables.query_alloc_block_size, diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index cd4d2e85316..9a9731459b3 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -99,6 +99,16 @@ void lex_free(void) } +void +st_parsing_options::reset() +{ + allows_variable= TRUE; + allows_select_into= TRUE; + allows_select_procedure= TRUE; + allows_derived= TRUE; +} + + /* This is called before every query that is to be parsed. Because of this, it's critical to not do too much things here. @@ -149,6 +159,7 @@ void lex_start(THD *thd, uchar *buf,uint length) lex->safe_to_cache_query= 1; lex->time_zone_tables_used= 0; lex->leaf_tables_insert= 0; + lex->parsing_options.reset(); lex->empty_field_list_on_rset= 0; lex->select_lex.select_number= 1; lex->next_state=MY_LEX_START; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 86d01675c2a..b963ada2004 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -891,10 +891,8 @@ struct st_parsing_options bool allows_select_procedure; bool allows_derived; - st_parsing_options() - : allows_variable(TRUE), allows_select_into(TRUE), - allows_select_procedure(TRUE), allows_derived(TRUE) - {} + st_parsing_options() { reset(); } + void reset(); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 4c250092ba6..7f5fb416a94 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1162,7 +1162,6 @@ pthread_handler_t handle_one_connection(void *arg) thd->version= refresh_version; thd->proc_info= 0; thd->command= COM_SLEEP; - thd->set_time(); thd->init_for_queries(); if (sys_init_connect.value_length && !(sctx->master_access & SUPER_ACL)) @@ -1178,7 +1177,6 @@ pthread_handler_t handle_one_connection(void *arg) sql_print_warning("%s", net->last_error); } thd->proc_info=0; - thd->set_time(); thd->init_for_queries(); } @@ -1312,6 +1310,7 @@ pthread_handler_t handle_bootstrap(void *arg) mode we have only one thread. */ thd->query_id=next_query_id(); + thd->set_time(); mysql_parse(thd,thd->query,length); close_thread_tables(thd); // Free tables if (thd->is_fatal_error) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 473a300feeb..a9921581f0c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -59,15 +59,6 @@ const LEX_STRING null_lex_str={0,0}; YYABORT; \ } -/* Helper for parsing "IS [NOT] truth_value" */ -inline Item *is_truth_value(Item *A, bool v1, bool v2) -{ - return new Item_func_if(create_func_ifnull(A, - new Item_int((char *) (v2 ? "TRUE" : "FALSE"), v2, 1)), - new Item_int((char *) (v1 ? "TRUE" : "FALSE"), v1, 1), - new Item_int((char *) (v1 ? "FALSE" : "TRUE"),!v1, 1)); -} - #ifndef DBUG_OFF #define YYDEBUG 1 #else @@ -277,6 +268,81 @@ void case_stmt_action_end_case(LEX *lex, bool simple) lex->sphead->do_cont_backpatch(); } +/** + Helper to resolve the SQL:2003 Syntax exception 1) in <in predicate>. + See SQL:2003, Part 2, section 8.4 <in predicate>, Note 184, page 383. + This function returns the proper item for the SQL expression + <code>left [NOT] IN ( expr )</code> + @param thd the current thread + @param left the in predicand + @param equal true for IN predicates, false for NOT IN predicates + @param expr first and only expression of the in value list + @return an expression representing the IN predicate. +*/ +Item* handle_sql2003_note184_exception(THD *thd, Item* left, bool equal, + Item *expr) +{ + /* + Relevant references for this issue: + - SQL:2003, Part 2, section 8.4 <in predicate>, page 383, + - SQL:2003, Part 2, section 7.2 <row value expression>, page 296, + - SQL:2003, Part 2, section 6.3 <value expression primary>, page 174, + - SQL:2003, Part 2, section 7.15 <subquery>, page 370, + - SQL:2003 Feature F561, "Full value expressions". + + The exception in SQL:2003 Note 184 means: + Item_singlerow_subselect, which corresponds to a <scalar subquery>, + should be re-interpreted as an Item_in_subselect, which corresponds + to a <table subquery> when used inside an <in predicate>. + + Our reading of Note 184 is reccursive, so that all: + - IN (( <subquery> )) + - IN ((( <subquery> ))) + - IN '('^N <subquery> ')'^N + - etc + should be interpreted as a <table subquery>, no matter how deep in the + expression the <subquery> is. + */ + + Item *result; + + DBUG_ENTER("handle_sql2003_note184_exception"); + + if (expr->type() == Item::SUBSELECT_ITEM) + { + Item_subselect *expr2 = (Item_subselect*) expr; + + if (expr2->substype() == Item_subselect::SINGLEROW_SUBS) + { + Item_singlerow_subselect *expr3 = (Item_singlerow_subselect*) expr2; + st_select_lex *subselect; + + /* + Implement the mandated change, by altering the semantic tree: + left IN Item_singlerow_subselect(subselect) + is modified to + left IN (subselect) + which is represented as + Item_in_subselect(left, subselect) + */ + subselect= expr3->invalidate_and_restore_select_lex(); + result= new (thd->mem_root) Item_in_subselect(left, subselect); + + if (! equal) + result = negate_expression(thd, result); + + DBUG_RETURN(result); + } + } + + if (equal) + result= new (thd->mem_root) Item_func_eq(left, expr); + else + result= new (thd->mem_root) Item_func_ne(left, expr); + + DBUG_RETURN(result); +} + %} %union { int num; @@ -4393,13 +4459,18 @@ bool_factor: | bool_test ; bool_test: - bool_pri IS TRUE_SYM { $$= is_truth_value($1,1,0); } - | bool_pri IS not TRUE_SYM { $$= is_truth_value($1,0,0); } - | bool_pri IS FALSE_SYM { $$= is_truth_value($1,0,1); } - | bool_pri IS not FALSE_SYM { $$= is_truth_value($1,1,1); } - | bool_pri IS UNKNOWN_SYM { $$= new Item_func_isnull($1); } - | bool_pri IS not UNKNOWN_SYM { $$= new Item_func_isnotnull($1); } - | bool_pri ; + bool_pri IS TRUE_SYM + { $$= new (YYTHD->mem_root) Item_func_istrue($1); } + | bool_pri IS not TRUE_SYM + { $$= new (YYTHD->mem_root) Item_func_isnottrue($1); } + | bool_pri IS FALSE_SYM + { $$= new (YYTHD->mem_root) Item_func_isfalse($1); } + | bool_pri IS not FALSE_SYM + { $$= new (YYTHD->mem_root) Item_func_isnotfalse($1); } + | bool_pri IS UNKNOWN_SYM { $$= new Item_func_isnull($1); } + | bool_pri IS not UNKNOWN_SYM { $$= new Item_func_isnotnull($1); } + | bool_pri + ; bool_pri: bool_pri IS NULL_SYM { $$= new Item_func_isnull($1); } @@ -4412,31 +4483,37 @@ bool_pri: | predicate ; predicate: - 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 '(' subselect ')' + { + $$= new (YYTHD->mem_root) Item_in_subselect($1, $4); + } + | bit_expr not IN_SYM '(' subselect ')' + { + THD *thd= YYTHD; + Item *item= new (thd->mem_root) Item_in_subselect($1, $5); + $$= negate_expression(thd, item); + } | bit_expr IN_SYM '(' expr ')' { - $$= new Item_func_eq($1, $4); + $$= handle_sql2003_note184_exception(YYTHD, $1, true, $4); } - | bit_expr IN_SYM '(' expr ',' expr_list ')' - { - $6->push_front($4); - $6->push_front($1); - $$= new Item_func_in(*$6); + | bit_expr IN_SYM '(' expr ',' expr_list ')' + { + $6->push_front($4); + $6->push_front($1); + $$= new (YYTHD->mem_root) Item_func_in(*$6); } | bit_expr not IN_SYM '(' expr ')' { - $$= new Item_func_ne($1, $5); + $$= handle_sql2003_note184_exception(YYTHD, $1, false, $5); } - | bit_expr not IN_SYM '(' expr ',' expr_list ')' + | 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; + $7->push_front($5); + $7->push_front($1); + Item_func_in *item = new (YYTHD->mem_root) Item_func_in(*$7); + item->negate(); + $$= item; } | bit_expr BETWEEN_SYM bit_expr AND_SYM predicate { $$= new Item_func_between($1,$3,$5); } |