diff options
Diffstat (limited to 'mysql-test/r')
28 files changed, 504 insertions, 72 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 2455bc4f997..92df7bc577e 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -2171,3 +2171,54 @@ DROP TABLE t1; CREATE TABLE t1(a INT, b INT, CONSTRAINT min check (a>5), CONSTRAINT min check (b>5)); ERROR HY000: Duplicate CHECK constraint name 'min' +create table t1 (a int, b int, check(a>b)); +alter table t1 drop column a; +ERROR 42S22: Unknown column 'a' in 'CHECK' +alter table t1 drop column b, add column b bigint first; +ERROR 42S22: Unknown column 'b' in 'CHECK' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (`a` > `b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (a int, b int, check(a>0)); +alter table t1 drop column a; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (a int, b int, check(a>0)); +alter table t1 drop column a, add column a bigint first; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (a int, b int, c int, unique(a)); +alter table t1 drop column a; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (a int, b int, c int, unique(a,b)); +alter table t1 drop column a; +ERROR 42000: Key column 'a' doesn't exist in table +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + UNIQUE KEY `a` (`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; diff --git a/mysql-test/r/binary_to_hex.result b/mysql-test/r/binary_to_hex.result new file mode 100644 index 00000000000..345df1de538 --- /dev/null +++ b/mysql-test/r/binary_to_hex.result @@ -0,0 +1,117 @@ +USE test; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (c1 TINYBLOB, +c2 BLOB, +c3 MEDIUMBLOB, +c4 LONGBLOB, +c5 TEXT, +c6 BIT(1), +c7 CHAR, +c8 VARCHAR(10), +c9 GEOMETRY) CHARACTER SET = binary; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyblob DEFAULT NULL, + `c2` blob DEFAULT NULL, + `c3` mediumblob DEFAULT NULL, + `c4` longblob DEFAULT NULL, + `c5` blob DEFAULT NULL, + `c6` bit(1) DEFAULT NULL, + `c7` binary(1) DEFAULT NULL, + `c8` varbinary(10) DEFAULT NULL, + `c9` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=binary +INSERT INTO t1 VALUES ('tinyblob-text readable', 'blob-text readable', +'mediumblob-text readable', 'longblob-text readable', +'text readable', b'1', 'c', 'variable', +POINT(1, 1)); +CREATE TABLE t2(id int, `col1` binary(10),`col2` blob); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) DEFAULT NULL, + `col1` binary(10) DEFAULT NULL, + `col2` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t2 VALUES (1, X'AB1234', X'123ABC'), (2, X'DE1234', X'123DEF'); +#Print the table contents when binary-as-hex option is off. +SELECT * FROM t1; +c1 c2 c3 c4 c5 c6 c7 c8 c9 +tinyblob-text readable blob-text readable mediumblob-text readable longblob-text readable text readable # c variable # +SELECT * FROM t2; +id col1 col2 +1 # # +2 # # +#Print the table contents after turning on the binary-as-hex option + +#Print the table contents in tab format + +c1 c2 c3 c4 c5 c6 c7 c8 c9 +0x74696E79626C6F622D74657874207265616461626C65 0x626C6F622D74657874207265616461626C65 0x6D656469756D626C6F622D74657874207265616461626C65 0x6C6F6E67626C6F622D74657874207265616461626C65 0x74657874207265616461626C65 0x01 0x63 0x7661726961626C65 0x000000000101000000000000000000F03F000000000000F03F +id col1 col2 +1 0xAB123400000000000000 0x123ABC +2 0xDE123400000000000000 0x123DEF + +#Print the table contents in table format + ++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+ +| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | ++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+ +| 0x74696E79626C6F622D74657874207265616461626C65 | 0x626C6F622D74657874207265616461626C65 | 0x6D656469756D626C6F622D74657874207265616461626C65 | 0x6C6F6E67626C6F622D74657874207265616461626C65 | 0x74657874207265616461626C65 | 0x01 | 0x63 | 0x7661726961626C65 | 0x000000000101000000000000000000F03F000000000000F03F | ++------------------------------------------------+----------------------------------------+----------------------------------------------------+------------------------------------------------+------------------------------+------------+------------+--------------------+------------------------------------------------------+ ++------+------------------------+------------+ +| id | col1 | col2 | ++------+------------------------+------------+ +| 1 | 0xAB123400000000000000 | 0x123ABC | ++------+------------------------+------------+ + +#Print the table contents vertically + +*************************** 1. row *************************** +c1: 0x74696E79626C6F622D74657874207265616461626C65 +c2: 0x626C6F622D74657874207265616461626C65 +c3: 0x6D656469756D626C6F622D74657874207265616461626C65 +c4: 0x6C6F6E67626C6F622D74657874207265616461626C65 +c5: 0x74657874207265616461626C65 +c6: 0x01 +c7: 0x63 +c8: 0x7661726961626C65 +c9: 0x000000000101000000000000000000F03F000000000000F03F + +#Print the table contents in xml format + +<?xml version="1.0"?> + +<resultset statement="SELECT * FROM t1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> + <row> + <field name="c1">0x74696E79626C6F622D74657874207265616461626C65</field> + <field name="c2">0x626C6F622D74657874207265616461626C65</field> + <field name="c3">0x6D656469756D626C6F622D74657874207265616461626C65</field> + <field name="c4">0x6C6F6E67626C6F622D74657874207265616461626C65</field> + <field name="c5">0x74657874207265616461626C65</field> + <field name="c6">0x01</field> + <field name="c7">0x63</field> + <field name="c8">0x7661726961626C65</field> + <field name="c9">0x000000000101000000000000000000F03F000000000000F03F</field> + </row> +</resultset> +<?xml version="1.0"?> + +<resultset statement="SELECT * FROM t2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> + <row> + <field name="id">1</field> + <field name="col1">0xAB123400000000000000</field> + <field name="col2">0x123ABC</field> + </row> + + <row> + <field name="id">2</field> + <field name="col1">0xDE123400000000000000</field> + <field name="col2">0x123DEF</field> + </row> +</resultset> + +#Print the table contents in html format + +<TABLE BORDER=1><TR><TH>c1</TH><TH>c2</TH><TH>c3</TH><TH>c4</TH><TH>c5</TH><TH>c6</TH><TH>c7</TH><TH>c8</TH><TH>c9</TH></TR><TR><TD>0x74696E79626C6F622D74657874207265616461626C65</TD><TD>0x626C6F622D74657874207265616461626C65</TD><TD>0x6D656469756D626C6F622D74657874207265616461626C65</TD><TD>0x6C6F6E67626C6F622D74657874207265616461626C65</TD><TD>0x74657874207265616461626C65</TD><TD>0x01</TD><TD>0x63</TD><TD>0x7661726961626C65</TD><TD>0x000000000101000000000000000000F03F000000000000F03F</TD></TR></TABLE><TABLE BORDER=1><TR><TH>id</TH><TH>col1</TH><TH>col2</TH></TR><TR><TD>1</TD><TD>0xAB123400000000000000</TD><TD>0x123ABC</TD></TR><TR><TD>2</TD><TD>0xDE123400000000000000</TD><TD>0x123DEF</TD></TR></TABLE>DROP TABLE t1, t2; diff --git a/mysql-test/r/check_constraint.result b/mysql-test/r/check_constraint.result index fcda0a84fcb..308b6a2fc2b 100644 --- a/mysql-test/r/check_constraint.result +++ b/mysql-test/r/check_constraint.result @@ -136,10 +136,6 @@ insert into t1(c1) values(1); ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` insert into t1(c1) values(2); drop table t1; -create table t1 (a int, b int, check(a>0)); -alter table t1 drop column a; -ERROR 42S22: Unknown column 'a' in 'CHECK' -drop table t1; create or replace table t1( c1 int auto_increment primary key, check( c1 > 0 or c1 is null ) ); ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the CHECK clause of `c1` create table t1 (a int check (@b in (select user from mysql.user))); diff --git a/mysql-test/r/count_distinct.result b/mysql-test/r/count_distinct.result index 3b65dd0e608..d55a232c715 100644 --- a/mysql-test/r/count_distinct.result +++ b/mysql-test/r/count_distinct.result @@ -94,3 +94,15 @@ count(distinct i) 2 drop table t1; drop view v1; +create table t1 (user_id char(64) character set utf8); +insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17); +set @@tmp_table_size = 1024; +select count(distinct user_id) from t1; +count(distinct user_id) +17 +alter table t1 modify user_id char(128) character set utf8; +select count(distinct user_id) from t1; +count(distinct user_id) +17 +drop table t1; +set @@tmp_table_size = default; diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result index e7a315f4314..3c849d5a649 100644 --- a/mysql-test/r/errors.result +++ b/mysql-test/r/errors.result @@ -170,7 +170,7 @@ UPDATE t1 SET a = 'new' WHERE COLUMN_CREATE( 1, 'v', 1, 'w' ) IS NULL; ERROR 22007: Illegal value used as argument of dynamic column function drop table t1; +set max_session_mem_used = 50000; +select * from seq_1_to_1000; set max_session_mem_used = 8192; select * from seq_1_to_1000; -Got one of the listed errors -set global max_session_mem_used = default; diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result index 0030fa53381..ece2019ef83 100644 --- a/mysql-test/r/func_regexp_pcre.result +++ b/mysql-test/r/func_regexp_pcre.result @@ -885,6 +885,38 @@ SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral, 1 Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT CONCAT(REPEAT('100,',133),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +CONCAT(REPEAT('100,',133),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' +1 +SELECT CONCAT(REPEAT('100,',200),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; +CONCAT(REPEAT('100,',200),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +REGEXP_INSTR(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') +1 +SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); +REGEXP_INSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) +535 +SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); +LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',133),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) +0 +SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); +LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) +803 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp SELECT REGEXP_INSTR('a_kollision', 'oll'); REGEXP_INSTR('a_kollision', 'oll') 4 diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index aa4ba5e6a1c..c9a2a83edd1 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -331,8 +331,8 @@ fid IsClosed(g) 116 0 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon; fid AsText(Centroid(g)) -117 POINT(55.58852775304245 17.426536064113982) -118 POINT(55.58852775304245 17.426536064113982) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) 119 POINT(2 2) SELECT fid, Area(g) FROM gis_multi_polygon; fid Area(g) @@ -680,11 +680,11 @@ insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363 select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85998; object_id geometrytype(geo) ISSIMPLE(GEO) ASTEXT(centroid(geo)) -85998 MULTIPOLYGON 1 POINT(115.31877315203187 -36.23747282102153) +85998 MULTIPOLYGON 1 POINT(115.2970604672862 -36.23335610879993) select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from t1 where object_id=85984; object_id geometrytype(geo) ISSIMPLE(GEO) ASTEXT(centroid(geo)) -85984 MULTIPOLYGON 1 POINT(-114.87787186923313 36.33101763469059) +85984 MULTIPOLYGON 1 POINT(-114.86854472054372 36.34725218253213) drop table t1; create table t1 (fl geometry not null); insert into t1 values (1); diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index d3fafe7e638..e025082177a 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2640,6 +2640,33 @@ select a from t1 group by a having a > 1; a drop table t1; set sql_mode= @save_sql_mode; +create table t1 (f1 int); +insert into t1 values (5),(9); +create table t2 (f2 int); +insert into t2 values (0),(6); +create table t3 (f3 int); +insert into t3 values (6),(3); +create table t4 (f4 int); +insert into t4 values (1),(0); +select +(select min(f1) from t1 where f1 in (select min(f4) from t2)) as field7, +(select count(*) from t3 where f3 in (select max(f4) from t2 group by field7)) +from t4; +ERROR 42S22: Reference 'field7' not supported (reference to group function) +drop table t1, t2, t3, t4; +create table t1 (i1 int); +insert into t1 values (1); +create table t2 (i int); +insert into t2 values (2); +select 1 from t1 left join t2 b on b.i = (select max(b.i) from t2); +1 +1 +drop table t1, t2; +create table t1 (c1 int, c2 int); +create table t2 (c1 int, c2 int); +select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3; +c1 c1 +drop table t1, t2; # # Bug #58782 # Missing rows with SELECT .. WHERE .. IN subquery diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 21387f67893..992e1d831b5 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -34,6 +34,9 @@ key1 key2 key3 key4 key5 key6 key7 key8 1022 1022 1022 1022 1022 1022 1022 2 1023 1023 1023 1023 1023 1023 1023 1 1024 1024 1024 1024 1024 1024 1024 0 +select * from t0 where key1=1022; +key1 key2 key3 key4 key5 key6 key7 key8 +1022 1022 1022 1022 1022 1022 1022 2 explain select * from t0 where key1 < 3 or key2 <4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result index 34719a27afc..1d7af4f8b38 100644 --- a/mysql-test/r/loadxml.result +++ b/mysql-test/r/loadxml.result @@ -5,10 +5,10 @@ load xml infile '../../std_data/loadxml.dat' into table t1 rows identified by '<row>'; select * from t1 order by a; a b -1 b1 -2 b2 -3 b3 -11 b11 +1 b1 +2 b2 +3 b3 +11 b11 111 b111 112 b112 & < > " ' &unknown; -- check entities 212 b212 @@ -84,17 +84,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; a b -1 !b1 -11 !b11 +1 ! b1 +11 ! b11 111 !b111 112 !b112 & < > " ' &unknown; -- check entities -2 !b2 +2 ! b2 212 !b212 213 !b213 214 !b214 215 !b215 216 !&bb b; -3 !b3 +3 ! b3 DROP TABLE t1; # # Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index a337a939acc..a6a5da8f8c8 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -475,6 +475,29 @@ even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@loca even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; DROP PROCEDURE test.pr; +use test; +call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be'); +alter table mysql.innodb_table_stats modify last_update int not null; +create table extralongname_extralongname_extralongname_extralongname_ext ( +id int(10) unsigned not null, +created_date date not null, +created timestamp not null, +primary key (created,id,created_date) +) engine=innodb stats_persistent=1 default charset=latin1 +partition by range (year(created_date)) +subpartition by hash (month(created_date)) +subpartitions 2 ( +partition p2007 values less than (2008), +partition p2008 values less than (2009) +); +select length(table_name) from mysql.innodb_table_stats; +length(table_name) +79 +79 +79 +79 +drop table extralongname_extralongname_extralongname_extralongname_ext; +End of 10.0 tests set sql_mode=default; # Droping the previously created mysql_upgrade_info file.. create table test.t1(a int) engine=MyISAM; @@ -537,4 +560,4 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE test.t1; SET GLOBAL enforce_storage_engine=NULL; -End of tests +End of 10.1 tests diff --git a/mysql-test/r/mysqld--help,win.rdiff b/mysql-test/r/mysqld--help,win.rdiff index b1544862a59..4e46a9da285 100644 --- a/mysql-test/r/mysqld--help,win.rdiff +++ b/mysql-test/r/mysqld--help,win.rdiff @@ -1,6 +1,6 @@ ---- mysqld--help.result 2016-11-04 13:35:06.665881700 +0000 -+++ mysqld--help,win.reject 2016-11-04 13:58:39.030512500 +0000 -@@ -318,7 +318,6 @@ +--- a/mysql-test/r/mysqld--help.result ++++ b/mysql-test/r/mysqld--help.result +@@ -334,7 +334,6 @@ The number of segments in a key cache -L, --language=name Client error messages in given language. May be given as a full path. Deprecated. Use --lc-messages-dir instead. @@ -8,7 +8,7 @@ --lc-messages=name Set the language used for the error messages. -L, --lc-messages-dir=name Directory where error messages are -@@ -521,6 +520,7 @@ +@@ -543,6 +542,7 @@ Use MySQL-5.6 (instead of MariaDB-5.3) format for TIME, DATETIME, TIMESTAMP columns. (Defaults to on; use --skip-mysql56-temporal-format to disable.) @@ -16,7 +16,7 @@ --net-buffer-length=# Buffer length for TCP/IP and socket communication --net-read-timeout=# -@@ -931,6 +931,9 @@ +@@ -956,6 +956,9 @@ characteristics (isolation level, read only/read write,snapshot - but not any work done / data modified within the transaction). @@ -26,7 +26,7 @@ --show-slave-auth-info Show user and password in SHOW SLAVE HOSTS on this master. -@@ -1043,6 +1046,10 @@ +@@ -1068,6 +1071,10 @@ Log slow queries to given log file. Defaults logging to 'hostname'-slow.log. Must be enabled to activate other slow log options @@ -37,29 +37,32 @@ --socket=name Socket file to use for connection --sort-buffer-size=# Each thread that needs to do a sort allocates a buffer of -@@ -1061,6 +1068,7 @@ +@@ -1086,6 +1093,7 @@ NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) + --standalone Dummy option to start as a standalone program (NT). --standard-compliant-cte - Allow only standards compiant CTE - (Defaults to on; use --skip-standards-compliant-cte to disable.) -@@ -1109,6 +1117,11 @@ + Allow only CTEs compliant to SQL standard + (Defaults to on; use --skip-standard-compliant-cte to disable.) +@@ -1134,8 +1142,12 @@ --thread-pool-max-threads=# Maximum allowed number of worker threads in the thread pool +- --thread-pool-oversubscribe=# +- How many additional active worker threads in a group are + --thread-pool-min-threads=# + Minimum number of threads in the thread pool. + --thread-pool-mode=name + Chose implementation of the threadpool. One of: windows, + generic - --thread-pool-oversubscribe=# - How many additional active worker threads in a group are ++ --thread-pool-oversubscribe=# How many additional active worker threads in a group are allowed. -@@ -1139,8 +1152,8 @@ - size, MySQL will automatically convert it to an on-disk - MyISAM or Aria table + --thread-pool-prio-kickup-timer=# + The number of milliseconds before a dequeued low-priority +@@ -1172,8 +1184,8 @@ + automatically convert it to an on-disk MyISAM or Aria + table. -t, --tmpdir=name Path for temporary files. Several paths may be specified, - separated by a colon (:), in this case they are used in a - round-robin fashion @@ -68,7 +71,7 @@ --transaction-alloc-block-size=# Allocation block size for transactions to be stored in binary log -@@ -1264,7 +1277,6 @@ +@@ -1298,7 +1310,6 @@ key-cache-division-limit 100 key-cache-file-hash-size 512 key-cache-segments 0 @@ -76,7 +79,7 @@ lc-messages en_US lc-messages-dir MYSQL_SHAREDIR/ lc-time-names en_US -@@ -1333,6 +1345,7 @@ +@@ -1368,6 +1379,7 @@ myisam-stats-method NULLS_UNEQUAL myisam-use-mmap FALSE mysql56-temporal-format TRUE @@ -84,7 +87,7 @@ net-buffer-length 16384 net-read-timeout 30 net-retry-count 10 -@@ -1434,6 +1447,8 @@ +@@ -1469,6 +1481,8 @@ session-track-state-change FALSE session-track-system-variables session-track-transaction-info OFF @@ -93,15 +96,15 @@ show-slave-auth-info FALSE silent-startup FALSE skip-grant-tables TRUE -@@ -1458,6 +1473,7 @@ +@@ -1493,6 +1507,7 @@ slave-type-conversions slow-launch-time 2 slow-query-log FALSE +slow-start-timeout 15000 sort-buffer-size 2097152 - sql-mode NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION + sql-mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION stack-trace TRUE -@@ -1471,14 +1487,16 @@ +@@ -1506,14 +1521,16 @@ sync-relay-log 10000 sync-relay-log-info 10000 sysdate-is-now FALSE diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 2630546624f..ac7de538f86 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1,4 +1,4 @@ -Windows bug: happens when a new line is exactly at the right offset +Windows bug: happens when a new line is exactly at the right offset. The following options may be given as the first argument: --print-defaults Print the program argument list and exit. --no-defaults Don't read default options from any option file. @@ -23,13 +23,15 @@ The following options may be given as the first argument: Creating and dropping stored procedures alters ACLs (Defaults to on; use --skip-automatic-sp-privileges to disable.) --back-log=# The number of outstanding connection requests MariaDB can - have. This comes into play when the main MySQL thread + have. This comes into play when the main MariaDB thread gets very many connection requests in a very short time (Automatically configured unless set explicitly) -b, --basedir=name Path to installation directory. All paths are usually resolved relative to this - --big-tables Allow big result sets by saving all temporary sets on - file (Solves most 'table full' errors) + --big-tables Old variable, which if set to 1, allows large result sets + by saving all temporary sets to disk, avoiding 'table + full' errors. No longer needed, as the server now handles + this automatically. sql_big_tables is a synonym. --bind-address=name IP address to bind to. --binlog-annotate-row-events Tells the master to annotate RBR events with the @@ -105,7 +107,7 @@ The following options may be given as the first argument: The size of the statement cache for updates to non-transactional engines for the binary log. If you often use statements updating a great number of rows, you - can increase this to get more performance + can increase this to get more performance. --bootstrap Used by mysql installation scripts. --bulk-insert-buffer-size=# Size of tree cache used in bulk insert optimisation. Note @@ -159,12 +161,21 @@ The following options may be given as the first argument: --default-week-format=# The default week format used by WEEK() functions --delay-key-write[=name] - Type of DELAY_KEY_WRITE. One of: OFF, ON, ALL + Specifies how MyISAM tables handles CREATE TABLE + DELAY_KEY_WRITE. If set to ON, the default, any DELAY KEY + WRITEs are honored. The key buffer is then flushed only + when the table closes, speeding up writes. MyISAM tables + should be automatically checked upon startup in this + case, and --external locking should not be used, as it + can lead to index corruption. If set to OFF, DELAY KEY + WRITEs are ignored, while if set to ALL, all new opened + tables are treated as if created with DELAY KEY WRITEs + enabled. --delayed-insert-limit=# After inserting delayed_insert_limit rows, the INSERT DELAYED handler will check if there are any SELECT statements pending. If so, it allows these to execute - before continuing + before continuing. --delayed-insert-timeout=# How long a INSERT DELAYED thread should wait for INSERT statements before terminating @@ -232,7 +243,7 @@ The following options may be given as the first argument: --gdb Set up signals usable for debugging. Deprecated, use --general-log Log connections and queries to a table or log file. Defaults logging to a file 'hostname'.log or a table - mysql.general_logif --log-output=TABLE is used + mysql.general_logif --log-output=TABLE is used. --general-log-file=name Log connections and queries to given file --getopt-prefix-matching @@ -377,7 +388,7 @@ The following options may be given as the first argument: logs. --log-slave-updates Tells the slave to log the updates from the slave thread to the binary log. You will need to turn it on if you - plan to daisy-chain the slaves + plan to daisy-chain the slaves. --log-slow-admin-statements Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. @@ -574,7 +585,7 @@ The following options may be given as the first argument: a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will - automatically pick a reasonable value + automatically pick a reasonable value. --optimizer-selectivity-sampling-limit=# Controls number of record samples to check condition selectivity @@ -762,7 +773,9 @@ The following options may be given as the first argument: The size of the buffer that is allocated when preloading indexes --profiling-history-size=# - Limit of query profiling memory + Number of statements about which profiling information is + maintained. If set to 0, no profiles are stored. See SHOW + PROFILES. --progress-report-time=# Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable @@ -803,21 +816,21 @@ The following options may be given as the first argument: --read-rnd-buffer-size=# When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks - --relay-log=name The location and name to use for relay logs + --relay-log=name The location and name to use for relay logs. --relay-log-index=name The location and name to use for the file that keeps a list of the last relay logs --relay-log-info-file=name The location and name of the file that remembers where - the SQL replication thread is in the relay logs + the SQL replication thread is in the relay logs. --relay-log-purge if disabled - do not purge relay logs. if enabled - purge - them as soon as they are no more needed + them as soon as they are no more needed. (Defaults to on; use --skip-relay-log-purge to disable.) --relay-log-recovery Enables automatic relay log recovery right after the database startup, which means that the IO Thread starts re-fetching from the master right after the last - transaction processed + transaction processed. --relay-log-space-limit=# Maximum space to use for all relay logs --replicate-annotate-row-events @@ -982,7 +995,7 @@ The following options may be given as the first argument: idempotent. For example, in row based replication attempts to delete rows that doesn't exist will be ignored. In STRICT mode, replication will stop on any - unexpected difference between the master and the slave + unexpected difference between the master and the slave. --slave-load-tmpdir=name The location where the slave should put its temporary files when replicating a LOAD DATA INFILE command @@ -1050,7 +1063,7 @@ The following options may be given as the first argument: --slow-query-log Log slow queries to a table or log file. Defaults logging to a file 'hostname'-slow.log or a table mysql.slow_log if --log-output=TABLE is used. Must be enabled to - activate other slow log options + activate other slow log options. --slow-query-log-file=name Log slow queries to given log file. Defaults logging to 'hostname'-slow.log. Must be enabled to activate other @@ -1152,10 +1165,10 @@ The following options may be given as the first argument: MyISAM or Aria table. --tmp-memory-table-size=# If an internal in-memory temporary table exceeds this - size, MySQL will automatically convert it to an on-disk + size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. --tmp-table-size=# Alias for tmp_memory_table_size. If an internal in-memory - temporary table exceeds this size, MySQL will + temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. -t, --tmpdir=name Path for temporary files. Several paths may be specified, diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index e6fc5e670ef..2037dcac182 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -34,6 +34,8 @@ create temporary table t3 (a int); create temporary table t4 (a int) select * from t3; insert into t3 values(1); insert into t4 select * from t3; +create table t3 (a int); +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a; diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result index 0eddfa70dd1..5bb3b17d4b8 100644 --- a/mysql-test/r/sp-destruct.result +++ b/mysql-test/r/sp-destruct.result @@ -12,7 +12,7 @@ create function bug14233_f() returns int return 42; create table t1 (id int); create trigger t1_ai after insert on t1 for each row call bug14233(); -alter table mysql.proc drop type; +alter table mysql.proc drop security_type; call bug14233(); ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted create view v1 as select bug14233_f(); @@ -142,7 +142,7 @@ drop database if exists mysqltest; flush table mysql.proc; create database mysqltest; # Corrupt mysql.proc to make it unusable by current version of server. -alter table mysql.proc drop column type; +alter table mysql.proc drop column security_type; # The below statement should not cause assertion failure. drop database mysqltest; Warnings: diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 53626d8013e..a8f3c57b2fd 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -8129,6 +8129,23 @@ c 1 DROP PROCEDURE p1; DROP TABLE t1; # +# MDEV-13346: CURSOR a query with GROUP BY using derived table +# +CREATE PROCEDURE p1() +BEGIN +DECLARE c CURSOR FOR +SELECT +IFNULL(NULL,1) AS col +FROM +( select 1 as id ) AS t +GROUP BY t.id +; +OPEN c; +END +| +CALL p1(); +DROP PROCEDURE p1; +# # Start of 10.3 tests # # diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index a64a69ad368..ffaaf09acc8 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1162,7 +1162,7 @@ test t2 idx4 1 6.2000 test t2 idx4 2 1.7222 test t2 idx4 3 1.1154 test t2 idx4 4 1.0000 -ALTER TABLE t2 DROP COLUMN b; +ALTER TABLE t2 DROP COLUMN b, DROP PRIMARY KEY, ADD PRIMARY KEY(a); SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency test t2 idx2 1 7.0000 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 9f07b1fefff..919693efffb 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7201,6 +7201,21 @@ SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; ERROR 21000: Subquery returns more than 1 row drop view v1; drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; # End of 10.0 tests # # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index e4b57d96b38..806475b3380 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7201,6 +7201,21 @@ SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; ERROR 21000: Subquery returns more than 1 row drop view v1; drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; # End of 10.0 tests # # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 22082774668..237a6dbf9bb 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7194,6 +7194,21 @@ SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; ERROR 21000: Subquery returns more than 1 row drop view v1; drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; # End of 10.0 tests # # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 5f71700c4ce..af1afe47f32 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7192,6 +7192,21 @@ SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; ERROR 21000: Subquery returns more than 1 row drop view v1; drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; # End of 10.0 tests # # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 8500fa2434c..75be8642069 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7207,6 +7207,21 @@ SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; ERROR 21000: Subquery returns more than 1 row drop view v1; drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; # End of 10.0 tests # # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 48bf9ce90c9..a6a6397375b 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7192,6 +7192,21 @@ SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; ERROR 21000: Subquery returns more than 1 row drop view v1; drop table t1,t2; +CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM; +INSERT t1 VALUES (4),(8); +CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT t2 VALUES (6); +SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +(SELECT MAX(sq.f2) FROM t1) +NULL +# +# Disable this query till MDEV-13399 is resolved +# +# INSERT t2 VALUES (9); +# --error ER_SUBQUERY_NO_1_ROW +# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; +# +drop table t1, t2; # End of 10.0 tests # # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops diff --git a/mysql-test/r/subselect_nulls.result b/mysql-test/r/subselect_nulls.result index 584c184870d..08982371269 100644 --- a/mysql-test/r/subselect_nulls.result +++ b/mysql-test/r/subselect_nulls.result @@ -115,3 +115,9 @@ k d1 d2 set optimizer_switch= @tmp_subselect_nulls; drop table x1; drop table x2; +select (select 1, 2) in (select 3, 4); +(select 1, 2) in (select 3, 4) +0 +select (select NULL, NULL) in (select 3, 4); +(select NULL, NULL) in (select 3, 4) +NULL diff --git a/mysql-test/r/type_json.result b/mysql-test/r/type_json.result index a3e55a6cdd1..2c1fdbe2b95 100644 --- a/mysql-test/r/type_json.result +++ b/mysql-test/r/type_json.result @@ -2,19 +2,21 @@ create or replace table t1(a json); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` text DEFAULT NULL + `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -create or replace table t1(a json character set utf8 default '{a:1}'); +create or replace table t1(a json character set utf8); +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 'character set utf8)' at line 1 +create or replace table t1(a json default '{a:1}'); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` text CHARACTER SET utf8 DEFAULT '{a:1}' + `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '{a:1}' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -create or replace table t1(a json binary not null check (json_valid(a))); +create or replace table t1(a json not null check (json_valid(a))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` text CHARACTER SET latin1 COLLATE latin1_bin NOT NULL CHECK (json_valid(`a`)) + `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`a`)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert t1 values ('[]'); insert t1 values ('a'); @@ -24,7 +26,7 @@ create or replace table t1(a json default(json_object('now', now()))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` text DEFAULT json_object('now',current_timestamp()) + `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT json_object('now',current_timestamp()) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert t1 values (); select * from t1; diff --git a/mysql-test/r/type_num.result b/mysql-test/r/type_num.result index 193a59741f5..966d94ee385 100644 --- a/mysql-test/r/type_num.result +++ b/mysql-test/r/type_num.result @@ -1219,9 +1219,6 @@ Warning 1292 Truncated incorrect INTEGER value: '1E+' Warning 1292 Truncated incorrect DECIMAL value: '1E+' Warning 1292 Truncated incorrect DOUBLE value: '1E+' # -# End of 10.0 tests -# -# # Start of 10.1 tests # # @@ -1247,5 +1244,17 @@ Warning 1292 Truncated incorrect DOUBLE value: '' Warning 1292 Truncated incorrect DOUBLE value: '' Warning 1292 Truncated incorrect DOUBLE value: '' # -# Start of 10.1 tests +# Start of 10.2 tests # +CREATE TABLE t1 (a INT SIGNED ZEROFILL); +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 'ZEROFILL)' at line 1 +CREATE TABLE t1 (a INT SIGNED UNSIGNED); +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 'UNSIGNED)' at line 1 +CREATE TABLE t1 (a INT ZEROFILL UNSIGNED ZEROFILL); +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 'ZEROFILL)' at line 1 +CREATE OR REPLACE TABLE t1 (a INT SIGNED); +CREATE OR REPLACE TABLE t1 (a INT UNSIGNED); +CREATE OR REPLACE TABLE t1 (a INT ZEROFILL); +CREATE OR REPLACE TABLE t1 (a INT UNSIGNED ZEROFILL); +CREATE OR REPLACE TABLE t1 (a INT ZEROFILL UNSIGNED); +DROP TABLE t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 956703237a1..b193f032146 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -2077,6 +2077,24 @@ d 2016-06-04 00:00:00 drop table t1; End of 5.0 tests +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2 (c varchar(30), d varchar(30)); +insert into t1 values ('1','1'),('2','2'),('4','4'); +create table t3 (e int, f int); +insert into t3 values (1,1),(2,2),(31,31),(32,32); +select e,f, (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3; +e f sub +1 1 1 +2 2 1 +31 31 0 +32 32 0 +select avg(f), (e , f) in (select e,b from t1 union select c,d from t2) as sub from t3 group by sub; +avg(f) sub +31.5000 0 +1.5000 1 +drop table t1,t2,t3; +End of 5.5 tests # # WL#1763 Avoid creating temporary table in UNION ALL # diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 721ad052935..d0d57e1b1e9 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5680,6 +5680,17 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`f` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t3`.`e` and `test`.`t1`.`a` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null) where `test`.`t1`.`a` < 5 drop view v1; drop table t1,t2,t3; +# +# MDEV-11240: Server crashes in check_view_single_update or +# Assertion `derived->table' failed in mysql_derived_merge_for_insert +# +CREATE TABLE t3 (a INT); +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2; +CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; +PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; +ERROR HY000: Can not insert into join view 'test.v2' without fields list +drop view v1,v2; +drop table t3; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- |