diff options
| author | Nirbhay Choubey <nirbhay@mariadb.com> | 2015-10-13 14:42:36 -0400 |
|---|---|---|
| committer | Nirbhay Choubey <nirbhay@mariadb.com> | 2015-10-13 14:42:36 -0400 |
| commit | fd68a7dac625be8ba64745474d85f689ddeb9ea0 (patch) | |
| tree | 96c409790c0a8213ce3432befe8e20e4137231ae /mysql-test | |
| parent | 13615c5e18eed62fa2dee80402dfebe3e74053c4 (diff) | |
| parent | 16c4b3c68b06653592a9500050ad977a38f4ebae (diff) | |
| download | mariadb-git-fd68a7dac625be8ba64745474d85f689ddeb9ea0.tar.gz | |
Merge tag 'mariadb-5.5.46' into 5.5-galera
Diffstat (limited to 'mysql-test')
57 files changed, 1431 insertions, 182 deletions
diff --git a/mysql-test/disabled.def b/mysql-test/disabled.def index 6dc3066e374..310890605ff 100644 --- a/mysql-test/disabled.def +++ b/mysql-test/disabled.def @@ -11,9 +11,8 @@ ############################################################################## tablespace : disabled in MariaDB (no TABLESPACE table attribute) events_time_zone : Test is not predictable as it depends on precise timing. -lowercase_table3 : Bug#11762269 2010-06-30 alik main.lowercase_table3 on Mac OSX read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exists -archive-big : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists mysql_embedded : Bug#12561297 2011-05-14 Anitha Dependent on PB2 changes - eventum#41836 file_contents : MDEV-6526 these files are not installed anymore +lowercase_fs_on : lower_case_table_names=0 is not an error until 10.1 diff --git a/mysql-test/include/restart_mysqld.inc b/mysql-test/include/restart_mysqld.inc index 7cb9c7994d8..ed0fe64a547 100644 --- a/mysql-test/include/restart_mysqld.inc +++ b/mysql-test/include/restart_mysqld.inc @@ -1,3 +1,8 @@ +# ==== Usage ==== +# +# [--let $shutdown_timeout= 30] +# [--let $allow_rpl_inited= 1] +# --source include/restart_mysqld.inc if ($rpl_inited) { @@ -7,6 +12,16 @@ if ($rpl_inited) } } +--let $server_shutdown_timeout= 10 +if ($shutdown_timeout) +{ + --let $server_shutdown_timeout= $shutdown_timeout +} +if ($shutdown_timeout == 0) +{ + --let $server_shutdown_timeout= 0 +} + # Write file to make mysql-test-run.pl expect the "crash", but don't start # it until it's told to --let $_server_id= `SELECT @@server_id` @@ -15,7 +30,7 @@ if ($rpl_inited) # Send shutdown to the connected server and give # it 10 seconds to die before zapping it -shutdown_server 10; +shutdown_server $server_shutdown_timeout; # Write file to make mysql-test-run.pl start up the server again --exec echo "restart" > $_expect_file_name diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index c7f548ae2bc..61f63cc7253 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -234,3 +234,20 @@ SELECT if(1, NULL, (SELECT min('hello'))); if(1, NULL, (SELECT min('hello'))) NULL End of 5.2 tests +# +# MDEV-8663: IF Statement returns multiple values erroneously +# (or Assertion `!null_value' failed in Item::send(Protocol*, String*) +# +CREATE TABLE `t1` ( +`datas` VARCHAR(25) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 't1' +INSERT INTO `t1` VALUES ('1,2'), ('2,3'), ('3,4'); +SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `t1`; +First Second Third +1.5 1 2 +2.0 1 2 +NULL 1 2 +drop table t1; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index d654dbccb0e..a121bd324ee 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -374,5 +374,203 @@ NULL Warnings: Warning 1411 Incorrect timeout value: '-1' for function get_lock # +# MDEV-8624 MariaDB hangs on query with many logical condition +# +CREATE TABLE `t1` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`submitdate` datetime DEFAULT NULL, +`lastpage` int(11) DEFAULT NULL, +`startlanguage` varchar(20) COLLATE utf8_unicode_ci NOT NULL, +`token` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL, +`datestamp` datetime NOT NULL, +`startdate` datetime NOT NULL, +`ipaddr` text COLLATE utf8_unicode_ci, +`refurl` text COLLATE utf8_unicode_ci, +`57813X540X1723` text COLLATE utf8_unicode_ci, +`57813X540X1724` text COLLATE utf8_unicode_ci, +`57813X540X1725` text COLLATE utf8_unicode_ci, +`57813X540X1726` double DEFAULT NULL, +`57813X540X1909` double DEFAULT NULL, +`57813X541X17271` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17272` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17273` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17274` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17275` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17276` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X541X17281` text COLLATE utf8_unicode_ci, +`57813X541X17282` text COLLATE utf8_unicode_ci, +`57813X541X17283` text COLLATE utf8_unicode_ci, +`57813X541X17284` text COLLATE utf8_unicode_ci, +`57813X541X17285` text COLLATE utf8_unicode_ci, +`57813X541X17286` text COLLATE utf8_unicode_ci, +`57813X542X18131` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18132` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18133` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18134` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18135` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18136` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18137` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18138` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18139` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X542X18141` text COLLATE utf8_unicode_ci, +`57813X542X18142` text COLLATE utf8_unicode_ci, +`57813X542X18143` text COLLATE utf8_unicode_ci, +`57813X542X18144` text COLLATE utf8_unicode_ci, +`57813X542X18145` text COLLATE utf8_unicode_ci, +`57813X542X18146` text COLLATE utf8_unicode_ci, +`57813X542X18147` text COLLATE utf8_unicode_ci, +`57813X542X18148` text COLLATE utf8_unicode_ci, +`57813X542X18149` text COLLATE utf8_unicode_ci, +`57813X543X18451` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18452` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18453` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18454` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18455` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18456` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X543X18461` text COLLATE utf8_unicode_ci, +`57813X543X18462` text COLLATE utf8_unicode_ci, +`57813X543X18463` text COLLATE utf8_unicode_ci, +`57813X543X18464` text COLLATE utf8_unicode_ci, +`57813X543X18465` text COLLATE utf8_unicode_ci, +`57813X543X18466` text COLLATE utf8_unicode_ci, +`57813X544X18711` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18712` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18713` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18714` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18715` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18716` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18717` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18718` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X544X18721` text COLLATE utf8_unicode_ci, +`57813X544X18722` text COLLATE utf8_unicode_ci, +`57813X544X18723` text COLLATE utf8_unicode_ci, +`57813X544X18724` text COLLATE utf8_unicode_ci, +`57813X544X18725` text COLLATE utf8_unicode_ci, +`57813X544X18726` text COLLATE utf8_unicode_ci, +`57813X544X18727` text COLLATE utf8_unicode_ci, +`57813X544X18728` text COLLATE utf8_unicode_ci, +`57813X546X1902` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X546X1903` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X546X1904` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, +`57813X545X1901` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `lime_survey_57813_idx` (`token`), +KEY `57813X540X1723` (`57813X540X1723`(100)), +KEY `57813X540X1724` (`57813X540X1724`(100)), +KEY `57813X540X1726` (`57813X540X1726`), +KEY `57813X540X1725` (`57813X540X1725`(100)), +KEY `57813X546X1902` (`57813X546X1902`), +KEY `57813X546X1903` (`57813X546X1903`), +KEY `57813X546X1904` (`57813X546X1904`) +); +SELECT +COUNT(*) as `N`, +ROUND( +( +SUM( +( +( +IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 57813X541X17271, 0 ) + +IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 57813X541X17272, 0 ) + +IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 57813X541X17273, 0 ) + +IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 57813X541X17274, 0 ) + +IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 57813X541X17275, 0 ) + +IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 57813X541X17276, 0 ) + +IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 57813X542X18131, 0 ) + +IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 57813X542X18132, 0 ) + +IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 57813X542X18133, 0 ) + +IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 57813X542X18134, 0 ) + +IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 57813X542X18135, 0 ) + +IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 57813X542X18136, 0 ) + +IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 57813X542X18137, 0 ) + +IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 57813X542X18138, 0 ) + +IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 57813X542X18139, 0 ) + +IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 57813X543X18451, 0 ) + +IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 57813X543X18452, 0 ) + +IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 57813X543X18453, 0 ) + +IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 57813X543X18454, 0 ) + +IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 57813X543X18455, 0 ) + +IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 57813X543X18456, 0 ) + +IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 57813X544X18711, 0 ) + +IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 57813X544X18712, 0 ) + +IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 57813X544X18713, 0 ) + +IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 57813X544X18714, 0 ) + +IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 57813X544X18715, 0 ) + +IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 57813X544X18716, 0 ) + +IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 57813X544X18717, 0 ) + +IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 57813X544X18718, 0 ) +) +/ +( +IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 1, 0 ) + +IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 1, 0 ) + +IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 1, 0 ) + +IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 1, 0 ) + +IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 1, 0 ) + +IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 1, 0 ) + +IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 1, 0 ) + +IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 1, 0 ) + +IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 1, 0 ) + +IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 1, 0 ) + +IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 1, 0 ) + +IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 1, 0 ) + +IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 1, 0 ) + +IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 1, 0 ) + +IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 1, 0 ) + +IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 1, 0 ) + +IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 1, 0 ) + +IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 1, 0 ) + +IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 1, 0 ) + +IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 1, 0 ) + +IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 1, 0 ) + +IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 1, 0 ) + +IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 1, 0 ) + +IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 1, 0 ) + +IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 1, 0 ) + +IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 1, 0 ) + +IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 1, 0 ) + +IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 1, 0 ) + +IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 1, 0 ) +) +) +) +/ COUNT(*) ), 4) as `AVG` +FROM `t1` +WHERE `submitdate` IS NOT NULL +AND ( +( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99' ) OR +( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99' ) OR +( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99' ) OR +( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99' ) OR +( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99' ) OR +( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99' ) OR +( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99' ) OR +( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99' ) OR +( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99' ) OR +( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99' ) OR +( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99' ) OR +( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99' ) OR +( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99' ) OR +( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99' ) OR +( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99' ) OR +( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99' ) OR +( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99' ) OR +( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99' ) OR +( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99' ) OR +( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99' ) OR +( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99' ) OR +( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99' ) OR +( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99' ) OR +( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99' ) OR +( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99' ) OR +( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99' ) OR +( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99' ) OR +( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99' ) OR +( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99' ) ) +AND 57813X540X1723 = 'Test'; +N AVG +0 NULL +drop table t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/information_schema2.result b/mysql-test/r/information_schema2.result index 3f7f3ecd4e1..7e9bdd7088f 100644 --- a/mysql-test/r/information_schema2.result +++ b/mysql-test/r/information_schema2.result @@ -6,15 +6,15 @@ select variable_name from information_schema.session_variables where variable_na (select variable_name from information_schema.session_variables where variable_name = 'basedir'); variable_name BASEDIR -create table t1 (a char); -insert t1 values ('a'),('t'),('z'); -flush status; -select a, exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) from t1; -a exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) -a 0 -t 1 -z 0 -show status like 'created_tmp_tables'; -Variable_name Value -Created_tmp_tables 38 -drop table t1; +create table t1 (x int); +create table t2 (x int); +create table t3 (x int); +create table t4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ; +delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); +select * from t4; +table_name +t1 +t2 +t3 +t4 +drop table t1, t2, t3, t4; diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result index 7742f456252..1128caf9122 100644 --- a/mysql-test/r/loadxml.result +++ b/mysql-test/r/loadxml.result @@ -93,3 +93,30 @@ a b 216 !&bb b; 3 !b3 DROP TABLE t1; +# +# Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS +# +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4)); +LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3, col4; +col1 col2 col3 col4 +0bc def ghi jkl +1no NULL pqr stu +2BC DEF GHI JKL +3NO NULL PQR STU +4bc def ghi jkl +5no pqr stu vwx +6BC DEF NULL JKL +7NO PQR STU VWX +8bc def ghi NULL +9kl NULL mno pqr +ABC DEF NULL JKL +MNO NULL STU VWX +DROP TABLE t1; +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER); +LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3; +col1 col2 col3 +ABC DEF NULL +GHI NULL 123 +DROP TABLE t1; diff --git a/mysql-test/r/lowercase_fs_on.result b/mysql-test/r/lowercase_fs_on.result new file mode 100644 index 00000000000..a090f46cfbf --- /dev/null +++ b/mysql-test/r/lowercase_fs_on.result @@ -0,0 +1,3 @@ +# +# Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS +# diff --git a/mysql-test/r/lowercase_table3.result b/mysql-test/r/lowercase_table3.result deleted file mode 100644 index 22e80aaeb26..00000000000 --- a/mysql-test/r/lowercase_table3.result +++ /dev/null @@ -1,11 +0,0 @@ -call mtr.add_suppression("Cannot find or open table test/BUG29839 from"); -DROP TABLE IF EXISTS t1,T1; -CREATE TABLE t1 (a INT); -SELECT * FROM T1; -a -FLUSH TABLES; -DROP TABLE t1; -CREATE TABLE bug29839 (a INT) ENGINE=INNODB; -SELECT * FROM BUG29839; -ERROR 42S02: Table 'test.BUG29839' doesn't exist -DROP TABLE bug29839; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 49fd19927fc..43601f4a7c6 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -4292,7 +4292,6 @@ Abernathy aberrant aberration drop table words; -mysqlimport: Error: 1146, Table 'test.words' doesn't exist, when using table: words drop table t1; drop table t2; drop table words2; @@ -5281,6 +5280,34 @@ drop table t1, t2; # # End of 5.1 tests # +# +# Bug #20772273 : MYSQLIMPORT --USE-THREADS DOESN'T USE MULTIPLE THREADS +# +CREATE DATABASE db_20772273; +USE db_20772273; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (3), (4); +SELECT * FROM t1; +a +1 +2 +SELECT * FROM t2; +a +3 +4 +SELECT * FROM t1; +a +1 +2 +SELECT * FROM t2; +a +3 +4 +DROP TABLE t1; +DROP TABLE t2; +DROP DATABASE db_20772273; Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index 0fe1034d447..0c15ded0d73 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -1089,7 +1089,7 @@ partition by key (a) subpartition by hash (sin(a+b)) (partition x1 (subpartition x11, subpartition x12), partition x2 (subpartition x21, subpartition x22)); -ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning +ERROR HY000: This partition function is not allowed select load_file('$MYSQLD_DATADIR/test/t1.par'); load_file('$MYSQLD_DATADIR/test/t1.par') NULL diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 4afde93925d..ba52fd90da6 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -81,7 +81,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect parameters to procedure 'ANALYSE' +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1))' at line 1 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -3590,7 +3590,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7015,3 +7015,69 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 9df216da3cb..d8d5c4ef604 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -88,7 +88,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect parameters to procedure 'ANALYSE' +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1))' at line 1 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -3594,7 +3594,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7012,6 +7012,72 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 7b93f277274..78908bc61db 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -84,7 +84,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect parameters to procedure 'ANALYSE' +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1))' at line 1 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -3590,7 +3590,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7010,4 +7010,70 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index b6d3a89ea7a..d1de4b8f370 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -87,7 +87,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect parameters to procedure 'ANALYSE' +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1))' at line 1 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -3596,7 +3596,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7021,6 +7021,72 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index d51d211e71d..524a5ddc1a6 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -84,7 +84,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect parameters to procedure 'ANALYSE' +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1))' at line 1 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -3590,7 +3590,7 @@ delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); -ERROR 42S22: Unknown column 'no_such_column' in 'where clause' +ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); @@ -7010,5 +7010,71 @@ select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; +# +# MDEV-7565: Server crash with Signal 6 (part 2) +# +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +ControlRev +NULL +# +# MDEV-7445:Server crash with Signal 6 +# +CREATE PROCEDURE procedure2() +BEGIN +Select +(Select Sum(`TestCase`.Revenue) From mysql.slow_log E +Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) +) As `ControlRev` + From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; +END | +call procedure2(); +ControlRev +NULL +call procedure2(); +ControlRev +NULL +drop procedure procedure2; +SELECT +(SELECT user FROM mysql.user +WHERE h.host in (SELECT host FROM mysql.user) +) AS sq +FROM mysql.host h GROUP BY h.host; +sq +# +# MDEV-7846:Server crashes in Item_subselect::fix +#_fields or fails with Thread stack overrun +# +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; +EXECUTE stmt; +sq +NULL +EXECUTE stmt; +sq +NULL +deallocate prepare stmt; +drop table t1,t2,t3,t4; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 3f3a3ac07df..3ea58553653 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -561,6 +561,21 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function DROP VIEW v1; DROP FUNCTION f1; DROP TABLE t1; +# Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT +# RESULTS +CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=INNODB; +INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string'); +UPDATE t1 SET b = a, a = 'inject'; +SELECT a, b FROM t1; +a b +inject start trail +UPDATE t1 SET b = c, c = 'inject'; +SELECT c, b FROM t1; +c b +inject even longer string +DROP TABLE t1; +# +# MDEV-3948 Assertion `records_are_comparable(table)' fails in compare_record(const TABLE*) on UPDATE with simple AND condition, index_merge+index_merge_intersection, InnoDB # # Verify that UPDATE does the same number of handler_update # operations, no matter if there is ORDER BY or not. diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 431e8d2d862..57065d74274 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4830,6 +4830,41 @@ View Create View character_set_client collation_connection v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `col2`) union all (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci DROP VIEW v1, v2, v3, v4, v5; # +# BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, +# IS REJECTED +# Without the patch, reports an error. +CREATE VIEW v1 (fld1, fld2) AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +# The column names are explicitly specified and not duplicates, hence +# succeeds. +CREATE VIEW v2 (fld1, fld2) AS +SELECT 1 AS a, 2 AS a +UNION ALL +SELECT 1 AS a, 1 AS a; +# The column name in the first SELECT are not duplicates, hence succeeds. +CREATE VIEW v3 AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +# Should report an error, since the explicitly specified column names are +# duplicates. +CREATE VIEW v4 (fld1, fld1) AS +SELECT 1 AS a, 2 AS b +UNION ALL +SELECT 1 AS a, 1 AS a; +ERROR 42S21: Duplicate column name 'fld1' +# Should report an error, since duplicate column name is specified in the +# First SELECT. +CREATE VIEW v4 AS +SELECT 1 AS a, 2 AS a +UNION ALL +SELECT 1 AS a, 1 AS a; +ERROR 42S21: Duplicate column name 'a' +# Cleanup +DROP VIEW v1, v2, v3; +# # lp:833600 Wrong result with view + outer join + uncorrelated subquery (non-semijoin) # CREATE TABLE t1 ( a int, b int ); diff --git a/mysql-test/std_data/bug16171518_1.dat b/mysql-test/std_data/bug16171518_1.dat new file mode 100644 index 00000000000..b65b9359ce1 --- /dev/null +++ b/mysql-test/std_data/bug16171518_1.dat @@ -0,0 +1,59 @@ +<test_rows> + <row> + <col1>0bc</col1> + <col2>def</col2> + <col3>ghi</col3> + <col4>jkl</col4> + </row> + <row> + <col1>1no</col1> + <col2/> + <col3>pqr</col3> + <col4>stu</col4> + </row> + + <row> + <col1>2BC</col1> + <col2>DEF</col2> + <col3>GHI</col3> + <col4>JKL</col4> + </row> + <row> + <col1>3NO</col1> + <col2 /> + <col3>PQR</col3> + <col4>STU</col4> + </row> + + <row col1="4bc" col2="def" col3="ghi" col4="jkl"/> + <row col1="5no" col2="pqr" col3="stu" col4="vwx" /> + + <row> + <field name='col1'>6BC</field> + <field name='col2'>DEF</field> + <field name='col3'></field> + <field name='col4'>JKL</field> + </row> + <row> + <field name='col1'>7NO</field> + <field name='col2'>PQR</field> + <field name='col3'>STU</field> + <field name='col4'>VWX</field> + </row> + + <row> + <col1>8bc</col1> + <col2>def</col2> + <col3>ghi</col3> + <col4 /> + </row> + <row> + <col1>9kl</col1> + <col2/> + <col3>mno</col3> + <col4>pqr</col4> + </row> + + <row col1="ABC" col2="DEF" col3="" col4="JKL"/> + <row col1="MNO" col2="" col3="STU" col4="VWX"/> +</test_rows> diff --git a/mysql-test/std_data/bug16171518_2.dat b/mysql-test/std_data/bug16171518_2.dat new file mode 100644 index 00000000000..8a483337a0f --- /dev/null +++ b/mysql-test/std_data/bug16171518_2.dat @@ -0,0 +1,12 @@ +<test_rows> + <row> + <col1>ABC</col1> + <col2>DEF</col2> + <col3 /> + </row> + <row> + <col1>GHI</col1> + <col2 /> + <col3>123</col3> + </row> +</test_rows> diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result index 469e670ae9d..3604f5e8f8e 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result @@ -1409,7 +1409,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F ### INSERT INTO `test`.`t1` ### SET -### @1=000000124.450000000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ +### @1=124.45000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ # at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 SET TIMESTAMP=1000000000/*!*/; @@ -1426,7 +1426,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F ### INSERT INTO `test`.`t1` ### SET -### @1=-000000543.210000000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ +### @1=-543.21000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ # at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 SET TIMESTAMP=1000000000/*!*/; @@ -1443,7 +1443,7 @@ BEGIN #010909 4:46:40 server id 1 end_log_pos # Delete_rows: table id # flags: STMT_END_F ### DELETE FROM `test`.`t1` ### WHERE -### @1=000000124.450000000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ +### @1=124.45000 /* DECIMAL(10,5) meta=2565 nullable=1 is_null=0 */ # at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 SET TIMESTAMP=1000000000/*!*/; diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result index 8e75d203ecc..e942071abcd 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result @@ -2390,9 +2390,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2483,9 +2483,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2659,9 +2659,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2752,9 +2752,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2832,9 +2832,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2925,9 +2925,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3005,9 +3005,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3178,9 +3178,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3271,9 +3271,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3444,9 +3444,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3537,9 +3537,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3630,9 +3630,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result index b4ea8551ca6..29a1704743c 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result @@ -2390,9 +2390,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2485,9 +2485,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2663,9 +2663,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2758,9 +2758,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2838,9 +2838,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -2933,9 +2933,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3013,9 +3013,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3188,9 +3188,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3283,9 +3283,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3458,9 +3458,9 @@ BEGIN ### @22=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=9999-12-31 23:59:59 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=2146522447 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3553,9 +3553,9 @@ BEGIN ### @22=-1.797... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=0 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000000 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=0 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='1000:01:01' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=1000-01-01 00:00:00 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=75601 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ @@ -3648,9 +3648,9 @@ BEGIN ### @22=-2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @23=2.225... /* DOUBLE meta=8 nullable=1 is_null=0 */ ### @24=1 /* DOUBLE meta=8 nullable=1 is_null=0 */ -### @25=-000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @26=000000009.999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ -### @27=000000001 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @25=-9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @26=9999999999 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ +### @27=1 /* DECIMAL(10,0) meta=2560 nullable=1 is_null=0 */ ### @28='2008:08:04' /* DATE meta=0 nullable=1 is_null=0 */ ### @29=2008-08-04 16:18:06 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @30=1217855904 /* TIMESTAMP meta=0 nullable=0 is_null=0 */ diff --git a/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result b/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result Binary files differindex 08eecb1c17d..c1d0a7f03fb 100644 --- a/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result +++ b/mysql-test/suite/engines/iuds/r/strings_charsets_update_delete.result diff --git a/mysql-test/suite/innodb/r/innodb_bug68148.result b/mysql-test/suite/innodb/r/innodb_bug68148.result new file mode 100644 index 00000000000..88247053389 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug68148.result @@ -0,0 +1,36 @@ +set global innodb_file_per_table=1; +CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; +CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; +CREATE TABLE `main` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`ref_id1` int(11) NOT NULL, +`ref_id2` int(11) NOT NULL, +PRIMARY KEY (`id`), +UNIQUE KEY `idx_1` (`ref_id1`,`ref_id2`), +KEY `FK_set_out_analysis_route_id` (`ref_id2`), +CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`) , +CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`) +) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS=0; +DROP INDEX `idx_1` ON `main`; +SHOW TABLES; +Tables_in_test +main +ref_table1 +ref_table2 +# restart and see if we can still access the main table +SET FOREIGN_KEY_CHECKS=0; +ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`); +SHOW CREATE TABLE `main`; +Table Create Table +main CREATE TABLE `main` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `ref_id1` int(11) NOT NULL, + `ref_id2` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `FK_set_out_analysis_route_id` (`ref_id2`), + KEY `idx_1` (`ref_id1`), + CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`), + CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE main, ref_table1, ref_table2; diff --git a/mysql-test/suite/innodb/t/innodb_bug60229-master.opt b/mysql-test/suite/innodb/t/innodb_bug60229-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug60229-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/innodb/t/innodb_bug60229.test b/mysql-test/suite/innodb/t/innodb_bug60229.test index 8dcf15157d6..aee0b96a942 100644 --- a/mysql-test/suite/innodb/t/innodb_bug60229.test +++ b/mysql-test/suite/innodb/t/innodb_bug60229.test @@ -2,6 +2,10 @@ # Bug #13083023 - 60229: BROKEN COMPATIBILITY: ERROR WHILE CREATE TABLE # WITH FOREIGN KEY CONSTRAINT. +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc -- source include/have_innodb.inc CREATE TABLE PERSON ( diff --git a/mysql-test/suite/innodb/t/innodb_bug68148.test b/mysql-test/suite/innodb/t/innodb_bug68148.test new file mode 100644 index 00000000000..531baa30e48 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug68148.test @@ -0,0 +1,41 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc + +# +# Bug #68148: drop index on a foreign key column leads to missing table +# MDEV-8845: Table disappear after modifying FK +# + +set global innodb_file_per_table=1; + +CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; + +CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; + +CREATE TABLE `main` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `ref_id1` int(11) NOT NULL, + `ref_id2` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `idx_1` (`ref_id1`,`ref_id2`), + KEY `FK_set_out_analysis_route_id` (`ref_id2`), + CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`) , + CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`) +) ENGINE=InnoDB; + +SET FOREIGN_KEY_CHECKS=0; + +DROP INDEX `idx_1` ON `main`; +SHOW TABLES; + +--echo # restart and see if we can still access the main table +--source include/restart_mysqld.inc + +# This is required to access the table +SET FOREIGN_KEY_CHECKS=0; +ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`); +SHOW CREATE TABLE `main`; + +DROP TABLE main, ref_table1, ref_table2; + + diff --git a/mysql-test/suite/innodb/t/xa_recovery.test b/mysql-test/suite/innodb/t/xa_recovery.test index ff6da07f6ac..aec606de77e 100644 --- a/mysql-test/suite/innodb/t/xa_recovery.test +++ b/mysql-test/suite/innodb/t/xa_recovery.test @@ -6,6 +6,12 @@ if (`select plugin_auth_version <= "5.5.43-MariaDB-37.2" from information_schema # Embedded server does not support restarting. --source include/not_embedded.inc +# MDEV-8841 - close tables opened by previous tests, +# so they don't get marked crashed when the server gets crashed +--disable_query_log +FLUSH TABLES; +--enable_query_log + CREATE TABLE t1 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); connect (con1,localhost,root); diff --git a/mysql-test/suite/jp/t/jp_enum_sjis-master.opt b/mysql-test/suite/jp/t/jp_enum_sjis-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_sjis-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_sjis.test b/mysql-test/suite/jp/t/jp_enum_sjis.test index e1f22f6fe27..1060f83ec06 100644 --- a/mysql-test/suite/jp/t/jp_enum_sjis.test +++ b/mysql-test/suite/jp/t/jp_enum_sjis.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_sjis.inc --source include/have_innodb.inc --character_set sjis diff --git a/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt b/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_ucs2.test b/mysql-test/suite/jp/t/jp_enum_ucs2.test index a3d7c47705d..321f8952148 100644 --- a/mysql-test/suite/jp/t/jp_enum_ucs2.test +++ b/mysql-test/suite/jp/t/jp_enum_ucs2.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_ucs2.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/jp/t/jp_enum_ujis-master.opt b/mysql-test/suite/jp/t/jp_enum_ujis-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_ujis-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_ujis.test b/mysql-test/suite/jp/t/jp_enum_ujis.test index 17e41e0691e..10e8ad55a3c 100644 --- a/mysql-test/suite/jp/t/jp_enum_ujis.test +++ b/mysql-test/suite/jp/t/jp_enum_ujis.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_ujis.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/jp/t/jp_enum_utf8-master.opt b/mysql-test/suite/jp/t/jp_enum_utf8-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_utf8-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_utf8.test b/mysql-test/suite/jp/t/jp_enum_utf8.test index ff5978da69c..f67939bd02b 100644 --- a/mysql-test/suite/jp/t/jp_enum_utf8.test +++ b/mysql-test/suite/jp/t/jp_enum_utf8.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_utf8.inc --source include/have_innodb.inc --disable_warnings diff --git a/mysql-test/suite/perfschema/r/query_cache.result b/mysql-test/suite/perfschema/r/query_cache.result index 8786cd055ca..837c2573a72 100644 --- a/mysql-test/suite/perfschema/r/query_cache.result +++ b/mysql-test/suite/perfschema/r/query_cache.result @@ -38,7 +38,7 @@ spins NULL select * from performance_schema.setup_timers where name='wait'; NAME TIMER_NAME -wait CYCLE +wait {CYCLE_OR_NANOSECOND} show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 @@ -53,7 +53,7 @@ spins NULL select * from performance_schema.setup_timers where name='wait'; NAME TIMER_NAME -wait CYCLE +wait {CYCLE_OR_NANOSECOND} show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 diff --git a/mysql-test/suite/perfschema/t/query_cache.test b/mysql-test/suite/perfschema/t/query_cache.test index 60d4a648222..802e574f89b 100644 --- a/mysql-test/suite/perfschema/t/query_cache.test +++ b/mysql-test/suite/perfschema/t/query_cache.test @@ -34,6 +34,7 @@ show status like "Qcache_hits"; select spins from performance_schema.events_waits_current order by event_name limit 1; +--replace_result CYCLE {CYCLE_OR_NANOSECOND} NANOSECOND {CYCLE_OR_NANOSECOND} select * from performance_schema.setup_timers where name='wait'; show status like "Qcache_queries_in_cache"; @@ -42,6 +43,7 @@ show status like "Qcache_hits"; select spins from performance_schema.events_waits_current order by event_name limit 1; +--replace_result CYCLE {CYCLE_OR_NANOSECOND} NANOSECOND {CYCLE_OR_NANOSECOND} select * from performance_schema.setup_timers where name='wait'; show status like "Qcache_queries_in_cache"; diff --git a/mysql-test/suite/plugins/r/feedback_plugin_load.result b/mysql-test/suite/plugins/r/feedback_plugin_load.result index 443b91bf0cc..ea6eae98601 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_load.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_load.result @@ -1,10 +1,13 @@ select plugin_status from information_schema.plugins where plugin_name='feedback'; plugin_status ACTIVE +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +variable_value = @feedback_used + 1 +1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; VARIABLE_NAME VARIABLE_VALUE -FEEDBACK used 1 FEEDBACK version 1.1 FEEDBACK_SEND_RETRY_WAIT 60 FEEDBACK_SEND_TIMEOUT 60 diff --git a/mysql-test/suite/plugins/r/feedback_plugin_send.result b/mysql-test/suite/plugins/r/feedback_plugin_send.result index 2852240ca5b..90a37f7ffbc 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_send.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_send.result @@ -1,10 +1,13 @@ select plugin_status from information_schema.plugins where plugin_name='feedback'; plugin_status ACTIVE +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +variable_value = @feedback_used + 1 +1 select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; VARIABLE_NAME VARIABLE_VALUE -FEEDBACK used 2 FEEDBACK version 1.1 FEEDBACK_SEND_RETRY_WAIT 60 FEEDBACK_SEND_TIMEOUT 60 diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test index 5ad301667b4..f7c62b96b49 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_load.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -4,7 +4,24 @@ if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'fe } select plugin_status from information_schema.plugins where plugin_name='feedback'; + +# Every SELECT from INFORMATION_SCHEMA.FEEDBACK increases the value of 'FEEDBACK used'. +# We cannot record the actual value, because the test can be executed more than once, +# but we can check that the value indeed increases as expected. +# There is still a room for some race condition, e.g. if at the very moment +# between first SELECT to store the value and the next SELECT to check that it increases, +# the feedback plugin is activated. But the probability of it is close to 0, +# so lets get back to it if it ever happens. + +# Lets say the plugin was used X times before this SELECT +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now $feedback_used == X+1, and 'FEEDBACK used' is also X+1. And variable_value is increased again when we run the next SELECT +SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now when we are happy with 'FEEDBACK used', we can check everything else + --replace_result https http --sorted_result select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used'; diff --git a/mysql-test/suite/plugins/t/feedback_plugin_send.test b/mysql-test/suite/plugins/t/feedback_plugin_send.test index 45b507f8e78..31542c33482 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_send.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_send.test @@ -14,6 +14,15 @@ if (!$MTR_FEEDBACK_PLUGIN) { # is doing some work in other workers. # sleep 310; + +# The test expects that the plugin will send a report at least 2 times, +# now (5 min after loading) and on server shutdown which happens below. +# Since we have already waited for 5 min, let's be generous +# and make sure the server has enough time to shut down properly. +# We won't lose anything if the shutdown is fast, but if it's slow, the plugin +# will still be able to finish the job and write about it in the error log. + +--let $shutdown_timeout= 60 source include/restart_mysqld.inc; replace_result https http; diff --git a/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result b/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result index e71bb2e29c9..c2a0498509b 100644 --- a/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result +++ b/mysql-test/suite/rpl/r/show_status_stop_slave_race-7126.result @@ -1,2 +1,4 @@ include/master-slave.inc [connection master] +start slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt b/mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt new file mode 100644 index 00000000000..e6685732d90 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_innodb_bug30888.opt @@ -0,0 +1 @@ +--innodb-flush-log-at-trx-commit=2 diff --git a/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test b/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test index a79a1885a6c..38759c9b16a 100644 --- a/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test +++ b/mysql-test/suite/rpl/t/show_status_stop_slave_race-7126.test @@ -10,3 +10,10 @@ --exec $MYSQL_SLAP --silent --socket=$SLAVE_MYSOCK -q "START SLAVE; STOP SLAVE; SHOW GLOBAL STATUS" -c 2 --number-of-queries=100 --create-schema=test # All done. + +--connection slave +start slave; + +--connection master +--source include/rpl_end.inc + diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index 2b89a618aa6..8fdba77db9b 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -206,6 +206,20 @@ SELECT if(1, NULL, (SELECT min('hello'))); --echo End of 5.2 tests +--echo # +--echo # MDEV-8663: IF Statement returns multiple values erroneously +--echo # (or Assertion `!null_value' failed in Item::send(Protocol*, String*) +--echo # +CREATE TABLE `t1` ( +`datas` VARCHAR(25) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO `t1` VALUES ('1,2'), ('2,3'), ('3,4'); + +SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `t1`; + +drop table t1; + --disable_query_log # Restore timezone to default set time_zone= @@global.time_zone; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 25d8cc1067a..5991220e273 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -398,5 +398,204 @@ SELECT GET_LOCK('ul1', NULL); SELECT GET_LOCK('ul1', -1); --echo # +--echo # MDEV-8624 MariaDB hangs on query with many logical condition +--echo # +CREATE TABLE `t1` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `submitdate` datetime DEFAULT NULL, + `lastpage` int(11) DEFAULT NULL, + `startlanguage` varchar(20) COLLATE utf8_unicode_ci NOT NULL, + `token` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL, + `datestamp` datetime NOT NULL, + `startdate` datetime NOT NULL, + `ipaddr` text COLLATE utf8_unicode_ci, + `refurl` text COLLATE utf8_unicode_ci, + `57813X540X1723` text COLLATE utf8_unicode_ci, + `57813X540X1724` text COLLATE utf8_unicode_ci, + `57813X540X1725` text COLLATE utf8_unicode_ci, + `57813X540X1726` double DEFAULT NULL, + `57813X540X1909` double DEFAULT NULL, + `57813X541X17271` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17272` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17273` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17274` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17275` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17276` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17281` text COLLATE utf8_unicode_ci, + `57813X541X17282` text COLLATE utf8_unicode_ci, + `57813X541X17283` text COLLATE utf8_unicode_ci, + `57813X541X17284` text COLLATE utf8_unicode_ci, + `57813X541X17285` text COLLATE utf8_unicode_ci, + `57813X541X17286` text COLLATE utf8_unicode_ci, + `57813X542X18131` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18132` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18133` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18134` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18135` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18136` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18137` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18138` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18139` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18141` text COLLATE utf8_unicode_ci, + `57813X542X18142` text COLLATE utf8_unicode_ci, + `57813X542X18143` text COLLATE utf8_unicode_ci, + `57813X542X18144` text COLLATE utf8_unicode_ci, + `57813X542X18145` text COLLATE utf8_unicode_ci, + `57813X542X18146` text COLLATE utf8_unicode_ci, + `57813X542X18147` text COLLATE utf8_unicode_ci, + `57813X542X18148` text COLLATE utf8_unicode_ci, + `57813X542X18149` text COLLATE utf8_unicode_ci, + `57813X543X18451` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18452` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18453` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18454` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18455` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18456` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18461` text COLLATE utf8_unicode_ci, + `57813X543X18462` text COLLATE utf8_unicode_ci, + `57813X543X18463` text COLLATE utf8_unicode_ci, + `57813X543X18464` text COLLATE utf8_unicode_ci, + `57813X543X18465` text COLLATE utf8_unicode_ci, + `57813X543X18466` text COLLATE utf8_unicode_ci, + `57813X544X18711` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18712` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18713` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18714` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18715` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18716` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18717` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18718` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18721` text COLLATE utf8_unicode_ci, + `57813X544X18722` text COLLATE utf8_unicode_ci, + `57813X544X18723` text COLLATE utf8_unicode_ci, + `57813X544X18724` text COLLATE utf8_unicode_ci, + `57813X544X18725` text COLLATE utf8_unicode_ci, + `57813X544X18726` text COLLATE utf8_unicode_ci, + `57813X544X18727` text COLLATE utf8_unicode_ci, + `57813X544X18728` text COLLATE utf8_unicode_ci, + `57813X546X1902` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X546X1903` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X546X1904` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X545X1901` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `lime_survey_57813_idx` (`token`), + KEY `57813X540X1723` (`57813X540X1723`(100)), + KEY `57813X540X1724` (`57813X540X1724`(100)), + KEY `57813X540X1726` (`57813X540X1726`), + KEY `57813X540X1725` (`57813X540X1725`(100)), + KEY `57813X546X1902` (`57813X546X1902`), + KEY `57813X546X1903` (`57813X546X1903`), + KEY `57813X546X1904` (`57813X546X1904`) +); + +SELECT +COUNT(*) as `N`, +ROUND( + ( + SUM( + ( + ( + IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 57813X541X17271, 0 ) + + IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 57813X541X17272, 0 ) + + IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 57813X541X17273, 0 ) + + IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 57813X541X17274, 0 ) + + IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 57813X541X17275, 0 ) + + IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 57813X541X17276, 0 ) + + IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 57813X542X18131, 0 ) + + IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 57813X542X18132, 0 ) + + IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 57813X542X18133, 0 ) + + IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 57813X542X18134, 0 ) + + IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 57813X542X18135, 0 ) + + IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 57813X542X18136, 0 ) + + IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 57813X542X18137, 0 ) + + IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 57813X542X18138, 0 ) + + IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 57813X542X18139, 0 ) + + IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 57813X543X18451, 0 ) + + IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 57813X543X18452, 0 ) + + IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 57813X543X18453, 0 ) + + IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 57813X543X18454, 0 ) + + IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 57813X543X18455, 0 ) + + IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 57813X543X18456, 0 ) + + IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 57813X544X18711, 0 ) + + IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 57813X544X18712, 0 ) + + IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 57813X544X18713, 0 ) + + IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 57813X544X18714, 0 ) + + IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 57813X544X18715, 0 ) + + IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 57813X544X18716, 0 ) + + IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 57813X544X18717, 0 ) + + IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 57813X544X18718, 0 ) + ) + / + ( + IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 1, 0 ) + + IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 1, 0 ) + + IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 1, 0 ) + + IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 1, 0 ) + + IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 1, 0 ) + + IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 1, 0 ) + + IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 1, 0 ) + + IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 1, 0 ) + + IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 1, 0 ) + + IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 1, 0 ) + + IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 1, 0 ) + + IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 1, 0 ) + + IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 1, 0 ) + + IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 1, 0 ) + + IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 1, 0 ) + + IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 1, 0 ) + + IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 1, 0 ) + + IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 1, 0 ) + + IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 1, 0 ) + + IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 1, 0 ) + + IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 1, 0 ) + + IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 1, 0 ) + + IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 1, 0 ) + + IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 1, 0 ) + + IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 1, 0 ) + + IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 1, 0 ) + + IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 1, 0 ) + + IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 1, 0 ) + + IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 1, 0 ) + ) + ) + ) + / COUNT(*) ), 4) as `AVG` +FROM `t1` +WHERE `submitdate` IS NOT NULL +AND ( + ( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99' ) OR + ( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99' ) OR + ( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99' ) OR + ( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99' ) OR + ( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99' ) OR + ( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99' ) OR + ( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99' ) OR + ( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99' ) OR + ( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99' ) OR + ( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99' ) OR + ( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99' ) OR + ( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99' ) OR + ( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99' ) OR + ( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99' ) OR + ( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99' ) OR + ( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99' ) OR + ( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99' ) OR + ( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99' ) OR + ( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99' ) OR + ( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99' ) OR + ( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99' ) OR + ( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99' ) OR + ( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99' ) OR + ( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99' ) OR + ( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99' ) OR + ( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99' ) OR + ( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99' ) OR + ( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99' ) OR + ( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99' ) ) +AND 57813X540X1723 = 'Test'; + +drop table t1; + +--echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/information_schema2.test b/mysql-test/t/information_schema2.test index 06bc6d1bf55..9810c5a0aae 100644 --- a/mysql-test/t/information_schema2.test +++ b/mysql-test/t/information_schema2.test @@ -8,15 +8,12 @@ select variable_name from information_schema.session_variables where variable_na (select variable_name from information_schema.session_variables where variable_name = 'basedir'); # -# information_schema tables inside subqueries, they should not be re-populated -# (i_s.columns needs to scan i_s itself, creating a tmp table for every i_s -# table. if it's re-populated, it'll do that multiple times) +# MDEV-8796 Delete with sub query with information_schema.TABLES deletes too many rows # -create table t1 (a char); -insert t1 values ('a'),('t'),('z'); -flush status; -select a, exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) from t1; -# fix the result in ps-protocol ---replace_result 39 38 -show status like 'created_tmp_tables'; -drop table t1; +create table t1 (x int); +create table t2 (x int); +create table t3 (x int); +create table t4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ; +delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE'); +select * from t4; +drop table t1, t2, t3, t4; diff --git a/mysql-test/t/loadxml.test b/mysql-test/t/loadxml.test index 6faf712b6ce..93e6e82189f 100644 --- a/mysql-test/t/loadxml.test +++ b/mysql-test/t/loadxml.test @@ -116,3 +116,17 @@ LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1 ROWS IDENTIFIED BY '<row>' (a,@b) SET b=concat('!',@b); SELECT * FROM t1 ORDER BY a; DROP TABLE t1; + + +--echo # +--echo # Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS +--echo # +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4)); +LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3, col4; +DROP TABLE t1; + +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER); +LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3; +DROP TABLE t1; diff --git a/mysql-test/t/lowercase_fs_on.test b/mysql-test/t/lowercase_fs_on.test new file mode 100644 index 00000000000..6da3ef32a0b --- /dev/null +++ b/mysql-test/t/lowercase_fs_on.test @@ -0,0 +1,38 @@ +# +# Specific tests for case-insensitive file systems +# i.e. lower_case_filesystem=ON +# +-- source include/have_case_insensitive_file_system.inc +# Embedded server does not support restarting. +--source include/not_embedded.inc + +--echo # +--echo # Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS +--echo # + +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/my_restart.err; + +--error 0,1 +--remove_file $SEARCH_FILE + +#Shutdown the server +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc + +#Start the server with --lower_case_table_names=0 in Windows. +--enable_reconnect +--error 1 +--exec $MYSQLD_CMD --lower_case_table_names=0 > $SEARCH_FILE 2>&1 + +#Search for the error messege in the server error log. +let SEARCH_PATTERN= \[ERROR\] The server option \'lower_case_table_names\' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination\. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode\.; +--source include/search_pattern_in_file.inc + +#Restart the server +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/wait_until_connected_again.inc + +#Cleanup +--error 0,1 +--remove_file $SEARCH_FILE diff --git a/mysql-test/t/lowercase_table3-master.opt b/mysql-test/t/lowercase_table3-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/t/lowercase_table3-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/t/lowercase_table3.test b/mysql-test/t/lowercase_table3.test deleted file mode 100644 index f7ca8211288..00000000000 --- a/mysql-test/t/lowercase_table3.test +++ /dev/null @@ -1,37 +0,0 @@ -# -# Test of force of lower-case-table-names=0 -# (User has case insensitive file system and wants to preserve case of -# table names) -# - ---source include/have_innodb.inc ---source include/have_lowercase0.inc ---source include/have_case_insensitive_file_system.inc ---source include/not_windows.inc - -call mtr.add_suppression("Cannot find or open table test/BUG29839 from"); - ---disable_warnings -DROP TABLE IF EXISTS t1,T1; ---enable_warnings - -# -# This is actually an error, but ok as the user has forced this -# by using --lower-case-table-names=0 -CREATE TABLE t1 (a INT); -SELECT * FROM T1; -FLUSH TABLES; -DROP TABLE t1; - -# -# InnoDB should in this case be case sensitive -# Note that this is not true on windows as no this OS, InnoDB is always -# storing things in lower case. -# - -CREATE TABLE bug29839 (a INT) ENGINE=INNODB; ---error ER_NO_SUCH_TABLE -SELECT * FROM BUG29839; -DROP TABLE bug29839; - -# End of 4.1 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 249f16e3a26..e0c0ce346ed 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -901,13 +901,8 @@ select * from t1; create view v1 as select * from v3 where b in (1, 2, 3, 4, 5, 6, 7); -# Disable warnings since LIMIT warning for unsafe statement if -# binlog_format = STATEMENT. Note: after BUG#45832, the warning should -# not be issued. ---disable_warnings create view v2 as select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1; ---enable_warnings --exec $MYSQL_DUMP --skip-comments test @@ -1777,7 +1772,7 @@ drop table words; --replace_regex /.*mysqlimport(\.exe)*/mysqlimport/ --replace_result mysqldump.exe mysqldump --error 1 ---exec $MYSQL_IMPORT --silent --use-threads=2 test $MYSQLTEST_VARDIR/tmp/t1.txt $MYSQLTEST_VARDIR/tmp/t2.txt $MYSQLTEST_VARDIR/std_data/words.dat $MYSQLTEST_VARDIR/std_data/words2.dat 2>&1 +--exec $MYSQL_IMPORT --silent --use-threads=2 test $MYSQLTEST_VARDIR/tmp/t1.txt $MYSQLTEST_VARDIR/tmp/t2.txt $MYSQLTEST_VARDIR/std_data/words.dat $MYSQLTEST_VARDIR/std_data/words2.dat drop table t1; drop table t2; @@ -2456,6 +2451,35 @@ drop table t1, t2; --echo # End of 5.1 tests --echo # +--echo # +--echo # Bug #20772273 : MYSQLIMPORT --USE-THREADS DOESN'T USE MULTIPLE THREADS +--echo # + +CREATE DATABASE db_20772273; +USE db_20772273; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (3), (4); + +SELECT * FROM t1; +SELECT * FROM t2; + +--exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ db_20772273 +--exec $MYSQL db_20772273 < $MYSQLTEST_VARDIR/tmp/t1.sql +--exec $MYSQL db_20772273 < $MYSQLTEST_VARDIR/tmp/t2.sql + +# Test mysqlimport with multiple threads +--exec $MYSQL_IMPORT --silent --use-threads=2 db_20772273 $MYSQLTEST_VARDIR/tmp/t1.txt $MYSQLTEST_VARDIR/tmp/t2.txt + +SELECT * FROM t1; +SELECT * FROM t2; + +#Cleanup +DROP TABLE t1; +DROP TABLE t2; +DROP DATABASE db_20772273; + # # MDEV-6091 mysqldump goes in a loop and segfaults if --dump-slave is specified and it cannot connect to the server # diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test index 64799574140..6b6e54b1052 100644 --- a/mysql-test/t/partition_error.test +++ b/mysql-test/t/partition_error.test @@ -1146,7 +1146,7 @@ subpartition by hash (rand(a+b)); # # Subpartition by hash, wrong subpartition function # ---error ER_SUBPARTITION_ERROR +--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a int not null, b int not null, diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d1c3774947a..67eac0d228c 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -53,7 +53,7 @@ SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); -- error ER_WRONG_USAGE select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); --- error ER_WRONG_PARAMETERS_TO_PROCEDURE +-- error ER_PARSE_ERROR SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; @@ -5890,3 +5890,77 @@ DROP TABLE t1,t2; create table t1 (c int); select exists(select 1 from t1 group by `c` in (select `c` from t1)); drop table t1; + +--echo # +--echo # MDEV-7565: Server crash with Signal 6 (part 2) +--echo # +Select + (Select Sum(`TestCase`.Revenue) From mysql.slow_log E + Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) + ) As `ControlRev` +From +(Select 3 as Revenue, 4 as TemplateID) As `TestCase` +Group By TestCase.Revenue, TestCase.TemplateID; + +--echo # +--echo # MDEV-7445:Server crash with Signal 6 +--echo # + +--delimiter | +CREATE PROCEDURE procedure2() +BEGIN + Select + (Select Sum(`TestCase`.Revenue) From mysql.slow_log E + Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) + ) As `ControlRev` + From + (Select 3 as Revenue, 4 as TemplateID) As `TestCase` + Group By TestCase.Revenue, TestCase.TemplateID; + +END | +--delimiter ; +call procedure2(); +call procedure2(); + +drop procedure procedure2; + + +SELECT + (SELECT user FROM mysql.user + WHERE h.host in (SELECT host FROM mysql.user) + ) AS sq +FROM mysql.host h GROUP BY h.host; + + +--echo # +--echo # MDEV-7846:Server crashes in Item_subselect::fix +--echo #_fields or fails with Thread stack overrun +--echo # +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); + +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; + +INSERT INTO t2 VALUES (1),(4); + +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); + +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); + + +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; + +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; +drop table t1,t2,t3,t4; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index daa20509ab6..be97dd02dc7 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -506,6 +506,21 @@ DROP VIEW v1; DROP FUNCTION f1; DROP TABLE t1; +--echo # Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT +--echo # RESULTS + +CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=INNODB; + +INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string'); +UPDATE t1 SET b = a, a = 'inject'; +SELECT a, b FROM t1; +UPDATE t1 SET b = c, c = 'inject'; +SELECT c, b FROM t1; + +DROP TABLE t1; + +--echo # +--echo # MDEV-3948 Assertion `records_are_comparable(table)' fails in compare_record(const TABLE*) on UPDATE with simple AND condition, index_merge+index_merge_intersection, InnoDB --echo # --echo # Verify that UPDATE does the same number of handler_update --echo # operations, no matter if there is ORDER BY or not. diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index ca70cd1efa7..fe04cdfecec 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4741,6 +4741,50 @@ SHOW CREATE VIEW v4; DROP VIEW v1, v2, v3, v4, v5; + +--echo # +--echo # BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, +--echo # IS REJECTED + +--echo # Without the patch, reports an error. +CREATE VIEW v1 (fld1, fld2) AS + SELECT 1 AS a, 2 AS b + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # The column names are explicitly specified and not duplicates, hence +--echo # succeeds. +CREATE VIEW v2 (fld1, fld2) AS + SELECT 1 AS a, 2 AS a + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # The column name in the first SELECT are not duplicates, hence succeeds. +CREATE VIEW v3 AS + SELECT 1 AS a, 2 AS b + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # Should report an error, since the explicitly specified column names are +--echo # duplicates. +--error ER_DUP_FIELDNAME +CREATE VIEW v4 (fld1, fld1) AS + SELECT 1 AS a, 2 AS b + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # Should report an error, since duplicate column name is specified in the +--echo # First SELECT. +--error ER_DUP_FIELDNAME +CREATE VIEW v4 AS + SELECT 1 AS a, 2 AS a + UNION ALL + SELECT 1 AS a, 1 AS a; + +--echo # Cleanup +DROP VIEW v1, v2, v3; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc |