From: Mattias Jonsson Date: January 20 2012 2:45pm Subject: bzr push into mysql-trunk branch (mattias.jonsson:3756 to 3758) Bug#13559657 List-Archive: http://lists.mysql.com/commits/142494 X-Bug: 13559657 Message-Id: <201201201445.q0KEjcvU028379@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3758 Mattias Jonsson 2012-01-20 [merge] merge of bug#13559657 into -trunk Also updated two result files. modified: mysql-test/r/partition_explicit_prune.result mysql-test/suite/parts/r/partition-dml-1-9-innodb.result mysql-test/suite/parts/r/partition-dml-1-9-myisam.result mysql-test/t/partition_explicit_prune.test sql/sql_lex.cc 3757 Mattias Jonsson 2012-01-20 [merge] merge modified: mysql-test/r/partition_binlog_stmt.result mysql-test/t/partition_binlog_stmt.test 3756 Georgi Kodinov 2012-01-20 [merge] merge modified: mysql-test/include/have_example_plugin.inc mysql-test/include/have_semisync_plugin.inc mysql-test/include/have_simple_parser.inc mysql-test/include/have_udf.inc mysql-test/t/udf_skip_grants-master.opt === modified file 'mysql-test/r/partition_binlog_stmt.result' --- a/mysql-test/r/partition_binlog_stmt.result revid:georgi.kodinov@stripped +++ b/mysql-test/r/partition_binlog_stmt.result revid:mattias.jonsson@stripped @@ -8,6 +8,6 @@ name TINYBLOB NOT NULL, modified TIMESTAMP DEFAULT '0000-00-00 00:00:00', INDEX namelocs (name(255))) ENGINE = MyISAM PARTITION BY HASH(id) PARTITIONS 2; -LOAD DATA LOCAL INFILE 'init_file.txt' +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/init_file.txt' INTO TABLE t1 (name); DROP TABLE t1; === modified file 'mysql-test/r/partition_explicit_prune.result' --- a/mysql-test/r/partition_explicit_prune.result revid:georgi.kodinov@stripped +++ b/mysql-test/r/partition_explicit_prune.result revid:mattias.jonsson@stripped @@ -1,3 +1,180 @@ +# +# Bug#13559657: PARTITION SELECTION DOES NOT WORK WITH VIEWS +# +CREATE TABLE t1 (a int) PARTITION BY HASH (a) PARTITIONS 2; +INSERT INTO t1 VALUES (0), (1), (2), (3); +CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0); +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` PARTITION (`p0`) latin1 latin1_swedish_ci +FLUSH STATUS; +SELECT * FROM v1; +a +0 +2 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 4 +HANDLER_READ_FIRST 1 +HANDLER_READ_KEY 1 +HANDLER_READ_RND_NEXT 3 +HANDLER_WRITE 17 +# 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +SELECT a FROM t1 PARTITION (p0); +a +0 +2 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 4 +HANDLER_READ_FIRST 1 +HANDLER_READ_KEY 1 +HANDLER_READ_RND_NEXT 3 +HANDLER_WRITE 17 +# 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +INSERT INTO v1 VALUES (10); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 4 +HANDLER_WRITE 18 +# 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +INSERT INTO v1 VALUES (11); +ERROR HY000: Found a row not matching the given partition set +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_EXTERNAL_LOCK 4 +HANDLER_ROLLBACK 1 +HANDLER_WRITE 17 +# 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +SELECT * FROM v1; +a +0 +10 +2 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 4 +HANDLER_READ_FIRST 1 +HANDLER_READ_KEY 1 +HANDLER_READ_RND_NEXT 4 +HANDLER_WRITE 17 +# 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +SELECT a FROM t1 PARTITION (p0); +a +0 +10 +2 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 4 +HANDLER_READ_FIRST 1 +HANDLER_READ_KEY 1 +HANDLER_READ_RND_NEXT 4 +HANDLER_WRITE 17 +# 4 locks (1 table, 1 partition lock/unlock) +SELECT * FROM t1; +a +0 +1 +10 +2 +3 +DROP VIEW v1; +CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0) WITH CHECK OPTION; +FLUSH STATUS; +INSERT INTO v1 VALUES (20); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 4 +HANDLER_WRITE 18 +# 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +INSERT INTO v1 VALUES (21); +ERROR HY000: Found a row not matching the given partition set +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_EXTERNAL_LOCK 4 +HANDLER_ROLLBACK 1 +HANDLER_WRITE 17 +# 4 locks (1 table, 1 partition lock/unlock) +SELECT * FROM v1; +a +0 +10 +2 +20 +SELECT * FROM t1; +a +0 +1 +10 +2 +20 +3 +DROP VIEW v1; +CREATE VIEW v1 AS +SELECT a FROM t1 PARTITION (p0) WHERE a = 30 WITH CHECK OPTION; +FLUSH STATUS; +INSERT INTO v1 VALUES (30); +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 4 +HANDLER_WRITE 18 +# 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +INSERT INTO v1 VALUES (31); +ERROR HY000: CHECK OPTION failed 'test.v1' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_EXTERNAL_LOCK 4 +HANDLER_ROLLBACK 1 +HANDLER_WRITE 17 +FLUSH STATUS; +INSERT INTO v1 VALUES (32); +ERROR HY000: CHECK OPTION failed 'test.v1' +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_EXTERNAL_LOCK 4 +HANDLER_ROLLBACK 1 +HANDLER_WRITE 17 +# 4 locks (1 table, 1 partition lock/unlock) +SELECT * FROM v1; +a +30 +SELECT * FROM t1; +a +0 +1 +10 +2 +20 +3 +30 +DROP VIEW v1; +DROP TABLE t1; # Original tests for WL#5217 # Must have InnoDB as engine to get the same statistics results. # embedded uses MyISAM as default. CREATE SELECT uses the default engine. === modified file 'mysql-test/suite/parts/r/partition-dml-1-9-innodb.result' --- a/mysql-test/suite/parts/r/partition-dml-1-9-innodb.result revid:georgi.kodinov@stripped +++ b/mysql-test/suite/parts/r/partition-dml-1-9-innodb.result revid:mattias.jonsson@stripped @@ -126,10 +126,6 @@ SELECT t1.a, t2.b FROM t1 PARTITION (`p0 WHERE t1.a = t2.a; SELECT * FROM v1; a b --3 (pNeg-)subp0 --4 (pNeg-)subp0 --1 (pNeg-)subp0 --2 (pNeg-)subp0 3 (p0-29-)subp3 21 (p0-29-)subp5 24 (p0-29-)subp5 @@ -138,26 +134,6 @@ a b 22 (p0-29-)subp5 2 (p0-29-)subp3 23 (p0-29-)subp5 -33 (p30-299-)subp6 -231 (p30-299-)subp8 -234 (p30-299-)subp8 -31 (p30-299-)subp6 -34 (p30-299-)subp6 -232 (p30-299-)subp8 -32 (p30-299-)subp6 -233 (p30-299-)subp8 -303 (p300-2999-)subp8 -301 (p300-2999-)subp8 -304 (p300-2999-)subp8 -302 (p300-2999-)subp8 -3003 (p3000-299999-)subp12 -299997 (p3000-299999-)subp14 -3001 (p3000-299999-)subp12 -3004 (p3000-299999-)subp12 -299998 (p3000-299999-)subp14 -3002 (p3000-299999-)subp12 -299996 (p3000-299999-)subp14 -299999 (p3000-299999-)subp14 SELECT * FROM t1 PARTITION (`p0-29`); a b 1 (p0-29-)subp3 === modified file 'mysql-test/suite/parts/r/partition-dml-1-9-myisam.result' --- a/mysql-test/suite/parts/r/partition-dml-1-9-myisam.result revid:georgi.kodinov@stripped +++ b/mysql-test/suite/parts/r/partition-dml-1-9-myisam.result revid:mattias.jonsson@stripped @@ -126,10 +126,6 @@ SELECT t1.a, t2.b FROM t1 PARTITION (`p0 WHERE t1.a = t2.a; SELECT * FROM v1; a b --3 (pNeg-)subp0 --4 (pNeg-)subp0 --1 (pNeg-)subp0 --2 (pNeg-)subp0 3 (p0-29-)subp3 21 (p0-29-)subp5 24 (p0-29-)subp5 @@ -138,26 +134,6 @@ a b 22 (p0-29-)subp5 2 (p0-29-)subp3 23 (p0-29-)subp5 -33 (p30-299-)subp6 -231 (p30-299-)subp8 -234 (p30-299-)subp8 -31 (p30-299-)subp6 -34 (p30-299-)subp6 -232 (p30-299-)subp8 -32 (p30-299-)subp6 -233 (p30-299-)subp8 -303 (p300-2999-)subp8 -301 (p300-2999-)subp8 -304 (p300-2999-)subp8 -302 (p300-2999-)subp8 -3003 (p3000-299999-)subp12 -299997 (p3000-299999-)subp14 -3001 (p3000-299999-)subp12 -3004 (p3000-299999-)subp12 -299998 (p3000-299999-)subp14 -3002 (p3000-299999-)subp12 -299996 (p3000-299999-)subp14 -299999 (p3000-299999-)subp14 SELECT * FROM t1 PARTITION (`p0-29`); a b 1 (p0-29-)subp3 === modified file 'mysql-test/t/partition_binlog_stmt.test' --- a/mysql-test/t/partition_binlog_stmt.test revid:georgi.kodinov@stripped +++ b/mysql-test/t/partition_binlog_stmt.test revid:mattias.jonsson@stripped @@ -8,7 +8,7 @@ DROP TABLE IF EXISTS t1; --echo # --echo # Bug#51851: Server with SBR locks mutex twice on LOAD DATA into --echo # partitioned MyISAM table ---write_file init_file.txt +--write_file $MYSQLTEST_VARDIR/tmp/init_file.txt abcd EOF @@ -19,8 +19,9 @@ CREATE TABLE t1 INDEX namelocs (name(255))) ENGINE = MyISAM PARTITION BY HASH(id) PARTITIONS 2; -LOAD DATA LOCAL INFILE 'init_file.txt' +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/tmp/init_file.txt' INTO TABLE t1 (name); ---remove_file init_file.txt +--remove_file $MYSQLTEST_VARDIR/tmp/init_file.txt DROP TABLE t1; === modified file 'mysql-test/t/partition_explicit_prune.test' --- a/mysql-test/t/partition_explicit_prune.test revid:georgi.kodinov@stripped +++ b/mysql-test/t/partition_explicit_prune.test revid:mattias.jonsson@stripped @@ -1,6 +1,86 @@ --source include/have_innodb.inc --source include/have_partition.inc +# Helper statement +let $get_handler_status_counts= SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; + +--echo # +--echo # Bug#13559657: PARTITION SELECTION DOES NOT WORK WITH VIEWS +--echo # +CREATE TABLE t1 (a int) PARTITION BY HASH (a) PARTITIONS 2; +INSERT INTO t1 VALUES (0), (1), (2), (3); +CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0); +SHOW CREATE VIEW v1; +FLUSH STATUS; +--sorted_result +SELECT * FROM v1; +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +--sorted_result +SELECT a FROM t1 PARTITION (p0); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +INSERT INTO v1 VALUES (10); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET +INSERT INTO v1 VALUES (11); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +--sorted_result +SELECT * FROM v1; +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +--sorted_result +SELECT a FROM t1 PARTITION (p0); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +--sorted_result +SELECT * FROM t1; +DROP VIEW v1; +CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0) WITH CHECK OPTION; +FLUSH STATUS; +INSERT INTO v1 VALUES (20); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET +INSERT INTO v1 VALUES (21); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +--sorted_result +SELECT * FROM v1; +--sorted_result +SELECT * FROM t1; +DROP VIEW v1; +CREATE VIEW v1 AS +SELECT a FROM t1 PARTITION (p0) WHERE a = 30 WITH CHECK OPTION; +FLUSH STATUS; +INSERT INTO v1 VALUES (30); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +FLUSH STATUS; +--error ER_VIEW_CHECK_FAILED +INSERT INTO v1 VALUES (31); +eval $get_handler_status_counts; +FLUSH STATUS; +--error ER_VIEW_CHECK_FAILED +INSERT INTO v1 VALUES (32); +eval $get_handler_status_counts; +--echo # 4 locks (1 table, 1 partition lock/unlock) +--sorted_result +SELECT * FROM v1; +--sorted_result +SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; + --echo # Original tests for WL#5217 --echo # Must have InnoDB as engine to get the same statistics results. @@ -8,9 +88,6 @@ SET @old_default_storage_engine = @@default_storage_engine; SET @@default_storage_engine = 'InnoDB'; -let $get_handler_status_counts= SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS -WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; - --let $MYSQLD_DATADIR= `SELECT @@datadir` --echo # Test to show if I_S affects HANDLER_ counts === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc revid:georgi.kodinov@stripped +++ b/sql/sql_lex.cc revid:mattias.jonsson@stripped @@ -2494,6 +2494,20 @@ void TABLE_LIST::print(THD *thd, String append_identifier(thd, str, table_name, table_name_length); cmp_name= table_name; } + if (partition_names && partition_names->elements) + { + int i, num_parts= partition_names->elements; + List_iterator name_it(*(partition_names)); + str->append(STRING_WITH_LEN(" PARTITION (")); + for (i= 1; i <= num_parts; i++) + { + String *name= name_it++; + append_identifier(thd, str, name->c_ptr(), name->length()); + if (i != num_parts) + str->append(','); + } + str->append(')'); + } } if (my_strcasecmp(table_alias_charset, cmp_name, alias)) { No bundle (reason: useless for push emails).