From 6a6d5bc98abdd25b32590fea7ef6572ecc355d7e Mon Sep 17 00:00:00 2001 From: Deepa Dixit Date: Tue, 25 Jul 2017 11:49:51 +0530 Subject: Bug#26161247: MTR: --NOREORDER IS SEARCHING FOR TEST SCRIPT ONLY IN MAIN SUITE Issue: ------ Running MTR with the --no-reorder option by specifying test cases on the command line, without prefixing the suite name results in an error saying the test case was not found in the main suite. This is because MTR looks for the test case only in the main suite, and no other suites. Fix: ---- The fix involves searching for the test in every suite if only the test name is specified. This back-ports two bug fixes: Bug#24967869 and Bug#24365783 Reviewed-by: Pavan Naik RB: 16812 --- mysql-test/lib/mtr_cases.pm | 66 ++++++++++++++++++++++++++++++++++++--------- 1 file changed, 54 insertions(+), 12 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/lib/mtr_cases.pm b/mysql-test/lib/mtr_cases.pm index e8d24eb399f..ce7d4e690c6 100644 --- a/mysql-test/lib/mtr_cases.pm +++ b/mysql-test/lib/mtr_cases.pm @@ -1,5 +1,5 @@ # -*- cperl -*- -# Copyright (c) 2005, 2015, Oracle and/or its affiliates. All rights reserved. +# Copyright (c) 2005, 2017, 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 @@ -143,7 +143,13 @@ sub collect_test_cases ($$$$) { if ( @$opt_cases ) { - # A list of tests was specified on the command line + # A list of tests was specified on the command line. + # Among those, the tests which are not already collected will be + # collected and stored temporarily in an array of hashes pointed + # by the below reference. This array is eventually appeneded to + # the one having all collected test cases. + my $cmdline_cases; + # Check that the tests specified was found # in at least one suite foreach my $test_name_spec ( @$opt_cases ) @@ -162,20 +168,56 @@ sub collect_test_cases ($$$$) { } if ( not $found ) { - $sname= "main" if !$opt_reorder and !$sname; - mtr_error("Could not find '$tname' in '$suites' suite(s)") unless $sname; - # If suite was part of name, find it there, may come with combinations - my @this_case = collect_one_suite($sname, [ $tname ]); - if (@this_case) + if ( $sname ) { - push (@$cases, @this_case); - } - else - { - mtr_error("Could not find '$tname' in '$sname' suite"); + # If suite was part of name, find it there, may come with combinations + my @this_case = collect_one_suite($sname, [ $tname ]); + + # If a test is specified multiple times on the command line, all + # instances of the test need to be picked. Hence, such tests are + # stored in the temporary array instead of adding them to $cases + # directly so that repeated tests are not run only once + if (@this_case) + { + push (@$cmdline_cases, @this_case); + } + else + { + mtr_error("Could not find '$tname' in '$sname' suite"); + } + } + else + { + if ( !$opt_reorder ) + { + # If --no-reorder is passed and if suite was not part of name, + # search in all the suites + foreach my $suite (split(",", $suites)) + { + my @this_case = collect_one_suite($suite, [ $tname ]); + if ( @this_case ) + { + push (@$cmdline_cases, @this_case); + $found= 1; + } + @this_case= collect_one_suite("i_".$suite, [ $tname ]); + if ( @this_case ) + { + push (@$cmdline_cases, @this_case); + $found= 1; + } + } + } + if ( !$found ) + { + mtr_error("Could not find '$tname' in '$suites' suite(s)"); + } } } } + # Add test cases collected in the temporary array to the one + # containing all previously collected test cases + push (@$cases, @$cmdline_cases) if $cmdline_cases; } if ( $opt_reorder && !$quick_collect) -- cgit v1.2.1 From ebd96c314953f5c0073ff5846484fd5e438fe0ad Mon Sep 17 00:00:00 2001 From: Tor Didriksen Date: Tue, 15 Aug 2017 13:15:19 +0200 Subject: Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE (SIG 6 -STRINGS/CTYPE-UTF8.C:5151) Backport from 5.7 to 5.5 Field_set::val_str() should return String("", 0, cs) rather than String(NULL, 0, cs) --- mysql-test/r/case.result | 16 ++++++++++++++++ mysql-test/t/case.test | 12 ++++++++++++ 2 files changed, 28 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index 40d900a0389..be712114a29 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -220,3 +220,19 @@ a d 3 11120436154190595086 drop table t1, t2; End of 5.0 tests +# +# Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE +# (SIG 6 -STRINGS/CTYPE-UTF8.C:5151) +# +set @@sql_mode=''; +CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2) engine=innodb; +Warnings: +Note 1291 Column 'c1' has duplicated value '' in SET +INSERT INTO t1 VALUES(990101.102); +Warnings: +Warning 1265 Data truncated for column 'c1' at row 1 +SELECT COALESCE(c1)FROM t1 ORDER BY 1; +COALESCE(c1) + +DROP TABLE t1; +set @@sql_mode=default; diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index e1c807fe32b..c2d8f44e953 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -173,3 +173,15 @@ select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 drop table t1, t2; --echo End of 5.0 tests + +--echo # +--echo # Bug#19875294 ASSERTION `SRC' FAILED IN MY_STRNXFRM_UNICODE +--echo # (SIG 6 -STRINGS/CTYPE-UTF8.C:5151) +--echo # + +set @@sql_mode=''; +CREATE TABLE t1(c1 SET('','')CHARACTER SET ucs2) engine=innodb; +INSERT INTO t1 VALUES(990101.102); +SELECT COALESCE(c1)FROM t1 ORDER BY 1; +DROP TABLE t1; +set @@sql_mode=default; -- cgit v1.2.1 From dbeffabc83ed01112e09d7e782d44f044cfcb691 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 6 Oct 2017 00:08:36 -0700 Subject: Fixed the bug mdev-11574. Do not build an index merge of two indexes when one index is an infix of the other index. --- mysql-test/r/range_vs_index_merge.result | 81 +++++++++++++++++++++++++ mysql-test/r/range_vs_index_merge_innodb.result | 81 +++++++++++++++++++++++++ mysql-test/t/range_vs_index_merge.test | 53 ++++++++++++++++ 3 files changed, 215 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 6813c40a5cf..bc46a4fdd0b 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1807,4 +1807,85 @@ id state capital 7 Pennsylvania Harrisburg 8 Virginia Richmond DROP TABLE t1; +# +# mdev-11574: do not build index merge of two indexes when +# one index is an infix of the other index +# +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +DROP INDEX Country ON City; +CREATE INDEX CountryName ON City(Country,Name); +CREATE INDEX Name ON City(Name); +select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +ID Name Country Population +175 Antwerpen BEL 446525 +176 Gent BEL 224180 +3068 Berlin DEU 3386667 +3087 Bonn DEU 301048 +3242 Lahti FIN 96921 +2974 Paris FRA 2125246 +1466 Napoli ITA 1002619 +1474 Venezia ITA 277305 +2808 Bergen NOR 230948 +2807 Oslo NOR 508726 +2928 Warszawa POL 1615369 +2931 Wroclaw POL 636765 +2918 Braga PRT 90535 +2915 Porto PRT 273060 +3580 Moscow RUS 8389200 +3581 St Petersburg RUS 4694000 +3048 Stockholm SWE 750348 +3051 Uppsala SWE 189569 +explain select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City range CountryName,Name CountryName 38 NULL 22 Using index condition; Using where +DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 13fbc0ac3ef..a6ec200538d 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -1808,5 +1808,86 @@ id state capital 7 Pennsylvania Harrisburg 8 Virginia Richmond DROP TABLE t1; +# +# mdev-11574: do not build index merge of two indexes when +# one index is an infix of the other index +# +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +DROP INDEX Country ON City; +CREATE INDEX CountryName ON City(Country,Name); +CREATE INDEX Name ON City(Name); +select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +ID Name Country Population +175 Antwerpen BEL 446525 +176 Gent BEL 224180 +3068 Berlin DEU 3386667 +3087 Bonn DEU 301048 +3242 Lahti FIN 96921 +2974 Paris FRA 2125246 +1466 Napoli ITA 1002619 +1474 Venezia ITA 277305 +2808 Bergen NOR 230948 +2807 Oslo NOR 508726 +2928 Warszawa POL 1615369 +2931 Wroclaw POL 636765 +2918 Braga PRT 90535 +2915 Porto PRT 273060 +3580 Moscow RUS 8389200 +3581 St Petersburg RUS 4694000 +3048 Stockholm SWE 750348 +3051 Uppsala SWE 189569 +explain select * from City +where +Country='FIN' AND Name IN ('Lahti','Imatra') OR +Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR +Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR +Country='DEU' AND Name IN ('Berlin', 'Bonn') OR +Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR +Country='PRT' AND Name IN ('Braga', 'Porto') OR +Country='FRA' AND Name IN ('Paris', 'Marcel') OR +Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR +Country='NOR' AND Name IN ('Oslo', 'Bergen') OR +Country='ITA' AND Name IN ('Napoli', 'Venezia'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where +DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test index 5d12d46c9e9..84b87579e85 100644 --- a/mysql-test/t/range_vs_index_merge.test +++ b/mysql-test/t/range_vs_index_merge.test @@ -1241,6 +1241,59 @@ WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 DROP TABLE t1; +--echo # +--echo # mdev-11574: do not build index merge of two indexes when +--echo # one index is an infix of the other index +--echo # + +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +DROP INDEX Country ON City; +CREATE INDEX CountryName ON City(Country,Name); +CREATE INDEX Name ON City(Name); + +--disable_query_log +--disable_result_log +--disable_warnings +ANALYZE TABLE City; +--enable_warnings +--enable_result_log +--enable_query_log + +let $q= +select * from City +where + Country='FIN' AND Name IN ('Lahti','Imatra') OR + Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR + Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR + Country='DEU' AND Name IN ('Berlin', 'Bonn') OR + Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR + Country='PRT' AND Name IN ('Braga', 'Porto') OR + Country='FRA' AND Name IN ('Paris', 'Marcel') OR + Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR + Country='NOR' AND Name IN ('Oslo', 'Bergen') OR + Country='ITA' AND Name IN ('Napoli', 'Venezia'); + +eval $q; +eval explain $q; + + +DROP DATABASE world; + #the following command must be the last one in the file set session optimizer_switch='index_merge_sort_intersection=default'; -- cgit v1.2.1 From e30b6a983fe135dd1af0c79c81dbdb5b14b881c8 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 6 Oct 2017 18:23:40 +0400 Subject: MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value --- mysql-test/r/func_time.result | 14 ++++++++++++++ mysql-test/suite/vcol/r/vcol_misc.result | 19 +++++++++++++++++++ mysql-test/suite/vcol/t/vcol_misc.test | 20 ++++++++++++++++++++ mysql-test/t/func_time.test | 16 ++++++++++++++++ 4 files changed, 69 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 68b1e0f04ad..fa159972c9a 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2626,3 +2626,17 @@ DROP TABLE t1; SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2 3 +# +# MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value +# +SET sql_mode='NO_ZERO_IN_DATE'; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 SELECT timediff(timestamp'2008-12-31 23:59:59.000001',timestamp'2008-12-30 01:01:01.000002'); +SELECT * FROM t1; +a +46:58:57.999999 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# End of 5.5 tests +# diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index 699b6d4efe3..0a8d87dc2f7 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -337,3 +337,22 @@ tsv timestamp as (adddate(ts, interval 1 day)) virtual ); drop table t1; set sql_mode=default; +# +# MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value +# +SET sql_mode='NO_ZERO_IN_DATE'; +CREATE TABLE t1 +( +a datetime DEFAULT NULL, +b datetime DEFAULT NULL, +c time GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL +); +INSERT INTO t1 VALUES ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',DEFAULT); +SELECT * FROM t1; +a b c +2008-12-31 23:59:59 2008-12-30 01:01:01 46:58:58 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# End of 5.5 tests +# diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index 80a36d9c623..1ac0b4f80b7 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -301,3 +301,23 @@ create table t1 ( ); drop table t1; set sql_mode=default; + +--echo # +--echo # MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value +--echo # + +SET sql_mode='NO_ZERO_IN_DATE'; +CREATE TABLE t1 +( + a datetime DEFAULT NULL, + b datetime DEFAULT NULL, + c time GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL +); +INSERT INTO t1 VALUES ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',DEFAULT); +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + +--echo # +--echo # End of 5.5 tests +--echo # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 92e1c38cec2..7544f9e7761 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1602,3 +1602,19 @@ DROP TABLE t1; --echo # MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision() --echo # SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; + + +--echo # +--echo # MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value +--echo # + +SET sql_mode='NO_ZERO_IN_DATE'; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 SELECT timediff(timestamp'2008-12-31 23:59:59.000001',timestamp'2008-12-30 01:01:01.000002'); +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + +--echo # +--echo # End of 5.5 tests +--echo # -- cgit v1.2.1 From ca948e335e0e43538f994484938dd729b32ae286 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Sat, 7 Oct 2017 13:42:11 +0400 Subject: MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant --- mysql-test/r/ctype_gbk.result | 18 ++++++++++++++++++ mysql-test/r/ctype_latin1.result | 18 ++++++++++++++++++ mysql-test/t/ctype_gbk.test | 14 ++++++++++++++ mysql-test/t/ctype_latin1.test | 16 ++++++++++++++++ 4 files changed, 66 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result index c5d997b0213..2d31a15bb62 100644 --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@ -4944,5 +4944,23 @@ E05B DROP TABLE t1; # Start of ctype_E05C.inc # +# MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant +# +SET NAMES latin1; +CREATE TABLE t1 (a TEXT CHARACTER SET gbk); +INSERT INTO t1 VALUES (0xEE5D); +SELECT a<>0xEE5D AS a FROM t1; +a +0 +CREATE VIEW v1 AS SELECT a<>0xEE5D AS a FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`a` <> 0xee5d) AS `a` from `t1` latin1 latin1_swedish_ci +SELECT * FROM v1; +a +0 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.0 tests # diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index eee915267d5..cf48aaab09b 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -7922,5 +7922,23 @@ SELECT _latin1 0x7E, _latin1 X'7E', _latin1 B'01111110'; _latin1 0x7E _latin1 X'7E' _latin1 B'01111110' ~ ~ ~ # +# MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant +# +SET NAMES latin1; +CREATE TABLE t1 (a TEXT CHARACTER SET latin1); +INSERT INTO t1 VALUES (0xC0); +SELECT a<>0xEE5D AS a FROM t1; +a +1 +CREATE VIEW v1 AS SELECT a<>0xC0 AS a FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`a` <> 0xc0) AS `a` from `t1` latin1 latin1_swedish_ci +SELECT * FROM v1; +a +0 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.0 tests # diff --git a/mysql-test/t/ctype_gbk.test b/mysql-test/t/ctype_gbk.test index d44009b6109..454377d98a7 100644 --- a/mysql-test/t/ctype_gbk.test +++ b/mysql-test/t/ctype_gbk.test @@ -199,6 +199,20 @@ let $ctype_unescape_combinations=selected; SET NAMES gbk; --source include/ctype_E05C.inc +--echo # +--echo # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant +--echo # + +SET NAMES latin1; +CREATE TABLE t1 (a TEXT CHARACTER SET gbk); +INSERT INTO t1 VALUES (0xEE5D); +SELECT a<>0xEE5D AS a FROM t1; +CREATE VIEW v1 AS SELECT a<>0xEE5D AS a FROM t1; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; +DROP TABLE t1; + --echo # --echo # End of 10.0 tests diff --git a/mysql-test/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test index aeaad2cc026..8a188b71e24 100644 --- a/mysql-test/t/ctype_latin1.test +++ b/mysql-test/t/ctype_latin1.test @@ -245,6 +245,22 @@ DROP TABLE t1; --echo # SELECT _latin1 0x7E, _latin1 X'7E', _latin1 B'01111110'; + +--echo # +--echo # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant +--echo # + +SET NAMES latin1; +CREATE TABLE t1 (a TEXT CHARACTER SET latin1); +INSERT INTO t1 VALUES (0xC0); +SELECT a<>0xEE5D AS a FROM t1; +CREATE VIEW v1 AS SELECT a<>0xC0 AS a FROM t1; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; +DROP TABLE t1; + + --echo # --echo # End of 10.0 tests --echo # -- cgit v1.2.1 From 5eb666ad37ff80e5f8dc714bb68fcb1e269b797a Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Sat, 7 Oct 2017 14:17:45 +0400 Subject: MDEV-12705 10.1.18-MariaDB-1~jessie - mysqld got signal 11. Space for the next operation wasn't reserved in Item_func_spatial_relate::val_str() --- mysql-test/r/gis-precise.result | 19 +++++++++++++++++++ mysql-test/t/gis-precise.test | 19 +++++++++++++++++++ 2 files changed, 38 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/gis-precise.result b/mysql-test/r/gis-precise.result index 3824ba6afbb..89e5c237413 100644 --- a/mysql-test/r/gis-precise.result +++ b/mysql-test/r/gis-precise.result @@ -486,6 +486,25 @@ ST_Touches(ST_PolygonFromText('POLYGON((0 0,0 5,5 5,5 0,0 0))'),ST_PointFromText select ST_Touches(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)')); ST_Touches(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)')) 0 +SELECT ST_RELATE( +ST_DIFFERENCE( +GEOMETRYFROMTEXT(' + MULTILINESTRING( + ( 12841 36140, 8005 31007, 26555 31075, 52765 41191, + 28978 6548, 45720 32057, 53345 3221 ), + ( 8304 59107, 25233 31592, 40502 25303, 8205 42940 ), + ( 7829 7305, 58841 56759, 64115 8512, 37562 54145, 2210 14701 ), + ( 20379 2805, 40807 27770, 28147 14883, 26439 29383, 55663 5086 ), + ( 35944 64702, 14433 23728, 49317 26241, 790 16941 ) + ) + '), +GEOMETRYFROMTEXT('POINT(46061 13545)') +), +GEOMETRYFROMTEXT('POINT(4599 60359)'), +'F*FFFF**F' + ) as relate_res; +relate_res +0 DROP TABLE IF EXISTS p1; CREATE PROCEDURE p1(dist DOUBLE, geom TEXT) BEGIN diff --git a/mysql-test/t/gis-precise.test b/mysql-test/t/gis-precise.test index 5e57569a912..7391b2114f3 100644 --- a/mysql-test/t/gis-precise.test +++ b/mysql-test/t/gis-precise.test @@ -362,5 +362,24 @@ select ST_Touches(ST_LineFromText('LINESTRING(0 0,5 5)'),ST_PointFromText('POINT select ST_Touches(ST_PolygonFromText('POLYGON((0 0,0 5,5 5,5 0,0 0))'),ST_PointFromText('POINT(0 0)')); select ST_Touches(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)')); +# MDEV-12705 10.1.18-MariaDB-1~jessie - mysqld got signal 11. +SELECT ST_RELATE( + ST_DIFFERENCE( + GEOMETRYFROMTEXT(' + MULTILINESTRING( + ( 12841 36140, 8005 31007, 26555 31075, 52765 41191, + 28978 6548, 45720 32057, 53345 3221 ), + ( 8304 59107, 25233 31592, 40502 25303, 8205 42940 ), + ( 7829 7305, 58841 56759, 64115 8512, 37562 54145, 2210 14701 ), + ( 20379 2805, 40807 27770, 28147 14883, 26439 29383, 55663 5086 ), + ( 35944 64702, 14433 23728, 49317 26241, 790 16941 ) + ) + '), + GEOMETRYFROMTEXT('POINT(46061 13545)') + ), + GEOMETRYFROMTEXT('POINT(4599 60359)'), + 'F*FFFF**F' + ) as relate_res; + --source include/gis_debug.inc -- cgit v1.2.1 From 9b897d663a116c5484dade0404870975fd5a2e68 Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Mon, 9 Oct 2017 01:43:31 +0300 Subject: MDEV-12263 Feature: skipped test file A note about unstable-tests lists in mysql-test/README, RedHat version of it, and also various changes to bring the file up-to-date --- mysql-test/README | 102 ++++++++++++++++++++++++++++++++---------------------- 1 file changed, 61 insertions(+), 41 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/README b/mysql-test/README index 0fba1cc07e3..707820b47ee 100644 --- a/mysql-test/README +++ b/mysql-test/README @@ -1,74 +1,94 @@ -This directory contains a test suite for the MySQL daemon. To run -the currently existing test cases, simply execute ./mysql-test-run in -this directory. It will fire up the newly built mysqld and test it. +This directory contains test suites for the MariaDB server. To run +currently existing test cases, execute ./mysql-test-run in this directory. -Note that you do not have to have to do "make install", and you could -actually have a co-existing MySQL installation. The tests will not -conflict with it. To run the test suite in a source directory, you -must do make first. +Some tests are known to fail on some platforms or be otherwise unreliable. +The file "unstable-tests" contains the list of such tests along with +a comment for every test. +To exclude them from the test run, execute + # ./mysql-test-run --skip-test-list=unstable-tests -All tests must pass. If one or more of them fail on your system, please -read the following manual section for instructions on how to report the -problem: +In general you do not have to have to do "make install", and you can have +a co-existing MariaDB installation, the tests will not conflict with it. +To run the tests in a source directory, you must do "make" first. + +In Red Hat distributions, you should run the script as user "mysql". +The user is created with nologin shell, so the best bet is something like + # su - + # cd /usr/share/mysql-test + # su -s /bin/bash mysql -c "./mysql-test-run --skip-test-list=rh-skipped-tests.list" + +This will use the installed MariaDB executables, but will run a private copy +of the server process (using data files within /usr/share/mysql-test), +so you need not start the mysqld service beforehand. + +"rh-skipped-tests.list" is Red Hat version of unstable-tests list, it +additionally includes tests known to fail specifically on Red Hat builds. +You can omit it if you want to check whether such failures occur for you. + +To clean up afterwards, remove the created "var" subdirectory, e.g. + # su -s /bin/bash - mysql -c "rm -rf /usr/share/mysql-test/var" + +If one or more tests fail on your system on reasons other than listed +in lists of unstable tests, please read the following manual section +for instructions on how to report the problem: https://mariadb.com/kb/en/reporting-bugs If you want to use an already running MySQL server for specific tests, use the --extern option to mysql-test-run. Please note that in this mode, -the test suite expects you to provide the names of the tests to run. +you are expected to provide names of the tests to run. + For example, here is the command to run the "alias" and "analyze" tests with an external server: -mysql-test-run --extern socket=/tmp/mysql.sock alias analyze + # mysql-test-run --extern socket=/tmp/mysql.sock alias analyze -To match your setup, you might also need to provide --socket, --user, and -other relevant options. +To match your setup, you might need to provide other relevant options. -With no test cases named on the command line, mysql-test-run falls back -to the normal "non-extern" behavior. The reason for this is that some -tests cannot run with an external server. +With no test names on the command line, mysql-test-run will attempt +to execute the default set of tests, which will certainly fail, because +many tests cannot run with an external server (they need to control the +options with which the server is started, restart the server during +execution, etc.) You can create your own test cases. To create a test case, create a new file in the t subdirectory using a text editor. The file should have a .test extension. For example: - xemacs t/test_case_name.test + # xemacs t/test_case_name.test - In the file, put a set of SQL statements that create some tables, - load test data, and run some queries to manipulate it. +In the file, put a set of SQL statements that create some tables, +load test data, and run some queries to manipulate it. - We would appreciate it if you name your test tables t1, t2, t3 ... (to not - conflict too much with existing tables). +Your test should begin by dropping the tables you are going to create and +end by dropping them again. This ensures that you can run the test over +and over again. - Your test should begin by dropping the tables you are going to create and - end by dropping them again. This ensures that you can run the test over - and over again. - - If you are using mysqltest commands (like result file names) in your - test case, you should create the result file as follows: +If you are using mysqltest commands in your test case, you should create +the result file as follows: - mysql-test-run --record test_case_name + # mysql-test-run --record test_case_name - or + or - mysqltest --record < t/test_case_name.test + # mysqltest --record < t/test_case_name.test - If you only have a simple test cases consisting of SQL statements and - comments, you can create the test case in one of the following ways: +If you only have a simple test case consisting of SQL statements and +comments, you can create the result file in one of the following ways: - mysql-test-run --record test_case_name + # mysql-test-run --record test_case_name - mysql test < t/test_case_name.test > r/test_case_name.result + # mysql test < t/test_case_name.test > r/test_case_name.result - mysqltest --record --database test --result-file=r/test_case_name.result < t/test_case_name.test + # mysqltest --record --database test --result-file=r/test_case_name.result < t/test_case_name.test - When this is done, take a look at r/test_case_name.result - - If the result is incorrect, you have found a bug. In this case, you should - edit the test result to the correct results so that we can verify - that the bug is corrected in future releases. +When this is done, take a look at r/test_case_name.result . +If the result is incorrect, you have found a bug. In this case, you should +edit the test result to the correct results so that we can verify that +the bug is corrected in future releases. If you want to submit your test case you can send it -to maria-developers@lists.launchpad.com or attach it to a bug report on +to maria-developers@lists.launchpad.net or attach it to a bug report on http://mariadb.org/jira/. If the test case is really big or if it contains 'not public' data, -- cgit v1.2.1 From bc85d22bf0034ed4125c6f01552cb174a7151e32 Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Mon, 9 Oct 2017 02:49:50 +0300 Subject: MDEV-12263 Feature: skipped test file Post-fix: RedHat does not use rh-skipped-tests.list anymore --- mysql-test/README | 11 +++++------ 1 file changed, 5 insertions(+), 6 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/README b/mysql-test/README index 707820b47ee..c4ded4e8e79 100644 --- a/mysql-test/README +++ b/mysql-test/README @@ -15,15 +15,14 @@ In Red Hat distributions, you should run the script as user "mysql". The user is created with nologin shell, so the best bet is something like # su - # cd /usr/share/mysql-test - # su -s /bin/bash mysql -c "./mysql-test-run --skip-test-list=rh-skipped-tests.list" + # su -s /bin/bash mysql -c "./mysql-test-run --skip-test-list=unstable-tests" -This will use the installed MariaDB executables, but will run a private copy -of the server process (using data files within /usr/share/mysql-test), +This will use the installed MariaDB executables, but will run a private +copy of the server process (using data files within /usr/share/mysql-test), so you need not start the mysqld service beforehand. -"rh-skipped-tests.list" is Red Hat version of unstable-tests list, it -additionally includes tests known to fail specifically on Red Hat builds. -You can omit it if you want to check whether such failures occur for you. +You can omit --skip-test-list option if you want to check whether +the listed failures occur for you. To clean up afterwards, remove the created "var" subdirectory, e.g. # su -s /bin/bash - mysql -c "rm -rf /usr/share/mysql-test/var" -- cgit v1.2.1 From 440157cbbe796b6b9a44a3de46bbb93d0cb5a77c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 5 Oct 2017 15:01:38 +0200 Subject: MDEV-13412 main.func_regexp_pcre fails in buildbot on ppc64le Caused by 2fcd8c12522. It used the documented pcre API -pcre_exec(NULL, NULL, NULL, -999, -999, 0, NULL, 0) to calculate the pcre stack frame size. Unfortunately, modern compilers broke it by cloning and inlining pcre match() function. 2fcd8c12522 tried to workaround it by setting the stack frame size to at least 500. It didn't work, 500 is not a universal constant. Now we fix our copy of pcre to not inline or clone match() - so that stack frame detection would work again - and detect at cmake time whether system pcre is broken or usable. Also use stack, not (much slower) malloc in bundled pcre, unless on Windows --- mysql-test/r/func_regexp_pcre.result | 18 +++++++++--------- mysql-test/t/func_regexp_pcre.test | 8 ++++---- 2 files changed, 13 insertions(+), 13 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result index 4089966a7d7..43ea5c23b63 100644 --- a/mysql-test/r/func_regexp_pcre.result +++ b/mysql-test/r/func_regexp_pcre.result @@ -879,32 +879,32 @@ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral, 1 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT CONCAT(REPEAT('100,',400),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; -CONCAT(REPEAT('100,',400),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' +SELECT CONCAT(REPEAT('100,',250),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +CONCAT(REPEAT('100,',250),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' 1 SELECT CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' 0 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',400),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); -REGEXP_INSTR(CONCAT(REPEAT('100,',400),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',250),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +REGEXP_INSTR(CONCAT(REPEAT('100,',250),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') 1 SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') 0 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); -LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) -535 +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) +335 SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) 0 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); -LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) 0 SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) diff --git a/mysql-test/t/func_regexp_pcre.test b/mysql-test/t/func_regexp_pcre.test index 07de4b33271..377ee1a3f02 100644 --- a/mysql-test/t/func_regexp_pcre.test +++ b/mysql-test/t/func_regexp_pcre.test @@ -434,18 +434,18 @@ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral, # # MDEV-13173 An RLIKE that previously worked on 10.0 now returns "Got error 'pcre_exec: recursion limit of 100 exceeded' from regexp" # -SELECT CONCAT(REPEAT('100,',400),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +SELECT CONCAT(REPEAT('100,',250),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; -SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',400),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',250),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); -SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); -SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',400/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); -- cgit v1.2.1 From c2509a1588ee1dc7351b67b2f9149003540015c4 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Tue, 10 Oct 2017 10:35:12 +0400 Subject: MDEV-13972 crash in Item_func_sec_to_time::get_date --- mysql-test/r/ctype_ucs.result | 8 ++++++++ mysql-test/r/func_time.result | 20 ++++++++++++++++++++ mysql-test/t/ctype_ucs.test | 7 +++++++ mysql-test/t/func_time.test | 15 +++++++++++++++ 4 files changed, 50 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 7a93c5524ef..d55d308c694 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -4367,5 +4367,13 @@ NO_ENGINE_SUBSTITUTION SET sql_mode=DEFAULT; SET NAMES utf8; # +# MDEV-13972 crash in Item_func_sec_to_time::get_date +# +SELECT SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)); +SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '77760000' +# # End of 5.5 tests # diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index fa159972c9a..cffed8eae5b 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2638,5 +2638,25 @@ a DROP TABLE t1; SET sql_mode=DEFAULT; # +# MDEV-13972 crash in Item_func_sec_to_time::get_date +# +DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); +DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect time value: '20000101' +SELECT TO_DAYS(SEC_TO_TIME(MAKEDATE(0,RAND(~0)))); +TO_DAYS(SEC_TO_TIME(MAKEDATE(0,RAND(~0)))) +0 +Warnings: +Warning 1292 Truncated incorrect time value: '20000101' +SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +SEC_TO_TIME(MAKEDATE(0,RAND(~0))) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '20000101' +# # End of 5.5 tests # diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index d94c9ae62ac..62890d0cd1b 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -862,6 +862,13 @@ SELECT @@sql_mode; SET sql_mode=DEFAULT; SET NAMES utf8; +--echo # +--echo # MDEV-13972 crash in Item_func_sec_to_time::get_date +--echo # + +SELECT SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)); + + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 7544f9e7761..8323bd30d2c 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1615,6 +1615,21 @@ SELECT * FROM t1; DROP TABLE t1; SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-13972 crash in Item_func_sec_to_time::get_date +--echo # + +# The below query can return warning sporadically +--disable_warnings +DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); +--enable_warnings + +DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); +SELECT TO_DAYS(SEC_TO_TIME(MAKEDATE(0,RAND(~0)))); +SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); + + --echo # --echo # End of 5.5 tests --echo # -- cgit v1.2.1 From 2db5e4d1f94507629490e3fa087e66a9b8eef4d2 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 10 Oct 2017 14:31:33 +0200 Subject: smaller stack size on quantal-x86 and wheezy-x86 fixes failures of func_regexp_pcre --- mysql-test/r/func_regexp_pcre.result | 18 +++++++++--------- mysql-test/t/func_regexp_pcre.test | 8 ++++---- 2 files changed, 13 insertions(+), 13 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result index 43ea5c23b63..d7d57356fe2 100644 --- a/mysql-test/r/func_regexp_pcre.result +++ b/mysql-test/r/func_regexp_pcre.result @@ -879,32 +879,32 @@ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral, 1 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT CONCAT(REPEAT('100,',250),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; -CONCAT(REPEAT('100,',250),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' +SELECT CONCAT(REPEAT('100,',190),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +CONCAT(REPEAT('100,',190),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' 1 SELECT CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' 0 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',250),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); -REGEXP_INSTR(CONCAT(REPEAT('100,',250),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',190),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +REGEXP_INSTR(CONCAT(REPEAT('100,',190),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') 1 SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') 0 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); -LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) -335 +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',190/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',190/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) +255 SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) 0 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp -SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); -LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',190/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',190/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) 0 SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) diff --git a/mysql-test/t/func_regexp_pcre.test b/mysql-test/t/func_regexp_pcre.test index 377ee1a3f02..8f8273bcecb 100644 --- a/mysql-test/t/func_regexp_pcre.test +++ b/mysql-test/t/func_regexp_pcre.test @@ -434,18 +434,18 @@ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral, # # MDEV-13173 An RLIKE that previously worked on 10.0 now returns "Got error 'pcre_exec: recursion limit of 100 exceeded' from regexp" # -SELECT CONCAT(REPEAT('100,',250),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +SELECT CONCAT(REPEAT('100,',190),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT CONCAT(REPEAT('100,',600),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; -SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',250),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',190),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',600),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); -SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',190/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); -SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',250/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',190/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); --replace_regex /[0-9]+ exceeded/NUM exceeded/ SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',600/3),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); -- cgit v1.2.1 From 93aadda513d8b5c2b49001514e235c4fdd73e08a Mon Sep 17 00:00:00 2001 From: Vesa Pentti Date: Sun, 8 Oct 2017 22:15:00 +0300 Subject: MDEV-13149 -- show function status now works with PAD_CHAR_TO_FULL_LENGTH --- ...ow_function_with_pad_char_to_full_length.result | 24 ++++++++++++++++++++++ ...show_function_with_pad_char_to_full_length.test | 23 +++++++++++++++++++++ 2 files changed, 47 insertions(+) create mode 100644 mysql-test/r/show_function_with_pad_char_to_full_length.result create mode 100644 mysql-test/t/show_function_with_pad_char_to_full_length.test (limited to 'mysql-test') diff --git a/mysql-test/r/show_function_with_pad_char_to_full_length.result b/mysql-test/r/show_function_with_pad_char_to_full_length.result new file mode 100644 index 00000000000..785cab7b3e6 --- /dev/null +++ b/mysql-test/r/show_function_with_pad_char_to_full_length.result @@ -0,0 +1,24 @@ +create function f() returns int return 1; +show function status; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +T f T T T T T T T T T +set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; +show function status; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +T f T T T T T T T T T +drop function f; +select @@sql_mode; +@@sql_mode +PAD_CHAR_TO_FULL_LENGTH +create function f() returns int return 1; +select ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME='f'; +ROUTINE_NAME +f +set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; +select ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME='f'; +ROUTINE_NAME +f +drop function f; +select @@sql_mode; +@@sql_mode +PAD_CHAR_TO_FULL_LENGTH diff --git a/mysql-test/t/show_function_with_pad_char_to_full_length.test b/mysql-test/t/show_function_with_pad_char_to_full_length.test new file mode 100644 index 00000000000..f47f36294d4 --- /dev/null +++ b/mysql-test/t/show_function_with_pad_char_to_full_length.test @@ -0,0 +1,23 @@ +# +# Test that show function status succeeds with +# sql_mode = 'PAD_CHAR_TO_FULL_LENGTH (MDEV-13149) + +# show function status + +create function f() returns int return 1; +--replace_column 1 T 3 T 4 T 5 T 6 T 7 T 8 T 9 T 10 T 11 T +show function status; +set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; +--replace_column 1 T 3 T 4 T 5 T 6 T 7 T 8 T 9 T 10 T 11 T +show function status; +drop function f; +select @@sql_mode; + +# select ROUTINE_NAME from information_schema.ROUTINES + +create function f() returns int return 1; +select ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME='f'; +set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; +select ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME='f'; +drop function f; +select @@sql_mode; -- cgit v1.2.1 From fc9ff69578fa8c3d818d6eaaa171b4be49d70814 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Tue, 10 Oct 2017 10:19:10 +0300 Subject: MDEV-13838: Wrong result after altering a partitioned table Reverted incorrect changes done on MDEV-7367 and MDEV-9469. Fixes properly also related bugs: MDEV-13668: InnoDB unnecessarily rebuilds table when renaming a column and adding index MDEV-9469: 'Incorrect key file' on ALTER TABLE MDEV-9548: Alter table (renaming and adding index) fails with "Incorrect key file for table" MDEV-10535: ALTER TABLE causes standalone/wsrep cluster crash MDEV-13640: ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..." Root cause for all these bugs is the fact that MariaDB .frm file can contain virtual columns but InnoDB dictionary does not and previous fixes were incorrect or unnecessarily forced table rebuilt. In index creation key_part->fieldnr can be bigger than number of columns in InnoDB data dictionary. We need to skip not stored fields when calculating correct column number for InnoDB data dictionary. dict_table_get_col_name_for_mysql Remove innobase_match_index_columns Revert incorrect change done on MDEV-7367 innobase_need_rebuild Remove unnecessary rebuild force when column is renamed. innobase_create_index_field_def Calculate InnoDB column number correctly and remove unnecessary column name set. innobase_create_index_def, innobase_create_key_defs Remove unneeded fields parameter. Revert unneeded memset. prepare_inplace_alter_table_dict Remove unneeded col_names parameter index_field_t Remove unneeded col_name member. row_merge_create_index Remove unneeded col_names parameter and resolution. Effected tests: innodb-alter-table : Add test case for MDEV-13668 innodb-alter : Remove MDEV-13668, MDEV-9469 FIXMEs and restore original tests innodb-wl5980-alter : Remove MDEV-13668, MDEV-9469 FIXMEs and restore original tests --- .../suite/innodb/r/innodb-alter-table.result | 41 ++++++++++++++++++++++ mysql-test/suite/innodb/r/innodb-alter.result | 18 +++++----- .../suite/innodb/r/innodb-wl5980-alter.result | 16 ++------- mysql-test/suite/innodb/t/innodb-alter-table.test | 33 +++++++++++++++++ mysql-test/suite/innodb/t/innodb-alter.test | 24 +++---------- mysql-test/suite/innodb/t/innodb-wl5980-alter.test | 31 ++-------------- 6 files changed, 94 insertions(+), 69 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb-alter-table.result b/mysql-test/suite/innodb/r/innodb-alter-table.result index c4460a7226b..34a05d39882 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-table.result +++ b/mysql-test/suite/innodb/r/innodb-alter-table.result @@ -185,3 +185,44 @@ ticket CREATE TABLE `ticket` ( KEY `org_id` (`org_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE ticket; +CREATE TABLE t ( +id bigint(20) unsigned NOT NULL auto_increment, +d date NOT NULL, +a bigint(20) unsigned NOT NULL, +b smallint(5) unsigned DEFAULT NULL, +PRIMARY KEY (id,d) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2 +PARTITION BY RANGE COLUMNS(d) +( +PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB, +PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB); +insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10); +insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10); +replace into t(d,a,b) select '2017-09-15',rand()*10000,rand()*10 from t t1, t t2, t t3, t t4; +select count(*) from t where d ='2017-09-15'; +count(*) +18 +ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a); +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `d` date NOT NULL, + `a` bigint(20) unsigned NOT NULL, + `c` smallint(5) unsigned DEFAULT NULL, + PRIMARY KEY (`id`,`d`), + KEY `idx_d_a` (`d`,`a`) +) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2 +/*!50500 PARTITION BY RANGE COLUMNS(d) +(PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB, + PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ +analyze table t; +Table Op Msg_type Msg_text +test.t analyze status OK +select count(*) from t where d ='2017-09-15'; +count(*) +18 +select count(*) from t force index(primary) where d ='2017-09-15'; +count(*) +18 +DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index 6ffa4c669d7..0bad6c5e0c1 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -540,9 +540,6 @@ ERROR 42000: Key column 'c2' doesn't exist in table ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY; ERROR 42000: Key column 'c2' doesn't exist in table ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. -ALTER TABLE t1n CHANGE c2 c4 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c4), LOCK=NONE; SHOW CREATE TABLE t1n; Table Create Table t1n CREATE TABLE `t1n` ( @@ -559,9 +556,6 @@ ALTER TABLE t1n DROP INDEX c4; ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE; ERROR 42S21: Duplicate column name 'c1' ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. -ALTER TABLE t1n CHANGE c4 c11 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c11), LOCK=NONE; SHOW CREATE TABLE t1n; Table Create Table t1n CREATE TABLE `t1n` ( @@ -640,10 +634,11 @@ CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL; ALTER TABLE t1o DROP INDEX ct, DROP INDEX FTS_DOC_ID_INDEX, CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL; ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id); -call mtr.add_suppression("Error: no matching column for .FTS_DOC_ID. in index .ct.--temporary-- of table .test...t1o"); ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ADD FULLTEXT INDEX(ct); -ERROR HY000: Incorrect key file for table 't1o'; try to repair it +ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, +ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY. DROP TABLE sys_indexes; CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -651,9 +646,16 @@ SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; NAME POS MTYPE PRTYPE LEN +FTS_DOC_ID 0 6 1800 8 +c2 1 6 1027 4 +ct 2 5 524540 10 +cu 3 5 524540 10 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; NAME POS NAME +PRIMARY 0 FTS_DOC_ID +FTS_DOC_ID_INDEX 0 FTS_DOC_ID +ct 0 ct SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i INNER JOIN sys_foreign sf ON i.ID = sf.ID; ID FOR_COL_NAME REF_COL_NAME POS diff --git a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result index 11e4e926b43..59d0921e31f 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result +++ b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result @@ -1010,9 +1010,6 @@ ERROR 42000: Key column 'c2' doesn't exist in table ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY; ERROR 42000: Key column 'c2' doesn't exist in table ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. -ALTER TABLE t1n CHANGE c2 c4 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c4), LOCK=NONE; ### files in MYSQL_DATA_DIR/test FTS_AUX_INDEX_1.ibd FTS_AUX_INDEX_2.ibd @@ -1113,9 +1110,6 @@ tt.ibd ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE; ERROR 42S21: Duplicate column name 'c1' ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. -ALTER TABLE t1n CHANGE c4 c11 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c11), LOCK=NONE; ### files in MYSQL_DATA_DIR/test FTS_AUX_INDEX_1.ibd FTS_AUX_INDEX_2.ibd @@ -1201,13 +1195,9 @@ tt.isl t1c.ibd t1p.ibd tt.ibd -call mtr.add_suppression("Error: no matching column for .FTS_DOC_ID. in index .ct.--temporary-- of table .test...t1o"); ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE; -ERROR HY000: Incorrect key file for table 't1o'; try to repair it -ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, LOCK=NONE; -ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE; ### files in MYSQL_DATA_DIR/test FTS_AUX_INDEX_1.ibd FTS_AUX_INDEX_2.ibd @@ -1249,6 +1239,9 @@ tt.isl t1c.ibd t1p.ibd tt.ibd +ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, +LOCK=NONE; +ERROR 0A000: LOCK=NONE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try LOCK=SHARED. SELECT sc.pos FROM information_schema.innodb_sys_columns sc INNER JOIN information_schema.innodb_sys_tables st ON sc.TABLE_ID=st.TABLE_ID @@ -1352,9 +1345,6 @@ tt.isl tt.ibd ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ADD FULLTEXT INDEX(ct); -ERROR HY000: Incorrect key file for table 't1o'; try to repair it -ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, LOCK=NONE; -ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE; ### files in MYSQL_DATA_DIR/test FTS_AUX_INDEX_1.ibd FTS_AUX_INDEX_2.ibd diff --git a/mysql-test/suite/innodb/t/innodb-alter-table.test b/mysql-test/suite/innodb/t/innodb-alter-table.test index 97f0075f344..0cf456ad146 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-table.test +++ b/mysql-test/suite/innodb/t/innodb-alter-table.test @@ -1,4 +1,5 @@ --source include/innodb_page_size.inc +--source include/have_partition.inc # # MMDEV-8386: MariaDB creates very big tmp file and hangs on xtradb @@ -171,3 +172,35 @@ ALTER TABLE ticket SHOW CREATE TABLE ticket; DROP TABLE ticket; + +# +# MDEV-13838: Wrong result after altering a partitioned table +# + +CREATE TABLE t ( +id bigint(20) unsigned NOT NULL auto_increment, +d date NOT NULL, +a bigint(20) unsigned NOT NULL, +b smallint(5) unsigned DEFAULT NULL, +PRIMARY KEY (id,d) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2 +PARTITION BY RANGE COLUMNS(d) +( +PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB, +PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB); + +insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10); +insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10); + +replace into t(d,a,b) select '2017-09-15',rand()*10000,rand()*10 from t t1, t t2, t t3, t t4; + +select count(*) from t where d ='2017-09-15'; + +ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a); +SHOW CREATE TABLE t; +analyze table t; + +select count(*) from t where d ='2017-09-15'; +select count(*) from t force index(primary) where d ='2017-09-15'; + +DROP TABLE t; diff --git a/mysql-test/suite/innodb/t/innodb-alter.test b/mysql-test/suite/innodb/t/innodb-alter.test index 67f637a1814..af75482703c 100644 --- a/mysql-test/suite/innodb/t/innodb-alter.test +++ b/mysql-test/suite/innodb/t/innodb-alter.test @@ -298,21 +298,12 @@ SHOW CREATE TABLE t1n; ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE; --error ER_KEY_COLUMN_DOES_NOT_EXITS ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY; -# FIXME: MDEV-13668 InnoDB unnecessarily rebuilds table -# when renaming a column and adding index ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE; -ALTER TABLE t1n CHANGE c2 c4 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c4), LOCK=NONE; SHOW CREATE TABLE t1n; ALTER TABLE t1n DROP INDEX c4; --error ER_DUP_FIELDNAME ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE; -# FIXME: MDEV-13668 ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE; -ALTER TABLE t1n CHANGE c4 c11 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c11), LOCK=NONE; SHOW CREATE TABLE t1n; DROP TABLE t1n; @@ -370,16 +361,12 @@ CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL; ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id); -# FIXME: MDEV-9469 'Incorrect key file' on ALTER TABLE -call mtr.add_suppression("Error: no matching column for .FTS_DOC_ID. in index .ct.--temporary-- of table .test...t1o"); ---error ER_NOT_KEYFILE ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ADD FULLTEXT INDEX(ct); -# FIXME: MDEV-9469 (enable this) -#--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -#ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, -#ALGORITHM=INPLACE; -#end of MDEV-9469 FIXME + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, +ALGORITHM=INPLACE; DROP TABLE sys_indexes; CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i @@ -494,6 +481,3 @@ eval ALTER TABLE $source_db.t1 DROP INDEX index2, algorithm=inplace; eval DROP TABLE $source_db.t1; eval DROP DATABASE $source_db; eval DROP DATABASE $dest_db; - - - diff --git a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test index a3ae83753e0..2815a6fc6d7 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test +++ b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test @@ -447,12 +447,7 @@ ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE; --error ER_KEY_COLUMN_DOES_NOT_EXITS ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY; -# FIXME: MDEV-13668 InnoDB unnecessarily rebuilds table -# when renaming a column and adding index ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE; -ALTER TABLE t1n CHANGE c2 c4 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c4), LOCK=NONE; --echo ### files in MYSQL_DATA_DIR/test --replace_regex $regexp @@ -474,11 +469,7 @@ ALTER TABLE t1n DROP INDEX c4; --error ER_DUP_FIELDNAME ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE; -# FIXME: MDEV-13668 ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE; -ALTER TABLE t1n CHANGE c4 c11 INT, LOCK=NONE; -ALTER TABLE t1n ADD INDEX(c11), LOCK=NONE; --echo ### files in MYSQL_DATA_DIR/test --replace_regex $regexp @@ -500,17 +491,10 @@ ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL; --replace_regex $regexp --list_files $MYSQL_TMP_DIR/alt_dir/test -# FIXME: MDEV-9469 'Incorrect key file' on ALTER TABLE -call mtr.add_suppression("Error: no matching column for .FTS_DOC_ID. in index .ct.--temporary-- of table .test...t1o"); ---error ER_NOT_KEYFILE ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE; -ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, LOCK=NONE; -ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE; -# end of MDEV-9469 FIXME - --echo ### files in MYSQL_DATA_DIR/test --replace_regex $regexp --list_files $MYSQL_DATA_DIR/test @@ -519,11 +503,9 @@ ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE; --list_files $MYSQL_TMP_DIR/alt_dir/test # This would create a hidden FTS_DOC_ID column, which cannot be done online. -# FIXME: MDEV-9469 (enable this) -#--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -#ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, -#LOCK=NONE; -#end of MDEV-9469 FIXME +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, +LOCK=NONE; # This should not show duplicates. SELECT sc.pos FROM information_schema.innodb_sys_columns sc @@ -534,7 +516,6 @@ WHERE st.NAME='test/t1o' AND sc.NAME='FTS_DOC_ID'; --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR SHOW CREATE TABLE t1o; -# FIXME: MDEV-13668 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, DROP INDEX ct, LOCK=NONE; @@ -572,15 +553,9 @@ ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id); --replace_regex $regexp --list_files $MYSQL_TMP_DIR/alt_dir/test -# FIXME: MDEV-9469 'Incorrect key file' on ALTER TABLE ---error ER_NOT_KEYFILE ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ADD FULLTEXT INDEX(ct); -ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, LOCK=NONE; -ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE; -#end of MDEV-9469 FIXME - --echo ### files in MYSQL_DATA_DIR/test --replace_regex $regexp --list_files $MYSQL_DATA_DIR/test -- cgit v1.2.1 From 9534c0451578db5562799e27bc2c7e9d61ec6900 Mon Sep 17 00:00:00 2001 From: Sachin Setiya Date: Wed, 11 Oct 2017 01:08:14 +0530 Subject: Bug Fix Tests in galera_3nodes fails to start because galera_port , sst_port is not defined. --- mysql-test/suite/galera_3nodes/galera_3nodes.cnf | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/galera_3nodes/galera_3nodes.cnf b/mysql-test/suite/galera_3nodes/galera_3nodes.cnf index fe3bcb1e8ff..91aa53ad7b1 100644 --- a/mysql-test/suite/galera_3nodes/galera_3nodes.cnf +++ b/mysql-test/suite/galera_3nodes/galera_3nodes.cnf @@ -14,6 +14,9 @@ wsrep-causal-reads=ON wsrep-sync-wait=15 [mysqld.1] +#galera_port=@OPT.port +#ist_port=@OPT.port +#sst_port=@OPT.port wsrep-cluster-address='gcomm://' wsrep_provider_options='base_port=@mysqld.1.#galera_port;evs.suspect_timeout=PT10S;evs.inactive_timeout=PT30S;evs.install_timeout=PT15S' -- cgit v1.2.1 From b9418ed3332358e7209300739435c5e0aeb5ba70 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= Date: Mon, 9 Oct 2017 13:32:40 +0300 Subject: MDEV-13676: Field "create Procedure" is NULL, even if the the user has role which is the definer. (SHOW CREATE PROCEDURE) During show create procedure we ommited to check the current role, if it is the actual definer of the procedure. In addition, we should support indirectly granted roles to the current role. Implemented a recursive lookup to search the tree of grants if the rolename is present. SQL Standard 2016, Part 5 Section 53 View I_S.ROUTINES selects ROUTINE_BODY and its WHERE clause says that the GRANTEE must be either PUBLIC, or CURRENT_USER or in the ENABLED_ROLES. --- mysql-test/suite/roles/definer.result | 114 +++++++++++++++++++++++++++++++ mysql-test/suite/roles/definer.test | 122 ++++++++++++++++++++++++++++++++++ 2 files changed, 236 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/roles/definer.result b/mysql-test/suite/roles/definer.result index 0010853be78..f11b5565ffe 100644 --- a/mysql-test/suite/roles/definer.result +++ b/mysql-test/suite/roles/definer.result @@ -623,3 +623,117 @@ show grants for utest; Grants for utest GRANT SELECT ON *.* TO 'utest' drop role utest; +# +# MDEV-13676: Field "create Procedure" is NULL, even if the the user +# has role which is the definer. (SHOW CREATE PROCEDURE) +# +create database rtest; +create role r1; +create role r2; +create role r3; +grant all privileges on rtest.* to r1; +create user user1; +grant r1 to user1; +grant r1 to r2; +grant r2 to user1; +grant r3 to user1; +set role r2; +use rtest; +CREATE DEFINER=current_role() PROCEDURE user1_proc() SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END;// +set role r2; +show create procedure user1_proc; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +user1_proc CREATE DEFINER=`r2` PROCEDURE `user1_proc`() + SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END latin1 latin1_swedish_ci latin1_swedish_ci +# +# Currently one can not use as definer any role except CURRENT_ROLE +# +CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END;// +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +set role r1; +CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END;// +show create procedure user1_proc2; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +user1_proc2 CREATE DEFINER=`r1` PROCEDURE `user1_proc2`() + SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END latin1 latin1_swedish_ci latin1_swedish_ci +# +# Test to see if the user can still see the procedure code if the +# role that owns it is granted to him indirectly. +# +set role r2; +show create procedure user1_proc2; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +user1_proc2 CREATE DEFINER=`r1` PROCEDURE `user1_proc2`() + SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END latin1 latin1_swedish_ci latin1_swedish_ci +# +# One should not be able to see the procedure code if the role that owns +# the procedure is not set by the user or is not in the subgraph of the +# currently active role. +# +set role r3; +show create procedure user1_proc2; +ERROR 42000: PROCEDURE user1_proc2 does not exist +use rtest; +# +# Try a few edge cases, with usernames identical to role name; +# +create user user_like_role; +create user foo; +create role user_like_role; +grant select on rtest.* to user_like_role; +grant select on rtest.* to foo; +grant select on rtest.* to user_like_role@'%'; +grant user_like_role to foo; +# +# Here we have a procedure that is owned by user_like_role USER +# We don't want user_like_role ROLE to have access to its code. +# +CREATE DEFINER=`user_like_role`@`%` PROCEDURE sensitive_proc() SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END;// +use rtest; +show create procedure sensitive_proc; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sensitive_proc CREATE DEFINER=`user_like_role`@`%` PROCEDURE `sensitive_proc`() + SQL SECURITY INVOKER +BEGIN +SELECT NOW(), VERSION(); +END latin1 latin1_swedish_ci latin1_swedish_ci +set role user_like_role; +use rtest; +# +# Foo has the set rolename identical to the procedure's definer's username. +# Foo should not have access to this procedure. +# +show create procedure sensitive_proc; +ERROR 42000: PROCEDURE sensitive_proc does not exist +drop role r1; +drop role r2; +drop role r3; +drop role user_like_role; +drop user user1; +drop user foo; +drop user user_like_role; +drop procedure user1_proc; +drop procedure user1_proc2; +drop procedure sensitive_proc; +drop database rtest; diff --git a/mysql-test/suite/roles/definer.test b/mysql-test/suite/roles/definer.test index 3c069105c8c..1a8be78fea8 100644 --- a/mysql-test/suite/roles/definer.test +++ b/mysql-test/suite/roles/definer.test @@ -329,3 +329,125 @@ execute stmt1; show grants for utest; drop role utest; +--echo # +--echo # MDEV-13676: Field "create Procedure" is NULL, even if the the user +--echo # has role which is the definer. (SHOW CREATE PROCEDURE) +--echo # + +create database rtest; +create role r1; +create role r2; +create role r3; +grant all privileges on rtest.* to r1; + +create user user1; +grant r1 to user1; +grant r1 to r2; +grant r2 to user1; +grant r3 to user1; + +connect (user1, localhost,user1,,,,,); +set role r2; +use rtest; + +DELIMITER //; +CREATE DEFINER=current_role() PROCEDURE user1_proc() SQL SECURITY INVOKER + BEGIN + SELECT NOW(), VERSION(); + END;// +DELIMITER ;// + +set role r2; +show create procedure user1_proc; + +--echo # +--echo # Currently one can not use as definer any role except CURRENT_ROLE +--echo # +DELIMITER //; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER + BEGIN + SELECT NOW(), VERSION(); + END;// +DELIMITER ;// + +set role r1; +DELIMITER //; +CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER + BEGIN + SELECT NOW(), VERSION(); + END;// +DELIMITER ;// + +show create procedure user1_proc2; +--echo # +--echo # Test to see if the user can still see the procedure code if the +--echo # role that owns it is granted to him indirectly. +--echo # +set role r2; +show create procedure user1_proc2; + +--echo # +--echo # One should not be able to see the procedure code if the role that owns +--echo # the procedure is not set by the user or is not in the subgraph of the +--echo # currently active role. +--echo # +set role r3; +--error ER_SP_DOES_NOT_EXIST +show create procedure user1_proc2; + +connection default; + +use rtest; + +--echo # +--echo # Try a few edge cases, with usernames identical to role name; +--echo # + +create user user_like_role; +create user foo; +create role user_like_role; +grant select on rtest.* to user_like_role; +grant select on rtest.* to foo; +grant select on rtest.* to user_like_role@'%'; + +grant user_like_role to foo; + +--echo # +--echo # Here we have a procedure that is owned by user_like_role USER +--echo # We don't want user_like_role ROLE to have access to its code. +--echo # +DELIMITER //; +CREATE DEFINER=`user_like_role`@`%` PROCEDURE sensitive_proc() SQL SECURITY INVOKER + BEGIN + SELECT NOW(), VERSION(); + END;// +DELIMITER ;// + +connect (user_like_role, localhost, user_like_role,,,,,); +use rtest; +show create procedure sensitive_proc; + +connect (foo, localhost, foo,,,,,); +set role user_like_role; +use rtest; + +--echo # +--echo # Foo has the set rolename identical to the procedure's definer's username. +--echo # Foo should not have access to this procedure. +--echo # +--error ER_SP_DOES_NOT_EXIST +show create procedure sensitive_proc; + +connection default; +drop role r1; +drop role r2; +drop role r3; +drop role user_like_role; +drop user user1; +drop user foo; +drop user user_like_role; +drop procedure user1_proc; +drop procedure user1_proc2; +drop procedure sensitive_proc; +drop database rtest; -- cgit v1.2.1 From 991b9ee73597ba7287267207b3918e157e346899 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 13 Oct 2017 07:06:09 +0400 Subject: MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 --- mysql-test/r/ctype_ucs.result | 22 +++++++++++ mysql-test/r/ctype_utf32.result | 15 ++++++++ mysql-test/r/ctype_utf8.result | 22 +++++++++++ mysql-test/r/errors.result | 2 +- mysql-test/r/myisam.result | 9 ++++- mysql-test/r/partition_datatype.result | 8 ++-- mysql-test/r/show_check.result | 4 +- mysql-test/r/strict.result | 4 +- mysql-test/r/type_blob.result | 2 +- mysql-test/r/type_varchar.result | 69 ++++++++++++++++++++++++++++++++++ mysql-test/suite/maria/maria.result | 9 ++++- mysql-test/suite/maria/maria.test | 3 +- mysql-test/t/ctype_ucs.test | 16 ++++++++ mysql-test/t/ctype_utf32.test | 13 +++++++ mysql-test/t/ctype_utf8.test | 16 ++++++++ mysql-test/t/myisam.test | 3 +- mysql-test/t/partition_datatype.test | 8 ++-- mysql-test/t/type_varchar.test | 44 ++++++++++++++++++++++ 18 files changed, 253 insertions(+), 16 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index d55d308c694..1bbd3af4af7 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -4375,5 +4375,27 @@ SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)) Warnings: Warning 1292 Truncated incorrect time value: '77760000' # +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +CREATE TABLE t1 (c1 VARCHAR(32766) CHARACTER SET ucs2); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(32766) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(32767) CHARACTER SET ucs2); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(32768) CHARACTER SET ucs2); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result index 9b062f9480f..02683bc66e6 100644 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@ -1283,5 +1283,20 @@ NO_ENGINE_SUBSTITUTION SET sql_mode=DEFAULT; SET NAMES utf8; # +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +CREATE TABLE t1 (c1 VARCHAR(16383) CHARACTER SET utf32); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(16383) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(16384) CHARACTER SET utf32); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 74fed6a3162..fee27ef177f 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -5933,5 +5933,27 @@ Warnings: SET sql_mode=DEFAULT; DROP TABLE t1; # +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +CREATE TABLE t1 (c1 VARCHAR(21844) CHARACTER SET utf8); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(21844) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(21845) CHARACTER SET utf8); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(21846) CHARACTER SET utf8); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result index 23c77d3978c..d71759cb128 100644 --- a/mysql-test/r/errors.result +++ b/mysql-test/r/errors.result @@ -27,7 +27,7 @@ create table t1 (a int(256)); ERROR 42000: Display width out of range for 'a' (max = 255) set sql_mode='traditional'; create table t1 (a varchar(66000)); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead set sql_mode=default; CREATE TABLE t1 (a INT); SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0))); diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 85af643387e..81a2001c28c 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1697,7 +1697,14 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (v varchar(65535)); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` text +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; set storage_engine=MyISAM; set @save_concurrent_insert=@@concurrent_insert; set global concurrent_insert=1; diff --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result index 1151a1cb1fe..879b603c5ad 100644 --- a/mysql-test/r/partition_datatype.result +++ b/mysql-test/r/partition_datatype.result @@ -314,12 +314,14 @@ bbbb drop table t1; create table t1 (a varchar(3070)) partition by key (a); ERROR HY000: The total length of the partitioning fields is too large +create table t1 (a varchar(65532) not null) partition by key (a); +ERROR HY000: The total length of the partitioning fields is too large create table t1 (a varchar(65533)) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +ERROR HY000: A BLOB field is not allowed in partition function create table t1 (a varchar(65534) not null) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +ERROR HY000: A BLOB field is not allowed in partition function create table t1 (a varchar(65535)) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +ERROR HY000: A BLOB field is not allowed in partition function create table t1 (a bit(27), primary key (a)) engine=myisam partition by hash (a) (partition p0, partition p1, partition p2); diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 0e4cf6c6775..8d4c3feb100 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -959,7 +959,7 @@ def information_schema COLUMNS COLUMNS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N def information_schema COLUMNS COLUMNS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 def information_schema COLUMNS COLUMNS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 def information_schema COLUMNS COLUMNS COLUMN_NAME COLUMN_NAME 253 192 1 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589815 0 Y 16 0 33 +def information_schema COLUMNS COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589788 0 Y 16 0 33 def information_schema COLUMNS COLUMNS IS_NULLABLE IS_NULLABLE 253 9 2 N 1 0 33 def information_schema COLUMNS COLUMNS DATA_TYPE DATA_TYPE 253 192 3 N 1 0 33 def information_schema COLUMNS COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 96 0 Y 0 0 33 @@ -984,7 +984,7 @@ def information_schema COLUMNS COLUMNS COLUMN_NAME Field 253 192 1 N 1 0 33 def information_schema COLUMNS COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 def information_schema COLUMNS COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 def information_schema COLUMNS COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_DEFAULT Default 252 589815 0 Y 16 0 33 +def information_schema COLUMNS COLUMNS COLUMN_DEFAULT Default 252 589788 0 Y 16 0 33 def information_schema COLUMNS COLUMNS EXTRA Extra 253 81 0 N 1 0 33 Field Type Null Key Default Extra c int(11) NO PRI NULL diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index da3c658b5a4..a3262ca5caa 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1240,9 +1240,9 @@ Warning 1364 Field 'i' doesn't have a default value DROP TABLE t1; set @@sql_mode='traditional'; create table t1(a varchar(65537)); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead create table t1(a varbinary(65537)); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead set @@sql_mode='traditional'; create table t1(a int, b date not null); alter table t1 modify a bigint unsigned not null; diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index f49b2a7d5ef..9b20ddeb1df 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -37,7 +37,7 @@ ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT CREATE TABLE t2 (a char(256)); ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead CREATE TABLE t1 (a varchar(70000) default "hello"); -ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +ERROR 42000: Column length too big for column 'a' (max = 65532); use BLOB or TEXT instead CREATE TABLE t2 (a blob default "hello"); ERROR 42000: BLOB/TEXT column 'a' can't have a default value drop table if exists t1,t2; diff --git a/mysql-test/r/type_varchar.result b/mysql-test/r/type_varchar.result index 38ed8a47339..0ab13be113e 100644 --- a/mysql-test/r/type_varchar.result +++ b/mysql-test/r/type_varchar.result @@ -510,3 +510,72 @@ SELECT 5 = a FROM t1; Warnings: Warning 1292 Truncated incorrect DOUBLE value: 's ' DROP TABLE t1; +# +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +CREATE TABLE t1 (c1 VARBINARY(65532)); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varbinary(65532) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65533)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65534)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65535)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65536)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumblob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65532)); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(65532) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65533)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65534)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65535)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65536)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 0de42a9e7fe..890f6bbfaf5 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -1586,7 +1586,14 @@ t1 CREATE TABLE `t1` ( ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 drop table t1; create table t1 (v varchar(65535)); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` text +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 +drop table t1; set @save_concurrent_insert=@@concurrent_insert; set global concurrent_insert=1; create table t1 (a int) ROW_FORMAT=FIXED; diff --git a/mysql-test/suite/maria/maria.test b/mysql-test/suite/maria/maria.test index 3fa7d755fe4..b6a3e2784b6 100644 --- a/mysql-test/suite/maria/maria.test +++ b/mysql-test/suite/maria/maria.test @@ -924,8 +924,9 @@ show create table t1; drop table t1; # ARIA specific varchar tests ---error 1118 create table t1 (v varchar(65535)); +show create table t1; +drop table t1; # # Test concurrent insert diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 62890d0cd1b..b3d0be4432f 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -868,6 +868,22 @@ SET NAMES utf8; SELECT SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2)); +--echo # +--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +--echo # + +CREATE TABLE t1 (c1 VARCHAR(32766) CHARACTER SET ucs2); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(32767) CHARACTER SET ucs2); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(32768) CHARACTER SET ucs2); +DESCRIBE t1; +DROP TABLE t1; + --echo # --echo # End of 5.5 tests diff --git a/mysql-test/t/ctype_utf32.test b/mysql-test/t/ctype_utf32.test index 2b3d3b3bdc5..fced2838273 100644 --- a/mysql-test/t/ctype_utf32.test +++ b/mysql-test/t/ctype_utf32.test @@ -890,6 +890,19 @@ SELECT @@sql_mode; SET sql_mode=DEFAULT; SET NAMES utf8; +--echo # +--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +--echo # + +CREATE TABLE t1 (c1 VARCHAR(16383) CHARACTER SET utf32); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(16384) CHARACTER SET utf32); +DESCRIBE t1; +DROP TABLE t1; + + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 75581ede8fa..592e3a3b662 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1695,6 +1695,22 @@ SELECT CHAR(i USING utf8) FROM t1; SET sql_mode=DEFAULT; DROP TABLE t1; +--echo # +--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +--echo # + +CREATE TABLE t1 (c1 VARCHAR(21844) CHARACTER SET utf8); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(21845) CHARACTER SET utf8); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(21846) CHARACTER SET utf8); +DESCRIBE t1; +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 43c12b42bc9..c4bb93b6bfe 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1030,8 +1030,9 @@ show create table t1; drop table t1; # MyISAM specific varchar tests ---error 1118 create table t1 (v varchar(65535)); +show create table t1; +drop table t1; eval set storage_engine=$default; diff --git a/mysql-test/t/partition_datatype.test b/mysql-test/t/partition_datatype.test index a6035fcb592..4ec0232718e 100644 --- a/mysql-test/t/partition_datatype.test +++ b/mysql-test/t/partition_datatype.test @@ -217,11 +217,13 @@ select * from t1 where a = 'bbbb'; drop table t1; -- error ER_PARTITION_FIELDS_TOO_LONG create table t1 (a varchar(3070)) partition by key (a); --- error ER_TOO_BIG_ROWSIZE +-- error ER_PARTITION_FIELDS_TOO_LONG +create table t1 (a varchar(65532) not null) partition by key (a); +-- error ER_BLOB_FIELD_IN_PART_FUNC_ERROR create table t1 (a varchar(65533)) partition by key (a); --- error ER_TOO_BIG_ROWSIZE +-- error ER_BLOB_FIELD_IN_PART_FUNC_ERROR create table t1 (a varchar(65534) not null) partition by key (a); --- error ER_TOO_BIG_ROWSIZE +-- error ER_BLOB_FIELD_IN_PART_FUNC_ERROR create table t1 (a varchar(65535)) partition by key (a); # diff --git a/mysql-test/t/type_varchar.test b/mysql-test/t/type_varchar.test index 33b84266118..ed8218208c3 100644 --- a/mysql-test/t/type_varchar.test +++ b/mysql-test/t/type_varchar.test @@ -217,3 +217,47 @@ CREATE TABLE t1 (a CHAR(16)); INSERT INTO t1 VALUES ('5'), ('s'), (''); SELECT 5 = a FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +--echo # + +CREATE TABLE t1 (c1 VARBINARY(65532)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARBINARY(65533)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARBINARY(65534)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARBINARY(65535)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARBINARY(65536)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(65532)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(65533)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(65534)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(65535)); +DESCRIBE t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 VARCHAR(65536)); +DESCRIBE t1; +DROP TABLE t1; -- cgit v1.2.1 From a4868c3509772da1666eb3d492515e7d39f8834d Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Mon, 19 Dec 2016 22:03:28 +0100 Subject: MDEV-9208: Function->Function->View = Mysqld segfault (Server crashes in Dependency_marker::visit_field on 2nd execution with merged subquery) Prevent crossing name resolution border in finding item tables. --- mysql-test/r/ps.result | 30 ++++++++++++++++++++++++++++++ mysql-test/t/ps.test | 26 ++++++++++++++++++++++++++ 2 files changed, 56 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index f954583a097..e2c0d6567ac 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -4173,4 +4173,34 @@ Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 deallocate prepare stmt2; drop table t1; +# +# MDEV-9208: Function->Function->View = Mysqld segfault +# (Server crashes in Dependency_marker::visit_field on 2nd +# execution with merged subquery) +# +CREATE TABLE t1 (i1 INT); +insert into t1 values(1),(2); +CREATE TABLE t2 (i2 INT); +insert into t2 values(1),(2); +prepare stmt from " + select 1 from ( + select + if (i1<0, 0, 0) as f1, + (select f1) as f2 + from t1, t2 + ) sq +"; +execute stmt; +1 +1 +1 +1 +1 +execute stmt; +1 +1 +1 +1 +1 +drop table t1,t2; # End of 5.5 tests diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index cfd810fd625..dac0bbd4d29 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3714,4 +3714,30 @@ deallocate prepare stmt2; drop table t1; +--echo # +--echo # MDEV-9208: Function->Function->View = Mysqld segfault +--echo # (Server crashes in Dependency_marker::visit_field on 2nd +--echo # execution with merged subquery) +--echo # + +CREATE TABLE t1 (i1 INT); +insert into t1 values(1),(2); + +CREATE TABLE t2 (i2 INT); +insert into t2 values(1),(2); + +prepare stmt from " + select 1 from ( + select + if (i1<0, 0, 0) as f1, + (select f1) as f2 + from t1, t2 + ) sq +"; + +execute stmt; +execute stmt; + +drop table t1,t2; + --echo # End of 5.5 tests -- cgit v1.2.1 From 2bab29ebba7a641d43a98737fd1c160971357cd4 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 13 Oct 2017 07:24:35 -0700 Subject: Fixed the bug mdev-13135. For each SELECT the list sj_nests is built by the function simplify_joins() when scanning different join nests. This function may be called several times for the same join nest. That's why before adding a new member to sj_nests it is necessary to check if it's already in the list. The code of simplify_joins() lacked this check and as a result it could cause memory overwright for some queries. --- mysql-test/r/subselect_mat_cost_bugs.result | 17 +++++++++++++++++ mysql-test/t/subselect_mat_cost_bugs.test | 19 +++++++++++++++++++ 2 files changed, 36 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 57b0526c6a3..df6b543bab8 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -502,3 +502,20 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index idx idx 5 NULL 5 Using where; Using index 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1; +# +# MDEV-13135: subquery with ON expression subject to +# semi-join optimizations +# +CREATE TABLE t1 (a INT); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a AS v_a FROM t1; +INSERT INTO t1 VALUES (1),(3); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1 WHERE a NOT IN ( +SELECT b FROM t2 INNER JOIN v1 ON (b IN ( SELECT a FROM t1 )) +WHERE v_a = b +); +a +1 +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 35f2b9588fe..67af6e3a54a 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -522,4 +522,23 @@ select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; drop table t1; +--echo # +--echo # MDEV-13135: subquery with ON expression subject to +--echo # semi-join optimizations +--echo # + +CREATE TABLE t1 (a INT); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a AS v_a FROM t1; +INSERT INTO t1 VALUES (1),(3); + +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (3),(4); + +SELECT * FROM t1 WHERE a NOT IN ( + SELECT b FROM t2 INNER JOIN v1 ON (b IN ( SELECT a FROM t1 )) + WHERE v_a = b +); + +DROP VIEW v1; +DROP TABLE t1,t2; -- cgit v1.2.1 From 235b68299bc112f9cb7be97af8d01bf904919a6b Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Sat, 18 Feb 2017 17:47:31 +0100 Subject: MDEV-9619: Assertion `null_ref_table' failed in virtual table_map Item_direct_view_ref::used_tables() const on 2nd execution of PS Refer left expression indirectly in case it changes from execution to execution. --- mysql-test/r/ps.result | 113 +++++++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/ps.test | 103 ++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 216 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index e2c0d6567ac..3fed0a5b0d1 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -4203,4 +4203,117 @@ execute stmt; 1 1 drop table t1,t2; +# +# MDEV-9619: Assertion `null_ref_table' failed in virtual +# table_map Item_direct_view_ref::used_tables() const on 2nd +# execution of PS +# +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); +PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; +EXECUTE stmt; +f1 +EXECUTE stmt; +f1 +insert into t1 values ('c'); +EXECUTE stmt; +f1 +c +EXECUTE stmt; +f1 +c +deallocate prepare stmt; +drop view v1; +drop table t1,t2; +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); +PREPARE stmt FROM "SELECT * FROM v1 WHERE (f1,f1) = SOME ( SELECT f2,f2 FROM t2 )"; +EXECUTE stmt; +f1 +EXECUTE stmt; +f1 +insert into t1 values ('c'); +EXECUTE stmt; +f1 +c +EXECUTE stmt; +f1 +c +deallocate prepare stmt; +drop view v1; +drop table t1,t2; +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 (111,table3.column3) IN ( SELECT 111,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; +create table t1 (a int, b int, c int); +create table t2 (x int, y int, z int); +create table t3 as select * from t1; +insert into t1 values (1,2,3),(4,5,6),(100,200,300),(400,500,600); +insert into t2 values (1,2,3),(7,8,9),(100,200,300),(400,500,600); +insert into t3 values (1,2,3),(11,12,13),(100,0,0),(400,500,600); +set @optimizer_switch_save=@@optimizer_switch; +set @join_cache_level_save=@@join_cache_level; +set optimizer_switch='materialization=off'; +set join_cache_level=0; +select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z); +a b c +1 2 3 +400 500 600 +prepare stmt from "select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z)"; +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +create view v1 as select * from t1; +create view v2 as select * from t2; +create view v3 as select * from t3; +select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z); +a b c +1 2 3 +400 500 600 +prepare stmt from "select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z)"; +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +EXECUTE stmt; +a b c +1 2 3 +400 500 600 +set optimizer_switch=@optimizer_switch_save; +set join_cache_level=@join_cache_level_save; +deallocate prepare stmt; +drop view v1,v2,v3; +drop table t1,t2,t3; # End of 5.5 tests diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index dac0bbd4d29..4431f722ae0 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3740,4 +3740,107 @@ execute stmt; drop table t1,t2; +--echo # +--echo # MDEV-9619: Assertion `null_ref_table' failed in virtual +--echo # table_map Item_direct_view_ref::used_tables() const on 2nd +--echo # execution of PS +--echo # + +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); + +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); + +PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; +EXECUTE stmt; +EXECUTE stmt; +insert into t1 values ('c'); +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; +drop view v1; +drop table t1,t2; + +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); + +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); + +PREPARE stmt FROM "SELECT * FROM v1 WHERE (f1,f1) = SOME ( SELECT f2,f2 FROM t2 )"; +EXECUTE stmt; +EXECUTE stmt; +insert into t1 values ('c'); +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; +drop view v1; +drop table t1,t2; + + + +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 (111,table3.column3) IN ( SELECT 111,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; + +create table t1 (a int, b int, c int); +create table t2 (x int, y int, z int); +create table t3 as select * from t1; +insert into t1 values (1,2,3),(4,5,6),(100,200,300),(400,500,600); +insert into t2 values (1,2,3),(7,8,9),(100,200,300),(400,500,600); +insert into t3 values (1,2,3),(11,12,13),(100,0,0),(400,500,600); + + +set @optimizer_switch_save=@@optimizer_switch; +set @join_cache_level_save=@@join_cache_level; +set optimizer_switch='materialization=off'; +set join_cache_level=0; +select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z); +prepare stmt from "select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z)"; +EXECUTE stmt; +EXECUTE stmt; + +create view v1 as select * from t1; +create view v2 as select * from t2; +create view v3 as select * from t3; +select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z); +prepare stmt from "select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z)"; +EXECUTE stmt; +EXECUTE stmt; +set optimizer_switch=@optimizer_switch_save; +set join_cache_level=@join_cache_level_save; + +deallocate prepare stmt; +drop view v1,v2,v3; +drop table t1,t2,t3; + --echo # End of 5.5 tests -- cgit v1.2.1 From 98cd0ec536915b25a841ffc227285b15f35acef7 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Sat, 14 Oct 2017 19:43:16 +0400 Subject: MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error --- .../sys_vars/inc/explicit_defaults_for_timestamp.inc | 13 +++++++++++++ .../sys_vars/r/explicit_defaults_for_timestamp_off.result | 13 +++++++++++++ .../sys_vars/r/explicit_defaults_for_timestamp_on.result | 15 +++++++++++++++ 3 files changed, 41 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc b/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc index 4cf3914e60a..1fea4ca5bb9 100644 --- a/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc +++ b/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc @@ -97,3 +97,16 @@ CREATE TABLE t1 (a INT); ALTER TABLE t1 ADD b TIMESTAMP; SHOW CREATE TABLE t1; DROP TABLE t1; + +--echo # +--echo # MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error +--echo # + +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +SET sql_mode='ANSI,NO_ZERO_DATE'; +CREATE TABLE t1 (a TIMESTAMP NOT NULL); +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode=DEFAULT; +SET timestamp=DEFAULT; diff --git a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result index cdf612e6db8..61a4eb8a934 100644 --- a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result +++ b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result @@ -173,3 +173,16 @@ t1 CREATE TABLE `t1` ( `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +# +# MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error +# +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +SET sql_mode='ANSI,NO_ZERO_DATE'; +CREATE TABLE t1 (a TIMESTAMP NOT NULL); +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +a +2001-01-01 10:20:30 +DROP TABLE t1; +SET sql_mode=DEFAULT; +SET timestamp=DEFAULT; diff --git a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result index 1c42da57bfc..fb820dc167d 100644 --- a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result +++ b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result @@ -178,3 +178,18 @@ t1 CREATE TABLE `t1` ( `b` timestamp NULL DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +# +# MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error +# +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +SET sql_mode='ANSI,NO_ZERO_DATE'; +CREATE TABLE t1 (a TIMESTAMP NOT NULL); +INSERT INTO t1 VALUES (); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +SELECT * FROM t1; +a +0000-00-00 00:00:00 +DROP TABLE t1; +SET sql_mode=DEFAULT; +SET timestamp=DEFAULT; -- cgit v1.2.1 From 1eee3a3fb79e809943ff9e37b022660d155ad486 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Mon, 16 Oct 2017 12:06:43 +0300 Subject: MDEV-13051 MySQL#86607 InnoDB crash after failed ADD INDEX and table_definition_cache eviction There are two bugs related to failed ADD INDEX and the InnoDB table cache eviction. dict_table_close(): Try dropping failed ADD INDEX when releasing the last table handle, not when releasing the last-but-one. dict_table_remove_from_cache_low(): Do not invoke row_merge_drop_indexes() after freeing all index metadata. Instead, directly invoke row_merge_drop_indexes_dict() to remove the metadata from the persistent data dictionary and to free the index pages. --- .../innodb/r/table_definition_cache_debug.result | 16 ++++++ .../innodb/t/table_definition_cache_debug.test | 66 ++++++++++++++++++++++ 2 files changed, 82 insertions(+) create mode 100644 mysql-test/suite/innodb/r/table_definition_cache_debug.result create mode 100644 mysql-test/suite/innodb/t/table_definition_cache_debug.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/table_definition_cache_debug.result b/mysql-test/suite/innodb/r/table_definition_cache_debug.result new file mode 100644 index 00000000000..a72d4baad21 --- /dev/null +++ b/mysql-test/suite/innodb/r/table_definition_cache_debug.result @@ -0,0 +1,16 @@ +SET @save_tdc= @@GLOBAL.table_definition_cache; +SET @save_toc= @@GLOBAL.table_open_cache; +SET GLOBAL table_definition_cache= 400; +SET GLOBAL table_open_cache= 1024; +CREATE TABLE to_be_evicted(a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO to_be_evicted VALUES(1,2),(2,1); +SET DEBUG_SYNC = 'row_log_apply_before SIGNAL scanned WAIT_FOR got_duplicate'; +ALTER TABLE to_be_evicted ADD UNIQUE INDEX(b); +SET DEBUG_SYNC = 'now WAIT_FOR scanned'; +BEGIN; +INSERT INTO to_be_evicted VALUES(3, 2); +SET DEBUG_SYNC = 'now SIGNAL got_duplicate'; +ERROR 23000: Duplicate entry '2' for key 'b' +COMMIT; +SET DEBUG_SYNC = RESET; +FLUSH TABLES; diff --git a/mysql-test/suite/innodb/t/table_definition_cache_debug.test b/mysql-test/suite/innodb/t/table_definition_cache_debug.test new file mode 100644 index 00000000000..57d64d6844e --- /dev/null +++ b/mysql-test/suite/innodb/t/table_definition_cache_debug.test @@ -0,0 +1,66 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +SET @save_tdc= @@GLOBAL.table_definition_cache; +SET @save_toc= @@GLOBAL.table_open_cache; + +# InnoDB plugin essentially ignores table_definition_cache size +# and hard-wires it to 400, which also is the minimum allowed value. +SET GLOBAL table_definition_cache= 400; +SET GLOBAL table_open_cache= 1024; + +CREATE TABLE to_be_evicted(a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO to_be_evicted VALUES(1,2),(2,1); + +connect(ddl,localhost,root,,); +SET DEBUG_SYNC = 'row_log_apply_before SIGNAL scanned WAIT_FOR got_duplicate'; +--send +ALTER TABLE to_be_evicted ADD UNIQUE INDEX(b); + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR scanned'; + +# During the ADD UNIQUE INDEX, start a transaction that inserts a duplicate +# and then hogs the table lock, so that the unique index cannot be dropped. +BEGIN; +INSERT INTO to_be_evicted VALUES(3, 2); +SET DEBUG_SYNC = 'now SIGNAL got_duplicate'; + +connection ddl; +--error ER_DUP_ENTRY +reap; + +disconnect ddl; +connection default; +# Release the table lock. +COMMIT; +SET DEBUG_SYNC = RESET; + +# Allow cache eviction. +FLUSH TABLES; +--disable_query_log + +# Pollute the cache with many tables, so that our table will be evicted. +let $N=1000; +let $loop=$N; +while ($loop) +{ + eval CREATE TABLE t_$loop(id INT)ENGINE=InnoDB; + dec $loop; +} + +# Hopefully let InnoDB evict the tables. +sleep 10; + +let $loop=$N; +while ($loop) +{ + eval DROP TABLE t_$loop; + dec $loop; +} + +SET GLOBAL table_definition_cache= @save_tdc; +SET GLOBAL table_open_cache= @save_toc; + +DROP TABLE to_be_evicted; -- cgit v1.2.1 From 9a791c9c8d75116d3d15bebb4df198b122b98f97 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Mon, 16 Oct 2017 13:21:11 +0300 Subject: MDEV-12676 MySQL#78423 InnoDB FTS duplicate key error fts_get_next_doc_id(): Assign the first and subsequent FTS_DOC_ID in the same way: by post-incrementing the cached value. If there is a user-specified FTS_DOC_ID, do not touch the internal sequence. --- .../suite/innodb_fts/r/concurrent_insert.result | 8 ++++++++ mysql-test/suite/innodb_fts/t/concurrent_insert.test | 20 ++++++++++++++++++++ 2 files changed, 28 insertions(+) create mode 100644 mysql-test/suite/innodb_fts/r/concurrent_insert.result create mode 100644 mysql-test/suite/innodb_fts/t/concurrent_insert.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb_fts/r/concurrent_insert.result b/mysql-test/suite/innodb_fts/r/concurrent_insert.result new file mode 100644 index 00000000000..b9798ca1a74 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/concurrent_insert.result @@ -0,0 +1,8 @@ +CREATE TABLE t1(a VARCHAR(5),FULLTEXT KEY(a)) ENGINE=InnoDB; +SET DEBUG_SYNC = 'get_next_FTS_DOC_ID SIGNAL prepared WAIT_FOR race'; +REPLACE INTO t1(a) values('aaa'); +SET DEBUG_SYNC = 'now WAIT_FOR prepared'; +REPLACE INTO t1(a) VALUES('aaa'); +SET DEBUG_SYNC = 'now SIGNAL race'; +SET DEBUG_SYNC = 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/concurrent_insert.test b/mysql-test/suite/innodb_fts/t/concurrent_insert.test new file mode 100644 index 00000000000..e5d61cd8b05 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/concurrent_insert.test @@ -0,0 +1,20 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1(a VARCHAR(5),FULLTEXT KEY(a)) ENGINE=InnoDB; +SET DEBUG_SYNC = 'get_next_FTS_DOC_ID SIGNAL prepared WAIT_FOR race'; +--send +REPLACE INTO t1(a) values('aaa'); + +connect(dml, localhost, root, ,); +SET DEBUG_SYNC = 'now WAIT_FOR prepared'; +REPLACE INTO t1(a) VALUES('aaa'); +SET DEBUG_SYNC = 'now SIGNAL race'; +disconnect dml; + +connection default; +reap; +SET DEBUG_SYNC = 'RESET'; + +DROP TABLE t1; -- cgit v1.2.1 From 421716391b8bafe9af853b1ee3f83d521b69db6e Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sat, 14 Oct 2017 15:03:43 +0200 Subject: MDEV-13912 Can't refer the same column twice in one ALTER TABLE backport ce6c0e584e3 MDEV-8960: Can't refer the same column twice in one ALTER TABLE Problem was that if column was created in alter table when it was refered again it was not tried to find from list of current columns. mysql_prepare_alter_table: There is two cases (1) If alter table adds a new column and then later alter changes the field definition, there was no check from list of new columns, instead an incorrect error was given. (2) If alter table adds a new column and then later alter changes the default, there was no check from list of new columns, instead an incorrect error was given. --- mysql-test/r/alter_table.result | 52 +++++++++++++++++++++++++++++++++++++++++ mysql-test/t/alter_table.test | 41 ++++++++++++++++++++++++++++++++ 2 files changed, 93 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 624cc7afa98..8e61031f8ac 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1340,3 +1340,55 @@ rename table t2 to t1; execute stmt1; deallocate prepare stmt1; drop table t2; +# +# MDEV-8960 Can't refer the same column twice in one ALTER TABLE +# +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` SET DEFAULT 2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL DEFAULT '2' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT, +MODIFY COLUMN `consultant_id` BIGINT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` bigint(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index eade7ba721e..ee9616e233d 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1231,3 +1231,44 @@ execute stmt1; deallocate prepare stmt1; drop table t2; +--echo # +--echo # MDEV-8960 Can't refer the same column twice in one ALTER TABLE +--echo # + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` DROP DEFAULT; + +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` SET DEFAULT 2; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT, +MODIFY COLUMN `consultant_id` BIGINT; +SHOW CREATE TABLE t1; +DROP TABLE t1; -- cgit v1.2.1 From 19a702a85c69d241e360d1d5a040378928a3fdca Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sat, 14 Oct 2017 15:59:54 +0200 Subject: MDEV-14056 DROP TEMPORARY TABLE IF EXISTS causes error 1290 with read_only option if it's a DROP TABLE, we cannot detect whether a table is temporary by looking in thd->temporary_tables - because the table might simply not exist at all. --- mysql-test/r/read_only.result | 3 +++ mysql-test/t/read_only.test | 5 +++++ 2 files changed, 8 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 807dc426696..2d0f9d730fd 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -47,6 +47,9 @@ delete t1 from t1,t3 where t1.a=t3.a; drop table t1; insert into t1 values(1); ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement +drop temporary table if exists t1; +Warnings: +Note 1051 Unknown table 't1' connection default; set global read_only=0; lock table t1 write; diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index a0bd7b49273..eb9bea803c2 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -114,6 +114,11 @@ drop table t1; --error ER_OPTION_PREVENTS_STATEMENT insert into t1 values(1); +# +# MDEV-14056 DROP TEMPORARY TABLE IF EXISTS causes error 1290 with read_only option +# +drop temporary table if exists t1; + # # Bug#11733 COMMITs should not happen if read-only is set # -- cgit v1.2.1 From b036b6b59464524d7dd54a4c9a75b5ee8a14dbe0 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 16 Oct 2017 12:34:17 +0200 Subject: MDEV-13937 Aria engine: Internal Error 160 after partition handling Partition wasn't setting HA_OPTION_PACK_RECORD on ALTER TABLE if the row format was PAGE. (so one bit in the null bitmap was reserved for a deleted bit - see make_empty_rec - and all actual null bits were one off) --- mysql-test/suite/parts/r/partition_alter_maria.result | 18 ++++++++++++++++++ mysql-test/suite/parts/t/partition_alter_maria.test | 18 ++++++++++++++++++ 2 files changed, 36 insertions(+) create mode 100644 mysql-test/suite/parts/r/partition_alter_maria.result create mode 100644 mysql-test/suite/parts/t/partition_alter_maria.test (limited to 'mysql-test') diff --git a/mysql-test/suite/parts/r/partition_alter_maria.result b/mysql-test/suite/parts/r/partition_alter_maria.result new file mode 100644 index 00000000000..6343566e408 --- /dev/null +++ b/mysql-test/suite/parts/r/partition_alter_maria.result @@ -0,0 +1,18 @@ +create table t1 ( +pk bigint not null auto_increment, +dt datetime default null, +unique (pk, dt) +) engine=aria row_format=dynamic +partition by range columns(dt) ( +partition `p20171231` values less than ('2017-12-31'), +partition `p20181231` values less than ('2018-12-31') +); +insert into t1 values (1,'2017-09-28 15:12:00'); +select * from t1; +pk dt +1 2017-09-28 15:12:00 +alter table t1 drop partition p20181231; +select * from t1; +pk dt +1 2017-09-28 15:12:00 +drop table t1; diff --git a/mysql-test/suite/parts/t/partition_alter_maria.test b/mysql-test/suite/parts/t/partition_alter_maria.test new file mode 100644 index 00000000000..db249591158 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter_maria.test @@ -0,0 +1,18 @@ +# +# MDEV-13937 Aria engine: Internal Error 160 after partition handling +# +source include/have_partition.inc; +create table t1 ( + pk bigint not null auto_increment, + dt datetime default null, + unique (pk, dt) +) engine=aria row_format=dynamic + partition by range columns(dt) ( + partition `p20171231` values less than ('2017-12-31'), + partition `p20181231` values less than ('2018-12-31') +); +insert into t1 values (1,'2017-09-28 15:12:00'); +select * from t1; +alter table t1 drop partition p20181231; +select * from t1; +drop table t1; -- cgit v1.2.1 From b000e169562697aa072600695d4f0c0412f94f4f Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 17 Oct 2017 10:57:51 +0200 Subject: Bug#26361149 MYSQL SERVER CRASHES AT: COL IN(IFNULL(CONST, COL), NAME_CONST('NAME', NULL)) based on: commit f7316aa0c9a Author: Ajo Robert Date: Thu Aug 24 17:03:21 2017 +0530 Bug#26361149 MYSQL SERVER CRASHES AT: COL IN(IFNULL(CONST, COL), NAME_CONST('NAME', NULL)) Backport of Bug#19143243 fix. NAME_CONST item can return NULL_ITEM type in case of incorrect arguments. NULL_ITEM has special processing in Item_func_in function. In Item_func_in::fix_length_and_dec an array of possible comparators is created. Since NAME_CONST function has NULL_ITEM type, corresponding array element is empty. Then NAME_CONST is wrapped to ITEM_CACHE. ITEM_CACHE can not return proper type(NULL_ITEM) in Item_func_in::val_int(), so the NULL_ITEM is attempted compared with an empty comparator. The fix is to disable the caching of Item_name_const item. --- mysql-test/r/func_in.result | 6 ++++++ mysql-test/t/func_in.test | 8 ++++++++ 2 files changed, 14 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index fc56660ac62..03ddbf5402b 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -812,3 +812,9 @@ EXECUTE s; 1 DROP TABLE t1; # End of 5.3 tests +create table t1 (a int); +insert t1 values (1),(2),(3); +select * from t1 where 1 in (a, name_const('a', null)); +a +1 +drop table t1; diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 1e695142d90..439f9868ec8 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -606,3 +606,11 @@ EXECUTE s; DROP TABLE t1; --echo # End of 5.3 tests + +# +# Bug#26361149 MYSQL SERVER CRASHES AT: COL IN(IFNULL(CONST, COL), NAME_CONST('NAME', NULL)) +# +create table t1 (a int); +insert t1 values (1),(2),(3); +select * from t1 where 1 in (a, name_const('a', null)); +drop table t1; -- cgit v1.2.1 From dfd010ef906705165d6866377ebd4c937adc7716 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 18 Oct 2017 06:35:11 +0300 Subject: MDEV-14086 Setting innodb_buffer_pool_load_now or innodb_buffer_load_abort will crash if innodb_read_only When MySQL 5.6.10 introduced innodb_read_only mode, it skipped the creation of the InnoDB buffer pool dump/restore subsystem in that mode. Attempts to set the variable innodb_buf_pool_dump_now would have no effect in innodb_read_only mode, but the corresponding condition was forgotten in from the other two update functions. MySQL 5.7.20 would fix the innodb_buffer_pool_load_now, but not innodb_buffer_pool_load_abort. Let us fix both in MariaDB. --- mysql-test/suite/innodb/r/innodb-get-fk.result | 3 +++ mysql-test/suite/innodb/t/innodb-get-fk.test | 5 +++++ 2 files changed, 8 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb-get-fk.result b/mysql-test/suite/innodb/r/innodb-get-fk.result index ee17f262854..aa1bff8f134 100644 --- a/mysql-test/suite/innodb/r/innodb-get-fk.result +++ b/mysql-test/suite/innodb/r/innodb-get-fk.result @@ -40,6 +40,9 @@ crew_role_assigned CREATE TABLE `crew_role_assigned` ( CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This is a comment about tables' +SET GLOBAL innodb_buffer_pool_load_now = ON; +SET GLOBAL innodb_buffer_pool_dump_now = ON; +SET GLOBAL innodb_buffer_pool_load_abort = ON; ALTER TABLE `repro`.`crew_role_assigned` COMMENT = "This is a new comment about tables"; SHOW CREATE TABLE `repro`.`crew_role_assigned`; Table Create Table diff --git a/mysql-test/suite/innodb/t/innodb-get-fk.test b/mysql-test/suite/innodb/t/innodb-get-fk.test index 339a7968623..46eb7dd0273 100644 --- a/mysql-test/suite/innodb/t/innodb-get-fk.test +++ b/mysql-test/suite/innodb/t/innodb-get-fk.test @@ -40,6 +40,11 @@ CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `rep ALTER TABLE `repro`.`crew_role_assigned` COMMENT = 'innodb_read_only'; SHOW CREATE TABLE `repro`.`crew_role_assigned`; +# These should be ignored in innodb_read_only mode. +SET GLOBAL innodb_buffer_pool_load_now = ON; +SET GLOBAL innodb_buffer_pool_dump_now = ON; +SET GLOBAL innodb_buffer_pool_load_abort = ON; + -- let $restart_parameters= -- source include/restart_mysqld.inc -- cgit v1.2.1 From 981747956349c23f2e1aec41232a82e4b7e822b3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 18 Oct 2017 08:25:04 +0300 Subject: Add a test of LIMIT with FULLTEXT INDEX In MariaDB, InnoDB fulltext search does not currently truncate results based on LIMIT. In MySQL 5.7, it does. --- mysql-test/suite/innodb_fts/r/fulltext.result | 36 ++++++++++++++++++++++++++- mysql-test/suite/innodb_fts/t/fulltext.test | 21 +++++++++++++--- 2 files changed, 52 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index 1cdce29cdf9..cc6cffb7bb6 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2,t3; CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB; INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Full-text indexes', 'are called collections'), @@ -637,3 +636,38 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests +create table t ( +FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t) +) ENGINE=InnoDB; +INSERT INTO t values (1, 'foo bar'), (2, 'foo bar'), (3, 'foo'); +SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) +LIMIT 0; +FTS_DOC_ID t +SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) +LIMIT 1; +FTS_DOC_ID t +1 foo bar +SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) +LIMIT 2; +FTS_DOC_ID t +1 foo bar +2 foo bar +SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) +LIMIT 3; +FTS_DOC_ID t +1 foo bar +2 foo bar +3 foo +SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) +LIMIT 4; +FTS_DOC_ID t +1 foo bar +2 foo bar +3 foo +SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) +LIMIT 5; +FTS_DOC_ID t +1 foo bar +2 foo bar +3 foo +DROP TABLE t; diff --git a/mysql-test/suite/innodb_fts/t/fulltext.test b/mysql-test/suite/innodb_fts/t/fulltext.test index 90d5d5c71e0..da445cfade7 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext.test +++ b/mysql-test/suite/innodb_fts/t/fulltext.test @@ -4,10 +4,6 @@ --source include/have_innodb.inc ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings - CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB; INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Full-text indexes', 'are called collections'), @@ -679,3 +675,20 @@ DEALLOCATE PREPARE stmt; DROP TABLE t1; --echo End of 5.1 tests + +create table t ( + FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t) +) ENGINE=InnoDB; + +INSERT INTO t values (1, 'foo bar'), (2, 'foo bar'), (3, 'foo'); +let $limit=0; +let $N=6; +while ($N) +{ + eval SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) + LIMIT $limit; + inc $limit; + dec $N; +} + +DROP TABLE t; -- cgit v1.2.1 From 30e89acd95138d8ebfad8f3d392c41b6f2b08790 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 18 Oct 2017 09:52:19 +0300 Subject: Import, adapt and extend a test from Oracle --- mysql-test/suite/innodb_fts/r/fulltext.result | 20 ++++++++++++++++++++ mysql-test/suite/innodb_fts/t/fulltext.test | 25 +++++++++++++++++++++++++ 2 files changed, 45 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index cc6cffb7bb6..6802894e44b 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -636,6 +636,26 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests +CREATE TABLE z(a INTEGER) engine=innodb; +CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb; +EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); +ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' +SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); +ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' +EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z; +ERROR 42S22: Unknown column 'b' in 'field list' +SELECT MATCH(b) AGAINST ('*') FROM z; +ERROR 42S22: Unknown column 'b' in 'field list' +EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z; +ERROR HY000: Can't find FULLTEXT index matching the column list +SELECT MATCH(a) AGAINST ('*') FROM z; +ERROR HY000: Can't find FULLTEXT index matching the column list +EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE q ALL NULL NULL NULL NULL 1 +SELECT MATCH(b) AGAINST ('*') FROM q; +ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' +DROP TABLE z, q; create table t ( FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t) ) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb_fts/t/fulltext.test b/mysql-test/suite/innodb_fts/t/fulltext.test index da445cfade7..663b202265b 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext.test +++ b/mysql-test/suite/innodb_fts/t/fulltext.test @@ -676,6 +676,31 @@ DROP TABLE t1; --echo End of 5.1 tests +# This is an adapted and extended version of an Oracle test for +# Bug#21140111: Explain ... match against: Assertion failed: ret ... +# No bug was repeatable for MariaDB. + +CREATE TABLE z(a INTEGER) engine=innodb; +CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb; + +--error ER_PARSE_ERROR +EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); +--error ER_PARSE_ERROR +SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); +--error ER_BAD_FIELD_ERROR +EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z; +--error ER_BAD_FIELD_ERROR +SELECT MATCH(b) AGAINST ('*') FROM z; +--error ER_FT_MATCHING_KEY_NOT_FOUND +EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z; +--error ER_FT_MATCHING_KEY_NOT_FOUND +SELECT MATCH(a) AGAINST ('*') FROM z; +EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q; +--error ER_PARSE_ERROR +SELECT MATCH(b) AGAINST ('*') FROM q; + +DROP TABLE z, q; + create table t ( FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t) ) ENGINE=InnoDB; -- cgit v1.2.1