From a5a9fcdfe44ffb093887a255254d128fe3752fd1 Mon Sep 17 00:00:00 2001 From: Monty Date: Fri, 5 Aug 2022 17:57:27 +0300 Subject: MDEV-12325 Unexpected data type and truncation when using CTE When creating a recursive CTE, the column types are taken from the non recursive part of the CTE (this is according to the SQL standard). This patch adds code to abort the CTE if the calculated values in the recursive part does not fit in the fields in the created temporary table. The new code only affects recursive CTE, so it should not cause any notable problems for old applications. Other things: - Fixed that we get correct row numbers for warnings generated with WITH RECURSIVE Reviewer: Alexander Barkov --- mysql-test/main/cte_recursive.result | 145 +++++++++++++++++++++++++++++++++++ mysql-test/main/cte_recursive.test | 43 +++++++++++ 2 files changed, 188 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 0c98530c9bf..7bf85016f11 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -4873,4 +4873,149 @@ a 0 NULL DROP TABLE t1; +# +# MDEV-12325 Unexpected data type and truncation when using CTE +# +CREATE TABLE t1 +( +id INT, mid INT, name TEXT +); +INSERT INTO t1 VALUES (0,NULL,'Name'),(1,0,'Name1'),(2,0,'Name2'),(11,1,'Name11'),(12,1,'Name12'); +WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid; +ERROR 22003: Out of range value for column 'mid' at row 2 +create table t2 as WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +ERROR 22003: Out of range value for column 'mid' at row 2 +create table t2 ignore as WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `level` int(1) DEFAULT NULL, + `id` int(11) DEFAULT NULL, + `mid` int(11) DEFAULT NULL, + `name` text DEFAULT NULL, + `mname` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t2 WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +ERROR 22003: Out of range value for column 'mid' at row 2 +insert ignore into t2 WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +drop table t2; +set @@sql_mode=""; +WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid; +level id mid name mname +1 0 NULL Name NULL +2 1 2147483647 Name1 NULL +2 2 2147483647 Name2 NULL +3 11 2147483647 Name11 NULL +3 12 2147483647 Name12 NULL +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +create table t2 as WITH RECURSIVE +cteReports (level, id, mid, name) AS +( +SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL +UNION ALL +SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e +INNER JOIN cteReports r ON e.mid = r.id +) +SELECT +level, id, mid, name, +(SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid;; +Warnings: +Warning 1264 Out of range value for column 'mid' at row 2 +Warning 1264 Out of range value for column 'mid' at row 3 +Warning 1264 Out of range value for column 'mid' at row 4 +Warning 1264 Out of range value for column 'mid' at row 5 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `level` int(1) DEFAULT NULL, + `id` int(11) DEFAULT NULL, + `mid` int(11) DEFAULT NULL, + `name` text DEFAULT NULL, + `mname` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +set @@sql_mode=default; +drop table t1,t2; +# # End of 10.3 tests +# diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 4c4bd99c24c..ca97c2d2900 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -3165,4 +3165,47 @@ SELECT * FROM cte; DROP TABLE t1; +--echo # +--echo # MDEV-12325 Unexpected data type and truncation when using CTE +--echo # + +CREATE TABLE t1 +( + id INT, mid INT, name TEXT +); +INSERT INTO t1 VALUES (0,NULL,'Name'),(1,0,'Name1'),(2,0,'Name2'),(11,1,'Name11'),(12,1,'Name12'); + +let $query= +WITH RECURSIVE +cteReports (level, id, mid, name) AS +( + SELECT 1, id, mid, name FROM t1 WHERE mid IS NULL + UNION ALL + SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e + INNER JOIN cteReports r ON e.mid = r.id +) +SELECT + level, id, mid, name, + (SELECT name FROM t1 WHERE id= cteReports.mid) AS mname +FROM cteReports ORDER BY level, mid; + +--error ER_WARN_DATA_OUT_OF_RANGE +--eval $query +--error ER_WARN_DATA_OUT_OF_RANGE +--eval create table t2 as $query; +--eval create table t2 ignore as $query; +show create table t2; +--error ER_WARN_DATA_OUT_OF_RANGE +--eval insert into t2 $query; +--eval insert ignore into t2 $query; +drop table t2; +set @@sql_mode=""; +--eval $query +--eval create table t2 as $query; +show create table t2; +set @@sql_mode=default; +drop table t1,t2; + +--echo # --echo # End of 10.3 tests +--echo # -- cgit v1.2.1 From 4a53253cf9aff82ad9066b13702181a34edc2749 Mon Sep 17 00:00:00 2001 From: Monty Date: Sat, 6 Aug 2022 15:06:22 +0300 Subject: Fixed that sp-no-valgrind.test is disabled on valgrind builds (not runs) --- mysql-test/include/not_valgrind_build.inc | 4 ++++ mysql-test/main/sp-no-valgrind.test | 2 +- 2 files changed, 5 insertions(+), 1 deletion(-) create mode 100644 mysql-test/include/not_valgrind_build.inc (limited to 'mysql-test') diff --git a/mysql-test/include/not_valgrind_build.inc b/mysql-test/include/not_valgrind_build.inc new file mode 100644 index 00000000000..2b60f11bfc7 --- /dev/null +++ b/mysql-test/include/not_valgrind_build.inc @@ -0,0 +1,4 @@ +if (`select version() like '%valgrind%'`) +{ + skip Does not run with binaries built with valgrind; +} diff --git a/mysql-test/main/sp-no-valgrind.test b/mysql-test/main/sp-no-valgrind.test index 89f8250bf72..21e52f2d3d5 100644 --- a/mysql-test/main/sp-no-valgrind.test +++ b/mysql-test/main/sp-no-valgrind.test @@ -1,5 +1,5 @@ ---source include/not_valgrind.inc +--source include/not_valgrind_build.inc --echo # MDEV-20699 do not cache SP in SHOW CREATE --echo # Warmup round, this might allocate some memory for session variable -- cgit v1.2.1 From 15426e5b3d789c55ddcaad44f38a7295ea3fa28a Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Tue, 9 Aug 2022 13:26:57 +0200 Subject: Version maturity fix. --- mysql-test/suite/sys_vars/r/sysvars_star.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/sys_vars/r/sysvars_star.result b/mysql-test/suite/sys_vars/r/sysvars_star.result index 54af0aa3014..eede5c17b36 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_star.result +++ b/mysql-test/suite/sys_vars/r/sysvars_star.result @@ -60,7 +60,7 @@ VARIABLE_NAME PLUGIN_MATURITY SESSION_VALUE NULL GLOBAL_VALUE alpha GLOBAL_VALUE_ORIGIN CONFIG -DEFAULT_VALUE beta +DEFAULT_VALUE gamma VARIABLE_SCOPE GLOBAL VARIABLE_TYPE ENUM VARIABLE_COMMENT The lowest desirable plugin maturity. Plugins less mature than that will not be installed or loaded -- cgit v1.2.1 From f2830af16c3a8c551e6b2821e5c7a33e652127b4 Mon Sep 17 00:00:00 2001 From: fluesvamp Date: Sun, 7 Aug 2022 17:07:39 -0500 Subject: Fix typos in the codebase. --- mysql-test/main/mysqltest.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/main/mysqltest.result b/mysql-test/main/mysqltest.result index fe269152357..d4309ffe97e 100644 --- a/mysql-test/main/mysqltest.result +++ b/mysql-test/main/mysqltest.result @@ -500,7 +500,7 @@ anything goes 0 != string mysqltest: At line 2: Only == and != are supported for string values mysqltest: At line 2: Found junk '~= 6' after $variable in condition -mysqltest: At line 2: Expression in if/while must beging with $, ` or a number +mysqltest: At line 2: Expression in if/while must begin with $, ` or a number mysqltest: At line 1: Missing right operand in comparison mysqltest: At line 1: Missing right operand in comparison counter is 2 -- cgit v1.2.1 From 47d0df6ef02fa1acd5581d1322475a08be3066f5 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 5 Aug 2022 21:47:02 +0200 Subject: take into account C/C specific CR_ERR_NET_WRITE error --- mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test b/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test index 4563e9b4469..6f67d7292dc 100644 --- a/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test +++ b/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test @@ -82,7 +82,7 @@ let $counter= 80; let $mysql_errno= 0; while (!$mysql_errno) { - --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013 + --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013,5014 show status; dec $counter; -- cgit v1.2.1 From 82c07fcabf039b2dc8f4b148e46bdb9662f3c0b1 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 9 Aug 2022 14:24:53 +0200 Subject: MDEV-23149 Server crashes in my_convert / ErrConvString::ptr / Item_char_typecast::check_truncation_with_warn --- mysql-test/main/func_compress.result | 11 ++++++++--- mysql-test/main/func_compress.test | 9 +++++---- mysql-test/main/func_json.result | 4 ++-- mysql-test/main/func_json.test | 2 +- 4 files changed, 16 insertions(+), 10 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/func_compress.result b/mysql-test/main/func_compress.result index 065b68b4979..dde7080fb2a 100644 --- a/mysql-test/main/func_compress.result +++ b/mysql-test/main/func_compress.result @@ -193,9 +193,6 @@ DROP TABLE t1; # End of 10.1 tests # # -# Start of 10.2 tests -# -# # MDEV-10134 Add full support for DEFAULT # CREATE TABLE t1 (a TEXT, b BLOB DEFAULT COMPRESS(a), bl INT DEFAULT UNCOMPRESSED_LENGTH(b), a1 TEXT DEFAULT UNCOMPRESS(b)); @@ -213,5 +210,13 @@ bl a1 100 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DROP TABLE t1; # +# MDEV-23149 Server crashes in my_convert / ErrConvString::ptr / Item_char_typecast::check_truncation_with_warn +# +select 'foo' in (cast(compress('bar') as char(4)), 'qux'); +'foo' in (cast(compress('bar') as char(4)), 'qux') +0 +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: '\x03\x00\x00\x00x\x9CKJ,\x02\x00\x02]\x016' +# # End of 10.2 tests # diff --git a/mysql-test/main/func_compress.test b/mysql-test/main/func_compress.test index 983b792f4c4..2a6c0276705 100644 --- a/mysql-test/main/func_compress.test +++ b/mysql-test/main/func_compress.test @@ -173,10 +173,6 @@ DROP TABLE t1; --echo # End of 10.1 tests --echo # ---echo # ---echo # Start of 10.2 tests ---echo # - --echo # --echo # MDEV-10134 Add full support for DEFAULT --echo # @@ -186,6 +182,11 @@ INSERT INTO t1 (a) VALUES (REPEAT('a',100)); SELECT bl, a1 FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-23149 Server crashes in my_convert / ErrConvString::ptr / Item_char_typecast::check_truncation_with_warn +--echo # +select 'foo' in (cast(compress('bar') as char(4)), 'qux'); + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 8e3b47e322a..48ce7ed1b3f 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -765,8 +765,8 @@ DROP TABLE t1; # # MDEV-16054 simple json functions flatline cpu on garbage input. # -select json_array(1,uuid(),compress(5.140264e+307)); -json_array(1,uuid(),compress(5.140264e+307)) +select json_array(1,user(),compress(5.140264e+307)); +json_array(1,user(),compress(5.140264e+307)) NULL # # MDEV-16869 String functions don't respect character set of JSON_VALUE. diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 16f323a9a56..9a063adc2cb 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -429,7 +429,7 @@ DROP TABLE t1; --echo # MDEV-16054 simple json functions flatline cpu on garbage input. --echo # -select json_array(1,uuid(),compress(5.140264e+307)); +select json_array(1,user(),compress(5.140264e+307)); --echo # --echo # MDEV-16869 String functions don't respect character set of JSON_VALUE. -- cgit v1.2.1 From 9ecdf860ce12255bd79869a4f3ac9f6784fe74cf Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 10 Aug 2022 08:59:28 +0200 Subject: missing ' --- mysql-test/mysql-test-run.pl | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 6882ad3e7e4..60955ac61a7 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -4709,7 +4709,7 @@ sub check_warnings ($) { $tinfo->{comment}.= "Could not execute 'check-warnings' for ". "testcase '$tname' (res: $res) server: '". - $mysqld->name() .":\n"; + $mysqld->name() ."':\n"; $tinfo->{comment}.= $report; $result= 2; -- cgit v1.2.1 From 122742897b47a19c85b1a5e9932ab3a8c2a4134e Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 23 Jun 2022 12:59:31 +0200 Subject: my_safe_process: try to kill the process softly first first SIGTERM and if the process didn't die in 10 seconds, SIGKILL it. This allows various tools like `rr`, `gcov`, `gprof`, etc to flush their data to disk properly --- mysql-test/lib/My/SafeProcess/safe_process.cc | 11 +++++++++-- 1 file changed, 9 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/lib/My/SafeProcess/safe_process.cc b/mysql-test/lib/My/SafeProcess/safe_process.cc index 4d0d1e2a3a0..dcf9491d2d6 100644 --- a/mysql-test/lib/My/SafeProcess/safe_process.cc +++ b/mysql-test/lib/My/SafeProcess/safe_process.cc @@ -140,13 +140,20 @@ void handle_core(pid_t pid __attribute__((unused))) {} static int kill_child(bool was_killed) { int status= 0; + pid_t ret_pid= 0; message("Killing child: %d", child_pid); // Terminate whole process group if (! was_killed) - kill(-child_pid, SIGKILL); + { + kill(-child_pid, SIGTERM); + sleep(10); // will be interrupted by SIGCHLD + if (!(ret_pid= waitpid(child_pid, &status, WNOHANG))) + kill(-child_pid, SIGKILL); + } - pid_t ret_pid= waitpid(child_pid, &status, 0); + if (!ret_pid) + ret_pid= waitpid(child_pid, &status, 0); if (ret_pid == child_pid) { int exit_code= 1; -- cgit v1.2.1