From 2e5b082867c0b4026d2a53acc84d9e432ed8e348 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 18 Sep 2009 12:34:08 +0300 Subject: Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing query The fix for bug 46749 removed the check for OUTER_REF_TABLE_BIT and substituted it for a check on the presence of Item_ident::depended_from. Removing it altogether was wrong : OUTER_REF_TABLE_BIT should still be checked in addition to depended_from (because it's not set in all cases and doesn't contradict to the check of depended_from). Fixed by returning the old condition back as a compliment to the new one. --- mysql-test/t/subselect4.test | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index ff4cdf3c439..440eca22828 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -28,5 +28,37 @@ SELECT 1; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing +--echo # query +--echo # + +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; + +--echo # Should not crash. +--echo # 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; + +--echo # should return 0 rows +SELECT + (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; + +DROP TABLE t1,t2,t3; --echo End of 5.0 tests. -- cgit v1.2.1 From 971715d200e1c8b7b0f9b22a22291cde9d3a88b0 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 18 Sep 2009 16:01:18 +0300 Subject: Bug#46760: Fast ALTER TABLE no longer works for InnoDB Despite copying the value of the old table's row type we don't always have to mark row type as being specified. Innodb uses this to check if it can do fast ALTER TABLE or not. Fixed by correctly flagging the presence of row_type only when it's actually changed. Added a test case for 39200. --- mysql-test/t/bug46760-master.opt | 2 ++ mysql-test/t/bug46760.test | 38 ++++++++++++++++++++++++++++++++++++++ 2 files changed, 40 insertions(+) create mode 100644 mysql-test/t/bug46760-master.opt create mode 100644 mysql-test/t/bug46760.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/bug46760-master.opt b/mysql-test/t/bug46760-master.opt new file mode 100644 index 00000000000..f830d135149 --- /dev/null +++ b/mysql-test/t/bug46760-master.opt @@ -0,0 +1,2 @@ +--innodb-lock-wait-timeout=2 +--innodb-file-per-table diff --git a/mysql-test/t/bug46760.test b/mysql-test/t/bug46760.test new file mode 100644 index 00000000000..f55edbbfa42 --- /dev/null +++ b/mysql-test/t/bug46760.test @@ -0,0 +1,38 @@ +-- source include/have_innodb.inc + +--echo # +--echo # Bug#46760: Fast ALTER TABLE no longer works for InnoDB +--echo # + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); + +--echo # By using --enable_info and verifying that number of affected +--echo # rows is 0 we check that this ALTER TABLE is really carried +--echo # out as "fast/online" operation, i.e. without full-blown data +--echo # copying. +--echo # +--echo # I.e. info for the below statement should normally look like: +--echo # +--echo # affected rows: 0 +--echo # info: Records: 0 Duplicates: 0 Warnings: 0 + +--enable_info +ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 10; +--disable_info +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +--echo # +--echo # MySQL Bug#39200: optimize table does not recognize +--echo # ROW_FORMAT=COMPRESSED +--echo # + +CREATE TABLE t1 (a INT) ROW_FORMAT=compressed; +SHOW CREATE TABLE t1; +OPTIMIZE TABLE t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo End of 5.1 tests -- cgit v1.2.1 From 88fe9da44c7eb969dbed31ba656926c3f6f18a92 Mon Sep 17 00:00:00 2001 From: Kristofer Pettersson Date: Mon, 21 Sep 2009 11:58:15 +0200 Subject: Fix for BUG#35570 "CHECKSUM TABLE unreliable if LINESTRING field (same content/ differen checksum)" The problem was that checksum of GEOMETRY type used memory addresses in the computation, making it un-repeatable thus useless. (This patch is a backport from 6.0 branch) mysql-test/r/myisam.result: test case for bug35570 that same tables give same checksums mysql-test/t/myisam.test: test case for bug35570 that same tables give same checksums sql/sql_table.cc: Type GEOMETRY is implemented on top of type BLOB, so, just like for BLOB, its 'field' contains pointers which it does not make sense to include in the checksum; it rather has to be converted to a string and then we can compute the checksum. --- mysql-test/t/myisam.test | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index ba6bc05cfea..5de7c997a24 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1503,5 +1503,20 @@ SELECT h+0, d + 0, e, g + 0 FROM t1; DROP TABLE t1; +--echo # +--echo # Test of BUG#35570 CHECKSUM TABLE unreliable if LINESTRING field +--echo # (same content / differen checksum) +--echo # + +CREATE TABLE t1 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t1 VALUES (GeomFromText("POINT(0 0)")); +checksum table t1; +CREATE TABLE t2 (line LINESTRING NOT NULL) engine=myisam; +INSERT INTO t2 VALUES (GeomFromText("POINT(0 0)")); +checksum table t2; +CREATE TABLE t3 select * from t1; +checksum table t3; +drop table t1,t2,t3; + --echo End of 5.1 tests -- cgit v1.2.1 From cce577044cad910a8e7a125acd6c290984609607 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Tue, 22 Sep 2009 08:22:07 -0300 Subject: Bug#45498: Socket variable not available on Windows The "socket" variable is not available on Windows even though the --socket option can be used to specify the pipe name for local connections that use a named pipe. The solution is to ensure that the variable is always defined. mysql-test/r/windows.result: Add test case result for Bug#45498 mysql-test/t/windows.test: Add test case for Bug#45498 sql/set_var.cc: socket variable must always be present. --- mysql-test/t/windows.test | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/windows.test b/mysql-test/t/windows.test index 89cd2ed19e8..b7d31948d23 100755 --- a/mysql-test/t/windows.test +++ b/mysql-test/t/windows.test @@ -92,3 +92,9 @@ execute abc; execute abc; deallocate prepare abc; +--echo # +--echo # Bug#45498: Socket variable not available on Windows +--echo # + +SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES + WHERE VARIABLE_NAME = 'socket'; -- cgit v1.2.1 From e3eca7fbd38556b1c4543efe9d32c070ccda047b Mon Sep 17 00:00:00 2001 From: Satya B Date: Wed, 23 Sep 2009 17:42:12 +0530 Subject: Additional Fix for BUG#44030 - Error: (1500) Couldn't read the MAX(ID) autoinc value from the index (PRIMARY) With the fix for BUG#46760, we correctly flag the presence of row_type only when it's actually changed and enables the FAST ALTER TABLE which was disabled with the BUG#39200. So the changes made by BUG#46760 makes MySQL data dictionaries to be out of sync but they are handled already by InnoDB with this BUG#44030. The test was originally written to handle this but we requested Innodb to update the test as the data dictionaries were in sync after the fix for BUG#39200. Adjusting the innodb-autoinc testcase as mentioned in the comments. mysql-test/lib/mtr_cases.pm: Re-enable the innodb-autoinc test case for plugin as we have a common result file. mysql-test/r/innodb-autoinc.result: Additional Fix for BUG#44030 - Error: (1500) Couldn't read the MAX(ID) autoinc value from the index (PRIMARY) Adjust the innodb-autoinc testcase as the patch for BUG#46760 enables the FAST ALTER TABLE and makes the data dictonaries go out of sync. This is expected in the testcase. mysql-test/t/innodb-autoinc.test: Additional Fix for BUG#44030 - Error: (1500) Couldn't read the MAX(ID) autoinc value from the index (PRIMARY) Adjust the innodb-autoinc testcase as the patch for BUG#46760 enables the FAST ALTER TABLE and makes the data dictonaries go out of sync. This is expected in the testcase. --- mysql-test/t/innodb-autoinc.test | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test index b51014ef4b3..61c42f45733 100644 --- a/mysql-test/t/innodb-autoinc.test +++ b/mysql-test/t/innodb-autoinc.test @@ -492,11 +492,7 @@ SELECT * FROM t1; # The MySQL and InnoDB data dictionaries should now be out of sync. # The select should print message to the error log SELECT * FROM t1; -# MySQL have made a change (http://lists.mysql.com/commits/75268) that no -# longer results in the two data dictionaries being out of sync. If they -# revert their changes then this check for ER_AUTOINC_READ_FAILED will need -# to be enabled. -# -- error ER_AUTOINC_READ_FAILED,1467 +-- error ER_AUTOINC_READ_FAILED,1467 INSERT INTO t1 VALUES(null); ALTER TABLE t1 AUTO_INCREMENT = 3; INSERT INTO t1 VALUES(null); -- cgit v1.2.1 From 6504c3eb890e2155cb61e38367011c805746a007 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Fri, 25 Sep 2009 11:26:49 +0200 Subject: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs Problem was still not completely fixed, due to qouting. This is the server side only fix (in explain_filename), the change from filename_to_tablename to use explain_filename in the InnoDB code must be done before the bug is fixed. mysql-test/include/have_not_innodb_plugin.inc: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs Added include file to allow test for only the 'old' built-in innodb engine mysql-test/r/not_true.require: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs Added require to match 'not' TRUE mysql-test/r/partition_innodb_builtin.result: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs New result file for partitioning specific to the 'old' built-in innodb engine mysql-test/r/partition_innodb_plugin.result: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs New result file for partitioning specific to the new plugin innodb engine mysql-test/t/disabled.def: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs Disabling the new test until the fix is included in the InnoDB source too. mysql-test/t/partition_innodb_builtin.test: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs New test file for partitioning specific to the 'old' built-in innodb engine mysql-test/t/partition_innodb_plugin.test: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs New test file for partitioning specific to the new plugin innodb engine sql/mysql_priv.h: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs Added thd as a parameter to explain_filename to be able to use the correct quote character sql/sql_table.cc: Bug#32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs Changed explain_filename, so that it does qouting correctly according to the sessions qoute char. --- mysql-test/t/disabled.def | 3 +- mysql-test/t/partition_innodb_builtin.test | 67 ++++++++++++++++++++++++++ mysql-test/t/partition_innodb_plugin.test | 75 ++++++++++++++++++++++++++++++ 3 files changed, 144 insertions(+), 1 deletion(-) create mode 100644 mysql-test/t/partition_innodb_builtin.test create mode 100644 mysql-test/t/partition_innodb_plugin.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 5436b7166f4..6613b6da415 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -13,4 +13,5 @@ kill : Bug#37780 2008-12-03 HHunger need some changes to be innodb_bug39438 : Bug#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically init_connect : Bug#44920 2009-07-06 pcrews MTR not processing master.opt input properly on Windows. *Must be done this way due to the nature of the bug* - +partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes +partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes diff --git a/mysql-test/t/partition_innodb_builtin.test b/mysql-test/t/partition_innodb_builtin.test new file mode 100644 index 00000000000..a9be41c7455 --- /dev/null +++ b/mysql-test/t/partition_innodb_builtin.test @@ -0,0 +1,67 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source include/have_not_innodb_plugin.inc + +# +# Bug#32430 - show engine innodb status causes errors +# +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; +--echo # con1 +connect(con1,localhost,root,,); +SET NAMES utf8; +START TRANSACTION; +--echo # default connection +connection default; +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +--echo # con1 +connection con1; +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +let $id_1= `SELECT CONNECTION_ID()`; +SEND; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +--echo # default connection +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = $id_1 AND STATE = 'Searching rows for update'; +--source include/wait_condition.inc +#--echo # tested wait condition $wait_condition_reps times +--error ER_LOCK_DEADLOCK +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +--echo # First table reported in 'SHOW ENGINE InnoDB STATUS' +# RECORD LOCKS space id 0 page no 50 n bits 80 index `PRIMARY` in \ +# Database `test`, Table `t1`, Partition `p0`, Subpartition `sp0` \ +# trx id 0 775 +# NOTE: replace_regex is very slow on match copy/past '(.*)' regex's +# on big texts, removing a lot of text before + after makes it much faster. +#/.*in (.*) trx.*/\1/ +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +# INNODB_LOCKS only exists in innodb_plugin +#SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @@sql_mode = @old_sql_mode; +--echo # con1 +connection con1; +REAP; +ROLLBACK; +disconnect con1; +--echo # default connection +connection default; +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/t/partition_innodb_plugin.test b/mysql-test/t/partition_innodb_plugin.test new file mode 100644 index 00000000000..fed8c96424a --- /dev/null +++ b/mysql-test/t/partition_innodb_plugin.test @@ -0,0 +1,75 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source suite/innodb/include/have_innodb_plugin.inc + +# +# Bug#32430 - show engine innodb status causes errors +# +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; +--echo # con1 +connect(con1,localhost,root,,); +SET NAMES utf8; +START TRANSACTION; +--echo # default connection +connection default; +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +--echo # con1 +connection con1; +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +let $id_1= `SELECT CONNECTION_ID()`; +SEND; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +--echo # default connection +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = $id_1 AND STATE = 'Searching rows for update'; +--source include/wait_condition.inc +#--echo # tested wait condition $wait_condition_reps times +# INNODB_LOCKS only exists in innodb_plugin +--sorted_result +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +--sorted_result +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +set @@sql_mode = @old_sql_mode; +--error ER_LOCK_DEADLOCK +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +--echo # First table reported in 'SHOW ENGINE InnoDB STATUS' +# RECORD LOCKS space id 0 page no 50 n bits 80 index `PRIMARY` in \ +# Database `test`, Table `t1`, Partition `p0`, Subpartition `sp0` \ +# trx id 0 775 +# NOTE: replace_regex is very slow on match copy/past '(.*)' regex's +# on big texts, removing a lot of text before + after makes it much faster. +#/.*in (.*) trx.*/\1/ +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @@sql_mode = @old_sql_mode; +--echo # con1 +connection con1; +REAP; +ROLLBACK; +disconnect con1; +--echo # default connection +connection default; +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; -- cgit v1.2.1 From 24f97dc168d96d7acf97aaa85860aadd5596198e Mon Sep 17 00:00:00 2001 From: Martin Hansson Date: Mon, 28 Sep 2009 12:48:52 +0200 Subject: Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, merge table The problem with break statements is that they have very local effects. Hence a break statement within the inner loop of a nested-loops join caused execution to proceed to the next table even though a serious error occurred. The problem was fixed by breaking out the inner loop into its own method. The change empowers all errors to terminate the execution. The errors that will now halt multi-DELETE execution altogether are - triggers returning errors - handler errors - server being killed mysql-test/r/delete.result: Bug#46958: Test result. mysql-test/t/delete.test: Bug#46958: Test case. sql/sql_class.h: Bug#46958: New method declaration. sql/sql_delete.cc: Bug#46958: New method implementation. --- mysql-test/t/delete.test | 44 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 44 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 602e30687c8..d77f5eb128b 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -292,3 +292,47 @@ DROP TABLE t1; DROP FUNCTION f1; --echo End of 5.0 tests + +--echo # +--echo # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, +--echo # merge table +--echo # +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); + +--error ER_NO_SUCH_TABLE +DELETE t1, t2, t3 FROM t1, t2, t3; + +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; + +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); + +--error ER_NO_SUCH_TABLE +DELETE t1, t2, t3 FROM t1, t2, t3; + +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; + +DROP TABLE t1, t2, t3; -- cgit v1.2.1 From da8e69f1018443dce968cc98ceafe95d96f5dfa7 Mon Sep 17 00:00:00 2001 From: Martin Hansson Date: Mon, 28 Sep 2009 13:25:47 +0200 Subject: Bug#35996: SELECT + SHOW VIEW should be enough to display view definition During SHOW CREATE VIEW there is no reason to 'anonymize' errors that name objects that a user does not have access to. Moreover it was inconsistently implemented. For example base tables being referenced from a view appear to be ok, but not views. The manual on the other hand is clear: If a user has the privileges SELECT and SHOW VIEW, the view definition is available to that user, period. The fix changes the behavior to support the manual. mysql-test/r/information_schema_db.result: Bug#35996: Changed warnings. mysql-test/r/view_grant.result: Bug#35996: Changed warnings, test result. mysql-test/t/information_schema_db.test: Bug#35996: Changed test case to reflect new behavior. mysql-test/t/view_grant.test: Bug#35996: Test case. sql/sql_acl.cc: Bug#35996: Code no longer necessary, we may as well exempt SHOW CREATE VIEW from this check. sql/sql_show.cc: Bug#35996: The fix: An Internal_error_handler that hides most errors raised by access checking as they are not relevant to SHOW CREATE VIEW. sql/table.cc: Bug#35996: Restricting this hack to act only when there is no Internal_error_handler. --- mysql-test/t/information_schema_db.test | 1 - mysql-test/t/view_grant.test | 121 ++++++++++++++++++++++++++++++++ 2 files changed, 121 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 0ff1d05f364..2f651057e5c 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -184,7 +184,6 @@ show fields from testdb_1.v7; --error ER_TABLEACCESS_DENIED_ERROR show create view testdb_1.v7; ---error ER_VIEW_NO_EXPLAIN show create view v4; #--error ER_VIEW_NO_EXPLAIN show fields from v4; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 824c67d867e..2ad488b7529 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1382,6 +1382,127 @@ DROP VIEW test.v3; DROP USER mysqluser1@localhost; USE test; +--echo # +--echo # Bug#35996: SELECT + SHOW VIEW should be enough to display view +--echo # definition +--echo # +-- source include/not_embedded.inc +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; + +--connect (connection1, localhost, mysqluser1,, mysqltest2) +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; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +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; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +--echo # Testing the case when the views reference missing objects. +--echo # Obviously, there are no privileges to check for, so we +--echo # need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--connection default +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; + +--connection connection1 +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_t1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_f1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--disconnect connection1 +--connection default +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; +SHOW CREATE VIEW v1; +DROP TABLE t1; +DROP VIEW v1; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc -- cgit v1.2.1 From 57e1a127914b15a51fdd36c818ca93d7def0f59a Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Mon, 28 Sep 2009 05:41:10 -0700 Subject: Bug#43746: YACC return wrong query string when parse 'load data infile' sql statement "load data" statements were written to the binlog as a mix of the original statement and bits recreated from parse-info. This relied on implementation details and broke with IGNORE_SPACES and versioned comments. We now completely resynthesize the query for LOAD DATA for binlog (which among other things normalizes them somewhat with regard to case, spaces, etc.). We have already parsed the query properly, so we make use of that rather than mix-and-match string literals and parsed items. This should make us safe with regard to versioned comments, even those spanning multiple tokens. Also no longer affected by IGNORE_SPACES. mysql-test/r/mysqlbinlog.result: LOAD DATA INFILE normalized mysql-test/suite/binlog/r/binlog_killed_simulate.result: LOAD DATA INFILE normalized mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result: LOAD DATA INFILE normalized mysql-test/suite/binlog/r/binlog_stm_blackhole.result: LOAD DATA INFILE normalized mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result: LOAD DATA INFILE normalized mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result: LOAD DATA INFILE normalized mysql-test/suite/rpl/r/rpl_loaddata.result: LOAD DATA INFILE normalized mysql-test/suite/rpl/r/rpl_loaddata_fatal.result: LOAD DATA INFILE normalized; offsets adjusted to reflect that mysql-test/suite/rpl/r/rpl_loaddata_map.result: LOAD DATA INFILE normalized mysql-test/suite/rpl/r/rpl_loaddatalocal.result: test for #43746 - trying to break LOAD DATA part of parser mysql-test/suite/rpl/r/rpl_stm_log.result: LOAD DATA INFILE normalized mysql-test/suite/rpl/t/rpl_loaddatalocal.test: try to break the LOAD DATA part of the parser (test for #43746) mysql-test/t/mysqlbinlog.test: LOAD DATA INFILE normalized; adjust offsets to reflect that sql/log_event.cc: clean up Load_log_event::print_query and friends so they don't print excess spaces. add support for printing charset names to print_query. sql/log_event.h: We already have three places where we synthesize LOAD DATA queries. Better use one of those! sql/sql_lex.h: When binlogging LOAD DATA statements, we make up the statement to be logged (from the parse-info, rather than substrings of the original query) now. Consequently, we no longer need (string-) pointers into the original query. sql/sql_load.cc: Completely rewrote write_execute_load_query_log_event() to synthesize the LOAD DATA statement wholesale, rather than piece it together from synthesized bits and literal excerpts from the original query. This will not only give us a nice, normalized statement (all uppercase, no excess spaces, etc.), it will also handle comments, including versioned comments right, which is certainly more than we can say about the previous incarnation. sql/sql_yacc.yy: We're no longer assembling LOAD DATA statements from bodyparts of the original query, so some bookkeeping in the parser can go. --- mysql-test/t/mysqlbinlog.test | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 7767abe43d0..9af631b7187 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -71,7 +71,8 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=239 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=330 $MYSQLD_DATADIR/master-bin.000002 + # These are tests for remote binlog. # They should return the same as previous test. @@ -107,7 +108,7 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=239 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=330 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 # Bug#7853 mysqlbinlog does not accept input from stdin --disable_query_log -- cgit v1.2.1 From 6d004e13e29975bc160c2db93b101ba75a3cfb4b Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Tue, 29 Sep 2009 07:23:38 +0500 Subject: Bug#47150 Assertion in Field_long::val_int() on MERGE + TRIGGER + multi-table UPDATE The bug is not related to MERGE table or TRIGGER. More correct description would be 'assertion on multi-table UPDATE + NATURAL JOIN + MERGEABLE VIEW'. On PREPARE stage(see test case) we call mark_common_columns() func which creates ON condition for NATURAL JOIN and sets appropriate table read_set bitmaps for fields which are used in ON condition. On EXECUTE stage mark_common_columns() is not called, we set necessary read_set bitmaps in setup_conds(). But 'B.f1' field is already processed and related item alredy fixed before setup_conds() as updated field and setup_conds can not set read_set bitmap because of that. The fix is to set read_set bitmap for appropriate table field even if Item_direct_view_ref item which represents a refernce to this field is fixed. mysql-test/r/join.result: test result mysql-test/t/join.test: test case sql/item.cc: The bug is not related to MERGE table or TRIGGER. More correct description would be 'assertion on multi-table UPDATE + NATURAL JOIN + MERGEABLE VIEW'. On PREPARE stage(see test case) we call mark_common_columns() func which creates ON condition for NATURAL JOIN and sets appropriate table read_set bitmaps for fields which are used in ON condition. On EXECUTE stage mark_common_columns() is not called, we set necessary read_set bitmaps in setup_conds(). But 'B.f1' field is already processed and related item alredy fixed before setup_conds() as updated field and setup_conds can not set read_set bitmap because of that. The fix is to set read_set bitmap for appropriate table field even if Item_direct_view_ref item which represents a refernce to this field is fixed. --- mysql-test/t/join.test | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index b5e30e63f54..1cd05c8cb65 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -729,4 +729,24 @@ SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; DROP TABLE IF EXISTS t1,t2; + --echo End of 5.0 tests. + + +# +# Bug#47150 Assertion in Field_long::val_int() on MERGE + TRIGGER + multi-table UPDATE +# +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; -- cgit v1.2.1 From 3cb2dfdb2a7fdae3bd77dc2fe63b0f08446e3738 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Tue, 29 Sep 2009 07:58:42 -0300 Subject: Bug#45567: Fast ALTER TABLE broken for enum and set The problem was that appending values to the end of an existing ENUM or SET column was being treated as table data modification, preventing a immediately (fast) table alteration that occurs when only table metadata is being modified. The cause was twofold: adding a enumeration or set members to the end of the list of valid member values was not being considered a "compatible" table alteration, and for SET columns, the check was being done upon the max display length and not the underlying (pack) length of the field. The solution is to augment the function that checks wether two ENUM or SET fields are compatible -- by comparing the pack lengths and performing a limited comparison of the member values. mysql-test/r/alter_table.result: Add test case result for Bug#45567 mysql-test/t/alter_table.test: Add test case for Bug#45567 sql/field.cc: Check whether two fields can be considered 'equal' for table alteration purposes. Fields are equal if they retain the same pack length and if new members are added to the end of the list. sql/field.h: Add comment and remove method. --- mysql-test/t/alter_table.test | 46 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 46 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index ae48d5a8736..17549745203 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1000,4 +1000,50 @@ ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # Bug#45567: Fast ALTER TABLE broken for enum and set +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a ENUM('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); +--echo # No copy: Add new enumeration to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); +--echo # Copy: Add new enumeration +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); +--echo # No copy: Add new enumerations to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); +--disable_info +DROP TABLE t1; + +CREATE TABLE t1 (a SET('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); +--echo # Copy: Add new member +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); +--echo # Copy: Numerical incrase (pack lenght) +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); +--disable_info +DROP TABLE t1; + --echo End of 5.1 tests -- cgit v1.2.1 From 7eac08363896c13f6f8f38b4367d7fd748d412be Mon Sep 17 00:00:00 2001 From: Ingo Struewing Date: Tue, 29 Sep 2009 17:38:40 +0200 Subject: WL#4259 - Debug Sync Facility Backport from 6.0 to 5.1. Only those sync points are included, which are used in debug_sync.test. The Debug Sync Facility allows to place synchronization points in the code: open_tables(...) DEBUG_SYNC(thd, "after_open_tables"); lock_tables(...) When activated, a sync point can - Send a signal and/or - Wait for a signal Nomenclature: - signal: A value of a global variable that persists until overwritten by a new signal. The global variable can also be seen as a "signal post" or "flag mast". Then the signal is what is attached to the "signal post" or "flag mast". - send a signal: Assign the value (the signal) to the global variable ("set a flag") and broadcast a global condition to wake those waiting for a signal. - wait for a signal: Loop over waiting for the global condition until the global value matches the wait-for signal. Please find more information in the top comment in debug_sync.cc or in the worklog entry. .bzrignore: WL#4259 - Debug Sync Facility Added the symbolic link libmysqld/debug_sync.cc. CMakeLists.txt: WL#4259 - Debug Sync Facility Added definition for ENABLED_DEBUG_SYNC. configure.in: WL#4259 - Debug Sync Facility Added definition for ENABLED_DEBUG_SYNC. include/my_sys.h: WL#4259 - Debug Sync Facility Added definition for the DEBUG_SYNC_C macro. libmysqld/CMakeLists.txt: WL#4259 - Debug Sync Facility Added sql/debug_sync.cc. libmysqld/Makefile.am: WL#4259 - Debug Sync Facility Added sql/debug_sync.cc. mysql-test/include/have_debug_sync.inc: WL#4259 - Debug Sync Facility New include file. mysql-test/mysql-test-run.pl: WL#4259 - Debug Sync Facility Added option --debug_sync_timeout. mysql-test/r/debug_sync.result: WL#4259 - Debug Sync Facility New test result. mysql-test/r/have_debug_sync.require: WL#4259 - Debug Sync Facility New require file. mysql-test/t/debug_sync.test: WL#4259 - Debug Sync Facility New test file. mysys/my_static.c: WL#4259 - Debug Sync Facility Added definition for debug_sync_C_callback_ptr. mysys/thr_lock.c: WL#4259 - Debug Sync Facility Added sync point "wait_for_lock". sql/CMakeLists.txt: WL#4259 - Debug Sync Facility Added debug_sync.cc and debug_sync.h. sql/Makefile.am: WL#4259 - Debug Sync Facility Added debug_sync.cc and debug_sync.h. sql/debug_sync.cc: WL#4259 - Debug Sync Facility New source file. sql/debug_sync.h: WL#4259 - Debug Sync Facility New header file. sql/mysqld.cc: WL#4259 - Debug Sync Facility Added opt_debug_sync_timeout. Added calls to debug_sync_init() and debug_sync_end(). Fixed a purecov comment (unrelated). sql/set_var.cc: WL#4259 - Debug Sync Facility Added server variable "debug_sync". sql/set_var.h: WL#4259 - Debug Sync Facility Added declaration for server variable "debug_sync". sql/share/errmsg.txt: WL#4259 - Debug Sync Facility Added error messages ER_DEBUG_SYNC_TIMEOUT and ER_DEBUG_SYNC_HIT_LIMIT. sql/sql_base.cc: WL#4259 - Debug Sync Facility Added sync points "after_flush_unlock" and "before_lock_tables_takes_lock". sql/sql_class.cc: WL#4259 - Debug Sync Facility Added initialization for debug_sync_control to THD::THD. Added calls to debug_sync_init_thread() and debug_sync_end_thread(). sql/sql_class.h: WL#4259 - Debug Sync Facility Added element debug_sync_control to THD. storage/myisam/myisamchk.c: Fixed a typo in an error message string (unrelated). --- mysql-test/t/debug_sync.test | 420 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 420 insertions(+) create mode 100644 mysql-test/t/debug_sync.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/debug_sync.test b/mysql-test/t/debug_sync.test new file mode 100644 index 00000000000..514e471b603 --- /dev/null +++ b/mysql-test/t/debug_sync.test @@ -0,0 +1,420 @@ +###################### t/debug_sync.test ############################### +# # +# Testing of the Debug Sync Facility. # +# # +# There is important documentation within sql/debug_sync.cc # +# # +# Used objects in this test case: # +# p0 - synchronization point 0. Non-existent dummy sync point. # +# s1 - signal 1. # +# s2 - signal 2. # +# # +# Creation: # +# 2008-02-18 istruewing # +# # +######################################################################## + +# +# We need the Debug Sync Facility. +# +--source include/have_debug_sync.inc + +# +# We are checking privileges, which the embedded server cannot do. +# +--source include/not_embedded.inc + +# +# Preparative cleanup. +# +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Show the special system variable. +# It shows ON or OFF depending on the command line option --debug-sync. +# The test case assumes it is ON (command line option present). +# +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +# +# Syntax. Valid forms. +# +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'; + +# +# Syntax. Valid forms. Lower case. +# +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'; + +# +# Syntax. Valid forms. Line wrap, leading, mid, trailing space. +# +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 '; + +# +# Syntax. Invalid forms. +# +--error ER_PARSE_ERROR +SET DEBUG_SYNC=''; +--error ER_PARSE_ERROR +SET DEBUG_SYNC=' '; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6 EXECUTE 2'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 EXECUTE 2 SIGNAL s1 TIMEOUT 6'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TIMEOUT 6 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 EXECUTE 2 TIMEOUT 6 SIGNAL s1'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 CLEAR HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='CLEAR'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 CLEAR p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='TEST'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TEST p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 RESET'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='RESET p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 RESET p0'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL '; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR '; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE '; + +# +# Syntax. Invalid keywords used. +# +--error ER_UNKNOWN_SYSTEM_VARIABLE +SET DEBUG_SYNCx='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAx s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOx s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUx 0 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTx 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIx 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 CLEARx'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 TESTx'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='RESETx'; + +# +# Syntax. Invalid numbers. Decimal only. +# +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 0x6 EXECUTE 2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 0x2 HIT_LIMIT 3'; +--error ER_PARSE_ERROR +SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 7 EXECUTE 2 HIT_LIMIT 0x3'; + +# +# Syntax. Invalid value type. +# +--error ER_WRONG_TYPE_FOR_VAR +SET DEBUG_SYNC= 7; + +# +# Syntax. DEBUG_SYNC is a SESSION-only variable. +# +--error ER_LOCAL_VARIABLE +SET GLOBAL DEBUG_SYNC= 'p0 CLEAR'; + +# +# Syntax. The variable value does not need to be a string literal. +# +SET @myvar= 'now SIGNAL from_myvar'; +SET DEBUG_SYNC= @myvar; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# +SET DEBUG_SYNC= LEFT('now SIGNAL from_function_cut_here', 24); +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +# +# Functional tests. +# +# NOTE: There is the special synchronization point 'now'. It is placed +# immediately after setting of the DEBUG_SYNC variable. +# So it is executed before the SET statement ends. +# +# NOTE: There is only one global signal (say "signal post" or "flag mast"). +# A SIGNAL action writes its signal into it ("sets a flag"). +# The signal persists until explicitly overwritten. +# To avoid confusion for later tests, it is recommended to clear +# the signal by signalling "empty" ("setting the 'empty' flag"): +# SET DEBUG_SYNC= 'now SIGNAL empty'; +# Preferably you can reset the whole facility with: +# SET DEBUG_SYNC= 'RESET'; +# The signal is then '' (really empty) which connot be done otherwise. +# + +# +# Time out immediately. This gives just a warning. +# +SET DEBUG_SYNC= 'now SIGNAL something'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# Suppress warning number +--replace_column 2 #### +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; +# +# If signal is present already, TIMEOUT 0 does not give a warning. +# +SET DEBUG_SYNC= 'now SIGNAL nothing'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; + +# +# EXECUTE 0 is effectively a no-op. +# +SET DEBUG_SYNC= 'now SIGNAL something EXECUTE 0'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'now WAIT_FOR anotherthing TIMEOUT 0 EXECUTE 0'; + +# +# Run into HIT_LIMIT. This gives an error. +# +--error ER_DEBUG_SYNC_HIT_LIMIT +SET DEBUG_SYNC= 'now HIT_LIMIT 1'; + +# +# Many actions. Watch the array growing and shrinking in the debug trace: +# egrep 'query:|debug_sync_action:' mysql-test/var/log/master.trace +# +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +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'; +# +# Execute some actions to show they exist. Each sets a distinct signal. +# +SET DEBUG_SYNC= 'p4a TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p3abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# +# Clear the actions. +# +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'; +# +# Execute some actions to show they have gone. +# +SET DEBUG_SYNC= 'p1abcd TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p7 TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'p9abcdef TEST'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +# +# Now cleanup. Actions are clear already, but signal needs to be cleared. +# +SET DEBUG_SYNC= 'RESET'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +# +# Facility requires SUPER privilege. +# +CREATE USER mysqltest_1@localhost; +GRANT SUPER ON *.* TO mysqltest_1@localhost; +--echo connection con1, mysqltest_1 +connect (con1,localhost,mysqltest_1,,); +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +--echo connection default +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; +--echo connection con1, mysqltest_2 +connect (con1,localhost,mysqltest_2,,); +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +--echo connection default +connection default; +DROP USER mysqltest_2@localhost; + +# +# Example 1. +# +# Preparative cleanup. +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +--enable_warnings +# +# Test. +CREATE TABLE t1 (c1 INT); + --echo connection con1 + connect (con1,localhost,root,,); + SET DEBUG_SYNC= 'before_lock_tables_takes_lock + SIGNAL opened WAIT_FOR flushed'; + send INSERT INTO t1 VALUES(1); +--echo connection default +connection default; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed'; +FLUSH TABLE t1; + --echo connection con1 + connection con1; + reap; + disconnect con1; +--echo connection default +connection default; +DROP TABLE t1; + +# +# Example 2. +# +# Preparative cleanup. +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +DROP TABLE IF EXISTS t1; +--enable_warnings +# +# Test. +CREATE TABLE t1 (c1 INT); +LOCK TABLE t1 WRITE; + --echo connection con1 + connect (con1,localhost,root,,); + # Retain action after use. First used by general_log. + SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2'; + send INSERT INTO t1 VALUES (1); +--echo connection default +connection default; +# Wait until INSERT waits for lock. +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# let INSERT continue. +UNLOCK TABLES; + --echo connection con1 + connection con1; + --echo retrieve INSERT result. + reap; + disconnect con1; +--echo connection default +connection default; +DROP TABLE t1; + +# +# Cleanup after test case. +# Otherwise signal would contain 'flushed' here, +# which could confuse the next test. +# +SET DEBUG_SYNC= 'RESET'; + -- cgit v1.2.1 From dec257ebf28b4f53e78dd14e9dc3e8b75316090d Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 30 Sep 2009 10:01:52 +0800 Subject: Bug #46998 mysqlbinlog can't output BEGIN even if the database is included in a transaction The 'BEGIN/COMMIT/ROLLBACK' log event could be filtered out if the database is not selected by --database option of mysqlbinlog command. This can result in problem if there are some statements in the transaction are not filtered out. To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' in regardless of the database filtering rules. client/mysqlbinlog.cc: Skip the database check for BEGIN/COMMIT/ROLLBACK log events. mysql-test/r/mysqlbinlog.result: Test result for bug#46998 mysql-test/t/mysqlbinlog.test: Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output in regardless of database filtering --- mysql-test/t/mysqlbinlog.test | 62 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 62 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index dd45f499866..c5b46b81207 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -304,4 +304,66 @@ FLUSH LOGS; # We do not need the results, just make sure that mysqlbinlog does not crash --exec $MYSQL_BINLOG --hexdump --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 >/dev/null +# +# #46998 +# This test verifies if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output +# in regardless of database filtering +# + +RESET MASTER; +FLUSH LOGS; + +# The following three test cases were wrtten into binlog_transaction.000001 +# Test case1: Test if the 'BEGIN' and 'COMMIT' are output for the 'test' database +# in transaction1 base on innodb engine tables +# use test; +# create table t1(a int) engine= innodb; +# use mysql; +# create table t2(a int) engine= innodb; +# Transaction1 begin +# begin; +# use test; +# insert into t1 (a) values (1); +# use mysql; +# insert into t2 (a) values (1); +# commit; +# Transaction1 end + +# Test case2: Test if the 'BEGIN' and 'ROLLBACK' are output for the 'test' database +# in transaction2 base on innodb and myisam engine tables +# use test; +# create table t3(a int) engine= innodb; +# use mysql; +# create table t4(a int) engine= myisam; +# Transaction2 begin +# begin; +# use test; +# insert into t3 (a) values (2); +# use mysql; +# insert into t4 (a) values (2); +# rollback; +# Transaction2 end + +# Test case3: Test if the 'BEGIN' and 'COMMIT' are output for the 'test' database +# in transaction3 base on NDB engine tables +# use test; +# create table t5(a int) engine= NDB; +# use mysql; +# create table t6(a int) engine= NDB; +# Transaction3 begin +# begin; +# use test; +# insert into t5 (a) values (3); +# use mysql; +# insert into t6 (a) values (3); +# commit; +# Transaction3 end + +--echo # +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +--exec $MYSQL_BINLOG --database=test --short-form $MYSQLTEST_VARDIR/std_data_ln/binlog_transaction.000001 +--echo # +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +--exec $MYSQL_BINLOG --database=not_exist --short-form $MYSQLTEST_VARDIR/std_data_ln/binlog_transaction.000001 + --echo End of 5.0 tests -- cgit v1.2.1 From 4fe81a8cc60acc14d69e3df4cae4db480e552462 Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Mon, 5 Oct 2009 10:27:36 +0500 Subject: Bug #44139: Table scan when NULL appears in IN clause SELECT ... WHERE ... IN (NULL, ...) does full table scan, even if the same query without the NULL uses efficient range scan. The bugfix for the bug 18360 introduced an optimization: if 1) all right-hand arguments of the IN function are constants 2) result types of all right argument items are compatible enough to use the same single comparison function to compare all of them to the left argument, then we can convert the right-hand list of constant items to an array of equally-typed constant values for the further QUICK index access etc. (see Item_func_in::fix_length_and_dec()). The Item_null constant item objects have STRING_RESULT result types, so, as far as Item_func_in::fix_length_and_dec() is aware of NULLs in the right list, this improvement efficiently optimizes IN function calls with a mixed right list of NULLs and string constants. However, the optimization doesn't affect mixed lists of NULLs and integers, floats etc., because there is no unique common comparator. New optimization has been added to ignore the result type of NULL constants in the static analysis of mixed right-hand lists. This is safe, because at the execution phase we care about presence of NULLs anyway. 1. The collect_cmp_types() function has been modified to optionally ignore NULL constants in the item list. 2. NULL-skipping code of the Item_func_in::fix_length_and_dec() function has been modified to work not only with in_string vectors but with in_vectors of other types. mysql-test/r/func_in.result: Added test case for the bug #44139. mysql-test/t/func_in.test: Added test case for the bug #44139. sql/item_cmpfunc.cc: Bug #44139: Table scan when NULL appears in IN clause 1. The collect_cmp_types() function has been modified to optionally ignore NULL constants in the item list. 2. NULL-skipping code of the Item_func_in::fix_length_and_dec() function has been modified to work not only with in_string vectors but with in_vectors of other types. --- mysql-test/t/func_in.test | 85 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 85 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index adc074259ad..61ae812d874 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -456,4 +456,89 @@ 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)); DROP TABLE t1; +--echo # +--echo # Bug #44139: Table scan when NULL appears in IN clause +--echo # + +--disable_warnings + +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; + +--enable_warnings + +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3); + +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_date + IN ('2009-09-01', '2009-09-02', '2009-09-03'); +EXPLAIN SELECT * FROM t1 WHERE c_date + IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03'); +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL); + +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'); +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'); +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL); + +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'); +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'); +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3); +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL); + +EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3'); +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3'); +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL); +EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); + +DROP TABLE t1; + +--echo # + --echo End of 5.1 tests -- cgit v1.2.1