#At file:///Users/mattiasj/mysql-bzr/merge-trunk-wl4443/ based on revid:magnus.blaudd@stripped
3692 Mattias Jonsson 2011-02-21
WL#4443 proof of concept.
Prototype for designing WL#4443.
Moved open_tables() and lock_tables()
(was open_and_lock_tables()) into
mysql_select/handle_select/mysql_union.
Splitted JOIN::optimize to
JOIN::optimize_before_locking +
JOIN::optimize_after_locking
Not yet done:
Implement pruning for INSERT.
Fix all problems with Items that uses tables
in the prepare/optimize_before_locking stages.
added:
mysql-test/r/wl4443.result
mysql-test/t/wl4443.test
modified:
sql/ha_partition.cc
sql/handler.h
sql/item_row.cc
sql/item_subselect.cc
sql/opt_range.cc
sql/sql_base.cc
sql/sql_base.h
sql/sql_derived.cc
sql/sql_derived.h
sql/sql_insert.cc
sql/sql_parse.cc
sql/sql_select.cc
sql/sql_select.h
sql/sql_union.cc
sql/sql_union.h
sql/sql_update.cc
sql/table.h
=== added file 'mysql-test/r/wl4443.result'
--- a/mysql-test/r/wl4443.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/wl4443.result 2011-02-21 16:27:53 +0000
@@ -0,0 +1,812 @@
+# Original tests for WL#4443
+# Must have InnoDB as engine to get the same statistics results.
+# embedded uses MyISAM as default. CREATE SELECT uses the default engine.
+SET @old_default_storage_engine = @@default_storage_engine;
+SET @@default_storage_engine = 'InnoDB';
+CREATE TABLE t1 (a int PRIMARY KEY, b varchar(128), KEY (b))
+ENGINE = InnoDB
+PARTITION BY HASH (a) PARTITIONS 13;
+CREATE TABLE t2 (a int PRIMARY KEY AUTO_INCREMENT, b varchar(128))
+ENGINE = InnoDB
+PARTITION BY HASH (a) PARTITIONS 13;
+# Test insert
+# TODO: Implement lock pruning for INSERT
+FLUSH STATUS;
+INSERT INTO t1 VALUES (1, 'First row, p1');
+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 28
+HANDLER_READ_KEY 28
+HANDLER_WRITE 18
+FLUSH STATUS;
+INSERT INTO t1 VALUES (1, 'First row, duplicate');
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_EXTERNAL_LOCK 28
+HANDLER_ROLLBACK 1
+HANDLER_WRITE 18
+FLUSH STATUS;
+INSERT INTO t1 VALUES (0, 'First row, p0'), (2, 'First row, p2'),
+(3, 'First row, p3'), (4, 'First row, p4');
+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 28
+HANDLER_WRITE 21
+FLUSH STATUS;
+INSERT INTO t1 VALUES (1 * 13, 'Second row, p0'), (2 * 13, 'Third row, p0'),
+(3 * 13, 'Fourth row, p0'), (4 * 13, 'Fifth row, p0');
+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 28
+HANDLER_WRITE 21
+FLUSH STATUS;
+INSERT INTO t2 VALUES (NULL, 'First auto-inc row');
+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 28
+HANDLER_READ_KEY 27
+HANDLER_WRITE 18
+# Test insert select
+FLUSH STATUS;
+TRUNCATE TABLE t2;
+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 28
+HANDLER_READ_KEY 14
+HANDLER_WRITE 17
+FLUSH STATUS;
+INSERT INTO t2 SELECT a, b FROM t1 WHERE a IN (1,4);
+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 34
+HANDLER_READ_FIRST 2
+HANDLER_READ_KEY 4
+HANDLER_READ_NEXT 2
+HANDLER_WRITE 19
+# Test insert on duplicated key update
+# Test select
+FLUSH STATUS;
+SELECT * FROM t1;
+a b
+0 First row, p0
+1 First row, p1
+13 Second row, p0
+2 First row, p2
+26 Third row, p0
+3 First row, p3
+39 Fourth row, p0
+4 First row, p4
+52 Fifth row, p0
+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 28
+HANDLER_READ_FIRST 13
+HANDLER_READ_KEY 26
+HANDLER_READ_NEXT 9
+HANDLER_WRITE 17
+FLUSH STATUS;
+SELECT * FROM t1 where a in (0, 1, 4) ORDER BY a;
+a b
+0 First row, p0
+1 First row, p1
+4 First row, p4
+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 8
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_WRITE 17
+FLUSH STATUS;
+SELECT * FROM t1 where a in (13, 26, 39, 52);
+a b
+13 Second row, p0
+26 Third row, p0
+39 Fourth row, p0
+52 Fifth row, p0
+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 2
+HANDLER_READ_NEXT 5
+HANDLER_WRITE 17
+FLUSH STATUS;
+SELECT * FROM t1 where a = 3;
+a b
+3 First row, p3
+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_KEY 2
+HANDLER_WRITE 17
+FLUSH STATUS;
+SELECT * FROM t1 where b like 'First%' ORDER BY a;
+a b
+0 First row, p0
+1 First row, p1
+2 First row, p2
+3 First row, p3
+4 First row, p4
+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 28
+HANDLER_READ_KEY 49
+HANDLER_READ_NEXT 5
+HANDLER_WRITE 17
+# Test select * from (select * from ...)
+# Currently derived tables can only be optimized/pruned after locking?
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3;
+a b
+0 First row, p0
+2 First row, p2
+3 First row, p3
+13 Second row, p0
+26 Third row, p0
+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 8
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 6
+HANDLER_WRITE 22
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3) t4;
+a b
+0 First row, p0
+2 First row, p2
+3 First row, p3
+13 Second row, p0
+26 Third row, p0
+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 8
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 12
+HANDLER_WRITE 27
+# Test EXPLAIN select * from (select * from ...)
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
+2 DERIVED t1 index PRIMARY b 131 NULL 7 Using where; Using index
+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 8
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_WRITE 22
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3) t4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 5
+3 DERIVED t1 index PRIMARY b 131 NULL 7 Using where; Using index
+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 8
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 6
+HANDLER_WRITE 27
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 5
+2 DERIVED t1 p0,p2,p3 index PRIMARY b 131 NULL 7 Using where; Using index
+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 8
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_WRITE 22
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3) t4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 5
+2 DERIVED <derived3> NULL ALL NULL NULL NULL NULL 5
+3 DERIVED t1 p0,p2,p3 index PRIMARY b 131 NULL 7 Using where; Using index
+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 8
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 6
+HANDLER_WRITE 27
+# Test select ... UNION select ...
+FLUSH STATUS;
+SELECT * FROM t1 UNION SELECT * from t2;
+a b
+52 Fifth row, p0
+0 First row, p0
+1 First row, p1
+2 First row, p2
+3 First row, p3
+4 First row, p4
+39 Fourth row, p0
+13 Second row, p0
+26 Third row, p0
+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 56
+HANDLER_READ_FIRST 26
+HANDLER_READ_KEY 52
+HANDLER_READ_NEXT 9
+HANDLER_READ_RND_NEXT 26
+HANDLER_WRITE 28
+FLUSH STATUS;
+SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2;
+a b
+0 First row, p0
+1 First row, p1
+4 First row, p4
+13 Second row, p0
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 21
+HANDLER_WRITE 23
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2;
+a b
+0 First row, p0
+1 First row, p1
+4 First row, p4
+13 Second row, p0
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 26
+HANDLER_WRITE 27
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2) t3) t4;
+a b
+0 First row, p0
+1 First row, p1
+4 First row, p4
+13 Second row, p0
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 31
+HANDLER_WRITE 31
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2) t4;
+a b
+0 First row, p0
+1 First row, p1
+4 First row, p4
+13 Second row, p0
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 31
+HANDLER_WRITE 31
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3) t4 UNION SELECT * from t2;
+a b
+0 First row, p0
+1 First row, p1
+4 First row, p4
+13 Second row, p0
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 31
+HANDLER_WRITE 31
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2 WHERE a = 1) t4;
+a b
+0 First row, p0
+1 First row, p1
+4 First row, p4
+13 Second row, p0
+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 12
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 8
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 16
+HANDLER_WRITE 30
+# Test EXPLAIN select ... UNION select ...
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 UNION SELECT * from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL b 131 NULL 17 Using index
+2 UNION t2 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
+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 56
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index PRIMARY b 131 NULL 7 Using where; Using index
+2 UNION t2 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
+2 DERIVED t1 index PRIMARY b 131 NULL 7 Using where; Using index
+3 UNION t2 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_WRITE 21
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2) t3) t4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 4
+3 DERIVED t1 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 26
+HANDLER_WRITE 31
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2) t4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 4
+3 DERIVED t1 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 26
+HANDLER_WRITE 31
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3) t4 UNION SELECT * from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 4
+3 DERIVED t1 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 5
+HANDLER_WRITE 25
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2 WHERE a = 1) t4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 4
+3 DERIVED t1 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 const PRIMARY PRIMARY 4 const 1
+NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
+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 12
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 8
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 11
+HANDLER_WRITE 30
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 UNION SELECT * from t2;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 index NULL b 131 NULL 17 Using index
+2 UNION t2 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL
+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 56
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 p0,p1,p4 index PRIMARY b 131 NULL 7 Using where; Using index
+2 UNION t2 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 4
+2 DERIVED t1 p0,p1,p4 index PRIMARY b 131 NULL 7 Using where; Using index
+3 UNION t2 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,3> NULL ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_WRITE 21
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2) t3) t4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> NULL ALL NULL NULL NULL NULL 4
+3 DERIVED t1 p0,p1,p4 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union3,4> NULL ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 26
+HANDLER_WRITE 31
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2) t4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> NULL ALL NULL NULL NULL NULL 4
+3 DERIVED t1 p0,p1,p4 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union2,4> NULL ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 16
+HANDLER_READ_KEY 32
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 26
+HANDLER_WRITE 31
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3) t4 UNION SELECT * from t2;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> NULL ALL NULL NULL NULL NULL 4
+3 DERIVED t1 p0,p1,p4 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL NULL NULL NULL NULL 13
+NULL UNION RESULT <union1,4> NULL ALL NULL NULL NULL NULL NULL
+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 36
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 5
+HANDLER_WRITE 25
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2 WHERE a = 1) t4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 4
+2 DERIVED <derived3> NULL ALL NULL NULL NULL NULL 4
+3 DERIVED t1 p0,p1,p4 index PRIMARY b 131 NULL 7 Using where; Using index
+4 UNION t2 p1 const PRIMARY PRIMARY 4 const 1
+NULL UNION RESULT <union2,4> NULL ALL NULL NULL NULL NULL NULL
+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 12
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 8
+HANDLER_READ_NEXT 7
+HANDLER_READ_RND_NEXT 11
+HANDLER_WRITE 30
+# Test EXPLAIN select
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 131 NULL 17 Using index
+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 28
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 where a in (0, 1, 4) ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 7 Using where
+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 8
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 where a in (13, 26, 39, 52);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY b 131 NULL 5 Using where; Using index
+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 17
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 where a = 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+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_KEY 2
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 where b like 'First%' ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range b b 131 NULL 13 Using where; Using index; Using filesort
+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 28
+HANDLER_READ_KEY 10
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 index NULL b 131 NULL 17 Using index
+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 28
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 where a in (0, 1, 4) ORDER BY a;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p4 index PRIMARY PRIMARY 4 NULL 7 Using where
+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 8
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 where a in (13, 26, 39, 52);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0 index PRIMARY b 131 NULL 5 Using where; Using index
+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 17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 where a = 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p3 const PRIMARY PRIMARY 4 const 1
+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_KEY 2
+HANDLER_WRITE 17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 where b like 'First%' ORDER BY a;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 range b b 131 NULL 13 Using where; Using index; Using filesort
+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 28
+HANDLER_READ_KEY 10
+HANDLER_WRITE 17
+# Test EXPLAIN select * from (select * from ...)
+# Test EXPLAIN select ... UNION select ...
+# Test delete
+# Test multi table delete
+# Test load ?
+# Test load ?
+# Test insert
+# Test insert on duplicated key update
+# Test insert select
+# Test replace
+# Test update
+SELECT * FROM t1;
+a b
+0 First row, p0
+1 First row, p1
+13 Second row, p0
+2 First row, p2
+26 Third row, p0
+3 First row, p3
+39 Fourth row, p0
+4 First row, p4
+52 Fifth row, p0
+# This should be prunable (does not change the partitioning key)
+FLUSH STATUS;
+UPDATE t1 set b = concat(b, ", updated 1") WHERE a IN (13, 26, 39, 52);
+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 2
+HANDLER_READ_RND_NEXT 6
+HANDLER_UPDATE 4
+HANDLER_WRITE 17
+# This should not be prunable (only after implementing 'update pruning')
+# i.e if all changed partitioning field is set to constant values,
+# set lock_partitions to be a union of read_partition and the matching
+# partition for the constants. Easy if all partitioning fields are set,
+# probably needs a second round of prune_partitions() with these fields
+# set to see if possible to prune locks.
+FLUSH STATUS;
+UPDATE t1 set a = 99, b = concat(b, ", updated 2 -> p8") WHERE a = 13;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_DELETE 1
+HANDLER_EXTERNAL_LOCK 28
+HANDLER_READ_KEY 4
+HANDLER_READ_RND 1
+HANDLER_WRITE 18
+# This should use ha_update_row instead of ha_write_row + ha_delete_row
+FLUSH STATUS;
+UPDATE t1 set a = 13 + 99, b = concat(b, ", updated 3") WHERE a = 99;
+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 28
+HANDLER_READ_KEY 4
+HANDLER_READ_RND 1
+HANDLER_UPDATE 1
+HANDLER_WRITE 17
+# This should not be prunable (only after implementing
+# 'optimized update pruning', which will probably never happen, since
+# it depends on which partitioning type is used (for this only hash is
+# simple, but range and list is possible, key is very hard)
+FLUSH STATUS;
+UPDATE t1 set a = a + 1, b = concat(b, ", updated 4 -> p9") WHERE a = 112;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_DELETE 1
+HANDLER_EXTERNAL_LOCK 28
+HANDLER_READ_KEY 4
+HANDLER_READ_RND 1
+HANDLER_WRITE 18
+# Test multi table update
+SELECT * FROM t1;
+a b
+0 First row, p0
+1 First row, p1
+113 Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
+2 First row, p2
+26 Third row, p0, updated 1
+3 First row, p3
+39 Fourth row, p0, updated 1
+4 First row, p4
+52 Fifth row, p0, updated 1
+# Test CREATE SELECT
+# TODO: Add variants for LOCK TABLES
+# TODO: add delayed locking/pruning for multi-table update
+# TODO: Document that functions in WHERE clause can now be evaluated
+# before any locks are taken (i.e. if optimization is possible
+# before locking). Fix binlog.binlog_stm_row.
+DROP TABLE t1, t2;
=== added file 'mysql-test/t/wl4443.test'
--- a/mysql-test/t/wl4443.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/wl4443.test 2011-02-21 16:27:53 +0000
@@ -0,0 +1,265 @@
+--source include/have_innodb.inc
+--source include/have_partition.inc
+
+--echo # Original tests for WL#4443
+
+--echo # Must have InnoDB as engine to get the same statistics results.
+--echo # embedded uses MyISAM as default. CREATE SELECT uses the default engine.
+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`
+
+
+
+CREATE TABLE t1 (a int PRIMARY KEY, b varchar(128), KEY (b))
+ENGINE = InnoDB
+PARTITION BY HASH (a) PARTITIONS 13;
+
+CREATE TABLE t2 (a int PRIMARY KEY AUTO_INCREMENT, b varchar(128))
+ENGINE = InnoDB
+PARTITION BY HASH (a) PARTITIONS 13;
+
+
+--echo # Test insert
+--echo # TODO: Implement lock pruning for INSERT
+FLUSH STATUS;
+INSERT INTO t1 VALUES (1, 'First row, p1');
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, 'First row, duplicate');
+eval $get_handler_status_counts;
+FLUSH STATUS;
+INSERT INTO t1 VALUES (0, 'First row, p0'), (2, 'First row, p2'),
+ (3, 'First row, p3'), (4, 'First row, p4');
+eval $get_handler_status_counts;
+FLUSH STATUS;
+INSERT INTO t1 VALUES (1 * 13, 'Second row, p0'), (2 * 13, 'Third row, p0'),
+ (3 * 13, 'Fourth row, p0'), (4 * 13, 'Fifth row, p0');
+eval $get_handler_status_counts;
+FLUSH STATUS;
+INSERT INTO t2 VALUES (NULL, 'First auto-inc row');
+eval $get_handler_status_counts;
+
+
+--echo # Test insert select
+FLUSH STATUS;
+TRUNCATE TABLE t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+INSERT INTO t2 SELECT a, b FROM t1 WHERE a IN (1,4);
+eval $get_handler_status_counts;
+
+
+--echo # Test insert on duplicated key update
+
+
+--echo # Test select
+FLUSH STATUS;
+--sorted_result
+SELECT * FROM t1;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM t1 where a in (0, 1, 4) ORDER BY a;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+SELECT * FROM t1 where a in (13, 26, 39, 52);
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+SELECT * FROM t1 where a = 3;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM t1 where b like 'First%' ORDER BY a;
+eval $get_handler_status_counts;
+
+
+--echo # Test select * from (select * from ...)
+--echo # Currently derived tables can only be optimized/pruned after locking?
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3) t4;
+eval $get_handler_status_counts;
+
+--echo # Test EXPLAIN select * from (select * from ...)
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3) t4;
+eval $get_handler_status_counts;
+
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a in (0,2,3,13,26)) t3) t4;
+eval $get_handler_status_counts;
+
+
+--echo # Test select ... UNION select ...
+FLUSH STATUS;
+SELECT * FROM t1 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2) t3) t4;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2) t4;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3) t4 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2 WHERE a = 1) t4;
+eval $get_handler_status_counts;
+
+
+--echo # Test EXPLAIN select ... UNION select ...
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2) t3) t4;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2) t4;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3) t4 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2 WHERE a = 1) t4;
+eval $get_handler_status_counts;
+
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4) UNION SELECT * from t2) t3) t4;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2) t4;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3) t4 UNION SELECT * from t2;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4)) t3 UNION SELECT * from t2 WHERE a = 1) t4;
+eval $get_handler_status_counts;
+
+
+
+--echo # Test EXPLAIN select
+FLUSH STATUS;
+--sorted_result
+EXPLAIN SELECT * FROM t1;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 where a in (0, 1, 4) ORDER BY a;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+EXPLAIN SELECT * FROM t1 where a in (13, 26, 39, 52);
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+EXPLAIN SELECT * FROM t1 where a = 3;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN SELECT * FROM t1 where b like 'First%' ORDER BY a;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+EXPLAIN PARTITIONS SELECT * FROM t1;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 where a in (0, 1, 4) ORDER BY a;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+EXPLAIN PARTITIONS SELECT * FROM t1 where a in (13, 26, 39, 52);
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+EXPLAIN PARTITIONS SELECT * FROM t1 where a = 3;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 where b like 'First%' ORDER BY a;
+eval $get_handler_status_counts;
+--echo # Test EXPLAIN select * from (select * from ...)
+--echo # Test EXPLAIN select ... UNION select ...
+--echo # Test delete
+--echo # Test multi table delete
+--echo # Test load ?
+--echo # Test load ?
+--echo # Test insert
+--echo # Test insert on duplicated key update
+--echo # Test insert select
+--echo # Test replace
+
+
+--echo # Test update
+--sorted_result
+SELECT * FROM t1;
+--echo # This should be prunable (does not change the partitioning key)
+FLUSH STATUS;
+UPDATE t1 set b = concat(b, ", updated 1") WHERE a IN (13, 26, 39, 52);
+eval $get_handler_status_counts;
+--echo # This should not be prunable (only after implementing 'update pruning')
+--echo # i.e if all changed partitioning field is set to constant values,
+--echo # set lock_partitions to be a union of read_partition and the matching
+--echo # partition for the constants. Easy if all partitioning fields are set,
+--echo # probably needs a second round of prune_partitions() with these fields
+--echo # set to see if possible to prune locks.
+FLUSH STATUS;
+UPDATE t1 set a = 99, b = concat(b, ", updated 2 -> p8") WHERE a = 13;
+eval $get_handler_status_counts;
+--echo # This should use ha_update_row instead of ha_write_row + ha_delete_row
+FLUSH STATUS;
+UPDATE t1 set a = 13 + 99, b = concat(b, ", updated 3") WHERE a = 99;
+eval $get_handler_status_counts;
+--echo # This should not be prunable (only after implementing
+--echo # 'optimized update pruning', which will probably never happen, since
+--echo # it depends on which partitioning type is used (for this only hash is
+--echo # simple, but range and list is possible, key is very hard)
+FLUSH STATUS;
+UPDATE t1 set a = a + 1, b = concat(b, ", updated 4 -> p9") WHERE a = 112;
+eval $get_handler_status_counts;
+
+
+--echo # Test multi table update
+--sorted_result
+SELECT * FROM t1;
+--echo # Test CREATE SELECT
+--echo # TODO: Add variants for LOCK TABLES
+--echo # TODO: add delayed locking/pruning for multi-table update
+--echo # TODO: Document that functions in WHERE clause can now be evaluated
+--echo # before any locks are taken (i.e. if optimization is possible
+--echo # before locking). Fix binlog.binlog_stm_row.
+DROP TABLE t1, t2;
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2011-02-02 22:02:29 +0000
+++ b/sql/ha_partition.cc 2011-02-21 16:27:53 +0000
@@ -2942,7 +2942,10 @@ int ha_partition::external_lock(THD *thd
else
used_partitions= &(m_part_info->lock_partitions);
first_used_partition= bitmap_get_first_set(used_partitions);
- DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
+
+ if (first_used_partition == MY_BIT_NONE)
+ DBUG_RETURN(0);
+
for (i= first_used_partition; i < m_tot_parts; i++)
{
if (bitmap_is_set(used_partitions, i))
@@ -3040,7 +3043,10 @@ THR_LOCK_DATA **ha_partition::store_lock
DBUG_ENTER("ha_partition::store_lock");
first_used_partition= bitmap_get_first_set(&(m_part_info->lock_partitions));
- DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
+
+ if (first_used_partition == MY_BIT_NONE)
+ DBUG_RETURN(to);
+
for (i= first_used_partition; i < m_tot_parts; i++)
{
if (bitmap_is_set(&(m_part_info->lock_partitions), i))
@@ -3076,7 +3082,10 @@ int ha_partition::start_stmt(THD *thd, t
DBUG_ENTER("ha_partition::start_stmt");
first_used_partition= bitmap_get_first_set(&(m_part_info->lock_partitions));
- DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
+
+ if (first_used_partition == MY_BIT_NONE)
+ DBUG_RETURN(0);
+
for (i= first_used_partition; i < m_tot_parts; i++)
{
if (bitmap_is_set(&(m_part_info->lock_partitions), i))
@@ -3188,7 +3197,7 @@ void ha_partition::try_semi_consistent_r
DBUG_ENTER("ha_partition::try_semi_consistent_read");
first_used_partition= bitmap_get_first_set(&(m_part_info->read_partitions));
- DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
+
for (i= first_used_partition; i < m_tot_parts; i++)
{
if (bitmap_is_set(&(m_part_info->read_partitions), i))
@@ -4273,7 +4282,6 @@ int ha_partition::index_init(uint inx, b
file= m_file;
do
{
- /* TODO RONM: Change to index_init() when code is stable */
if (bitmap_is_set(&(m_part_info->read_partitions), (file - m_file)))
if ((error= (*file)->ha_index_init(inx, sorted)))
{
@@ -4312,7 +4320,6 @@ int ha_partition::index_end()
do
{
int tmp;
- /* TODO RONM: Change to index_end() when code is stable */
if (bitmap_is_set(&(m_part_info->read_partitions), (file - m_file)))
if ((tmp= (*file)->ha_index_end()))
error= tmp;
@@ -6075,7 +6082,11 @@ int ha_partition::reset(void)
uint first_used_partition;
first_used_partition= bitmap_get_first_set(&m_part_info->lock_partitions);
if (first_used_partition == MY_BIT_NONE)
+ {
+ /* Be sure lock_partitions are set if no pruning in the next statement */
+ m_part_info->set_partition_bitmaps(NULL);
DBUG_RETURN(result);
+ }
file= m_file + first_used_partition;
do
@@ -6286,6 +6297,11 @@ const key_map *ha_partition::keys_to_use
DBUG_ENTER("ha_partition::keys_to_use_for_scanning");
first_used_partition= bitmap_get_first_set(&(m_part_info->read_partitions));
+ if (first_used_partition == MY_BIT_NONE)
+ {
+ DBUG_ASSERT(0); // How can this happen?
+ DBUG_RETURN(handler::keys_to_use_for_scanning());
+ }
DBUG_RETURN(m_file[first_used_partition]->keys_to_use_for_scanning());
}
@@ -6325,6 +6341,10 @@ double ha_partition::scan_time()
partitions_optimizer_call_preparations(&first, &num_used_parts,
&check_min_num);
+
+ if (first == MY_BIT_NONE)
+ DBUG_RETURN(scan_time);
+
for (part_id= first; partitions_called < num_used_parts ; part_id++)
{
if (!bitmap_is_set(&(m_part_info->read_partitions), part_id))
@@ -6359,7 +6379,12 @@ ha_rows ha_partition::estimate_rows(bool
uint first, part_id, num_used_parts, check_min_num, partitions_called= 0;
DBUG_ENTER("ha_partition::estimate_rows");
- partitions_optimizer_call_preparations(&first, &num_used_parts, &check_min_num);
+ partitions_optimizer_call_preparations(&first, &num_used_parts,
+ &check_min_num);
+
+ if (first == MY_BIT_NONE)
+ DBUG_RETURN(estimated_rows);
+
for (part_id= first; partitions_called < num_used_parts ; part_id++)
{
if (!bitmap_is_set(&(m_part_info->read_partitions), part_id))
@@ -6567,7 +6592,13 @@ const char *ha_partition::index_type(uin
DBUG_ENTER("ha_partition::index_type");
first_used_partition= bitmap_get_first_set(&(m_part_info->read_partitions));
- DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
+
+ if (first_used_partition == MY_BIT_NONE)
+ {
+ DBUG_ASSERT(0); // How can this happen?
+ DBUG_RETURN(handler::index_type(inx));
+ }
+
DBUG_RETURN(m_file[first_used_partition]->index_type(inx));
}
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2011-02-02 22:02:29 +0000
+++ b/sql/handler.h 2011-02-21 16:27:53 +0000
@@ -2429,6 +2429,7 @@ public:
{ return HA_ERR_WRONG_COMMAND; }
virtual int rename_partitions(const char *path)
{ return HA_ERR_WRONG_COMMAND; }
+ int get_lock_type() { return m_lock_type; }
};
=== modified file 'sql/item_row.cc'
--- a/sql/item_row.cc 2010-12-14 11:15:13 +0000
+++ b/sql/item_row.cc 2011-02-21 16:27:53 +0000
@@ -81,6 +81,7 @@ bool Item_row::fix_fields(THD *thd, Item
const_item_cache&= item->const_item() && !with_null;
not_null_tables_cache|= item->not_null_tables();
+#ifdef FIX_THIS_FOR_WL4443
if (const_item_cache)
{
if (item->cols() > 1)
@@ -91,9 +92,14 @@ bool Item_row::fix_fields(THD *thd, Item
with_null|= 1;
}
}
+#endif /* FIX_THIS_FOR_WL4443 */
maybe_null|= item->maybe_null;
with_sum_func= with_sum_func || item->with_sum_func;
}
+#ifndef FIX_THIS_FOR_WL4443
+ /* better safe than sorry, makes compare a bit slower */
+ with_null= maybe_null;
+#endif /* FIX_THIS_FOR_WL4443 */
fixed= 1;
return FALSE;
}
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2011-01-18 11:42:09 +0000
+++ b/sql/item_subselect.cc 2011-02-21 16:27:53 +0000
@@ -2313,7 +2313,7 @@ int subselect_single_select_engine::exec
SELECT_LEX_UNIT *unit= select_lex->master_unit();
unit->set_limit(unit->global_parameters);
- if (join->optimize())
+ if (join->optimize_before_locking() || join->optimize_after_locking())
{
executed= true;
rc= join->error ? join->error : 1;
@@ -3382,7 +3382,7 @@ int subselect_hash_sj_engine::exec()
int res= 0;
SELECT_LEX *save_select= thd->lex->current_select;
thd->lex->current_select= materialize_engine->select_lex;
- if ((res= materialize_join->optimize()))
+ if ((res= materialize_join->optimize_before_locking()) || (res= materialize_join->optimize_after_locking()))
goto err; /* purecov: inspected */
if (materialize_engine->save_join_if_explain())
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-02-02 22:02:29 +0000
+++ b/sql/opt_range.cc 2011-02-21 16:27:53 +0000
@@ -2807,6 +2807,17 @@ end:
/* Must be a subset of the locked partitions */
bitmap_intersect(&(prune_param.part_info->read_partitions),
&(prune_param.part_info->lock_partitions));
+ /*
+ If not yet locked, also prune partitions to lock if not UPDATEing
+ partition key fields.
+ TODO: enhance this prune locking to also allow pruning of
+ 'UPDATE t SET part_key = const WHERE cond_is_prunable' so it adds
+ a lock for part_key partition.
+ */
+ if (table->file->get_lock_type() == F_UNLCK &&
+ !partition_key_modified(table, table->write_set))
+ bitmap_intersect(&(prune_param.part_info->lock_partitions),
+ &(prune_param.part_info->read_partitions));
if (bitmap_is_clear_all(&(prune_param.part_info->read_partitions)))
retval= TRUE;
DBUG_RETURN(retval);
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2011-02-14 10:28:11 +0000
+++ b/sql/sql_base.cc 2011-02-21 16:27:53 +0000
@@ -5431,6 +5431,23 @@ end:
/**
+ Cleanup failed open or lock tables.
+
+ @param thd Thread context.
+ @param mdl_savepoint Savepoint for rollback.
+*/
+
+void open_or_lock_cleanup(THD *thd, const MDL_savepoint &mdl_savepoint)
+{
+ if (! thd->in_sub_stmt)
+ trans_rollback_stmt(thd); /* Necessary if derived handling failed. */
+ close_thread_tables(thd);
+ /* Don't keep locks for a failed statement. */
+ thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
+}
+
+
+/**
Open all tables in list, locks them and optionally process derived tables.
@param thd Thread context.
@@ -5487,11 +5504,7 @@ bool open_and_lock_tables(THD *thd, TABL
DBUG_RETURN(FALSE);
err:
- if (! thd->in_sub_stmt)
- trans_rollback_stmt(thd); /* Necessary if derived handling failed. */
- close_thread_tables(thd);
- /* Don't keep locks for a failed statement. */
- thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
+ open_or_lock_cleanup(thd, mdl_savepoint);
DBUG_RETURN(TRUE);
}
@@ -7941,7 +7954,8 @@ bool setup_tables(THD *thd, Name_resolut
uint tablenr= 0;
DBUG_ENTER("setup_tables");
- DBUG_ASSERT ((select_insert && !tables->next_name_resolution_table) || !tables ||
+ DBUG_ASSERT ((select_insert && !tables->next_name_resolution_table) ||
+ !tables ||
(context->table_list && context->first_name_resolution_table));
/*
this is used for INSERT ... SELECT.
=== modified file 'sql/sql_base.h'
--- a/sql/sql_base.h 2010-11-23 22:37:59 +0000
+++ b/sql/sql_base.h 2011-02-21 16:27:53 +0000
@@ -246,6 +246,7 @@ bool lock_table_names(THD *thd, TABLE_LI
uint flags);
bool open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags,
Prelocking_strategy *prelocking_strategy);
+void open_or_lock_cleanup(THD *thd, const MDL_savepoint &mdl_savepoint);
/* open_and_lock_tables with optional derived handling */
bool open_and_lock_tables(THD *thd, TABLE_LIST *tables,
bool derived, uint flags,
=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc 2010-12-16 10:09:53 +0000
+++ b/sql/sql_derived.cc 2011-02-21 16:27:53 +0000
@@ -240,6 +240,167 @@ exit:
/*
+ prepare fill derived table
+
+ SYNOPSIS
+ mysql_derived_prepare_filling()
+ thd Thread handle
+ lex LEX for this thread
+ unit node that contains all SELECT's for derived tables
+ orig_table_list TABLE_LIST for the upper SELECT
+
+ IMPLEMENTATION
+ Derived table is resolved with temporary table. It is created based on the
+ queries defined. After temporary table is filled, if this is not EXPLAIN,
+ then the entire unit / node is deleted. unit is deleted if UNION is used
+ for derived table and node is deleted is it is a simple SELECT.
+ If you use this function, make sure it's not called at prepare.
+ Due to evaluation of LIMIT clause it can not be used at prepared stage.
+
+ RETURN
+ FALSE OK
+ TRUE Error
+*/
+
+bool mysql_derived_prepare_filling(THD *thd, LEX *lex,
+ TABLE_LIST *orig_table_list)
+{
+ TABLE *table= orig_table_list->table;
+ SELECT_LEX_UNIT *unit= orig_table_list->derived;
+ bool res= false;
+
+ DBUG_ENTER("mysql_derived_prepare_filling");
+ /*check that table creation pass without problem and it is derived table */
+ if (table && unit)
+ {
+ SELECT_LEX *first_select= unit->first_select();
+ select_union *derived_result= orig_table_list->derived_result;
+ SELECT_LEX *save_current_select= lex->current_select;
+ DBUG_ASSERT(table->db_stat);
+ if (unit->is_union())
+ {
+ // execute union without clean up
+ res= unit->prepare(thd, derived_result, SELECT_NO_UNLOCK);
+ for (SELECT_LEX *sl= unit->first_select() ;sl; sl= sl->next_select())
+ {
+ if (sl->join->optimize_before_locking())
+ {
+ res= true;
+ break;
+ }
+ }
+ }
+ else
+ {
+ unit->set_limit(first_select);
+ if (unit->select_limit_cnt == HA_POS_ERROR)
+ first_select->options&= ~OPTION_FOUND_ROWS;
+
+ lex->current_select= first_select;
+ res= mysql_prepare_select(thd, &first_select->ref_pointer_array,
+ first_select->table_list.first,
+ first_select->with_wild,
+ first_select->item_list, first_select->where,
+ (first_select->order_list.elements +
+ first_select->group_list.elements),
+ first_select->order_list.first,
+ first_select->group_list.first,
+ first_select->having, (ORDER*) NULL,
+ (first_select->options |
+ thd->variables.option_bits |
+ SELECT_NO_UNLOCK),
+ derived_result, unit, first_select,
+ &orig_table_list->derived_free_join,
+ &orig_table_list->derived_join);
+ }
+
+ if (!res)
+ {
+ /*
+ Here we entirely fix both TABLE_LIST and list of SELECT's as
+ there were no derived tables
+ */
+ if (derived_result->flush())
+ res= TRUE;
+ }
+ lex->current_select= save_current_select;
+ }
+ DBUG_RETURN(res);
+}
+
+
+/*
+ execute fill derived table
+
+ SYNOPSIS
+ mysql_derived_execute_filling()
+ thd Thread handle
+ lex LEX for this thread
+ unit node that contains all SELECT's for derived tables
+ orig_table_list TABLE_LIST for the upper SELECT
+
+ IMPLEMENTATION
+ Derived table is resolved with temporary table. It is created based on the
+ queries defined. After temporary table is filled, if this is not EXPLAIN,
+ then the entire unit / node is deleted. unit is deleted if UNION is used
+ for derived table and node is deleted is it is a simple SELECT.
+ If you use this function, make sure it's not called at prepare.
+ Due to evaluation of LIMIT clause it can not be used at prepared stage.
+
+ RETURN
+ FALSE OK
+ TRUE Error
+*/
+
+bool mysql_derived_execute_filling(THD *thd, LEX *lex,
+ TABLE_LIST *orig_table_list)
+{
+ TABLE *table= orig_table_list->table;
+ SELECT_LEX_UNIT *unit= orig_table_list->derived;
+ bool res= FALSE;
+ DBUG_ENTER("mysql_derived_execute_filling");
+
+ /*check that table creation pass without problem and it is derived table */
+ if (table && unit)
+ {
+ SELECT_LEX *first_select= unit->first_select();
+ select_union *derived_result= orig_table_list->derived_result;
+ SELECT_LEX *save_current_select= lex->current_select;
+ if (unit->is_union())
+ {
+ // execute union without clean up
+ /* TODO: add flags to check if tables are already opened and locked */
+ res= unit->exec();
+ }
+ else
+ {
+ unit->set_limit(first_select);
+ if (unit->select_limit_cnt == HA_POS_ERROR)
+ first_select->options&= ~OPTION_FOUND_ROWS;
+
+ lex->current_select= first_select;
+ res= mysql_execute_select(thd, first_select,
+ orig_table_list->derived_free_join,
+ orig_table_list->derived_join);
+ orig_table_list->derived_join= NULL;
+ }
+
+ if (!res)
+ {
+ /*
+ Here we entirely fix both TABLE_LIST and list of SELECT's as
+ there were no derived tables
+ */
+ if (derived_result->flush())
+ res= TRUE;
+ }
+ lex->current_select= save_current_select;
+ }
+ DBUG_RETURN(res);
+}
+
+
+/*
fill derived table
SYNOPSIS
@@ -268,6 +429,7 @@ bool mysql_derived_filling(THD *thd, LEX
SELECT_LEX_UNIT *unit= orig_table_list->derived;
bool res= FALSE;
+ DBUG_ENTER("mysql_derived_filling");
/*check that table creation pass without problem and it is derived table */
if (table && unit)
{
@@ -297,7 +459,8 @@ bool mysql_derived_filling(THD *thd, LEX
first_select->having, (ORDER*) NULL,
(first_select->options | thd->variables.option_bits |
SELECT_NO_UNLOCK),
- derived_result, unit, first_select);
+ derived_result, unit, first_select, NULL, true, NULL,
+ NULL);
}
if (!res)
@@ -311,7 +474,7 @@ bool mysql_derived_filling(THD *thd, LEX
}
lex->current_select= save_current_select;
}
- return res;
+ DBUG_RETURN(res);
}
@@ -322,7 +485,8 @@ bool mysql_derived_filling(THD *thd, LEX
bool mysql_derived_cleanup(THD *thd, LEX *lex, TABLE_LIST *derived)
{
SELECT_LEX_UNIT *unit= derived->derived;
+ DBUG_ENTER("mysql_derived_cleanup");
if (unit)
unit->cleanup();
- return false;
+ DBUG_RETURN(false);
}
=== modified file 'sql/sql_derived.h'
--- a/sql/sql_derived.h 2010-12-16 10:09:53 +0000
+++ b/sql/sql_derived.h 2011-02-21 16:27:53 +0000
@@ -24,6 +24,8 @@ bool mysql_handle_derived(LEX *lex, bool
LEX *lex,
TABLE_LIST *table));
bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *t);
+bool mysql_derived_execute_filling(THD *thd, LEX *lex, TABLE_LIST *t);
+bool mysql_derived_prepare_filling(THD *thd, LEX *lex, TABLE_LIST *t);
bool mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *t);
/**
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2011-01-31 13:44:38 +0000
+++ b/sql/sql_insert.cc 2011-02-21 16:27:53 +0000
@@ -59,6 +59,7 @@
#include "unireg.h" // REQUIRED: for other includes
#include "sql_insert.h"
#include "sql_update.h" // compare_record
+#include "sql_derived.h" // mysql_derived_*
#include "sql_base.h" // close_thread_tables
#include "sql_cache.h" // query_cache_*
#include "key.h" // key_copy
@@ -680,6 +681,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
#endif
thr_lock_type lock_type;
Item *unused_conds= 0;
+ MDL_savepoint mdl_savepoint;
+ uint table_counter, *used_table_counter= NULL;
DBUG_ENTER("mysql_insert");
/*
@@ -711,8 +714,20 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
}
else
{
- if (open_and_lock_tables(thd, table_list, TRUE, 0))
+ used_table_counter= &table_counter;
+ mdl_savepoint= thd->mdl_context.mdl_savepoint();
+ if (open_tables(thd, &table_list, used_table_counter, 0))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
DBUG_RETURN(TRUE);
+ }
+ if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+ (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_prepare_filling)))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
}
lock_type= table_list->lock_type;
@@ -754,6 +769,25 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
table_list->next_local= 0;
context->resolve_in_table_list_only(table_list);
+#ifdef WITH_PARTITION_STORAGE_ENGINE_NOT_YET
+ if (table->part_info)
+ {
+ if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT ||
+ /* all inserted rows must explicitly set auto increment column */
+ (table->found_next_number_field && TRUE) /* WAS HERE */
+ {
+ /* Pruning not possible, must mark all partitions for read/lock */
+ }
+ else
+ {
+ /*
+ Pruning probably possible, must mark none partitions for read/lock,
+ and add them on row by row basis (if any row does not set auto_inc
+ value explicitly, all partitions must be marked).
+ */
+ }
+ }
+#endif /* WITH_PARTITION_STORAGE_ENGINE */
while ((values= its++))
{
counter++;
@@ -764,12 +798,40 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
}
if (setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0))
goto abort;
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ /* TODO: calculate partition id and store for reuse */
+ /* Must check for table->auto_increment_field_not_null -> prune none. */
+ /* If auto_increment_filed_not_null + val_int() (NOT_ZERO...) */
+ /* Perhaps only do this if its.elements < MAX_INSERT_ROWS_TO_PRUNE */
+ /* Must check for on duplicate key ... */
+ /* WASHERE */
+
+#endif /* WITH_PARTITION_STORAGE_ENGINE */
}
its.rewind ();
/* Restore the current context. */
ctx_state.restore_state(context, table_list);
+ if (used_table_counter)
+ {
+ /* lock the tables and handle derived tables */
+ if (lock_tables(thd, table_list, *used_table_counter, 0))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+ /* Handle derived tables */
+ if (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_execute_filling))
+ {
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ DBUG_RETURN(true);
+ }
+ if (!thd->lex->describe)
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ }
+
/*
Fill in the given fields and dump it to the table file
*/
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2011-02-21 11:34:14 +0000
+++ b/sql/sql_parse.cc 2011-02-21 16:27:53 +0000
@@ -32,6 +32,7 @@
#include "log.h" // flush_error_log
#include "sql_view.h" // mysql_create_view, mysql_drop_view
#include "sql_delete.h" // mysql_delete
+#include "sql_derived.h" // mysql_derived_*
#include "sql_insert.h" // mysql_insert
#include "sql_update.h" // mysql_update, mysql_multi_update
#include "sql_partition.h" // struct partition_info
@@ -2361,6 +2362,8 @@ case SQLCOM_PREPARE:
if (select_lex->item_list.elements) // With select
{
select_result *result;
+ uint counter;
+ MDL_savepoint mdl_savepoint;
/*
If:
@@ -2415,7 +2418,12 @@ case SQLCOM_PREPARE:
goto end_with_restore_list;
}
- if (!(res= open_and_lock_tables(thd, lex->query_tables, TRUE, 0)))
+ mdl_savepoint= thd->mdl_context.mdl_savepoint();
+ if (!((res= open_tables(thd, &lex->query_tables, &counter, 0)) ||
+ (res= mysql_handle_derived(lex, &mysql_derived_prepare)) ||
+ (thd->fill_derived_tables() &&
+ (res= mysql_handle_derived(lex,
+ &mysql_derived_prepare_filling)))))
{
/* The table already exists */
if (create_table->table || create_table->view)
@@ -2433,6 +2441,7 @@ case SQLCOM_PREPARE:
my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_info.alias);
res= 1;
}
+ open_or_lock_cleanup(thd, mdl_savepoint);
goto end_with_restore_list;
}
@@ -2474,9 +2483,12 @@ case SQLCOM_PREPARE:
CREATE from SELECT give its SELECT_LEX for SELECT,
and item_list belong to SELECT
*/
- res= handle_select(thd, lex, result, 0);
+ res= handle_select(thd, result, 0, lex->query_tables, true,
+ &counter, &mdl_savepoint);
delete result;
}
+ else
+ open_or_lock_cleanup(thd, mdl_savepoint);
lex->link_first_table_back(create_table, link_to_local);
}
@@ -2596,7 +2608,7 @@ end_with_restore_list:
&table->next_local->grant.privilege,
&table->next_local->grant.m_internal,
0, 0))
- goto error;
+ goto error;
TABLE_LIST old_list, new_list;
/*
we do not need initialize old_list and new_list because we will
@@ -2625,7 +2637,7 @@ end_with_restore_list:
#else
{
if (check_global_access(thd, SUPER_ACL))
- goto error;
+ goto error;
res = show_binlogs(thd);
break;
}
@@ -2639,7 +2651,7 @@ end_with_restore_list:
goto error;
#else
{
- /*
+ /*
Access check:
SHOW CREATE TABLE require any privileges on the table level (ie
effecting all columns in the table).
@@ -2657,8 +2669,8 @@ end_with_restore_list:
{
DBUG_PRINT("debug", ("check_table_access failed"));
my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0),
- "SHOW", thd->security_ctx->priv_user,
- thd->security_ctx->host_or_ip, first_table->alias);
+ "SHOW", thd->security_ctx->priv_user,
+ thd->security_ctx->host_or_ip, first_table->alias);
goto error;
}
DBUG_PRINT("debug", ("check_table_access succeeded"));
@@ -2680,8 +2692,8 @@ end_with_restore_list:
(first_table->grant.privilege & SHOW_CREATE_TABLE_ACLS) == 0)
{
my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0),
- "SHOW", thd->security_ctx->priv_user,
- thd->security_ctx->host_or_ip, first_table->alias);
+ "SHOW", thd->security_ctx->priv_user,
+ thd->security_ctx->host_or_ip, first_table->alias);
goto error;
}
}
@@ -2763,11 +2775,11 @@ end_with_restore_list:
if (res)
break;
if (opt_readonly &&
- !(thd->security_ctx->master_access & SUPER_ACL) &&
- some_non_temp_table_to_be_updated(thd, all_tables))
+ !(thd->security_ctx->master_access & SUPER_ACL) &&
+ some_non_temp_table_to_be_updated(thd, all_tables))
{
- my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only");
- break;
+ my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only");
+ break;
}
#ifdef HAVE_REPLICATION
} /* unlikely */
@@ -2842,7 +2854,7 @@ end_with_restore_list:
MYSQL_INSERT_START(thd->query());
res= mysql_insert(thd, all_tables, lex->field_list, lex->many_values,
- lex->update_list, lex->value_list,
+ lex->update_list, lex->value_list,
lex->duplicates, lex->ignore);
MYSQL_INSERT_DONE(res, (ulong) thd->get_row_count_func());
/*
@@ -2870,6 +2882,8 @@ end_with_restore_list:
case SQLCOM_INSERT_SELECT:
{
select_result *sel_result;
+ uint counter;
+ MDL_savepoint mdl_savepoint;
DBUG_ASSERT(first_table == all_tables && first_table != 0);
if ((res= insert_precheck(thd, all_tables)))
break;
@@ -2883,7 +2897,12 @@ end_with_restore_list:
unit->set_limit(select_lex);
- if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
+ mdl_savepoint= thd->mdl_context.mdl_savepoint();
+ if (!((res= open_tables(thd, &all_tables, &counter, 0)) ||
+ (res= mysql_handle_derived(lex, &mysql_derived_prepare)) ||
+ (thd->fill_derived_tables() &&
+ (res= mysql_handle_derived(lex,
+ &mysql_derived_prepare_filling)))))
{
MYSQL_INSERT_SELECT_START(thd->query());
/* Skip first table, which is the table we are inserting in */
@@ -2891,6 +2910,7 @@ end_with_restore_list:
select_lex->table_list.first= second_table;
select_lex->context.table_list=
select_lex->context.first_name_resolution_table= second_table;
+ /* TODO: Add support for INSERT SELECT partitioning pruning */
res= mysql_insert_select_prepare(thd);
if (!res && (sel_result= new select_insert(first_table,
first_table->table,
@@ -2900,7 +2920,8 @@ end_with_restore_list:
lex->duplicates,
lex->ignore)))
{
- res= handle_select(thd, lex, sel_result, OPTION_SETUP_TABLES_DONE);
+ res= handle_select(thd, sel_result, OPTION_SETUP_TABLES_DONE,
+ all_tables, true, &counter, &mdl_savepoint);
/*
Invalidate the table in the query cache if something changed
after unlocking when changes become visible.
@@ -2918,6 +2939,8 @@ end_with_restore_list:
}
delete sel_result;
}
+ else
+ open_or_lock_cleanup(thd, mdl_savepoint);
/* revert changes for SP */
MYSQL_INSERT_SELECT_DONE(res, (ulong) thd->get_row_count_func());
select_lex->table_list.first= first_table;
@@ -2954,6 +2977,8 @@ end_with_restore_list:
DBUG_ASSERT(first_table == all_tables && first_table != 0);
TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first;
multi_delete *del_result;
+ uint counter;
+ MDL_savepoint mdl_savepoint;
if ((res= multi_delete_precheck(thd, all_tables)))
break;
@@ -2965,13 +2990,21 @@ end_with_restore_list:
goto error;
thd_proc_info(thd, "init");
- if ((res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
+ mdl_savepoint= thd->mdl_context.mdl_savepoint();
+ if ((res= open_tables(thd, &all_tables, &counter, 0)) ||
+ (res= mysql_handle_derived(lex, &mysql_derived_prepare)) ||
+ (thd->fill_derived_tables() &&
+ (res= mysql_handle_derived(lex, &mysql_derived_prepare_filling))))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
break;
+ }
MYSQL_MULTI_DELETE_START(thd->query());
if ((res= mysql_multi_delete_prepare(thd)))
{
MYSQL_MULTI_DELETE_DONE(1, 0);
+ open_or_lock_cleanup(thd, mdl_savepoint);
goto error;
}
@@ -2988,7 +3021,8 @@ end_with_restore_list:
(select_lex->options | thd->variables.option_bits |
SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT,
- del_result, unit, select_lex);
+ del_result, unit, select_lex, all_tables, true,
+ &counter, &mdl_savepoint);
res|= thd->is_error();
MYSQL_MULTI_DELETE_DONE(res, del_result->num_deleted());
if (res)
@@ -2999,6 +3033,7 @@ end_with_restore_list:
{
res= TRUE; // Error
MYSQL_MULTI_DELETE_DONE(1, 0);
+ open_or_lock_cleanup(thd, mdl_savepoint);
}
break;
}
@@ -4472,9 +4507,8 @@ static bool execute_sqlcom_select(THD *t
new Item_int((ulonglong) thd->variables.select_limit);
}
thd->thd_marker.emb_on_expr_nest= NULL;
- if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
+ if (lex->describe)
{
- if (lex->describe)
{
/*
We always use select_send for EXPLAIN, even if it's an EXPLAIN
@@ -4485,7 +4519,7 @@ static bool execute_sqlcom_select(THD *t
if (!(result= new select_send()))
return 1; /* purecov: inspected */
thd->send_explain_fields(result);
- res= mysql_explain_union(thd, &thd->lex->unit, result);
+ res= mysql_explain_union(thd, &all_tables, &thd->lex->unit, result);
/*
The code which prints the extended description is not robust
against malformed queries, so skip it if we have an error.
@@ -4506,12 +4540,13 @@ static bool execute_sqlcom_select(THD *t
result->send_eof();
delete result;
}
- else
+ }
+ else
+ {
{
if (!result && !(result= new select_send()))
return 1; /* purecov: inspected */
- query_cache_store_query(thd, all_tables);
- res= handle_select(thd, lex, result, 0);
+ res= handle_select(thd, result, 0, all_tables, false, NULL, NULL);
if (result != lex->result)
delete result;
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-02-18 10:59:18 +0000
+++ b/sql/sql_select.cc 2011-02-21 16:27:53 +0000
@@ -39,7 +39,8 @@
// mysql_unlock_read_tables
#include "sql_show.h" // append_identifier
#include "sql_base.h" // setup_wild, setup_fields, fill_record
-#include "sql_parse.h" // check_stack_overrun
+#include "sql_derived.h" // mysql_derived_*
+#include "sql_parse.h" // check_stack_overrun
#include "sql_partition.h" // make_used_partitions_str
#include "sql_acl.h" // *_ACL
#include "sql_test.h" // print_where, print_keyuse_array,
@@ -288,20 +289,27 @@ Item_equal *find_item_equal(COND_EQUAL *
This handles SELECT with and without UNION
*/
-bool handle_select(THD *thd, LEX *lex, select_result *result,
- ulong setup_tables_done_option)
+bool handle_select(THD *thd, select_result *result,
+ ulong setup_tables_done_option,
+ TABLE_LIST *tables_to_open_and_lock,
+ bool open_tables_is_done, uint *tables_to_lock,
+ MDL_savepoint *open_mdl_savepoint)
{
bool res;
- register SELECT_LEX *select_lex = &lex->select_lex;
+ register SELECT_LEX *select_lex = &thd->lex->select_lex;
DBUG_ENTER("handle_select");
MYSQL_SELECT_START(thd->query());
if (select_lex->master_unit()->is_union() ||
select_lex->master_unit()->fake_select_lex)
- res= mysql_union(thd, lex, result, &lex->unit, setup_tables_done_option);
+ {
+ res= mysql_union(thd, result, &thd->lex->unit, setup_tables_done_option,
+ tables_to_open_and_lock, open_tables_is_done,
+ tables_to_lock, open_mdl_savepoint);
+ }
else
{
- SELECT_LEX_UNIT *unit= &lex->unit;
+ SELECT_LEX_UNIT *unit= &thd->lex->unit;
unit->set_limit(unit->global_parameters);
/*
'options' of mysql_select will be set in JOIN, as far as JOIN for
@@ -317,10 +325,11 @@ bool handle_select(THD *thd, LEX *lex, s
select_lex->order_list.first,
select_lex->group_list.first,
select_lex->having,
- lex->proc_list.first,
+ thd->lex->proc_list.first,
select_lex->options | thd->variables.option_bits |
setup_tables_done_option,
- result, unit, select_lex);
+ result, unit, select_lex, tables_to_open_and_lock,
+ open_tables_is_done, tables_to_lock, open_mdl_savepoint);
}
DBUG_PRINT("info",("res: %d report_error: %d", res,
thd->is_error()));
@@ -508,7 +517,7 @@ inline int setup_without_group(THD *thd,
/*****************************************************************************
Check fields, find best join, do the select and output fields.
- mysql_select assumes that all tables are already opened
+ All tables must be opened.
*****************************************************************************/
/**
@@ -1749,20 +1758,17 @@ static int clear_sj_tmp_tables(JOIN *joi
*/
int
-JOIN::optimize()
+JOIN::optimize_before_locking()
{
- bool need_distinct;
- ulonglong select_opts_for_readinfo;
- uint no_jbuf_after;
+ DBUG_ENTER("JOIN::optimize_before_locking");
- DBUG_ENTER("JOIN::optimize");
// to prevent double initialization on EXPLAIN
- if (optimized)
+ if (optimized > 0)
DBUG_RETURN(0);
optimized= 1;
DEBUG_SYNC(thd, "before_join_optimize");
- thd_proc_info(thd, "optimizing");
+ thd_proc_info(thd, "optimizing before locking");
/* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */
if (flatten_subqueries())
@@ -1854,6 +1860,10 @@ JOIN::optimize()
zero_result_cause= select_lex->having_value == Item::COND_FALSE ?
"Impossible HAVING" : "Impossible WHERE";
tables= 0;
+ /*
+ DBUG_ASSERT(0);
+ DBUG_RETURN(1);
+ */
goto setup_subq_exit;
}
}
@@ -1861,6 +1871,7 @@ JOIN::optimize()
#ifdef WITH_PARTITION_STORAGE_ENGINE
{
TABLE_LIST *tbl;
+ //bool no_partition_is_used= true;
for (tbl= select_lex->leaf_tables; tbl; tbl= tbl->next_leaf)
{
/*
@@ -1874,10 +1885,60 @@ JOIN::optimize()
Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
tbl->table->no_partitions_used= prune_partitions(thd, tbl->table,
prune_cond);
+ //if (tbl->table->no_partitions_used)
+ //no_partition_is_used= false;
}
+ //else
+ //no_partition_is_used= false;
}
+ //if (no_partition_is_used)
+ //goto setup_subq_exit;
+ // TODO: test with one table that is completely pruned and one that is not.
}
#endif
+ DBUG_RETURN(0);
+setup_subq_exit:
+ /*
+ Even with zero matching rows, subqueries in the HAVING clause may
+ need to be evaluated if there are aggregate functions in the
+ query. If we have planned to materialize the subquery, we need to
+ set it up properly before prematurely leaving optimize().
+ */
+ if (setup_subquery_materialization())
+ DBUG_RETURN(1);
+ /* No matching rows, don't execute optimize_after_locking(). */
+ optimized= 2;
+ error= 0;
+ DBUG_RETURN(0);
+}
+
+/**
+ global select optimisation.
+
+ @note
+ error code saved in field 'error'
+
+ @retval
+ 0 success
+ @retval
+ 1 error
+*/
+
+int
+JOIN::optimize_after_locking()
+{
+ bool need_distinct;
+ ulonglong select_opts_for_readinfo;
+ uint no_jbuf_after;
+
+ DBUG_ENTER("JOIN::optimize_after_locking");
+ // to prevent double initialization on EXPLAIN
+ if (optimized > 1)
+ DBUG_RETURN(0);
+ optimized= 2;
+
+ thd_proc_info(thd, "optimizing after locking");
+
/*
Try to optimize count(*), min() and max() to const fields if
@@ -3460,7 +3521,7 @@ void JOIN::cleanup_item_list(List<Item>
/**
- An entry point to single-unit select (a select without UNION).
+ Prepare stage of mysql_select.
@param thd thread handler
@param rref_pointer_array a reference to ref_pointer_array of
@@ -3489,37 +3550,42 @@ void JOIN::cleanup_item_list(List<Item>
This object is responsible for send result
set rows to the client or inserting them
into a table.
- @param select_lex the only SELECT_LEX of this query
@param unit top-level UNIT of this query
UNIT is an artificial object created by the
parser for every SELECT clause.
e.g.
SELECT * FROM t1 WHERE a1 IN (SELECT * FROM t2)
has 2 unions.
+ @param select_lex the only SELECT_LEX of this query
+ @param[out] free_join if returned JOIN should be freed
+ @param[in,out] join_ptr JOIN to execute
@retval
FALSE success
@retval
TRUE an error
+
+ @note tables must be opened before calling mysql_prepare_select.
*/
bool
-mysql_select(THD *thd, Item ***rref_pointer_array,
- TABLE_LIST *tables, uint wild_num, List<Item> &fields,
- Item *conds, uint og_num, ORDER *order, ORDER *group,
- Item *having, ORDER *proc_param, ulonglong select_options,
- select_result *result, SELECT_LEX_UNIT *unit,
- SELECT_LEX *select_lex)
+mysql_prepare_select(THD *thd, Item ***rref_pointer_array,
+ TABLE_LIST *tables, uint wild_num, List<Item> &fields,
+ Item *conds, uint og_num, ORDER *order, ORDER *group,
+ Item *having, ORDER *proc_param, ulonglong select_options,
+ select_result *result, SELECT_LEX_UNIT *unit,
+ SELECT_LEX *select_lex, bool *free_join, JOIN **join_ptr)
{
bool err;
- bool free_join= 1;
- DBUG_ENTER("mysql_select");
+ JOIN *join;
+ DBUG_ENTER("mysql_prepare_select");
+ DBUG_ASSERT(join_ptr);
select_lex->context.resolve_in_select_list= TRUE;
- JOIN *join;
if (select_lex->join != 0)
{
join= select_lex->join;
+ *join_ptr= join;
/*
is it single SELECT in derived table, called in derived table
creation
@@ -3531,9 +3597,7 @@ mysql_select(THD *thd, Item ***rref_poin
{
//here is EXPLAIN of subselect or derived table
if (join->change_result(result))
- {
DBUG_RETURN(TRUE);
- }
/*
Original join tabs might be overwritten at first
subselect execution. So we need to restore them.
@@ -3548,32 +3612,63 @@ mysql_select(THD *thd, Item ***rref_poin
conds, og_num, order, group, having, proc_param,
select_lex, unit);
if (err)
- {
- goto err;
- }
+ DBUG_RETURN(TRUE);
}
}
- free_join= 0;
+ *free_join= 0;
join->select_options= select_options;
}
else
{
if (!(join= new JOIN(thd, fields, select_options, result)))
DBUG_RETURN(TRUE); /* purecov: inspected */
+ *join_ptr= join;
+ *free_join= 1;
thd_proc_info(thd, "init");
thd->used_tables=0; // Updated by setup_fields
err= join->prepare(rref_pointer_array, tables, wild_num,
conds, og_num, order, group, having, proc_param,
select_lex, unit);
if (err)
- {
- goto err;
- }
+ DBUG_RETURN(TRUE);
}
- if ((err= join->optimize()))
+ err= join->optimize_before_locking();
+
+ DBUG_RETURN(err);
+}
+
+
+/**
+ Execute stage of mysql_select.
+
+ @param thd thread handler
+ @param select_lex the only SELECT_LEX of this query
+ @param free_join if join should be freed
+ @param join JOIN to execute
+
+ @retval
+ FALSE success
+ @retval
+ TRUE an error
+
+ @note tables must be opened and locked before calling mysql_execute_select.
+*/
+
+bool
+mysql_execute_select(THD *thd, SELECT_LEX *select_lex, bool free_join,
+ JOIN *join)
+{
+ bool err;
+
+ DBUG_ENTER("mysql_execute_select");
+
+ if (!join)
+ DBUG_RETURN(false);
+
+ if ((err= join->optimize_after_locking()))
{
- goto err; // 1
+ goto err; // 1
}
if (thd->lex->describe & DESCRIBE_EXTENDED)
@@ -3604,6 +3699,159 @@ err:
}
+/**
+ An entry point to single-unit select (a select without UNION).
+
+ @param thd thread handler
+ @param rref_pointer_array a reference to ref_pointer_array of
+ the top-level select_lex for this query
+ @param tables list of all tables used in this query.
+ The tables have been pre-opened.
+ @param wild_num number of wildcards used in the top level
+ select of this query.
+ For example statement
+ SELECT *, t1.*, catalog.t2.* FROM t0, t1, t2;
+ has 3 wildcards.
+ @param fields list of items in SELECT list of the top-level
+ select
+ e.g. SELECT a, b, c FROM t1 will have Item_field
+ for a, b and c in this list.
+ @param conds top level item of an expression representing
+ WHERE clause of the top level select
+ @param og_num total number of ORDER BY and GROUP BY clauses
+ arguments
+ @param order linked list of ORDER BY agruments
+ @param group linked list of GROUP BY arguments
+ @param having top level item of HAVING expression
+ @param proc_param list of PROCEDUREs
+ @param select_options select options (BIG_RESULT, etc)
+ @param result an instance of result set handling class.
+ This object is responsible for send result
+ set rows to the client or inserting them
+ into a table.
+ @param unit top-level UNIT of this query
+ UNIT is an artificial object created by the
+ parser for every SELECT clause.
+ e.g.
+ SELECT * FROM t1 WHERE a1 IN (SELECT * FROM t2)
+ has 2 unions.
+ @param select_lex the only SELECT_LEX of this query
+ @param tables_to_open_and_lock Tables to open and lock
+ (NULL if already locked)
+ @param open_tables_is_done All tables are already opened
+ @param tables_to_lock If open_tables_is_done, not null means we should
+ also lock tables
+ @param open_mdl_savepoint If open_tables_is_done, savepoint to use on
+ failure
+
+ @retval
+ FALSE success
+ @retval
+ TRUE an error
+*/
+
+bool
+mysql_select(THD *thd, Item ***rref_pointer_array,
+ TABLE_LIST *tables, uint wild_num, List<Item> &fields,
+ Item *conds, uint og_num, ORDER *order, ORDER *group,
+ Item *having, ORDER *proc_param, ulonglong select_options,
+ select_result *result, SELECT_LEX_UNIT *unit,
+ SELECT_LEX *select_lex, TABLE_LIST *tables_to_open_and_lock,
+ bool open_tables_is_done, uint *tables_to_lock,
+ MDL_savepoint *open_mdl_savepoint)
+{
+ bool free_join= 1;
+ JOIN *join;
+ uint table_counter;
+ MDL_savepoint mdl_savepoint;
+ DBUG_ENTER("mysql_select");
+
+ if (!open_tables_is_done)
+ {
+ DBUG_ASSERT(!tables_to_lock && !open_mdl_savepoint);
+ mdl_savepoint= thd->mdl_context.mdl_savepoint();
+ /* Open the tables */
+ if (open_tables(thd, &tables_to_open_and_lock, &table_counter, 0))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+ query_cache_store_query(thd, tables_to_open_and_lock);
+ if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+ (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_prepare_filling)))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+ }
+ else
+ {
+ if (tables_to_lock)
+ {
+ DBUG_ASSERT(open_mdl_savepoint);
+ mdl_savepoint= *open_mdl_savepoint;
+ }
+ }
+
+ if (mysql_prepare_select(thd, rref_pointer_array, tables, wild_num, fields,
+ conds, og_num, order, group, having, proc_param,
+ select_options, result, unit, select_lex,
+ &free_join, &join))
+ {
+ /* TODO: Test this! */
+ if (free_join)
+ {
+ thd_proc_info(thd, "end");
+ (void) select_lex->cleanup();
+ }
+ if (!open_tables_is_done || tables_to_lock)
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+
+
+ /* Lock all tables */
+ if (!open_tables_is_done || tables_to_lock)
+ {
+ if (open_tables_is_done)
+ table_counter= *tables_to_lock;
+
+ /* Lock the tables */
+ if (lock_tables(thd, tables_to_open_and_lock, table_counter, 0))
+ {
+ if (free_join)
+ {
+ thd_proc_info(thd, "end");
+ (void) select_lex->cleanup();
+ }
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+ /* Handle derived tables */
+ if (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_execute_filling))
+ {
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ if (free_join)
+ {
+ thd_proc_info(thd, "end");
+ (void) select_lex->cleanup();
+ }
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+ if (!thd->lex->describe)
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ }
+
+ if (mysql_execute_select(thd, select_lex, free_join, join))
+ DBUG_RETURN(true);
+
+ DBUG_RETURN(false);
+}
+
+
int subq_sj_candidate_cmp(Item_exists_subselect* const *el1,
Item_exists_subselect* const *el2)
{
@@ -14648,6 +14896,8 @@ internal_remove_eq_conds(THD *thd, Item
return (Item*) 0;
}
}
+ /* TODO: Fix this so one can properly delay locking */
+#ifdef FIXED_DELAYED_LOCKING_ISSUE
else if (cond->const_item() && !cond->is_expensive())
/*
DontEvaluateMaterializedSubqueryTooEarly:
@@ -14663,6 +14913,7 @@ internal_remove_eq_conds(THD *thd, Item
*cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
return (Item*) 0;
}
+#endif
else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)
{ // boolan compare function
Item *left_item= ((Item_func*) cond)->arguments()[0];
@@ -23266,19 +23517,23 @@ void select_describe(JOIN *join, bool ne
unit;
unit= unit->next_unit())
{
- if (mysql_explain_union(thd, unit, result))
+ if (mysql_explain_union(thd, NULL, unit, result))
DBUG_VOID_RETURN;
}
DBUG_VOID_RETURN;
}
-bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
+bool mysql_explain_union(THD *thd, TABLE_LIST **tables, SELECT_LEX_UNIT *unit,
+ select_result *result)
{
- DBUG_ENTER("mysql_explain_union");
bool res= 0;
+ TABLE_LIST *tables_to_open_and_lock= NULL;
+ bool open_tables_is_done= true;
+
SELECT_LEX *first= unit->first_select();
+ DBUG_ENTER("mysql_explain_union");
for (SELECT_LEX *sl= first;
sl;
sl= sl->next_select())
@@ -23301,31 +23556,45 @@ bool mysql_explain_union(THD *thd, SELEC
"UNION")));
sl->options|= SELECT_DESCRIBE;
}
+
+ if (tables)
+ {
+ tables_to_open_and_lock= *tables;
+ open_tables_is_done= false;
+ }
+
if (unit->is_union())
{
unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization
unit->fake_select_lex->type= "UNION RESULT";
unit->fake_select_lex->options|= SELECT_DESCRIBE;
+ /*
if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
res= unit->exec();
res|= unit->cleanup();
+ */
+ res= mysql_union(thd, result, unit,
+ (SELECT_NO_UNLOCK | SELECT_DESCRIBE),
+ tables_to_open_and_lock, open_tables_is_done, NULL, NULL);
}
else
{
thd->lex->current_select= first;
unit->set_limit(unit->global_parameters);
res= mysql_select(thd, &first->ref_pointer_array,
- first->table_list.first,
- first->with_wild, first->item_list,
- first->where,
- first->order_list.elements +
- first->group_list.elements,
- first->order_list.first,
- first->group_list.first,
- first->having,
- thd->lex->proc_list.first,
- first->options | thd->variables.option_bits | SELECT_DESCRIBE,
- result, unit, first);
+ first->table_list.first,
+ first->with_wild, first->item_list,
+ first->where,
+ first->order_list.elements +
+ first->group_list.elements,
+ first->order_list.first,
+ first->group_list.first,
+ first->having,
+ thd->lex->proc_list.first,
+ (first->options | thd->variables.option_bits |
+ SELECT_DESCRIBE),
+ result, unit, first, tables_to_open_and_lock,
+ open_tables_is_done, NULL, NULL);
}
DBUG_RETURN(res || thd->is_error());
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2011-02-08 15:49:51 +0000
+++ b/sql/sql_select.h 2011-02-21 16:27:53 +0000
@@ -1823,7 +1823,7 @@ public:
const char *zero_result_cause; ///< not 0 if exec must return zero result
bool union_part; ///< this subselect is part of union
- bool optimized; ///< flag to avoid double optimization in EXPLAIN
+ uint optimized; ///< flag to avoid double optimization in EXPLAIN
Array<Item_exists_subselect> sj_subselects;
@@ -1917,7 +1917,8 @@ public:
Item *conds, uint og_num, ORDER *order, ORDER *group,
Item *having, ORDER *proc_param, SELECT_LEX *select,
SELECT_LEX_UNIT *unit);
- int optimize();
+ int optimize_before_locking();
+ int optimize_after_locking();
int reinit();
void exec();
int destroy();
@@ -2204,16 +2205,30 @@ int setup_group(THD *thd, Item **ref_poi
bool fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
Item **ref_pointer_array, ORDER *group_list= NULL);
-bool handle_select(THD *thd, LEX *lex, select_result *result,
- ulong setup_tables_done_option);
+bool handle_select(THD *thd, select_result *result,
+ ulong setup_tables_done_option,
+ TABLE_LIST *tables_to_open_and_lock,
+ bool open_tables_is_done, uint *tables_to_lock,
+ MDL_savepoint *open_mdl_savepoint);
+bool mysql_prepare_select(THD *thd, Item ***rref_pointer_array,
+ TABLE_LIST *tables, uint wild_num, List<Item> &fields,
+ Item *conds, uint og_num, ORDER *order, ORDER *group,
+ Item *having, ORDER *proc_param,
+ ulonglong select_options, select_result *result,
+ SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex,
+ bool *free_join, JOIN **join_ptr);
+bool mysql_execute_select(THD *thd, SELECT_LEX *select_lex, bool free_join,
+ JOIN *join);
bool mysql_select(THD *thd, Item ***rref_pointer_array,
TABLE_LIST *tables, uint wild_num, List<Item> &list,
Item *conds, uint og_num, ORDER *order, ORDER *group,
Item *having, ORDER *proc_param, ulonglong select_type,
select_result *result, SELECT_LEX_UNIT *unit,
- SELECT_LEX *select_lex);
+ SELECT_LEX *select_lex, TABLE_LIST *tables_to_open_and_lock,
+ bool open_tables_is_done, uint *tables_to_lock,
+ MDL_savepoint *open_mdl_savepoint);
void free_underlaid_joins(THD *thd, SELECT_LEX *select);
-bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit,
+bool mysql_explain_union(THD *thd, TABLE_LIST **tables, SELECT_LEX_UNIT *unit,
select_result *result);
Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
Item ***copy_func, Field **from_field,
=== modified file 'sql/sql_union.cc'
--- a/sql/sql_union.cc 2010-11-11 09:40:06 +0000
+++ b/sql/sql_union.cc 2011-02-21 16:27:53 +0000
@@ -25,19 +25,85 @@
#include "sql_union.h"
#include "sql_select.h"
#include "sql_cursor.h"
+#include "sql_derived.h" // mysql_derived_*
#include "sql_base.h" // fill_record
#include "filesort.h" // filesort_free_buffers
-bool mysql_union(THD *thd, LEX *lex, select_result *result,
- SELECT_LEX_UNIT *unit, ulong setup_tables_done_option)
+bool mysql_union(THD *thd, select_result *result,
+ SELECT_LEX_UNIT *unit, ulong setup_tables_done_option,
+ TABLE_LIST* tables_to_open_and_lock, bool open_tables_is_done,
+ uint *tables_to_lock, MDL_savepoint *open_mdl_savepoint)
{
DBUG_ENTER("mysql_union");
bool res;
- if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK |
- setup_tables_done_option)))
- res= unit->exec();
+ uint table_counter;
+ MDL_savepoint mdl_savepoint;
+
+ if (!open_tables_is_done)
+ {
+ DBUG_ASSERT(!tables_to_lock && !open_mdl_savepoint);
+ mdl_savepoint= thd->mdl_context.mdl_savepoint();
+ /* Open the tables */
+ if (open_tables(thd, &tables_to_open_and_lock, &table_counter, 0))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+ query_cache_store_query(thd, tables_to_open_and_lock);
+ if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+ (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_prepare_filling)))
+ {
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ DBUG_RETURN(true);
+ }
+ }
+ else
+ {
+ if (tables_to_lock)
+ {
+ DBUG_ASSERT(open_mdl_savepoint);
+ mdl_savepoint= *open_mdl_savepoint;
+ }
+ }
+
+ if (unit->prepare(thd, result, SELECT_NO_UNLOCK | setup_tables_done_option))
+ goto err;
+
+ for (SELECT_LEX *sl= unit->first_select() ;sl; sl= sl->next_select())
+ {
+ if (sl->join->optimize_before_locking())
+ goto err;
+ }
+
+ /* Lock all tables */
+ if (!open_tables_is_done || tables_to_lock)
+ {
+ if (open_tables_is_done)
+ table_counter= *tables_to_lock;
+
+ /* Lock the tables */
+ if (lock_tables(thd, tables_to_open_and_lock, table_counter, 0))
+ goto err;
+
+ /* Handle derived tables */
+ if (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_execute_filling))
+ {
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ goto err;
+ }
+ if (!thd->lex->describe)
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ }
+
+ res= unit->exec();
res|= unit->cleanup();
DBUG_RETURN(res);
+err:
+ open_or_lock_cleanup(thd, mdl_savepoint);
+ (void) unit->cleanup();
+ DBUG_RETURN(true);
}
@@ -258,7 +324,7 @@ bool st_select_lex_unit::prepare(THD *th
tmp_result);
/*
setup_tables_done_option should be set only for very first SELECT,
- because it protect from secont setup_tables call for select-like non
+ because it protect from second setup_tables call for select-like non
select commands (DELETE/INSERT/...) and they use only very first
SELECT (for union it can be only INSERT ... SELECT).
*/
@@ -500,15 +566,15 @@ bool st_select_lex_unit::exec()
else
{
set_limit(sl);
- if (sl == global_parameters || describe)
- {
- offset_limit_cnt= 0;
- /*
- We can't use LIMIT at this stage if we are using ORDER BY for the
- whole query
- */
- if (sl->order_list.first || describe)
- select_limit_cnt= HA_POS_ERROR;
+ if (sl == global_parameters || describe)
+ {
+ offset_limit_cnt= 0;
+ /*
+ We can't use LIMIT at this stage if we are using ORDER BY for the
+ whole query
+ */
+ if (sl->order_list.first || describe)
+ select_limit_cnt= HA_POS_ERROR;
}
/*
@@ -520,7 +586,7 @@ bool st_select_lex_unit::exec()
(select_limit_cnt == HA_POS_ERROR || sl->braces) ?
sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
- saved_error= sl->join->optimize();
+ saved_error= sl->join->optimize_after_locking();
}
if (!saved_error)
{
@@ -615,7 +681,8 @@ bool st_select_lex_unit::exec()
global_parameters->order_list.first,
NULL, NULL, NULL,
fake_select_lex->options | SELECT_NO_UNLOCK,
- result, this, fake_select_lex);
+ result, this, fake_select_lex, NULL, true, NULL,
+ NULL);
}
else
{
@@ -638,7 +705,8 @@ bool st_select_lex_unit::exec()
global_parameters->order_list.first,
NULL, NULL, NULL,
fake_select_lex->options | SELECT_NO_UNLOCK,
- result, this, fake_select_lex);
+ result, this, fake_select_lex, NULL, true,
+ NULL, NULL);
}
else
{
=== modified file 'sql/sql_union.h'
--- a/sql/sql_union.h 2010-07-02 02:58:51 +0000
+++ b/sql/sql_union.h 2011-02-21 16:27:53 +0000
@@ -24,8 +24,10 @@ struct LEX;
typedef class st_select_lex_unit SELECT_LEX_UNIT;
-bool mysql_union(THD *thd, LEX *lex, select_result *result,
- SELECT_LEX_UNIT *unit, ulong setup_tables_done_option);
+bool mysql_union(THD *thd, select_result *result,
+ SELECT_LEX_UNIT *unit, ulong setup_tables_done_option,
+ TABLE_LIST* tables_to_open_and_lock, bool open_tables_is_done,
+ uint *tables_to_lock, MDL_savepoint *open_mdl_savepoint);
#endif /* SQL_UNION_INCLUDED */
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2010-12-29 00:38:59 +0000
+++ b/sql/sql_update.cc 2011-02-21 16:27:53 +0000
@@ -293,20 +293,9 @@ int mysql_update(THD *thd,
/* convert to multiupdate */
DBUG_RETURN(2);
}
- if (lock_tables(thd, table_list, table_count, 0))
- DBUG_RETURN(1);
-
if (mysql_handle_derived(thd->lex, &mysql_derived_prepare))
DBUG_RETURN(1);
- if (thd->fill_derived_tables() &&
- mysql_handle_derived(thd->lex, &mysql_derived_filling))
- {
- mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
- DBUG_RETURN(1);
- }
- mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
-
thd_proc_info(thd, "init");
table= table_list->table;
@@ -398,6 +387,18 @@ int mysql_update(THD *thd,
DBUG_RETURN(0);
}
#endif
+
+ if (lock_tables(thd, table_list, table_count, 0))
+ DBUG_RETURN(1);
+
+ if (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_filling))
+ {
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ DBUG_RETURN(1);
+ }
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+
/* Update the table->file->stats.records number */
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
@@ -1244,6 +1245,7 @@ bool mysql_multi_update(THD *thd,
List<Item> total_list;
+ /* TODO: handle open and lock tables in mysql_select instead */
res= mysql_select(thd, &select_lex->ref_pointer_array,
table_list, select_lex->with_wild,
total_list,
@@ -1251,7 +1253,7 @@ bool mysql_multi_update(THD *thd,
(ORDER *)NULL,
options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
OPTION_SETUP_TABLES_DONE,
- *result, unit, select_lex);
+ *result, unit, select_lex, NULL, true, NULL, NULL);
DBUG_PRINT("info",("res: %d report_error: %d", res, (int) thd->is_error()));
res|= thd->is_error();
=== modified file 'sql/table.h'
--- a/sql/table.h 2011-02-02 22:02:29 +0000
+++ b/sql/table.h 2011-02-21 16:27:53 +0000
@@ -44,6 +44,7 @@ struct TABLE_LIST;
class ACL_internal_schema_access;
class ACL_internal_table_access;
class Field;
+class JOIN; /* hack for proof of concept WL#4443 */
/*
Used to identify NESTED_JOIN structures within a join (applicable only to
@@ -1528,6 +1529,12 @@ struct TABLE_LIST
derived tables. Use TABLE_LIST::is_anonymous_derived_table().
*/
st_select_lex_unit *derived; /* SELECT_LEX_UNIT of derived table */
+ /*
+ TODO: check if this is necessary, maybe derived->first_select()->join
+ can be used?
+ */
+ JOIN *derived_join;
+ bool derived_free_join;
ST_SCHEMA_TABLE *schema_table; /* Information_schema table */
st_select_lex *schema_select_lex;
/*
Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20110221162753-xewli7mr2zm72wfw.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (mattias.jonsson:3692) WL#4443 | Mattias Jonsson | 21 Feb |
