diff options
Diffstat (limited to 'mysql-test/r')
39 files changed, 1233 insertions, 124 deletions
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 30b4ff11227..12cbf294b69 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -150,7 +150,7 @@ select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; -ERROR 23000: Duplicate entry '255' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 @@ -162,8 +162,7 @@ select last_insert_id(); last_insert_id() 255 insert into t1 set i = null; -Warnings: -Warning 1264 Out of range value for column 'i' at row 1 +ERROR 22003: Out of range value for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 @@ -487,7 +486,7 @@ SELECT @@SESSION.AUTO_INCREMENT_OFFSET; @@SESSION.AUTO_INCREMENT_OFFSET 1 INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -ERROR 22003: Out of range value for column 't1' at row 167 +ERROR 22003: Out of range value for column 'c1' at row 2 SELECT * FROM t1; c1 1 diff --git a/mysql-test/r/auto_increment_ranges_innodb.result b/mysql-test/r/auto_increment_ranges_innodb.result new file mode 100644 index 00000000000..fb936ddfd2b --- /dev/null +++ b/mysql-test/r/auto_increment_ranges_innodb.result @@ -0,0 +1,266 @@ +set default_storage_engine=innodb; +drop table if exists t1; +# +# Testing ranges with smallint +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32767); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(32767-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +32766 +32767 +truncate table t1; +insert into t1 values(32767),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(32767-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32767+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +32767 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned smallint +# +create table t1 (a smallint unsigned primary key auto_increment); +insert into t1 values(65535); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(65535-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +65534 +65535 +truncate table t1; +insert into t1 values(65535),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(65535-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(65535+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +65535 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with integer +# +create table t1 (a int primary key auto_increment); +insert into t1 values(2147483647); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(2147483647-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +2147483646 +2147483647 +truncate table t1; +insert into t1 values(2147483647),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(2147483647-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(2147483647+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +2147483647 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned integer +# +create table t1 (a int unsigned primary key auto_increment); +insert into t1 values(4294967295); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(4294967295-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +4294967294 +4294967295 +truncate table t1; +insert into t1 values(4294967295),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(4294967295-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(4294967295+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +4294967295 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with bigint +# +create table t1 (a bigint primary key auto_increment); +insert into t1 values(cast(9223372036854775807 as unsigned)); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775806 +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775807 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned bigint +# +create table t1 (a bigint unsigned primary key auto_increment); +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +truncate table t1; +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551614 +truncate table t1; +insert into t1 values(18446744073709551615),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +truncate table t1; +insert into t1 values(18446744073709551615-1),(NULL),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +drop table t1; +# +# Test IGNORE and strict mode +# +create table t1 (a smallint primary key auto_increment); +insert ignore into t1 values(32766),(NULL),(NULL),(1); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +select * from t1; +a +1 +32766 +32767 +truncate table t1; +set @org_mode=@@sql_mode; +set @@sql_mode='ansi,traditional'; +insert ignore into t1 values(32766),(NULL),(NULL); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32766),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +set @@sql_mode=@org_mode; +drop table t1; +# +# Test auto increment with negative numbers +# +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +-5 +1 +2 +3 +5 +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES (-5), (NULL); +SELECT * FROM t1; +a +-5 +1 +DROP TABLE t1; +# +# Test inserting a value out-of-range into an auto increment column +# +CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (32768); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +INSERT INTO t1 VALUES (NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +2 +32767 +DROP TABLE t1; +# +# Test old behaviour +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32766),(NULL); +delete from t1 where a=32767; +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; diff --git a/mysql-test/r/auto_increment_ranges_myisam.result b/mysql-test/r/auto_increment_ranges_myisam.result new file mode 100644 index 00000000000..5101321c864 --- /dev/null +++ b/mysql-test/r/auto_increment_ranges_myisam.result @@ -0,0 +1,272 @@ +set default_storage_engine=MYISAM; +drop table if exists t1; +# +# Testing ranges with smallint +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32767); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(32767-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +32766 +32767 +truncate table t1; +insert into t1 values(32767),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +32767 +truncate table t1; +insert into t1 values(32767-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32767+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +32767 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned smallint +# +create table t1 (a smallint unsigned primary key auto_increment); +insert into t1 values(65535); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(65535-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +65534 +65535 +truncate table t1; +insert into t1 values(65535),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +65535 +truncate table t1; +insert into t1 values(65535-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(65535+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +65535 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with integer +# +create table t1 (a int primary key auto_increment); +insert into t1 values(2147483647); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(2147483647-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +2147483646 +2147483647 +truncate table t1; +insert into t1 values(2147483647),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +2147483647 +truncate table t1; +insert into t1 values(2147483647-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(2147483647+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +2147483647 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned integer +# +create table t1 (a int unsigned primary key auto_increment); +insert into t1 values(4294967295); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(4294967295-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +4294967294 +4294967295 +truncate table t1; +insert into t1 values(4294967295),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +4294967295 +truncate table t1; +insert into t1 values(4294967295-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(4294967295+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +4294967295 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with bigint +# +create table t1 (a bigint primary key auto_increment); +insert into t1 values(cast(9223372036854775807 as unsigned)); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1); +insert into t1 values(NULL); +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775806 +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)),(NULL); +ERROR 22003: Out of range value for column 'a' at row 2 +select * from t1; +a +9223372036854775807 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)-1),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(cast(9223372036854775807 as unsigned)+1); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +select * from t1; +a +9223372036854775807 +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; +# +# Testing ranges with unsigned bigint +# +create table t1 (a bigint unsigned primary key auto_increment); +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +truncate table t1; +insert into t1 values(18446744073709551615-1); +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +insert into t1 values(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551614 +truncate table t1; +insert into t1 values(18446744073709551615),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +select * from t1; +a +18446744073709551615 +truncate table t1; +insert into t1 values(18446744073709551615-1),(NULL),(NULL); +ERROR HY000: Failed to read auto-increment value from storage engine +drop table t1; +# +# Test IGNORE and strict mode +# +create table t1 (a smallint primary key auto_increment); +insert ignore into t1 values(32766),(NULL),(NULL),(1); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +select * from t1; +a +1 +32766 +32767 +truncate table t1; +set @org_mode=@@sql_mode; +set @@sql_mode='ansi,traditional'; +insert ignore into t1 values(32766),(NULL),(NULL); +Warnings: +Warning 167 Out of range value for column 'a' at row 3 +truncate table t1; +insert into t1 values(32766),(NULL),(NULL); +ERROR 22003: Out of range value for column 'a' at row 3 +set @@sql_mode=@org_mode; +drop table t1; +# +# Test auto increment with negative numbers +# +CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +-5 +1 +2 +3 +4 +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES (-5), (NULL); +SELECT * FROM t1; +a +-5 +1 +DROP TABLE t1; +# +# Test inserting a value out-of-range into an auto increment column +# +CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (32768); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +INSERT INTO t1 VALUES (NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +2 +32767 +DROP TABLE t1; +# +# Test old behaviour +# +create table t1 (a smallint primary key auto_increment); +insert into t1 values(32766),(NULL); +delete from t1 where a=32767; +insert into t1 values(NULL); +ERROR 22003: Out of range value for column 'a' at row 1 +drop table t1; diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 3e3d75f66e4..af198edc4ca 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -529,6 +529,8 @@ SUCCESS # 13. Read-write statement: INSERT IGNORE, change 0 rows. # insert ignore t1 set a=2; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' call p_verify_status_increment(2, 2, 1, 0); SUCCESS diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 0a6dff6d9a3..ba52959be84 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -2396,6 +2396,8 @@ a b drop table t1; create table if not exists t1 (a int unique, b int) ignore select 1 as a, 1 as b union select 1 as a, 2 as b; +Warnings: +Warning 1062 Duplicate entry '1' for key 'a' select * from t1; a b 1 1 diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5446406924f..ba01db4a66f 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1687,6 +1687,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; b c a +8 c c EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1701,6 +1702,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; b c a +8 c c INSERT INTO t3 VALUES (100), (200); EXPLAIN EXTENDED SELECT t.b, t.c, t1.a @@ -1716,7 +1718,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; b c a -NULL NULL c +8 c c EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1731,7 +1733,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; b c a -NULL NULL c +8 c c SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 205197bc7f6..80729545902 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2867,3 +2867,16 @@ SET @@global.max_allowed_packet:= @tmp_max; # # End of 5.5 tests # +SELECT @tmp_max:= @@global.max_allowed_packet; +@tmp_max:= @@global.max_allowed_packet +1048576 +SET @@global.max_allowed_packet=1024*1024*1024; +SELECT @@global.max_allowed_packet; +@@global.max_allowed_packet +1073741824 +SELECT CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000))); +CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000))) +NULL +Warnings: +Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated +SET @@global.max_allowed_packet:= @tmp_max; diff --git a/mysql-test/r/information_schema_all_engines.result b/mysql-test/r/information_schema_all_engines.result index 7d47f0404bb..4dac9a3e53c 100644 --- a/mysql-test/r/information_schema_all_engines.result +++ b/mysql-test/r/information_schema_all_engines.result @@ -16,6 +16,7 @@ INDEX_STATISTICS INNODB_BUFFER_POOL_PAGES INNODB_BUFFER_POOL_PAGES_BLOB INNODB_BUFFER_POOL_PAGES_INDEX +INNODB_CHANGED_PAGES INNODB_CMP INNODB_CMPMEM INNODB_CMPMEM_RESET @@ -88,6 +89,7 @@ INDEX_STATISTICS TABLE_SCHEMA INNODB_BUFFER_POOL_PAGES page_type INNODB_BUFFER_POOL_PAGES_BLOB space_id INNODB_BUFFER_POOL_PAGES_INDEX index_id +INNODB_CHANGED_PAGES space_id INNODB_CMP page_size INNODB_CMPMEM page_size INNODB_CMPMEM_RESET page_size @@ -160,6 +162,7 @@ INDEX_STATISTICS TABLE_SCHEMA INNODB_BUFFER_POOL_PAGES page_type INNODB_BUFFER_POOL_PAGES_BLOB space_id INNODB_BUFFER_POOL_PAGES_INDEX index_id +INNODB_CHANGED_PAGES space_id INNODB_CMP page_size INNODB_CMPMEM page_size INNODB_CMPMEM_RESET page_size @@ -238,6 +241,7 @@ INDEX_STATISTICS information_schema.INDEX_STATISTICS 1 INNODB_BUFFER_POOL_PAGES information_schema.INNODB_BUFFER_POOL_PAGES 1 INNODB_BUFFER_POOL_PAGES_BLOB information_schema.INNODB_BUFFER_POOL_PAGES_BLOB 1 INNODB_BUFFER_POOL_PAGES_INDEX information_schema.INNODB_BUFFER_POOL_PAGES_INDEX 1 +INNODB_CHANGED_PAGES information_schema.INNODB_CHANGED_PAGES 1 INNODB_CMP information_schema.INNODB_CMP 1 INNODB_CMPMEM information_schema.INNODB_CMPMEM 1 INNODB_CMPMEM_RESET information_schema.INNODB_CMPMEM_RESET 1 @@ -299,6 +303,7 @@ Database: information_schema | INNODB_BUFFER_POOL_PAGES | | INNODB_BUFFER_POOL_PAGES_BLOB | | INNODB_BUFFER_POOL_PAGES_INDEX | +| INNODB_CHANGED_PAGES | | INNODB_CMP | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | @@ -361,6 +366,7 @@ Database: INFORMATION_SCHEMA | INNODB_BUFFER_POOL_PAGES | | INNODB_BUFFER_POOL_PAGES_BLOB | | INNODB_BUFFER_POOL_PAGES_INDEX | +| INNODB_CHANGED_PAGES | | INNODB_CMP | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | @@ -411,5 +417,5 @@ Wildcard: inf_rmation_schema | information_schema | SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 57 +information_schema 58 mysql 23 diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 655303be7f4..7110f541fb7 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -327,9 +327,11 @@ select row_count(); row_count() 3 insert ignore into t1 values (1, 1); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' select row_count(); row_count() -0 +-1 replace into t1 values (1, 11); select row_count(); row_count() @@ -371,7 +373,15 @@ drop table t1,t2; create table t1 (id int primary key auto_increment, data int, unique(data)); insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90); +Warnings: +Warning 1062 Duplicate entry '110' for key 'data' +Warning 1062 Duplicate entry '120' for key 'data' +Warning 1062 Duplicate entry '100' for key 'data' insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150); +Warnings: +Warning 1062 Duplicate entry '110' for key 'data' +Warning 1062 Duplicate entry '120' for key 'data' +Warning 1062 Duplicate entry '100' for key 'data' select * from t1 order by id; id data 1 100 diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index c952b914167..8bfc4e9215e 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -6,6 +6,10 @@ insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1; insert into t2 (payoutID) SELECT payoutID+10 FROM t1; ERROR 23000: Duplicate entry '16' for key 'PRIMARY' insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1; +Warnings: +Warning 1062 Duplicate entry '16' for key 'PRIMARY' +Warning 1062 Duplicate entry '16' for key 'PRIMARY' +Warning 1062 Duplicate entry '22' for key 'PRIMARY' select * from t2; payoutID 1 diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index c3acea5d11f..1987c5c0559 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -172,11 +172,15 @@ DROP TABLE t2; create table t1 (a int not null unique) engine=myisam; insert into t1 values (1),(2); insert ignore into t1 select 1 on duplicate key update a=2; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' select * from t1; a 1 2 insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ; +Warnings: +Warning 1062 Duplicate entry '2' for key 'a' select * from t1; a 1 diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 6471a2f8bbd..bb455f2b39a 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5589,4 +5589,52 @@ set join_buffer_size=default; set join_cache_level=default; set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3; +# +# Bug #1058071: LEFT JOIN using blobs +# (mdev-564) when join buffer size is small +# +CREATE TABLE t1 ( +col269 decimal(31,10) unsigned DEFAULT NULL, +col280 multipoint DEFAULT NULL, +col281 tinyint(1) DEFAULT NULL, +col282 time NOT NULL, +col284 datetime DEFAULT NULL, +col286 date DEFAULT NULL, +col287 datetime DEFAULT NULL, +col288 decimal(30,29) DEFAULT NULL, +col291 time DEFAULT NULL, +col292 time DEFAULT NULL +) ENGINE=Aria; +INSERT INTO t1 VALUES +(0.0,PointFromText('POINT(9 0)'),0,'11:24:05','2013-04-14 21:30:28',NULL,'2011-12-20 06:00:34',9.9,'13:04:39',NULL), +(0.0,NULL,127,'05:43:12','2012-09-05 06:15:27','2027-01-01','2011-10-29 10:48:29',0.0,'06:24:05','11:33:37'), +(0.0,NULL,127,'12:54:41','2013-01-12 11:32:58','2011-11-03','2013-01-03 02:00:34',00,'11:54:15','20:19:15'), +(0.0,PointFromText('POINT(9 0)'),0,'19:48:07','2012-07-16 15:45:25','2012-03-25','2013-09-07 17:21:52',0.5,'17:36:54','21:24:19'), +(0.0,PointFromText('POINT(9 0)'),0,'03:43:48','2012-09-28 00:00:00','2012-06-26','2011-11-16 05:01:09',00,'01:25:42','19:30:06'), +(0.0,LineStringFromText('LINESTRING(0 0,9 9,0 0,9 0,0 0)'),127,'11:33:21','2012-03-31 10:29:22','2012-10-10','2012-04-21 19:21:06',NULL,'05:13:22','09:48:34'), +(NULL,PointFromText('POINT(9 0)'),127,'00:00:00','0000-00-00','2012-04-04 21:26:12','2013-03-04',0.0,'12:54:30',NULL), +(NULL,PointFromText('POINT(9 0)'),1,'00:00:00','2013-05-01 22:37:49','2013-06-26','2012-09-22 17:31:03',0.0,'08:09:57','11:15:36'); +Warnings: +Note 1265 Data truncated for column 'col286' at row 7 +CREATE TABLE t2 (b int) ENGINE=Aria; +INSERT INTO t2 VALUES (NULL); +CREATE TABLE t3 (c int) ENGINE=Aria; +INSERT INTO t3 VALUES (NULL); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; +set join_buffer_size=128; +EXPLAIN +SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 +GROUP BY elt(t1.col282,1,t1.col280); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 +GROUP BY elt(t1.col282,1,t1.col280); +c +1 +set join_buffer_size=default; +set optimizer_switch=@tmp_optimizer_switch; +DROP table t1,t2,t3; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 1a6f8534cc2..3ccd1451bc4 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index e5f5857d2c9..6f90555325b 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -702,6 +702,8 @@ id 2 99 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); +Warnings: +Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' commit; select id,id3 from t1; id id3 diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index 03d212dbc04..bd50df7c40e 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -278,6 +278,8 @@ bb-1 NULL cc-2 NULL-1 drop table t1, t2, t3, t4; create table t1 (a int, b int not null,unique key (a,b),index(b)); insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); diff --git a/mysql-test/r/mysqld--help,win.rdiff b/mysql-test/r/mysqld--help,win.rdiff index 957292b265f..cd39445f3e6 100644 --- a/mysql-test/r/mysqld--help,win.rdiff +++ b/mysql-test/r/mysqld--help,win.rdiff @@ -1,6 +1,6 @@ ---- r/mysqld--help.result 2012-01-13 16:50:49.000000000 +0100 -+++ r/mysqld--help-win.result 2012-01-19 14:12:00.000000000 +0100 -@@ -240,7 +240,6 @@ +--- mysql-test/r/mysqld--help.result 2012-09-08 22:22:06 +0000 ++++ mysql-test/r/mysqld--help.result 2012-10-01 14:03:59 +0000 +@@ -244,7 +244,6 @@ The number of segments in a key cache -L, --language=name Client error messages in given language. May be given as a full path. Deprecated. Use --lc-messages-dir instead. @@ -8,7 +8,7 @@ --lc-messages=name Set the language used for the error messages. -L, --lc-messages-dir=name Directory where error messages are -@@ -436,6 +435,7 @@ +@@ -440,6 +439,7 @@ NULLS_UNEQUAL (default behavior for 4.1 and later), NULLS_EQUAL (emulate 4.0 behavior), and NULLS_IGNORED --myisam-use-mmap Use memory mapping for reading and writing MyISAM tables @@ -16,7 +16,7 @@ --net-buffer-length=# Buffer length for TCP/IP and socket communication --net-read-timeout=# -@@ -695,6 +695,9 @@ +@@ -707,6 +707,9 @@ files within specified directory --server-id=# Uniquely identifies the server instance in the community of replication partners @@ -26,7 +26,7 @@ --show-slave-auth-info Show user and password in SHOW SLAVE HOSTS on this master. -@@ -759,6 +762,10 @@ +@@ -774,6 +777,10 @@ Log slow queries to given log file. Defaults logging to 'hostname'-slow.log. Must be enabled to activate other slow log options @@ -37,7 +37,7 @@ --socket=name Socket file to use for connection --sort-buffer-size=# Each thread that needs to do a sort allocates a buffer of -@@ -767,6 +774,7 @@ +@@ -782,6 +789,7 @@ for the complete list of valid sql modes --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) @@ -45,7 +45,7 @@ --stored-program-cache=# The soft upper limit for number of cached stored routines for one connection. -@@ -807,8 +815,8 @@ +@@ -822,8 +830,8 @@ size, MySQL will automatically convert it to an on-disk MyISAM or Aria table -t, --tmpdir=name Path for temporary files. Several paths may be specified, @@ -56,15 +56,15 @@ --transaction-alloc-block-size=# Allocation block size for transactions to be stored in binary log -@@ -908,7 +916,6 @@ +@@ -923,7 +931,6 @@ key-cache-age-threshold 300 + key-cache-block-size 1024 key-cache-division-limit 100 key-cache-segments 0 - language MYSQL_SHAREDIR/ -large-pages FALSE lc-messages en_US lc-messages-dir MYSQL_SHAREDIR/ lc-time-names en_US -@@ -971,6 +978,7 @@ +@@ -986,6 +993,7 @@ myisam-repair-threads 1 myisam-sort-buffer-size 8388608 myisam-stats-method nulls_unequal myisam-use-mmap FALSE @@ -72,7 +72,7 @@ net-buffer-length 16384 net-read-timeout 30 net-retry-count 10 -@@ -1035,6 +1043,8 @@ +@@ -1051,6 +1059,8 @@ safe-user-create FALSE secure-auth FALSE secure-file-priv (No default value) server-id 0 @@ -81,7 +81,7 @@ show-slave-auth-info FALSE skip-grant-tables TRUE skip-name-resolve FALSE -@@ -1050,6 +1060,7 @@ +@@ -1067,6 +1077,7 @@ slave-transaction-retries 10 slave-type-conversions slow-launch-time 2 slow-query-log FALSE @@ -89,3 +89,4 @@ sort-buffer-size 2097152 sql-mode stack-trace TRUE + diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 718909d1b27..c36cc96bb1a 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -204,6 +204,10 @@ The following options may be given as the first argument: -?, --help Display this help and exit. --ignore-builtin-innodb Disable initialization of builtin InnoDB plugin + --ignore-db-dirs=name + Specifies a directory to add to the ignore list when + collecting database names from the datadir. Put a blank + argument to reset the list accumulated so far. --init-connect=name Command(s) that are executed for each new connection (unless the user has SUPER privilege) --init-file=name Read SQL commands from this file at startup @@ -909,6 +913,7 @@ general-log FALSE group-concat-max-len 1024 help TRUE ignore-builtin-innodb FALSE +ignore-db-dirs init-connect init-file (No default value) init-rpl-role MASTER diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 35c6ebe1442..ba79011f53e 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -1,6 +1,8 @@ drop table if exists t1,t2; create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam; insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' explain select * from t1 where a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 5 const 3 Using where; Using index diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result index 439930b8fe4..b86925e0eb8 100644 --- a/mysql-test/r/openssl_1.result +++ b/mysql-test/r/openssl_1.result @@ -44,9 +44,9 @@ ERROR 42000: DELETE command denied to user 'ssl_user4'@'localhost' for table 't1 drop user ssl_user1@localhost, ssl_user2@localhost, ssl_user3@localhost, ssl_user4@localhost, ssl_user5@localhost; drop table t1; -mysqltest: Could not open connection 'default': 2026 SSL connection error: ASN: bad other signature confirmation -mysqltest: Could not open connection 'default': 2026 SSL connection error: ASN: bad other signature confirmation -mysqltest: Could not open connection 'default': 2026 SSL connection error: ASN: bad other signature confirmation +mysqltest: Could not open connection 'default': 2026 SSL connection error: xxxx +mysqltest: Could not open connection 'default': 2026 SSL connection error: xxxx +mysqltest: Could not open connection 'default': 2026 SSL connection error: xxxx SSL error: Unable to get private key from '' mysqltest: Could not open connection 'default': 2026 SSL connection error: Unable to get private key SSL error: Unable to get certificate from '' diff --git a/mysql-test/r/order_fill_sortbuf.result b/mysql-test/r/order_fill_sortbuf.result index 2226e842901..6a0bd9d966b 100644 --- a/mysql-test/r/order_fill_sortbuf.result +++ b/mysql-test/r/order_fill_sortbuf.result @@ -1,4 +1,5 @@ drop table if exists t1,t2; +set @@sort_buffer_size=32804; CREATE TABLE `t1` ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index d2f740a06f4..0a3d16cf48e 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -777,3 +777,14 @@ execute stmt1 ; prepare stmt1 from ' select * from t5 ' ; execute stmt1 ; drop table t1, t5, t9; +# +# testcase for bug#11765413 - Crash with dependent subquery and +# prepared statement +create table t1 (c1 int); +insert into t1 values (1); +prepare stmt1 from "select 1 from t1 where 1=(select 1 from t1 having c1)"; +execute stmt1; +1 +1 +drop prepare stmt1; +drop table t1; diff --git a/mysql-test/r/replace.result b/mysql-test/r/replace.result index 842302c89ac..59b8565f671 100644 --- a/mysql-test/r/replace.result +++ b/mysql-test/r/replace.result @@ -13,9 +13,9 @@ drop table t1; create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value"); insert into t1 values (126,"first"),(63, "middle"),(0,"last"); insert into t1 values (0,"error"); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'a' at row 1 replace into t1 values (0,"error"); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' +ERROR 22003: Out of range value for column 'a' at row 1 replace into t1 values (126,"first updated"); replace into t1 values (63,default); select * from t1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 30d5ca00bc0..378b20829f3 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4887,7 +4887,7 @@ CREATE TABLE t5 (f1 int) ; INSERT INTO t5 VALUES (20),(5); CREATE TABLE t6(f1 int); INSERT INTO t6 VALUES (9),(7); -SET SESSION join_buffer_size = 2048; +SET SESSION join_buffer_size = 2176; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra @@ -4901,50 +4901,50 @@ SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; f1 f1 f1 f1 f2 f1 f1 3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 -3 9 NULL NULL NULL 20 7 -7 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 5 7 -7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 20 9 3 9 NULL NULL NULL 20 9 -18 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 20 7 -18 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 5 9 -18 9 NULL NULL NULL 5 7 -3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 20 9 -18 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 20 7 7 9 NULL NULL NULL 20 7 18 9 NULL NULL NULL 20 7 -7 9 NULL NULL NULL 5 9 -18 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 7 -18 9 NULL NULL NULL 5 7 -3 9 NULL NULL NULL 20 9 -7 9 NULL NULL NULL 20 9 3 9 NULL NULL NULL 20 7 7 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 5 7 -7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 20 9 3 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 5 9 18 9 NULL NULL NULL 20 7 3 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 5 7 +18 9 NULL NULL NULL 5 7 +3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 5 9 18 9 NULL NULL NULL 5 9 3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 20 7 +18 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 18 9 NULL NULL NULL 20 9 +7 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 20 7 7 9 NULL NULL NULL 20 7 18 9 NULL NULL NULL 20 7 +7 9 NULL NULL NULL 5 7 +18 9 NULL NULL NULL 5 9 +3 9 NULL NULL NULL 5 9 7 9 NULL NULL NULL 5 9 18 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 5 7 +3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 SET SESSION join_buffer_size = DEFAULT; diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index a9c74afdd9c..b5c6f89b87b 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -4898,7 +4898,7 @@ CREATE TABLE t5 (f1 int) ; INSERT INTO t5 VALUES (20),(5); CREATE TABLE t6(f1 int); INSERT INTO t6 VALUES (9),(7); -SET SESSION join_buffer_size = 2048; +SET SESSION join_buffer_size = 2176; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra @@ -4913,48 +4913,48 @@ f1 f1 f1 f1 f2 f1 f1 3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 18 9 NULL NULL NULL 20 9 -3 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 9 -18 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 20 7 -7 9 NULL NULL NULL 20 7 -18 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 5 7 -7 9 NULL NULL NULL 5 7 -18 9 NULL NULL NULL 5 7 3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 18 9 NULL NULL NULL 20 9 -3 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 9 -18 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 20 7 -7 9 NULL NULL NULL 20 7 -18 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 5 7 -7 9 NULL NULL NULL 5 7 -18 9 NULL NULL NULL 5 7 3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 18 9 NULL NULL NULL 20 9 -3 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 9 -18 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 20 7 -7 9 NULL NULL NULL 20 7 -18 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 5 7 -7 9 NULL NULL NULL 5 7 -18 9 NULL NULL NULL 5 7 3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 18 9 NULL NULL NULL 20 9 3 9 NULL NULL NULL 5 9 7 9 NULL NULL NULL 5 9 18 9 NULL NULL NULL 5 9 +3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 5 9 +3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 5 9 +3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 5 9 3 9 NULL NULL NULL 20 7 7 9 NULL NULL NULL 20 7 18 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 20 7 +7 9 NULL NULL NULL 20 7 +18 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 20 7 +7 9 NULL NULL NULL 20 7 +18 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 20 7 +7 9 NULL NULL NULL 20 7 +18 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 5 7 +18 9 NULL NULL NULL 5 7 +3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 5 7 +18 9 NULL NULL NULL 5 7 +3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 5 7 +18 9 NULL NULL NULL 5 7 3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 30d5ca00bc0..378b20829f3 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -4887,7 +4887,7 @@ CREATE TABLE t5 (f1 int) ; INSERT INTO t5 VALUES (20),(5); CREATE TABLE t6(f1 int); INSERT INTO t6 VALUES (9),(7); -SET SESSION join_buffer_size = 2048; +SET SESSION join_buffer_size = 2176; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra @@ -4901,50 +4901,50 @@ SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; f1 f1 f1 f1 f2 f1 f1 3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 -3 9 NULL NULL NULL 20 7 -7 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 5 7 -7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 20 9 3 9 NULL NULL NULL 20 9 -18 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 20 7 -18 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 5 9 -18 9 NULL NULL NULL 5 7 -3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 20 9 -18 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 20 7 7 9 NULL NULL NULL 20 7 18 9 NULL NULL NULL 20 7 -7 9 NULL NULL NULL 5 9 -18 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 7 -18 9 NULL NULL NULL 5 7 -3 9 NULL NULL NULL 20 9 -7 9 NULL NULL NULL 20 9 3 9 NULL NULL NULL 20 7 7 9 NULL NULL NULL 20 7 -3 9 NULL NULL NULL 5 9 -7 9 NULL NULL NULL 5 9 -3 9 NULL NULL NULL 5 7 -7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 20 9 3 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 5 9 18 9 NULL NULL NULL 20 7 3 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 5 7 +18 9 NULL NULL NULL 5 7 +3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 5 9 18 9 NULL NULL NULL 5 9 3 9 NULL NULL NULL 5 9 +7 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 3 9 NULL NULL NULL 5 7 +7 9 NULL NULL NULL 20 7 +18 9 NULL NULL NULL 20 9 +3 9 NULL NULL NULL 20 9 7 9 NULL NULL NULL 20 9 18 9 NULL NULL NULL 20 9 +7 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 20 7 +3 9 NULL NULL NULL 20 7 7 9 NULL NULL NULL 20 7 18 9 NULL NULL NULL 20 7 +7 9 NULL NULL NULL 5 7 +18 9 NULL NULL NULL 5 9 +3 9 NULL NULL NULL 5 9 7 9 NULL NULL NULL 5 9 18 9 NULL NULL NULL 5 9 +18 9 NULL NULL NULL 5 7 +3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 SET SESSION join_buffer_size = DEFAULT; diff --git a/mysql-test/r/strict_autoinc_1myisam.result b/mysql-test/r/strict_autoinc_1myisam.result index afcccb1c40f..b22540f295b 100644 --- a/mysql-test/r/strict_autoinc_1myisam.result +++ b/mysql-test/r/strict_autoinc_1myisam.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select * from t1; a -127 drop table t1; diff --git a/mysql-test/r/strict_autoinc_2innodb.result b/mysql-test/r/strict_autoinc_2innodb.result index e534286e2a2..1cf720da12a 100644 --- a/mysql-test/r/strict_autoinc_2innodb.result +++ b/mysql-test/r/strict_autoinc_2innodb.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select * from t1; a -127 drop table t1; diff --git a/mysql-test/r/strict_autoinc_3heap.result b/mysql-test/r/strict_autoinc_3heap.result index 0a31da04460..21eea537af5 100644 --- a/mysql-test/r/strict_autoinc_3heap.result +++ b/mysql-test/r/strict_autoinc_3heap.result @@ -20,9 +20,7 @@ count(*) 0 set @@sql_mode=@org_mode; insert into t1 values(null); -Warnings: -Warning 1264 Out of range value for column 'a' at row 1 +ERROR 22003: Out of range value for column 'a' at row 1 select * from t1; a -127 drop table t1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f775336299b..a4bad836d1f 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6638,6 +6638,23 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) set optimizer_switch=@mdev367_optimizer_switch; DROP TABLE t1; # +# MDEV-521 single value subselect transformation problem +# +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('u1'),('u2'); +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +FLUSH TABLES; +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +DROP TABLE t1; +# return optimizer switch changed in the beginning of this test +set optimizer_switch=@subselect_tmp; +# # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # @@ -6847,6 +6864,27 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # +# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +EXPLAIN +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +drop table t1, t2, t3; +# # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery # CREATE TABLE t1 (a INT, KEY(a)); diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index ed00e4ef684..7eff7f949a8 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -179,4 +179,23 @@ pk a b SET optimizer_switch=@tmp_optimizer_switch; DROP VIEW v1; DROP TABLE t1,t2,t3; +# +# MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed +# +CREATE TABLE t1 (a int, b int, INDEX idx(a)); +INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1); +CREATE TABLE t2 (a int, b int, INDEX idx(a)); +INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (1,0), (1,1), (1,3); +SELECT * FROM t3 +WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2 +WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9 +AND t3.b = t1.b +GROUP BY t1.b); +a b +1 0 +1 1 +1 3 +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect2_test_tmp; diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 0f9912fc1f3..c35075d80a1 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -206,6 +206,165 @@ default(a) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DROP TABLE t; # +# LP BUG#1009187, MDEV-373, MYSQL bug#58628 +# Wrong result for a query with [NOT] IN subquery predicate if +# the left part of the predicate is explicit NULL +# +CREATE TABLE t1 (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t1 VALUES (0,10), (1,20), (2,30), (3,40); +CREATE TABLE t2a (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO t2a VALUES (0,0), (1,1), (2,2), (3,3); +CREATE TABLE t2b (pk INT, i INT); +INSERT INTO t2b VALUES (0,0), (1,1), (2,2), (3,3); +CREATE TABLE t2c (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t2c VALUES (0,0), (1,1), (2,2), (3,3); +create index it2c on t2c (i,pk); +CREATE TABLE t2d (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i)); +INSERT INTO t2d VALUES (0,0), (1,1), (2,2), (3,3); +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2a unique_subquery PRIMARY PRIMARY 8 const,test.t1.pk 1 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE 1+NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2c index_subquery it2c it2c 8 const,test.t1.pk 2 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2d const PRIMARY PRIMARY 4 const 1 Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2a eq_ref PRIMARY PRIMARY 8 const,test.t1.pk 1 Using where; Using index; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk) +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk) +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2c ref it2c it2c 8 const,test.t1.pk 2 Using where; Using index; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk) +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2d const PRIMARY PRIMARY 4 const 1 Using where; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk) from t1; +(NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk) +1 +NULL +1 +1 +drop table t1, t2a, t2b, t2c, t2d; +# # End of 5.1 tests. # # @@ -271,7 +430,7 @@ set @old_optimizer_switch = @@session.optimizer_switch, SET SESSION OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=on'; SET SESSION engine_condition_pushdown = 1; Warnings: -Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 7.0. Please use '@@optimizer_switch' instead +Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead SELECT `time_nokey` G1 FROM t1 WHERE ( `varchar_nokey` , `varchar_key` ) IN ( SELECT `varchar_nokey` , `varchar_nokey` ) AND `varchar_key` >= 'c' HAVING G1 ORDER BY `pk` ; @@ -279,7 +438,7 @@ G1 set @@session.optimizer_switch = @old_optimizer_switch, @@session.engine_condition_pushdown = @old_engine_condition_pushdown; Warnings: -Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 7.0. Please use '@@optimizer_switch' instead +Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead DROP TABLE t1; # # During work with BUG#45863 I had problems with a query that was @@ -466,7 +625,7 @@ SELECT @old_icp:=@@engine_condition_pushdown; # SET SESSION engine_condition_pushdown = 'ON'; Warnings: -Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 7.0. Please use '@@optimizer_switch' instead +Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead SELECT pk FROM t2 @@ -481,7 +640,7 @@ pk # Restore old value for Index condition pushdown SET SESSION engine_condition_pushdown=@old_icp; Warnings: -Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 7.0. Please use '@@optimizer_switch' instead +Warning 1287 '@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead DROP TABLE t1,t2; # # End of 5.3 tests. @@ -1192,7 +1351,7 @@ ON SUBQUERY2_t3.f2) GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 Using temporary; Using filesort -1 PRIMARY t1 index NULL f4 5 NULL 11 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where 2 DEPENDENT SUBQUERY SUBQUERY2_t1 system NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY SUBQUERY2_t2 index NULL f4 5 NULL 11 Using index 2 DEPENDENT SUBQUERY SUBQUERY2_t3 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index e72d25fdafa..cb9847a0d99 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6637,6 +6637,23 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) set optimizer_switch=@mdev367_optimizer_switch; DROP TABLE t1; # +# MDEV-521 single value subselect transformation problem +# +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('u1'),('u2'); +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +FLUSH TABLES; +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +DROP TABLE t1; +# return optimizer switch changed in the beginning of this test +set optimizer_switch=@subselect_tmp; +# # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # @@ -6845,6 +6862,26 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # +# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +EXPLAIN +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +drop table t1, t2, t3; +# # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery # CREATE TABLE t1 (a INT, KEY(a)); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 9030265356b..63eeb816b38 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6633,6 +6633,23 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) set optimizer_switch=@mdev367_optimizer_switch; DROP TABLE t1; # +# MDEV-521 single value subselect transformation problem +# +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('u1'),('u2'); +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +FLUSH TABLES; +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +DROP TABLE t1; +# return optimizer switch changed in the beginning of this test +set optimizer_switch=@subselect_tmp; +# # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # @@ -6842,6 +6859,27 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # +# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +EXPLAIN +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +drop table t1, t2, t3; +# # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery # CREATE TABLE t1 (a INT, KEY(a)); diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index e68f5990c08..6fd21f8d0b0 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6644,6 +6644,23 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) set optimizer_switch=@mdev367_optimizer_switch; DROP TABLE t1; # +# MDEV-521 single value subselect transformation problem +# +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('u1'),('u2'); +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +FLUSH TABLES; +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +DROP TABLE t1; +# return optimizer switch changed in the beginning of this test +set optimizer_switch=@subselect_tmp; +# # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # @@ -6853,6 +6870,27 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # +# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +EXPLAIN +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +drop table t1, t2, t3; +# # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery # CREATE TABLE t1 (a INT, KEY(a)); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index f0ce541294a..b924a18ca8f 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6633,6 +6633,23 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) set optimizer_switch=@mdev367_optimizer_switch; DROP TABLE t1; # +# MDEV-521 single value subselect transformation problem +# +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('u1'),('u2'); +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +FLUSH TABLES; +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +f1 +u1 +u2 +DROP TABLE t1; +# return optimizer switch changed in the beginning of this test +set optimizer_switch=@subselect_tmp; +# # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # @@ -6842,6 +6859,27 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # +# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +EXPLAIN +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +drop table t1, t2, t3; +# # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery # CREATE TABLE t1 (a INT, KEY(a)); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index ef998abafcf..6047d00c858 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -165,6 +165,8 @@ select @log; (BEFORE_INSERT: new=(id=1, data=1))(AFTER_INSERT: new=(id=1, data=1)) set @log:= ""; insert ignore t1 values (1, 2); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' select @log; @log (BEFORE_INSERT: new=(id=1, data=2)) diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index e0cfe1d346c..e055a333faf 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -538,7 +538,7 @@ Warning 1292 Truncated incorrect read_buffer_size value: '100' set read_rnd_buffer_size=100; set global rpl_recovery_rank=100; Warnings: -Warning 1287 The syntax '@@rpl_recovery_rank' is deprecated and will be removed in MariaDB 7.0. +Warning 1287 '@@rpl_recovery_rank' is deprecated and will be removed in a future release. set global server_id=100; set global slow_launch_time=100; set sort_buffer_size=100; @@ -1060,7 +1060,7 @@ set global net_write_timeout =@my_net_write_timeout; set global net_read_timeout =@my_net_read_timeout; set global rpl_recovery_rank =@my_rpl_recovery_rank; Warnings: -Warning 1287 The syntax '@@rpl_recovery_rank' is deprecated and will be removed in MariaDB 7.0. +Warning 1287 '@@rpl_recovery_rank' is deprecated and will be removed in a future release. set global server_id =@my_server_id; set global slow_launch_time =@my_slow_launch_time; set global default_storage_engine =@my_storage_engine; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 5af20fd0c3c..1bcc9fb727f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4530,6 +4530,55 @@ WHERE t4.a >= v1.a); a a DROP VIEW v1; DROP TABLE t1,t2,t3,t4; +# +# LP bug #823237: dependent subquery with LEFT JOIN +# referencing view in WHERE +# (duplicate of LP bug #823189) +# +CREATE TABLE t1 (a int); +CREATE TABLE t2 ( b int, d int, e int); +INSERT INTO t2 VALUES (7,8,0); +CREATE TABLE t3 ( c int); +INSERT INTO t3 VALUES (0); +CREATE TABLE t4 (a int, b int, c int); +INSERT INTO t4 VALUES (93,1,0), (95,NULL,0); +CREATE VIEW v4 AS SELECT * FROM t4; +EXPLAIN EXTENDED +SELECT * FROM t3 , t4 +WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) +WHERE t2.b > t4.b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1276 Field or reference 'test.t4.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from dual where (7 > `test`.`t4`.`b`)))) +SELECT * FROM t3 , t4 +WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) +WHERE t2.b > t4.b); +c a b c +0 93 1 0 +EXPLAIN EXTENDED +SELECT * FROM t3, v4 +WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) +WHERE t2.b > v4.b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1276 Field or reference 'v4.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from dual where (7 > `test`.`t4`.`b`)))) +SELECT * FROM t3, v4 +WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) +WHERE t2.b > v4.b); +c a b c +0 93 1 0 +DROP VIEW v4; +DROP TABLE t1,t2,t3,t4; drop table if exists t_9801; drop view if exists v_9801; create table t_9801 (s1 int); @@ -4730,6 +4779,49 @@ id id bbb iddqd val1 30631 NULL NULL NULL NULL drop view v2; drop table t1,t2; +# +# MDEV-589 (LP BUG#1007647) : +# Assertion `vcol_table == 0 || vcol_table == table' failed in +# fill_record(THD*, List<Item>&, List<Item>&, bool) +# +CREATE TABLE t1 (f1 INT, f2 INT); +CREATE TABLE t2 (f1 INT, f2 INT); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t1 AS a2; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1; +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t2 AS a2; +CREATE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM v3; +INSERT INTO v3 (f1, f2) VALUES (1, 2); +ERROR HY000: Can not modify more than one base table through a join view 'test.v3' +INSERT INTO v1 (f1, f2) VALUES (1, 2); +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +INSERT INTO v4 (f1, f2) VALUES (1, 2); +ERROR HY000: Can not modify more than one base table through a join view 'test.v4' +INSERT INTO v2 (f1, f2) VALUES (1, 2); +ERROR HY000: Can not modify more than one base table through a join view 'test.v2' +drop view v4,v3,v2,v1; +drop table t1,t2; +# +# MDEV-3799 fix of above bugfix (MDEV-589) +# Wrong result (NULLs instead of real values) with RIGHT JOIN +# in a FROM subquery and derived_merge=on +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(8); +SELECT * FROM ( +SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2 +) AS alias; +f1 f2 +NULL 7 +NULL 8 +SELECT * FROM ( +SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2 +) AS alias; +f2 f1 +7 NULL +8 NULL +drop tables t1,t2; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result index 069d3c25826..15b750d2fd3 100644 --- a/mysql-test/r/xtradb_mrr.result +++ b/mysql-test/r/xtradb_mrr.result @@ -276,6 +276,8 @@ bb-1 NULL cc-2 NULL-1 drop table t1, t2, t3, t4; create table t1 (a int, b int not null,unique key (a,b),index(b)); insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); |