diff options
Diffstat (limited to 'mysql-test/r')
73 files changed, 2306 insertions, 177 deletions
diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index c620eb97cab..f90bcb521e1 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12728,3 +12728,31 @@ Table Op Msg_type Msg_text test.t1 repair Error Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! test.t1 repair error Corrupt DROP TABLE t1; +# +# BUG#48757 - missing .ARZ file causes server crash +# +CREATE TABLE t1(a INT) ENGINE=ARCHIVE; +FLUSH TABLE t1; +SELECT * FROM t1; +ERROR HY000: Can't find file: 't1' (errno: 2) +DROP TABLE t1; +ERROR 42S02: Unknown table 't1' +# +# BUG#46565 - repair of partition fail for archive engine +# +# Installing corrupted table files for t1. +SELECT * FROM t1; +ERROR HY000: Table 't1' is marked as crashed and should be repaired +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair error Corrupt +SELECT * FROM t1; +ERROR HY000: Table 't1' is marked as crashed and should be repaired +REPAIR TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT * FROM t1; +a +1 +2 +DROP TABLE t1; diff --git a/mysql-test/r/backup.result b/mysql-test/r/backup.result index bab2c83448c..89be20aee70 100644 --- a/mysql-test/r/backup.result +++ b/mysql-test/r/backup.result @@ -4,23 +4,23 @@ create table t4(n int); backup table t4 to '../../bogus'; Table Op Msg_type Msg_text test.t4 backup error Failed copying .frm file (errno: X) -test.t4 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t4 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t4 backup Error Can't create/write to file 'MYSQLTEST_VARDIR/bogus/t4.frm' (Errcode: X) test.t4 backup status Operation failed backup table t4 to '../../tmp'; Table Op Msg_type Msg_text -test.t4 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t4 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t4 backup status OK backup table t4 to '../../tmp'; Table Op Msg_type Msg_text test.t4 backup error Failed copying .frm file (errno: X) -test.t4 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t4 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t4 backup Error Can't create/write to file 'MYSQLTEST_VARDIR/tmp/t4.frm' (Errcode: X) test.t4 backup status Operation failed drop table t4; restore table t4 from '../../tmp'; Table Op Msg_type Msg_text -test.t4 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t4 restore Warning 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t4 restore status OK select count(*) from t4; count(*) @@ -29,18 +29,18 @@ create table t1(n int); insert into t1 values (23),(45),(67); backup table t1 to '../../tmp'; Table Op Msg_type Msg_text -test.t1 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t1 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 backup status OK drop table t1; restore table t1 from '../../bogus'; Table Op Msg_type Msg_text t1 restore error Failed copying .frm file Warnings: -Warning 1287 The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +Warning 1287 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead Error 29 File 'MYSQLTEST_VARDIR/bogus/t1.frm' not found (Errcode: X) restore table t1 from '../../tmp'; Table Op Msg_type Msg_text -test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t1 restore Warning 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK select n from t1; n @@ -53,13 +53,13 @@ insert into t2 values (123),(145),(167); insert into t3 values (223),(245),(267); backup table t2,t3 to '../../tmp'; Table Op Msg_type Msg_text -test.t2 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t2 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t2 backup status OK test.t3 backup status OK drop table t1,t2,t3; restore table t1,t2,t3 from '../../tmp'; Table Op Msg_type Msg_text -test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t1 restore Warning 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK test.t2 restore status OK test.t3 restore status OK @@ -81,14 +81,14 @@ k drop table t1,t2,t3,t4; restore table t1 from '../../tmp'; Table Op Msg_type Msg_text -test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t1 restore Warning 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK rename table t1 to t5; lock tables t5 write; backup table t5 to '../../tmp'; unlock tables; Table Op Msg_type Msg_text -test.t5 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t5 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t5 backup status OK drop table t5; DROP TABLE IF EXISTS `t+1`; @@ -96,12 +96,12 @@ CREATE TABLE `t+1` (c1 INT); INSERT INTO `t+1` VALUES (1), (2), (3); BACKUP TABLE `t+1` TO '../../tmp'; Table Op Msg_type Msg_text -test.t+1 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t+1 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t+1 backup status OK DROP TABLE `t+1`; RESTORE TABLE `t+1` FROM '../../tmp'; Table Op Msg_type Msg_text -test.t+1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t+1 restore Warning 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t+1 restore status OK SELECT * FROM `t+1`; c1 diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 4a5b8fcf4aa..8f9b75fc664 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -404,3 +404,37 @@ describe t1; Field Type Null Key Default Extra bi decimal(19,0) NO 0 drop table t1; +# +# Bug #45360: wrong results +# +CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, +a BIGINT(20) UNSIGNED, +b VARCHAR(20)); +INSERT INTO t1 (a) VALUES +(0), +(CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)), +(CAST(0x8000000000000000 AS UNSIGNED)), +(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)); +UPDATE t1 SET b = a; +# FFFFFFFFFFFFFFFF +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 18446744073709551615 AND TRIM(a) = b; +SHOW WARNINGS; +Level Code Message +Note 1003 select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 18446744073709551615) and ('18446744073709551615' = `test`.`t1`.`b`)) +# 8000000000000000 +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 9223372036854775808 AND TRIM(a) = b; +SHOW WARNINGS; +Level Code Message +Note 1003 select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 9223372036854775808) and ('9223372036854775808' = `test`.`t1`.`b`)) +# 7FFFFFFFFFFFFFFF +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 9223372036854775807 AND TRIM(a) = b; +SHOW WARNINGS; +Level Code Message +Note 1003 select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 9223372036854775807) and ('9223372036854775807' = `test`.`t1`.`b`)) +# 0 +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 0 AND TRIM(a) = b; +SHOW WARNINGS; +Level Code Message +Note 1003 select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 0) and ('0' = `test`.`t1`.`b`)) +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/bug39022.result b/mysql-test/r/bug39022.result new file mode 100644 index 00000000000..5963709aa2a --- /dev/null +++ b/mysql-test/r/bug39022.result @@ -0,0 +1,32 @@ +# +# Bug #39022: Mysql randomly crashing in lock_sec_rec_cons_read_sees +# +CREATE TABLE t1(a TINYINT NOT NULL,b TINYINT,PRIMARY KEY(b)) ENGINE=innodb; +CREATE TABLE t2(d TINYINT NOT NULL,UNIQUE KEY(d)) ENGINE=innodb; +INSERT INTO t1 VALUES (13,0),(8,1),(9,2),(6,3), +(11,5),(11,6),(7,7),(7,8),(4,9),(6,10),(3,11),(11,12), +(12,13),(7,14); +INSERT INTO t2 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10), +(11),(12),(13),(14); +# in thread1 +START TRANSACTION; +# in thread2 +REPLACE INTO t2 VALUES (-17); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +d +# in thread1 +REPLACE INTO t1(a,b) VALUES (67,20); +# in thread2 +COMMIT; +START TRANSACTION; +REPLACE INTO t1(a,b) VALUES (65,-50); +REPLACE INTO t2 VALUES (-91); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +# in thread1 +# should not crash +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# in thread2 +d +# in thread1; +DROP TABLE t1,t2; diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result index f9563b89b76..796821a87bd 100644 --- a/mysql-test/r/compare.result +++ b/mysql-test/r/compare.result @@ -88,7 +88,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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 #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat('0',`test`.`t2`.`a`,'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a` +Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat('0',`test`.`t2`.`a`,'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a` DROP TABLE t1,t2; CREATE TABLE t1 (a TIMESTAMP); INSERT INTO t1 VALUES (NOW()),(NOW()),(NOW()); diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index 4b96f5a5ed0..e2eebdfc992 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5394,17 +5394,24 @@ select * from t1; ERROR HY000: File 'MYSQLD_DATADIR/test/t1.CSV' not found (Errcode: 2) unlock tables; drop table t1; -create table t1(a enum ('a') not null) engine=csv; -insert into t1 values (2); +CREATE TABLE t1 (e enum('foo','bar') NOT NULL) ENGINE = CSV; +INSERT INTO t1 VALUES(); +INSERT INTO t1 VALUES(default); +INSERT INTO t1 VALUES(0); Warnings: -Warning 1265 Data truncated for column 'a' at row 1 -select * from t1 limit 1; -ERROR HY000: Table 't1' is marked as crashed and should be repaired -repair table t1; -Table Op Msg_type Msg_text -test.t1 repair Warning Data truncated for column 'a' at row 1 -test.t1 repair status OK -select * from t1 limit 1; -a -drop table t1; +Warning 1265 Data truncated for column 'e' at row 1 +INSERT INTO t1 VALUES(3); +Warnings: +Warning 1265 Data truncated for column 'e' at row 1 +INSERT INTO t1 VALUES(-1); +Warnings: +Warning 1265 Data truncated for column 'e' at row 1 +SELECT * FROM t1; +e +foo +foo + + + +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/ctype_ldml.result b/mysql-test/r/ctype_ldml.result index d5585dcfad9..aa9fb478217 100644 --- a/mysql-test/r/ctype_ldml.result +++ b/mysql-test/r/ctype_ldml.result @@ -49,6 +49,15 @@ SELECT * FROM t1 WHERE LOWER(a)=LOWER('N'); a DROP TABLE t1; # +# Bug#51976 LDML collations issue (cyrillic example) +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci); +INSERT INTO t1 (a) VALUES ('Hello'); +SELECT a, UPPER(a), LOWER(a) FROM t1; +a UPPER(a) LOWER(a) +Hello HELLO hello +DROP TABLE t1; +# # Bug#43827 Server closes connections and restarts # CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci); diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result index 5b0d82407a2..9afffe4c3bc 100644 --- a/mysql-test/r/default.result +++ b/mysql-test/r/default.result @@ -180,7 +180,6 @@ insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:0 insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4); Warnings: Warning 1364 Field 'a' doesn't have a default value -Warning 1364 Field 'b' doesn't have a default value Warning 1364 Field 'c' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'e' doesn't have a default value @@ -193,7 +192,7 @@ a b c d e f g h i x two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 1 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 2 two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 3 - 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 + small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 drop table bug20691; create table t1 (id int not null); insert into t1 values(default); diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 58278492985..77b2071494d 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -278,6 +278,18 @@ DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 0, got 1 DROP TABLE t1; DROP FUNCTION f1; +# +# Bug #49552 : sql_buffer_result cause crash + not found records +# in multitable delete/subquery +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SET SESSION SQL_BUFFER_RESULT=1; +DELETE t1 FROM (SELECT SUM(a) a FROM t1) x,t1; +SET SESSION SQL_BUFFER_RESULT=DEFAULT; +SELECT * FROM t1; +a +DROP TABLE t1; End of 5.0 tests # # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, @@ -337,16 +349,4 @@ END | DELETE IGNORE FROM t1; ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. DROP TABLE t1; -# -# Bug #49552 : sql_buffer_result cause crash + not found records -# in multitable delete/subquery -# -CREATE TABLE t1(a INT); -INSERT INTO t1 VALUES (1),(2),(3); -SET SESSION SQL_BUFFER_RESULT=1; -DELETE t1 FROM (SELECT SUM(a) a FROM t1) x,t1; -SET SESSION SQL_BUFFER_RESULT=DEFAULT; -SELECT * FROM t1; -a -DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 5a1bf1a1290..b8db8b53e06 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -168,6 +168,23 @@ WHERE t1.a = d1.a; ERROR 42S22: Unknown column 'd1.a' in 'where clause' DROP TABLE t1; # +# Bug#48295: +# explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode +# +CREATE TABLE t1 (f1 INT); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +EXPLAIN EXTENDED SELECT 1 FROM t1 +WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SHOW WARNINGS; +Level Code Message +Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...)) +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1; +End of 5.0 tests. +# # Bug#37870: Usage of uninitialized value caused failed assertion. # create table t1 (dt datetime not null, t time not null); @@ -195,19 +212,18 @@ dt 2001-01-01 01:01:01 drop tables t1, t2; # -# Bug#48295: -# explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode +# Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query # -CREATE TABLE t1 (f1 INT); -SELECT @@session.sql_mode INTO @old_sql_mode; -SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; -EXPLAIN EXTENDED SELECT 1 FROM t1 -WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); -ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause -SHOW WARNINGS; -Level Code Message -Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause -Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...)) -SET SESSION sql_mode=@old_sql_mode; -DROP TABLE t1; +CREATE TABLE t1 (c int); +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (d int); +INSERT INTO t2 VALUES (NULL), (0); +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 select (select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` +DROP TABLE t1, t2; End of 5.1 tests. diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 360ba84c491..8b88f3138c7 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -611,6 +611,26 @@ WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) count(*) 0 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)); +INSERT INTO t1 VALUES +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'), +('awrd'); +SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE); +COUNT(*) +0 +DROP TABLE t1; # # Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with # fulltext search and row op @@ -619,4 +639,17 @@ CREATE TABLE t1(a CHAR(1),FULLTEXT(a)); SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1); 1 DROP TABLE t1; +# +# BUG#51866 - crash with repair by sort and fulltext keys +# +CREATE TABLE t1(a CHAR(4), FULLTEXT(a)); +INSERT INTO t1 VALUES('aaaa'); +SET myisam_sort_buffer_size=4; +Warnings: +Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4' +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/func_concat.result b/mysql-test/r/func_concat.result index c4c2b46c6c2..fae8979a6e7 100644 --- a/mysql-test/r/func_concat.result +++ b/mysql-test/r/func_concat.result @@ -130,4 +130,22 @@ SELECT @query; @query abcde,0,1234 DROP PROCEDURE p1; +# +# Bug #40625: Concat fails on DOUBLE values in a Stored Procedure, +# while DECIMAL works +# +CREATE PROCEDURE p1() +BEGIN +DECLARE v1 DOUBLE(10,3); +SET v1= 100; +SET @s = CONCAT('########################################', 40 , v1); +SELECT @s; +END;// +CALL p1(); +@s +########################################40100.000 +CALL p1(); +@s +########################################40100.000 +DROP PROCEDURE p1; # End of 5.1 tests diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 3b78851a1b9..766f3b6bfaa 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -978,4 +978,36 @@ GROUP BY t1.a 1 1 DROP TABLE t1, t2; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (),(); +EXPLAIN EXTENDED SELECT 1 FROM +(SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Distinct +2 DERIVED td ALL NULL NULL NULL NULL 2 100.00 Distinct; Using join buffer +Warnings: +Note 1003 select 1 AS `1` from (select distinct group_concat(`test`.`td`.`f1` separator ',') AS `GROUP_CONCAT(td.f1)` from `test`.`t1` join `test`.`t1` `td` group by `test`.`td`.`f1`) `d` join `test`.`t1` +SELECT 1 FROM +(SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1; +1 +1 +1 +DROP TABLE t1; +# +# Bug #52397: another crash with explain extended and group_concat +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (0); +EXPLAIN EXTENDED SELECT 1 FROM +(SELECT GROUP_CONCAT(t1.a ORDER BY t1.a ASC) FROM +t1 t2, t1 GROUP BY t1.a) AS d; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +Warnings: +Note 1003 select 1 AS `1` from (select group_concat(`test`.`t1`.`a` order by `test`.`t1`.`a` ASC separator ',') AS `GROUP_CONCAT(t1.a ORDER BY t1.a ASC)` from `test`.`t1` `t2` join `test`.`t1` group by `test`.`t1`.`a`) `d` +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index d144e84dfdc..4268268fabb 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1307,12 +1307,12 @@ explain extended select encode(f1,'zxcv') as 'enc' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select encode('','zxcv') AS `enc` from `test`.`t1` +Note 1003 select encode(NULL,'zxcv') AS `enc` from `test`.`t1` explain extended select decode(f1,'zxcv') as 'enc' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select decode('','zxcv') AS `enc` from `test`.`t1` +Note 1003 select decode(NULL,'zxcv') AS `enc` from `test`.`t1` drop table t1; create table t1 (a bigint not null)engine=myisam; insert into t1 set a = 1024*1024*1024*4; @@ -2587,3 +2587,17 @@ FROM t2 WHERE t2.b = 1 GROUP BY t2.b; DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a) secret DROP TABLE t1, t2; +# +# Bug#52164 Assertion failed: param.sort_length, file .\filesort.cc, line 149 +# +CREATE TABLE t1 (a LONGBLOB NOT NULL); +INSERT INTO t1 VALUES (''),(''); +SELECT 1 FROM t1, t1 t2 +ORDER BY QUOTE(t1.a); +1 +1 +1 +1 +1 +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 22c8a6e4b6f..8b96a60b79c 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -682,7 +682,7 @@ select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1; timestampadd(SQL_TSI_FRAC_SECOND, 1, date) 2003-01-02 00:00:00.000001 Warnings: -Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 5.6. Please use MICROSECOND instead +Warning 1287 'FRAC_SECOND' is deprecated and will be removed in a future release. Please use MICROSECOND instead select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a; a 3 @@ -717,7 +717,7 @@ select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05 a 7689538999999 Warnings: -Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 5.6. Please use MICROSECOND instead +Warning 1287 'FRAC_SECOND' is deprecated and will be removed in a future release. Please use MICROSECOND instead select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1, timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2, timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3, @@ -1088,7 +1088,7 @@ timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12: id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 5.6. Please use MICROSECOND instead +Warning 1287 'FRAC_SECOND' is deprecated and will be removed in a future release. Please use MICROSECOND instead Note 1003 select timestampdiff(WEEK,'2001-02-01','2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,'2001-02-01 12:59:59.120000','2001-05-01 12:58:58.119999') AS `a2` select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') @@ -1287,12 +1287,12 @@ SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18'); TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18') 2008-02-18 00:00:00.000001 Warnings: -Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 5.6. Please use MICROSECOND instead +Warning 1287 'FRAC_SECOND' is deprecated and will be removed in a future release. Please use MICROSECOND instead SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18'); TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18') 86400000000 Warnings: -Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 5.6. Please use MICROSECOND instead +Warning 1287 'FRAC_SECOND' is deprecated and will be removed in a future release. Please use MICROSECOND instead SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND); 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 'FRAC_SECOND)' at line 1 SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND); diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 4fa2f63598c..a28f537b2de 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -1076,4 +1076,26 @@ SELECT 1 FROM t1 WHERE a >= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1 1 DROP TABLE t1; +# +# Bug #51357: crash when using handler commands on spatial indexes +# +CREATE TABLE t1(a GEOMETRY NOT NULL,SPATIAL INDEX a(a)); +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +a +HANDLER t1 READ a NEXT; +a +HANDLER t1 READ a PREV; +a +HANDLER t1 READ a LAST; +a +HANDLER t1 CLOSE; +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +a +INSERT INTO t1 VALUES (GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))')); +# should not crash +HANDLER t1 READ a NEXT; +HANDLER t1 CLOSE; +DROP TABLE t1; End of 5.0 tests. diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 742d4b90807..90503300065 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1703,3 +1703,91 @@ COUNT(i) 1 DROP TABLE t1; SET @@sql_mode = @old_sql_mode; +# +# Bug #45640: optimizer bug produces wrong results +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30); +# should return 4 ordered records: +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; +aa COUNT(DISTINCT b) +1 1 +2 1 +3 1 +4 1 +SELECT (SELECT (SELECT t1.a)) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; +aa COUNT(DISTINCT b) +1 1 +2 1 +3 1 +4 1 +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; +aa COUNT(DISTINCT b) +1 1 +2 1 +3 1 +4 1 +# should return the same result in a reverse order: +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; +aa COUNT(DISTINCT b) +4 1 +3 1 +2 1 +1 1 +# execution plan should not use temporary table: +EXPLAIN EXTENDED +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; +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 filesort +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select (select `test`.`t1`.`a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by ((select `test`.`t1`.`a`) + 0) +EXPLAIN EXTENDED +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; +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 filesort +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select (select `test`.`t1`.`a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by -((select `test`.`t1`.`a`)) +# should return only one record +SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1 +GROUP BY aa; +aa COUNT(DISTINCT b) +4 4 +CREATE TABLE t2 SELECT DISTINCT a FROM t1; +# originally reported queries (1st two columns of next two query +# results should be same): +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) +FROM t1 GROUP BY aa, b; +aa b COUNT(DISTINCT b) +1 10 1 +2 20 1 +3 30 1 +4 40 1 +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) +FROM t1 GROUP BY aa, b; +aa b COUNT( b) +1 10 1 +2 20 2 +3 30 1 +4 40 1 +# ORDER BY for sure: +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) +FROM t1 GROUP BY aa, b ORDER BY -aa, -b; +aa b COUNT(DISTINCT b) +4 40 1 +3 30 1 +2 20 1 +1 10 1 +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) +FROM t1 GROUP BY aa, b ORDER BY -aa, -b; +aa b COUNT( b) +4 40 1 +3 30 1 +2 20 2 +1 10 1 +DROP TABLE t1, t2; +# +# End of 5.1 tests diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 662cc5be034..01f27a498ef 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2524,4 +2524,247 @@ SELECT a, MAX(b) FROM t WHERE b GROUP BY a; a MAX(b) 2 1 DROP TABLE t; +CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)); +INSERT INTO t1 VALUES(1,1),(2,1); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b; +c b +1 1 +SELECT a FROM t1 WHERE b=1; +a +1 +2 +DROP TABLE t1; +# +# Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column +# for NULL +# +## Test for NULLs allowed +CREATE TABLE t1 ( a INT, KEY (a) ); +INSERT INTO t1 VALUES (1), (2), (3); +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a = NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a = NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a <> NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a <> NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a > NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a > NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a < NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a < NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a <=> NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x No matching min/max row +SELECT MIN( a ) FROM t1 WHERE a <=> NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index +SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x No matching min/max row +SELECT MIN( a ) FROM t1 WHERE a IS NULL; +MIN( a ) +NULL +INSERT INTO t1 VALUES (NULL), (NULL); +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a = NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a = NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a <> NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a <> NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a > NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a > NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a < NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a < NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a <=> NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Select tables optimized away +SELECT MIN( a ) FROM t1 WHERE a <=> NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index +SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Select tables optimized away +SELECT MIN( a ) FROM t1 WHERE a IS NULL; +MIN( a ) +NULL +DROP TABLE t1; +## Test for NOT NULLs +CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES (1), (2), (3); +# +# NULL-safe operator test disabled for non-NULL indexed columns. +# +# See bugs +# +# - Bug#52173: Reading NULL value from non-NULL index gives +# wrong result in embedded server +# +# - Bug#52174: Sometimes wrong plan when reading a MAX value from +# non-NULL index +# +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a = NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a = NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a <> NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a <> NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a > NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a > NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a < NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a < NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index +SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); +MIN( a ) +NULL +EXPLAIN +SELECT MIN( a ) FROM t1 WHERE a IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x Impossible WHERE +SELECT MIN( a ) FROM t1 WHERE a IS NULL; +MIN( a ) +NULL +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/handler_myisam.result b/mysql-test/r/handler_myisam.result index 90a1bdfe6be..a970e20a2c0 100644 --- a/mysql-test/r/handler_myisam.result +++ b/mysql-test/r/handler_myisam.result @@ -756,4 +756,17 @@ TRUNCATE t1; HANDLER t1 READ FIRST; ERROR 42S02: Unknown table 't1' in HANDLER DROP TABLE t1; +# +# BUG#51877 - HANDLER interface causes invalid memory read +# +CREATE TABLE t1(a INT, KEY(a)); +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +a +INSERT INTO t1 VALUES(1); +HANDLER t1 READ a NEXT; +a +1 +HANDLER t1 CLOSE; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 9c3cc8fc89e..54293e9d02e 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -12,7 +12,7 @@ explain extended select count(a) as b from t1 where a=0 having b >=0; 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 noticed after reading const tables Warnings: -Note 1003 select count('0') AS `b` from `test`.`t1` where 0 having (`b` >= 0) +Note 1003 select count(NULL) AS `b` from `test`.`t1` where 0 having (`b` >= 0) drop table t1; CREATE TABLE t1 ( raw_id int(10) NOT NULL default '0', @@ -430,4 +430,103 @@ SELECT b, COUNT(DISTINCT a) FROM t1 GROUP BY b HAVING b is NULL; b COUNT(DISTINCT a) NULL 1 DROP TABLE t1; +# +# Bug#50995 Having clause on subquery result produces incorrect results. +# +CREATE TABLE t1 +( +id1 INT, +id2 INT NOT NULL, +INDEX id1(id2) +); +INSERT INTO t1 SET id1=1, id2=1; +INSERT INTO t1 SET id1=2, id2=1; +INSERT INTO t1 SET id1=3, id2=1; +SELECT t1.id1, +(SELECT 0 FROM DUAL +WHERE t1.id1=t1.id1) AS amount FROM t1 +WHERE t1.id2 = 1 +HAVING amount > 0 +ORDER BY t1.id1; +id1 amount +DROP TABLE t1; +# +# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause +# +CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT); +INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9); +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); +f1 f2 +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); +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 HAVING noticed after reading const tables +Warnings: +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having 0 +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8); +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 HAVING noticed after reading const tables +Warnings: +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having 0 +DROP TABLE t1; +# +# Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355 +# +CREATE TABLE t1(f1 INT, f2 INT); +INSERT INTO t1 VALUES (10,8); +CREATE TABLE t2 (f1 INT); +INSERT INTO t2 VALUES (5); +SELECT COUNT(f1) FROM t2 +HAVING (7, 9) IN (SELECT f1, MIN(f2) FROM t1); +COUNT(f1) +DROP TABLE t1, t2; +CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t1 VALUES (16,'f'); +INSERT INTO t1 VALUES (16,'f'); +CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t2 VALUES (13,'f'); +INSERT INTO t2 VALUES (20,'f'); +CREATE TABLE t3 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t3 VALUES (7,'f'); +SELECT t1.f2 FROM t1 +STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2 +HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1) +ORDER BY f2; +f2 +f +f +f +f +DROP TABLES t1,t2,t3; +# +# Bug#52340 Segfault: read_cached_record (tab=0x94a2634) at sql_select.cc:14411 +# +CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t1 VALUES (16,'d'); +CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t2 VALUES (13,'e'); +INSERT INTO t2 VALUES (20,'d'); +SELECT MAX(t2.f2) FROM t2 JOIN t1 ON t1.f2 +HAVING ('e' , 'd') IN +(SELECT ts1.f2, ts2.f2 FROM t2 ts1 JOIN t2 ts2 ON ts1.f1) +ORDER BY t1.f2; +MAX(t2.f2) +NULL +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index ccd047271f6..5300e6cb399 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -1155,3 +1155,86 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL); +SELECT * FROM t1; +c1 +-685113344 +1 +2 +3 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL); +SELECT * FROM t1; +c1 +-685113344 +2 +3 +4 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL); +INSERT INTO t1 VALUES (4), (5), (6), (NULL); +SELECT * FROM t1; +c1 +-685113344 +1 +2 +3 +4 +5 +6 +7 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5); +SELECT * FROM t1; +c1 +-685113344 +1 +2 +5 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); +SELECT * FROM t1; +c1 +-685113344 +1 +2 +3 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/r/innodb_bug47621.result b/mysql-test/r/innodb_bug47621.result new file mode 100644 index 00000000000..c5f56c09788 --- /dev/null +++ b/mysql-test/r/innodb_bug47621.result @@ -0,0 +1,21 @@ +CREATE TABLE bug47621 (salesperson INT) ENGINE=InnoDB; +ALTER TABLE bug47621 CHANGE salesperson sales_acct_id INT; +create index orgs on bug47621(sales_acct_id); +ALTER TABLE bug47621 CHANGE sales_acct_id salesperson INT; +drop table bug47621; +CREATE TABLE bug47621_sale ( +salesperson INT, +PRIMARY KEY(salesperson)) engine = innodb; +CREATE TABLE bug47621_shirt( +id SMALLINT, +owner INT, +FOREIGN KEY(owner) +references bug47621_sale(salesperson) ON DELETE RESTRICT) +engine = innodb; +insert into bug47621_sale values(9); +insert into bug47621_shirt values(1, 9); +ALTER TABLE bug47621_shirt CHANGE id new_id INT; +drop table bug47621_shirt; +ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; +ALTER TABLE bug47621_sale ADD INDEX idx (sales_acct_id); +drop table bug47621_sale; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 402ab3c1b16..0e691611f02 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -2273,4 +2273,81 @@ END| DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; +# +# Bug #49324: more valgrind errors in test_if_skip_sort_order +# +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ; +#should not cause valgrind warnings +SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a; +1 +DROP TABLE t1; +# +# Bug#50843: Filesort used instead of clustered index led to +# performance degradation. +# +create table t1(f1 int not null primary key, f2 int) engine=innodb; +create table t2(f1 int not null, key (f1)) engine=innodb; +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1),(2),(3); +explain select t1.* from t1 left join t2 using(f1) group by t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 +1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index +drop table t1,t2; +# +# +# Bug #39653: find_shortest_key in sql_select.cc does not consider +# clustered primary keys +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, +KEY (b,c)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), +(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), +(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), +(11,11,11,11,11,11); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 10 +ref NULL +rows 10 +Extra Using index +DROP TABLE t1; +# +# Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may +# corrupt definition at engine +# +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) +ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b); +SHOW INDEXES FROM t1;; +Table t1 +Non_unique 0 +Key_name k +Seq_in_index 1 +Column_name a +Collation A +Cardinality 0 +Sub_part NULL +Packed NULL +Null +Index_type BTREE +Comment +Table t1 +Non_unique 0 +Key_name k +Seq_in_index 2 +Column_name b +Collation A +Cardinality 0 +Sub_part NULL +Packed NULL +Null +Index_type BTREE +Comment +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index baabf48cb2f..940b3b4102e 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1117,6 +1117,35 @@ ON t4.a = t5.a ON t1.a = t3.a; a a a a a a DROP TABLE t1,t2,t3,t4,t5,t6; +# +# Bug#48483: crash in get_best_combination() +# +CREATE TABLE t1(f1 INT); +INSERT INTO t1 VALUES (1),(2); +CREATE VIEW v1 AS SELECT 1 FROM t1 LEFT JOIN t1 AS t2 on 1=1; +EXPLAIN EXTENDED +SELECT 1 FROM v1 right join v1 AS v2 ON RAND(); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select 1 AS `1` from `test`.`t1` left join `test`.`t1` `t2` on((1 = 1)) left join (`test`.`t1` left join `test`.`t1` `t2` on((1 = 1))) on(rand()) where 1 +DROP VIEW v1; +DROP TABLE t1; +# +# Bug#52177 crash with explain, row comparison, join, text field +# +CREATE TABLE t1 (a TINYINT, b TEXT, KEY (a)); +INSERT INTO t1 VALUES (0,''),(0,''); +FLUSH TABLES; +EXPLAIN SELECT 1 FROM t1 LEFT JOIN t1 a ON 1 +WHERE ROW(t1.a, 1111.11) = ROW(1111.11, 1111.11) AND +ROW(t1.b, 1111.11) <=> ROW('',''); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1; End of 5.0 tests. CREATE TABLE t1 (f1 int); CREATE TABLE t2 (f1 int); @@ -1128,3 +1157,31 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP VIEW v1; DROP TABLE t1, t2; +CREATE TABLE t1(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM; +CREATE TABLE t2(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM; +INSERT INTO t1 VALUES ('1',null),(null,null); +INSERT INTO t2 VALUES ('1',null),(null,null); +CREATE TABLE mm1(a CHAR(9),b INT,KEY(b),KEY(a)) +ENGINE=MERGE UNION=(t1,t2); +SELECT t1.a FROM mm1,t1; +a +NULL +1 +NULL +1 +NULL +1 +NULL +1 +DROP TABLE t1, t2, mm1; +# +# Bug #50335: Assertion `!(order->used & map)' in eq_ref_table +# +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (0,0), (1,1); +SELECT * FROM t1 STRAIGHT_JOIN t1 t2 ON t1.a=t2.a AND t1.a=t2.b ORDER BY t2.a, t1.a; +a b a b +0 0 0 0 +1 1 1 1 +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 083be3737f7..4543446e807 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1289,3 +1289,23 @@ a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) 1 3 6 3 NULL 3 6 3 DROP TABLE t1, t2; +# +# Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison +# +CREATE TABLE t1(f1 INT, f2 INT, f3 INT); +INSERT INTO t1 VALUES (1, NULL, 3); +CREATE TABLE t2(f1 INT, f2 INT); +INSERT INTO t2 VALUES (2, 1); +EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 +WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '1' AS `f1`,NULL AS `f2`,'3' AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t1` left join `test`.`t2` on(multiple equal(NULL)) where ((coalesce('1',NULL),'3') in ((1,3),(2,2))) +SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 +WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); +f1 f2 f3 f1 f2 +1 NULL 3 NULL NULL +DROP TABLE t1, t2; +End of 5.1 tests diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 1e488b320d7..ef206565db5 100644..100755 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -456,6 +456,13 @@ LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") set session sql_mode=@OLD_SQL_MODE; DROP TABLE t1,t2; +# +# Bug #51893: crash with certain characters given to load_file +# function on windows +# +select load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D1428AAB5CCA72E75AA2EF325E196A5A02E2E8278873C64375845994B0F39BE2FF7B478332A7B0AA5E48877C47B6F513E997848AF8CCB8A899F3393AB35333CF0871E36698193862D486B4B9078B70C0A0A507B8A250F3F876F5A067632D5E65193E4445A1EC3A2C9B4C6F07AC334F0F62BC33357CB502E9B1C19D2398B6972AEC2EF21630F8C9134C4F7DD662D8AD7BDC9E19C46720F334B66C22D4BF32ED275144E20E7669FFCF6FC143667C9F02A577F32960FA9F2371BE1FA90E49CBC69C01531F140556854D588DD0E55E1307D78CA38E975CD999F9AEA604266329EE62BFB5ADDA67F549E211ECFBA906C60063696352ABB82AA782D25B17E872EA587871F450446DB1BAE0123D20404A8F2D2698B371002E986C8FCB969A99FF0E150A2709E2ED7633D02ADA87D5B3C9487D27B2BD9D21E2EC3215DCC3CDCD884371281B95A2E9987AAF82EB499C058D9C3E7DC1B66635F60DB121C72F929622DD47B6B2E69F59FF2AE6B63CC2EC60FFBA20EA50569DBAB5DAEFAEB4F03966C9637AB55662EDD28439155A82D053A5299448EDB2E7BEB0F62889E2F84E6C7F34B3212C9AAC32D521D5AB8480993F1906D5450FAB342A0FA6ED223E178BAC036B81E15783604C32A961EA1EF20BE2EBB93D34ED37BC03142B7583303E4557E48551E4BD7CBDDEA146D5485A5D212C35189F0BD6497E66912D2780A59A53B532E12C0A5ED1EC0445D96E8F2DD825221CFE4A65A87AA21DC8750481B9849DD81694C3357A0ED9B78D608D8EDDE28FAFBEC17844DE5709F41E121838DB55639D77E32A259A416D7013B2EB1259FDE1B498CBB9CAEE1D601DF3C915EA91C69B44E6B72062F5F4B3C73F06F2D5AD185E1692E2E0A01E7DD5133693681C52EE13B2BE42D03BDCF48E4E133CF06662339B778E1C3034F9939A433E157449172F7969ACCE1F5D2F65A4E09E4A5D5611EBEDDDBDB0C0C0A); +load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D142 +NULL End of 5.0 tests CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1); @@ -484,4 +491,15 @@ SET character_set_filesystem=default; select @@character_set_filesystem; @@character_set_filesystem binary +# +# Bug #51850: crash/memory overlap when using load data infile and set +# col equal to itself! +# +CREATE TABLE t1(col0 LONGBLOB); +SELECT 'test' INTO OUTFILE 't1.txt'; +LOAD DATA INFILE 't1.txt' IGNORE INTO TABLE t1 SET col0=col0; +SELECT * FROM t1; +col0 +test +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 5c3e3d789a1..4ce678e37aa 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -199,7 +199,7 @@ SELECT @@general_log, @@log; 1 1 SET GLOBAL log = 0; Warnings: -Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead +Warning 1287 '@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead SHOW VARIABLES LIKE 'general_log'; Variable_name Value general_log OFF @@ -230,7 +230,7 @@ SELECT @@slow_query_log, @@log_slow_queries; 0 0 SET GLOBAL log_slow_queries = 0; Warnings: -Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead +Warning 1287 '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead SHOW VARIABLES LIKE 'slow_query_log'; Variable_name Value slow_query_log OFF @@ -283,16 +283,16 @@ SET GLOBAL slow_query_log_file = @old_slow_query_log_file; deprecated: SET GLOBAL log = 0; Warnings: -Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead +Warning 1287 '@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead SET GLOBAL log_slow_queries = 0; Warnings: -Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead +Warning 1287 '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead SET GLOBAL log = DEFAULT; Warnings: -Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead +Warning 1287 '@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead SET GLOBAL log_slow_queries = DEFAULT; Warnings: -Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead +Warning 1287 '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead not deprecated: SELECT @@global.general_log_file INTO @my_glf; SELECT @@global.slow_query_log_file INTO @my_sqlf; diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result new file mode 100644 index 00000000000..5d9be85a48a --- /dev/null +++ b/mysql-test/r/log_tables_upgrade.result @@ -0,0 +1,47 @@ +# +# Bug#49823: mysql_upgrade fatal error due to general_log / slow_low CSV NULL +# +USE test; +SET @saved_general_log = @@GLOBAL.general_log; +SET GLOBAL general_log = OFF; +USE mysql; +FLUSH TABLES; +REPAIR TABLE test.bug49823; +Table Op Msg_type Msg_text +test.bug49823 repair status OK +RENAME TABLE general_log TO renamed_general_log; +RENAME TABLE test.bug49823 TO general_log; +mtr.global_suppressions OK +mtr.test_suppressions OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.general_log +Error : You can't use locks with log tables. +status : OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.ndb_binlog_index OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.renamed_general_log OK +mysql.servers OK +mysql.slow_log +Error : You can't use locks with log tables. +status : OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +DROP TABLE general_log; +RENAME TABLE renamed_general_log TO general_log; +SET GLOBAL general_log = @saved_general_log; +USE test; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index e3633404db1..83dae077312 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -2171,4 +2171,83 @@ Trigger sql_mode SQL Original Statement character_set_client collation_connectio tr1 CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo() latin1 latin1_swedish_ci latin1_swedish_ci DROP TRIGGER tr1; DROP TABLE t1, t2, t3; +# +# BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40) +# +CREATE DATABASE `test/1`; +CREATE TABLE `test/1`.`t/1`(a INT); +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM m1; +a +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test/1`.`t/1`) +DROP TABLE m1; +CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM `test/1`.m1; +a +SHOW CREATE TABLE `test/1`.m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t/1`) +DROP TABLE `test/1`.m1; +DROP TABLE `test/1`.`t/1`; +CREATE TEMPORARY TABLE `test/1`.`t/1`(a INT); +CREATE TEMPORARY TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM m1; +a +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test/1`.`t/1`) +DROP TABLE m1; +CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM `test/1`.m1; +a +SHOW CREATE TABLE `test/1`.m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t/1`) +DROP TABLE `test/1`.m1; +DROP TABLE `test/1`.`t/1`; +DROP DATABASE `test/1`; +CREATE TABLE `t@1`(a INT); +SELECT * FROM m1; +a +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t@1`) +DROP TABLE `t@1`; +CREATE DATABASE `test@1`; +CREATE TABLE `test@1`.`t@1`(a INT); +FLUSH TABLE m1; +SELECT * FROM m1; +a +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test@1`.`t@1`) +DROP TABLE m1; +DROP TABLE `test@1`.`t@1`; +DROP DATABASE `test@1`; +# +# Bug#51494c rash with join, explain and 'sounds like' operator +# +CREATE TABLE t1 (a INT) ENGINE=MYISAM; +INSERT INTO t1 VALUES(1); +CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL, +KEY idx0 (d, c)) ENGINE=MERGE; +EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN +t2 WHERE b SOUNDS LIKE e AND d = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t2, t1; End of 5.1 tests diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 6b498e55d85..2f9fb6b67f5 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -198,4 +198,15 @@ def IF(i, d, d) IF(i, d, d) 10 10 10 Y 128 0 63 def IFNULL(d, d) IFNULL(d, d) 10 10 10 Y 128 0 63 def LEAST(d, d) LEAST(d, d) 10 10 10 Y 128 0 63 DROP TABLE t1; +# +# Bug#41788 mysql_fetch_field returns org_table == table by a view +# +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1; +SELECT f1 FROM v1 va; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test v1 va f1 f1 3 11 0 Y 32768 0 63 +f1 +DROP VIEW v1; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 449333a4ae6..04bf7720c43 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -634,4 +634,15 @@ select count(*) from t3 /* must be 1 */; count(*) 1 drop table t1, t2, t3; +# +# Bug#49534: multitable IGNORE update with sql_safe_updates error +# causes debug assertion +# +CREATE TABLE t1( a INT, KEY( a ) ); +INSERT INTO t1 VALUES (1), (2), (3); +SET SESSION sql_safe_updates = 1; +# Must not cause failed assertion +UPDATE IGNORE t1, t1 t1a SET t1.a = 1 WHERE t1a.a = 1; +ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column +DROP TABLE t1; end of tests diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index ef38f221a97..ab5f52ed876 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1868,6 +1868,19 @@ CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 467455460 DROP TABLE t1; +# +# BUG#48438 - crash with error in unioned query against merge table and view... +# +SET GLOBAL table_open_cache=3; +CREATE TABLE t1(a INT); +SELECT 1 FROM t1 AS a1, t1 AS a2, t1 AS a3, t1 AS a4 FOR UPDATE; +1 +SELECT TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +TABLE_ROWS DATA_LENGTH +0 0 +DROP TABLE t1; +SET GLOBAL table_open_cache=DEFAULT; End of 5.0 tests create table t1 (a int not null, key `a` (a) key_block_size=1024); show create table t1; @@ -2331,6 +2344,8 @@ INSERT INTO t1 SELECT a+1280,b FROM t1; INSERT INTO t1 SELECT a+2560,b FROM t1; INSERT INTO t1 SELECT a+5120,b FROM t1; SET myisam_sort_buffer_size=4; +Warnings: +Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4' REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair error myisam_sort_buffer_size is too small @@ -2344,4 +2359,88 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +# +# BUG#49628 - corrupt table after legal SQL, LONGTEXT column +# +CREATE TABLE t1(a INT, b LONGTEXT, UNIQUE(a)); +REPLACE INTO t1 VALUES +(1, REPEAT('a', 129015)),(1, NULL), +(2, NULL),(3, NULL),(4, NULL),(5, NULL),(6, NULL),(7, NULL), +(1, REPEAT('b', 129016)),(1, NULL), +(1, REPEAT('c', 129015)),(1, REPEAT('d', 129015)); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +# +# Bug#51304: checksum table gives different results +# for same data when using bit fields +# +CREATE TABLE t1(a INT, b BIT(1)); +INSERT INTO t1 VALUES(1, 0), (2, 1); +CREATE TABLE t2 SELECT * FROM t1; +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 3775188275 +CHECKSUM TABLE t2 EXTENDED; +Table Checksum +test.t2 3775188275 +CHECKSUM TABLE t3 EXTENDED; +Table Checksum +test.t3 3775188275 +DROP TABLE t1, t2, t3; +# +# BUG#51307 - widespread corruption with partitions and insert...select +# +CREATE TABLE t1(a CHAR(255), KEY(a)); +SELECT * FROM t1, t1 AS a1; +a a +SET myisam_sort_buffer_size=4; +Warnings: +Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4' +INSERT INTO t1 VALUES +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'); +Warnings: +Error 1034 myisam_sort_buffer_size is too small +Error 1034 Number of rows changed from 0 to 157 +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +INSERT INTO t1 VALUES('1'); +SELECT * FROM t1, t1 AS a1 WHERE t1.a=1 AND a1.a=1; +a a +1 1 +DROP TABLE t1; +# +# BUG#47444 - --myisam_repair_threads>1can result in all index +# cardinalities=1 +# +SET myisam_repair_threads=2; +SET myisam_sort_buffer_size=4096; +CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0),(1),(2),(3); +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +CARDINALITY +14 +14 +14 +DROP TABLE t1; +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +SET myisam_repair_threads=@@global.myisam_repair_threads; End of 5.1 tests diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index b7aa981f834..69d19b5e87f 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -479,7 +479,7 @@ FLUSH LOGS; RESET MASTER; FLUSH LOGS; # -# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified exists /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; @@ -532,7 +532,7 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; # -# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified does not exist /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; @@ -565,5 +565,76 @@ DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +# +# Test if the 'SAVEPOINT', 'ROLLBACK TO' are output if the database specified exists +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +SET TIMESTAMP=1266652094/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +BEGIN +/*!*/; +use test/*!*/; +SET TIMESTAMP=1266652094/*!*/; +SavePoint mixed_cases +/*!*/; +use db1/*!*/; +SET TIMESTAMP=1266652094/*!*/; +INSERT INTO db1.t2 VALUES("in savepoint mixed_cases") +/*!*/; +SET TIMESTAMP=1266652094/*!*/; +INSERT INTO db1.t1 VALUES(40) +/*!*/; +use test/*!*/; +SET TIMESTAMP=1266652094/*!*/; +ROLLBACK TO mixed_cases +/*!*/; +use db1/*!*/; +SET TIMESTAMP=1266652094/*!*/; +INSERT INTO db1.t2 VALUES("after rollback to") +/*!*/; +SET TIMESTAMP=1266652094/*!*/; +INSERT INTO db1.t1 VALUES(50) +/*!*/; +COMMIT/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +# +# Test if the 'SAVEPOINT', 'ROLLBACK TO' are output if the database specified does not exist +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +SET TIMESTAMP=1266652094/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +BEGIN +/*!*/; +use test/*!*/; +SET TIMESTAMP=1266652094/*!*/; +SavePoint mixed_cases +/*!*/; +SET TIMESTAMP=1266652094/*!*/; +ROLLBACK TO mixed_cases +/*!*/; +COMMIT/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; End of 5.0 tests End of 5.1 tests diff --git a/mysql-test/r/mysqlbinlog_row_innodb.result b/mysql-test/r/mysqlbinlog_row_innodb.result index 86f0b67ebb3..ee448311278 100644 --- a/mysql-test/r/mysqlbinlog_row_innodb.result +++ b/mysql-test/r/mysqlbinlog_row_innodb.result @@ -2365,7 +2365,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F ### INSERT INTO test.t1 ### SET -### @1=b'00000000' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'0' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'0000000000000000000000000000000000000000000000000000000000000000' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=-128 (128) /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -2458,7 +2458,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F ### INSERT INTO test.t1 ### SET -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=-1 (255) /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -2553,7 +2553,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F ### INSERT INTO test.t1 ### SET -### @1=NULL /* type=16 meta=256 nullable=1 is_null=1 */ +### @1=NULL /* type=16 meta=1 nullable=1 is_null=1 */ ### @2=NULL /* type=16 meta=2048 nullable=1 is_null=1 */ ### @3=NULL /* type=1 meta=0 nullable=1 is_null=1 */ ### @4=NULL /* type=1 meta=0 nullable=1 is_null=1 */ @@ -2634,7 +2634,7 @@ BEGIN ### @79=3 /* INT meta=0 nullable=1 is_null=0 */ ### INSERT INTO test.t1 ### SET -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -2727,7 +2727,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F ### UPDATE test.t1 ### WHERE -### @1=b'00000000' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'0' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'0000000000000000000000000000000000000000000000000000000000000000' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=-128 (128) /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -2807,7 +2807,7 @@ BEGIN ### @78=b'00000000' /* SET(1 bytes) meta=63489 nullable=1 is_null=0 */ ### @79=1 /* INT meta=0 nullable=1 is_null=0 */ ### SET -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=-1 (255) /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -2900,7 +2900,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F ### UPDATE test.t1 ### WHERE -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=-1 (255) /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -2980,7 +2980,7 @@ BEGIN ### @78=b'00000111' /* SET(1 bytes) meta=63489 nullable=1 is_null=0 */ ### @79=2 /* INT meta=0 nullable=1 is_null=0 */ ### SET -### @1=b'00000000' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'0' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'0000000000000000000000000000000000000000000000000000000000000000' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=-128 (128) /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -3073,7 +3073,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F ### UPDATE test.t1 ### WHERE -### @1=NULL /* type=16 meta=256 nullable=1 is_null=1 */ +### @1=NULL /* type=16 meta=1 nullable=1 is_null=1 */ ### @2=NULL /* type=16 meta=2048 nullable=1 is_null=1 */ ### @3=NULL /* type=1 meta=0 nullable=1 is_null=1 */ ### @4=NULL /* type=1 meta=0 nullable=1 is_null=1 */ @@ -3153,7 +3153,7 @@ BEGIN ### @78=NULL /* TIMESTAMP meta=63489 nullable=1 is_null=1 */ ### @79=3 /* INT meta=0 nullable=1 is_null=0 */ ### SET -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -3246,7 +3246,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Update_rows: table id # flags: STMT_END_F ### UPDATE test.t1 ### WHERE -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -3326,7 +3326,7 @@ BEGIN ### @78=b'00000110' /* SET(1 bytes) meta=63489 nullable=1 is_null=0 */ ### @79=4 /* INT meta=0 nullable=1 is_null=0 */ ### SET -### @1=NULL /* type=16 meta=256 nullable=1 is_null=1 */ +### @1=NULL /* type=16 meta=1 nullable=1 is_null=1 */ ### @2=NULL /* type=16 meta=2048 nullable=1 is_null=1 */ ### @3=NULL /* type=1 meta=0 nullable=1 is_null=1 */ ### @4=NULL /* type=1 meta=0 nullable=1 is_null=1 */ @@ -3419,7 +3419,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F ### DELETE FROM test.t1 ### WHERE -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=-1 (255) /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -3512,7 +3512,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F ### DELETE FROM test.t1 ### WHERE -### @1=b'00000000' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'0' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'0000000000000000000000000000000000000000000000000000000000000000' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=-128 (128) /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -3605,7 +3605,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F ### DELETE FROM test.t1 ### WHERE -### @1=b'00000001' /* BIT(8) meta=256 nullable=1 is_null=0 */ +### @1=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ ### @2=b'1111111111111111111111111111111111111111111111111111111111111111' /* BIT(64) meta=2048 nullable=1 is_null=0 */ ### @3=127 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* TINYINT meta=0 nullable=1 is_null=0 */ @@ -3698,7 +3698,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F ### DELETE FROM test.t1 ### WHERE -### @1=NULL /* type=16 meta=256 nullable=1 is_null=1 */ +### @1=NULL /* type=16 meta=1 nullable=1 is_null=1 */ ### @2=NULL /* type=16 meta=2048 nullable=1 is_null=1 */ ### @3=NULL /* type=1 meta=0 nullable=1 is_null=1 */ ### @4=NULL /* type=1 meta=0 nullable=1 is_null=1 */ diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index 7d903a17d2e..4c711c5a977 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -148,9 +148,10 @@ hello hello ;;;;;;;; # MySQL: -- The -mysqltest: At line 1: End of line junk detected: "6" -mysqltest: At line 1: End of line junk detected: "6" -mysqltest: At line 1: Missing delimiter +mysqltest: At line 1: Extra argument '6' passed to 'sleep' +mysqltest: At line 1: Extra argument '6' passed to 'sleep' +mysqltest: At line 1: Extra argument 'A comment +show status' passed to 'sleep' mysqltest: At line 1: End of line junk detected: "sleep 7 # Another comment " @@ -217,6 +218,12 @@ source database echo message echo message mysqltest: At line 1: Missing argument in exec +1 +1 +2 +2 +X +3 MySQL "MySQL" MySQL: The @@ -349,8 +356,10 @@ here is the sourced script here is the sourced script "hello" "hello" -mysqltest: At line 1: Missing argument to sleep -mysqltest: At line 1: Missing argument to real_sleep +mysqltest: At line 2: Invalid argument to sleep "xyz" +mysqltest: At line 2: Invalid argument to real_sleep "xyz" +mysqltest: At line 1: Missing required argument 'sleep_delay' to command 'sleep' +mysqltest: At line 1: Missing required argument 'sleep_delay' to command 'real_sleep' mysqltest: At line 1: Invalid argument to sleep "abc" mysqltest: At line 1: Invalid argument to real_sleep "abc" 1 @@ -378,6 +387,10 @@ test test2 test3 test4 +outer +true-inner +true-inner again +true-outer Counter is greater than 0, (counter=10) Counter is not 0, (counter=0) 1 @@ -418,6 +431,9 @@ mysqltest: At line 1: Wrong number of arguments to replace_column in 'replace_co mysqltest: At line 1: Wrong column number to replace_column in 'replace_column a b' mysqltest: At line 1: Wrong column number to replace_column in 'replace_column a 1' mysqltest: At line 1: Wrong column number to replace_column in 'replace_column 1 b c ' +select "LONG_STRING" as x; +x +LONG_STRING mysqltest: At line 1: Invalid integer argument "10!" mysqltest: At line 1: Invalid integer argument "a" mysqltest: At line 1: Missing required argument 'connection name' to command 'connect' @@ -524,7 +540,28 @@ a D 1 1 1 4 drop table t1; +create table t1 ( f1 char(10)); +insert into t1 values ("Abcd"); +select * from t1; +f1 +Abcd +select * from t2;; +ERROR 42S02: Table 'test.t2' doesn't exist +select * from t1; +f1 +Abcd +select * from t1;; +Result coming up +f1 +Abcd +select * from t1;; +f1 +Abcd +mysqltest: At line 2: Cannot run query on connection between send and reap +select * from t1;; +drop table t1; mysqltest: At line 1: Missing required argument 'filename' to command 'remove_file' +mysqltest: At line 1: Missing required argument 'directory' to command 'remove_files_wildcard' mysqltest: At line 1: Missing required argument 'filename' to command 'write_file' mysqltest: At line 1: End of file encountered before 'EOF' delimiter was found Content for test_file1 @@ -554,6 +591,8 @@ hello mysqltest: At line 1: Max delimiter length(16) exceeded hello hello +val is 5 +val is 5 mysqltest: At line 1: test of die Some output create table t1( a int, b char(255), c timestamp); @@ -681,6 +720,29 @@ INSERT INTO t1 SELECT f1 - 256 FROM t1; INSERT INTO t1 SELECT f1 - 512 FROM t1; SELECT * FROM t1; DROP TABLE t1; +select "500g blåbærsyltetøy" as "will be lower cased"; +will be lower cased +500g blåbærsyltetøy +SELECT "UPPER" AS "WILL NOT BE lower cased"; +WILL NOT BE lower cased +UPPER +UP +SELECT 0 as "UP AGAIN"; +UP AGAIN +0 +select "abcdef" as "uvwxyz"; +uvwxyz +abcdef +select "xyz" as name union select "abc" as name order by name desc; +name +abc +xyz +select 1 as "some new text"; +some new text +1 +select 0 as "will not lower case ÄËÐ"; +will not lower case ÄËÐ +0 CREATE TABLE t1( a int, b varchar(255), c datetime ); @@ -727,6 +789,8 @@ mysqltest: At line 1: change user failed: Access denied for user 'root'@'localho file1.txt file1.txt file2.txt +file11.txt +dir-list.txt SELECT 'c:\\a.txt' AS col; col z diff --git a/mysql-test/r/no_binlog.result b/mysql-test/r/no_binlog.result new file mode 100644 index 00000000000..6ae267664fd --- /dev/null +++ b/mysql-test/r/no_binlog.result @@ -0,0 +1,2 @@ +SHOW BINARY LOGS; +ERROR HY000: You are not using binary logging diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 08357795046..23a485dc5ed 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2088,4 +2088,15 @@ SELECT 1 FROM t1 JOIN t1 AS t2 USING (a) FOR UPDATE; 1 1 DROP TABLE t1; +# +# BUG#51868 - crash with myisam_use_mmap and partitioned myisam tables +# +SET GLOBAL myisam_use_mmap=1; +CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 1; +INSERT INTO t1 VALUES(0); +FLUSH TABLE t1; +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES(0); +DROP TABLE t1; +SET GLOBAL myisam_use_mmap=default; End of 5.1 tests diff --git a/mysql-test/r/partition_debug_sync.result b/mysql-test/r/partition_debug_sync.result new file mode 100644 index 00000000000..5eb19f42395 --- /dev/null +++ b/mysql-test/r/partition_debug_sync.result @@ -0,0 +1,57 @@ +DROP TABLE IF EXISTS t1, t2; +SET DEBUG_SYNC= 'RESET'; +# +# Bug#42438: Crash ha_partition::change_table_ptr +# Test when remove partitioning is done while drop table is waiting +# for the table. +# Con 1 +SET DEBUG_SYNC= 'RESET'; +CREATE TABLE t1 +(a INTEGER, +b INTEGER NOT NULL, +KEY (b)) +ENGINE = MYISAM +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (2), +PARTITION p1 VALUES LESS THAN (20), +PARTITION p2 VALUES LESS THAN (100), +PARTITION p3 VALUES LESS THAN MAXVALUE ) */; +SET DEBUG_SYNC= 'alter_table_before_create_table_no_lock SIGNAL removing_partitioning WAIT_FOR waiting_for_alter'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL partitioning_removed'; +ALTER TABLE t1 REMOVE PARTITIONING; +# Con default +SET DEBUG_SYNC= 'now WAIT_FOR removing_partitioning'; +SET DEBUG_SYNC= 'waiting_for_table SIGNAL waiting_for_alter'; +SET DEBUG_SYNC= 'rm_table_part2_before_delete_table WAIT_FOR partitioning_removed'; +DROP TABLE IF EXISTS t1; +# Con 1 +SET DEBUG_SYNC= 'RESET'; +SET DEBUG_SYNC= 'RESET'; +# +# Bug#42438: Crash ha_partition::change_table_ptr +# Test when remove partitioning is failing due to drop table is already +# in progress. +CREATE TABLE t2 +(a INTEGER, +b INTEGER NOT NULL, +KEY (b)) +ENGINE = MYISAM +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (2), +PARTITION p1 VALUES LESS THAN (20), +PARTITION p2 VALUES LESS THAN (100), +PARTITION p3 VALUES LESS THAN MAXVALUE ) */; +SET DEBUG_SYNC= 'before_lock_tables_takes_lock SIGNAL removing_partitions WAIT_FOR waiting_for_alter'; +SET DEBUG_SYNC= 'alter_table_before_rename_result_table WAIT_FOR delete_done'; +ALTER TABLE t2 REMOVE PARTITIONING; +# Con default +SET DEBUG_SYNC= 'now WAIT_FOR removing_partitions'; +SET DEBUG_SYNC= 'waiting_for_table SIGNAL waiting_for_alter'; +SET DEBUG_SYNC= 'rm_table_part2_before_binlog SIGNAL delete_done'; +DROP TABLE IF EXISTS t2; +# Con 1 +ERROR 42S02: Table 'test.t2' doesn't exist +SET DEBUG_SYNC= 'RESET'; +# Con default +SET DEBUG_SYNC= 'RESET'; +End of 5.1 tests diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index b692203823d..6ebf033adb7 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -1,4 +1,46 @@ drop table if exists t1; +# +# Bug#50392: insert_id is not reset for partitioned tables +# auto_increment on duplicate entry +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +SET INSERT_ID= 13; +INSERT INTO t1 VALUES (NULL); +SET INSERT_ID= 12; +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +ERROR 23000: Duplicate entry '13' for key 'PRIMARY' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`a`) +) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +12 +13 +14 +DROP TABLE t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) PARTITION BY KEY(a); +SET INSERT_ID= 13; +INSERT INTO t1 VALUES (NULL); +SET INSERT_ID= 12; +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +ERROR 23000: Duplicate entry '13' for key 'PRIMARY' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`a`) +) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 +/*!50100 PARTITION BY KEY (a) */ +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +12 +13 +14 +DROP TABLE t1; CREATE TABLE t1 (a INTEGER NOT NULL, PRIMARY KEY (a)); INSERT INTO t1 VALUES (1),(1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index f2f6ef138ff..2a04aafe554 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -1,4 +1,73 @@ -drop table if exists t1; +drop table if exists t1, t2; +# +# Bug#51830: Incorrect partition pruning on range partition (regression) +# +CREATE TABLE t1 (a INT NOT NULL) +ENGINE = InnoDB +PARTITION BY RANGE(a) +(PARTITION p10 VALUES LESS THAN (10), +PARTITION p30 VALUES LESS THAN (30), +PARTITION p50 VALUES LESS THAN (50), +PARTITION p70 VALUES LESS THAN (70), +PARTITION p90 VALUES LESS THAN (90)); +INSERT INTO t1 VALUES (10),(30),(50); +INSERT INTO t1 VALUES (70); +INSERT INTO t1 VALUES (80); +INSERT INTO t1 VALUES (89); +INSERT INTO t1 VALUES (90); +ERROR HY000: Table has no partition for value 90 +INSERT INTO t1 VALUES (100); +ERROR HY000: Table has no partition for value 100 +insert INTO t1 VALUES (110); +ERROR HY000: Table has no partition for value 110 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where +DROP TABLE t1; +# +# Bug#50104: Partitioned table with just 1 partion works with fk +# +CREATE TABLE t2 ( +id INT, +PRIMARY KEY (id) +) ENGINE=InnoDB ; +CREATE TABLE t1 ( +id INT NOT NULL AUTO_INCREMENT, +parent_id INT DEFAULT NULL, +PRIMARY KEY (id), +KEY parent_id (parent_id) +) ENGINE=InnoDB; +ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1; +ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); +ERROR HY000: Foreign key clause is not yet supported in conjunction with partitioning +ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2; +ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); +ERROR HY000: Foreign key clause is not yet supported in conjunction with partitioning +DROP TABLE t1, t2; create table t1 (a int not null, b datetime not null, primary key (a,b)) diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index cf0474a3f6b..568c21b27be 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -270,7 +270,7 @@ a 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a > 1; a 2 @@ -327,13 +327,13 @@ a 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a > 7; a 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index DROP TABLE t1; CREATE TABLE t1 (a INT PRIMARY KEY) PARTITION BY RANGE (a) ( @@ -556,7 +556,7 @@ a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a > 1; a 2 @@ -601,13 +601,13 @@ a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a > 6; a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index DROP TABLE t1; # test of RANGE and index CREATE TABLE t1 (a DATE, KEY(a)) @@ -757,10 +757,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 2 Using where; Using index +1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 2 Using where; Using index +1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index @@ -1086,10 +1086,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index +1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index +1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index @@ -1101,7 +1101,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01 range a a 4 NULL 2 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index @@ -2101,6 +2101,21 @@ insert into t7 values (10),(30),(50); explain partitions select * from t7 where a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a < 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a <= 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a = 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a >= 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a > 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a < 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -2110,9 +2125,33 @@ id select_type table partitions type possible_keys key key_len ref rows Extra explain partitions select * from t7 where a = 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30 system NULL NULL NULL NULL 1 +explain partitions select * from t7 where a >= 10; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a > 10; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a < 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a <= 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a = 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a > 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a >= 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a < 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a <= 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -2122,6 +2161,9 @@ id select_type table partitions type possible_keys key key_len ref rows Extra explain partitions select * from t7 where a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a > 91; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 11 and a < 29; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -2137,6 +2179,21 @@ insert into t7 values (10),(30),(50); explain partitions select * from t7 where a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a < 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a <= 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a = 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a >= 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a > 9; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a < 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -2146,9 +2203,33 @@ id select_type table partitions type possible_keys key key_len ref rows Extra explain partitions select * from t7 where a = 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30 system NULL NULL NULL NULL 1 +explain partitions select * from t7 where a >= 10; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a > 10; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a < 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a <= 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a = 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a > 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a >= 89; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a < 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where +explain partitions select * from t7 where a <= 90; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -2158,6 +2239,9 @@ id select_type table partitions type possible_keys key key_len ref rows Extra explain partitions select * from t7 where a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions select * from t7 where a > 91; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 11 and a < 29; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result index 02d2f6359c5..731f2478cc9 100644 --- a/mysql-test/r/partition_range.result +++ b/mysql-test/r/partition_range.result @@ -1,4 +1,18 @@ drop table if exists t1, t2; +# +# Bug#48229: group by performance issue of partitioned table +# +CREATE TABLE t1 ( +a INT, +b INT, +KEY a (a,b) +) +PARTITION BY HASH (a) PARTITIONS 1; +INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020); +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index +DROP TABLE t1; create table t1 (a int) partition by range (a) ( partition p0 values less than (maxvalue)); diff --git a/mysql-test/r/plugin_not_embedded.result b/mysql-test/r/plugin_not_embedded.result new file mode 100644 index 00000000000..82cfe7b23b8 --- /dev/null +++ b/mysql-test/r/plugin_not_embedded.result @@ -0,0 +1,10 @@ +# +# Bug#51770: UNINSTALL PLUGIN requires no privileges +# +GRANT INSERT ON mysql.plugin TO bug51770@localhost; +INSTALL PLUGIN example SONAME 'ha_example.so'; +UNINSTALL PLUGIN example; +ERROR 42000: DELETE command denied to user 'bug51770'@'localhost' for table 'plugin' +GRANT DELETE ON mysql.plugin TO bug51770@localhost; +UNINSTALL PLUGIN example; +DROP USER bug51770@localhost; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 1e67bfa7d37..cf08d763e5c 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1786,7 +1786,7 @@ prepare stmt from "create view v1 (c,d,e,f) as select a,b,a in (select a+2 from execute stmt; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) AS `a+2` from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` AS `a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci select * from v1; c d e f drop view v1; @@ -1794,7 +1794,7 @@ execute stmt; deallocate prepare stmt; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) AS `a+2` from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` AS `a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci select * from v1; c d e f drop view v1; diff --git a/mysql-test/r/query_cache_with_views.result b/mysql-test/r/query_cache_with_views.result index 03430bd504b..7f0417a60c7 100644 --- a/mysql-test/r/query_cache_with_views.result +++ b/mysql-test/r/query_cache_with_views.result @@ -193,4 +193,17 @@ show status like "Qcache_hits"; Variable_name Value Qcache_hits 2 drop table t1; +# +# Bug46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table +# +CREATE TABLE t1 (c1 INT, c2 INT); +CREATE TABLE t2 LIKE t1; +SET AUTOCOMMIT=OFF; +CREATE VIEW t1_view AS SELECT c1 FROM t1 NATURAL JOIN t2 ; +INSERT INTO t1_view (c1, c2) SELECT c1, c2 FROM t1; +ERROR 42S22: Unknown column 'c2' in 'field list' +DROP TABLE t1; +DROP TABLE t2; +DROP VIEW t1_view; +SET AUTOCOMMIT=DEFAULT; set GLOBAL query_cache_size=default; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index f5f9f24c898..6353ae23013 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4049,10 +4049,10 @@ join_0.c1; 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 noticed after reading const tables Warnings: -Note 1003 select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0' +Note 1003 select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL SHOW WARNINGS; Level Code Message -Note 1003 select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0' +Note 1003 select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL DROP TABLE t1; SELECT 1 AS ` `; @@ -4647,17 +4647,17 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 +Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 DROP TABLE t1; # # Bug#45266: Uninitialized variable lead to an empty result. diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 7a70e439841..4da176aa1a6 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1293,7 +1293,7 @@ drop database mysqltest; show full plugin; show warnings; Level Code Message -Warning 1287 The syntax 'SHOW PLUGIN' is deprecated and will be removed in MySQL 6.0. Please use 'SHOW PLUGINS' instead +Warning 1287 'SHOW PLUGIN' is deprecated and will be removed in a future release. Please use 'SHOW PLUGINS' instead show plugin; show plugins; create database `mysqlttest\1`; diff --git a/mysql-test/r/skip_name_resolve.result b/mysql-test/r/skip_name_resolve.result index 8aa9877a8e9..1d92d52110d 100644 --- a/mysql-test/r/skip_name_resolve.result +++ b/mysql-test/r/skip_name_resolve.result @@ -9,3 +9,30 @@ SELECT USER(); USER() # SHOW PROCESSLIST; +# +# Bug #37168: Missing variable - skip_name_resolve +# +SHOW VARIABLES LIKE 'skip_name_resolve'; +Variable_name Value +skip_name_resolve ON +SHOW GLOBAL VARIABLES LIKE 'skip_name_resolve'; +Variable_name Value +skip_name_resolve ON +SHOW SESSION VARIABLES LIKE 'skip_name_resolve'; +Variable_name Value +skip_name_resolve ON +SELECT @@skip_name_resolve; +@@skip_name_resolve +1 +SELECT @@LOCAL.skip_name_resolve; +ERROR HY000: Variable 'skip_name_resolve' is a GLOBAL variable +SELECT @@GLOBAL.skip_name_resolve; +@@GLOBAL.skip_name_resolve +1 +SET @@skip_name_resolve=0; +ERROR HY000: Variable 'skip_name_resolve' is a read only variable +SET @@LOCAL.skip_name_resolve=0; +ERROR HY000: Variable 'skip_name_resolve' is a read only variable +SET @@GLOBAL.skip_name_resolve=0; +ERROR HY000: Variable 'skip_name_resolve' is a read only variable +End of 5.1 tests diff --git a/mysql-test/r/sp-bugs.result b/mysql-test/r/sp-bugs.result index 14c5311bbe5..2374b433fba 100644 --- a/mysql-test/r/sp-bugs.result +++ b/mysql-test/r/sp-bugs.result @@ -44,4 +44,33 @@ SELECT f2 (); f2 () NULL DROP SCHEMA testdb; +USE test; +# +# Bug#50423: Crash on second call of a procedure dropping a trigger +# +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS tr1; +DROP PROCEDURE IF EXISTS p1; +CREATE TABLE t1 (f1 INTEGER); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1; +CREATE PROCEDURE p1 () DROP TRIGGER tr1; +CALL p1 (); +CALL p1 (); +ERROR HY000: Trigger does not exist +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Bug#50423: Crash on second call of a procedure dropping a trigger +# +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS tr1; +DROP PROCEDURE IF EXISTS p1; +CREATE TABLE t1 (f1 INTEGER); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1; +CREATE PROCEDURE p1 () DROP TRIGGER tr1; +CALL p1 (); +CALL p1 (); +ERROR HY000: Trigger does not exist +DROP TABLE t1; +DROP PROCEDURE p1; End of 5.1 tests diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index b3968ea7eb6..ec2ba5747c3 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1643,7 +1643,7 @@ create table t1 (a int) type=MyISAM; drop table t1; end| Warnings: -Warning 1287 The syntax 'TYPE=storage_engine' is deprecated and will be removed in MySQL 6.0. Please use 'ENGINE=storage_engine' instead +Warning 1287 'TYPE=storage_engine' is deprecated and will be removed in a future release. Please use 'ENGINE=storage_engine' instead call p1(); call p1(); drop procedure p1; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 1e6227e7380..2180a23b91a 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4304,10 +4304,10 @@ call bug13012()| Table Op Msg_type Msg_text test.t1 repair status OK Table Op Msg_type Msg_text -test.t1 backup Warning The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t1 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 backup status OK Table Op Msg_type Msg_text -test.t1 restore Warning The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead +test.t1 restore Warning 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead test.t1 restore status OK drop procedure bug13012| create view v1 as select * from t1| diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result index af73d637590..b21395d9623 100644 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@ -216,6 +216,17 @@ SELECT RELEASE_LOCK('Bug44521'); RELEASE_LOCK('Bug44521') 1 DROP PROCEDURE p; +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1); +CREATE FUNCTION f1 (inp TEXT) RETURNS INT NO SQL RETURN sleep(60); +CREATE VIEW v1 AS SELECT f1('a') FROM t1; +SELECT * FROM v1;; +SELECT * FROM v1; +ERROR 70100: Query execution was interrupted +ERROR 70100: Query execution was interrupted +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION f1; # ------------------------------------------------------------------ # -- End of 5.1 tests # ------------------------------------------------------------------ diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result index 3cc251bc0a6..a64f53efde7 100644 --- a/mysql-test/r/sp_trans.result +++ b/mysql-test/r/sp_trans.result @@ -535,7 +535,7 @@ use db_bug7787| CREATE PROCEDURE p1() SHOW INNODB STATUS; | Warnings: -Warning 1287 The syntax 'SHOW INNODB STATUS' is deprecated and will be removed in MySQL 6.0. Please use 'SHOW ENGINE INNODB STATUS' instead +Warning 1287 'SHOW INNODB STATUS' is deprecated and will be removed in a future release. Please use 'SHOW ENGINE INNODB STATUS' instead GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost| DROP DATABASE db_bug7787| drop user user_bug7787@localhost| diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 75d5a23858d..43ebe165a6d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -32,7 +32,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1249 Select 2 was reduced during optimization -Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))` +Note 1003 select (select 0 union select 0) AS `(SELECT (SELECT 0 UNION SELECT 0))` SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; ERROR 42S22: Reference 'a' not supported (forward reference in item list) SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b; @@ -50,7 +50,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1) +Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1') = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -187,7 +187,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`) +Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`) select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 @@ -203,7 +203,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: -Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.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` +Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`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 7 @@ -314,7 +314,7 @@ NULL UNION RESULT <union2,3> ALL NULL 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 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `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` +Note 1003 select (select '2' from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`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 create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -332,7 +332,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t6.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`)) +Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 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 drop table t1,t2,t3; @@ -367,7 +367,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -399,7 +399,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index Warnings: -Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')` +Note 1003 select (select distinct `test`.`t1`.`date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')` SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; date 2002-08-03 @@ -743,7 +743,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -906,7 +906,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -1018,19 +1018,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found 2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` +Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found 2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select (select encrypt('test') AS `ENCRYPT('test')` from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` +Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found 2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` +Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` drop table t1; CREATE TABLE `t1` ( `mot` varchar(30) character set latin1 NOT NULL default '', @@ -1125,7 +1125,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() from `test`.`t1` limit 1) from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1` drop table t1; select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent); ERROR 42S02: Table 'test.t1' doesn't exist @@ -1179,7 +1179,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1189,7 +1189,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', @@ -1234,7 +1234,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`)) +Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`)) drop table t1; CREATE TABLE t1 ( ID int(10) unsigned NOT NULL auto_increment, @@ -1317,7 +1317,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1356,7 +1356,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1496,7 +1496,7 @@ 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 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max('0') from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))) select * from t3 where a >= some (select b from t2); a explain extended select * from t3 where a >= some (select b from t2); @@ -1504,7 +1504,7 @@ 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 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))) select * from t3 where a >= all (select b from t2 group by 1); a 6 @@ -1515,7 +1515,7 @@ 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 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select '0' AS `b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1523,7 +1523,7 @@ 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 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select '0' AS `b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1566,7 +1566,7 @@ 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 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) drop table t2, t3; CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now()); @@ -1743,7 +1743,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.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)))) +Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`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)); insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); @@ -2279,7 +2279,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.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`)) +Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)) drop table t1; CREATE TABLE t1 (t1_a int); INSERT INTO t1 VALUES (1); @@ -2820,19 +2820,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); @@ -4275,7 +4275,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) +Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); @@ -4353,13 +4353,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having 1)) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having 1)) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1)) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1)) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4612,4 +4612,105 @@ FROM t1,t1 a ); 1 DROP TABLE t1; +# +# Bug #45989 take 2 : memory leak after explain encounters an +# error in the query +# +CREATE TABLE t1(a LONGTEXT); +INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); +INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); +EXPLAIN EXTENDED SELECT DISTINCT 1 FROM t1, +(SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) AS d1 +WHERE t1.a = d1.a; +ERROR 42S22: Unknown column 'd1.a' in 'where clause' +DROP TABLE t1; End of 5.1 tests. +Set up test tables. +CREATE TABLE t1 ( +t1_id INT UNSIGNED, +PRIMARY KEY(t1_id) +) Engine=MyISAM; +INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 SELECT * FROM t1; +CREATE TABLE t3 ( +t3_id INT UNSIGNED AUTO_INCREMENT, +t1_id INT UNSIGNED, +amount DECIMAL(16,2), +PRIMARY KEY(t3_id), +KEY(t1_id) +) Engine=MyISAM; +INSERT INTO t3 (t1_id, t3_id, amount) +VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00); +This is the 'inner query' running by itself. +Produces correct results. +SELECT +t1.t1_id, +IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM +t1 +LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY +t1.t1_id +; +t1_id total_amount +1 100.00 +2 200.00 +3 0.00 +4 400.00 +5 0.00 +SELECT * FROM (the same inner query) +Produces correct results. +SELECT * FROM ( +SELECT +t1.t1_id, +IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM +t1 +LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY +t1.t1_id +) AS t; +t1_id total_amount +1 100.00 +2 200.00 +3 0.00 +4 400.00 +5 0.00 +Now make t2.t1_id part of a key. +ALTER TABLE t2 ADD PRIMARY KEY(t1_id); +Same inner query by itself. +Still correct results. +SELECT +t1.t1_id, +IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM +t1 +LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY +t1.t1_id; +t1_id total_amount +1 100.00 +2 200.00 +3 0 +4 400.00 +5 0 +SELECT * FROM (the same inner query), now with indexes on the LEFT JOIN +SELECT * FROM ( +SELECT +t1.t1_id, +IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM +t1 +LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY +t1.t1_id +) AS t; +t1_id total_amount +1 100.00 +2 200.00 +3 0.00 +4 400.00 +5 0.00 +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 38c9f47e0d7..468f939c943 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -30,7 +30,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2` +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2` explain extended select a, oref from t2 where a in (select max(ie) from t1 where oref=t2.oref group by grp); @@ -39,7 +39,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))) +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))) select a, oref, a in ( select max(ie) from t1 where oref=t2.oref group by grp union select max(ie) from t1 where oref=t2.oref group by grp @@ -68,7 +68,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int, oref int, key(a)); insert into t1 values @@ -157,7 +157,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); insert into t1 values @@ -185,7 +185,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); insert into t1 (oref, grp) values @@ -209,7 +209,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2` +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2` drop table t1, t2; create table t1 (a int, b int, primary key (a)); insert into t1 values (1,1), (3,1),(100,1); @@ -258,7 +258,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2` +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2` select a,b, oref, (a,b) in (select a,b from t1,t4 where c=t2.oref) Z from t2; @@ -703,7 +703,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`)))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`)))))) SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); a SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); @@ -864,7 +864,7 @@ Level Code Message Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2 Error 1054 Unknown column 'c' in 'field list' -Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM +Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM (SELECT COUNT(b) FROM t1) AS x GROUP BY c )` from `test`.`t1` group by `test`.`t1`.`b`) `y` DROP TABLE t1; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 245dcec04da..4dac7443135 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -168,4 +168,16 @@ Warning 1618 <DATA DIRECTORY> option ignored Warning 1618 <INDEX DIRECTORY> option ignored DROP TABLE t1; SET @@SQL_MODE=@OLD_SQL_MODE; +# +# BUG#40980 - Drop table can remove another MyISAM table's +# data and index files +# +CREATE TABLE user(a INT) DATA DIRECTORY='MYSQL_TMP_DIR/mysql' + INDEX DIRECTORY='MYSQL_TMP_DIR/mysql'; +FLUSH TABLE user; +# Symlinking mysql database to tmpdir +FLUSH TABLE mysql.user; +DROP TABLE user; +FLUSH TABLE mysql.user; +SELECT * FROM mysql.user; End of 5.1 tests diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 71fe02911fc..2cb3b543bb7 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -141,7 +141,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `a2`.`id`)))) +Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) from `test`.`t2` where (`test`.`t2`.`id` = `a2`.`id`)))) This should use one table: explain select id from v2 where id=2; id select_type table type possible_keys key key_len ref rows Extra @@ -169,7 +169,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `f`.`id`)))) +Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where ((`f`.`id` = `a2`.`id`) and (`a2`.`attr2` between 12 and 14) and (`a2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) from `test`.`t2` where (`test`.`t2`.`id` = `f`.`id`)))) drop view v1, v2; drop table t0, t1, t2; create table t1 (a int); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 17a1af9d74b..47711746262 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -2060,4 +2060,45 @@ ERROR 42S02: Table 'test.a_nonextisting_table' doesn't exist SELECT * FROM t2; a b DROP TABLE t1, t2; +# +# Bug#51650 crash with user variables and triggers +# +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (b VARCHAR(50) NOT NULL); +CREATE TABLE t2 (a VARCHAR(10) NOT NULL DEFAULT ''); +CREATE TRIGGER trg1 AFTER INSERT ON t2 +FOR EACH ROW BEGIN +SELECT 1 FROM t1 c WHERE +(@bug51650 IS NULL OR @bug51650 != c.b) AND c.b = NEW.a LIMIT 1 INTO @foo; +END// +SET @bug51650 = 1; +INSERT IGNORE INTO t2 VALUES(); +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +INSERT IGNORE INTO t1 SET b = '777'; +INSERT IGNORE INTO t2 SET a = '111'; +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +SET @bug51650 = 1; +INSERT IGNORE INTO t2 SET a = '777'; +DROP TRIGGER trg1; +DROP TABLE t1, t2; +CREATE TABLE t1 (id INT NOT NULL); +CREATE TABLE t2 (id INT NOT NULL); +INSERT t1 VALUES (1),(2),(3); +UPDATE t1 SET id=NULL; +Warnings: +Warning 1048 Column 'id' cannot be null +Warning 1048 Column 'id' cannot be null +Warning 1048 Column 'id' cannot be null +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW +INSERT INTO t2 VALUES (3); +UPDATE t1 SET id=NULL; +Warnings: +Warning 1048 Column 'id' cannot be null +Warning 1048 Column 'id' cannot be null +Warning 1048 Column 'id' cannot be null +DROP TRIGGER t1_bu; +DROP TABLE t1,t2; End of 5.1 tests. diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index 51feab0a421..9f32a10a3fb 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -785,4 +785,19 @@ t1 CREATE TABLE `t1` ( KEY `a` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# Bug#50591 bit(31) causes Duplicate entry '1-NULL' for key 'group_key' +# +CREATE TABLE t1(a INT, b BIT(7) NOT NULL); +INSERT INTO t1 VALUES (NULL, 0),(NULL, 0); +SELECT SUM(a) FROM t1 GROUP BY b, a; +SUM(a) +NULL +DROP TABLE t1; +CREATE TABLE t1(a INT, b BIT(7) NOT NULL, c BIT(8) NOT NULL); +INSERT INTO t1 VALUES (NULL, 0, 0),(NULL, 0, 0); +SELECT SUM(a) FROM t1 GROUP BY c, b, a; +SUM(a) +NULL +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index d11ab236c34..08c30d884fd 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -891,11 +891,11 @@ CREATE TABLE b15776 (a year(-2)); 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 '-2))' at line 1 CREATE TABLE b15776 (a timestamp(4294967294)); Warnings: -Warning 1287 The syntax 'TIMESTAMP(4294967294)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(4294967294)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead DROP TABLE b15776; CREATE TABLE b15776 (a timestamp(4294967295)); Warnings: -Warning 1287 The syntax 'TIMESTAMP(4294967295)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(4294967295)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead DROP TABLE b15776; CREATE TABLE b15776 (a timestamp(4294967296)); ERROR 42000: Display width out of range for column 'a' (max = 4294967295) diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index f96e07b0c5e..dab1d78ba27 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -275,4 +275,25 @@ select * from t1 where a between '0000-00-01' and '0000-00-02'; a 0000-00-01 drop table t1; +# +# Bug#50918: Date columns treated differently in Views than in Base +# Tables +# +CREATE TABLE t1 ( the_date DATE, the_time TIME ); +INSERT INTO t1 VALUES ( '2010-01-01', '01:01:01' ); +SELECT * FROM t1 t11 JOIN t1 t12 ON addtime( t11.the_date, t11.the_time ) = +addtime( t12.the_date, t12.the_time ); +the_date the_time the_date the_time +2010-01-01 01:01:01 2010-01-01 01:01:01 +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = +addtime( v1.the_date, v1.the_time ); +the_date the_time the_date the_time +2010-01-01 01:01:01 2010-01-01 01:01:01 +SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = +addtime( cast(v1.the_date AS DATETIME), v1.the_time ); +the_date the_time the_date the_time +2010-01-01 01:01:01 2010-01-01 01:01:01 +DROP TABLE t1; +DROP VIEW v1; End of 5.1 tests diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index b6281443751..9b18f250d21 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -517,7 +517,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0)) +Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 from `test`.`t1` `x1` where 0)) select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -529,7 +529,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0)) +Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 from `test`.`t2` `x1` where 0)) select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date @@ -543,7 +543,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`)))) +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(select 1 from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`)))) select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -555,7 +555,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`)))) +Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`)))) select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 24cb725de9f..e26c2e68775 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -101,13 +101,13 @@ create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6), t8 timestamp(8), t10 timestamp(10), t12 timestamp(12), t14 timestamp(14)); Warnings: -Warning 1287 The syntax 'TIMESTAMP(2)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(4)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(6)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(8)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(10)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(12)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead -Warning 1287 The syntax 'TIMESTAMP(14)' is deprecated and will be removed in MySQL 6.0. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(2)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(4)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(6)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(8)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(10)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(12)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead +Warning 1287 'TIMESTAMP(14)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead insert t1 values (0,0,0,0,0,0,0), ("1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result index 56b326327c6..8948214f565 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -309,4 +309,36 @@ yyyy c4 2069 2069 DROP TABLE t2, t4; # +# Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type +# +CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4)); +INSERT INTO t1 (s) VALUES ('bad'); +Warnings: +Warning 1364 Field 'y' doesn't have a default value +INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001); +SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y; +y s y s +0000 bad 0000 bad +0000 0 0000 bad +0000 bad 0000 0 +0000 0 0000 0 +2000 2000 2000 2000 +2001 2001 2001 2001 +SELECT * FROM t1 WHERE t1.y = 0; +y s +0000 bad +0000 0 +SELECT * FROM t1 WHERE t1.y = 2000; +y s +2000 2000 +SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y; +ta_y s tb_y s +0000 bad 0000 bad +0000 0 0000 bad +0000 bad 0000 0 +0000 0 0000 0 +2000 2000 2000 2000 +2001 2001 2001 2001 +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index df281046e13..054f416bfd4 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1585,7 +1585,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 UNION t1 system NULL NULL NULL NULL 0 0.00 const row not found NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: -Note 1003 select '0' AS `a` from `test`.`t1` union select '0' AS `a` from `test`.`t1` order by `a` +Note 1003 select NULL AS `a` from `test`.`t1` union select NULL AS `a` from `test`.`t1` order by `a` DROP TABLE t1; End of 5.0 tests # @@ -1636,7 +1636,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `test`.`t1`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`b` = 12)) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `test`.`t1`.`a` from `test`.`t2` where (`test`.`t2`.`b` = 12)) # Should not crash SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY (SELECT a FROM t2 WHERE b = 12); diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index d859579e835..006eaba4e69 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -514,3 +514,16 @@ ALTER TABLE t2 COMMENT = 'ABC'; UPDATE t2, t1 SET t2.f1 = 2, t1.f1 = 9; ALTER TABLE t2 COMMENT = 'DEF'; DROP TABLE t1, t2; +# +# Bug#50545: Single table UPDATE IGNORE crashes on join view in +# sql_safe_updates mode. +# +CREATE TABLE t1 ( a INT, KEY( a ) ); +INSERT INTO t1 VALUES (0), (1); +CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; +SET SESSION sql_safe_updates = 1; +UPDATE IGNORE v1 SET a = 1; +ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column +SET SESSION sql_safe_updates = DEFAULT; +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index d3d9cb21b83..54506a9a4a2 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -519,6 +519,8 @@ select @@max_user_connections; 100 set global max_write_lock_count=100; set myisam_sort_buffer_size=100; +Warnings: +Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '100' set global net_buffer_length=100; Warnings: Warning 1292 Truncated incorrect net_buffer_length value: '100' @@ -1483,4 +1485,13 @@ SELECT @@GLOBAL.max_binlog_cache_size; @@GLOBAL.max_binlog_cache_size 5368709120 SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; +# +# Bug #37168 : Missing variable - skip_name_resolve +# +SELECT @@skip_name_resolve; +@@skip_name_resolve +0 +SHOW VARIABLES LIKE 'skip_name_resolve'; +Variable_name Value +skip_name_resolve OFF End of 5.1 tests diff --git a/mysql-test/r/variables+c.result b/mysql-test/r/variables_community.result index 7a2976d1267..7a2976d1267 100644 --- a/mysql-test/r/variables+c.result +++ b/mysql-test/r/variables_community.result diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index a4134357a49..f84186743f4 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -717,7 +717,7 @@ create view v1 as select a from t1; create view v2 as select a from t2 where a in (select a from v1); show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` AS `a` from `v1`) latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` from `v1`) latin1 latin1_swedish_ci drop view v2, v1; drop table t1, t2; CREATE VIEW `v 1` AS select 5 AS `5`; @@ -2982,7 +2982,7 @@ SHOW WARNINGS; Level Code Message SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci DROP VIEW v1; DROP TABLE t1, t2; DROP FUNCTION IF EXISTS f1; @@ -3844,6 +3844,36 @@ CREATE VIEW v1 AS SELECT a FROM t1; ALTER TABLE v1; DROP VIEW v1; DROP TABLE t1; +# +# Bug#48449: hang on show create view after upgrading when +# view contains function of view +# +DROP VIEW IF EXISTS v1,v2; +DROP TABLE IF EXISTS t1,t2; +DROP FUNCTION IF EXISTS f1; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE FUNCTION f1() RETURNS INT +BEGIN +SELECT a FROM v2 INTO @a; +RETURN @a; +END// +# Trigger pre-locking when opening v2. +CREATE VIEW v1 AS SELECT f1() FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `f1`() AS `f1()` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1599 View `test`.`v2` has no creation context +DROP VIEW v1,v2; +DROP TABLE t1,t2; +DROP FUNCTION f1; +CREATE TABLE t1(f1 INT); +INSERT INTO t1 VALUES (); +CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE +ROW(1,1) >= ROW(1, (SELECT 1 FROM t1 WHERE f1 >= ANY ( SELECT '1' ))); +DROP VIEW v1; +DROP TABLE t1; # ----------------------------------------------------------------- # -- End of 5.1 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/r/view_alias.result b/mysql-test/r/view_alias.result new file mode 100644 index 00000000000..72c4bf29f25 --- /dev/null +++ b/mysql-test/r/view_alias.result @@ -0,0 +1,111 @@ +# +# Bug#40277 SHOW CREATE VIEW returns invalid SQL +# Bug#41999 SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list +# +# 65 characters exceed the maximum length of a column identifier. The system cannot derive the name from statement. +# Constant with length = 65 . Expect to get the identifier 'Name_exp_1'. +CREATE VIEW v1 AS SELECT '<--- 65 char including the arrows --->'; +SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; +COLUMN_NAME +Name_exp_1 +DROP VIEW v1; +CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`; +DROP VIEW v1; +CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`; +DROP VIEW v1; +# Subquery with length = 65 . Expect to get the identifier 'Name_exp_1'. +# Attention: Identifier for the column within the subquery will be not generated. +CREATE VIEW v1 AS SELECT (SELECT '<--- 54 char including the arrows (+ 11 outside) -->'); +SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; +COLUMN_NAME +Name_exp_1 +DROP VIEW v1; +CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`; +DROP VIEW v1; +CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`; +DROP VIEW v1; +# ----------------------------------------------------------------------------------------------------------------- +# 64 characters are the maximum length of a column identifier. The system can derive the name from the statement. +CREATE VIEW v1 AS SELECT '<--- 64 char including the arrows --->'; +SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; +COLUMN_NAME +<--- 64 char including the arrows ---> +DROP VIEW v1; +CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`; +DROP VIEW v1; +CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`; +DROP VIEW v1; +CREATE VIEW v1 AS SELECT (SELECT '<--- 53 char including the arrows (+ 11 outside) --->'); +SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; +COLUMN_NAME +(SELECT '<--- 53 char including the arrows (+ 11 outside) --->') +DROP VIEW v1; +CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`; +DROP VIEW v1; +CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`; +DROP VIEW v1; +# ----------------------------------------------------------------------------------------------------------------- +# Identifiers must not have trailing spaces. The system cannot derive the name from a constant with trailing space. +# Generated identifiers have at their end the position within the select column list. +# 'c2 ' -> 'Name_exp_1' , ' c4 ' -> 'Name_exp_2' +CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 '; +SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'; +COLUMN_NAME +c1 +Name_exp_2 +c3 +Name_exp_4 +DROP VIEW v1; +CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`; +DROP VIEW v1; +CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`; +DROP VIEW v1; +# +# Bug#40277 SHOW CREATE VIEW returns invalid SQL +# +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1,t2; +# Column name exceeds the maximum length. +CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555'; +DROP VIEW v1; +CREATE VIEW v1 AS select '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555' AS `Name_exp_1`; +DROP VIEW v1; +# Column names with leading trailing spaces. +CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 '; +DROP VIEW v1; +CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`; +DROP VIEW v1; +# Column name conflicts with a auto-generated one. +CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2'; +DROP VIEW v1; +CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`,'Name_exp_2' AS `My_exp_Name_exp_2`; +DROP VIEW v1; +# Invalid conlumn name in subquery. +CREATE VIEW v1 AS SELECT (SELECT ' c1 '); +DROP VIEW v1; +CREATE VIEW v1 AS select (select ' c1 ') AS `(SELECT ' c1 ')`; +DROP VIEW v1; +CREATE TABLE t1(a INT); +CREATE TABLE t2 LIKE t1; +# Test alias in subquery +CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0); +DROP VIEW v1; +CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 1 from `test`.`t2` `b` where (`b`.`a` = 0)); +DROP VIEW v1; +# Test column alias in subquery +CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias); +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select `t1`.`a` AS `alias` from `t1` group by `t1`.`a`) latin1 latin1_swedish_ci +DROP VIEW v1; +CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select `test`.`t1`.`a` AS `alias` from `test`.`t1` group by `test`.`t1`.`a`); +DROP VIEW v1; +# Alias as the expression column name. +CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT ' a ' AS alias FROM t1 GROUP BY alias); +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select ' a ' AS `alias` from `t1` group by ' a ') latin1 latin1_swedish_ci +DROP VIEW v1; +CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select ' a ' AS `alias` from `test`.`t1` group by ' a '); +DROP VIEW v1; +DROP TABLE t1, t2; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 2d5c515d0b5..52c8bc8a3d5 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -1237,3 +1237,14 @@ SELECT a FROM v2; a DROP USER mysqluser1; DROP DATABASE mysqltest1; +USE test; +# +# Bug#47734: Assertion failed: ! is_set() when locking a view with non-existing definer +# +DROP VIEW IF EXISTS v1; +CREATE DEFINER=`unknown`@`unknown` SQL SECURITY DEFINER VIEW v1 AS SELECT 1; +Warnings: +Note 1449 The user specified as a definer ('unknown'@'unknown') does not exist +LOCK TABLES v1 READ; +ERROR HY000: The user specified as a definer ('unknown'@'unknown') does not exist +DROP VIEW v1; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 6be5c9fb130..95a969d3791 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -168,7 +168,7 @@ max_error_count 10 drop table t1; set table_type=MYISAM; Warnings: -Warning 1287 The syntax '@@table_type' is deprecated and will be removed in MySQL 6.0. Please use '@@storage_engine' instead +Warning 1287 '@@table_type' is deprecated and will be removed in a future release. Please use '@@storage_engine' instead create table t1 (a int); insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); update t1 set a='abc'; diff --git a/mysql-test/r/xa.result b/mysql-test/r/xa.result index d23c8e672b0..fedbb43ea2a 100644 --- a/mysql-test/r/xa.result +++ b/mysql-test/r/xa.result @@ -74,6 +74,23 @@ ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was det xa rollback 'a','c'; xa start 'a','c'; drop table t1; +# +# BUG#51342 - more xid crashing +# +CREATE TABLE t1(a INT) ENGINE=InnoDB; +XA START 'x'; +SET SESSION autocommit=0; +INSERT INTO t1 VALUES(1); +SET SESSION autocommit=1; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state +SELECT @@autocommit; +@@autocommit +0 +INSERT INTO t1 VALUES(1); +XA END 'x'; +XA COMMIT 'x' ONE PHASE; +DROP TABLE t1; +SET SESSION autocommit=1; End of 5.0 tests xa start 'a'; xa end 'a'; |