diff options
Diffstat (limited to 'mysql-test/r')
32 files changed, 733 insertions, 70 deletions
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index bc9daf43f14..a39b424827c 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -454,3 +454,11 @@ select last_insert_id(); last_insert_id() 3 drop table t1; +create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e)); +insert into t1 values(null,1,1,1,now()); +insert into t1 values(null,0,0,0,null); +replace into t1 values(null,1,0,2,null); +select last_insert_id(); +last_insert_id() +3 +drop table t1; diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result index 44c258d7611..f9563b89b76 100644 --- a/mysql-test/r/compare.result +++ b/mysql-test/r/compare.result @@ -90,4 +90,9 @@ Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat('0',`test`.`t2`.`a`,'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a` DROP TABLE t1,t2; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (NOW()),(NOW()),(NOW()); +SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00'; +a +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 0644210eef3..66258aa3eca 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1559,6 +1559,8 @@ SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 c1 1 c1 A NULL NULL NULL YES BTREE DROP TABLE t1; +create user mysqltest_1@'test@test'; +ERROR HY000: Malformed hostname (illegal symbol: '@') CREATE TABLE t1 (a INTEGER AUTO_INCREMENT PRIMARY KEY, b INTEGER NOT NULL); INSERT IGNORE INTO t1 (b) VALUES (5); CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) diff --git a/mysql-test/r/ctype_cp932_binlog_stm.result b/mysql-test/r/ctype_cp932_binlog_stm.result index ff6957202d7..0cd2d395ebc 100644 --- a/mysql-test/r/ctype_cp932_binlog_stm.result +++ b/mysql-test/r/ctype_cp932_binlog_stm.result @@ -40,9 +40,9 @@ IN ind DECIMAL(10,2)) BEGIN INSERT INTO t4 VALUES (ins1, ins2, ind); END -master-bin.000001 784 Query 1 992 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) -master-bin.000001 992 Query 1 1081 use `test`; DROP PROCEDURE bug18293 -master-bin.000001 1081 Query 1 1160 use `test`; DROP TABLE t4 +master-bin.000001 784 Query 1 1048 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172 COLLATE 'latin1_swedish_ci'), NAME_CONST('ins2',_cp932 0xED40ED41ED42 COLLATE 'cp932_japanese_ci'), NAME_CONST('ind',47.93)) +master-bin.000001 1048 Query 1 1137 use `test`; DROP PROCEDURE bug18293 +master-bin.000001 1137 Query 1 1216 use `test`; DROP TABLE t4 End of 5.0 tests SHOW BINLOG EVENTS FROM 364; ERROR HY000: Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 20dd85834dd..ec2182a3304 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -1098,6 +1098,17 @@ ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_gen select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '=' drop table t1; +CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET UCS2); +INSERT INTO t1 VALUES ('a'); +SET @@sql_mode=pad_char_to_full_length; +SELECT HEX(s1) FROM t1; +HEX(s1) +00610020002000200020 +SET @@sql_mode=default; +SELECT HEX(s1) FROM t1; +HEX(s1) +0061 +DROP TABLE t1; set collation_connection=ucs2_general_ci; drop table if exists t1; create table t1 as diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 1fbda27f5da..6a06a9661aa 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -608,6 +608,65 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; a a DROP TABLE t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20) +default NULL); +INSERT INTO t1 VALUES (1,1,'ORANGE'); +INSERT INTO t1 VALUES (2,2,'APPLE'); +INSERT INTO t1 VALUES (3,2,'APPLE'); +INSERT INTO t1 VALUES (4,3,'PEAR'); +SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name = +'APPLE'; +SELECT @v1, @v2; +@v1 @v2 +2 APPLE +SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id, +fruit_name HAVING fruit_name = 'APPLE'; +SELECT @v3, @v4; +@v3 @v4 +2 APPLE +SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE +fruit_name = 'APPLE'; +SELECT @v5, @v6, @v7, @v8; +@v5 @v6 @v7 @v8 +3 PEAR 3 PEAR +SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1 +WHERE fruit_name = 'APPLE'; +SELECT @v5, @v6, @v7, @v8, @v9, @v10; +@v5 @v6 @v7 @v8 @v9 @v10 +3 PEAR 3 PEAR 5 PEARAPPLE +SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO +@v13, @v14 FROM t1 WHERE fruit_name = 'APPLE'; +SELECT @v11, @v12, @v13, @v14; +@v11 @v12 @v13 @v14 +6 PEARPEAR 6 PEARPEAR +SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE'; +SELECT @v15, @v16; +@v15 @v16 +6 PEARPEAR +SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name = +'APPLE'; +SELECT @v17, @v18; +@v17 @v18 +4 Bob +DROP TABLE IF EXISTS t2; +CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20) +default NULL); +SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE +'../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE'; +LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2; +SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE +'../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE'; +LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2; +SELECT @v19, @v20; +@v19 @v20 +2 APPLE +SELECT * FROM t2; +fruit_id fruit_name +2 APPLE +2 APPLE +DROP TABLE t1; +DROP TABLE t2; CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0); SELECT a FROM t1 WHERE a=0; diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result index 64d643505c5..db503f7aa6d 100644 --- a/mysql-test/r/events_2.result +++ b/mysql-test/r/events_2.result @@ -328,4 +328,81 @@ create event очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66 on schedule every 2 year do select 1; ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длинна' is too long +create event event_35981 on schedule every 6 month on completion preserve +disable +do +select 1; +The following SELECTs should all give 1 +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'PRESERVE'; +count(*) +1 +alter event event_35981 enable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'PRESERVE'; +count(*) +1 +alter event event_35981 on completion not preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'NOT PRESERVE'; +count(*) +1 +alter event event_35981 disable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'NOT PRESERVE'; +count(*) +1 +alter event event_35981 on completion preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'PRESERVE'; +count(*) +1 +drop event event_35981; +create event event_35981 on schedule every 6 month disable +do +select 1; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'NOT PRESERVE'; +count(*) +1 +drop event event_35981; +create event event_35981 on schedule every 1 hour starts current_timestamp +on completion not preserve +do +select 1; +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +drop event event_35981; +create event event_35981 on schedule every 1 hour starts current_timestamp +on completion not preserve +do +select 1; +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +drop event event_35981; +create event event_35981 on schedule every 1 hour starts current_timestamp +on completion preserve +do +select 1; +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion not preserve; +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +drop event event_35981; drop database events_test; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index c198176532f..fd9cf69907c 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1416,4 +1416,41 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; AVG(a) CAST(AVG(a) AS DECIMAL) 15 15 DROP TABLE t1; +CREATE TABLE derived1 (a bigint(21)); +INSERT INTO derived1 VALUES (2); +CREATE TABLE D ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) DEFAULT NULL, +int_key int(11) DEFAULT NULL, +filler blob, +PRIMARY KEY (pk), +KEY int_key (int_key) +); +INSERT INTO D VALUES +(39,40,4,repeat(' X', 42)), +(43,56,4,repeat(' X', 42)), +(47,12,4,repeat(' X', 42)), +(71,28,4,repeat(' X', 42)), +(76,54,4,repeat(' X', 42)), +(83,45,4,repeat(' X', 42)), +(105,53,12,NULL); +SELECT +(SELECT COUNT( int_nokey ) +FROM derived1 AS X +WHERE +X.int_nokey < 61 +GROUP BY pk +LIMIT 1) +FROM D AS X +WHERE X.int_key < 13 +GROUP BY int_nokey LIMIT 1; +(SELECT COUNT( int_nokey ) +FROM derived1 AS X +WHERE +X.int_nokey < 61 +GROUP BY pk +LIMIT 1) +1 +DROP TABLE derived1; +DROP TABLE D; End of 5.0 tests diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 1827871861e..45d335bba9e 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -520,6 +520,16 @@ a filler b 4 zz 4 5 qq 4 drop table t1, t2; +CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'), +KEY b(b), KEY a(a)); +INSERT INTO t1 VALUES ('y',''), ('z',''); +SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR +(a='pure-S') OR (a='DE80337a') OR (a='DE80799'); +b a + y + z +DROP TABLE t1; +End of 5.0 tests #---------------- ROR-index_merge tests ----------------------- SET SESSION STORAGE_ENGINE = MyISAM; drop table if exists t0,t1,t2; diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index eff5f117c50..83b8217b570 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -221,3 +221,24 @@ drop view testdb_1.v1, v2, testdb_1.v3, v4; drop database testdb_1; drop user testdb_1@localhost; drop user testdb_2@localhost; +create database testdb_1; +create table testdb_1.t1 (a int); +create view testdb_1.v1 as select * from testdb_1.t1; +grant show view on testdb_1.* to mysqltest_1@localhost; +grant select on testdb_1.v1 to mysqltest_1@localhost; +select table_schema, table_name, view_definition from information_schema.views +where table_name='v1'; +table_schema table_name view_definition +testdb_1 v1 select `testdb_1`.`t1`.`a` AS `a` from `testdb_1`.`t1` +show create view testdb_1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v1` AS select `testdb_1`.`t1`.`a` AS `a` from `testdb_1`.`t1` latin1 latin1_swedish_ci +revoke select on testdb_1.v1 from mysqltest_1@localhost; +select table_schema, table_name, view_definition from information_schema.views +where table_name='v1'; +table_schema table_name view_definition +testdb_1 v1 +show create view testdb_1.v1; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v1' +drop user mysqltest_1@localhost; +drop database testdb_1; diff --git a/mysql-test/r/innodb-semi-consistent.result b/mysql-test/r/innodb-semi-consistent.result index f1139390f20..6173048c320 100644 --- a/mysql-test/r/innodb-semi-consistent.result +++ b/mysql-test/r/innodb-semi-consistent.result @@ -1,4 +1,5 @@ drop table if exists t1; +set binlog_format=mixed; set session transaction isolation level read committed; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2),(3),(4),(5),(6),(7); @@ -6,6 +7,7 @@ set autocommit=0; select * from t1 where a=3 lock in share mode; a 3 +set binlog_format=mixed; set session transaction isolation level read committed; set autocommit=0; update t1 set a=10 where a=5; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 84760e1720c..5f8c59b9a0c 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1024,6 +1024,7 @@ id code name 4 2 Erik 5 3 Sasha COMMIT; +SET binlog_format='MIXED'; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); @@ -2961,9 +2962,11 @@ drop table t1,t2; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); commit; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t1 set b = 5 where b = 1; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; select * from t1 where a = 7 and b = 3 for update; @@ -3002,6 +3005,7 @@ d e 3 1 8 6 12 1 +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t1 select * from t2; @@ -3032,30 +3036,39 @@ a b 3 1 8 6 12 1 +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into t1 select * from t2; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; update t3 set b = (select b from t2 where a = d); +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t5 (select * from t2 lock in share mode); +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t6 set e = (select b from t2 where a = d lock in share mode); +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode; +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t8 (select * from t2 for update); +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t9 set e = (select b from t2 where a = d for update); +SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update; @@ -3202,6 +3215,7 @@ id -10 1 DROP TABLE t1; +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; DROP TABLE IF EXISTS t1, t2; @@ -3212,6 +3226,7 @@ CREATE TABLE t1 ( a int ) ENGINE=InnoDB; CREATE TABLE t2 LIKE t1; SELECT * FROM t2; a +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (1); @@ -3219,10 +3234,12 @@ COMMIT; SELECT * FROM t1 WHERE a=1; a 1 +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; SELECT * FROM t2; a +SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (2); diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 0b33e51ea35..985f4d2b464 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1512,6 +1512,7 @@ ok drop table t1; SET SESSION AUTOCOMMIT = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +set binlog_format=mixed; # Switch to connection con1 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) ENGINE = InnoDB; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index f01ab2bfb8d..736ecf1d90e 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -747,11 +747,13 @@ select t1.b from v1a; ERROR 42S22: Unknown column 't1.b' in 'field list' select * from v1a join v1b on t1.b = t2.b; ERROR 42S22: Unknown column 't1.b' in 'on clause' -select * from information_schema.statistics join information_schema.columns -using(table_name,column_name) where table_name='user'; -TABLE_NAME COLUMN_NAME TABLE_CATALOG TABLE_SCHEMA NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT TABLE_CATALOG TABLE_SCHEMA ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT -user Host NULL mysql 0 mysql PRIMARY 1 A # NULL NULL BTREE NULL mysql 1 NO char 60 180 NULL NULL utf8 utf8_bin char(60) PRI # -user User NULL mysql 0 mysql PRIMARY 2 A # NULL NULL BTREE NULL mysql 2 NO char 16 48 NULL NULL utf8 utf8_bin char(16) PRI # +select +statistics.TABLE_NAME, statistics.COLUMN_NAME, statistics.TABLE_CATALOG, statistics.TABLE_SCHEMA, statistics.NON_UNIQUE, statistics.INDEX_SCHEMA, statistics.INDEX_NAME, statistics.SEQ_IN_INDEX, statistics.COLLATION, statistics.SUB_PART, statistics.PACKED, statistics.NULLABLE, statistics.INDEX_TYPE, statistics.COMMENT, +columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_NULLABLE, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.CHARACTER_OCTET_LENGTH, columns.NUMERIC_PRECISION, columns.NUMERIC_SCALE, columns.CHARACTER_SET_NAME, columns.COLLATION_NAME, columns.COLUMN_TYPE, columns.COLUMN_KEY, columns.EXTRA, columns.COLUMN_COMMENT +from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; +TABLE_NAME COLUMN_NAME TABLE_CATALOG TABLE_SCHEMA NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLLATION SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT TABLE_CATALOG TABLE_SCHEMA COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA COLUMN_COMMENT +user Host NULL mysql 0 mysql PRIMARY 1 A NULL NULL BTREE NULL mysql NO char 60 180 NULL NULL utf8 utf8_bin char(60) PRI +user User NULL mysql 0 mysql PRIMARY 2 A NULL NULL BTREE NULL mysql NO char 16 48 NULL NULL utf8 utf8_bin char(16) PRI drop table t1; drop table t2; drop table t3; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 967259013f6..1e488b320d7 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -337,6 +337,126 @@ DROP VIEW v2; DROP VIEW v3; # -- End of Bug#35469. +Bug#37114 +SET SESSION character_set_client=latin1; +SET SESSION character_set_server=latin1; +SET SESSION character_set_connection=latin1; +SET @OLD_SQL_MODE=@@SESSION.SQL_MODE; +test LOAD DATA INFILE +SET sql_mode = ''; +SELECT '1 \\aa\n' INTO DUMPFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt'; +CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' '; +SELECT * FROM t1; +id val1 +1 \aa +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1; +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS TERMINATED BY ' ' FROM t1; +INSERT INTO t1 (id, val1) VALUES (1, '\aa'); +SELECT * FROM t1; +id val1 +1 \aa +1 \aa +SET sql_mode=''; +INSERT INTO t1 (id, val1) VALUES (1, '\aa'); +SELECT * FROM t1; +id val1 +1 \aa +1 \aa +1 aa +DROP TABLE t1; +test SELECT INTO OUTFILE +CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4)); +CREATE TABLE t2 LIKE t1; +SET sql_mode = ''; +INSERT INTO t1 (id, val1) VALUES (5, '\ttab'); +INSERT INTO t1 (id, val1) VALUES (4, '\\r'); +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; +INSERT INTO t1 (id, val1) VALUES (3, '\tx'); +1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' '; +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION +SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; +t id val1 hex(val1) +before 3 \tx 5C7478 +after 3 \tx 5C7478 +before 4 \r 5C72 +after 4 \r 5C72 +before 5 tab 09746162 +after 5 tab 09746162 +TRUNCATE t2; +SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); +LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") +3 \tx +4 \r +5 tab + +1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '\' TERMINATED BY ' '; +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION +SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; +t id val1 hex(val1) +before 3 \tx 5C7478 +after 3 \tx 5C7478 +before 4 \r 5C72 +after 4 \r 5C72 +before 5 tab 09746162 +after 5 tab 09746162 +TRUNCATE t2; +SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); +LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") +3 \\tx +4 \\r +5 tab + +SET sql_mode = ''; +2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' '; +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION +SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; +t id val1 hex(val1) +before 3 \tx 5C7478 +after 3 \tx 5C7478 +before 4 \r 5C72 +after 4 \r 5C72 +before 5 tab 09746162 +after 5 tab 09746162 +TRUNCATE t2; +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; +SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); +LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") +3 \\tx +4 \\r +5 tab + +SET sql_mode = ''; +2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '' TERMINATED BY ' '; +SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION +SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; +t id val1 hex(val1) +before 3 \tx 5C7478 +after 3 \tx 5C7478 +before 4 \r 5C72 +after 4 \r 5C72 +before 5 tab 09746162 +after 5 tab 09746162 +TRUNCATE t2; +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; +SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); +LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") +3 \tx +4 \r +5 tab + +set session sql_mode=@OLD_SQL_MODE; +DROP TABLE t1,t2; +End of 5.0 tests CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1); SET NAMES latin1; @@ -364,3 +484,4 @@ SET character_set_filesystem=default; select @@character_set_filesystem; @@character_set_filesystem binary +End of 5.1 tests diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index cd05fc1473f..a0e3d9fad06 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -96,6 +96,40 @@ alter table t1 auto_increment=0; alter table t1 auto_increment=0; unlock tables; drop table t1; +CREATE TABLE t1 ( +a int(11) unsigned default NULL, +b varchar(255) default NULL, +UNIQUE KEY a (a), +KEY b (b) +); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); +CREATE TABLE t2 SELECT * FROM t1; +CREATE TABLE t3 SELECT * FROM t1; +# test altering of columns that multiupdate doesn't use +# normal mode +# PS mode +# test altering of columns that multiupdate uses +# normal mode +# PS mode +DROP TABLE t1, t2, t3; +CREATE TABLE t1( a INT, b INT ); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); +# 1. test regular tables +# 1.1. test altering of columns that multiupdate doesn't use +# 1.1.1. normal mode +# 1.1.2. PS mode +# 1.2. test altering of columns that multiupdate uses +# 1.2.1. normal mode +# 1.2.2. PS mode +ALTER TABLE t1 ADD COLUMN a INT; +# 2. test UNIONs +# 2.1. test altering of columns that multiupdate doesn't use +# 2.1.1. normal mode +# 2.1.2. PS mode +# 2.2. test altering of columns that multiupdate uses +# 2.2.1. normal mode +# 2.2.2. PS mode +DROP TABLE t1; End of 5.0 tests create table t1 (i int); lock table t1 read; diff --git a/mysql-test/r/log_basic.result b/mysql-test/r/log_basic.result index 20ec6bcc88f..864982121e5 100644 --- a/mysql-test/r/log_basic.result +++ b/mysql-test/r/log_basic.result @@ -6,8 +6,8 @@ INIT_VALUE SELECT @@log AS INIT_VALUE; INIT_VALUE 1 -SET @@global.log = ON; -SET global log = 0; +SET @@global.general_log = ON; +SET global general_log = 0; 'Bug# 34832: log is a system but it is not accessible using SET @@global.log;' 'SET GLOBAL log; and SELECT @@global.log. SHOW VARIABLES shows the value of log.' '#--------------------FN_DYNVARS_062_02-------------------------#' diff --git a/mysql-test/r/log_bin_trust_routine_creators_basic.result b/mysql-test/r/log_bin_trust_routine_creators_basic.result index 2b7fb548f36..cfcbcddfca3 100644 --- a/mysql-test/r/log_bin_trust_routine_creators_basic.result +++ b/mysql-test/r/log_bin_trust_routine_creators_basic.result @@ -5,17 +5,17 @@ SELECT @start_global_value; '#--------------------FN_DYNVARS_064_01-------------------------#' SET @@global.log_bin_trust_routine_creators = TRUE; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SET @@global.log_bin_trust_routine_creators = DEFAULT; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 0 '#--------------------FN_DYNVARS_064_02-------------------------#' SET @@global.log_bin_trust_routine_creators = DEFAULT; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators = 'FALSE'; @@global.log_bin_trust_routine_creators = 'FALSE' 1 @@ -24,37 +24,37 @@ Warning 1292 Truncated incorrect DOUBLE value: 'FALSE' '#--------------------FN_DYNVARS_064_03-------------------------#' SET @@global.log_bin_trust_routine_creators = ON; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 1 SET @@global.log_bin_trust_routine_creators = OFF; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 0 SET @@global.log_bin_trust_routine_creators = 0; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 0 SET @@global.log_bin_trust_routine_creators = 1; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 1 SET @@global.log_bin_trust_routine_creators = TRUE; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 1 SET @@global.log_bin_trust_routine_creators = FALSE; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 0 @@ -70,7 +70,7 @@ SET @@global.log_bin_trust_routine_creators = "OFFF"; ERROR 42000: Variable 'log_bin_trust_routine_creators' can't be set to the value of 'OFFF' SET @@global.log_bin_trust_routine_creators = OF; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 0 @@ -96,14 +96,14 @@ ERROR HY000: Variable 'log_bin_trust_routine_creators' is a GLOBAL variable '#---------------------FN_DYNVARS_064_07----------------------#' SET @@global.log_bin_trust_routine_creators = TRUE; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@log_bin_trust_routine_creators = @@global.log_bin_trust_routine_creators; @@log_bin_trust_routine_creators = @@global.log_bin_trust_routine_creators 1 '#---------------------FN_DYNVARS_064_08----------------------#' SET @@global.log_bin_trust_routine_creators = TRUE; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@log_bin_trust_routine_creators; @@log_bin_trust_routine_creators 1 @@ -115,7 +115,7 @@ SELECT log_bin_trust_routine_creators = @@session.log_bin_trust_routine_creators ERROR 42S22: Unknown column 'log_bin_trust_routine_creators' in 'field list' SET @@global.log_bin_trust_routine_creators = @start_global_value; Warnings: -Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead +Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead SELECT @@global.log_bin_trust_routine_creators; @@global.log_bin_trust_routine_creators 1 diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 9e1a541fc9e..35fb59671b8 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -190,6 +190,8 @@ SELECT @@general_log, @@log; @@general_log @@log 1 1 SET GLOBAL log = 0; +Warnings: +Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead SHOW VARIABLES LIKE 'general_log'; Variable_name Value general_log OFF @@ -219,6 +221,8 @@ SELECT @@slow_query_log, @@log_slow_queries; @@slow_query_log @@log_slow_queries 0 0 SET GLOBAL log_slow_queries = 0; +Warnings: +Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead SHOW VARIABLES LIKE 'slow_query_log'; Variable_name Value slow_query_log OFF @@ -273,6 +277,30 @@ SET GLOBAL general_log_file = @general_log_file_saved; SET GLOBAL slow_query_log_file = @slow_query_log_file_saved; # -- End of Bug#32748. +deprecated: +SET GLOBAL log = 0; +Warnings: +Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead +SET GLOBAL log_slow_queries = 0; +Warnings: +Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead +SET GLOBAL log = DEFAULT; +Warnings: +Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead +SET GLOBAL log_slow_queries = DEFAULT; +Warnings: +Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead +not deprecated: +SELECT @@global.general_log_file INTO @my_glf; +SELECT @@global.slow_query_log_file INTO @my_sqlf; +SET GLOBAL general_log = 0; +SET GLOBAL slow_query_log = 0; +SET GLOBAL general_log_file = 'WL4403_G.log'; +SET GLOBAL slow_query_log_file = 'WL4403_SQ.log'; +SET GLOBAL general_log_file = @my_glf; +SET GLOBAL slow_query_log_file = @my_sqlf; +SET GLOBAL general_log = DEFAULT; +SET GLOBAL slow_query_log = DEFAULT; End of 5.1 tests set @@global.general_log= @start_general_log; set @@global.slow_query_log= @start_slow_query_log; diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 2af4d5e94dc..be78c6cdbeb 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -839,6 +839,35 @@ 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; +DROP TABLE IF EXISTS log_count; +DROP TABLE IF EXISTS slow_log_copy; +DROP TABLE IF EXISTS general_log_copy; +CREATE TABLE log_count (count BIGINT(21)); +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; +SET GLOBAL general_log = ON; +SET GLOBAL slow_query_log = ON; +CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log)); +DROP TABLE slow_log_copy; +CREATE TABLE general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log)); +DROP TABLE general_log_copy; +SET GLOBAL general_log = OFF; +SET GLOBAL slow_query_log = OFF; +CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log)); +DROP TABLE slow_log_copy; +CREATE TABLE general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log)); +DROP TABLE general_log_copy; +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; +DROP TABLE log_count; SET @old_slow_log_state = @@global.slow_query_log; SET SESSION long_query_time = 0; SET GLOBAL slow_query_log = ON; diff --git a/mysql-test/r/mysqldump-max.result b/mysql-test/r/mysqldump-max.result index 2d118092a2c..c1c61cd7fe8 100644 --- a/mysql-test/r/mysqldump-max.result +++ b/mysql-test/r/mysqldump-max.result @@ -277,3 +277,16 @@ drop table t3; drop table t4; drop table t5; drop table t6; +SELECT @@global.storage_engine INTO @old_engine; +SET GLOBAL storage_engine=InnoDB; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES(); +SELECT COUNT(*) FROM v1; +COUNT(*) +1 +SELECT COUNT(*) FROM v1; +COUNT(*) +1 +DROP VIEW v1; +DROP TABLE t1; +SET GLOBAL storage_engine=@old_engine; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 3e0b4f80f84..ec397d8fb89 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -1,3 +1,8 @@ +Bug#37938 - Test "mysqldump" lacks various insert statements +Turn off concurrent inserts to avoid random errors +NOTE: We reset the variable back to saved value at the end of test +SET @OLD_CONCURRENT_INSERT = @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT = 0; DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa, t3; drop database if exists mysqldump_test_db; drop database if exists db1; @@ -1999,7 +2004,7 @@ SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( `a` varchar(30) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; @@ -2096,7 +2101,7 @@ SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( `a` int(11) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; @@ -2170,7 +2175,7 @@ SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( `a` varchar(30) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; @@ -2286,7 +2291,7 @@ SET character_set_client = utf8; `a` int(11), `b` int(11), `c` varchar(30) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v2`; /*!50001 DROP VIEW IF EXISTS `v2`*/; @@ -2294,7 +2299,7 @@ SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v2` ( `a` int(11) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v3`; /*!50001 DROP VIEW IF EXISTS `v3`*/; @@ -2304,7 +2309,7 @@ SET character_set_client = utf8; `a` int(11), `b` int(11), `c` varchar(30) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; @@ -3041,7 +3046,7 @@ SET character_set_client = utf8; `a` int(11), `b` varchar(32), `c` varchar(32) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v1`; /*!50001 DROP VIEW IF EXISTS `v1`*/; @@ -3051,7 +3056,7 @@ SET character_set_client = utf8; `a` int(11), `b` varchar(32), `c` varchar(32) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v2`; /*!50001 DROP VIEW IF EXISTS `v2`*/; @@ -3061,7 +3066,7 @@ SET character_set_client = utf8; `a` int(11), `b` varchar(32), `c` varchar(32) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; USE `test`; @@ -3441,7 +3446,7 @@ SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( `id` int(11) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; USE `mysqldump_test_db`; @@ -3501,7 +3506,7 @@ SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `nasishnasifu` ( `id` bigint(20) unsigned -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; USE `mysqldump_tables`; @@ -3959,7 +3964,7 @@ SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `v1` ( `id` int(11) -) */; +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; USE `mysqldump_test_db`; @@ -4089,6 +4094,7 @@ DROP DATABASE mysqldump_test_db; # -- End of test case for Bug#32538. +SET @@GLOBAL.CONCURRENT_INSERT = @OLD_CONCURRENT_INSERT; # # End of 5.1 tests # diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index a441a841a07..e76c874324e 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1448,13 +1448,15 @@ create trigger t_ad after delete on t for each row insert into t values (old.s1) insert into t values (1); drop table t; USE mysql; +TRUNCATE TABLE general_log; +SET @old_general_log_state = @@global.general_log; SET GLOBAL general_log = 0; ALTER TABLE general_log ENGINE = MyISAM; ALTER TABLE general_log PARTITION BY RANGE (TO_DAYS(event_time)) (PARTITION p0 VALUES LESS THAN (733144), PARTITION p1 VALUES LESS THAN (3000000)); ERROR HY000: Incorrect usage of PARTITION and log table ALTER TABLE general_log ENGINE = CSV; -SET GLOBAL general_log = default; +SET GLOBAL general_log = @old_general_log_state; use test; create table t2 (b int); create table t1 (b int) @@ -1637,4 +1639,93 @@ select count(*) from t1, t2 where t1.createdDate = t2.createdDate; count(*) 1 drop table t1, t2; +SET @orig_sql_mode = @@SQL_MODE; +SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; +CREATE TABLE t1 (c1 INT) +PARTITION BY LIST(1 DIV c1) ( +PARTITION p0 VALUES IN (NULL), +PARTITION p1 VALUES IN (1) +); +INSERT INTO t1 VALUES (0); +ERROR 22012: Division by 0 +SELECT * FROM t1; +c1 +TRUNCATE t1; +INSERT INTO t1 VALUES (NULL), (0), (1), (2); +ERROR 22012: Division by 0 +SELECT * FROM t1; +c1 +NULL +DROP TABLE t1; +SET SQL_MODE= @orig_sql_mode; +create table t1 (s1 int) partition by hash(s1) partitions 2; +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +s1 +1 +1 +1 +1 +1 +1 +1 +1 +drop table t1; +create table t1 (s1 int) partition by range(s1) +(partition pa1 values less than (10), +partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +s1 +1 +1 +1 +1 +1 +1 +1 +1 +drop table t1; +create table t1 (s1 int) partition by range(s1) +(partition pa1 values less than (10), +partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (20); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +s1 +20 +20 +20 +20 +20 +20 +20 +20 +drop table t1; +create table t1 (s1 int) partition by range(s1) +(partition pa1 values less than (10), +partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +insert into t1 select s1 from t1 where s1=3; +select count(*) from t1; +count(*) +288 +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result index e4e5a748b0d..23a38ff3885 100644 --- a/mysql-test/r/partition_range.result +++ b/mysql-test/r/partition_range.result @@ -742,3 +742,23 @@ WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p407,p408,p409,p507,p508,p509 ALL NULL NULL NULL NULL 18 Using where DROP TABLE t1; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( +defid int(10) unsigned NOT NULL, +day int(10) unsigned NOT NULL, +count int(10) unsigned NOT NULL, +filler char(200), +KEY (defid,day) +) +PARTITION BY RANGE (day) ( +PARTITION p7 VALUES LESS THAN (20070401) , +PARTITION p8 VALUES LESS THAN (20070501)); +insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; +insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; +insert into t2 values(52, 20070321, 123, 'filler') ; +insert into t2 values(52, 20070322, 456, 'filler') ; +select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; +sum(count) +579 +drop table t1, t2; diff --git a/mysql-test/r/slow_launch_time_func.result b/mysql-test/r/slow_launch_time_func.result index 766d7fb4dd9..7665166ff29 100644 --- a/mysql-test/r/slow_launch_time_func.result +++ b/mysql-test/r/slow_launch_time_func.result @@ -1,37 +1,17 @@ SET @global_slow_launch_time = @@GLOBAL.slow_launch_time; -'#--------------------FN_DYNVARS_124_01-------------------------#' ** Connection default ** +'#--------------------FN_DYNVARS_124_01-------------------------#' SET @@GLOBAL.slow_launch_time=0; SELECT @@GLOBAL.slow_launch_time; @@GLOBAL.slow_launch_time 0 -** Connecting conn5 using username 'root' ** -** Connecting conn6 using username 'root' ** -** Connecting conn7 using username 'root' ** -** Connecting conn8 using username 'root' ** -** Connecting conn9 using username 'root' ** -** Connecting conn10 using username 'root' ** -** Connecting conn11 using username 'root' ** -** Connecting conn12 using username 'root' ** -** Connecting conn13 using username 'root' ** -** Connecting conn14 using username 'root' ** -** Connecting conn15 using username 'root' ** -** Connecting conn16 using username 'root' ** -show status like 'slow_launch_threads'; -Variable_name Value -Slow_launch_threads 12 -12 Expected -** Connection default ** -** Disconnecting conn5 ** -** Disconnecting conn6 ** -** Disconnecting conn7 ** -** Disconnecting conn8 ** -** Disconnecting conn9 ** -** Disconnecting conn10 ** -** Disconnecting conn11 ** -** Disconnecting conn12 ** -** Disconnecting conn13 ** -** Disconnecting conn14 ** -** Disconnecting conn15 ** -** Disconnecting conn16 ** +** Connecting conn1 using username 'root' ** +** Switch to connection default and disconnect conn1 ** +'#--------------------FN_DYNVARS_124_02-------------------------#' +SET @@GLOBAL.slow_launch_time= 1000; +SELECT @@GLOBAL.slow_launch_time; +@@GLOBAL.slow_launch_time +1000 +** Connecting conn2 using username 'root' ** +** Switch to connection default and disconnect conn2 ** SET @@GLOBAL.slow_launch_time = @global_slow_launch_time; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index cbb7cc706af..3def6536897 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1629,6 +1629,13 @@ end loop label1; end loop; end| ERROR 42000: End-label label1 without match +CREATE TABLE t1 (a INT)| +INSERT INTO t1 VALUES (1),(2)| +CREATE PROCEDURE p1(a INT) BEGIN END| +CALL p1((SELECT * FROM t1))| +ERROR 21000: Subquery returns more than 1 row +DROP PROCEDURE IF EXISTS p1| +DROP TABLE t1| drop procedure if exists p1; create procedure p1() begin diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index e640d6668b1..0d966908d64 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6836,6 +6836,16 @@ drop procedure p1; drop function f1; drop view v1; drop table t1; +drop procedure if exists `p2` $ +create procedure `p2`(in `a` text charset utf8) +begin +declare `pos` int default 1; +declare `str` text charset utf8; +set `str` := `a`; +select substr(`str`, `pos`+ 1 ) into `str`; +end $ +call `p2`('s s s s s s'); +drop procedure `p2`; # ------------------------------------------------------------------ # -- End of 5.0 tests # ------------------------------------------------------------------ diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 97547ff7c47..1e758ce68c7 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -1,3 +1,5 @@ +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 0; flush status; show status like 'Table_lock%'; Variable_name Value @@ -7,22 +9,31 @@ select * from information_schema.session_status where variable_name like 'Table_ VARIABLE_NAME VARIABLE_VALUE TABLE_LOCKS_IMMEDIATE 2 TABLE_LOCKS_WAITED 0 -SET SQL_LOG_BIN=0; +# Switched to connection: con1 +set sql_log_bin=0; set @old_general_log = @@global.general_log; set global general_log = 'OFF'; drop table if exists t1; create table t1(n int) engine=myisam; insert into t1 values(1); +select 1; +1 +1 +# Switched to connection: con2 lock tables t1 read; unlock tables; lock tables t1 read; +# Switched to connection: con1 update t1 set n = 3; +# Switched to connection: con2 unlock tables; +# Switched to connection: con1 show status like 'Table_locks_waited'; Variable_name Value Table_locks_waited 1 drop table t1; set global general_log = @old_general_log; +# Switched to connection: default select 1; 1 1 @@ -198,3 +209,4 @@ ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table ' drop database db37908; drop procedure proc37908; drop function func37908; +set @@global.concurrent_insert= @old_concurrent_insert; diff --git a/mysql-test/r/trigger-trans.result b/mysql-test/r/trigger-trans.result index 9e0f1e2c351..2c4e355af9d 100644 --- a/mysql-test/r/trigger-trans.result +++ b/mysql-test/r/trigger-trans.result @@ -161,3 +161,32 @@ SELECT @a, @b; 1 1 DROP TABLE t2, t1; End of 5.0 tests +BUG#31612 +Trigger fired multiple times leads to gaps in auto_increment sequence +create table t1 (a int, val char(1)) engine=InnoDB; +create table t2 (b int auto_increment primary key, +val char(1)) engine=InnoDB; +create trigger t1_after_insert after +insert on t1 for each row insert into t2 set val=NEW.val; +insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'), +(123, 'd'), (123, 'e'), (123, 'f'), (123, 'g'); +insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'), +(654, 'd'), (654, 'e'), (654, 'f'), (654, 'g'); +select * from t2 order by b; +b val +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 a +9 b +10 c +11 d +12 e +13 f +14 g +drop trigger t1_after_insert; +drop table t1,t2; diff --git a/mysql-test/r/tx_isolation_func.result b/mysql-test/r/tx_isolation_func.result index 8b763edd70c..2242525f14b 100644 --- a/mysql-test/r/tx_isolation_func.result +++ b/mysql-test/r/tx_isolation_func.result @@ -21,8 +21,10 @@ INSERT INTO t1 VALUES(24, 24); '#----------------------------FN_DYNVARS_184_01--------------------------------------#' ** Connection con0 ** SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +set binlog_format=mixed; ** Connection con1 ** SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +set binlog_format=mixed; ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 2cd132ce03c..2d91835d723 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -121,8 +121,8 @@ select @a:=0; select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i; @a+0 @a:=@a+0+count(*) count(*) @a+0 0 1 1 0 -1 3 2 0 -3 6 3 0 +0 2 2 0 +0 3 3 0 set @a=0; select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; @a @a:="hello" @a @a:=3 @a @a:="hello again" @@ -370,4 +370,33 @@ select @rownum := @rownum + 1 as row, @prev_score := a as score from t1 order by score desc; drop table t1; +create table t1(b bigint); +insert into t1 (b) values (10), (30), (10); +set @var := 0; +select if(b=@var, 999, b) , @var := b from t1 order by b; +if(b=@var, 999, b) @var := b +10 10 +999 10 +30 30 +drop table t1; +create temporary table t1 (id int); +insert into t1 values (2), (3), (3), (4); +set @lastid=-1; +select @lastid != id, @lastid, @lastid := id from t1; +@lastid != id @lastid @lastid := id +1 -1 2 +1 2 3 +0 3 3 +1 3 4 +drop table t1; +create temporary table t1 (id bigint); +insert into t1 values (2), (3), (3), (4); +set @lastid=-1; +select @lastid != id, @lastid, @lastid := id from t1; +@lastid != id @lastid @lastid := id +1 -1 2 +1 2 3 +0 3 3 +1 3 4 +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index a4237597646..7629a1d79a3 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -167,7 +167,7 @@ max_error_count 10 drop table t1; set table_type=MYISAM; Warnings: -Warning 1287 The syntax 'table_type' is deprecated and will be removed in MySQL 5.2. Please use 'storage_engine' instead +Warning 1287 The syntax '@@table_type' is deprecated and will be removed in MySQL 5.2. Please use '@@storage_engine' instead create table t1 (a int); insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); update t1 set a='abc'; |