From 0eadadad25d9e44232e1567897cf9dcb957ccdcd Mon Sep 17 00:00:00 2001 From: Debarun Banerjee Date: Wed, 24 Jun 2015 10:27:12 +0530 Subject: BUG#20310212 PARTITION DDL- CRASH AFTER THD::NOCHECK_REGISTER_ITEM_ Problem : --------- Issue-1: The root cause for the issues is that (col1 > 1) is not a valid partition function and we should have thrown error at much early stage [partition_info::check_partition_info]. We are not checking sub-partition expression when partition expression is NULL. Issue-2: Potential issue for future if any partition function needs to change item tree during open/fix_fields. We should release changed items, if any, before doing closefrm when we open the partitioned table during creation in create_table_impl. Solution : ---------- 1.check_partition_info() - Check for sub-partition expression even if no partition expression. [partition by ... columns(...) subpartition by hash()] 2.create_table_impl() - Assert that the change list is empty before doing closefrm for partitioned table. Currently no supported partition function seems to be changing item tree during open. Reviewed-by: Mattias Jonsson RB: 9345 --- mysql-test/r/partition_error.result | 2 +- mysql-test/t/partition_error.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index 0e4f89b70db..1e0f2cbea10 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -1089,7 +1089,7 @@ partition by key (a) subpartition by hash (sin(a+b)) (partition x1 (subpartition x11, subpartition x12), partition x2 (subpartition x21, subpartition x22)); -ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning +ERROR HY000: This partition function is not allowed select load_file('$MYSQLD_DATADIR/test/t1.par'); load_file('$MYSQLD_DATADIR/test/t1.par') NULL diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test index c0f49a4d414..9a6939032a3 100644 --- a/mysql-test/t/partition_error.test +++ b/mysql-test/t/partition_error.test @@ -1145,7 +1145,7 @@ subpartition by hash (rand(a+b)); # # Subpartition by hash, wrong subpartition function # ---error ER_SUBPARTITION_ERROR +--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a int not null, b int not null, -- cgit v1.2.1 From 7c5d18e2271ce4fcd9294511860841dbb4fec31a Mon Sep 17 00:00:00 2001 From: Arun Kuruvila Date: Tue, 30 Jun 2015 10:27:12 +0530 Subject: Bug #20772273 : MYSQLIMPORT --USE-THREADS DOESN'T USE MULTIPLE THREADS Description:- The utility "mysqlimport" does not use multiple threads for the execution with option "--use-threads". "mysqlimport" while importing multiple files and multiple tables, uses a single thread even if the number of threads are specified with "--use-threads" option. Analysis:- This utility uses ifdef HAVE_LIBPTHREAD to check for libpthread library and if defined uses libpthread library for mutlithreaing. Since HAVE_LIBPTHREAD is not defined anywhere in the source, "--use-threads" option is silently ignored. Fix:- "-DTHREADS" is set to the COMPILE_FLAGS which will enable pthreads. HAVE_LIBPTHREAD macro is removed. --- mysql-test/r/mysqldump.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/mysqldump.test | 29 +++++++++++++++++++++++++++++ 2 files changed, 57 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index a31516d10d2..c578f9e8df6 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5255,3 +5255,31 @@ SET @@global.general_log= @old_general_log_state; # # End of 5.1 tests # +# +# Bug #20772273 : MYSQLIMPORT --USE-THREADS DOESN'T USE MULTIPLE THREADS +# +CREATE DATABASE db_20772273; +USE db_20772273; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (3), (4); +SELECT * FROM t1; +a +1 +2 +SELECT * FROM t2; +a +3 +4 +SELECT * FROM t1; +a +1 +2 +SELECT * FROM t2; +a +3 +4 +DROP TABLE t1; +DROP TABLE t2; +DROP DATABASE db_20772273; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 478d8fb2863..54780b95627 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2401,3 +2401,32 @@ SET @@global.general_log= @old_general_log_state; --echo # --echo # End of 5.1 tests --echo # + +--echo # +--echo # Bug #20772273 : MYSQLIMPORT --USE-THREADS DOESN'T USE MULTIPLE THREADS +--echo # + +CREATE DATABASE db_20772273; +USE db_20772273; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (3), (4); + +SELECT * FROM t1; +SELECT * FROM t2; + +--exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ db_20772273 +--exec $MYSQL db_20772273 < $MYSQLTEST_VARDIR/tmp/t1.sql +--exec $MYSQL db_20772273 < $MYSQLTEST_VARDIR/tmp/t2.sql + +# Test mysqlimport with multiple threads +--exec $MYSQL_IMPORT --silent --use-threads=2 db_20772273 $MYSQLTEST_VARDIR/tmp/t1.txt $MYSQLTEST_VARDIR/tmp/t2.txt + +SELECT * FROM t1; +SELECT * FROM t2; + +#Cleanup +DROP TABLE t1; +DROP TABLE t2; +DROP DATABASE db_20772273; -- cgit v1.2.1 From 2ac01ca6606a300dc7c043affccb9f850284a5e7 Mon Sep 17 00:00:00 2001 From: Praveenkumar Hulakund Date: Fri, 3 Jul 2015 16:56:13 +0530 Subject: Bug#18487951 - QUERY_CACHE_MIN_RES_UNIT SET TO ZERO, CRASHES IN QUERY_CACHE::FIND_BIN Follow up patch to fix sys_vars.query_cache_min_res_unit_basic_32 test failure. --- .../suite/sys_vars/r/query_cache_min_res_unit_basic_32.result | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result b/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result index c408a39fdc0..9dc4a0d5062 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result +++ b/mysql-test/suite/sys_vars/r/query_cache_min_res_unit_basic_32.result @@ -50,7 +50,7 @@ Warnings: Warning 1292 Truncated incorrect query_cache_min_res_unit value: '4294967296' SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit -0 +4294967288 SET @@global.query_cache_min_res_unit = 511; SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit @@ -71,23 +71,23 @@ Warnings: Warning 1292 Truncated incorrect query_cache_min_res_unit value: '42949672950' SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit -0 +4294967288 SET @@global.query_cache_min_res_unit = ON; ERROR 42000: Incorrect argument type to variable 'query_cache_min_res_unit' SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit -0 +4294967288 SET @@global.query_cache_min_res_unit = 'test'; ERROR 42000: Incorrect argument type to variable 'query_cache_min_res_unit' SELECT @@global.query_cache_min_res_unit; @@global.query_cache_min_res_unit -0 +4294967288 '#-------------------FN_DYNVARS_132_05----------------------------#' SET @@session.query_cache_min_res_unit = 0; ERROR HY000: Variable 'query_cache_min_res_unit' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@query_cache_min_res_unit; @@query_cache_min_res_unit -0 +4294967288 '#----------------------FN_DYNVARS_132_06------------------------#' SELECT @@global.query_cache_min_res_unit = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -- cgit v1.2.1 From 33a2e5abd86727155b629246445d508bb2cd02c0 Mon Sep 17 00:00:00 2001 From: Sreeharsha Ramanavarapu Date: Fri, 10 Jul 2015 07:52:00 +0530 Subject: Bug #20238729: ILLEGALLY CRAFTED UTF8 SELECT PROVIDES NO WARNINGS Backporting to 5.1 and 5.5 --- .../iuds/r/strings_charsets_update_delete.result | Bin 112951 -> 113476 bytes .../r/character_set_connection_func.result | 6 ++++++ 2 files changed, 6 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result b/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result index 08eecb1c17d..b862e23e3d3 100644 Binary files a/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result and b/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result differ diff --git a/mysql-test/suite/sys_vars/r/character_set_connection_func.result b/mysql-test/suite/sys_vars/r/character_set_connection_func.result index 6fc33a4f369..9e1dbc62565 100644 --- a/mysql-test/suite/sys_vars/r/character_set_connection_func.result +++ b/mysql-test/suite/sys_vars/r/character_set_connection_func.result @@ -23,6 +23,8 @@ SET @@session.character_set_connection = latin1; SELECT 'ЁЂЃЄ' AS utf_text; utf_text ???? +Warnings: +Warning 1105 Can't convert the character string from utf8 to latin1: '\xD0\x81\xD0\x82\xD0\x83...' SET @@session.character_set_connection = utf8; SELECT 'ЁЂЃЄ' AS utf_text; utf_text @@ -30,6 +32,8 @@ utf_text '---now inserting utf8 string with different character_set_connection--' SET @@session.character_set_connection = ascii; INSERT INTO t1 VALUES('ЁЂЃЄ'); +Warnings: +Warning 1105 Can't convert the character string from utf8 to ascii: '\xD0\x81\xD0\x82\xD0\x83...' SELECT * FROM t1; b ???? @@ -39,6 +43,8 @@ SET @@session.character_set_connection = ascii; SET @@session.character_set_client = latin1; SET @@session.character_set_results = latin1; INSERT INTO t1 VALUES('ЁЂЃЄ'); +Warnings: +Warning 1105 Can't convert the character string from latin1 to ascii: '\xD0\x81\xD0\x82\xD0\x83...' SELECT * FROM t1; b ???????? -- cgit v1.2.1 From 49667f044197cefdb7c90b8beab3e78ec23deecd Mon Sep 17 00:00:00 2001 From: Christopher Powers Date: Fri, 10 Jul 2015 20:42:33 +0200 Subject: Bug#21374104 SETUP_TIMERS INITIALIZATION ASSUMES CYCLE TIMER IS ALWAYS AVAILABLE For WAIT events, fall back to other timers if CYCLE is not available. --- mysql-test/suite/perfschema/r/query_cache.result | 4 ++-- mysql-test/suite/perfschema/t/query_cache.test | 2 ++ 2 files changed, 4 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/perfschema/r/query_cache.result b/mysql-test/suite/perfschema/r/query_cache.result index 8786cd055ca..837c2573a72 100644 --- a/mysql-test/suite/perfschema/r/query_cache.result +++ b/mysql-test/suite/perfschema/r/query_cache.result @@ -38,7 +38,7 @@ spins NULL select * from performance_schema.setup_timers where name='wait'; NAME TIMER_NAME -wait CYCLE +wait {CYCLE_OR_NANOSECOND} show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 @@ -53,7 +53,7 @@ spins NULL select * from performance_schema.setup_timers where name='wait'; NAME TIMER_NAME -wait CYCLE +wait {CYCLE_OR_NANOSECOND} show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 diff --git a/mysql-test/suite/perfschema/t/query_cache.test b/mysql-test/suite/perfschema/t/query_cache.test index 60d4a648222..802e574f89b 100644 --- a/mysql-test/suite/perfschema/t/query_cache.test +++ b/mysql-test/suite/perfschema/t/query_cache.test @@ -34,6 +34,7 @@ show status like "Qcache_hits"; select spins from performance_schema.events_waits_current order by event_name limit 1; +--replace_result CYCLE {CYCLE_OR_NANOSECOND} NANOSECOND {CYCLE_OR_NANOSECOND} select * from performance_schema.setup_timers where name='wait'; show status like "Qcache_queries_in_cache"; @@ -42,6 +43,7 @@ show status like "Qcache_hits"; select spins from performance_schema.events_waits_current order by event_name limit 1; +--replace_result CYCLE {CYCLE_OR_NANOSECOND} NANOSECOND {CYCLE_OR_NANOSECOND} select * from performance_schema.setup_timers where name='wait'; show status like "Qcache_queries_in_cache"; -- cgit v1.2.1 From 888fabd6909237f55ed9b9cf7a0c852c2e5f0beb Mon Sep 17 00:00:00 2001 From: Sreeharsha Ramanavarapu Date: Thu, 16 Jul 2015 07:56:39 +0530 Subject: Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT RESULTS Issue: ----- Updating varchar and text fields in the same update statement can produce incorrect results. When a varchar field is assigned to the text field and the varchar field is then set to a different value, the text field's result contains the varchar field's new value. SOLUTION: --------- Currently the blob type does not allocate space for the string to be stored. Instead it contains a pointer to the varchar string. So when the varchar field is changed as part of the update statement, the value contained in the blob also changes. The fix would be to actually store the value by allocating space for the blob's string. We can avoid allocating this space when the varchar field is not being written into. --- mysql-test/r/update.result | 13 +++++++++++++ mysql-test/t/update.test | 13 +++++++++++++ 2 files changed, 26 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 54170ae3dad..d15130d1254 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -551,3 +551,16 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function DROP VIEW v1; DROP FUNCTION f1; DROP TABLE t1; +# Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT +# RESULTS +CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=INNODB; +INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string'); +UPDATE t1 SET b = a, a = 'inject'; +SELECT a, b FROM t1; +a b +inject start trail +UPDATE t1 SET b = c, c = 'inject'; +SELECT c, b FROM t1; +c b +inject even longer string +DROP TABLE t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index c515f8873d8..14e08bd9b8b 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -503,3 +503,16 @@ UPDATE v1 SET pk = 7 WHERE pk > 0; DROP VIEW v1; DROP FUNCTION f1; DROP TABLE t1; + +--echo # Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT +--echo # RESULTS + +CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=INNODB; + +INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string'); +UPDATE t1 SET b = a, a = 'inject'; +SELECT a, b FROM t1; +UPDATE t1 SET b = c, c = 'inject'; +SELECT c, b FROM t1; + +DROP TABLE t1; -- cgit v1.2.1 From b5380e092c1cac3050a711c308136eee15c51826 Mon Sep 17 00:00:00 2001 From: Nisha Gopalakrishnan Date: Thu, 23 Jul 2015 10:47:58 +0530 Subject: BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, IS REJECTED. Analysis ======== View creation with named columns over UNION is rejected. Consider the following view definition: CREATE VIEW v1 (fld1, fld2) AS SELECT 1 AS a, 2 AS b UNION ALL SELECT 1 AS a, 1 AS a; A 'duplicate column' error was reported due to the duplicate alias name in the secondary SELECT. The VIEW column names are either explicitly specified or determined from the first SELECT (which can be auto generated if not specified). Since a duplicate column name check was performed even for the secondary SELECTs, an error was reported. Fix ==== Check for duplicate column names only for the named columns if specified or only for the first SELECT. --- mysql-test/r/view.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/t/view.test | 44 ++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 79 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c31b4f5216b..a3adf80c096 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4146,3 +4146,38 @@ SHOW CREATE VIEW v4; View Create View character_set_client collation_connection v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `col2`) union all (select '' AS `Name_exp_3`) latin1 latin1_swedish_ci DROP VIEW v1, v2, v3, v4; +# +# BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, +# IS REJECTED +# Without the patch, reports an error. +CREATE VIEW v1 (fld1, fld2) AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +# The column names are explicitly specified and not duplicates, hence +# succeeds. +CREATE VIEW v2 (fld1, fld2) AS +SELECT 1 AS a, 2 AS a +UNION ALL +SELECT 1 AS a, 1 AS a; +# The column name in the first SELECT are not duplicates, hence succeeds. +CREATE VIEW v3 AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +# Should report an error, since the explicitly specified column names are +# duplicates. +CREATE VIEW v4 (fld1, fld1) AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +ERROR 42S21: Duplicate column name 'fld1' +# Should report an error, since duplicate column name is specified in the +# First SELECT. +CREATE VIEW v4 AS +SELECT 1 AS a, 2 AS a +UNION ALL +SELECT 1 AS a, 1 AS a; +ERROR 42S21: Duplicate column name 'a' +# Cleanup +DROP VIEW v1, v2, v3; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 959153ee851..f9c305831c2 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4184,6 +4184,50 @@ SHOW CREATE VIEW v4; DROP VIEW v1, v2, v3, v4; + +--echo # +--echo # BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, +--echo # IS REJECTED + +--echo # Without the patch, reports an error. +CREATE VIEW v1 (fld1, fld2) AS + SELECT 1 AS a, 2 AS b + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # The column names are explicitly specified and not duplicates, hence +--echo # succeeds. +CREATE VIEW v2 (fld1, fld2) AS + SELECT 1 AS a, 2 AS a + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # The column name in the first SELECT are not duplicates, hence succeeds. +CREATE VIEW v3 AS + SELECT 1 AS a, 2 AS b + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # Should report an error, since the explicitly specified column names are +--echo # duplicates. +--error ER_DUP_FIELDNAME +CREATE VIEW v4 (fld1, fld1) AS + SELECT 1 AS a, 2 AS b + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # Should report an error, since duplicate column name is specified in the +--echo # First SELECT. +--error ER_DUP_FIELDNAME +CREATE VIEW v4 AS + SELECT 1 AS a, 2 AS a + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # Cleanup +DROP VIEW v1, v2, v3; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc -- cgit v1.2.1 From 67be190c0b7a803684eaffca53063f31fa8e1ddc Mon Sep 17 00:00:00 2001 From: sayantan dutta Date: Tue, 18 Feb 2014 17:57:54 +0530 Subject: Follow up Fix: Bug #18145121 - DEPRECATED PERL SYNTAX IN MTR (cherry picked from commit 3eb933e0eb55f962404a04741767177e12a9885f) Conflicts: mysql-test/mysql-test-run.pl --- 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 02ff577c7bf..684d262f410 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -490,7 +490,7 @@ sub main { } } - if ( not defined @$completed ) { + if ( not $completed ) { mtr_error("Test suite aborted"); } -- cgit v1.2.1 From 1d317440d6be293f1a6f38bf72951eccac38be55 Mon Sep 17 00:00:00 2001 From: sayantan dutta Date: Mon, 24 Feb 2014 18:44:37 +0530 Subject: Follow-up fix : Bug #18145121 - DEPRECATED PERL SYNTAX IN MTR (cherry picked from commit 1bfe5f724bc4c24da635f632247e7d263aa53970) Conflicts: mysql-test/lib/mtr_cases.pm --- mysql-test/lib/mtr_cases.pm | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/lib/mtr_cases.pm b/mysql-test/lib/mtr_cases.pm index b3bc2a83b92..e8d24eb399f 100644 --- a/mysql-test/lib/mtr_cases.pm +++ b/mysql-test/lib/mtr_cases.pm @@ -1,5 +1,5 @@ # -*- cperl -*- -# Copyright (c) 2005, 2011, Oracle and/or its affiliates. All rights reserved. +# Copyright (c) 2005, 2015, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -336,7 +336,7 @@ sub collect_one_suite($) # Build a hash of disabled testcases for this suite # ---------------------------------------------------------------------- my %disabled; - my @disabled_collection= @{$opt_skip_test_list} if defined @{$opt_skip_test_list}; + my @disabled_collection= @{$opt_skip_test_list} if $opt_skip_test_list; unshift (@disabled_collection, "$testdir/disabled.def"); for my $skip (@disabled_collection) { -- cgit v1.2.1 From 557a57f3a23c486fbe12b66306ab7adffd609677 Mon Sep 17 00:00:00 2001 From: Mithun C Y Date: Mon, 17 Aug 2015 15:23:47 +0530 Subject: Bug #21350175: SUBQUERIES IN PROCEDURE CLAUSE OF SELECT STATEMENT CAUSES SERVER FAILURES. Analysis : ========== During JOIN::prepare of sub-query which creates the derived tables we call setup_procedure. Here we call fix_fields for parameters of procedure clause. Calling setup_procedure at this point may cause issue. If sub-query is one of parameter being fixed it might lead to complicated dependencies on derived tables being prepared. SOLUTION : ========== In 5.6 with WL#6242, we have made procedure clause parameters can only be NUM, so sub-queries are not allowed as parameters. So in 5.5 we can block sub-queries in procedure clause parameters. This eliminates above conflicting dependencies. --- mysql-test/r/subselect.result | 2 +- mysql-test/t/subselect.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 49cf73677b1..aec8e07e071 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -75,7 +75,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect parameters to procedure 'ANALYSE' +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1))' at line 1 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index aec0db59843..a5a4b2b5c32 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -30,7 +30,7 @@ SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); -- error ER_WRONG_USAGE select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); --- error ER_WRONG_PARAMETERS_TO_PROCEDURE +-- error ER_PARSE_ERROR SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; -- cgit v1.2.1 From 93ac0eb1c46a708529b290fd072c9d1e3e3526e8 Mon Sep 17 00:00:00 2001 From: Karthik Kamath Date: Tue, 18 Aug 2015 10:38:06 +0530 Subject: BUG#11754258: INCORRECT ERROR MESSAGE WHEN CREATING UNSAFE VIEW It appears that the code refactoring done as part of the patch for the MySQL BUG#11749859 fixed this issue. This issue is not reproducible on MySQL 5.5+ versions now. As part of this patch, the test file "mysqldump.test" has been updated to remove the comment which was referring to the bug and also the line which suppresses the warning. --- mysql-test/t/mysqldump.test | 5 ----- 1 file changed, 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 54780b95627..11d766c3293 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -875,13 +875,8 @@ select * from t1; create view v1 as select * from v3 where b in (1, 2, 3, 4, 5, 6, 7); -# Disable warnings since LIMIT warning for unsafe statement if -# binlog_format = STATEMENT. Note: after BUG#45832, the warning should -# not be issued. ---disable_warnings create view v2 as select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1; ---enable_warnings --exec $MYSQL_DUMP --skip-comments test -- cgit v1.2.1 From ee02650bac49b66a38c7c485ea2f81edd4403ffc Mon Sep 17 00:00:00 2001 From: Shishir Jaiswal Date: Tue, 18 Aug 2015 12:24:27 +0530 Subject: Bug #16171518 - LOAD XML DOES NOT HANDLE EMPTY ELEMENTS DESCRIPTION =========== Inability of mysql LOAD XML command to handle empty XML tags i.e. . Also the behaviour is wrong and (different than above) when there is a space in empty tag i.e. ANALYSIS ======== In read_xml() the case where we encounter a close tag ('/') we're decreasing the 'level' blindly which is wrong. Actually when its an without-space-empty-tag (succeeding char is '>'), we need to skip the decrement. In other words whenever we hit a close tag ('/'), decrease the 'level' only when (i) It's not an (without space) empty tag i.e. or, (ii) It is of format FIX === The switch case for '/' is modified. We've removed the blind decrement of 'level'. We do it only when its not an without-space-empty-tag. Also we are setting 'in_tag' to false to let program know that we're done reading current tag (required in the case of format ) --- mysql-test/r/loadxml.result | 27 ++++++++++++++++ mysql-test/std_data/bug16171518_1.dat | 59 +++++++++++++++++++++++++++++++++++ mysql-test/std_data/bug16171518_2.dat | 12 +++++++ mysql-test/t/loadxml.test | 14 +++++++++ 4 files changed, 112 insertions(+) create mode 100644 mysql-test/std_data/bug16171518_1.dat create mode 100644 mysql-test/std_data/bug16171518_2.dat (limited to 'mysql-test') diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result index 7742f456252..1128caf9122 100644 --- a/mysql-test/r/loadxml.result +++ b/mysql-test/r/loadxml.result @@ -93,3 +93,30 @@ a b 216 !&bb b; 3 !b3 DROP TABLE t1; +# +# Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS +# +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4)); +LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3, col4; +col1 col2 col3 col4 +0bc def ghi jkl +1no NULL pqr stu +2BC DEF GHI JKL +3NO NULL PQR STU +4bc def ghi jkl +5no pqr stu vwx +6BC DEF NULL JKL +7NO PQR STU VWX +8bc def ghi NULL +9kl NULL mno pqr +ABC DEF NULL JKL +MNO NULL STU VWX +DROP TABLE t1; +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER); +LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3; +col1 col2 col3 +ABC DEF NULL +GHI NULL 123 +DROP TABLE t1; diff --git a/mysql-test/std_data/bug16171518_1.dat b/mysql-test/std_data/bug16171518_1.dat new file mode 100644 index 00000000000..b65b9359ce1 --- /dev/null +++ b/mysql-test/std_data/bug16171518_1.dat @@ -0,0 +1,59 @@ + + + 0bc + def + ghi + jkl + + + 1no + + pqr + stu + + + + 2BC + DEF + GHI + JKL + + + 3NO + + PQR + STU + + + + + + + 6BC + DEF + + JKL + + + 7NO + PQR + STU + VWX + + + + 8bc + def + ghi + + + + 9kl + + mno + pqr + + + + + diff --git a/mysql-test/std_data/bug16171518_2.dat b/mysql-test/std_data/bug16171518_2.dat new file mode 100644 index 00000000000..8a483337a0f --- /dev/null +++ b/mysql-test/std_data/bug16171518_2.dat @@ -0,0 +1,12 @@ + + + ABC + DEF + + + + GHI + + 123 + + diff --git a/mysql-test/t/loadxml.test b/mysql-test/t/loadxml.test index 6faf712b6ce..93e6e82189f 100644 --- a/mysql-test/t/loadxml.test +++ b/mysql-test/t/loadxml.test @@ -116,3 +116,17 @@ LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1 ROWS IDENTIFIED BY '' (a,@b) SET b=concat('!',@b); SELECT * FROM t1 ORDER BY a; DROP TABLE t1; + + +--echo # +--echo # Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS +--echo # +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4)); +LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3, col4; +DROP TABLE t1; + +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER); +LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3; +DROP TABLE t1; -- cgit v1.2.1 From f4ff086abea975222572fcfd232bf296018f5d85 Mon Sep 17 00:00:00 2001 From: Arun Kuruvila Date: Fri, 21 Aug 2015 08:35:42 +0530 Subject: Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS Description:- Server variable "--lower_case_tables_names" when set to "0" on windows platform which does not support case sensitive file operations leads to problems. A warning message is printed in the error log while starting the server with "--lower_case_tables_names=0". Also according to the documentation, seting "lower_case_tables_names" to "0" on a case-insensitive filesystem might lead to index corruption. Analysis:- The problem reported in the bug is:- Creating an INNODB table 'a' and executing a query, "INSERT INTO a SELECT a FROM A;" on a server started with "--lower_case_tables_names=0" and running on a case-insensitive filesystem leads innodb to flat spin. Optimizer thinks that "a" and "A" are two different tables as the variable "lower_case_table_names" is set to "0". As a result, optimizer comes up with a plan which does not need a temporary table. If the same table is used in select and insert, a temporary table is needed. This incorrect optimizer plan leads to infinite insertions. Fix:- If the server is started with "--lower_case_tables_names" set to 0 on a case-insensitive filesystem, an error, "The server option 'lower_case_table_names'is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.", is printed in the server error log and the server exits. --- mysql-test/r/lowercase_fs_on.result | 3 ++ mysql-test/r/lowercase_table3.result | 11 ------- .../suite/innodb/t/innodb_bug60229-master.opt | 1 - mysql-test/suite/innodb/t/innodb_bug60229.test | 4 +++ mysql-test/suite/jp/t/jp_enum_sjis-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_sjis.test | 4 +++ mysql-test/suite/jp/t/jp_enum_ucs2-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_ucs2.test | 4 +++ mysql-test/suite/jp/t/jp_enum_ujis-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_ujis.test | 4 +++ mysql-test/suite/jp/t/jp_enum_utf8-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_utf8.test | 4 +++ mysql-test/t/lowercase_fs_on.test | 38 ++++++++++++++++++++++ mysql-test/t/lowercase_table3-master.opt | 1 - mysql-test/t/lowercase_table3.test | 37 --------------------- 15 files changed, 61 insertions(+), 54 deletions(-) create mode 100644 mysql-test/r/lowercase_fs_on.result delete mode 100644 mysql-test/r/lowercase_table3.result delete mode 100644 mysql-test/suite/innodb/t/innodb_bug60229-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_sjis-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_ucs2-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_ujis-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_utf8-master.opt create mode 100644 mysql-test/t/lowercase_fs_on.test delete mode 100644 mysql-test/t/lowercase_table3-master.opt delete mode 100644 mysql-test/t/lowercase_table3.test (limited to 'mysql-test') diff --git a/mysql-test/r/lowercase_fs_on.result b/mysql-test/r/lowercase_fs_on.result new file mode 100644 index 00000000000..a090f46cfbf --- /dev/null +++ b/mysql-test/r/lowercase_fs_on.result @@ -0,0 +1,3 @@ +# +# Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS +# diff --git a/mysql-test/r/lowercase_table3.result b/mysql-test/r/lowercase_table3.result deleted file mode 100644 index 22e80aaeb26..00000000000 --- a/mysql-test/r/lowercase_table3.result +++ /dev/null @@ -1,11 +0,0 @@ -call mtr.add_suppression("Cannot find or open table test/BUG29839 from"); -DROP TABLE IF EXISTS t1,T1; -CREATE TABLE t1 (a INT); -SELECT * FROM T1; -a -FLUSH TABLES; -DROP TABLE t1; -CREATE TABLE bug29839 (a INT) ENGINE=INNODB; -SELECT * FROM BUG29839; -ERROR 42S02: Table 'test.BUG29839' doesn't exist -DROP TABLE bug29839; diff --git a/mysql-test/suite/innodb/t/innodb_bug60229-master.opt b/mysql-test/suite/innodb/t/innodb_bug60229-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug60229-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/innodb/t/innodb_bug60229.test b/mysql-test/suite/innodb/t/innodb_bug60229.test index 8dcf15157d6..aee0b96a942 100644 --- a/mysql-test/suite/innodb/t/innodb_bug60229.test +++ b/mysql-test/suite/innodb/t/innodb_bug60229.test @@ -2,6 +2,10 @@ # Bug #13083023 - 60229: BROKEN COMPATIBILITY: ERROR WHILE CREATE TABLE # WITH FOREIGN KEY CONSTRAINT. +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc -- source include/have_innodb.inc CREATE TABLE PERSON ( diff --git a/mysql-test/suite/jp/t/jp_enum_sjis-master.opt b/mysql-test/suite/jp/t/jp_enum_sjis-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_sjis-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_sjis.test b/mysql-test/suite/jp/t/jp_enum_sjis.test index e1f22f6fe27..1060f83ec06 100644 --- a/mysql-test/suite/jp/t/jp_enum_sjis.test +++ b/mysql-test/suite/jp/t/jp_enum_sjis.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_sjis.inc --source include/have_innodb.inc --character_set sjis diff --git a/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt b/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_ucs2.test b/mysql-test/suite/jp/t/jp_enum_ucs2.test index a3d7c47705d..321f8952148 100644 --- a/mysql-test/suite/jp/t/jp_enum_ucs2.test +++ b/mysql-test/suite/jp/t/jp_enum_ucs2.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_ucs2.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/jp/t/jp_enum_ujis-master.opt b/mysql-test/suite/jp/t/jp_enum_ujis-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_ujis-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_ujis.test b/mysql-test/suite/jp/t/jp_enum_ujis.test index 17e41e0691e..10e8ad55a3c 100644 --- a/mysql-test/suite/jp/t/jp_enum_ujis.test +++ b/mysql-test/suite/jp/t/jp_enum_ujis.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_ujis.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/jp/t/jp_enum_utf8-master.opt b/mysql-test/suite/jp/t/jp_enum_utf8-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_utf8-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_utf8.test b/mysql-test/suite/jp/t/jp_enum_utf8.test index ff5978da69c..f67939bd02b 100644 --- a/mysql-test/suite/jp/t/jp_enum_utf8.test +++ b/mysql-test/suite/jp/t/jp_enum_utf8.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_utf8.inc --source include/have_innodb.inc --disable_warnings diff --git a/mysql-test/t/lowercase_fs_on.test b/mysql-test/t/lowercase_fs_on.test new file mode 100644 index 00000000000..6da3ef32a0b --- /dev/null +++ b/mysql-test/t/lowercase_fs_on.test @@ -0,0 +1,38 @@ +# +# Specific tests for case-insensitive file systems +# i.e. lower_case_filesystem=ON +# +-- source include/have_case_insensitive_file_system.inc +# Embedded server does not support restarting. +--source include/not_embedded.inc + +--echo # +--echo # Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS +--echo # + +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/my_restart.err; + +--error 0,1 +--remove_file $SEARCH_FILE + +#Shutdown the server +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc + +#Start the server with --lower_case_table_names=0 in Windows. +--enable_reconnect +--error 1 +--exec $MYSQLD_CMD --lower_case_table_names=0 > $SEARCH_FILE 2>&1 + +#Search for the error messege in the server error log. +let SEARCH_PATTERN= \[ERROR\] The server option \'lower_case_table_names\' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination\. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode\.; +--source include/search_pattern_in_file.inc + +#Restart the server +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/wait_until_connected_again.inc + +#Cleanup +--error 0,1 +--remove_file $SEARCH_FILE diff --git a/mysql-test/t/lowercase_table3-master.opt b/mysql-test/t/lowercase_table3-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/t/lowercase_table3-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/t/lowercase_table3.test b/mysql-test/t/lowercase_table3.test deleted file mode 100644 index f7ca8211288..00000000000 --- a/mysql-test/t/lowercase_table3.test +++ /dev/null @@ -1,37 +0,0 @@ -# -# Test of force of lower-case-table-names=0 -# (User has case insensitive file system and wants to preserve case of -# table names) -# - ---source include/have_innodb.inc ---source include/have_lowercase0.inc ---source include/have_case_insensitive_file_system.inc ---source include/not_windows.inc - -call mtr.add_suppression("Cannot find or open table test/BUG29839 from"); - ---disable_warnings -DROP TABLE IF EXISTS t1,T1; ---enable_warnings - -# -# This is actually an error, but ok as the user has forced this -# by using --lower-case-table-names=0 -CREATE TABLE t1 (a INT); -SELECT * FROM T1; -FLUSH TABLES; -DROP TABLE t1; - -# -# InnoDB should in this case be case sensitive -# Note that this is not true on windows as no this OS, InnoDB is always -# storing things in lower case. -# - -CREATE TABLE bug29839 (a INT) ENGINE=INNODB; ---error ER_NO_SUCH_TABLE -SELECT * FROM BUG29839; -DROP TABLE bug29839; - -# End of 4.1 tests -- cgit v1.2.1 From 102a85f9f30cdf8c3baa3893c68932617240bfa6 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Thu, 3 Sep 2015 18:00:43 +0200 Subject: MDEV-8663: IF Statement returns multiple values erroneously (or Assertion `!null_value' failed in Item::send(Protocol*, String*)) Postreview addons by Bar Fix: keeping contract: NULL value mean NULL pointer in val_str and val_deciman. --- mysql-test/r/func_if.result | 17 +++++++++++++++++ mysql-test/t/func_if.test | 14 ++++++++++++++ 2 files changed, 31 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index c7f548ae2bc..61f63cc7253 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -234,3 +234,20 @@ SELECT if(1, NULL, (SELECT min('hello'))); if(1, NULL, (SELECT min('hello'))) NULL End of 5.2 tests +# +# MDEV-8663: IF Statement returns multiple values erroneously +# (or Assertion `!null_value' failed in Item::send(Protocol*, String*) +# +CREATE TABLE `t1` ( +`datas` VARCHAR(25) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 't1' +INSERT INTO `t1` VALUES ('1,2'), ('2,3'), ('3,4'); +SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `t1`; +First Second Third +1.5 1 2 +2.0 1 2 +NULL 1 2 +drop table t1; diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index 2b89a618aa6..8fdba77db9b 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -206,6 +206,20 @@ SELECT if(1, NULL, (SELECT min('hello'))); --echo End of 5.2 tests +--echo # +--echo # MDEV-8663: IF Statement returns multiple values erroneously +--echo # (or Assertion `!null_value' failed in Item::send(Protocol*, String*) +--echo # +CREATE TABLE `t1` ( +`datas` VARCHAR(25) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO `t1` VALUES ('1,2'), ('2,3'), ('3,4'); + +SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `t1`; + +drop table t1; + --disable_query_log # Restore timezone to default set time_zone= @@global.time_zone; -- cgit v1.2.1 From 29ac245dd04c5416d57a90b0ab3c4d08cbc4d723 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Mon, 7 Sep 2015 13:13:52 +0200 Subject: MDEV-8473: mysqlbinlog -v does not properly decode DECIMAL values in an RBR log Backport of upstream patch. revno: 5696 --- .../suite/binlog/r/binlog_mysqlbinlog_row.result | 6 +- .../binlog/r/binlog_mysqlbinlog_row_innodb.result | 72 +++++++++++----------- .../binlog/r/binlog_mysqlbinlog_row_myisam.result | 72 +++++++++++----------- 3 files changed, 75 insertions(+), 75 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result index 469e670ae9d..3604f5e8f8e 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result @@ -1409,7 +1409,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F ### INSERT INTO `test`.`t1` ### SET -### @1=000000124.450000000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ +### @1=124.45000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ # at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 SET TIMESTAMP=1000000000/*!*/; @@ -1426,7 +1426,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F ### INSERT INTO `test`.`t1` ### SET -### @1=-000000543.210000000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ +### @1=-543.21000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ # at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 SET TIMESTAMP=1000000000/*!*/; @@ -1443,7 +1443,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F ### DELETE FROM `test`.`t1` ### WHERE -### @1=000000124.450000000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ +### @1=124.45000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ # at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 SET TIMESTAMP=1000000000/*!*/; diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result index 8e75d203ecc..e942071abcd 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result @@ -2390,9 +2390,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2483,9 +2483,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2659,9 +2659,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2752,9 +2752,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2832,9 +2832,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2925,9 +2925,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3005,9 +3005,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3178,9 +3178,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3271,9 +3271,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3444,9 +3444,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3537,9 +3537,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3630,9 +3630,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result index b4ea8551ca6..29a1704743c 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result @@ -2390,9 +2390,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2485,9 +2485,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2663,9 +2663,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2758,9 +2758,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2838,9 +2838,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2933,9 +2933,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3013,9 +3013,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3188,9 +3188,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3283,9 +3283,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3458,9 +3458,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3553,9 +3553,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3648,9 +3648,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ -- cgit v1.2.1 From dca4ab92b8e32ae1abe54666cf2313d94d5804a4 Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Thu, 24 Sep 2015 21:24:28 +0300 Subject: MDEV-8841 innodb_zip.innodb-create-options fails in buildbot The real problem is that when innodb.xa_recovery test intentionally crashes the server, system tables can be opened and marked as crashed, and the next test in line gets blamed for the error which appears in the error log. Fixed by flushing the tables before crashing the server --- mysql-test/suite/innodb/t/xa_recovery.test | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/t/xa_recovery.test b/mysql-test/suite/innodb/t/xa_recovery.test index ff6da07f6ac..aec606de77e 100644 --- a/mysql-test/suite/innodb/t/xa_recovery.test +++ b/mysql-test/suite/innodb/t/xa_recovery.test @@ -6,6 +6,12 @@ if (`select plugin_auth_version <= "5.5.43-MariaDB-37.2" from information_schema # Embedded server does not support restarting. --source include/not_embedded.inc +# MDEV-8841 - close tables opened by previous tests, +# so they don't get marked crashed when the server gets crashed +--disable_query_log +FLUSH TABLES; +--enable_query_log + CREATE TABLE t1 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); connect (con1,localhost,root); -- cgit v1.2.1 From 86ed494aef0e7fb1f75af32d0665e9b42f36ee17 Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Sat, 26 Sep 2015 02:48:55 +0300 Subject: MDEV-8849 rpl.rpl_innodb_bug30888 sporadically fails in buildbot with testcase timeout The test would take really long if it was run without --mem. Fixed by adding --innodb-flush-log-at-trx-commit=2 --- mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt | 1 + 1 file changed, 1 insertion(+) create mode 100644 mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt b/mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt new file mode 100644 index 00000000000..e6685732d90 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt @@ -0,0 +1 @@ +--innodb-flush-log-at-trx-commit=2 -- cgit v1.2.1 From bdcf37076595b003d74edc6a23c53ac23fba64a8 Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Sun, 27 Sep 2015 16:00:48 +0300 Subject: MDEV-7933 plugins.feedback_plugin_send depends on being executed after plugins.feedback_plugin_load The culprit is the feedback_plugin_load test, which is run both separately and from inside feedback_plugin_send.test. The test queries I_S.FEEDBACK, and every time it does, 'FEEDBACK used' value is increased. Fixed by checking that the value is increased instead of recording the actual value in the result file. --- .../suite/plugins/r/feedback_plugin_load.result | 7 +++++-- .../suite/plugins/r/feedback_plugin_send.result | 7 +++++-- mysql-test/suite/plugins/t/feedback_plugin_load.test | 19 ++++++++++++++++++- 3 files changed, 28 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/plugins/r/feedback_plugin_load.result b/mysql-test/suite/plugins/r/feedback_plugin_load.result index 443b91bf0cc..ea6eae98601 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_load.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_load.result @@ -1,10 +1,13 @@ select plugin_status from information_schema.plugins where plugin_name='feedback'; plugin_status ACTIVE +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +variable_value = @feedback_used + 1 +1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; VARIABLE_NAME VARIABLE_VALUE -FEEDBACK used 1 FEEDBACK version 1.1 FEEDBACK_SEND_RETRY_WAIT 60 FEEDBACK_SEND_TIMEOUT 60 diff --git a/mysql-test/suite/plugins/r/feedback_plugin_send.result b/mysql-test/suite/plugins/r/feedback_plugin_send.result index 2852240ca5b..90a37f7ffbc 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_send.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_send.result @@ -1,10 +1,13 @@ select plugin_status from information_schema.plugins where plugin_name='feedback'; plugin_status ACTIVE +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +variable_value = @feedback_used + 1 +1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; VARIABLE_NAME VARIABLE_VALUE -FEEDBACK used 2 FEEDBACK version 1.1 FEEDBACK_SEND_RETRY_WAIT 60 FEEDBACK_SEND_TIMEOUT 60 diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test index 5ad301667b4..f7c62b96b49 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_load.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -4,7 +4,24 @@ if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'fe } select plugin_status from information_schema.plugins where plugin_name='feedback'; + +# Every SELECT from INFORMATION_SCHEMA.FEEDBACK increases the value of 'FEEDBACK used'. +# We cannot record the actual value, because the test can be executed more than once, +# but we can check that the value indeed increases as expected. +# There is still a room for some race condition, e.g. if at the very moment +# between first SELECT to store the value and the next SELECT to check that it increases, +# the feedback plugin is activated. But the probability of it is close to 0, +# so lets get back to it if it ever happens. + +# Lets say the plugin was used X times before this SELECT +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now $feedback_used == X+1, and 'FEEDBACK used' is also X+1. And variable_value is increased again when we run the next SELECT +SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now when we are happy with 'FEEDBACK used', we can check everything else + --replace_result https http --sorted_result select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; -- cgit v1.2.1 From ce7d8c5ee8c459ac692715994fa73b8f29e9e48a Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Sun, 27 Sep 2015 18:01:47 +0300 Subject: MDEV-7330 plugins.feedback_plugin_send fails sporadically in buildbot The test restarts the server and expects that the feedback plugin will send a report on shutdown, and will write about it in the error log. But the server is only given 10 sec to shut down properly, which is not always enough. Added a parameter to restart_mysqld.inc, and set it to a bigger value in feedback_plugin_send --- mysql-test/include/restart_mysqld.inc | 17 ++++++++++++++++- mysql-test/suite/plugins/t/feedback_plugin_send.test | 9 +++++++++ 2 files changed, 25 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/include/restart_mysqld.inc b/mysql-test/include/restart_mysqld.inc index 7cb9c7994d8..ed0fe64a547 100644 --- a/mysql-test/include/restart_mysqld.inc +++ b/mysql-test/include/restart_mysqld.inc @@ -1,3 +1,8 @@ +# ==== Usage ==== +# +# [--let $shutdown_timeout= 30] +# [--let $allow_rpl_inited= 1] +# --source include/restart_mysqld.inc if ($rpl_inited) { @@ -7,6 +12,16 @@ if ($rpl_inited) } } +--let $server_shutdown_timeout= 10 +if ($shutdown_timeout) +{ + --let $server_shutdown_timeout= $shutdown_timeout +} +if ($shutdown_timeout == 0) +{ + --let $server_shutdown_timeout= 0 +} + # Write file to make mysql-test-run.pl expect the "crash", but don't start # it until it's told to --let $_server_id= `SELECT @@server_id` @@ -15,7 +30,7 @@ if ($rpl_inited) # Send shutdown to the connected server and give # it 10 seconds to die before zapping it -shutdown_server 10; +shutdown_server $server_shutdown_timeout; # Write file to make mysql-test-run.pl start up the server again --exec echo "restart" > $_expect_file_name diff --git a/mysql-test/suite/plugins/t/feedback_plugin_send.test b/mysql-test/suite/plugins/t/feedback_plugin_send.test index 45b507f8e78..31542c33482 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_send.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_send.test @@ -14,6 +14,15 @@ if (!$MTR_FEEDBACK_PLUGIN) { # is doing some work in other workers. # sleep 310; + +# The test expects that the plugin will send a report at least 2 times, +# now (5 min after loading) and on server shutdown which happens below. +# Since we have already waited for 5 min, let's be generous +# and make sure the server has enough time to shut down properly. +# We won't lose anything if the shutdown is fast, but if it's slow, the plugin +# will still be able to finish the job and write about it in the error log. + +--let $shutdown_timeout= 60 source include/restart_mysqld.inc; replace_result https http; -- cgit v1.2.1 From f804b74fd498bce2d527008146b5a0288580d75c Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Mon, 28 Sep 2015 03:40:29 +0300 Subject: MDEV-8154 rpl.show_status_stop_slave_race-7126 sporadically causes internal check failure The patch was pushed into 10.0, but it needs to be applied to 5.5 as well --- mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result | 2 ++ mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test | 7 +++++++ 2 files changed, 9 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result b/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result index e71bb2e29c9..c2a0498509b 100644 --- a/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result +++ b/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result @@ -1,2 +1,4 @@ include/master-slave.inc [connection master] +start slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test b/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test index a79a1885a6c..38759c9b16a 100644 --- a/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test +++ b/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test @@ -10,3 +10,10 @@ --exec $MYSQL_SLAP --silent --socket=$SLAVE_MYSOCK -q "START SLAVE; STOP SLAVE; SHOW GLOBAL STATUS" -c 2 --number-of-queries=100 --create-schema=test # All done. + +--connection slave +start slave; + +--connection master +--source include/rpl_end.inc + -- cgit v1.2.1 From 02a38fd27e71fb6657a4da294bd8dd2e1c2b216c Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Thu, 24 Sep 2015 17:25:52 +0200 Subject: MDEV-8624: MariaDB hangs on query with many logical condition Made no_rows_in_result()/restore_to_before_no_rows_in_result() not looking annecessary deep with walk() method. --- mysql-test/r/func_misc.result | 198 +++++++++++++++++++++++++++++++++++++++++ mysql-test/t/func_misc.test | 199 ++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 397 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index d654dbccb0e..a121bd324ee 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -374,5 +374,203 @@ NULL Warnings: Warning 1411 Incorrect timeout value: '-1' for function get_lock # +# MDEV-8624 MariaDB hangs on query with many logical condition +# +CREATE TABLE `t1` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`submitdate` datetime DEFAULT NULL, +`lastpage` int(11) DEFAULT NULL, +`startlanguage` varchar(20) COLLATE utf8_unicode_ci NOT NULL, +`token` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL, +`datestamp` datetime NOT NULL, +`startdate` datetime NOT NULL, +`ipaddr` text COLLATE utf8_unicode_ci, +`refurl` text COLLATE utf8_unicode_ci, +`57813X540X1723` text COLLATE utf8_unicode_ci, +`57813X540X1724` text COLLATE utf8_unicode_ci, +`57813X540X1725` text COLLATE utf8_unicode_ci, +`57813X540X1726` double DEFAULT NULL, +`57813X540X1909` double DEFAULT NULL, +`57813X541X17271` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17272` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17273` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17274` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17275` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17276` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17281` text COLLATE utf8_unicode_ci, +`57813X541X17282` text COLLATE utf8_unicode_ci, +`57813X541X17283` text COLLATE utf8_unicode_ci, +`57813X541X17284` text COLLATE utf8_unicode_ci, +`57813X541X17285` text COLLATE utf8_unicode_ci, +`57813X541X17286` text COLLATE utf8_unicode_ci, +`57813X542X18131` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18132` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18133` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18134` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18135` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18136` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18137` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18138` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18139` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18141` text COLLATE utf8_unicode_ci, +`57813X542X18142` text COLLATE utf8_unicode_ci, +`57813X542X18143` text COLLATE utf8_unicode_ci, +`57813X542X18144` text COLLATE utf8_unicode_ci, +`57813X542X18145` text COLLATE utf8_unicode_ci, +`57813X542X18146` text COLLATE utf8_unicode_ci, +`57813X542X18147` text COLLATE utf8_unicode_ci, +`57813X542X18148` text COLLATE utf8_unicode_ci, +`57813X542X18149` text COLLATE utf8_unicode_ci, +`57813X543X18451` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18452` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18453` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18454` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18455` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18456` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18461` text COLLATE utf8_unicode_ci, +`57813X543X18462` text COLLATE utf8_unicode_ci, +`57813X543X18463` text COLLATE utf8_unicode_ci, +`57813X543X18464` text COLLATE utf8_unicode_ci, +`57813X543X18465` text COLLATE utf8_unicode_ci, +`57813X543X18466` text COLLATE utf8_unicode_ci, +`57813X544X18711` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18712` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18713` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18714` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18715` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18716` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18717` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18718` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18721` text COLLATE utf8_unicode_ci, +`57813X544X18722` text COLLATE utf8_unicode_ci, +`57813X544X18723` text COLLATE utf8_unicode_ci, +`57813X544X18724` text COLLATE utf8_unicode_ci, +`57813X544X18725` text COLLATE utf8_unicode_ci, +`57813X544X18726` text COLLATE utf8_unicode_ci, +`57813X544X18727` text COLLATE utf8_unicode_ci, +`57813X544X18728` text COLLATE utf8_unicode_ci, +`57813X546X1902` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X546X1903` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X546X1904` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X545X1901` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `lime_survey_57813_idx` (`token`), +KEY `57813X540X1723` (`57813X540X1723`(100)), +KEY `57813X540X1724` (`57813X540X1724`(100)), +KEY `57813X540X1726` (`57813X540X1726`), +KEY `57813X540X1725` (`57813X540X1725`(100)), +KEY `57813X546X1902` (`57813X546X1902`), +KEY `57813X546X1903` (`57813X546X1903`), +KEY `57813X546X1904` (`57813X546X1904`) +); +SELECT +COUNT(*) as `N`, +ROUND( +( +SUM( +( +( +IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 57813X541X17271, 0 ) + +IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 57813X541X17272, 0 ) + +IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 57813X541X17273, 0 ) + +IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 57813X541X17274, 0 ) + +IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 57813X541X17275, 0 ) + +IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 57813X541X17276, 0 ) + +IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 57813X542X18131, 0 ) + +IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 57813X542X18132, 0 ) + +IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 57813X542X18133, 0 ) + +IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 57813X542X18134, 0 ) + +IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 57813X542X18135, 0 ) + +IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 57813X542X18136, 0 ) + +IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 57813X542X18137, 0 ) + +IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 57813X542X18138, 0 ) + +IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 57813X542X18139, 0 ) + +IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 57813X543X18451, 0 ) + +IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 57813X543X18452, 0 ) + +IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 57813X543X18453, 0 ) + +IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 57813X543X18454, 0 ) + +IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 57813X543X18455, 0 ) + +IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 57813X543X18456, 0 ) + +IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 57813X544X18711, 0 ) + +IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 57813X544X18712, 0 ) + +IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 57813X544X18713, 0 ) + +IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 57813X544X18714, 0 ) + +IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 57813X544X18715, 0 ) + +IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 57813X544X18716, 0 ) + +IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 57813X544X18717, 0 ) + +IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 57813X544X18718, 0 ) +) +/ +( +IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 1, 0 ) + +IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 1, 0 ) + +IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 1, 0 ) + +IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 1, 0 ) + +IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 1, 0 ) + +IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 1, 0 ) + +IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 1, 0 ) + +IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 1, 0 ) + +IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 1, 0 ) + +IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 1, 0 ) + +IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 1, 0 ) + +IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 1, 0 ) + +IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 1, 0 ) + +IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 1, 0 ) + +IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 1, 0 ) + +IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 1, 0 ) + +IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 1, 0 ) + +IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 1, 0 ) + +IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 1, 0 ) + +IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 1, 0 ) + +IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 1, 0 ) + +IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 1, 0 ) + +IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 1, 0 ) + +IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 1, 0 ) + +IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 1, 0 ) + +IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 1, 0 ) + +IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 1, 0 ) + +IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 1, 0 ) + +IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 1, 0 ) +) +) +) +/ COUNT(*) ), 4) as `AVG` +FROM `t1` +WHERE `submitdate` IS NOT NULL +AND ( +( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99' ) OR +( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99' ) OR +( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99' ) OR +( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99' ) OR +( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99' ) OR +( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99' ) OR +( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99' ) OR +( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99' ) OR +( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99' ) OR +( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99' ) OR +( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99' ) OR +( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99' ) OR +( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99' ) OR +( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99' ) OR +( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99' ) OR +( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99' ) OR +( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99' ) OR +( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99' ) OR +( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99' ) OR +( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99' ) OR +( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99' ) OR +( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99' ) OR +( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99' ) OR +( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99' ) OR +( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99' ) OR +( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99' ) OR +( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99' ) OR +( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99' ) OR +( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99' ) ) +AND 57813X540X1723 = 'Test'; +N AVG +0 NULL +drop table t1; +# # End of 5.5 tests # diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 25d8cc1067a..5991220e273 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -397,6 +397,205 @@ drop table t1,tv; SELECT GET_LOCK('ul1', NULL); SELECT GET_LOCK('ul1', -1); +--echo # +--echo # MDEV-8624 MariaDB hangs on query with many logical condition +--echo # +CREATE TABLE `t1` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `submitdate` datetime DEFAULT NULL, + `lastpage` int(11) DEFAULT NULL, + `startlanguage` varchar(20) COLLATE utf8_unicode_ci NOT NULL, + `token` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL, + `datestamp` datetime NOT NULL, + `startdate` datetime NOT NULL, + `ipaddr` text COLLATE utf8_unicode_ci, + `refurl` text COLLATE utf8_unicode_ci, + `57813X540X1723` text COLLATE utf8_unicode_ci, + `57813X540X1724` text COLLATE utf8_unicode_ci, + `57813X540X1725` text COLLATE utf8_unicode_ci, + `57813X540X1726` double DEFAULT NULL, + `57813X540X1909` double DEFAULT NULL, + `57813X541X17271` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17272` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17273` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17274` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17275` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17276` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17281` text COLLATE utf8_unicode_ci, + `57813X541X17282` text COLLATE utf8_unicode_ci, + `57813X541X17283` text COLLATE utf8_unicode_ci, + `57813X541X17284` text COLLATE utf8_unicode_ci, + `57813X541X17285` text COLLATE utf8_unicode_ci, + `57813X541X17286` text COLLATE utf8_unicode_ci, + `57813X542X18131` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18132` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18133` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18134` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18135` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18136` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18137` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18138` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18139` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18141` text COLLATE utf8_unicode_ci, + `57813X542X18142` text COLLATE utf8_unicode_ci, + `57813X542X18143` text COLLATE utf8_unicode_ci, + `57813X542X18144` text COLLATE utf8_unicode_ci, + `57813X542X18145` text COLLATE utf8_unicode_ci, + `57813X542X18146` text COLLATE utf8_unicode_ci, + `57813X542X18147` text COLLATE utf8_unicode_ci, + `57813X542X18148` text COLLATE utf8_unicode_ci, + `57813X542X18149` text COLLATE utf8_unicode_ci, + `57813X543X18451` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18452` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18453` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18454` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18455` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18456` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18461` text COLLATE utf8_unicode_ci, + `57813X543X18462` text COLLATE utf8_unicode_ci, + `57813X543X18463` text COLLATE utf8_unicode_ci, + `57813X543X18464` text COLLATE utf8_unicode_ci, + `57813X543X18465` text COLLATE utf8_unicode_ci, + `57813X543X18466` text COLLATE utf8_unicode_ci, + `57813X544X18711` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18712` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18713` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18714` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18715` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18716` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18717` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18718` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18721` text COLLATE utf8_unicode_ci, + `57813X544X18722` text COLLATE utf8_unicode_ci, + `57813X544X18723` text COLLATE utf8_unicode_ci, + `57813X544X18724` text COLLATE utf8_unicode_ci, + `57813X544X18725` text COLLATE utf8_unicode_ci, + `57813X544X18726` text COLLATE utf8_unicode_ci, + `57813X544X18727` text COLLATE utf8_unicode_ci, + `57813X544X18728` text COLLATE utf8_unicode_ci, + `57813X546X1902` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X546X1903` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X546X1904` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X545X1901` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `lime_survey_57813_idx` (`token`), + KEY `57813X540X1723` (`57813X540X1723`(100)), + KEY `57813X540X1724` (`57813X540X1724`(100)), + KEY `57813X540X1726` (`57813X540X1726`), + KEY `57813X540X1725` (`57813X540X1725`(100)), + KEY `57813X546X1902` (`57813X546X1902`), + KEY `57813X546X1903` (`57813X546X1903`), + KEY `57813X546X1904` (`57813X546X1904`) +); + +SELECT +COUNT(*) as `N`, +ROUND( + ( + SUM( + ( + ( + IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 57813X541X17271, 0 ) + + IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 57813X541X17272, 0 ) + + IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 57813X541X17273, 0 ) + + IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 57813X541X17274, 0 ) + + IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 57813X541X17275, 0 ) + + IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 57813X541X17276, 0 ) + + IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 57813X542X18131, 0 ) + + IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 57813X542X18132, 0 ) + + IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 57813X542X18133, 0 ) + + IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 57813X542X18134, 0 ) + + IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 57813X542X18135, 0 ) + + IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 57813X542X18136, 0 ) + + IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 57813X542X18137, 0 ) + + IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 57813X542X18138, 0 ) + + IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 57813X542X18139, 0 ) + + IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 57813X543X18451, 0 ) + + IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 57813X543X18452, 0 ) + + IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 57813X543X18453, 0 ) + + IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 57813X543X18454, 0 ) + + IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 57813X543X18455, 0 ) + + IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 57813X543X18456, 0 ) + + IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 57813X544X18711, 0 ) + + IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 57813X544X18712, 0 ) + + IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 57813X544X18713, 0 ) + + IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 57813X544X18714, 0 ) + + IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 57813X544X18715, 0 ) + + IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 57813X544X18716, 0 ) + + IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 57813X544X18717, 0 ) + + IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 57813X544X18718, 0 ) + ) + / + ( + IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 1, 0 ) + + IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 1, 0 ) + + IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 1, 0 ) + + IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 1, 0 ) + + IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 1, 0 ) + + IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 1, 0 ) + + IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 1, 0 ) + + IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 1, 0 ) + + IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 1, 0 ) + + IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 1, 0 ) + + IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 1, 0 ) + + IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 1, 0 ) + + IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 1, 0 ) + + IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 1, 0 ) + + IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 1, 0 ) + + IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 1, 0 ) + + IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 1, 0 ) + + IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 1, 0 ) + + IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 1, 0 ) + + IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 1, 0 ) + + IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 1, 0 ) + + IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 1, 0 ) + + IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 1, 0 ) + + IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 1, 0 ) + + IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 1, 0 ) + + IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 1, 0 ) + + IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 1, 0 ) + + IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 1, 0 ) + + IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 1, 0 ) + ) + ) + ) + / COUNT(*) ), 4) as `AVG` +FROM `t1` +WHERE `submitdate` IS NOT NULL +AND ( + ( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99' ) OR + ( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99' ) OR + ( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99' ) OR + ( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99' ) OR + ( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99' ) OR + ( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99' ) OR + ( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99' ) OR + ( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99' ) OR + ( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99' ) OR + ( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99' ) OR + ( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99' ) OR + ( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99' ) OR + ( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99' ) OR + ( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99' ) OR + ( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99' ) OR + ( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99' ) OR + ( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99' ) OR + ( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99' ) OR + ( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99' ) OR + ( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99' ) OR + ( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99' ) OR + ( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99' ) OR + ( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99' ) OR + ( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99' ) OR + ( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99' ) OR + ( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99' ) OR + ( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99' ) OR + ( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99' ) OR + ( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99' ) ) +AND 57813X540X1723 = 'Test'; + +drop table t1; + --echo # --echo # End of 5.5 tests --echo # -- cgit v1.2.1 From 006acf7454730ca6b6a603b2cd74b9bde3b6c020 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Wed, 30 Sep 2015 10:49:45 +0300 Subject: Bug #68148: drop index on a foreign key column leads to missing table MDEV-8845: Table disappear after modifying FK Added test case. --- mysql-test/suite/innodb/r/innodb_bug68148.result | 36 +++++++++++++++++++++ mysql-test/suite/innodb/t/innodb_bug68148.test | 41 ++++++++++++++++++++++++ 2 files changed, 77 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb_bug68148.result create mode 100644 mysql-test/suite/innodb/t/innodb_bug68148.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb_bug68148.result b/mysql-test/suite/innodb/r/innodb_bug68148.result new file mode 100644 index 00000000000..88247053389 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug68148.result @@ -0,0 +1,36 @@ +set global innodb_file_per_table=1; +CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; +CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; +CREATE TABLE `main` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`ref_id1` int(11) NOT NULL, +`ref_id2` int(11) NOT NULL, +PRIMARY KEY (`id`), +UNIQUE KEY `idx_1` (`ref_id1`,`ref_id2`), +KEY `FK_set_out_analysis_route_id` (`ref_id2`), +CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`) , +CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`) +) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS=0; +DROP INDEX `idx_1` ON `main`; +SHOW TABLES; +Tables_in_test +main +ref_table1 +ref_table2 +# restart and see if we can still access the main table +SET FOREIGN_KEY_CHECKS=0; +ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`); +SHOW CREATE TABLE `main`; +Table Create Table +main CREATE TABLE `main` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `ref_id1` int(11) NOT NULL, + `ref_id2` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `FK_set_out_analysis_route_id` (`ref_id2`), + KEY `idx_1` (`ref_id1`), + CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`), + CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE main, ref_table1, ref_table2; diff --git a/mysql-test/suite/innodb/t/innodb_bug68148.test b/mysql-test/suite/innodb/t/innodb_bug68148.test new file mode 100644 index 00000000000..531baa30e48 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug68148.test @@ -0,0 +1,41 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc + +# +# Bug #68148: drop index on a foreign key column leads to missing table +# MDEV-8845: Table disappear after modifying FK +# + +set global innodb_file_per_table=1; + +CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; + +CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; + +CREATE TABLE `main` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `ref_id1` int(11) NOT NULL, + `ref_id2` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `idx_1` (`ref_id1`,`ref_id2`), + KEY `FK_set_out_analysis_route_id` (`ref_id2`), + CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`) , + CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`) +) ENGINE=InnoDB; + +SET FOREIGN_KEY_CHECKS=0; + +DROP INDEX `idx_1` ON `main`; +SHOW TABLES; + +--echo # restart and see if we can still access the main table +--source include/restart_mysqld.inc + +# This is required to access the table +SET FOREIGN_KEY_CHECKS=0; +ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`); +SHOW CREATE TABLE `main`; + +DROP TABLE main, ref_table1, ref_table2; + + -- cgit v1.2.1 From 7ccde2cbd55814d6f8525552d27674a5d1f577bf Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Thu, 23 Apr 2015 19:04:11 +0200 Subject: MDEV-7565: Server crash with Signal 6 (part 2) Preparation of subselect moved earlier (before checks which needs it prepared). --- mysql-test/r/subselect.result | 12 ++++++++++++ mysql-test/r/subselect_no_mat.result | 12 ++++++++++++ mysql-test/r/subselect_no_opts.result | 12 ++++++++++++ mysql-test/r/subselect_no_scache.result | 12 ++++++++++++ mysql-test/r/subselect_no_semijoin.result | 12 ++++++++++++ mysql-test/t/subselect.test | 12 ++++++++++++ 6 files changed, 72 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 4afde93925d..8eac09a2a17 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7015,3 +7015,15 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 9df216da3cb..aed0e6a7e30 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7012,6 +7012,18 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 7b93f277274..07c95851828 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7010,4 +7010,16 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index b6d3a89ea7a..9a49fedb093 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7021,6 +7021,18 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index d51d211e71d..658888d45f5 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7010,5 +7010,17 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d1c3774947a..dd9603ca5d4 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5890,3 +5890,15 @@ DROP TABLE t1,t2; create table t1 (c int); select exists(select 1 from t1 group by `c` in (select `c` from t1)); drop table t1; + +--echo # +--echo # MDEV-7565: Server crash with Signal 6 (part 2) +--echo # +Select + (Select Sum(`TestCase`.Revenue) From mysql.slow_log E + Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) + ) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; + -- cgit v1.2.1 From 2e3e8180483628e6744b6590acf5dd546c6a6076 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Thu, 23 Apr 2015 19:11:06 +0200 Subject: MDEV-7445: Server crash with Signal 6 Problem was in rewriting left expression which had 2 references on it. Solved with making subselect reference main. Item_in_optimized can have not Item_in_subselect reference in left part so type casting with no check is dangerous. Item::cols() should be checked after Item::fix_fields(). --- mysql-test/r/subselect.result | 20 ++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 20 ++++++++++++++++++++ mysql-test/r/subselect_no_opts.result | 20 ++++++++++++++++++++ mysql-test/r/subselect_no_scache.result | 20 ++++++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 20 ++++++++++++++++++++ mysql-test/t/subselect.test | 21 +++++++++++++++++++++ 6 files changed, 121 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 8eac09a2a17..19a845150d8 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7027,3 +7027,23 @@ From Group By TestCase.Revenue, TestCase.TemplateID; ControlRev NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index aed0e6a7e30..649f954fa96 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7024,6 +7024,26 @@ From Group By TestCase.Revenue, TestCase.TemplateID; ControlRev NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 07c95851828..aa06ba11d19 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7022,4 +7022,24 @@ From Group By TestCase.Revenue, TestCase.TemplateID; ControlRev NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 9a49fedb093..a9ee039f3d0 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7033,6 +7033,26 @@ From Group By TestCase.Revenue, TestCase.TemplateID; ControlRev NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 658888d45f5..4d5c3474ac1 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7022,5 +7022,25 @@ From Group By TestCase.Revenue, TestCase.TemplateID; ControlRev NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index dd9603ca5d4..4fb404b1fff 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5902,3 +5902,24 @@ From (Select 3 as Revenue, 4 as TemplateID) As `TestCase` Group By TestCase.Revenue, TestCase.TemplateID; +--echo # +--echo # MDEV-7445:Server crash with Signal 6 +--echo # + +--delimiter | +CREATE PROCEDURE procedure2() +BEGIN + Select + (Select Sum(`TestCase`.Revenue) From mysql.slow_log E + Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) + ) As `ControlRev` + From + (Select 3 as Revenue, 4 as TemplateID) As `TestCase` + Group By TestCase.Revenue, TestCase.TemplateID; + +END | +--delimiter ; +call procedure2(); +call procedure2(); + +drop procedure procedure2; -- cgit v1.2.1 From 0ab93fd6f3050cabac5fbb503173c95bb7073cfc Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Thu, 23 Apr 2015 19:16:57 +0200 Subject: MDEV-7445:Server crash with Signal 6 MDEV-7565: Server crash with Signal 6 (part 2) followup test suite and its fix. --- mysql-test/r/subselect.result | 8 +++++++- mysql-test/r/subselect_no_mat.result | 8 +++++++- mysql-test/r/subselect_no_opts.result | 8 +++++++- mysql-test/r/subselect_no_scache.result | 8 +++++++- mysql-test/r/subselect_no_semijoin.result | 8 +++++++- mysql-test/t/subselect.test | 7 +++++++ 6 files changed, 42 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 19a845150d8..0ab2d487e99 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3590,7 +3590,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7047,3 +7047,9 @@ call procedure2(); ControlRev NULL drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 649f954fa96..5780351e811 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -3594,7 +3594,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7044,6 +7044,12 @@ call procedure2(); ControlRev NULL drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index aa06ba11d19..4bea029341b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -3590,7 +3590,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7042,4 +7042,10 @@ call procedure2(); ControlRev NULL drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index a9ee039f3d0..fdb3b129d46 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -3596,7 +3596,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7053,6 +7053,12 @@ call procedure2(); ControlRev NULL drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 4d5c3474ac1..cb6d35d3606 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -3590,7 +3590,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7042,5 +7042,11 @@ call procedure2(); ControlRev NULL drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 4fb404b1fff..3eb056d879b 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5923,3 +5923,10 @@ call procedure2(); call procedure2(); drop procedure procedure2; + + +SELECT + (SELECT user FROM mysql.user + WHERE h.host in (SELECT host FROM mysql.user) + ) AS sq +FROM mysql.host h GROUP BY h.host; -- cgit v1.2.1 From 54b998173b128bb8362b5dbafbd66c4199776937 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Thu, 23 Apr 2015 20:08:57 +0200 Subject: MDEV-7846: Server crashes in Item_subselect::fix_fields or fails with Thread stack overrun Substitute into transformed subselects original left expression and than register its change in case it was substituted. --- mysql-test/r/subselect.result | 28 +++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 28 +++++++++++++++++++++++++ mysql-test/r/subselect_no_opts.result | 28 +++++++++++++++++++++++++ mysql-test/r/subselect_no_scache.result | 28 +++++++++++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 28 +++++++++++++++++++++++++ mysql-test/t/subselect.test | 34 +++++++++++++++++++++++++++++++ 6 files changed, 174 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 0ab2d487e99..cf52ba2e2be 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7053,3 +7053,31 @@ WHERE h.host in (SELECT host FROM mysql.user) ) AS sq FROM mysql.host h GROUP BY h.host; sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 5780351e811..73a69e98c5b 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7050,6 +7050,34 @@ WHERE h.host in (SELECT host FROM mysql.user) ) AS sq FROM mysql.host h GROUP BY h.host; sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 4bea029341b..1512e39c52d 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7048,4 +7048,32 @@ WHERE h.host in (SELECT host FROM mysql.user) ) AS sq FROM mysql.host h GROUP BY h.host; sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index fdb3b129d46..26cea1f9f70 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7059,6 +7059,34 @@ WHERE h.host in (SELECT host FROM mysql.user) ) AS sq FROM mysql.host h GROUP BY h.host; sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index cb6d35d3606..4c6f03780dd 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7048,5 +7048,33 @@ WHERE h.host in (SELECT host FROM mysql.user) ) AS sq FROM mysql.host h GROUP BY h.host; sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 3eb056d879b..00ab48b2c63 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5930,3 +5930,37 @@ SELECT WHERE h.host in (SELECT host FROM mysql.user) ) AS sq FROM mysql.host h GROUP BY h.host; + + +--echo # +--echo # MDEV-7846:Server crashes in Item_subselect::fix +--echo #_fields or fails with Thread stack overrun +--echo # +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); + +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; + +INSERT INTO t2 VALUES (1),(4); + +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); + +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); + + +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; + +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; +drop table t1,t2,t3,t4; -- cgit v1.2.1 From c8d511293aae155210089b6de4143d11569af18d Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 8 Oct 2015 00:32:07 +0200 Subject: MDEV-8796 Delete with sub query with information_schema.TABLES deletes too many rows make_cond_for_info_schema() does preserve outer fields --- mysql-test/r/information_schema2.result | 24 ++++++++++++------------ mysql-test/t/information_schema2.test | 19 ++++++++----------- 2 files changed, 20 insertions(+), 23 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema2.result b/mysql-test/r/information_schema2.result index 3f7f3ecd4e1..7e9bdd7088f 100644 --- a/mysql-test/r/information_schema2.result +++ b/mysql-test/r/information_schema2.result @@ -6,15 +6,15 @@ select variable_name from information_schema.session_variables where variable_na (select variable_name from information_schema.session_variables where variable_name = 'basedir'); variable_name BASEDIR -create table t1 (a char); -insert t1 values ('a'),('t'),('z'); -flush status; -select a, exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) from t1; -a exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) -a 0 -t 1 -z 0 -show status like 'created_tmp_tables'; -Variable_name Value -Created_tmp_tables 38 -drop table t1; +create table t1 (x int); +create table t2 (x int); +create table t3 (x int); +create table t4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ; +delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); +select * from t4; +table_name +t1 +t2 +t3 +t4 +drop table t1, t2, t3, t4; diff --git a/mysql-test/t/information_schema2.test b/mysql-test/t/information_schema2.test index 06bc6d1bf55..9810c5a0aae 100644 --- a/mysql-test/t/information_schema2.test +++ b/mysql-test/t/information_schema2.test @@ -8,15 +8,12 @@ select variable_name from information_schema.session_variables where variable_na (select variable_name from information_schema.session_variables where variable_name = 'basedir'); # -# information_schema tables inside subqueries, they should not be re-populated -# (i_s.columns needs to scan i_s itself, creating a tmp table for every i_s -# table. if it's re-populated, it'll do that multiple times) +# MDEV-8796 Delete with sub query with information_schema.TABLES deletes too many rows # -create table t1 (a char); -insert t1 values ('a'),('t'),('z'); -flush status; -select a, exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) from t1; -# fix the result in ps-protocol ---replace_result 39 38 -show status like 'created_tmp_tables'; -drop table t1; +create table t1 (x int); +create table t2 (x int); +create table t3 (x int); +create table t4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ; +delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); +select * from t4; +drop table t1, t2, t3, t4; -- cgit v1.2.1 From 16c4b3c68b06653592a9500050ad977a38f4ebae Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 9 Oct 2015 16:43:59 +0200 Subject: fixes for buildbot: * OSX (mysqlimport freeing unallocated memory) * Windows (didn't compile MSI) * fulltest2 (innodb crashes in --embedded --big) --- mysql-test/disabled.def | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/disabled.def b/mysql-test/disabled.def index 6dc3066e374..310890605ff 100644 --- a/mysql-test/disabled.def +++ b/mysql-test/disabled.def @@ -11,9 +11,8 @@ ############################################################################## tablespace : disabled in MariaDB (no TABLESPACE table attribute) events_time_zone : Test is not predictable as it depends on precise timing. -lowercase_table3 : Bug#11762269 2010-06-30 alik main.lowercase_table3 on Mac OSX read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exists -archive-big : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists mysql_embedded : Bug#12561297 2011-05-14 Anitha Dependent on PB2 changes - eventum#41836 file_contents : MDEV-6526 these files are not installed anymore +lowercase_fs_on : lower_case_table_names=0 is not an error until 10.1 -- cgit v1.2.1