diff options
Diffstat (limited to 'mysql-test/r')
42 files changed, 1579 insertions, 29 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 5a115e9ea99..db7173d0b47 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1268,4 +1268,66 @@ a b 4 b 5 a DROP TABLE t1; +# +# Bug#45567: Fast ALTER TABLE broken for enum and set +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a ENUM('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +# No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# No copy: Add new enumeration to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Add new enumeration +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# No copy: Add new enumerations to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +DROP TABLE t1; +CREATE TABLE t1 (a SET('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +# No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Add new member +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Numerical incrase (pack lenght) +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index 8c26ea1ff82..e865d775c6a 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12695,3 +12695,25 @@ a b 1 NULL 2 NULL DROP TABLE t1; +CREATE TABLE t1(a INT, b BLOB) ENGINE=archive; +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM +INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DATA_LENGTH AVG_ROW_LENGTH +8666 15 +INSERT INTO t1 VALUES(1, 'sampleblob1'),(2, 'sampleblob2'); +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM +INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DATA_LENGTH AVG_ROW_LENGTH +8700 4350 +DROP TABLE t1; +SET @save_join_buffer_size= @@join_buffer_size; +SET @@join_buffer_size= 8228; +CREATE TABLE t1(a CHAR(255)) ENGINE=archive; +INSERT INTO t1 VALUES('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), +('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), +('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +SELECT COUNT(t1.a) FROM t1, t1 a, t1 b, t1 c, t1 d, t1 e; +COUNT(t1.a) +729 +DROP TABLE t1; +SET @@join_buffer_size= @save_join_buffer_size; diff --git a/mysql-test/r/bug46760.result b/mysql-test/r/bug46760.result new file mode 100644 index 00000000000..413df050b10 --- /dev/null +++ b/mysql-test/r/bug46760.result @@ -0,0 +1,43 @@ +# +# Bug#46760: Fast ALTER TABLE no longer works for InnoDB +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +# By using --enable_info and verifying that number of affected +# rows is 0 we check that this ALTER TABLE is really carried +# out as "fast/online" operation, i.e. without full-blown data +# copying. +# +# I.e. info for the below statement should normally look like: +# +# affected rows: 0 +# info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 10; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT '10' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MySQL Bug#39200: optimize table does not recognize +# ROW_FORMAT=COMPRESSED +# +CREATE TABLE t1 (a INT) ROW_FORMAT=compressed; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status Table is already up to date +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 3373d206952..b829ef30fb1 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1572,6 +1572,19 @@ CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' DROP TABLE t1, t2; +# +# BUG#46384 - mysqld segfault when trying to create table with same +# name as existing view +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (1),(2),(3); +CREATE VIEW v1 AS SELECT t1.a FROM t1, t2; +CREATE TABLE v1 AS SELECT * FROM t1; +ERROR 42S01: Table 'v1' already exists +DROP VIEW v1; +DROP TABLE t1,t2; End of 5.0 tests CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); diff --git a/mysql-test/r/ctype_gbk_binlog.result b/mysql-test/r/ctype_gbk_binlog.result index a49e170ff19..df927af9a6b 100644 --- a/mysql-test/r/ctype_gbk_binlog.result +++ b/mysql-test/r/ctype_gbk_binlog.result @@ -1,3 +1,4 @@ +RESET MASTER; SET NAMES gbk; CREATE TABLE t1 ( f1 BLOB diff --git a/mysql-test/r/ctype_ldml.result b/mysql-test/r/ctype_ldml.result index 711921eb526..fe870f82743 100644 --- a/mysql-test/r/ctype_ldml.result +++ b/mysql-test/r/ctype_ldml.result @@ -321,3 +321,11 @@ Vv Xx YyÝýỲỳỴỵỶỷỸỹ drop table t1; +Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20 +set names latin1; +show collation like 'latin1_test'; +Collation Charset Id Default Compiled Sortlen +latin1_test latin1 99 Yes 1 +select "foo" = "foo " collate latin1_test; +"foo" = "foo " collate latin1_test +1 diff --git a/mysql-test/r/debug_sync.result b/mysql-test/r/debug_sync.result new file mode 100644 index 00000000000..8b46334204c --- /dev/null +++ b/mysql-test/r/debug_sync.result @@ -0,0 +1,277 @@ +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: '' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE 2'; +SET DEBUG_SYNC='p0 SIGNAL s1 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 SIGNAL s1'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 EXECUTE 2'; +SET DEBUG_SYNC='p0 WAIT_FOR s2 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 HIT_LIMIT 3'; +SET DEBUG_SYNC='p0 CLEAR'; +SET DEBUG_SYNC='p0 TEST'; +SET DEBUG_SYNC='RESET'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 timeout 6'; +set debug_sync='p0 signal s1 wait_for s2 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2 execute 2'; +set debug_sync='p0 signal s1 wait_for s2 hit_limit 3'; +set debug_sync='p0 signal s1 wait_for s2'; +set debug_sync='p0 signal s1 execute 2 hit_limit 3'; +set debug_sync='p0 signal s1 execute 2'; +set debug_sync='p0 signal s1 hit_limit 3'; +set debug_sync='p0 signal s1'; +set debug_sync='p0 wait_for s2 timeout 6 execute 2 hit_limit 3'; +set debug_sync='p0 wait_for s2 timeout 6 execute 2'; +set debug_sync='p0 wait_for s2 timeout 6 hit_limit 3'; +set debug_sync='p0 wait_for s2 timeout 6'; +set debug_sync='p0 wait_for s2 execute 2 hit_limit 3'; +set debug_sync='p0 wait_for s2 execute 2'; +set debug_sync='p0 wait_for s2 hit_limit 3'; +set debug_sync='p0 wait_for s2'; +set debug_sync='p0 hit_limit 3'; +set debug_sync='p0 clear'; +set debug_sync='p0 test'; +set debug_sync='reset'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 + EXECUTE 2 HIT_LIMIT 3'; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2'; +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=' p0 SIGNAL s1 WAIT_FOR s2 '; +SET DEBUG_SYNC=''; +ERROR 42000: Missing synchronization point name +SET DEBUG_SYNC=' '; +ERROR 42000: Missing synchronization point name +SET DEBUG_SYNC='p0'; +ERROR 42000: Missing action after synchronization point name 'p0' +SET DEBUG_SYNC='p0 EXECUTE 2'; +ERROR 42000: Missing action before EXECUTE +SET DEBUG_SYNC='p0 TIMEOUT 6 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 TIMEOUT 6'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAL' +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 EXECUTE 2 SIGNAL s1 TIMEOUT 6'; +ERROR 42000: Missing action before EXECUTE +SET DEBUG_SYNC='p0 TIMEOUT 6 SIGNAL s1'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUT' +SET DEBUG_SYNC='p0 EXECUTE 2 TIMEOUT 6 SIGNAL s1'; +ERROR 42000: Missing action before EXECUTE +SET DEBUG_SYNC='p0 CLEAR HIT_LIMIT 3'; +ERROR 42000: Nothing must follow action CLEAR +SET DEBUG_SYNC='CLEAR'; +ERROR 42000: Missing action after synchronization point name 'CLEAR' +SET DEBUG_SYNC='p0 CLEAR p0'; +ERROR 42000: Nothing must follow action CLEAR +SET DEBUG_SYNC='TEST'; +ERROR 42000: Missing action after synchronization point name 'TEST' +SET DEBUG_SYNC='p0 TEST p0'; +ERROR 42000: Nothing must follow action TEST +SET DEBUG_SYNC='p0 RESET'; +ERROR 42000: Illegal or out of order stuff: 'RESET' +SET DEBUG_SYNC='RESET p0'; +ERROR 42000: Illegal or out of order stuff: 'p0' +SET DEBUG_SYNC='p0 RESET p0'; +ERROR 42000: Illegal or out of order stuff: 'RESET' +SET DEBUG_SYNC='p0 SIGNAL '; +ERROR 42000: Missing signal name after action SIGNAL +SET DEBUG_SYNC='p0 WAIT_FOR '; +ERROR 42000: Missing signal name after action WAIT_FOR +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE '; +ERROR 42000: Missing valid number after EXECUTE +SET DEBUG_SYNCx='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +ERROR HY000: Unknown system variable 'DEBUG_SYNCx' +SET DEBUG_SYNC='p0 SIGNAx s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'SIGNAx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOx s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'WAIT_FOx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUx 0 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'TIMEOUx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTx 2 HIT_LIMIT 3'; +ERROR 42000: Illegal or out of order stuff: 'EXECUTx' +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIx 3'; +ERROR 42000: Illegal or out of order stuff: 'HIT_LIMIx' +SET DEBUG_SYNC='p0 CLEARx'; +ERROR 42000: Illegal or out of order stuff: 'CLEARx' +SET DEBUG_SYNC='p0 TESTx'; +ERROR 42000: Illegal or out of order stuff: 'TESTx' +SET DEBUG_SYNC='RESETx'; +ERROR 42000: Missing action after synchronization point name 'RESETx' +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 0x6 EXECUTE 2 HIT_LIMIT 3'; +ERROR 42000: Missing valid number after TIMEOUT +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 0x2 HIT_LIMIT 3'; +ERROR 42000: Missing valid number after EXECUTE +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 7 EXECUTE 2 HIT_LIMIT 0x3'; +ERROR 42000: Missing valid number after HIT_LIMIT +SET DEBUG_SYNC= 7; +ERROR 42000: Incorrect argument type to variable 'debug_sync' +SET GLOBAL DEBUG_SYNC= 'p0 CLEAR'; +ERROR HY000: Variable 'debug_sync' is a SESSION variable and can't be used with SET GLOBAL +SET @myvar= 'now SIGNAL from_myvar'; +SET DEBUG_SYNC= @myvar; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'from_myvar' +SET DEBUG_SYNC= LEFT('now SIGNAL from_function_cut_here', 24); +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'from_function' +SET DEBUG_SYNC= 'now SIGNAL something'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'something' +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; +Warnings: +Warning #### debug sync point wait timed out +SET DEBUG_SYNC= 'now SIGNAL nothing'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'nothing' +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; +SET DEBUG_SYNC= 'now SIGNAL something EXECUTE 0'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 'nothing' +SET DEBUG_SYNC= 'now WAIT_FOR anotherthing TIMEOUT 0 EXECUTE 0'; +SET DEBUG_SYNC= 'now HIT_LIMIT 1'; +ERROR HY000: debug sync point hit limit reached +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: '' +SET DEBUG_SYNC= 'p1abcd SIGNAL s1 EXECUTE 2'; +SET DEBUG_SYNC= 'p2abc SIGNAL s2 EXECUTE 2'; +SET DEBUG_SYNC= 'p9abcdef SIGNAL s9 EXECUTE 2'; +SET DEBUG_SYNC= 'p4a SIGNAL s4 EXECUTE 2'; +SET DEBUG_SYNC= 'p5abcde SIGNAL s5 EXECUTE 2'; +SET DEBUG_SYNC= 'p6ab SIGNAL s6 EXECUTE 2'; +SET DEBUG_SYNC= 'p7 SIGNAL s7 EXECUTE 2'; +SET DEBUG_SYNC= 'p8abcdef SIGNAL s8 EXECUTE 2'; +SET DEBUG_SYNC= 'p3abcdef SIGNAL s3 EXECUTE 2'; +SET DEBUG_SYNC= 'p4a TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's4' +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's1' +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's7' +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's9' +SET DEBUG_SYNC= 'p3abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'p1abcd CLEAR'; +SET DEBUG_SYNC= 'p2abc CLEAR'; +SET DEBUG_SYNC= 'p5abcde CLEAR'; +SET DEBUG_SYNC= 'p6ab CLEAR'; +SET DEBUG_SYNC= 'p8abcdef CLEAR'; +SET DEBUG_SYNC= 'p9abcdef CLEAR'; +SET DEBUG_SYNC= 'p3abcdef CLEAR'; +SET DEBUG_SYNC= 'p4a CLEAR'; +SET DEBUG_SYNC= 'p7 CLEAR'; +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: 's3' +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signal: '' +CREATE USER mysqltest_1@localhost; +GRANT SUPER ON *.* TO mysqltest_1@localhost; +connection con1, mysqltest_1 +SET DEBUG_SYNC= 'RESET'; +connection default +DROP USER mysqltest_1@localhost; +CREATE USER mysqltest_2@localhost; +GRANT ALL ON *.* TO mysqltest_2@localhost; +REVOKE SUPER ON *.* FROM mysqltest_2@localhost; +connection con1, mysqltest_2 +SET DEBUG_SYNC= 'RESET'; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +connection default +DROP USER mysqltest_2@localhost; +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT); +connection con1 +SET DEBUG_SYNC= 'before_lock_tables_takes_lock + SIGNAL opened WAIT_FOR flushed'; +INSERT INTO t1 VALUES(1); +connection default +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed'; +FLUSH TABLE t1; +connection con1 +connection default +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT); +LOCK TABLE t1 WRITE; +connection con1 +SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2'; +INSERT INTO t1 VALUES (1); +connection default +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +UNLOCK TABLES; +connection con1 +retrieve INSERT result. +connection default +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index eb93c69d960..0124a7da35a 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -279,3 +279,48 @@ ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 0, got DROP TABLE t1; DROP FUNCTION f1; End of 5.0 tests +# +# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, +# merge table +# +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +CREATE TABLE t3 ( a INT ); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2); +CREATE TRIGGER tr1 BEFORE DELETE ON t2 +FOR EACH ROW INSERT INTO no_such_table VALUES (1); +DELETE t1, t2, t3 FROM t1, t2, t3; +ERROR 42S02: Table 'test.no_such_table' doesn't exist +SELECT * FROM t1; +a +SELECT * FROM t2; +a +1 +2 +SELECT * FROM t3; +a +1 +2 +DROP TABLE t1, t2, t3; +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +CREATE TABLE t3 ( a INT ); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2); +CREATE TRIGGER tr1 AFTER DELETE ON t2 +FOR EACH ROW INSERT INTO no_such_table VALUES (1); +DELETE t1, t2, t3 FROM t1, t2, t3; +ERROR 42S02: Table 'test.no_such_table' doesn't exist +SELECT * FROM t1; +a +SELECT * FROM t2; +a +2 +SELECT * FROM t3; +a +1 +2 +DROP TABLE t1, t2, t3; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index e0324af8cfd..b1cb70fa43c 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -763,4 +763,34 @@ a b d c 1 2 0 2 1 2 0 3 DROP TABLE t1; +# +# Bug #46159: simple query that never returns +# +SET @old_max_heap_table_size = @@max_heap_table_size; +SET @@max_heap_table_size = 16384; +SET @old_sort_buffer_size = @@sort_buffer_size; +SET @@sort_buffer_size = 32804; +CREATE TABLE t1(c1 int, c2 VARCHAR(20)); +INSERT INTO t1 VALUES (1, '1'), (1, '1'), (2, '2'), (3, '1'), (3, '1'), (4, '4'); +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1 LIMIT 4; +c1 c2 COUNT(*) +1 1 2 +2 2 1 +3 1 2 +4 4 1 +SELECT DISTINCT c2 FROM t1 GROUP BY c1 HAVING COUNT(*) > 1; +c2 +1 +5 +DROP TABLE t1; +SET @@sort_buffer_size = @old_sort_buffer_size; +SET @@max_heap_table_size = @old_max_heap_table_size; End of 5.1 tests diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index a81ff554ca2..96fcbc33d3f 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -159,6 +159,14 @@ CREATE TABLE t1 (a INT PRIMARY KEY); EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a); ERROR 42000: Key 'a' doesn't exist in table 't1' DROP TABLE t1; +CREATE TABLE t1(a LONGTEXT); +INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); +INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); +EXPLAIN SELECT DISTINCT 1 FROM t1, +(SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1 +WHERE t1.a = d1.a; +ERROR 42S22: Unknown column 'd1.a' in 'where clause' +DROP TABLE t1; # # Bug#37870: Usage of uninitialized value caused failed assertion. # diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 3d989ad1730..94147640cde 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1477,3 +1477,47 @@ COUNT(*) SET SQL_MODE=default; DROP TABLE t1; End of 5.0 tests +# +# BUG#47280 - strange results from count(*) with order by multiple +# columns without where/group +# +# +# Initialize test +# +CREATE TABLE t1 ( +pk INT NOT NULL, +i INT, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,11),(2,12),(3,13); +# +# Start test +# All the following queries shall return 1 record +# + +# Masking all correct values {11...13} for column i in this result. +SELECT MAX(pk) as max, i +FROM t1 +ORDER BY max; +max i +3 # + +EXPLAIN +SELECT MAX(pk) as max, i +FROM t1 +ORDER BY max; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary + +# Only 11 is correct for collumn i in this result +SELECT MAX(pk) as max, i +FROM t1 +WHERE pk<2 +ORDER BY max; +max i +1 11 +# +# Cleanup +# +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 88a822a2fa6..ffdacc43735 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -608,4 +608,146 @@ SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN ((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d)); SUM( DISTINCT e ) DROP TABLE t1; +# +# Bug #44139: Table scan when NULL appears in IN clause +# +CREATE TABLE t1 ( +c_int INT NOT NULL, +c_decimal DECIMAL(5,2) NOT NULL, +c_float FLOAT(5, 2) NOT NULL, +c_bit BIT(10) NOT NULL, +c_date DATE NOT NULL, +c_datetime DATETIME NOT NULL, +c_timestamp TIMESTAMP NOT NULL, +c_time TIME NOT NULL, +c_year YEAR NOT NULL, +c_char CHAR(10) NOT NULL, +INDEX(c_int), INDEX(c_decimal), INDEX(c_float), INDEX(c_bit), INDEX(c_date), +INDEX(c_datetime), INDEX(c_timestamp), INDEX(c_time), INDEX(c_year), +INDEX(c_char)); +INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5); +INSERT INTO t1 (c_int) SELECT 0 FROM t1; +INSERT INTO t1 (c_int) SELECT 0 FROM t1; +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_date +IN ('2009-09-01', '2009-09-02', '2009-09-03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_date +IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_datetime +IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_datetime +IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_timestamp +IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_timestamp +IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index a0c3935fde0..2a2fe50ad0f 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2534,6 +2534,15 @@ SELECT LOAD_FILE(a) FROM t1; LOAD_FILE(a) NULL DROP TABLE t1; +CREATE TABLE t1 (f2 VARCHAR(20)); +CREATE TABLE t2 (f2 VARCHAR(20)); +INSERT INTO t1 VALUES ('MIN'),('MAX'); +INSERT INTO t2 VALUES ('LOAD'); +SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1; +concat_name +LOAD_MIN +LOAD_MAX +DROP TABLE t1, t2; End of 5.0 tests drop table if exists t1; create table t1(f1 tinyint default null)engine=myisam; diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index ac9a53ca238..620f5dc19ec 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -876,10 +876,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by @@ -924,7 +924,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by diff --git a/mysql-test/r/have_debug_sync.require b/mysql-test/r/have_debug_sync.require new file mode 100644 index 00000000000..c2090bc5657 --- /dev/null +++ b/mysql-test/r/have_debug_sync.require @@ -0,0 +1,2 @@ +debug_sync +1 diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 4cc96d3e9ce..bc7500e4690 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -139,7 +139,7 @@ show create view testdb_1.v7; View Create View character_set_client collation_connection v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci 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 +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist show fields from testdb_1.v7; Field Type Null Key Default Extra f1 char(4) YES NULL @@ -169,7 +169,7 @@ show create view testdb_1.v7; View Create View character_set_client collation_connection v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci 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 +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist revoke insert(f1) on v3 from testdb_2@localhost; revoke show view on v5 from testdb_2@localhost; use testdb_1; @@ -187,7 +187,8 @@ ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' show create view testdb_1.v7; ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' show create view v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `v3`.`f1` AS `f1`,`v3`.`f2` AS `f2` from `testdb_1`.`v3` latin1 latin1_swedish_ci show fields from v4; Field Type Null Key Default Extra f1 char(4) YES NULL diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index ade4db35ce6..d2e8eb19e0c 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -867,3 +867,25 @@ INSERT INTO t2 SELECT NULL FROM t1; Got one of the listed errors DROP TABLE t1; DROP TABLE t2; +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (null); +INSERT INTO t1 VALUES (null); +ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; +SELECT * FROM t1; +d1 +1 +3 +SELECT * FROM t1; +d1 +1 +3 +INSERT INTO t1 VALUES(null); +Got one of the listed errors +ALTER TABLE t1 AUTO_INCREMENT = 3; +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +d1 +1 +3 +4 +DROP TABLE t1; diff --git a/mysql-test/r/innodb_bug44369.result b/mysql-test/r/innodb_bug44369.result new file mode 100644 index 00000000000..e4b84ecac19 --- /dev/null +++ b/mysql-test/r/innodb_bug44369.result @@ -0,0 +1,14 @@ +create table bug44369 (DB_ROW_ID int) engine=innodb; +ERROR HY000: Can't create table 'test.bug44369' (errno: -1) +create table bug44369 (db_row_id int) engine=innodb; +ERROR HY000: Can't create table 'test.bug44369' (errno: -1) +show errors; +Level Code Message +Error 1005 Error creating table 'test/bug44369' with column name 'db_row_id'. 'db_row_id' is a reserved name. Please try to re-create the table with a different column name. +Error 1005 Can't create table 'test.bug44369' (errno: -1) +create table bug44369 (db_TRX_Id int) engine=innodb; +ERROR HY000: Can't create table 'test.bug44369' (errno: -1) +show errors; +Level Code Message +Error 1005 Error creating table 'test/bug44369' with column name 'db_TRX_Id'. 'db_TRX_Id' is a reserved name. Please try to re-create the table with a different column name. +Error 1005 Can't create table 'test.bug44369' (errno: -1) diff --git a/mysql-test/r/innodb_bug46000.result b/mysql-test/r/innodb_bug46000.result new file mode 100644 index 00000000000..ccff888a48d --- /dev/null +++ b/mysql-test/r/innodb_bug46000.result @@ -0,0 +1,17 @@ +create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; +ERROR HY000: Can't create table 'test.bug46000' (errno: -1) +create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; +ERROR HY000: Can't create table 'test.bug46000' (errno: -1) +show errors; +Level Code Message +Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Error 1005 Can't create table 'test.bug46000' (errno: -1) +create table bug46000(id int) engine=innodb; +create index GEN_CLUST_INDEX on bug46000(id); +ERROR HY000: Can't create table '#sql-temporary' (errno: -1) +show errors; +Level Code Message +Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Error 1005 Can't create table '#sql-temporary' (errno: -1) +create index idx on bug46000(id); +drop table bug46000; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 3ff5f04b6c6..b112bde4f27 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -385,9 +385,10 @@ name dept rs5 cs10 rs5 cs9 DELETE FROM t1; +# Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by +1 SIMPLE t1 range name name 44 NULL # Using where; Using index for group-by SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept DROP TABLE t1; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 736ecf1d90e..77f73532474 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1064,3 +1064,13 @@ a b c d 128 NULL 128 NULL DROP TABLE IF EXISTS t1,t2; End of 5.0 tests. +CREATE TABLE t1 (f1 int); +CREATE TABLE t2 (f1 int); +INSERT INTO t2 VALUES (1); +CREATE VIEW v1 AS SELECT * FROM t2; +PREPARE stmt FROM 'UPDATE t2 AS A NATURAL JOIN v1 B SET B.f1 = 1'; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1, t2; diff --git a/mysql-test/r/lowercase_mixed_tmpdir_innodb.result b/mysql-test/r/lowercase_mixed_tmpdir_innodb.result new file mode 100755 index 00000000000..a478b49cfda --- /dev/null +++ b/mysql-test/r/lowercase_mixed_tmpdir_innodb.result @@ -0,0 +1,6 @@ +drop table if exists t1; +create table t1 (id int) engine=InnoDB; +insert into t1 values (1); +create temporary table t2 engine=InnoDB select * from t1; +drop temporary table t2; +drop table t1; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 732b1b260f8..f5050ccf1c0 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -2252,4 +2252,44 @@ h+0 d + 0 e g + 0 1 1 3 0 1 1 4 0 DROP TABLE t1; +# +# Test of BUG#35570 CHECKSUM TABLE unreliable if LINESTRING field +# (same content / differen checksum) +# +CREATE TABLE t1 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t1 VALUES (GeomFromText("POINT(0 0)")); +checksum table t1; +Table Checksum +test.t1 326284887 +CREATE TABLE t2 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t2 VALUES (GeomFromText("POINT(0 0)")); +checksum table t2; +Table Checksum +test.t2 326284887 +CREATE TABLE t3 select * from t1; +checksum table t3; +Table Checksum +test.t3 326284887 +drop table t1,t2,t3; +CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); +INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), +(6,'0'),(7,'0'); +INSERT INTO t1 SELECT a+10,b FROM t1; +INSERT INTO t1 SELECT a+20,b FROM t1; +INSERT INTO t1 SELECT a+40,b FROM t1; +INSERT INTO t1 SELECT a+80,b FROM t1; +INSERT INTO t1 SELECT a+160,b FROM t1; +INSERT INTO t1 SELECT a+320,b FROM t1; +INSERT INTO t1 SELECT a+640,b FROM t1; +INSERT INTO t1 SELECT a+1280,b FROM t1; +INSERT INTO t1 SELECT a+2560,b FROM t1; +INSERT INTO t1 SELECT a+5120,b FROM t1; +SET myisam_sort_buffer_size=4; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair error myisam_sort_buffer_size is too small +test.t1 repair warning Number of rows changed from 0 to 7168 +test.t1 repair status OK +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/mysqlbinlog-cp932.result b/mysql-test/r/mysqlbinlog-cp932.result index 1640a3b1642..cbf6159516a 100644 --- a/mysql-test/r/mysqlbinlog-cp932.result +++ b/mysql-test/r/mysqlbinlog-cp932.result @@ -1,4 +1,4 @@ -flush logs; +RESET MASTER; create table t3 (f text character set utf8); create table t4 (f text character set cp932); flush logs; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 295a2f41d40..5f32561798b 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -44,16 +44,16 @@ SET TIMESTAMP=1000000000/*!*/; insert into t2 values () /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; DELIMITER ; # End of log file @@ -144,16 +144,16 @@ SET TIMESTAMP=1000000000/*!*/; insert into t2 values () /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; DELIMITER ; # End of log file @@ -359,29 +359,29 @@ SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a varchar(64) character set utf8) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=7/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=7/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-a-0' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-a-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-b-0' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-b-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-c-0' INTO table t1 character set koi8r +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-c-0' INTO TABLE `t1` CHARACTER SET koi8r FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; drop table t1 @@ -473,5 +473,94 @@ IS NOT NULL SET @@global.server_id= 1; RESET MASTER; FLUSH LOGS; +RESET MASTER; +FLUSH LOGS; +# +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +ROLLBACK/*!*/; +use test/*!*/; +SET TIMESTAMP=1253783037/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +create table t1(a int) engine= innodb +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t1 (a) values (1) +/*!*/; +COMMIT/*!*/; +SET TIMESTAMP=1253783037/*!*/; +create table t3(a int) engine= innodb +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t3 (a) values (2) +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +ROLLBACK +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +create table t5(a int) engine= NDB +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t5 (a) values (3) +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +COMMIT +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +# +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +ROLLBACK/*!*/; +SET TIMESTAMP=1253783037/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +BEGIN +/*!*/; +COMMIT/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +ROLLBACK +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +COMMIT +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; End of 5.0 tests End of 5.1 tests diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index f68413264e4..2e3a9489593 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -317,6 +317,7 @@ here is the sourced script outer=2 ifval=0 outer=1 ifval=1 here is the sourced script +ERROR 42S02: Table 'test.nowhere' doesn't exist In loop here is the sourced script diff --git a/mysql-test/r/not_true.require b/mysql-test/r/not_true.require new file mode 100644 index 00000000000..0032832f3d1 --- /dev/null +++ b/mysql-test/r/not_true.require @@ -0,0 +1,2 @@ +TRUE +NULL diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 306fce1f3c2..0c72f816c21 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1557,3 +1557,34 @@ a 2001 1991 DROP TABLE t1; +# +# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering +# is used +# +CREATE TABLE t1 (a INT, b INT, KEY (a)); +INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL); +INSERT INTO t1 SELECT a+4, b FROM t1; +INSERT INTO t1 SELECT a+8, b FROM t1; +CREATE TABLE t2 (a INT, b INT); +INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL); +INSERT INTO t2 SELECT a+4, b FROM t2; +# shouldn't have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +# should have "using filesort" +EXPLAIN +SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +# should have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +DROP TABLE t1, t2; +End of 5.1 tests diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 2d54a66fe11..6611d39628f 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -50,6 +50,21 @@ t1 CREATE TABLE `t1` ( PARTITION p3 VALUES LESS THAN (733969) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ DROP TABLE t1; +create table t1 (a int, b int, key(a)) +partition by list (a) +( partition p0 values in (1), +partition p1 values in (2)); +insert into t1 values (1,1),(2,1),(2,2),(2,3); +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A 1 NULL NULL YES BTREE +drop table t1; CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a)) ENGINE=MyISAM PARTITION BY HASH (a); diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index ad4d08e89ff..b8cfa25349d 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -1,4 +1,18 @@ drop table if exists t1; +create table t1 (a int not null, +b datetime not null, +primary key (a,b)) +engine=innodb +partition by range (to_days(b)) +subpartition by hash (a) +subpartitions 2 +( partition p0 values less than (to_days('2009-01-01')), +partition p1 values less than (to_days('2009-02-01')), +partition p2 values less than (to_days('2009-03-01')), +partition p3 values less than maxvalue); +alter table t1 reorganize partition p1,p2 into +( partition p2 values less than (to_days('2009-03-01'))); +drop table t1; CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (5), @@ -256,3 +270,7 @@ SUBPARTITION BY KEY (char_column) SUBPARTITIONS 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ drop table t1; +CREATE TABLE t1 (a INT) ENGINE=InnoDB +PARTITION BY list(a) (PARTITION p1 VALUES IN (1)); +CREATE INDEX i1 ON t1 (a); +DROP TABLE t1; diff --git a/mysql-test/r/partition_innodb_builtin.result b/mysql-test/r/partition_innodb_builtin.result new file mode 100644 index 00000000000..384ce0790a4 --- /dev/null +++ b/mysql-test/r/partition_innodb_builtin.result @@ -0,0 +1,39 @@ +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) +(SUBPARTITION `sp0``\""e`, +SUBPARTITION `sp1``\""e`), +PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) +(SUBPARTITION `sp2``\""e`, +SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +# con1 +SET NAMES utf8; +START TRANSACTION; +# default connection +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +# con1 +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +# default connection +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# First table reported in 'SHOW ENGINE InnoDB STATUS' +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @@sql_mode = @old_sql_mode; +# con1 +ROLLBACK; +# default connection +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/r/partition_innodb_plugin.result b/mysql-test/r/partition_innodb_plugin.result new file mode 100644 index 00000000000..dd91eee316a --- /dev/null +++ b/mysql-test/r/partition_innodb_plugin.result @@ -0,0 +1,50 @@ +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) +(SUBPARTITION `sp0``\""e`, +SUBPARTITION `sp1``\""e`), +PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) +(SUBPARTITION `sp2``\""e`, +SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +# con1 +SET NAMES utf8; +START TRANSACTION; +# default connection +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +# con1 +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +# default connection +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +lock_table COUNT(*) +`test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ 2 +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +lock_table COUNT(*) +"test"."t`\""""e" /* Partition "p0`\""""e", Subpartition "sp0`\""""e" */ 2 +set @@sql_mode = @old_sql_mode; +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# First table reported in 'SHOW ENGINE InnoDB STATUS' +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @@sql_mode = @old_sql_mode; +# con1 +ROLLBACK; +# default connection +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/r/partition_open_files_limit.result b/mysql-test/r/partition_open_files_limit.result new file mode 100644 index 00000000000..1441ba4e78e --- /dev/null +++ b/mysql-test/r/partition_open_files_limit.result @@ -0,0 +1,22 @@ +DROP TABLE IF EXISTS `t1`; +# Bug#46922: crash when adding partitions and open_files_limit is reached +CREATE TABLE t1 (a INT PRIMARY KEY) +ENGINE=MyISAM PARTITION BY KEY () PARTITIONS 1; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +# if the bug exists, then crash will happen here +ALTER TABLE t1 ADD PARTITION PARTITIONS 511; +ERROR HY000: Out of resources when opening file '<partition file>' (Errcode: 24) +SELECT * FROM t1; +a +1 +10 +11 +2 +3 +4 +5 +6 +7 +8 +9 +DROP TABLE t1; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index cc5e8d2be96..c98a7696ea6 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1219,3 +1219,182 @@ explain select * from t2 where a=1000 and b<11; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 5 const 502 Using where drop table t1, t2; +CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); +CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); +CREATE TABLE t3( a INT, b INT, KEY( a, b ) ); +INSERT INTO t1( a, b ) +VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7); +INSERT INTO t2( a, b ) +VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1), +( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), +(11, 1), (12, 1), (13, 1), (14, 1), (15, 1), +(16, 1), (17, 1), (18, 1), (19, 1), (20, 1); +INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1; +INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1; +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t3 +VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), +(6, 0), (7, 0), (8, 0), (9, 0), (10, 0); +INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 < a AND b = 3 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 < a AND b = 3 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 <= a AND b = 3 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 <= a AND b = 3 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +5 <= a AND b = 3 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +5 <= a AND b = 3 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 1 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +a b +1 1 +2 1 +3 1 +4 1 +5 1 +6 1 +7 1 +8 1 +9 1 +10 1 +11 1 +12 1 +13 1 +14 1 +15 1 +15 3 +16 1 +16 3 +17 1 +17 3 +18 1 +18 3 +19 1 +19 3 +20 1 +EXPLAIN +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 1 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 2 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +a b +1 1 +2 1 +3 1 +4 1 +5 1 +5 2 +6 1 +6 2 +7 1 +7 2 +8 1 +8 2 +9 1 +9 2 +10 1 +11 1 +12 1 +13 1 +14 1 +15 1 +15 3 +16 1 +16 3 +17 1 +17 3 +18 1 +18 3 +19 1 +19 3 +20 1 +EXPLAIN +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 2 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index +SELECT * FROM t3 WHERE +5 <= a AND a < 10 AND b = 3 OR +a < 5 OR +a < 10; +a b +1 0 +2 0 +3 0 +4 0 +5 0 +6 0 +7 0 +8 0 +9 0 +EXPLAIN +SELECT * FROM t3 WHERE +5 <= a AND a < 10 AND b = 3 OR +a < 5 OR +a < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range a a 5 NULL 8 Using where; Using index +DROP TABLE t1, t2, t3; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 3ad556b8c30..67514c314f4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6963,6 +6963,22 @@ CALL p1(); CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; +# +# Bug #46629: Item_in_subselect::val_int(): Assertion `0' +# on subquery inside a SP +# +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT, b INT PRIMARY KEY); +CREATE PROCEDURE p1 () +BEGIN +SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B); +END| +CALL p1; +ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' +CALL p1; +ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' +DROP PROCEDURE p1; +DROP TABLE t1, t2; # ------------------------------------------------------------------ # -- End of 5.1 tests # ------------------------------------------------------------------ diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result new file mode 100644 index 00000000000..482e0045840 --- /dev/null +++ b/mysql-test/r/subselect4.result @@ -0,0 +1,61 @@ +# +# Bug #46791: Assertion failed:(table->key_read==0),function unknown +# function,file sql_base.cc +# +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,1),(2,2); +CREATE TABLE t3 LIKE t1; +# should have 1 impossible where and 2 dependent subqueries +EXPLAIN +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +# should not crash the next statement +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); +1 +1 +# should not crash: the crash is caused by the previous statement +SELECT 1; +1 +1 +DROP TABLE t1,t2,t3; +# +# Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing +# query +# +CREATE TABLE t1 ( +a INT, +b INT, +PRIMARY KEY (a), +KEY b (b) +); +INSERT INTO t1 VALUES (1, 1), (2, 1); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1; +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 SELECT * FROM t1; +# Should not crash. +# Should have 1 impossible where and 2 dependent subqs. +EXPLAIN +SELECT +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index +2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer +# should return 0 rows +SELECT +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +DROP TABLE t1,t2,t3; +End of 5.0 tests. diff --git a/mysql-test/r/trigger_notembedded.result b/mysql-test/r/trigger_notembedded.result index f8975cb2ee5..4673ac26f73 100644 --- a/mysql-test/r/trigger_notembedded.result +++ b/mysql-test/r/trigger_notembedded.result @@ -180,8 +180,6 @@ NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL SET @a = 5 ROW BEFOR DROP USER mysqltest_dfn@localhost; DROP USER mysqltest_inv@localhost; DROP DATABASE mysqltest_db1; -Warnings: -Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index b831771d9c5..51feab0a421 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -749,6 +749,16 @@ bin(a1) 110000111111111 110001011111111 drop table t1bit7, t2bit7; +# +# Bug42803: Field_bit does not have unsigned_flag field, +# can lead to bad memory access +# +CREATE TABLE t1 (a BIT(7), b BIT(9), KEY(a, b)); +INSERT INTO t1 VALUES(0, 0), (5, 3), (5, 6), (6, 4), (7, 0); +EXPLAIN SELECT a+0, b+0 FROM t1 WHERE a > 4 and b < 7 ORDER BY 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 2 NULL 4 Using where; Using index; Using filesort +DROP TABLE t1; End of 5.0 tests create table t1(a bit(7)); insert into t1 values(0x40); diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 15410ac2039..601b364fbbe 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -392,4 +392,20 @@ a 4 DROP FUNCTION sequence; DROP TABLE t1,t2; +# +# Bug#46259: 5.0.83 -> 5.1.36, query doesn't work +# +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1), (2), (3); +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b` + 1, 1 ); +b +1 +2 +3 +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 ); +b +2 +3 +1 +DROP TABLE t1; End of 5.0 tests. diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 982b3b98d9c..a9d4164cda4 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -606,7 +606,7 @@ SHOW CREATE VIEW v; View Create View character_set_client collation_connection v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist SELECT * FROM v; ERROR HY000: The user specified as a definer ('no-such-user'@'localhost') does not exist DROP VIEW v; @@ -963,7 +963,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; Warnings: Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist @@ -971,7 +971,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; Warnings: Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist @@ -979,7 +979,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`no_such`@`user_2` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist DROP VIEW v1; DROP TABLE t1; CREATE USER mysqluser1@localhost; @@ -1044,3 +1044,177 @@ DROP DATABASE mysqltest1; DROP VIEW test.v3; DROP USER mysqluser1@localhost; USE test; +# +# Bug#35996: SELECT + SHOW VIEW should be enough to display view +# definition +# +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW +ON mysqltest2.* TO mysqluser1@localhost; +USE mysqltest1; +CREATE TABLE t1( a INT ); +CREATE TABLE t2( a INT, b INT ); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b; +GRANT SELECT ON TABLE t1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost; +GRANT SELECT ON TABLE v1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost; +CREATE VIEW v_t1 AS SELECT * FROM t1; +CREATE VIEW v_t2 AS SELECT * FROM t2; +CREATE VIEW v_f1 AS SELECT f1() AS a; +CREATE VIEW v_v1 AS SELECT * FROM v1; +CREATE VIEW v_v2 AS SELECT * FROM v2; +GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost; +CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a; +CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1; +CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost; +REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost; +REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost; +REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +# Testing the case when the views reference missing objects. +# Obviously, there are no privileges to check for, so we +# need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost; +SHOW CREATE VIEW mysqltest1.v_t1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_t1' +SHOW CREATE VIEW mysqltest1.v_f1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_f1' +SHOW CREATE VIEW mysqltest1.v_v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_v1' +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP USER mysqluser1@localhost; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +USE test; +CREATE TABLE t1( a INT ); +CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 2e393aea9e4..8a87852d582 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -313,4 +313,9 @@ ERROR 22001: Data too long for column 'c_tinytext' at row 1 insert into t2 values(@q); ERROR 22001: Data too long for column 'c_tinyblob' at row 1 drop table t1, t2; +DROP TABLE t1; +ERROR 42S02: Unknown table 't1' +SHOW ERRORS; +Level Code Message +Error 1051 Unknown table 't1' End of 5.0 tests diff --git a/mysql-test/r/windows.result b/mysql-test/r/windows.result index 4e0d73ea0eb..d0cdd858d4a 100644 --- a/mysql-test/r/windows.result +++ b/mysql-test/r/windows.result @@ -53,3 +53,10 @@ ERROR HY000: No paths allowed for shared library execute abc; ERROR HY000: No paths allowed for shared library deallocate prepare abc; +# +# Bug#45498: Socket variable not available on Windows +# +SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME = 'socket'; +VARIABLE_NAME +SOCKET |