diff options
Diffstat (limited to 'mysql-test')
75 files changed, 1938 insertions, 198 deletions
diff --git a/mysql-test/r/almost_full.result b/mysql-test/r/almost_full.result new file mode 100644 index 00000000000..eb28f12fa51 --- /dev/null +++ b/mysql-test/r/almost_full.result @@ -0,0 +1,29 @@ +drop table if exists t1; +set global myisam_data_pointer_size=2; +CREATE TABLE t1 (a int auto_increment primary key not null, b longtext) ENGINE=MyISAM; +DELETE FROM t1 WHERE a=1 or a=5; +INSERT INTO t1 SET b=repeat('a',600); +ERROR HY000: The table 't1' is full +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +UPDATE t1 SET b=repeat('a', 800) where a=10; +ERROR HY000: The table 't1' is full +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +INSERT INTO t1 SET b=repeat('a',400); +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +DELETE FROM t1 WHERE a=2 or a=6; +UPDATE t1 SET b=repeat('a', 600) where a=11; +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +drop table t1; +set global myisam_data_pointer_size=default; diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index 0a473a16771..77d3cba63d5 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12683,3 +12683,7 @@ check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +CREATE TABLE t1(a VARCHAR(510)) ENGINE = ARCHIVE; +INSERT INTO t1(a) VALUES (''); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; diff --git a/mysql-test/r/binlog_unsafe.result b/mysql-test/r/binlog_unsafe.result index 281bb475944..47284ed8bc3 100644 --- a/mysql-test/r/binlog_unsafe.result +++ b/mysql-test/r/binlog_unsafe.result @@ -5,9 +5,9 @@ CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY); CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3; INSERT INTO t1 SELECT UUID(); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. SHOW WARNINGS; Level Warning -Code 1591 +Code 1592 Message Statement is not safe to log in statement format. DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index afd0a5a990e..c1af92c5f8d 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -414,4 +414,28 @@ NULL NULL 20070719 drop table t1; +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), +MINUTE(f1), +SECOND(f1) FROM t1; +HOUR(f1) MINUTE(f1) SECOND(f1) +0 0 0 +NULL NULL NULL +SELECT HOUR(CAST('2007-07-19' AS DATE)), +MINUTE(CAST('2007-07-19' AS DATE)), +SECOND(CAST('2007-07-19' AS DATE)); +HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE)) +0 0 0 +SELECT HOUR(CAST(NULL AS DATE)), +MINUTE(CAST(NULL AS DATE)), +SECOND(CAST(NULL AS DATE)); +HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE)) +NULL NULL NULL +SELECT HOUR(NULL), +MINUTE(NULL), +SECOND(NULL); +HOUR(NULL) MINUTE(NULL) SECOND(NULL) +NULL NULL NULL +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index b6b77101874..83609856393 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -31,7 +31,7 @@ create event e_55 on schedule at 10000101000000 do drop table t; ERROR HY000: Incorrect AT value: '10000101000000' create event e_55 on schedule at 20000101000000 do drop table t; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. show events; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; @@ -457,22 +457,22 @@ CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci @@ -482,19 +482,19 @@ The following should succeed giving a warning. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; Warnings: -Note 1543 Event execution time is in the past. Event has been disabled +Note 1544 Event execution time is in the past. Event has been disabled CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1543 Event execution time is in the past. Event has been disabled +Note 1544 Event execution time is in the past. Event has been disabled CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1543 Event execution time is in the past. Event has been disabled +Note 1544 Event execution time is in the past. Event has been disabled The following should succeed without warnings. ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result index 984e22a2c1a..16ec64b4c50 100644 --- a/mysql-test/r/events_trans.result +++ b/mysql-test/r/events_trans.result @@ -63,7 +63,7 @@ begin work; insert into t1 (a) values ("OK: create event if not exists"); create event if not exists e1 on schedule every 2 day do select 2; Warnings: -Note 1536 Event 'e1' already exists +Note 1537 Event 'e1' already exists rollback work; select * from t1; a diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 7ae7806baea..d257e526abf 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -867,4 +867,13 @@ select group_concat(distinct a, c order by a desc, c desc) from t1; group_concat(distinct a, c order by a desc, c desc) 31,11,10,01,00 drop table t1; +create table t1 (f1 char(20)); +insert into t1 values (''),(''); +select group_concat(distinct f1) from t1; +group_concat(distinct f1) + +select group_concat(f1) from t1; +group_concat(f1) +, +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index ce29d25b736..abf717091af 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1378,4 +1378,24 @@ SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ); 1 1 DROP TABLE t1; +CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b)); +SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01'; +MIN(b) +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +SET SQL_MODE=ONLY_FULL_GROUP_BY; +SELECT a FROM t1 HAVING COUNT(*)>2; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT COUNT(*), a FROM t1; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SET SQL_MODE=DEFAULT; +SELECT a FROM t1 HAVING COUNT(*)>2; +a +1 +SELECT COUNT(*), a FROM t1; +COUNT(*) a +4 1 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 78d86d3f342..6f6edd5112b 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -722,9 +722,9 @@ Warning 1265 Data truncated for column 'format(130,10)' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `bin(130)` varchar(64) NOT NULL DEFAULT '', - `oct(130)` varchar(64) NOT NULL DEFAULT '', - `conv(130,16,10)` varchar(64) NOT NULL DEFAULT '', + `bin(130)` varchar(64) DEFAULT NULL, + `oct(130)` varchar(64) DEFAULT NULL, + `conv(130,16,10)` varchar(64) DEFAULT NULL, `hex(130)` varchar(6) NOT NULL DEFAULT '', `char(130)` varbinary(4) NOT NULL DEFAULT '', `format(130,10)` varchar(4) NOT NULL DEFAULT '', @@ -1316,6 +1316,18 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select decode('',_latin1'zxcv') AS `enc` from `test`.`t1` drop table t1; +create table t1 (a bigint not null)engine=myisam; +insert into t1 set a = 1024*1024*1024*4; +delete from t1 order by (inet_ntoa(a)) desc limit 10; +drop table t1; +create table t1 (a char(36) not null)engine=myisam; +insert ignore into t1 set a = ' '; +insert ignore into t1 set a = ' '; +select * from t1 order by (oct(a)); +a + + +drop table t1; End of 4.1 tests create table t1 (d decimal default null); insert into t1 values (null); diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 4e25ada43a0..4d402d7d0e9 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -529,7 +529,7 @@ Db char(64) NO PRI User char(16) NO PRI Table_name char(64) NO PRI Grantor char(77) NO MUL -Timestamp timestamp NO CURRENT_TIMESTAMP +Timestamp timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') NO Column_priv set('Select','Insert','Update','References') NO use test; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 7498267c91d..1472fdfdd7a 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1064,6 +1064,71 @@ select t1.f1,t.* from t1, t1 t group by 1; ERROR 42000: 'test.t.f1' isn't in GROUP BY drop table t1; SET SQL_MODE = ''; +CREATE TABLE t1( +id INT AUTO_INCREMENT PRIMARY KEY, +c1 INT NOT NULL, +c2 INT NOT NULL, +UNIQUE KEY (c2,c1)); +INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3); +SELECT * FROM t1 ORDER BY c1; +id c1 c2 +5 1 3 +4 2 3 +3 3 5 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY id ORDER BY c1; +id c1 c2 +5 1 3 +4 2 3 +3 3 5 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY id ORDER BY id DESC; +id c1 c2 +5 1 3 +4 2 3 +3 3 5 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1; +id c1 c2 +2 4 1 +1 5 1 +5 1 3 +4 2 3 +3 3 5 +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1; +id c1 c2 +3 3 5 +5 1 3 +4 2 3 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC; +id c1 c2 +3 3 5 +4 2 3 +5 1 3 +1 5 1 +2 4 1 +SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1; +id c1 c2 +1 5 1 +4 2 3 +3 3 5 +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1; +id c1 c2 +3 3 5 +4 2 3 +1 5 1 +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC; +id c1 c2 +3 3 5 +4 2 3 +1 5 1 +DROP TABLE t1; +End of 5.0 tests CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY i2(a,b)); diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 7693fe628ef..4df3b56da86 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1141,11 +1141,13 @@ DROP FUNCTION func2; select column_type, group_concat(table_schema, '.', table_name), count(*) as num from information_schema.columns where table_schema='information_schema' and -(column_type = 'varchar(7)' or column_type = 'varchar(20)') +(column_type = 'varchar(7)' or column_type = 'varchar(20)' + or column_type = 'varchar(27)') group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num +varchar(27) information_schema.COLUMNS 1 varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 -varchar(20) information_schema.COLUMNS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 6 +varchar(20) information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 5 create table t1(f1 char(1) not null, f2 char(9) not null) default character set utf8; select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from @@ -1604,4 +1606,9 @@ select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION +explain extended select 1 from information_schema.tables; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE tables ALL NULL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +Warnings: +Note 1003 select 1 AS `1` from `information_schema`.`tables` End of 5.1 tests. diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 51f7694ab68..2da4e305b01 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -141,7 +141,7 @@ Warnings: Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them show fields from testdb_1.v7; Field Type Null Key Default Extra -f1 null YES NULL +f1 char(4) YES NULL Warnings: Note 1449 There is no 'no_such_user'@'no_such_host' registered create table t3 (f1 char(4), f2 char(4)); @@ -161,7 +161,7 @@ View Create View character_set_client collation_connection v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v6` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` latin1 latin1_swedish_ci show fields from testdb_1.v7; Field Type Null Key Default Extra -f1 null YES NULL +f1 char(4) YES NULL Warnings: Note 1449 There is no 'no_such_user'@'no_such_host' registered show create view testdb_1.v7; @@ -189,7 +189,7 @@ show create view v4; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table show fields from v4; Field Type Null Key Default Extra -f1 null YES NULL +f1 char(4) YES NULL f2 char(4) YES NULL show fields from v2; Field Type Null Key Default Extra diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 3a3ef584ce3..b0ec0d25935 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -175,3 +175,66 @@ SET GLOBAL slow_query_log = ON; SET GLOBAL READ_ONLY = OFF; SET GLOBAL general_log = @old_general_log_state; SET GLOBAL slow_query_log = @old_slow_log_state; +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; +SHOW VARIABLES LIKE 'general_log'; +Variable_name Value +general_log ON +SHOW VARIABLES LIKE 'log'; +Variable_name Value +log ON +SELECT @@general_log, @@log; +@@general_log @@log +1 1 +SET GLOBAL log = 0; +SHOW VARIABLES LIKE 'general_log'; +Variable_name Value +general_log OFF +SHOW VARIABLES LIKE 'log'; +Variable_name Value +log OFF +SELECT @@general_log, @@log; +@@general_log @@log +0 0 +SET GLOBAL general_log = 1; +SHOW VARIABLES LIKE 'general_log'; +Variable_name Value +general_log ON +SHOW VARIABLES LIKE 'log'; +Variable_name Value +log ON +SELECT @@general_log, @@log; +@@general_log @@log +1 1 +SHOW VARIABLES LIKE 'slow_query_log'; +Variable_name Value +slow_query_log OFF +SHOW VARIABLES LIKE 'log_slow_queries'; +Variable_name Value +log_slow_queries OFF +SELECT @@slow_query_log, @@log_slow_queries; +@@slow_query_log @@log_slow_queries +0 0 +SET GLOBAL log_slow_queries = 0; +SHOW VARIABLES LIKE 'slow_query_log'; +Variable_name Value +slow_query_log OFF +SHOW VARIABLES LIKE 'log_slow_queries'; +Variable_name Value +log_slow_queries OFF +SELECT @@slow_query_log, @@log_slow_queries; +@@slow_query_log @@log_slow_queries +0 0 +SET GLOBAL slow_query_log = 1; +SHOW VARIABLES LIKE 'slow_query_log'; +Variable_name Value +slow_query_log ON +SHOW VARIABLES LIKE 'log_slow_queries'; +Variable_name Value +log_slow_queries ON +SELECT @@slow_query_log, @@log_slow_queries; +@@slow_query_log @@log_slow_queries +1 1 +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; +End of 5.1 tests diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 261e3292f4d..f3578598195 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -50,7 +50,7 @@ general_log CREATE TABLE `general_log` ( ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show fields from mysql.general_log; Field Type Null Key Default Extra -event_time timestamp NO CURRENT_TIMESTAMP +event_time timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP user_host mediumtext NO NULL thread_id int(11) NO NULL server_id int(11) NO NULL @@ -73,7 +73,7 @@ slow_log CREATE TABLE `slow_log` ( ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show fields from mysql.slow_log; Field Type Null Key Default Extra -start_time timestamp NO CURRENT_TIMESTAMP +start_time timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP user_host mediumtext NO NULL query_time time NO NULL lock_time time NO NULL @@ -820,3 +820,31 @@ Execute select '000 001 002 003 004 005 006 007 008 009010 011 012 013 014 015 0 Query set global general_log = off deallocate prepare long_query; set global general_log = @old_general_log_state; +SET @old_slow_log_state = @@global.slow_query_log; +SET SESSION long_query_time = 0; +SET GLOBAL slow_query_log = ON; +FLUSH LOGS; +TRUNCATE TABLE mysql.slow_log; +CREATE TABLE t1 (f1 SERIAL,f2 INT, f3 INT, PRIMARY KEY(f1), KEY(f2)); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (2,2,2); +INSERT INTO t1 VALUES (3,3,3); +INSERT INTO t1 VALUES (4,4,4); +SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3 FROM t1 WHERE f3=4; +Bug#31700 - SCAN f1 f2 f3 +Bug#31700 - SCAN 4 4 4 +SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3 FROM t1 WHERE f2=3; +Bug#31700 - KEY f1 f2 f3 +Bug#31700 - KEY 3 3 3 +SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3 FROM t1 WHERE f1=2; +Bug#31700 - PK f1 f2 f3 +Bug#31700 - PK 2 2 2 +SELECT start_time, rows_examined, rows_sent, sql_text FROM mysql.slow_log WHERE sql_text LIKE '%Bug#31700%' ORDER BY start_time; +start_time rows_examined rows_sent sql_text +TIMESTAMP 4 1 SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3 FROM t1 WHERE f3=4 +TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3 FROM t1 WHERE f2=3 +TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3 FROM t1 WHERE f1=2 +DROP TABLE t1; +TRUNCATE TABLE mysql.slow_log; +SET GLOBAL slow_query_log = @old_slow_log_state; +SET SESSION long_query_time =@old_long_query_time; diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result index 3653461c6b8..c37dc41c495 100644 --- a/mysql-test/r/lowercase_view.result +++ b/mysql-test/r/lowercase_view.result @@ -119,7 +119,7 @@ create table t1Aa (col1 int); create view v1Aa as select col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; select Aaa.col1 from t1Aa as AaA; col1 @@ -128,6 +128,23 @@ drop view v1AA; create view v1Aa as select AaA.col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; drop table t1Aa; +CREATE TABLE t1 (a int, b int); +select X.a from t1 AS X group by X.b having (X.a = 1); +a +select X.a from t1 AS X group by X.b having (x.a = 1); +a +select X.a from t1 AS X group by X.b having (x.b = 1); +a +CREATE OR REPLACE VIEW v1 AS +select X.a from t1 AS X group by X.b having (X.a = 1); +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `x`.`a` AS `a` from `t1` `x` group by `x`.`b` having (`x`.`a` = 1) latin1 latin1_swedish_ci +SELECT * FROM v1; +a +DROP VIEW v1; +DROP TABLE t1; +End of 5.0 tests. diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 4a776b6a253..76c6ae7d034 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -21,7 +21,7 @@ def test t1 t1 g g 5 4 0 Y 32768 3 63 def test t1 t1 h h 246 7 0 Y 0 4 63 def test t1 t1 i i 13 4 0 Y 32864 0 63 def test t1 t1 j j 10 10 0 Y 128 0 63 -def test t1 t1 k k 7 19 0 N 1249 0 63 +def test t1 t1 k k 7 19 0 N 9441 0 63 def test t1 t1 l l 12 19 0 Y 128 0 63 def test t1 t1 m m 254 1 0 Y 256 0 8 def test t1 t1 n n 254 3 0 Y 2048 0 8 diff --git a/mysql-test/r/mysql_comments.result b/mysql-test/r/mysql_comments.result new file mode 100644 index 00000000000..7f1c0b50c5e --- /dev/null +++ b/mysql-test/r/mysql_comments.result @@ -0,0 +1,62 @@ +drop table if exists t1; +drop function if exists foofct; +drop procedure if exists empty; +drop procedure if exists foosp; +drop procedure if exists nicesp; +drop trigger if exists t1_empty; +drop trigger if exists t1_bi; +"Pass 1 : --disable-comments" +1 +1 +2 +2 +foofct("call 1") +call 1 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nreturn\n\n\n\nx latin1 latin1_swedish_ci latin1_swedish_ci +foofct("call 2") +call 2 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nbegin\n \n \n \n\n \n\n \n return x;\nend latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +empty CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +foo 42 +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n\n\n\n\n \n\n \n values ("foo", 42) latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n \n declare b int;\n declare c float;\n\n \n \n\n \nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n\n\n\n \n declare b int;\n declare c float;\n\n \n \n\n \n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +trig 12 +"Pass 2 : --enable-comments" +1 +1 +2 +2 +foofct("call 1") +call 1 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nreturn\n-- comment 1a\n# comment 1b\n/* comment 1c */\nx # after body, on same line latin1 latin1_swedish_ci latin1_swedish_ci +foofct("call 2") +call 2 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nbegin\n -- comment 1a\n # comment 1b\n /*\n comment 1c\n */\n\n -- empty line below\n\n -- empty line above\n return x;\nend latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +empty CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +foo 42 +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n## These comments are part of the procedure body, and should be kept.\n# Comment 2a\n-- Comment 2b\n/* Comment 2c */\n -- empty line below\n\n -- empty line above\n values ("foo", 42) # comment 3, still part of the body latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n# comment 1a\n-- comment 1b\n/*\n comment 1c\n*/\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +trig 12 +End of 5.0 tests diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index c0856314ba5..d07aed5317a 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -4212,5 +4212,24 @@ TRUNCATE mysql.event; SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation # +# Bug#31113 mysqldump 5.1 can't handle a dash ("-") in database names +# +create database `test-database`; +use `test-database`; +create table test (a int); +DROP TABLE IF EXISTS `test`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `test` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +SET character_set_client = @saved_cs_client; +LOCK TABLES `test` WRITE; +/*!40000 ALTER TABLE `test` DISABLE KEYS */; +/*!40000 ALTER TABLE `test` ENABLE KEYS */; +UNLOCK TABLES; +drop database `test-database`; +use test; +# # End of 5.1 tests # diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result new file mode 100644 index 00000000000..1bcaf308b7c --- /dev/null +++ b/mysql-test/r/outfile_loaddata.result @@ -0,0 +1,85 @@ +DROP TABLE IF EXISTS t1, t2; +# +# Bug#31663 FIELDS TERMINATED BY special character +# +CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256)); +INSERT INTO t1 VALUES (101, 202, '-r-', '=raker='); +# FIELDS TERMINATED BY 'raker', warning: +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'raker' FROM t1; +Warnings: +Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +101raker202raker-r-raker=raker= + +CREATE TABLE t2 SELECT * FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'raker'; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +SELECT * FROM t2; +i1 i2 c1 c2 +101 202 -r- =raker= +101 202 -r- = +DROP TABLE t2; +# Only numeric fields, FIELDS TERMINATED BY 'r', no warnings: +SELECT i1, i2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'r' FROM t1; +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +101r202 + +CREATE TABLE t2 SELECT i1, i2 FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'r'; +SELECT i1, i2 FROM t2; +i1 i2 +101 202 +101 202 +DROP TABLE t2; +# FIELDS TERMINATED BY '0', warning: +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY '0' FROM t1; +Warnings: +Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +10102020-r-0=raker= + +CREATE TABLE t2 SELECT * FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY '0'; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +SELECT * FROM t2; +i1 i2 c1 c2 +101 202 -r- =raker= +1 1 2 2 +DROP TABLE t2; +# FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', warning: +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1; +Warnings: +Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +10102020"-r-"0"=raker=" + +CREATE TABLE t2 SELECT * FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +SELECT * FROM t2; +i1 i2 c1 c2 +101 202 -r- =raker= +1 1 2 2 +DROP TABLE t2; +# Only string fields, FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', no warnings: +SELECT c1, c2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1; +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +"-r-"0"=raker=" + +CREATE TABLE t2 SELECT c1, c2 FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'; +SELECT c1, c2 FROM t2; +c1 c2 +-r- =raker= +-r- =raker= +DROP TABLE t2; +DROP TABLE t1; +# End of 5.0 tests. diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index aa6a64ac76e..d646226daf8 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1285,4 +1285,9 @@ t1 CREATE TABLE `t1` ( `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (b) (PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (20) ENGINE = MyISAM) */ drop table t1, t2; +create table t1 +(s1 timestamp on update current_timestamp, s2 int) +partition by key(s1) partitions 3; +insert into t1 values (null,null); +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 5b755b6bfd5..f80e0001ea8 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -136,3 +136,16 @@ SELECT COUNT(*) FROM t1; COUNT(*) 2 DROP TABLE t1; +create table t1 (int_column int, char_column char(5)) +PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 +(PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); +alter table t1 PARTITION BY RANGE (int_column) +subpartition by key (char_column) subpartitions 2 +(PARTITION p1 VALUES LESS THAN (5) ENGINE = myisam); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `int_column` int(11) DEFAULT NULL, + `char_column` char(5) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (int_column) SUBPARTITION BY KEY (char_column) SUBPARTITIONS 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ +drop table t1; diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index 8628acecf55..e4af1535775 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -17,3 +17,13 @@ UNINSTALL PLUGIN EXAMPLE; ERROR 42000: PLUGIN EXAMPLE does not exist UNINSTALL PLUGIN non_exist; ERROR 42000: PLUGIN non_exist does not exist +# +# Bug#32034: check_func_enum() does not check correct values but set it +# to impossible int val +# +INSTALL PLUGIN example SONAME 'ha_example.so'; +SET GLOBAL example_enum_var= e1; +SET GLOBAL example_enum_var= e2; +SET GLOBAL example_enum_var= impossible; +ERROR 42000: Variable 'enum_var' can't be set to the value of 'impossible' +UNINSTALL PLUGIN example; diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 6f0bad71f23..eb8ef7a85c3 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 85a109b0630..15650efa41d 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 980b89ad504..486f770220e 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -64,7 +64,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index e95f8766a24..0229b0ece08 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -106,7 +106,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 @@ -3128,7 +3128,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 1acb82e4137..719d0926022 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2827,6 +2827,14 @@ FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 8FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF drop table t1; +CREATE TABLE t1 (c0 int); +CREATE TABLE t2 (c0 int); +INSERT INTO t1 VALUES(@@connect_timeout); +INSERT INTO t2 VALUES(@@connect_timeout); +SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); +c0 c0 +X X +DROP TABLE t1, t2; End of 4.1 tests CREATE TABLE t1 ( K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', @@ -3233,40 +3241,40 @@ drop table t1, t2 ,t3; create table t1(f1 int, f2 date); insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), (4,'2005-10-01'),(5,'2005-12-30'); -select * from t1 where f2 >= 0; +select * from t1 where f2 >= 0 order by f2; f1 f2 1 2005-01-01 2 2005-09-01 3 2005-09-30 4 2005-10-01 5 2005-12-30 -select * from t1 where f2 >= '0000-00-00'; +select * from t1 where f2 >= '0000-00-00' order by f2; f1 f2 1 2005-01-01 2 2005-09-01 3 2005-09-30 4 2005-10-01 5 2005-12-30 -select * from t1 where f2 >= '2005-09-31'; +select * from t1 where f2 >= '2005-09-31' order by f2; f1 f2 4 2005-10-01 5 2005-12-30 -select * from t1 where f2 >= '2005-09-3a'; +select * from t1 where f2 >= '2005-09-3a' order by f2; f1 f2 +3 2005-09-30 4 2005-10-01 5 2005-12-30 Warnings: Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 -select * from t1 where f2 <= '2005-09-31'; +select * from t1 where f2 <= '2005-09-31' order by f2; f1 f2 1 2005-01-01 2 2005-09-01 3 2005-09-30 -select * from t1 where f2 <= '2005-09-3a'; +select * from t1 where f2 <= '2005-09-3a' order by f2; f1 f2 1 2005-01-01 2 2005-09-01 -3 2005-09-30 Warnings: Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 drop table t1; @@ -4062,25 +4070,208 @@ x 1 Warnings: Warning 1466 Leading spaces are removed from name ' x' +CREATE VIEW v1 AS SELECT 1 AS ``; +ERROR 42000: Incorrect column name '' CREATE VIEW v1 AS SELECT 1 AS ` `; +ERROR 42000: Incorrect column name ' ' +CREATE VIEW v1 AS SELECT 1 AS ` `; +ERROR 42000: Incorrect column name ' ' +CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); +ERROR 42000: Incorrect column name ' ' +CREATE VIEW v1 AS SELECT 1 AS ` x`; Warnings: -Warning 1474 Name ' ' has become '' -SELECT `` FROM v1; - +Warning 1466 Leading spaces are removed from name ' x' +SELECT `x` FROM v1; +x +1 +ALTER VIEW v1 AS SELECT 1 AS ` `; +ERROR 42000: Incorrect column name ' ' +DROP VIEW v1; +select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' + and '2007/10/20 00:00:00 GMT'; +str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' + and '2007/10/20 00:00:00 GMT' 1 -CREATE VIEW v2 AS SELECT 1 AS ` `; Warnings: -Warning 1474 Name ' ' has become '' -SELECT `` FROM v2; - +Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' +Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' +select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; +str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 1 -CREATE VIEW v3 AS SELECT 1 AS ` x`; Warnings: -Warning 1466 Leading spaces are removed from name ' x' -SELECT `x` FROM v3; -x +Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6' +select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; +str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6' +select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; +str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56' +1 +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; +str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00' +0 +select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00' +1 +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34' +select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; +str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34' +1 +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' +1 +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' + and '2007/10/20 00:00:00'; +str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' + and '2007/10/20 00:00:00' +1 +set SQL_MODE=TRADITIONAL; +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34:00' +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' + and '2007/10/20'; +str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' + and '2007/10/20' +0 +Warnings: +Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/09/01' at row 1 +Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/10/20' at row 1 +set SQL_MODE=DEFAULT; +select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; +str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '' +select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; +str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' +0 +select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34' +0 +select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34' +NULL +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '' +select str_to_date('1','%Y-%m-%d') = '1'; +str_to_date('1','%Y-%m-%d') = '1' +0 +Warnings: +Warning 1292 Truncated incorrect date value: '1' +select str_to_date('1','%Y-%m-%d') = '1'; +str_to_date('1','%Y-%m-%d') = '1' +0 +Warnings: +Warning 1292 Truncated incorrect date value: '1' +select str_to_date('','%Y-%m-%d') = ''; +str_to_date('','%Y-%m-%d') = '' +0 +Warnings: +Warning 1292 Truncated incorrect date value: '' +select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; +str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL +0 +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; +str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00' +0 +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; +str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL +0 +CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, +c22 INT DEFAULT NULL, +KEY(c21, c22)); +CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, +c32 INT DEFAULT NULL, +c33 INT NOT NULL, +c34 INT UNSIGNED DEFAULT 0, +KEY (c33, c34, c32)); +INSERT INTO t1 values (),(),(),(),(); +INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; +INSERT INTO t3 VALUES (1, 1, 1, 0), +(2, 2, 0, 0), +(3, 3, 1, 0), +(4, 4, 0, 0), +(5, 5, 1, 0); +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND +t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND +t3.c33 = 1 AND t2.c22 in (1, 3) +ORDER BY c32; +c32 +1 +1 +3 +3 +5 +5 +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND +t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND +t3.c33 = 1 AND t2.c22 in (1, 3) +ORDER BY c32 DESC; +c32 +5 +5 +3 +3 +1 1 -DROP VIEW v1, v2, v3; +DROP TABLE t1, t2, t3; # # Bug#30736: Row Size Too Large Error Creating a Table and diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 04a5253e6a8..ff32d2a1512 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -979,7 +979,7 @@ def COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 192 0 Y 0 0 33 def COLUMNS COLLATION_NAME COLLATION_NAME 253 192 0 Y 0 0 33 def COLUMNS COLUMN_TYPE COLUMN_TYPE 252 589815 7 N 17 0 33 def COLUMNS COLUMN_KEY COLUMN_KEY 253 9 3 N 1 0 33 -def COLUMNS EXTRA EXTRA 253 60 0 N 1 0 33 +def COLUMNS EXTRA EXTRA 253 81 0 N 1 0 33 def COLUMNS PRIVILEGES PRIVILEGES 253 240 31 N 1 0 33 def COLUMNS COLUMN_COMMENT COLUMN_COMMENT 253 765 0 N 1 0 33 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT @@ -998,7 +998,7 @@ def COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 def COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 def COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 def COLUMNS COLUMN_DEFAULT Default 252 589815 0 Y 16 0 33 -def COLUMNS EXTRA Extra 253 60 0 N 1 0 33 +def COLUMNS EXTRA Extra 253 81 0 N 1 0 33 Field Type Null Key Default Extra c int(11) NO PRI NULL ---------------------------------------------------------------- diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result index 6bb8cbea2f1..cd652c03f96 100644 --- a/mysql-test/r/skip_grants.result +++ b/mysql-test/r/skip_grants.result @@ -72,3 +72,8 @@ count(*) select count(*) from information_schema.USER_PRIVILEGES; count(*) 0 +CREATE FUNCTION a RETURNS STRING SONAME ''; +ERROR HY000: Can't initialize function 'a'; UDFs are unavailable with the --skip-grant-tables option +DROP FUNCTION a; +ERROR 42000: FUNCTION test.a does not exist +End of 5.0 tests diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 412f6b94fa2..68aa278585f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5670,7 +5670,7 @@ drop function if exists pi; create function pi() returns varchar(50) return "pie, my favorite desert."; Warnings: -Note 1584 This function 'pi' has the same name as a native function +Note 1585 This function 'pi' has the same name as a native function SET @save_sql_mode=@@sql_mode; SET SQL_MODE='IGNORE_SPACE'; select pi(), pi (); @@ -5719,15 +5719,15 @@ use test; create function `database`() returns varchar(50) return "Stored function database"; Warnings: -Note 1584 This function 'database' has the same name as a native function +Note 1585 This function 'database' has the same name as a native function create function `current_user`() returns varchar(50) return "Stored function current_user"; Warnings: -Note 1584 This function 'current_user' has the same name as a native function +Note 1585 This function 'current_user' has the same name as a native function create function md5(x varchar(50)) returns varchar(50) return "Stored function md5"; Warnings: -Note 1584 This function 'md5' has the same name as a native function +Note 1585 This function 'md5' has the same name as a native function SET SQL_MODE='IGNORE_SPACE'; select database(), database (); database() database () diff --git a/mysql-test/r/sp_gis.result b/mysql-test/r/sp_gis.result index b0960dec647..7a76507754f 100644 --- a/mysql-test/r/sp_gis.result +++ b/mysql-test/r/sp_gis.result @@ -7,11 +7,11 @@ return 1; create function x() returns int return 2; Warnings: -Note 1584 This function 'x' has the same name as a native function +Note 1585 This function 'x' has the same name as a native function create function y() returns int return 3; Warnings: -Note 1584 This function 'y' has the same name as a native function +Note 1585 This function 'y' has the same name as a native function select a(); a() 1 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a25183a0e6d..25df7a56aa3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4139,6 +4139,66 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1; (SELECT SUM(t1.a) FROM t2 WHERE a=1) 3 DROP TABLE t1,t2; +CREATE TABLE t1 (a1 INT, a2 INT); +CREATE TABLE t2 (b1 INT, b2 INT); +INSERT INTO t1 VALUES (100, 200); +INSERT INTO t1 VALUES (101, 201); +INSERT INTO t2 VALUES (101, 201); +INSERT INTO t2 VALUES (103, 203); +SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; +((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL +0 +0 +DROP TABLE t1, t2; +CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); +INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43); +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +s1 s2 +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); +s1 s2 +CREATE INDEX I1 ON t1 (s1); +CREATE INDEX I2 ON t1 (s2); +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +s1 s2 +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); +s1 s2 +TRUNCATE t1; +INSERT INTO t1 VALUES (0x41,0x41); +SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1); +s1 s2 +DROP TABLE t1; +CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1)); +CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2)); +CREATE TABLE t3 (a3 BINARY(2) default '0'); +INSERT INTO t1 VALUES (1),(2),(3),(4); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2; +LEFT(t2.a2, 1) +1 +2 +3 +SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1; +a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) +1 0 +2 0 +3 0 +4 0 +DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3)); +CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY); +CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); +INSERT INTO t2 VALUES (2), (3), (4), (5); +INSERT INTO t3 VALUES (10), (20), (30); +SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; +LEFT(t1.a1,1) +1 +2 +3 +SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); +a2 +DROP TABLE t1, t2, t3; End of 5.0 tests. CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 189722bfe9b..47ffc90e3cd 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1978,3 +1978,9 @@ a 1 drop table table_25411_a; drop table table_25411_b; +DROP TRIGGER IF EXISTS trg; +Warnings: +Note 1360 Trigger does not exist +SHOW CREATE TRIGGER trg; +ERROR HY000: Trigger does not exist +End of 5.1 tests. diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index 42276a004bb..e37a398d22e 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -812,4 +812,81 @@ select group_concat(f1),group_concat(f2) from t1; group_concat(f1) group_concat(f2) -0.123456 0.123456 drop table t1; +create table t1 ( +ua_id decimal(22,0) not null, +ua_invited_by_id decimal(22,0) default NULL, +primary key(ua_id) +); +insert into t1 values (123, NULL), (456, NULL); +this must not produce error 1048: +select * from t1 where ua_invited_by_id not in (select ua_id from t1); +ua_id ua_invited_by_id +drop table t1; +DROP TABLE IF EXISTS t3; +DROP TABLE IF EXISTS t4; +CREATE TABLE t1( a NUMERIC, b INT ); +INSERT INTO t1 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c; +c +123456.000000000000000000000000000000 +123456.000000000000000000000000000000 +CREATE TABLE t2( a NUMERIC, b INT ); +INSERT INTO t2 VALUES (123456, 100); +SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c; +c +123456 +SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c; +c +123456 +CREATE TABLE t3( a DECIMAL, b INT ); +INSERT INTO t3 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c; +c +123456.000000000000000000000000000000 +123456.000000000000000000000000000000 +CREATE TABLE t4( a DECIMAL, b INT ); +INSERT INTO t4 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c; +c +123456.000000000000000000000000000000 +123456.000000000000000000000000000000 +delete from t1; +INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5); +Warnings: +Note 1265 Data truncated for column 'a' at row 2 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(10,0) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select round(a,b) as c from t1 order by c; +c +1000 +1234567890 +DROP TABLE t1, t2, t3, t4; End of 5.0 tests diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 149f0d2dca5..6e08067d8a4 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -57,7 +57,7 @@ ushort smallint(5) unsigned zerofill NULL NO MUL 00000 # umedium mediumint(8) unsigned NULL NO MUL 0 # ulong int(11) unsigned NULL NO MUL 0 # ulonglong bigint(13) unsigned NULL NO MUL 0 # -time_stamp timestamp NULL NO CURRENT_TIMESTAMP # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field date NULL YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # @@ -225,7 +225,7 @@ ushort smallint(5) unsigned zerofill NULL NO 00000 # umedium mediumint(8) unsigned NULL NO MUL 0 # ulong int(11) unsigned NULL NO MUL 0 # ulonglong bigint(13) unsigned NULL NO MUL 0 # -time_stamp timestamp NULL NO CURRENT_TIMESTAMP # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field char(10) latin1_swedish_ci YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 7caf7e78fe9..596dcbf432c 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -251,7 +251,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO 2003-01-01 00:00:00 +t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; create table t1 (t1 timestamp default now() on update now(), t2 datetime); @@ -276,7 +276,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; create table t1 (t1 timestamp, t2 datetime, t3 timestamp); @@ -302,7 +302,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; @@ -328,7 +328,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL truncate table t1; insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result index 552f4896698..d98173dbe15 100644 --- a/mysql-test/r/xml.result +++ b/mysql-test/r/xml.result @@ -647,32 +647,32 @@ select extractValue('<a>a','/a'); extractValue('<a>a','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' select extractValue('<a>a<','/a'); extractValue('<a>a<','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' select extractValue('<a>a</','/a'); extractValue('<a>a</','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)' select extractValue('<a>a</a','/a'); extractValue('<a>a</a','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' select extractValue('<a>a</a></b>','/a'); extractValue('<a>a</a></b>','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)' select extractValue('<a b=>a</a>','/a'); extractValue('<a b=>a</a>','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' select extractValue('<e>1</e>','position()'); ERROR HY000: XPATH syntax error: '' select extractValue('<e>1</e>','last()'); @@ -723,17 +723,17 @@ select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//* extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' select extractValue('<.>test</.>','//*'); extractValue('<.>test</.>','//*') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<->test</->','//*'); extractValue('<->test</->','//*') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<:>test</:>','//*'); extractValue('<:>test</:>','//*') test diff --git a/mysql-test/suite/ndb/r/ndb_dd_basic.result b/mysql-test/suite/ndb/r/ndb_dd_basic.result index 0ff28f889b7..f04905b8d13 100644 --- a/mysql-test/suite/ndb/r/ndb_dd_basic.result +++ b/mysql-test/suite/ndb/r/ndb_dd_basic.result @@ -8,20 +8,20 @@ INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=MYISAM; Warnings: -Error 1477 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1478 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' ALTER LOGFILE GROUP lg1 ADD UNDOFILE 'undofile02.dat' INITIAL_SIZE = 4M ENGINE=XYZ; Warnings: Warning 1286 Unknown table engine 'XYZ' -Error 1477 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1478 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 12M; Warnings: -Error 1477 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1478 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' set storage_engine=ndb; CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' diff --git a/mysql-test/suite/ndb/r/ndb_dd_ddl.result b/mysql-test/suite/ndb/r/ndb_dd_ddl.result index 569769cfaef..d8d9e8631d5 100644 --- a/mysql-test/suite/ndb/r/ndb_dd_ddl.result +++ b/mysql-test/suite/ndb/r/ndb_dd_ddl.result @@ -16,7 +16,7 @@ ERROR HY000: Failed to create LOGFILE GROUP SHOW WARNINGS; Level Code Message Error 1296 Got error 1514 'Currently there is a limit of one logfile group' from NDB -Error 1527 Failed to create LOGFILE GROUP +Error 1528 Failed to create LOGFILE GROUP CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' INITIAL_SIZE 1M diff --git a/mysql-test/suite/ndb/r/ndb_gis.result b/mysql-test/suite/ndb/r/ndb_gis.result index b401dee8054..6c44c6fb822 100644 --- a/mysql-test/suite/ndb/r/ndb_gis.result +++ b/mysql-test/suite/ndb/r/ndb_gis.result @@ -463,7 +463,7 @@ drop table t1; End of 4.1 tests CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); Warnings: -Error 1477 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' +Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); @@ -1013,7 +1013,7 @@ drop table t1; End of 4.1 tests CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); Warnings: -Error 1477 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' +Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); diff --git a/mysql-test/suite/ndb/r/ndb_row_format.result b/mysql-test/suite/ndb/r/ndb_row_format.result index ea495e7e9c5..eea0692dd92 100644 --- a/mysql-test/suite/ndb/r/ndb_row_format.result +++ b/mysql-test/suite/ndb/r/ndb_row_format.result @@ -8,7 +8,7 @@ ENGINE=NDB; ERROR HY000: Can't create table 'test.t1' (errno: 138) SHOW WARNINGS; Level Code Message -Error 1477 Table storage engine 'ndbcluster' does not support the create option 'Row format FIXED incompatible with variable sized attribute' +Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Row format FIXED incompatible with variable sized attribute' Error 1005 Can't create table 'test.t1' (errno: 138) CREATE TABLE t1 ( a INT KEY, diff --git a/mysql-test/suite/ndb/r/ndb_single_user.result b/mysql-test/suite/ndb/r/ndb_single_user.result index 552629ae532..0a4f7cd0b5f 100644 --- a/mysql-test/suite/ndb/r/ndb_single_user.result +++ b/mysql-test/suite/ndb/r/ndb_single_user.result @@ -11,7 +11,7 @@ ERROR HY000: Failed to create LOGFILE GROUP show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1527 Failed to create LOGFILE GROUP +Error 1528 Failed to create LOGFILE GROUP create table t1 (a int key, b int unique, c int) engine ndb; CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' @@ -27,14 +27,14 @@ ERROR HY000: Failed to create TABLESPACE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1527 Failed to create TABLESPACE +Error 1528 Failed to create TABLESPACE DROP LOGFILE GROUP lg1 ENGINE =NDB; ERROR HY000: Failed to drop LOGFILE GROUP show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1528 Failed to drop LOGFILE GROUP +Error 1529 Failed to drop LOGFILE GROUP CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 @@ -47,7 +47,7 @@ ERROR HY000: Failed to alter: DROP DATAFILE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1532 Failed to alter: DROP DATAFILE +Error 1533 Failed to alter: DROP DATAFILE ALTER TABLESPACE ts1 DROP DATAFILE 'datafile.dat' ENGINE NDB; @@ -57,7 +57,7 @@ ERROR HY000: Failed to drop TABLESPACE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1528 Failed to drop TABLESPACE +Error 1529 Failed to drop TABLESPACE DROP TABLESPACE ts1 ENGINE NDB; DROP LOGFILE GROUP lg1 diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result index fe5a5b28682..007cd5a252c 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. STOP SLAVE; RESET SLAVE; @@ -139,7 +139,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -157,7 +157,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -201,7 +201,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -219,7 +219,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -263,7 +263,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -281,7 +281,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -324,7 +324,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -342,7 +342,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -436,7 +436,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -454,7 +454,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -497,7 +497,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -515,7 +515,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -822,7 +822,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -840,7 +840,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result index e70a2efaf29..873fc5cddbc 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. STOP SLAVE; RESET SLAVE; @@ -139,7 +139,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -157,7 +157,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -201,7 +201,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -219,7 +219,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -263,7 +263,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -281,7 +281,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -324,7 +324,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -342,7 +342,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -436,7 +436,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -454,7 +454,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -497,7 +497,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -515,7 +515,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -822,7 +822,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -840,7 +840,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_incident.result b/mysql-test/suite/rpl/r/rpl_incident.result index 568078cc276..c3baabbdbc3 100644 --- a/mysql-test/suite/rpl/r/rpl_incident.result +++ b/mysql-test/suite/rpl/r/rpl_incident.result @@ -44,7 +44,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1589 +Last_Errno 1590 Last_Error The incident LOST_EVENTS occured on the master. Message: <none> Skip_Counter 0 Exec_Master_Log_Pos # @@ -62,7 +62,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1589 +Last_SQL_Errno 1590 Last_SQL_Error The incident LOST_EVENTS occured on the master. Message: <none> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result index d30110f85e9..a4d67754bd6 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result @@ -65,7 +65,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1592 +Last_Errno 1593 Last_Error Fatal error: Not enough memory Skip_Counter 0 Exec_Master_Log_Pos 325 @@ -83,7 +83,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1592 +Last_SQL_Errno 1593 Last_SQL_Error Fatal error: Not enough memory SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_row_colSize.result b/mysql-test/suite/rpl/r/rpl_row_colSize.result index 9358e36cac6..6d002a722f1 100644 --- a/mysql-test/suite/rpl/r/rpl_row_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_row_colSize.result @@ -37,7 +37,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -55,7 +55,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -91,7 +91,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 12, test.t1 on slave has size 12. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -109,7 +109,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 12, test.t1 on slave has size 12. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -145,7 +145,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -163,7 +163,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -200,7 +200,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 5, test.t1 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -218,7 +218,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 5, test.t1 has type 4 SELECT COUNT(*) FROM t1; COUNT(*) @@ -255,7 +255,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 8, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -273,7 +273,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 8, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -309,7 +309,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 2. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -327,7 +327,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 2. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -364,7 +364,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -382,7 +382,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -419,7 +419,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 20, test.t1 on slave has size 11. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -437,7 +437,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 20, test.t1 on slave has size 11. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -505,7 +505,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -523,7 +523,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -560,7 +560,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 100. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -578,7 +578,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 100. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -614,7 +614,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 200, test.t1 on slave has size 10. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -632,7 +632,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 200, test.t1 on slave has size 10. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -668,7 +668,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 1000. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -686,7 +686,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 1000. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -723,7 +723,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 4, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -741,7 +741,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 4, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result index 566537ab745..06dc90f18aa 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result @@ -214,7 +214,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -232,7 +232,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -300,7 +300,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -318,7 +318,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result index 83df75b81e9..3911fe89b7f 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result @@ -214,7 +214,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -232,7 +232,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -300,7 +300,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -318,7 +318,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_udf.result b/mysql-test/suite/rpl/r/rpl_udf.result index fcd2f4743ba..79a82b5fbc7 100644 --- a/mysql-test/suite/rpl/r/rpl_udf.result +++ b/mysql-test/suite/rpl/r/rpl_udf.result @@ -182,19 +182,19 @@ CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM; affected rows: 0 INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 SELECT * FROM t1 ORDER BY sum; sum price diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result index 41e888827b1..54056ac613b 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. STOP SLAVE; RESET SLAVE; @@ -139,7 +139,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -157,7 +157,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -201,7 +201,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -219,7 +219,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -263,7 +263,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -281,7 +281,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -324,7 +324,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -342,7 +342,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -436,7 +436,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -454,7 +454,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -497,7 +497,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -515,7 +515,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -823,7 +823,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -841,7 +841,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/t/almost_full.test b/mysql-test/t/almost_full.test new file mode 100644 index 00000000000..5c67ab3c088 --- /dev/null +++ b/mysql-test/t/almost_full.test @@ -0,0 +1,41 @@ +# +# Some special cases with empty tables +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +set global myisam_data_pointer_size=2; +CREATE TABLE t1 (a int auto_increment primary key not null, b longtext) ENGINE=MyISAM; + +--disable_query_log +let $1= 303; +while ($1) +{ + INSERT INTO t1 SET b=repeat('a',200); + dec $1; +} +--enable_query_log + +DELETE FROM t1 WHERE a=1 or a=5; + +--error 1114 +INSERT INTO t1 SET b=repeat('a',600); +CHECK TABLE t1 EXTENDED; + +--error 1114 +UPDATE t1 SET b=repeat('a', 800) where a=10; +CHECK TABLE t1 EXTENDED; + +INSERT INTO t1 SET b=repeat('a',400); +CHECK TABLE t1 EXTENDED; + +DELETE FROM t1 WHERE a=2 or a=6; +UPDATE t1 SET b=repeat('a', 600) where a=11; +CHECK TABLE t1 EXTENDED; +drop table t1; + +set global myisam_data_pointer_size=default; + +# End of 4.1 tests diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index e0a2e877af5..f8eff10e30a 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1567,3 +1567,25 @@ insert into t1 set a=''; insert into t1 set a='a'; check table t1 extended; drop table t1; + +# +# BUG#31036 - Using order by with archive table crashes server +# + +CREATE TABLE t1(a VARCHAR(510)) ENGINE = ARCHIVE; + +let $bug31036=41; +--disable_query_log +while($bug31036) +{ + INSERT INTO t1(a) VALUES (REPEAT('a', 510)); + dec $bug31036; +} +--enable_query_log +INSERT INTO t1(a) VALUES (''); + +--disable_result_log +SELECT * FROM t1 ORDER BY a; +--enable_result_log + +DROP TABLE t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 5563e260a06..50865215944 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -246,4 +246,26 @@ INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL), SELECT cast(date(d1) as signed) FROM t1; drop table t1; +# +# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE +# + +# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns +# and typecasts (NULL in, NULL out). +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), + MINUTE(f1), + SECOND(f1) FROM t1; +SELECT HOUR(CAST('2007-07-19' AS DATE)), + MINUTE(CAST('2007-07-19' AS DATE)), + SECOND(CAST('2007-07-19' AS DATE)); +SELECT HOUR(CAST(NULL AS DATE)), + MINUTE(CAST(NULL AS DATE)), + SECOND(CAST(NULL AS DATE)); +SELECT HOUR(NULL), + MINUTE(NULL), + SECOND(NULL); +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 4c5dd6467bd..322be2b7b8e 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -590,4 +590,13 @@ select group_concat(distinct a, c order by a desc, c desc) from t1; drop table t1; +# +# Bug#30897 GROUP_CONCAT returns extra comma on empty fields +# +create table t1 (f1 char(20)); +insert into t1 values (''),(''); +select group_concat(distinct f1) from t1; +select group_concat(f1) from t1; +drop table t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index e1ec6906cd6..8beed28cfbf 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -880,5 +880,33 @@ SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ); DROP TABLE t1; +# +# Bug #30715: Assertion failed: item_field->field->real_maybe_null(), file +# .\opt_sum.cc, line +# + +CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b)); +SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01'; +DROP TABLE t1; + +# +# Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2; +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); + +SET SQL_MODE=ONLY_FULL_GROUP_BY; +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT a FROM t1 HAVING COUNT(*)>2; +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT COUNT(*), a FROM t1; + +SET SQL_MODE=DEFAULT; +SELECT a FROM t1 HAVING COUNT(*)>2; +SELECT COUNT(*), a FROM t1; + +DROP TABLE t1; + ### --echo End of 5.0 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 0ba0ba30cd2..b6da14211ae 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -782,6 +782,19 @@ explain extended select encode(f1,'zxcv') as 'enc' from t1; explain extended select decode(f1,'zxcv') as 'enc' from t1; drop table t1; +# +# Bug #31758 inet_ntoa, oct, crashes server with null + filesort +# +create table t1 (a bigint not null)engine=myisam; +insert into t1 set a = 1024*1024*1024*4; +delete from t1 order by (inet_ntoa(a)) desc limit 10; +drop table t1; +create table t1 (a char(36) not null)engine=myisam; +insert ignore into t1 set a = ' '; +insert ignore into t1 set a = ' '; +select * from t1 order by (oct(a)); +drop table t1; + --echo End of 4.1 tests # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 2ea7aed6bd2..94c2b463aaa 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -790,6 +790,41 @@ drop table t1; SET SQL_MODE = ''; # + +# +# Bug #32202: ORDER BY not working with GROUP BY +# + +CREATE TABLE t1( + id INT AUTO_INCREMENT PRIMARY KEY, + c1 INT NOT NULL, + c2 INT NOT NULL, + UNIQUE KEY (c2,c1)); + +INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3); + +# Show that the test cases from the bug report pass +SELECT * FROM t1 ORDER BY c1; +SELECT * FROM t1 GROUP BY id ORDER BY c1; + +# Show that DESC is handled correctly +SELECT * FROM t1 GROUP BY id ORDER BY id DESC; + +# Show that results are correctly ordered when ORDER BY fields +# are a subset of GROUP BY ones +SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1; +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1; +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC; + +# Show that results are correctly ordered when GROUP BY fields +# are a subset of ORDER BY ones +SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1; +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1; +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC; + +DROP TABLE t1; + +--echo End of 5.0 tests # Bug #21174: Index degrades sort performance and # optimizer does not honor IGNORE INDEX. # a.k.a WL3527. diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 1987d9d5773..2dd19a94758 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -807,7 +807,8 @@ DROP FUNCTION func2; select column_type, group_concat(table_schema, '.', table_name), count(*) as num from information_schema.columns where table_schema='information_schema' and -(column_type = 'varchar(7)' or column_type = 'varchar(20)') +(column_type = 'varchar(7)' or column_type = 'varchar(20)' + or column_type = 'varchar(27)') group by column_type order by num; # @@ -1232,4 +1233,9 @@ select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL; select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL; select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; +# +# Bug#31630 debug assert with explain extended select ... from i_s +# +explain extended select 1 from information_schema.tables; + --echo End of 5.1 tests. diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index c67da261ef1..f7795e49b37 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -179,6 +179,42 @@ SET GLOBAL READ_ONLY = OFF; SET GLOBAL general_log = @old_general_log_state; SET GLOBAL slow_query_log = @old_slow_log_state; +# +# Bug #29131: SHOW VARIABLES reports variable 'log' but SET doesn't recognize it +# + +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; + +SHOW VARIABLES LIKE 'general_log'; +SHOW VARIABLES LIKE 'log'; +SELECT @@general_log, @@log; +SET GLOBAL log = 0; +SHOW VARIABLES LIKE 'general_log'; +SHOW VARIABLES LIKE 'log'; +SELECT @@general_log, @@log; +SET GLOBAL general_log = 1; +SHOW VARIABLES LIKE 'general_log'; +SHOW VARIABLES LIKE 'log'; +SELECT @@general_log, @@log; + +SHOW VARIABLES LIKE 'slow_query_log'; +SHOW VARIABLES LIKE 'log_slow_queries'; +SELECT @@slow_query_log, @@log_slow_queries; +SET GLOBAL log_slow_queries = 0; +SHOW VARIABLES LIKE 'slow_query_log'; +SHOW VARIABLES LIKE 'log_slow_queries'; +SELECT @@slow_query_log, @@log_slow_queries; +SET GLOBAL slow_query_log = 1; +SHOW VARIABLES LIKE 'slow_query_log'; +SHOW VARIABLES LIKE 'log_slow_queries'; +SELECT @@slow_query_log, @@log_slow_queries; + +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; + +--echo End of 5.1 tests + --enable_ps_protocol # diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 12098b4543b..39bf8029a7c 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -924,3 +924,34 @@ set global general_log = off; select command_type, argument from mysql.general_log; deallocate prepare long_query; set global general_log = @old_general_log_state; + +# +# Bug #31700: thd->examined_row_count not incremented for 'const' type queries +# +SET @old_slow_log_state = @@global.slow_query_log; + +SET SESSION long_query_time = 0; +SET GLOBAL slow_query_log = ON; +FLUSH LOGS; +TRUNCATE TABLE mysql.slow_log; + +# Let there be three columns, unique, non-unique, and non-indexed: +CREATE TABLE t1 (f1 SERIAL,f2 INT, f3 INT, PRIMARY KEY(f1), KEY(f2)); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (2,2,2); +INSERT INTO t1 VALUES (3,3,3); +INSERT INTO t1 VALUES (4,4,4); + +SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3 FROM t1 WHERE f3=4; +SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3 FROM t1 WHERE f2=3; +SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3 FROM t1 WHERE f1=2; + +--replace_column 1 TIMESTAMP +SELECT start_time, rows_examined, rows_sent, sql_text FROM mysql.slow_log WHERE sql_text LIKE '%Bug#31700%' ORDER BY start_time; + +DROP TABLE t1; + +TRUNCATE TABLE mysql.slow_log; + +SET GLOBAL slow_query_log = @old_slow_log_state; +SET SESSION long_query_time =@old_long_query_time; diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test index e9cc26bec18..d6612b3e6b9 100644 --- a/mysql-test/t/lowercase_view.test +++ b/mysql-test/t/lowercase_view.test @@ -138,3 +138,26 @@ create view v1Aa as select AaA.col1 from t1Aa as AaA; show create view v1AA; drop view v1AA; drop table t1Aa; + + +# +# Bug #31562: HAVING and lower case +# + +CREATE TABLE t1 (a int, b int); + +select X.a from t1 AS X group by X.b having (X.a = 1); +select X.a from t1 AS X group by X.b having (x.a = 1); +select X.a from t1 AS X group by X.b having (x.b = 1); + +CREATE OR REPLACE VIEW v1 AS +select X.a from t1 AS X group by X.b having (X.a = 1); + +SHOW CREATE VIEW v1; + +SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; + +--echo End of 5.0 tests. diff --git a/mysql-test/t/mysql_comments.sql b/mysql-test/t/mysql_comments.sql new file mode 100644 index 00000000000..2497c35e465 --- /dev/null +++ b/mysql-test/t/mysql_comments.sql @@ -0,0 +1,218 @@ +##============================================================================ +## Notes +##============================================================================ + +# Test case for Bug#11230 + +# The point of this test is to make sure that '#', '-- ' and '/* ... */' +# comments, as well as empty lines, are sent from the client to the server. +# This is to ensure better error reporting, and to keep comments in the code +# for stored procedures / functions / triggers (Bug#11230). +# As a result, be careful when editing comments in this script, they do +# matter. +# +# Also, note that this is a script for **mysql**, not mysqltest. +# This is critical, as the mysqltest client interprets comments differently. + +##============================================================================ +## Setup +##============================================================================ + +## See mysql_comments.test for initial cleanup + +# Test tables +# +# t1 is reused throughout the file, and dropped at the end. +# +drop table if exists t1; +create table t1 ( + id char(16) not null default '', + data int not null +); + +##============================================================================ +## Comments outside statements +##============================================================================ + +# Ignored 1a +-- Ignored 1b +/* + Ignored 1c +*/ + +select 1; + +##============================================================================ +## Comments inside statements +##============================================================================ + +select # comment 1a +# comment 2a +-- comment 2b +/* + comment 2c +*/ +2 +; # not strictly inside, but on same line +# ignored + +##============================================================================ +## Comments inside functions +##============================================================================ + +drop function if exists foofct ; + +create function foofct (x char(20)) +returns char(20) +/* not inside the body yet */ +return +-- comment 1a +# comment 1b +/* comment 1c */ +x; # after body, on same line + +select foofct("call 1"); + +show create function foofct; +drop function foofct; + +delimiter | + +create function foofct(x char(20)) +returns char(20) +begin + -- comment 1a + # comment 1b + /* + comment 1c + */ + + -- empty line below + + -- empty line above + return x; +end| + +delimiter ; + +select foofct("call 2"); + +show create function foofct; +drop function foofct; + +##============================================================================ +## Comments inside stored procedures +##============================================================================ + +# Empty statement +drop procedure if exists empty; +create procedure empty() +begin +end; + +call empty(); +show create procedure empty; +drop procedure empty; + +drop procedure if exists foosp; + +## These comments are before the create, and will be lost +# Comment 1a +-- Comment 1b +/* + Comment 1c + */ +create procedure foosp() +/* Comment not quiet in the body yet */ + insert into test.t1 +## These comments are part of the procedure body, and should be kept. +# Comment 2a +-- Comment 2b +/* Comment 2c */ + -- empty line below + + -- empty line above + values ("foo", 42); # comment 3, still part of the body +## After the ';', therefore not part of the body +# comment 4a +-- Comment 4b +/* + Comment 4c + */ + +call foosp(); +select * from t1; +delete from t1; +show create procedure foosp; +drop procedure foosp; + +drop procedure if exists nicesp; + +delimiter | + +create procedure nicesp(a int) +begin + -- declare some variables here + declare b int; + declare c float; + + -- do more stuff here + -- commented nicely and so on + + -- famous last words ... +end| + +delimiter ; + +show create procedure nicesp; +drop procedure nicesp; + +##============================================================================ +## Comments inside triggers +##============================================================================ + +drop trigger if exists t1_empty; + +create trigger t1_empty after delete on t1 +for each row +begin +end; + +show create trigger t1_empty; + +drop trigger if exists t1_bi; + +delimiter | + +create trigger t1_bi before insert on t1 +for each row +begin +# comment 1a +-- comment 1b +/* + comment 1c +*/ + -- declare some variables here + declare b int; + declare c float; + + -- do more stuff here + -- commented nicely and so on + + -- famous last words ... + set NEW.data := 12; +end| + +delimiter ; + +show create trigger t1_bi; + +# also make sure the trigger still works +insert into t1(id) value ("trig"); +select * from t1; + +##============================================================================ +## Cleanup +##============================================================================ + +drop table t1; diff --git a/mysql-test/t/mysql_comments.test b/mysql-test/t/mysql_comments.test new file mode 100644 index 00000000000..1f997aeb1ab --- /dev/null +++ b/mysql-test/t/mysql_comments.test @@ -0,0 +1,37 @@ +# This test should work in embedded server after we fix mysqltest +-- source include/not_embedded.inc +###################### mysql_comments.test ############################# +# # +# Testing of comments handling by the command line client (mysql) # +# # +# Creation: # +# 2007-10-29 akopytov Implemented this test as a part of fixes for # +# bug #26215 and bug #11230 # +# # +######################################################################## + +# +# Bug #11230: Keeping comments when storing stored procedures +# + +# See the content of mysql_comments.sql +# Set the test database to a known state before running the tests. +--disable_warnings +drop table if exists t1; +drop function if exists foofct; +drop procedure if exists empty; +drop procedure if exists foosp; +drop procedure if exists nicesp; +drop trigger if exists t1_empty; +drop trigger if exists t1_bi; +--enable_warnings + +# Test without comments +--echo "Pass 1 : --disable-comments" +--exec $MYSQL --disable-comments test 2>&1 < "./t/mysql_comments.sql" + +# Test with comments +--echo "Pass 2 : --enable-comments" +--exec $MYSQL --enable-comments test 2>&1 < "./t/mysql_comments.sql" + +--echo End of 5.0 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 158e8a769bd..0e4e9989ffa 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1788,6 +1788,18 @@ TRUNCATE mysql.event; --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug29938.sql SHOW EVENTS; + +--echo # +--echo # Bug#31113 mysqldump 5.1 can't handle a dash ("-") in database names +--echo # +create database `test-database`; +use `test-database`; +create table test (a int); +--exec $MYSQL_DUMP --compact --opt --quote-names test-database +drop database `test-database`; +use test; + + --echo # --echo # End of 5.1 tests --echo # diff --git a/mysql-test/t/outfile_loaddata.test b/mysql-test/t/outfile_loaddata.test new file mode 100644 index 00000000000..2f6ac998b3d --- /dev/null +++ b/mysql-test/t/outfile_loaddata.test @@ -0,0 +1,89 @@ +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +--echo # +--echo # Bug#31663 FIELDS TERMINATED BY special character +--echo # + +CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256)); +INSERT INTO t1 VALUES (101, 202, '-r-', '=raker='); + +--let $fields=* +--let $clauses=FIELDS TERMINATED BY 'raker' +--echo # $clauses, warning: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=i1, i2 +--let $clauses=FIELDS TERMINATED BY 'r' +--echo # Only numeric fields, $clauses, no warnings: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=* +--let $clauses=FIELDS TERMINATED BY '0' +--echo # $clauses, warning: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=* +--let $clauses=FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' +--echo # $clauses, warning: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=c1, c2 +--let $clauses=FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' +--echo # Only string fields, $clauses, no warnings: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +DROP TABLE t1; + +--echo # End of 5.0 tests. diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index f986215ee89..65e78a5e740 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1510,4 +1510,25 @@ PARTITION BY RANGE (b) ( show create table t1; drop table t1, t2; +# +# Bug #32067 Partitions: crash with timestamp column +# this bug occurs randomly on some UPDATE statement +# with the '1032: Can't find record in 't1'' error + +create table t1 + (s1 timestamp on update current_timestamp, s2 int) + partition by key(s1) partitions 3; + +insert into t1 values (null,null); +--disable_query_log +let $cnt= 1000; +while ($cnt) +{ + update t1 set s2 = 1; + update t1 set s2 = 2; + dec $cnt; +} +--enable_query_log + +drop table t1; --echo End of 5.1 tests diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 4a50332b3df..6b73d0b2c5e 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -142,3 +142,15 @@ PARTITION BY KEY(a) PARTITIONS 10; INSERT INTO t1 VALUES(1),(2); SELECT COUNT(*) FROM t1; DROP TABLE t1; + +# +# Bug #31893 Partitions: crash if subpartitions and engine change +# +create table t1 (int_column int, char_column char(5)) + PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 + (PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); +alter table t1 PARTITION BY RANGE (int_column) + subpartition by key (char_column) subpartitions 2 + (PARTITION p1 VALUES LESS THAN (5) ENGINE = myisam); +show create table t1; +drop table t1; diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index fb6d5febe45..d8d6d069676 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -24,3 +24,18 @@ UNINSTALL PLUGIN EXAMPLE; --error 1305 UNINSTALL PLUGIN non_exist; + + +--echo # +--echo # Bug#32034: check_func_enum() does not check correct values but set it +--echo # to impossible int val +--echo # + +INSTALL PLUGIN example SONAME 'ha_example.so'; + +SET GLOBAL example_enum_var= e1; +SET GLOBAL example_enum_var= e2; +--error 1231 +SET GLOBAL example_enum_var= impossible; + +UNINSTALL PLUGIN example; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 71a7caba399..8d273441ce8 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2361,6 +2361,27 @@ insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), select hex(a), hex(b) from t1; drop table t1; +# +# Bug #32103: optimizer crash when join on int and mediumint with variable in +# where clause +# + +CREATE TABLE t1 (c0 int); +CREATE TABLE t2 (c0 int); + +# We need any variable that: +# 1. has integer type, +# 2. can be used with the "@@name" syntax +# 3. available in every server build +INSERT INTO t1 VALUES(@@connect_timeout); +INSERT INTO t2 VALUES(@@connect_timeout); + +# We only need to ensure 1 row is returned to validate the results +--replace_column 1 X 2 X +SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); + +DROP TABLE t1, t2; + --echo End of 4.1 tests # @@ -2743,14 +2764,14 @@ create table t1(f1 int, f2 date); insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), (4,'2005-10-01'),(5,'2005-12-30'); # should return all records -select * from t1 where f2 >= 0; -select * from t1 where f2 >= '0000-00-00'; +select * from t1 where f2 >= 0 order by f2; +select * from t1 where f2 >= '0000-00-00' order by f2; # should return 4,5 -select * from t1 where f2 >= '2005-09-31'; -select * from t1 where f2 >= '2005-09-3a'; +select * from t1 where f2 >= '2005-09-31' order by f2; +select * from t1 where f2 >= '2005-09-3a' order by f2; # should return 1,2,3 -select * from t1 where f2 <= '2005-09-31'; -select * from t1 where f2 <= '2005-09-3a'; +select * from t1 where f2 <= '2005-09-31' order by f2; +select * from t1 where f2 <= '2005-09-3a' order by f2; drop table t1; # @@ -3455,24 +3476,129 @@ DROP TABLE t1; # --disable_ps_protocol - SELECT 1 AS ` `; SELECT 1 AS ` `; SELECT 1 AS ` x`; +--enable_ps_protocol -CREATE VIEW v1 AS SELECT 1 AS ` `; -SELECT `` FROM v1; - -CREATE VIEW v2 AS SELECT 1 AS ` `; -SELECT `` FROM v2; - -CREATE VIEW v3 AS SELECT 1 AS ` x`; -SELECT `x` FROM v3; - -DROP VIEW v1, v2, v3; +--error 1166 +CREATE VIEW v1 AS SELECT 1 AS ``; ---enable_ps_protocol +--error 1166 +CREATE VIEW v1 AS SELECT 1 AS ` `; +--error 1166 +CREATE VIEW v1 AS SELECT 1 AS ` `; + +--error 1166 +CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); + +--error 1166 +ALTER VIEW v1 AS SELECT 1 AS ` `; + +DROP VIEW v1; + +# +# Bug#31800: Date comparison fails with timezone and slashes for greater +# than comparison +# + +# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a +# DATETIME comparator, while greater/less-than used bin-string comparisons. +# Should correctly be compared as DATE or DATETIME, but throw a warning: + +select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' + and '2007/10/20 00:00:00 GMT'; +select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; +select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; + +# We have all we need -- and trailing garbage: +# (leaving out a leading zero in first example to prove it's a +# value-comparison, not a string-comparison!) +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; +# no time at all: +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; +# partial time: +select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +# fail, different second part: +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +# correct syntax, no trailing nonsense -- this one must throw no warning: +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; +# no warning, but failure (different hour parts): +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; +# succeed: +select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +# succeed, but warn for "trailing garbage" (":34"): +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +# invalid date (Feb 30) succeeds +select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; +# 0-day for both, just works in default SQL mode. +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +# 0-day, succeed +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' + and '2007/10/20 00:00:00'; +set SQL_MODE=TRADITIONAL; +# 0-day throws warning in traditional mode, and fails +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +# different code-path: get_datetime_value() with 0-day +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' + and '2007/10/20'; +set SQL_MODE=DEFAULT; +select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; +select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; +select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; + +select str_to_date('1','%Y-%m-%d') = '1'; +select str_to_date('1','%Y-%m-%d') = '1'; +select str_to_date('','%Y-%m-%d') = ''; + +# these three should work! +select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; + +# +# Bug #30666: Incorrect order when using range conditions on 2 tables or more +# + +CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, + c22 INT DEFAULT NULL, + KEY(c21, c22)); +CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, + c32 INT DEFAULT NULL, + c33 INT NOT NULL, + c34 INT UNSIGNED DEFAULT 0, + KEY (c33, c34, c32)); + +INSERT INTO t1 values (),(),(),(),(); +INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; +INSERT INTO t3 VALUES (1, 1, 1, 0), + (2, 2, 0, 0), + (3, 3, 1, 0), + (4, 4, 0, 0), + (5, 5, 1, 0); + +# Show that ORDER BY produces the correct results order +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND + t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND + t3.c33 = 1 AND t2.c22 in (1, 3) + ORDER BY c32; + +# Show that ORDER BY DESC produces the correct results order +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND + t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND + t3.c33 = 1 AND t2.c22 in (1, 3) + ORDER BY c32 DESC; + +DROP TABLE t1, t2, t3; ########################################################################### --echo diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test index 5e0fc65bc34..018366f705f 100644 --- a/mysql-test/t/skip_grants.test +++ b/mysql-test/t/skip_grants.test @@ -122,3 +122,15 @@ select count(*) from information_schema.COLUMN_PRIVILEGES; select count(*) from information_schema.SCHEMA_PRIVILEGES; select count(*) from information_schema.TABLE_PRIVILEGES; select count(*) from information_schema.USER_PRIVILEGES; + +# +# Bug #32020: loading udfs while --skip-grant-tables is enabled causes out of +# memory errors +# + +--error ER_CANT_INITIALIZE_UDF +CREATE FUNCTION a RETURNS STRING SONAME ''; +--error ER_SP_DOES_NOT_EXIST +DROP FUNCTION a; + +--echo End of 5.0 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 95514fd773e..df51239b256 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2987,6 +2987,63 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1; SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1; DROP TABLE t1,t2; +# +# Bug #31884: Assertion + crash in subquery in the SELECT clause. +# + +CREATE TABLE t1 (a1 INT, a2 INT); +CREATE TABLE t2 (b1 INT, b2 INT); + +INSERT INTO t1 VALUES (100, 200); +INSERT INTO t1 VALUES (101, 201); +INSERT INTO t2 VALUES (101, 201); +INSERT INTO t2 VALUES (103, 203); + +SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; +DROP TABLE t1, t2; + +# +# Bug #28076: inconsistent binary/varbinary comparison +# + +CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); +INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43); + +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); + +CREATE INDEX I1 ON t1 (s1); +CREATE INDEX I2 ON t1 (s2); + +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); + +TRUNCATE t1; +INSERT INTO t1 VALUES (0x41,0x41); +SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1); + +DROP TABLE t1; + +CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1)); +CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2)); +CREATE TABLE t3 (a3 BINARY(2) default '0'); +INSERT INTO t1 VALUES (1),(2),(3),(4); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2; +SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1; +DROP TABLE t1,t2,t3; + +CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3)); +CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY); +CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); +INSERT INTO t2 VALUES (2), (3), (4), (5); +INSERT INTO t3 VALUES (10), (20), (30); +SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; +SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); +DROP TABLE t1, t2, t3; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 9f4634e1e17..1c98a0f8d29 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2246,3 +2246,15 @@ select * from table_25411_a; drop table table_25411_a; drop table table_25411_b; +# +# Bug #31866: MySQL Server crashes on SHOW CREATE TRIGGER statement +# + +--disable-warnings +DROP TRIGGER IF EXISTS trg; +--enable-warnings + +--error ER_TRG_DOES_NOT_EXIST +SHOW CREATE TRIGGER trg; + +--echo End of 5.1 tests. diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 4d61350a613..807d1e6b01e 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -425,5 +425,57 @@ insert into t1 values (-0.123456,0.123456); select group_concat(f1),group_concat(f2) from t1; drop table t1; ---echo End of 5.0 tests +# +# BUG#31450 "Query causes error 1048" +# +create table t1 ( + ua_id decimal(22,0) not null, + ua_invited_by_id decimal(22,0) default NULL, + primary key(ua_id) +); +insert into t1 values (123, NULL), (456, NULL); +--echo this must not produce error 1048: +select * from t1 where ua_invited_by_id not in (select ua_id from t1); + +drop table t1; + +# +# Bug #30889: filesort and order by with float/numeric crashes server +# +--disable_warnings +DROP TABLE IF EXISTS t3; +DROP TABLE IF EXISTS t4; +--enable_warnings +CREATE TABLE t1( a NUMERIC, b INT ); +INSERT INTO t1 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c; +SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c; + +CREATE TABLE t2( a NUMERIC, b INT ); +INSERT INTO t2 VALUES (123456, 100); +SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c; + +CREATE TABLE t3( a DECIMAL, b INT ); +INSERT INTO t3 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c; +SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c; + +CREATE TABLE t4( a DECIMAL, b INT ); +INSERT INTO t4 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c; +SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c; + +delete from t1; +INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5); +show create table t1; + +select round(a,b) as c from t1 order by c; + +DROP TABLE t1, t2, t3, t4; + +--echo End of 5.0 tests |