diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/main/create.result | 11 | ||||
| -rw-r--r-- | mysql-test/main/create.test | 13 | ||||
| -rw-r--r-- | mysql-test/main/func_group.result | 11 | ||||
| -rw-r--r-- | mysql-test/main/func_group.test | 12 | ||||
| -rw-r--r-- | mysql-test/main/insert_select.result | 13 | ||||
| -rw-r--r-- | mysql-test/main/insert_select.test | 17 | ||||
| -rw-r--r-- | mysql-test/main/join_nested.result | 52 | ||||
| -rw-r--r-- | mysql-test/main/join_nested.test | 38 | ||||
| -rw-r--r-- | mysql-test/main/join_nested_jcl6.result | 52 | ||||
| -rw-r--r-- | mysql-test/main/ps.result | 18 | ||||
| -rw-r--r-- | mysql-test/main/ps.test | 39 |
11 files changed, 271 insertions, 5 deletions
diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index 2daced2f0a2..23c202b5576 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -2032,6 +2032,17 @@ Warnings: Warning 1280 Name 'foo' ignored for PRIMARY key. DROP TABLE t1; # +# MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +# +create table t1(c1 varchar(1)); +insert into t1(c1) values('#'); +select @@sql_mode like '%strict_all_tables%'; +@@sql_mode like '%strict_all_tables%' +0 +create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1; +ERROR 22007: Truncated incorrect DECIMAL value: '#' +drop table t1; +# # End of 10.3 tests # # diff --git a/mysql-test/main/create.test b/mysql-test/main/create.test index cd51af0557b..6eb222c872d 100644 --- a/mysql-test/main/create.test +++ b/mysql-test/main/create.test @@ -1893,6 +1893,19 @@ CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAI DROP TABLE t1; --echo # +--echo # MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +--echo # + +create table t1(c1 varchar(1)); +insert into t1(c1) values('#'); + +select @@sql_mode like '%strict_all_tables%'; +--error ER_TRUNCATED_WRONG_VALUE +create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1; + +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 3d521f2e00b..a0ee121df06 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2,t3,t4,t5,t6; set @sav_dpi= @@div_precision_increment; set div_precision_increment= 5; show variables like 'div_precision_increment'; @@ -2567,5 +2566,15 @@ stddev_samp(i) stddev_pop(i) stddev(i) std(i) drop view v1; drop table t1; # +# MDEV-29988: Major performance regression with 10.6.11 +# +create table t1 (a varchar(10) charset utf8mb4, b int, c int); +insert t1 values (1,2,3),(4,5,6),(1,7,8); +select concat(a,":",group_concat(b)) from t1 group by a; +concat(a,":",group_concat(b)) +1:2,7 +4:5 +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index ac4ca32e7f5..e5ae33f7208 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -2,10 +2,6 @@ # simple test of all group functions # ---disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6; ---enable_warnings - set @sav_dpi= @@div_precision_increment; set div_precision_increment= 5; show variables like 'div_precision_increment'; @@ -1802,5 +1798,13 @@ drop view v1; drop table t1; --echo # +--echo # MDEV-29988: Major performance regression with 10.6.11 +--echo # +create table t1 (a varchar(10) charset utf8mb4, b int, c int); +insert t1 values (1,2,3),(4,5,6),(1,7,8); +select concat(a,":",group_concat(b)) from t1 group by a; +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index 6d939d0ed8e..29618c6ddd4 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -1042,4 +1042,17 @@ select * from t1; a deallocate prepare stmt; drop table t1,t2,t3; +# +# MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +# +create table t1(c1 varchar(1)); +create table t2(c1 varchar(1)); +insert into t1(c1) values('#'); +select @@sql_mode like '%strict_all_tables%'; +@@sql_mode like '%strict_all_tables%' +0 +insert into t2(c1) select if(c1 = '#', c1 = 0, c1) as c1 from t1; +drop table t1, t2; +# # End of 10.3 test +# diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 6baa7e43c34..a3604e38f34 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -595,4 +595,21 @@ deallocate prepare stmt; drop table t1,t2,t3; + +--echo # +--echo # MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +--echo # + +create table t1(c1 varchar(1)); +create table t2(c1 varchar(1)); + +insert into t1(c1) values('#'); + +select @@sql_mode like '%strict_all_tables%'; +insert into t2(c1) select if(c1 = '#', c1 = 0, c1) as c1 from t1; + +drop table t1, t2; + +--echo # --echo # End of 10.3 test +--echo # diff --git a/mysql-test/main/join_nested.result b/mysql-test/main/join_nested.result index 4f6583b6fbc..f98a9fad993 100644 --- a/mysql-test/main/join_nested.result +++ b/mysql-test/main/join_nested.result @@ -1999,3 +1999,55 @@ Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.` DROP TABLE t1,t2,t3; set join_cache_level= @save_join_cache_level; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-27624: Nested left joins with not_exists optimization +# for most inner left join +# +set @save_join_cache_level= @@join_cache_level; +CREATE TABLE t1 (a INT NOT NULL, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1); +CREATE TABLE t2(a INT NOT NULL); +INSERT INTO t2 VALUES (1), (2); +CREATE TABLE t3(a INT not null, b INT); +INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1); +set join_cache_level = 0; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +set join_cache_level = 2; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists; Using join buffer (incremental, BNL join) +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +DROP TABLE t1, t2, t3; +set join_cache_level= @save_join_cache_level; +# end of 10.3 tests diff --git a/mysql-test/main/join_nested.test b/mysql-test/main/join_nested.test index cc1bd327031..8ac6b5a1d6b 100644 --- a/mysql-test/main/join_nested.test +++ b/mysql-test/main/join_nested.test @@ -1419,3 +1419,41 @@ DROP TABLE t1,t2,t3; set join_cache_level= @save_join_cache_level; set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # MDEV-27624: Nested left joins with not_exists optimization +--echo # for most inner left join +--echo # + +set @save_join_cache_level= @@join_cache_level; + +CREATE TABLE t1 (a INT NOT NULL, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1); + +CREATE TABLE t2(a INT NOT NULL); +INSERT INTO t2 VALUES (1), (2); + +CREATE TABLE t3(a INT not null, b INT); +INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1); + +let $q= +SELECT * +FROM t1 + LEFT JOIN + ( t2 LEFT JOIN t3 ON t2.a = t3.b ) + ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; + +set join_cache_level = 0; +eval EXPLAIN $q; +eval $q; + +set join_cache_level = 2; +eval EXPLAIN $q; +eval $q; + +DROP TABLE t1, t2, t3; + +set join_cache_level= @save_join_cache_level; + +--echo # end of 10.3 tests diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index 26fa772dfd1..7226a5d62b1 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -2008,6 +2008,58 @@ Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.` DROP TABLE t1,t2,t3; set join_cache_level= @save_join_cache_level; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-27624: Nested left joins with not_exists optimization +# for most inner left join +# +set @save_join_cache_level= @@join_cache_level; +CREATE TABLE t1 (a INT NOT NULL, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1); +CREATE TABLE t2(a INT NOT NULL); +INSERT INTO t2 VALUES (1), (2); +CREATE TABLE t3(a INT not null, b INT); +INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1); +set join_cache_level = 0; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +set join_cache_level = 2; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists; Using join buffer (incremental, BNL join) +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +DROP TABLE t1, t2, t3; +set join_cache_level= @save_join_cache_level; +# end of 10.3 tests CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 6f5fc9d09ac..673b18cb3b0 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5722,6 +5722,24 @@ EXECUTE stmt USING 'd'; EXECUTE stmt USING 'd'; 300 DROP TABLE t1, t2, t3; +set @@max_session_mem_used=default; +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = ?"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = 'a'"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; +# +# End of 10.3 tests +# # # MDEV-19263: Server crashes in mysql_handle_single_derived # upon 2nd execution of PS diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 97624925519..e260803a8d3 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -5109,6 +5109,45 @@ EXECUTE stmt USING 'd'; EXECUTE stmt USING 'd'; DROP TABLE t1, t2, t3; +set @@max_session_mem_used=default; +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = ?"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +let $run= 1000; +disable_result_log; +disable_query_log; +while ($run) { + execute stmt using repeat('x',10000); + dec $run; +} +enable_result_log; +enable_query_log; +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; + +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = 'a'"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +let $run= 1000; +disable_result_log; +disable_query_log; +while ($run) { + execute stmt; + dec $run; +} +enable_result_log; +enable_query_log; +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; + +--echo # +--echo # End of 10.3 tests +--echo # + --echo # --echo # MDEV-19263: Server crashes in mysql_handle_single_derived --echo # upon 2nd execution of PS |