3449 Tor Didriksen 2012-01-05 [merge]
Merge opt-backporting => opt-team
modified:
mysql-test/r/distinct.result
mysql-test/r/group_by.result
mysql-test/r/having.result
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/join.result
mysql-test/r/join_outer.result
mysql-test/r/join_outer_bka.result
mysql-test/r/join_outer_bka_nixbnl.result
mysql-test/r/myisam_icp.result
mysql-test/r/myisam_icp_all.result
mysql-test/r/myisam_icp_none.result
mysql-test/suite/opt_trace/include/general2.inc
mysql-test/suite/opt_trace/r/general2_no_prot.result
mysql-test/suite/opt_trace/r/general2_ps_prot.result
mysql-test/suite/opt_trace/r/temp_table.result
mysql-test/t/distinct.test
mysql-test/t/group_by.test
mysql-test/t/having.test
mysql-test/t/join.test
mysql-test/t/join_outer.test
sql/field.h
sql/field_conv.cc
sql/item_sum.cc
sql/opt_sum.cc
sql/sql_base.cc
sql/sql_executor.cc
sql/sql_lex.cc
sql/sql_optimizer.cc
sql/sql_parse.cc
sql/sql_planner.cc
sql/sql_select.cc
sql/sql_tmp_table.cc
sql/table.cc
sql/table.h
storage/myisam/mi_create.c
unittest/gunit/bounded_queue-t.cc
unittest/gunit/bounds_checked_array-t.cc
unittest/gunit/cost_estimate-t.cc
unittest/gunit/dbug-t.cc
unittest/gunit/dynarray-t.cc
unittest/gunit/field-t.cc
unittest/gunit/filesort_buffer-t.cc
unittest/gunit/get_diagnostics-t.cc
unittest/gunit/gunit_test_main.cc
unittest/gunit/item-t.cc
unittest/gunit/mdl-t.cc
unittest/gunit/mdl_mytap-t.cc
unittest/gunit/my_regex-t.cc
unittest/gunit/opt_range-t.cc
unittest/gunit/opt_trace-t.cc
unittest/gunit/sql_list-t.cc
unittest/gunit/sql_plist-t.cc
unittest/gunit/stdcxx-t.cc
unittest/gunit/tap_event_listener.cc
unittest/gunit/test_utils.cc
unittest/gunit/thread_utils-t.cc
unittest/gunit/thread_utils.cc
3448 Tor Didriksen 2012-01-05 [merge]
Empty merge opt-backporting => opt-team
3447 Tor Didriksen 2012-01-05 [merge]
Empty merge opt-backporting => opt-team
3446 Tor Didriksen 2012-01-05 [merge]
NULL merge opt-backporting => opt-team
3445 Tor Didriksen 2012-01-05 [merge]
Automerge opt-backporting => opt-team
modified:
mysql-test/r/select_all_bka.result
mysql-test/r/select_all_bka_nixbnl.result
3444 Tor Didriksen 2012-01-05 [merge]
Empty merge opt-backporting => opt-team
3443 Tor Didriksen 2012-01-05 [merge]
Empty merge opt-backporting => opt-team
3442 Tor Didriksen 2012-01-05 [merge]
Merge opt-backporting => opt-team, reverted, copied result files
modified:
mysql-test/r/innodb_explain_non_select_all.result
mysql-test/r/innodb_icp_all.result
mysql-test/r/innodb_mrr_all.result
mysql-test/r/innodb_mrr_cost_all.result
3441 Tor Didriksen 2012-01-05 [merge]
Automerge opt-backporting => opt-team
3440 Tor Didriksen 2011-12-16 [merge]
merge trunk => opt-team
modified:
mysql-test/suite/binlog/t/binlog_index.test
mysql-test/suite/rpl/t/rpl_binlog_errors.test
=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result 2011-11-02 12:47:31 +0000
+++ b/mysql-test/r/distinct.result 2012-01-04 13:53:33 +0000
@@ -826,3 +826,62 @@ ORDER BY col_time_key
LIMIT 3;
col_int_key
DROP TABLE t1;
+#
+# BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN +
+# DISTINCT OR ORDER BY
+#
+CREATE TABLE t1 (
+a INT,
+b INT NOT NULL
+);
+INSERT INTO t1 VALUES (1,2), (3,3);
+
+EXPLAIN SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN
+(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect
+ON t1.a = subselect.b
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+2 DERIVED it_a ALL NULL NULL NULL NULL 2
+2 DERIVED it_b ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN
+(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect
+ON t1.a = subselect.b
+;
+b
+3
+NULL
+DROP TABLE t1;
+#
+# BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN +
+# LIMIT + MIX OF MYISAM AND INNODB
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+CREATE TABLE t2 (b INT);
+CREATE TABLE t3 (
+a INT,
+b INT,
+PRIMARY KEY (b)
+);
+INSERT INTO t3 VALUES (2001,1), (2007,2);
+EXPLAIN SELECT DISTINCT t3.a AS t3_date
+FROM t1
+LEFT JOIN t2 ON false
+LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found
+1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Using temporary
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Distinct
+SELECT DISTINCT t3.a AS t3_date
+FROM t1
+LEFT JOIN t2 ON false
+LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+t3_date
+NULL
+DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/group_by.result 2012-01-05 10:17:03 +0000
@@ -2204,3 +2204,65 @@ field1 field2
1 3
DROP VIEW v1;
DROP TABLE t1;
+#
+# Bug#13422961: WRONG RESULTS FROM SELECT WITH AGGREGATES AND
+# IMPLICIT GROUPING + MYISAM OR MEM
+#
+CREATE TABLE it (
+pk INT NOT NULL,
+col_int_nokey INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE TABLE ot (
+pk int(11) NOT NULL,
+col_int_nokey int(11) NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO ot VALUES (10,8);
+
+SELECT col_int_nokey, MAX( pk )
+FROM ot
+WHERE (8, 1) IN ( SELECT pk, COUNT( col_int_nokey ) FROM it );
+col_int_nokey MAX( pk )
+NULL NULL
+
+DROP TABLE it,ot;
+#
+# Bug#13430588: WRONG RESULT FROM IMPLICITLY GROUPED QUERY WITH
+# CONST TABLE AND NO MATCHING ROWS
+#
+CREATE TABLE t1 (i INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (j INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+
+SELECT i, j, COUNT(i) FROM t1 JOIN t2 WHERE j=3;
+i j COUNT(i)
+NULL NULL 0
+
+DROP TABLE t1,t2;
+#
+# BUG#13541761: WRONG RESULTS ON CORRELATED SUBQUERY +
+# AGGREGATE FUNCTION + MYISAM OR MEMORY
+#
+CREATE TABLE t1 (
+a varchar(1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('a'), ('b');
+CREATE TABLE t2 (
+a varchar(1),
+b int(11)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('a',1);
+
+EXPLAIN SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX
+FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
+SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX
+FROM t1;
+MAX
+NULL
+1
+DROP TABLE t1,t2;
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result 2011-11-25 14:07:13 +0000
+++ b/mysql-test/r/having.result 2011-12-20 12:04:31 +0000
@@ -633,4 +633,24 @@ HAVING x < 2;
x
DROP TABLE it,ot;
DROP FUNCTION f;
+#
+# Bug#11760517: MIN/MAX FAILS TO EVALUATE HAVING CONDITION,
+# RETURNS INCORRECT NULL RESULT
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, i4 INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1;
+MIN(table1.i4) min_pk
+NULL NULL
+
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1
+HAVING min_pk <= 10;
+MIN(table1.i4) min_pk
+
+DROP TABLE t1;
End of 5.6 tests
=== modified file 'mysql-test/r/innodb_explain_non_select_all.result'
--- a/mysql-test/r/innodb_explain_non_select_all.result 2011-12-15 12:13:58 +0000
+++ b/mysql-test/r/innodb_explain_non_select_all.result 2012-01-05 10:12:47 +0000
@@ -30,12 +30,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
Handler_update 3
@@ -69,13 +69,13 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_delete 3
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
DROP TABLE t1;
@@ -108,13 +108,13 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_delete 1
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
DROP TABLE t1;
@@ -152,12 +152,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 8
# Status of testing query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 8
Handler_update 1
@@ -185,10 +185,6 @@ id select_type table type possible_keys
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_first 1
-Handler_read_key 2
-Handler_read_rnd_next 4
-Handler_write 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1;
@@ -200,20 +196,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` = 1)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_first 1
-Handler_read_key 2
-Handler_read_rnd_next 4
-Handler_write 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 12
Handler_write 3
# Status of testing query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 12
Handler_update 1
Handler_write 3
@@ -253,12 +245,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 5
# Status of testing query execution:
Variable_name Value
Handler_read_first 4
-Handler_read_key 8
+Handler_read_key 4
Handler_read_rnd_next 7
Handler_update 3
@@ -300,12 +292,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 8
# Status of testing query execution:
Variable_name Value
Handler_read_first 4
-Handler_read_key 8
+Handler_read_key 4
Handler_read_rnd_next 11
Handler_update 2
@@ -347,12 +339,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 3
-Handler_read_key 6
+Handler_read_key 3
Handler_read_rnd_next 12
# Status of testing query execution:
Variable_name Value
Handler_read_first 5
-Handler_read_key 10
+Handler_read_key 5
Handler_read_rnd_next 15
Handler_update 2
@@ -380,10 +372,6 @@ id select_type table type possible_keys
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_first 1
-Handler_read_key 2
-Handler_read_rnd_next 4
-Handler_write 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12;
@@ -395,20 +383,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_first 1
-Handler_read_key 2
-Handler_read_rnd_next 4
-Handler_write 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 12
Handler_write 3
# Status of testing query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 8
+Handler_read_key 5
Handler_read_rnd 3
Handler_read_rnd_next 25
Handler_update 3
@@ -458,13 +442,13 @@ Handler_write 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 5
Handler_write 1
# Status of testing query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 6
+Handler_read_key 4
Handler_read_rnd 3
Handler_read_rnd_next 9
Handler_update 3
@@ -494,10 +478,6 @@ id select_type table type possible_keys
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_first 1
-Handler_read_key 2
-Handler_read_rnd_next 4
-Handler_write 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1;
@@ -509,20 +489,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` > 1)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_first 1
-Handler_read_key 2
-Handler_read_rnd_next 4
-Handler_write 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 12
Handler_write 3
# Status of testing query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 16
Handler_update 2
Handler_write 3
@@ -557,13 +533,13 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 2
# Status of testing query execution:
Variable_name Value
Handler_delete 1
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 2
DROP TABLE t1;
@@ -648,7 +624,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range a,b a 5 NULL 1 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 3;
@@ -658,14 +633,14 @@ Warnings:
Note 1003 /* select#1 */ select '3' AS `a`,'3' AS `b` from `test`.`t1` where 1
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 4
+Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 4
+Handler_read_key 1
# Status of testing query execution:
Variable_name Value
Handler_delete 1
-Handler_read_key 4
+Handler_read_key 1
DROP TABLE t1;
#15
@@ -685,7 +660,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range a,b a 5 NULL 1 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 3;
@@ -695,13 +669,12 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` < 3)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 9
+Handler_read_key 1
# Status of testing query execution:
Variable_name Value
-Handler_read_key 5
+Handler_read_key 1
DROP TABLE t1;
#16
@@ -720,7 +693,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
@@ -730,14 +702,13 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 5
+Handler_read_key 1
# Status of testing query execution:
Variable_name Value
-Handler_read_key 4
+Handler_read_key 1
INSERT INTO t1 VALUES (1), (2), (3);
#
@@ -754,7 +725,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
@@ -764,16 +734,15 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 5
+Handler_read_key 1
Handler_read_next 3
# Status of testing query execution:
Variable_name Value
Handler_delete 3
-Handler_read_key 4
+Handler_read_key 1
Handler_read_next 3
DROP TABLE t1;
@@ -794,7 +763,6 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL PRIMARY PRIMARY 4 NULL 4 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1;
@@ -804,16 +772,15 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (@a:=`test`.`t1`.`a`) order by `test`.`t1`.`a` limit 1
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 3
+Handler_read_key 1
# Status of testing query execution:
Variable_name Value
Handler_delete 1
Handler_read_first 1
-Handler_read_key 3
+Handler_read_key 1
DROP TABLE t1;
#18
@@ -834,7 +801,6 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL c,b,a NULL NULL NULL 10 100.00 Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1;
@@ -844,11 +810,10 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 6
+Handler_read_key 1
Handler_read_rnd_next 11
Sort_rows 1
Sort_scan 1
@@ -856,7 +821,7 @@ Sort_scan 1
Variable_name Value
Handler_delete 1
Handler_read_first 1
-Handler_read_key 7
+Handler_read_key 2
Handler_read_rnd 1
Handler_read_rnd_next 11
Sort_rows 10
@@ -888,7 +853,6 @@ id select_type table type possible_keys
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3;
@@ -900,18 +864,17 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`b3` AS `b3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`b3` = `test`.`t1`.`b1`) and (`test`.`t3`.`a3` = `test`.`t2`.`b2`) and (`test`.`t2`.`a2` = `test`.`t1`.`a1`))
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 2
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 12
+Handler_read_key 7
Handler_read_next 3
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_delete 8
Handler_read_first 1
-Handler_read_key 19
+Handler_read_key 12
Handler_read_next 3
Handler_read_rnd 5
Handler_read_rnd_next 4
@@ -951,12 +914,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 8
# Status of testing query execution:
Variable_name Value
Handler_read_first 4
-Handler_read_key 8
+Handler_read_key 4
Handler_read_rnd_next 10
Handler_update 3
@@ -996,13 +959,13 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 6
-Handler_read_key 12
+Handler_read_key 6
Handler_read_rnd_next 30
# Status of testing query execution:
Variable_name Value
Handler_delete 3
Handler_read_first 6
-Handler_read_key 12
+Handler_read_key 6
Handler_read_rnd_next 30
SET @@optimizer_switch= @save_optimizer_switch;
@@ -1037,13 +1000,13 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 12
# Status of testing query execution:
Variable_name Value
Handler_delete 3
Handler_read_first 6
-Handler_read_key 12
+Handler_read_key 6
Handler_read_rnd_next 30
DROP TABLE t1, t2;
@@ -1076,12 +1039,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 6
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 6
Handler_update 5
@@ -1115,13 +1078,13 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 6
# Status of testing query execution:
Variable_name Value
Handler_delete 5
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 6
DROP TABLE t1;
@@ -1147,7 +1110,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a a 15 NULL X X Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1157,17 +1119,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 3
+Handler_read_key 1
Handler_read_next 4
# Status of testing query execution:
Variable_name Value
Handler_delete 5
Handler_read_first 1
-Handler_read_key 3
+Handler_read_key 1
Handler_read_next 4
DROP TABLE t1, t2;
@@ -1201,12 +1162,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
Handler_write 3
@@ -1241,12 +1202,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 4
Handler_write 3
@@ -1312,25 +1273,23 @@ id select_type table type possible_keys
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 5
+Handler_read_key 1
Handler_read_next 4
# Status of testing query execution:
Variable_name Value
Handler_delete 5
-Handler_read_key 4
+Handler_read_key 1
Handler_read_next 4
DROP TABLE t1;
@@ -1356,7 +1315,6 @@ Warning 1713 Cannot use range access on
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
@@ -1370,11 +1328,10 @@ Warning 1713 Cannot use range access on
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 5
Sort_scan 1
@@ -1382,7 +1339,7 @@ Sort_scan 1
Variable_name Value
Handler_delete 5
Handler_read_first 1
-Handler_read_key 9
+Handler_read_key 6
Handler_read_rnd 5
Handler_read_rnd_next 27
Sort_rows 8
@@ -1410,7 +1367,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1420,11 +1376,10 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 1
Sort_scan 1
@@ -1432,7 +1387,7 @@ Sort_scan 1
Variable_name Value
Handler_delete 1
Handler_read_first 1
-Handler_read_key 5
+Handler_read_key 2
Handler_read_rnd 1
Handler_read_rnd_next 27
Sort_rows 1
@@ -1461,7 +1416,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a a 15 NULL X X Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1471,17 +1425,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 3
+Handler_read_key 1
Handler_read_next 4
# Status of testing query execution:
Variable_name Value
Handler_delete 5
Handler_read_first 1
-Handler_read_key 3
+Handler_read_key 1
Handler_read_next 4
DROP TABLE t1, t2;
@@ -1508,7 +1461,6 @@ Warnings:
Warning 1713 Cannot use range access on index 'a' due to type or collation conversion on field 'b'
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1518,11 +1470,10 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 1
Sort_scan 1
@@ -1530,7 +1481,7 @@ Sort_scan 1
Variable_name Value
Handler_delete 1
Handler_read_first 1
-Handler_read_key 5
+Handler_read_key 2
Handler_read_rnd 1
Handler_read_rnd_next 27
Sort_rows 1
@@ -1606,7 +1557,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 4
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
@@ -1616,11 +1566,10 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 4
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 7
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 4
Sort_scan 1
@@ -1628,7 +1577,7 @@ Sort_scan 1
Variable_name Value
Handler_delete 4
Handler_read_first 1
-Handler_read_key 11
+Handler_read_key 5
Handler_read_rnd 4
Handler_read_rnd_next 27
Sort_rows 4
@@ -1656,25 +1605,23 @@ id select_type table type possible_keys
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 5
+Handler_read_key 1
Handler_read_prev 4
# Status of testing query execution:
Variable_name Value
Handler_delete 5
-Handler_read_key 4
+Handler_read_key 1
Handler_read_prev 4
DROP TABLE t1, t2;
@@ -1699,7 +1646,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
@@ -1709,11 +1655,10 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 5
Sort_scan 1
@@ -1721,7 +1666,7 @@ Sort_scan 1
Variable_name Value
Handler_delete 5
Handler_read_first 1
-Handler_read_key 9
+Handler_read_key 6
Handler_read_rnd 5
Handler_read_rnd_next 27
Sort_rows 26
@@ -1750,7 +1695,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a a 6 NULL X X
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
@@ -1760,16 +1704,15 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 3
+Handler_read_key 1
Handler_read_last 1
Handler_read_prev 4
# Status of testing query execution:
Variable_name Value
Handler_delete 5
-Handler_read_key 3
+Handler_read_key 1
Handler_read_last 1
Handler_read_prev 4
@@ -1795,24 +1738,22 @@ id select_type table type possible_keys
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL X X Using where; Using temporary
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL X X Using index condition
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL X X Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 5
+Handler_read_key 1
Handler_read_next 4
# Status of testing query execution:
Variable_name Value
-Handler_read_key 10
+Handler_read_key 6
Handler_read_next 4
Handler_read_rnd 5
Handler_update 5
@@ -1842,7 +1783,6 @@ Warning 1713 Cannot use range access on
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
@@ -1856,18 +1796,17 @@ Warning 1713 Cannot use range access on
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 5
Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 9
+Handler_read_key 6
Handler_read_rnd 5
Handler_read_rnd_next 27
Handler_update 5
@@ -1896,7 +1835,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1906,18 +1844,17 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 1
Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 5
+Handler_read_key 2
Handler_read_rnd 1
Handler_read_rnd_next 27
Handler_update 1
@@ -1947,7 +1884,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a a 15 NULL X X Using where; Using temporary
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1957,16 +1893,15 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 3
+Handler_read_key 1
Handler_read_next 4
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 9
+Handler_read_key 6
Handler_read_next 4
Handler_read_rnd 5
Handler_update 5
@@ -1995,7 +1930,6 @@ Warnings:
Warning 1713 Cannot use range access on index 'a' due to type or collation conversion on field 'b'
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -2005,18 +1939,17 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 1
Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 5
+Handler_read_key 2
Handler_read_rnd 1
Handler_read_rnd_next 27
Handler_update 1
@@ -2092,7 +2025,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 4
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
@@ -2102,18 +2034,17 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 4
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 7
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 4
Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 11
+Handler_read_key 5
Handler_read_rnd 4
Handler_read_rnd_next 27
Handler_update 4
@@ -2142,24 +2073,22 @@ id select_type table type possible_keys
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where; Using temporary
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 5
+Handler_read_key 1
Handler_read_prev 4
# Status of testing query execution:
Variable_name Value
-Handler_read_key 10
+Handler_read_key 6
Handler_read_prev 4
Handler_read_rnd 5
Handler_update 5
@@ -2186,7 +2115,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
@@ -2196,18 +2124,17 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_rnd_next 27
Sort_rows 5
Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 9
+Handler_read_key 6
Handler_read_rnd 5
Handler_read_rnd_next 27
Handler_update 5
@@ -2237,7 +2164,6 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL a a 6 NULL X X Using temporary
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
@@ -2247,15 +2173,14 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 3
+Handler_read_key 1
Handler_read_last 1
Handler_read_prev 4
# Status of testing query execution:
Variable_name Value
-Handler_read_key 9
+Handler_read_key 6
Handler_read_last 1
Handler_read_prev 4
Handler_read_rnd 5
@@ -2285,7 +2210,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range PRIMARY,c1_idx c1_idx 2 NULL 2 100.00 Using where; Using temporary
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
@@ -2295,14 +2219,13 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 5
+Handler_read_key 1
Handler_read_prev 1
# Status of testing query execution:
Variable_name Value
-Handler_read_key 8
+Handler_read_key 3
Handler_read_prev 1
Handler_read_rnd 2
Handler_update 2
@@ -2321,7 +2244,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range PRIMARY,c1_idx c1_idx 2 NULL 2 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
@@ -2331,15 +2253,14 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 5
+Handler_read_key 1
Handler_read_prev 1
# Status of testing query execution:
Variable_name Value
Handler_delete 2
-Handler_read_key 5
+Handler_read_key 1
Handler_read_prev 1
DROP TABLE t1;
@@ -2360,7 +2281,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using temporary
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 34;
@@ -2370,14 +2290,13 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 34)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 2
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_key 4
+Handler_read_key 1
Handler_read_next 2
# Status of testing query execution:
Variable_name Value
-Handler_read_key 7
+Handler_read_key 3
Handler_read_next 2
Handler_read_rnd 2
Handler_update 2
@@ -2416,12 +2335,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_read_first 3
-Handler_read_key 6
+Handler_read_key 3
Handler_read_rnd_next 5
#
@@ -2453,12 +2372,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 4
# Status of testing query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 4
DROP TABLE t1, t2;
@@ -2486,7 +2405,6 @@ Warnings:
Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1;
@@ -2498,17 +2416,16 @@ Note 1276 Field or reference 'test.t1.f1
Note 1003 /* select#1 */ select (/* select#2 */ select max(`test`.`t2`.`f4`) from `test`.`t2` where (`test`.`t2`.`f3` = `test`.`t1`.`f1`)) AS `(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)` from `test`.`t1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 7
+Handler_read_key 3
Handler_read_next 2
Handler_read_rnd_next 3
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 7
+Handler_read_key 3
Handler_read_next 2
Handler_read_rnd_next 3
Handler_update 2
@@ -2582,12 +2499,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 4
+Handler_read_key 2
Handler_read_rnd_next 6
# Status of testing query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 6
+Handler_read_key 3
Handler_read_rnd 1
Handler_read_rnd_next 9
Handler_write 2
@@ -2624,12 +2541,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 3
-Handler_read_key 6
+Handler_read_key 3
Handler_read_rnd_next 9
# Status of testing query execution:
Variable_name Value
Handler_read_first 5
-Handler_read_key 13
+Handler_read_key 7
Handler_read_rnd 2
Handler_read_rnd_next 19
Handler_update 1
@@ -2655,7 +2572,6 @@ id select_type table type possible_keys
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 2
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM v1 WHERE a < 4;
@@ -2665,17 +2581,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 4)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 2
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_next 3
# Status of testing query execution:
Variable_name Value
Handler_delete 3
Handler_read_first 1
-Handler_read_key 4
+Handler_read_key 1
Handler_read_next 3
DROP TABLE t1;
@@ -2702,7 +2617,6 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a;
@@ -2713,17 +2627,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 5
+Handler_read_key 2
Handler_read_rnd_next 11
# Status of testing query execution:
Variable_name Value
Handler_delete 4
Handler_read_first 1
-Handler_read_key 13
+Handler_read_key 9
Handler_read_rnd 4
Handler_read_rnd_next 5
@@ -2751,7 +2664,6 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_key 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a;
@@ -2762,17 +2674,16 @@ Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
-Handler_read_key 5
+Handler_read_key 2
Handler_read_rnd_next 11
# Status of testing query execution:
Variable_name Value
Handler_delete 4
Handler_read_first 1
-Handler_read_key 13
+Handler_read_key 9
Handler_read_rnd 4
Handler_read_rnd_next 5
@@ -2843,12 +2754,12 @@ Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 1
# Status of testing query execution:
Variable_name Value
Handler_read_first 1
-Handler_read_key 2
+Handler_read_key 1
Handler_read_rnd_next 1
DROP TABLE t1, t2;
@@ -2871,53 +2782,41 @@ INSERT INTO t2 VALUES (1), (2), (3);
EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
-2 DEPENDENT SUBQUERY <derived3> system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 3 Using where
3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 DEPENDENT SUBQUERY <derived3> system NULL NULL NULL NULL 1 100.00
+2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 3 100.00 Using where
3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_first 2
-Handler_read_key 3
-Handler_read_rnd_next 5
-Handler_write 1
-Sort_rows 3
-Sort_scan 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 1 100.00 Materialize; Scan
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY <derived3> ref auto_key0 auto_key0 5 test.t1.a 2 100.00 Using index; FirstMatch(t1)
3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where (`test`.`t1`.`a` = `x`.`b`)
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where (`x`.`b` = `test`.`t1`.`a`)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_first 1
-Handler_read_key 3
-Handler_read_rnd_next 4
-Handler_write 1
-Sort_rows 3
-Sort_scan 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
Handler_read_key 5
-Handler_read_rnd_next 12
-Handler_write 2
+Handler_read_rnd_next 8
+Handler_write 1
Sort_rows 3
Sort_scan 1
# Status of testing query execution:
Variable_name Value
-Handler_read_first 3
-Handler_read_key 5
-Handler_read_rnd_next 9
+Handler_read_first 2
+Handler_read_key 2
+Handler_read_rnd_next 13
Handler_update 1
Handler_write 1
Sort_rows 3
@@ -2929,49 +2828,37 @@ Sort_scan 1
#
EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 1 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 3 End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY <derived3> ref auto_key0 auto_key0 5 test.t1.a 2 Using index; FirstMatch(t1)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (Block Nested Loop)
3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 1 100.00 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY <derived3> ref auto_key0 auto_key0 5 test.t1.a 2 100.00 Using index; FirstMatch(t1)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop)
3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_first 1
-Handler_read_key 3
-Handler_read_rnd_next 4
-Handler_write 1
-Sort_rows 3
-Sort_scan 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 1 100.00 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY <derived3> ref auto_key0 auto_key0 5 test.t1.a 2 100.00 Using index; FirstMatch(t1)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop)
3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where (`test`.`t1`.`a` = `x`.`b`)
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where (`x`.`b` = `test`.`t1`.`a`)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
-Handler_read_first 1
-Handler_read_key 3
-Handler_read_rnd_next 4
-Handler_write 1
-Sort_rows 3
-Sort_scan 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 2
Handler_read_key 5
-Handler_read_rnd_next 10
+Handler_read_rnd_next 8
Handler_write 1
Sort_rows 3
Sort_scan 1
@@ -2979,7 +2866,7 @@ Sort_scan 1
Variable_name Value
Handler_read_first 2
Handler_read_key 5
-Handler_read_rnd_next 10
+Handler_read_rnd_next 8
Handler_write 1
Sort_rows 3
Sort_scan 1
@@ -2990,61 +2877,49 @@ Sort_scan 1
#
EXPLAIN UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived4> ALL NULL NULL NULL NULL 1 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY <derived4> ref auto_key0 auto_key0 5 test.t1.a 2 Using index; FirstMatch(t1)
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using join buffer (Block Nested Loop)
4 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 3
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <derived4> ALL NULL NULL NULL NULL 1 100.00 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY <derived4> ref auto_key0 auto_key0 5 test.t1.a 2 100.00 Using index; FirstMatch(t1)
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop)
4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
# Status of EXPLAIN EXTENDED query
Variable_name Value
-Handler_read_first 2
-Handler_read_key 5
-Handler_read_rnd_next 8
-Handler_write 4
-Sort_rows 3
-Sort_scan 1
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <derived4> ALL NULL NULL NULL NULL 1 100.00 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY <derived4> ref auto_key0 auto_key0 5 test.t1.a 2 100.00 Using index; FirstMatch(t1)
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop)
4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`y`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` where (`test`.`t1`.`a` = `x`.`b`)
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`y`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` where (`x`.`b` = `test`.`t1`.`a`)
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
Handler_read_first 2
Handler_read_key 5
Handler_read_rnd_next 8
-Handler_write 4
-Sort_rows 3
-Sort_scan 1
-# Status of "equivalent" SELECT query execution:
-Variable_name Value
-Handler_read_first 3
-Handler_read_key 7
-Handler_read_rnd_next 14
-Handler_write 4
+Handler_write 1
Sort_rows 3
Sort_scan 1
# Status of testing query execution:
Variable_name Value
-Handler_read_first 3
-Handler_read_key 7
-Handler_read_rnd_next 14
-Handler_write 4
+Handler_read_first 2
+Handler_read_key 5
+Handler_read_rnd_next 8
+Handler_write 1
Sort_rows 3
Sort_scan 1
@@ -3063,7 +2938,7 @@ JOIN t1 AS a12 ON a12.c1 = a11.c1
);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 1 100.00
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED a11 index PRIMARY PRIMARY 4 NULL 1 100.00 Using index
3 DERIVED a21 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
3 DERIVED a12 eq_ref PRIMARY PRIMARY 4 test.a11.c1 1 100.00 Using index
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_icp.result 2012-01-03 11:04:14 +0000
@@ -511,9 +511,9 @@ ON table3.col_varchar_key = table2.col_v
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY table2 index col_varchar_key col_varchar_key 9 NULL 19 Using where; Using index
+1 PRIMARY table1 ALL col_int_key NULL NULL NULL 19
+1 PRIMARY table2 range col_varchar_key col_varchar_key 4 NULL 18 Using where; Using index; Using join buffer (Block Nested Loop)
1 PRIMARY table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_key 1 Using where
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 1
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result 2011-10-13 11:00:58 +0000
+++ b/mysql-test/r/innodb_icp_all.result 2012-01-05 10:17:03 +0000
@@ -349,7 +349,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-
ORDER BY ts DESC
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where
DROP TABLE t1;
#
@@ -511,9 +511,9 @@ ON table3.col_varchar_key = table2.col_v
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY table2 index col_varchar_key col_varchar_key 9 NULL 19 Using where; Using index
+1 PRIMARY table1 ALL col_int_key NULL NULL NULL 19
+1 PRIMARY table2 range col_varchar_key col_varchar_key 4 NULL 18 Using where; Using index; Using join buffer (Block Nested Loop)
1 PRIMARY table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_key 1 Using where
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 1
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
@@ -543,7 +543,7 @@ WHERE t1.pk < 317 AND 2 IN (SELECT COUNT
FROM t2)
ORDER BY t1.c2;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where; Using filesort
+1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1
SELECT t1.c1
FROM t1
@@ -628,7 +628,7 @@ PRIMARY KEY (pk)
INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where
SET SESSION optimizer_switch='index_condition_pushdown=off';
SELECT pk, c1 FROM t1 WHERE pk <> 3;
pk c1
@@ -709,8 +709,8 @@ EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition
-1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using join buffer (Block Nested Loop)
SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2012-01-03 11:04:14 +0000
@@ -510,9 +510,9 @@ ON table3.col_varchar_key = table2.col_v
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY table2 index col_varchar_key col_varchar_key 9 NULL 19 Using where; Using index
+1 PRIMARY table1 ALL col_int_key NULL NULL NULL 19
+1 PRIMARY table2 range col_varchar_key col_varchar_key 4 NULL 18 Using where; Using index; Using join buffer (Block Nested Loop)
1 PRIMARY table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_key 1 Using where
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 1
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
=== modified file 'mysql-test/r/innodb_mrr_all.result'
--- a/mysql-test/r/innodb_mrr_all.result 2011-12-01 11:30:35 +0000
+++ b/mysql-test/r/innodb_mrr_all.result 2011-12-16 09:47:20 +0000
@@ -567,7 +567,7 @@ FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t
WHERE t1.pk > 176;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
SELECT STRAIGHT_JOIN t1.c1
FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.pk > 176;
=== modified file 'mysql-test/r/innodb_mrr_cost_all.result'
--- a/mysql-test/r/innodb_mrr_cost_all.result 2011-12-01 11:30:35 +0000
+++ b/mysql-test/r/innodb_mrr_cost_all.result 2011-12-16 09:47:20 +0000
@@ -567,7 +567,7 @@ FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t
WHERE t1.pk > 176;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
SELECT STRAIGHT_JOIN t1.c1
FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.pk > 176;
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result 2011-11-22 10:48:41 +0000
+++ b/mysql-test/r/join.result 2012-01-03 11:04:14 +0000
@@ -1306,3 +1306,42 @@ pk
8
DROP VIEW v1;
DROP TABLE t1;
+#
+# BUG#11752239 - 43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS
+#
+create table t1(c1 int primary key, c2 char(10)) engine=myisam;
+create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+insert into t1 values(1,'a');
+insert into t2 values(1,'a', 1);
+insert into t3 values(1,'a', 1);
+insert into t3 values(2,'b',2);
+insert into t4 values(1,'a', 1);
+insert into t4 values(2,'a', 2);
+insert into t4 values(3,'a', 3);
+insert into t4 values(4,'a', 4);
+insert into t1 values(2,'b');
+insert into t1 values(3,'c');
+EXPLAIN SELECT * FROM t4 JOIN (t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system NULL NULL NULL NULL 1
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t4 JOIN (t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 ALL NULL NULL NULL NULL 4
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+EXPLAIN SELECT * FROM t4 STRAIGHT_JOIN (t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t4 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t4.ref_t1 1
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+drop table t1,t2,t3,t4;
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result 2011-10-17 13:52:27 +0000
+++ b/mysql-test/r/join_outer.result 2012-01-03 11:04:14 +0000
@@ -1807,3 +1807,192 @@ field1
20
drop table t1,t2,t3;
+# Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+col_int int(11) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk pk
+1 NULL
+EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk pk
+1 NULL
+EXPLAIN SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+drop table t1,t2,t3,t4,t5,t6;
=== modified file 'mysql-test/r/join_outer_bka.result'
--- a/mysql-test/r/join_outer_bka.result 2011-10-17 13:52:27 +0000
+++ b/mysql-test/r/join_outer_bka.result 2012-01-03 11:04:14 +0000
@@ -1808,4 +1808,193 @@ field1
20
drop table t1,t2,t3;
+# Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+col_int int(11) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk pk
+1 NULL
+EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk pk
+1 NULL
+EXPLAIN SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop)
+1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+drop table t1,t2,t3,t4,t5,t6;
set optimizer_switch=default;
=== modified file 'mysql-test/r/join_outer_bka_nixbnl.result'
--- a/mysql-test/r/join_outer_bka_nixbnl.result 2011-10-17 13:52:27 +0000
+++ b/mysql-test/r/join_outer_bka_nixbnl.result 2012-01-03 11:04:14 +0000
@@ -1808,4 +1808,193 @@ field1
20
drop table t1,t2,t3;
+# Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+col_int int(11) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk pk
+1 NULL
+EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1
+1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 1
+1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where
+SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk pk
+1 NULL
+EXPLAIN SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1
+1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 1
+1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where
+1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where
+drop table t1,t2,t3,t4,t5,t6;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp.result 2012-01-03 11:04:14 +0000
@@ -509,9 +509,9 @@ ON table3.col_varchar_key = table2.col_v
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
+1 PRIMARY table1 ALL col_int_key NULL NULL NULL 19
+1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result 2011-10-13 11:00:58 +0000
+++ b/mysql-test/r/myisam_icp_all.result 2012-01-03 11:04:14 +0000
@@ -509,9 +509,9 @@ ON table3.col_varchar_key = table2.col_v
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
+1 PRIMARY table1 ALL col_int_key NULL NULL NULL 19
+1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2012-01-03 11:04:14 +0000
@@ -508,9 +508,9 @@ ON table3.col_varchar_key = table2.col_v
ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
+1 PRIMARY table1 ALL col_int_key NULL NULL NULL 19
+1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
=== modified file 'mysql-test/r/select_all_bka.result'
--- a/mysql-test/r/select_all_bka.result 2011-09-07 10:21:50 +0000
+++ b/mysql-test/r/select_all_bka.result 2011-12-16 10:57:51 +0000
@@ -5009,5 +5009,23 @@ avg(distinct(t1.a))
0
DROP TABLE t1;
# End of test BUG#57203
+#
+# Bug#63020: Function "format"'s 'locale' argument is not considered
+# when creating a "view'
+#
+CREATE TABLE t1 (f1 DECIMAL(10,2));
+INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92);
+CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1;
+SHOW CREATE VIEW view_t1;
+View Create View character_set_client collation_connection
+view_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select format(`t1`.`f1`,1,'sk_SK') AS `f1` from `t1` latin1 latin1_swedish_ci
+SELECT * FROM view_t1;
+f1
+11,7
+17 865,3
+12 345 678,9
+DROP TABLE t1;
+DROP VIEW view_t1;
+# End of test BUG#63020
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_all_bka_nixbnl.result'
--- a/mysql-test/r/select_all_bka_nixbnl.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/select_all_bka_nixbnl.result 2011-12-16 10:57:51 +0000
@@ -5009,5 +5009,23 @@ avg(distinct(t1.a))
0
DROP TABLE t1;
# End of test BUG#57203
+#
+# Bug#63020: Function "format"'s 'locale' argument is not considered
+# when creating a "view'
+#
+CREATE TABLE t1 (f1 DECIMAL(10,2));
+INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92);
+CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1;
+SHOW CREATE VIEW view_t1;
+View Create View character_set_client collation_connection
+view_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select format(`t1`.`f1`,1,'sk_SK') AS `f1` from `t1` latin1 latin1_swedish_ci
+SELECT * FROM view_t1;
+f1
+11,7
+17 865,3
+12 345 678,9
+DROP TABLE t1;
+DROP VIEW view_t1;
+# End of test BUG#63020
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/suite/opt_trace/include/general2.inc'
--- a/mysql-test/suite/opt_trace/include/general2.inc 2011-11-08 07:51:49 +0000
+++ b/mysql-test/suite/opt_trace/include/general2.inc 2012-01-03 11:04:14 +0000
@@ -156,9 +156,6 @@ SELECT t1.f2 FROM t1
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
ORDER BY f2;
---echo Maps should say that t2 and t3 depend on t1
---echo (because of straight_join), they don't, this is
---echo bug#11766858 and bug#11752239
--replace_regex /("sort_buffer_size":) [0-9]+/\1 NNN/
select TRACE from information_schema.OPTIMIZER_TRACE;
=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result 2011-11-30 11:36:14 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result 2012-01-03 11:04:14 +0000
@@ -675,9 +675,6 @@ f
f
f
f
-Maps should say that t2 and t3 depend on t1
-(because of straight_join), they don't, this is
-bug#11766858 and bug#11752239
select TRACE from information_schema.OPTIMIZER_TRACE;
TRACE
{
@@ -797,6 +794,7 @@ TRACE
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
+ 0
] /* depends_on_map_bits */
},
{
@@ -805,6 +803,7 @@ TRACE
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
+ 0
] /* depends_on_map_bits */
}
] /* table_dependencies */
@@ -834,9 +833,10 @@ TRACE
{
"database": "test",
"table": "t3",
- "rows": 1,
- "cost": 1,
- "table_type": "system"
+ "table_scan": {
+ "rows": 1,
+ "cost": 2
+ } /* table_scan */
}
] /* rows_estimation */
},
@@ -861,6 +861,45 @@ TRACE
"rest_of_plan": [
{
"database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "rows": 1,
+ "cost": 2.005,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.8147,
+ "rows_for_plan": 2,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "rows": 2,
+ "cost": 2.0099,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 7.6247,
+ "rows_for_plan": 4,
+ "sort_cost": 4,
+ "new_cost_for_plan": 11.625,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
@@ -875,34 +914,15 @@ TRACE
} /* best_access_path */,
"cost_for_plan": 5.2196,
"rows_for_plan": 4,
- "sort_cost": 4,
- "new_cost_for_plan": 9.2196,
- "chosen": true
+ "pruned_by_heuristic": true
}
] /* rest_of_plan */
- },
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "rows": 2,
- "cost": 2.0098,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.4098,
- "rows_for_plan": 2,
- "pruned_by_heuristic": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`t2`.`f2` = 'f')",
+ "original_condition": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
@@ -913,8 +933,13 @@ TRACE
},
{
"database": "test",
+ "table": "t3",
+ "attached": null
+ },
+ {
+ "database": "test",
"table": "t2",
- "attached": "(`test`.`t2`.`f2` = 'f')"
+ "attached": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
@@ -941,6 +966,11 @@ TRACE
},
{
"database": "test",
+ "table": "t3",
+ "access_type": "table_scan"
+ },
+ {
+ "database": "test",
"table": "t2",
"access_type": "table_scan"
}
=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2011-11-30 11:36:14 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2012-01-03 11:04:14 +0000
@@ -703,9 +703,6 @@ f
f
f
f
-Maps should say that t2 and t3 depend on t1
-(because of straight_join), they don't, this is
-bug#11766858 and bug#11752239
select TRACE from information_schema.OPTIMIZER_TRACE;
TRACE
{
@@ -815,6 +812,7 @@ TRACE
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
+ 0
] /* depends_on_map_bits */
},
{
@@ -823,6 +821,7 @@ TRACE
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
+ 0
] /* depends_on_map_bits */
}
] /* table_dependencies */
@@ -852,9 +851,10 @@ TRACE
{
"database": "test",
"table": "t3",
- "rows": 1,
- "cost": 1,
- "table_type": "system"
+ "table_scan": {
+ "rows": 1,
+ "cost": 2
+ } /* table_scan */
}
] /* rows_estimation */
},
@@ -879,6 +879,45 @@ TRACE
"rest_of_plan": [
{
"database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "rows": 1,
+ "cost": 2.005,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.8147,
+ "rows_for_plan": 2,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "rows": 2,
+ "cost": 2.0099,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 7.6247,
+ "rows_for_plan": 4,
+ "sort_cost": 4,
+ "new_cost_for_plan": 11.625,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
@@ -893,34 +932,15 @@ TRACE
} /* best_access_path */,
"cost_for_plan": 5.2196,
"rows_for_plan": 4,
- "sort_cost": 4,
- "new_cost_for_plan": 9.2196,
- "chosen": true
+ "pruned_by_heuristic": true
}
] /* rest_of_plan */
- },
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "rows": 2,
- "cost": 2.0098,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.4098,
- "rows_for_plan": 2,
- "pruned_by_heuristic": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`t2`.`f2` = 'f')",
+ "original_condition": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
@@ -931,8 +951,13 @@ TRACE
},
{
"database": "test",
+ "table": "t3",
+ "attached": null
+ },
+ {
+ "database": "test",
"table": "t2",
- "attached": "(`test`.`t2`.`f2` = 'f')"
+ "attached": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
@@ -959,6 +984,11 @@ TRACE
},
{
"database": "test",
+ "table": "t3",
+ "access_type": "table_scan"
+ },
+ {
+ "database": "test",
"table": "t2",
"access_type": "table_scan"
}
=== modified file 'mysql-test/suite/opt_trace/r/temp_table.result'
--- a/mysql-test/suite/opt_trace/r/temp_table.result 2011-11-21 08:51:57 +0000
+++ b/mysql-test/suite/opt_trace/r/temp_table.result 2012-01-04 09:51:39 +0000
@@ -133,7 +133,7 @@ SELECT uniq, col1 FROM t1 GROUP BY col2,
"key_length": 1040,
"unique_constraint": true,
"location": "disk (MyISAM)",
- "record_format": "fixed"
+ "record_format": "packed"
} /* tmp_table_info */
} /* creating_tmp_table */
},
@@ -153,7 +153,7 @@ SELECT uniq, col1 FROM t1 GROUP BY col2,
"filesort_priority_queue_optimization": {
"limit": 3,
"rows_estimate": 18,
- "row_size": 1043,
+ "row_size": 1042,
"memory_available": 262144,
"chosen": true
} /* filesort_priority_queue_optimization */,
@@ -163,7 +163,7 @@ SELECT uniq, col1 FROM t1 GROUP BY col2,
"rows": 4,
"examined_rows": 8,
"number_of_tmp_files": 0,
- "sort_buffer_size": 4204,
+ "sort_buffer_size": 4200,
"sort_mode": "<sort_key, rowid>"
} /* filesort_summary */
}
@@ -484,7 +484,7 @@ SELECT uniq, col1, col2 FROM t1 GROUP BY
"key_length": 13,
"unique_constraint": false,
"location": "disk (MyISAM)",
- "record_format": "fixed"
+ "record_format": "packed"
} /* tmp_table_info */
} /* converting_tmp_table_to_myisam */
},
@@ -506,7 +506,7 @@ SELECT uniq, col1, col2 FROM t1 GROUP BY
"rows": 16,
"examined_rows": 16,
"number_of_tmp_files": 0,
- "sort_buffer_size": 676,
+ "sort_buffer_size": 650,
"sort_mode": "<sort_key, rowid>"
} /* filesort_summary */
}
=== modified file 'mysql-test/t/distinct.test'
--- a/mysql-test/t/distinct.test 2011-11-02 12:47:31 +0000
+++ b/mysql-test/t/distinct.test 2012-01-04 13:53:33 +0000
@@ -652,3 +652,56 @@ ORDER BY col_time_key
LIMIT 3;
DROP TABLE t1;
+
+--echo #
+--echo # BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN +
+--echo # DISTINCT OR ORDER BY
+--echo #
+
+CREATE TABLE t1 (
+ a INT,
+ b INT NOT NULL
+);
+INSERT INTO t1 VALUES (1,2), (3,3);
+
+let $query=
+SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN
+ (SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect
+ ON t1.a = subselect.b
+;
+
+--echo
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1;
+
+--echo #
+--echo # BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN +
+--echo # LIMIT + MIX OF MYISAM AND INNODB
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+
+CREATE TABLE t2 (b INT);
+
+CREATE TABLE t3 (
+ a INT,
+ b INT,
+ PRIMARY KEY (b)
+);
+INSERT INTO t3 VALUES (2001,1), (2007,2);
+
+let $query=
+SELECT DISTINCT t3.a AS t3_date
+FROM t1
+ LEFT JOIN t2 ON false
+ LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test 2011-11-17 10:09:13 +0000
+++ b/mysql-test/t/group_by.test 2012-01-04 13:53:33 +0000
@@ -1538,3 +1538,74 @@ LIMIT 3;
DROP VIEW v1;
DROP TABLE t1;
+
+--echo #
+--echo # Bug#13422961: WRONG RESULTS FROM SELECT WITH AGGREGATES AND
+--echo # IMPLICIT GROUPING + MYISAM OR MEM
+--echo #
+
+CREATE TABLE it (
+ pk INT NOT NULL,
+ col_int_nokey INT NOT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+CREATE TABLE ot (
+ pk int(11) NOT NULL,
+ col_int_nokey int(11) NOT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO ot VALUES (10,8);
+
+--echo
+SELECT col_int_nokey, MAX( pk )
+FROM ot
+WHERE (8, 1) IN ( SELECT pk, COUNT( col_int_nokey ) FROM it );
+
+--echo
+DROP TABLE it,ot;
+
+--echo #
+--echo # Bug#13430588: WRONG RESULT FROM IMPLICITLY GROUPED QUERY WITH
+--echo # CONST TABLE AND NO MATCHING ROWS
+--echo #
+CREATE TABLE t1 (i INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (j INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+
+--echo
+SELECT i, j, COUNT(i) FROM t1 JOIN t2 WHERE j=3;
+
+--echo
+DROP TABLE t1,t2;
+
+--echo #
+--echo # BUG#13541761: WRONG RESULTS ON CORRELATED SUBQUERY +
+--echo # AGGREGATE FUNCTION + MYISAM OR MEMORY
+--echo #
+
+CREATE TABLE t1 (
+ a varchar(1)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES ('a'), ('b');
+
+CREATE TABLE t2 (
+ a varchar(1),
+ b int(11)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES ('a',1);
+
+let $query=
+SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX
+FROM t1;
+
+--echo
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2;
+
=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test 2011-11-25 14:07:13 +0000
+++ b/mysql-test/t/having.test 2011-12-20 12:04:31 +0000
@@ -667,4 +667,25 @@ HAVING x < 2;
DROP TABLE it,ot;
DROP FUNCTION f;
+--echo #
+--echo # Bug#11760517: MIN/MAX FAILS TO EVALUATE HAVING CONDITION,
+--echo # RETURNS INCORRECT NULL RESULT
+--echo #
+CREATE TABLE t1 (pk INT PRIMARY KEY, i4 INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+
+--echo
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1;
+
+--echo
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1
+HAVING min_pk <= 10;
+
+--echo
+DROP TABLE t1;
+
--echo End of 5.6 tests
=== modified file 'mysql-test/t/join.test'
--- a/mysql-test/t/join.test 2011-11-22 10:48:41 +0000
+++ b/mysql-test/t/join.test 2012-01-03 11:04:14 +0000
@@ -1015,3 +1015,30 @@ eval $query;
DROP VIEW v1;
DROP TABLE t1;
+
+--echo #
+--echo # BUG#11752239 - 43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS
+--echo #
+create table t1(c1 int primary key, c2 char(10)) engine=myisam;
+create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+insert into t1 values(1,'a');
+insert into t2 values(1,'a', 1);
+insert into t3 values(1,'a', 1);
+insert into t3 values(2,'b',2);
+insert into t4 values(1,'a', 1);
+insert into t4 values(2,'a', 2);
+insert into t4 values(3,'a', 3);
+insert into t4 values(4,'a', 4);
+insert into t1 values(2,'b');
+insert into t1 values(3,'c');
+
+let $rest_of_query=(t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+
+eval EXPLAIN SELECT * FROM t4 JOIN $rest_of_query;
+eval EXPLAIN SELECT STRAIGHT_JOIN * FROM t4 JOIN $rest_of_query;
+eval EXPLAIN SELECT * FROM t4 STRAIGHT_JOIN $rest_of_query;
+
+drop table t1,t2,t3,t4;
=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test 2011-10-17 13:52:27 +0000
+++ b/mysql-test/t/join_outer.test 2012-01-03 11:04:14 +0000
@@ -1408,3 +1408,84 @@ FROM t1;
--echo
drop table t1,t2,t3;
+
+--echo # Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+
+CREATE TABLE t1 (
+ pk int(11) NOT NULL,
+ col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+ pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+ pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+ pk int(11) NOT NULL,
+ col_int int(11) DEFAULT NULL,
+ col_int_key int(11) DEFAULT NULL,
+ col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+ col_int int(11) DEFAULT NULL,
+ col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+ col_int_key int(11) DEFAULT NULL,
+ col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+ pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+
+# EXPLAIN of query above (t2 is before t5 in plan)
+
+let $rest_of_query=t6a.pk, t2.pk
+FROM
+ t6 AS t6a
+ LEFT JOIN
+ (
+ t2
+ RIGHT JOIN
+ (
+ (
+ t1
+ LEFT JOIN
+ (
+ t4
+ JOIN
+ t3
+ ON t4.col_int
+ )
+ ON t4.col_int_key = t1.pk
+ )
+ LEFT JOIN
+ (
+ t5
+ JOIN
+ t6 AS t6b
+ ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+ )
+ ON t1.pk = t5.col_int
+ )
+ ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+ AND t5.col_varchar_10_utf8_key = 0
+ )
+ ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+
+eval SELECT STRAIGHT_JOIN $rest_of_query;
+eval EXPLAIN SELECT STRAIGHT_JOIN $rest_of_query;
+
+# right result (same query, just remove STRAIGHT_JOIN):
+
+eval SELECT $rest_of_query;
+eval EXPLAIN SELECT $rest_of_query;
+
+drop table t1,t2,t3,t4,t5,t6;
=== modified file 'sql/field.h'
--- a/sql/field.h 2011-12-15 12:12:14 +0000
+++ b/sql/field.h 2011-12-20 12:04:31 +0000
@@ -642,14 +642,24 @@ public:
inline bool is_null(my_ptrdiff_t row_offset= 0)
{
/*
- If the field is NULLable, it has a valid null_ptr pointer, and its
- NULLity is recorded in the "null_bit" bit of null_ptr[row_offset].
- Otherwise, it can still be NULL, if it belongs to the inner table of an
- outer join and the row is NULL-complemented: that case is recorded in
- TABLE::null_row.
+ The table may have been marked as containing only NULL values
+ for all fields if it is a NULL-complemented row of an OUTER JOIN
+ or if the query is an implicitly grouped query (has aggregate
+ functions but no GROUP BY clause) with no qualifying rows. If
+ this is the case (in which TABLE::null_row is true), the field
+ is considered to be NULL.
+
+ Otherwise, if the field is NULLable, it has a valid null_ptr
+ pointer, and its NULLity is recorded in the "null_bit" bit of
+ null_ptr[row_offset].
*/
- return null_ptr ? (null_ptr[row_offset] & null_bit ? 1 : 0) :
- table->null_row;
+ if (table->null_row)
+ return true;
+
+ if (null_ptr)
+ return (null_ptr[row_offset] & null_bit);
+
+ return false;
}
inline bool is_real_null(my_ptrdiff_t row_offset= 0)
{ return null_ptr ? (null_ptr[row_offset] & null_bit ? 1 : 0) : 0; }
=== modified file 'sql/field_conv.cc'
--- a/sql/field_conv.cc 2011-12-15 12:12:14 +0000
+++ b/sql/field_conv.cc 2012-01-04 13:53:33 +0000
@@ -82,24 +82,8 @@ static void do_field_8(Copy_field *copy)
copy->to_ptr[7]=copy->from_ptr[7];
}
-
static void do_field_to_null_str(Copy_field *copy)
{
- if (*copy->from_null_ptr & copy->from_bit)
- {
- memset(copy->to_ptr, 0, copy->from_length);
- copy->to_null_ptr[0]=1; // Always bit 1
- }
- else
- {
- copy->to_null_ptr[0]=0;
- memcpy(copy->to_ptr,copy->from_ptr,copy->from_length);
- }
-}
-
-
-static void do_outer_field_to_null_str(Copy_field *copy)
-{
if (*copy->null_row ||
(copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit)))
{
@@ -211,21 +195,6 @@ static void do_skip(Copy_field *copy __a
static void do_copy_null(Copy_field *copy)
{
- if (*copy->from_null_ptr & copy->from_bit)
- {
- *copy->to_null_ptr|=copy->to_bit;
- copy->to_field->reset();
- }
- else
- {
- *copy->to_null_ptr&= ~copy->to_bit;
- (copy->do_copy2)(copy);
- }
-}
-
-
-static void do_outer_field_null(Copy_field *copy)
-{
if (*copy->null_row ||
(copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit)))
{
@@ -242,7 +211,7 @@ static void do_outer_field_null(Copy_fie
static void do_copy_not_null(Copy_field *copy)
{
- if (*copy->from_null_ptr & copy->from_bit)
+ if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
{
copy->to_field->set_warning(Sql_condition::WARN_LEVEL_WARN,
WARN_DATA_TRUNCATED, 1);
@@ -263,7 +232,7 @@ static void do_copy_maybe_null(Copy_fiel
static void do_copy_timestamp(Copy_field *copy)
{
- if (*copy->from_null_ptr & copy->from_bit)
+ if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
{
/* Same as in set_field_to_null_with_conversions() */
copy->to_field->set_time();
@@ -275,7 +244,7 @@ static void do_copy_timestamp(Copy_field
static void do_copy_next_number(Copy_field *copy)
{
- if (*copy->from_null_ptr & copy->from_bit)
+ if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
{
/* Same as in set_field_to_null_with_conversions() */
copy->to_field->table->auto_increment_field_not_null= FALSE;
@@ -574,6 +543,7 @@ void Copy_field::set(uchar *to,Field *fr
from_ptr=from->ptr;
to_ptr=to;
from_length=from->pack_length();
+ null_row= &from->table->null_row;
if (from->maybe_null())
{
from_null_ptr=from->null_ptr;
@@ -581,13 +551,7 @@ void Copy_field::set(uchar *to,Field *fr
to_ptr[0]= 1; // Null as default value
to_null_ptr= (uchar*) to_ptr++;
to_bit= 1;
- if (from->table->maybe_null)
- {
- null_row= &from->table->null_row;
- do_copy= do_outer_field_to_null_str;
- }
- else
- do_copy= do_field_to_null_str;
+ do_copy= do_field_to_null_str;
}
else
{
@@ -630,6 +594,7 @@ void Copy_field::set(Field *to,Field *fr
// set up null handling
from_null_ptr=to_null_ptr=0;
+ null_row= &from->table->null_row;
if (from->maybe_null())
{
from_null_ptr= from->null_ptr;
@@ -638,13 +603,7 @@ void Copy_field::set(Field *to,Field *fr
{
to_null_ptr= to->null_ptr;
to_bit= to->null_bit;
- if (from_null_ptr)
- do_copy= do_copy_null;
- else
- {
- null_row= &from->table->null_row;
- do_copy= do_outer_field_null;
- }
+ do_copy= do_copy_null;
}
else
{
=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc 2011-12-15 15:15:37 +0000
+++ b/sql/item_sum.cc 2011-12-20 12:04:31 +0000
@@ -1057,7 +1057,7 @@ void Aggregator_distinct::endup()
{
/*
We don't have a tree only if 'setup()' hasn't been called;
- this is the case of sql_select.cc:return_zero_rows.
+ this is the case of sql_executor.cc:return_zero_rows.
*/
if (tree)
table->field[0]->set_notnull();
=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc 2011-07-21 17:51:38 +0000
+++ b/sql/opt_sum.cc 2011-12-16 14:39:02 +0000
@@ -268,7 +268,7 @@ int opt_sum_query(THD *thd,
TABLE_LIST *embedded;
for (embedded= tl ; embedded; embedded= embedded->embedding)
{
- if (embedded->on_expr)
+ if (embedded->join_cond())
break;
}
if (embedded)
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2011-12-14 12:32:55 +0000
+++ b/sql/sql_base.cc 2011-12-16 14:39:02 +0000
@@ -7914,8 +7914,8 @@ store_top_level_join_columns(THD *thd, T
/* Add a TRUE condition to outer joins that have no common columns. */
if (table_ref_2->outer_join &&
- !table_ref_1->on_expr && !table_ref_2->on_expr)
- table_ref_2->on_expr= new Item_int((longlong) 1,1); /* Always true. */
+ !table_ref_1->join_cond() && !table_ref_2->join_cond())
+ table_ref_2->set_join_cond(new Item_int((longlong) 1,1)); // Always true.
/* Change this table reference to become a leaf for name resolution. */
if (left_neighbor)
@@ -8732,15 +8732,15 @@ int setup_conds(THD *thd, TABLE_LIST *ta
do
{
embedded= embedding;
- if (embedded->on_expr)
+ if (embedded->join_cond())
{
/* Make a join an a expression */
select_lex->resolve_place= st_select_lex::RESOLVE_JOIN_NEST;
select_lex->resolve_nest= embedded;
thd->where="on clause";
- if ((!embedded->on_expr->fixed &&
- embedded->on_expr->fix_fields(thd, &embedded->on_expr)) ||
- embedded->on_expr->check_cols(1))
+ if ((!embedded->join_cond()->fixed &&
+ embedded->join_cond()->fix_fields(thd, embedded->join_cond_ref())) ||
+ embedded->join_cond()->check_cols(1))
goto err_no_arena;
select_lex->cond_count++;
select_lex->resolve_place= st_select_lex::RESOLVE_NONE;
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2011-12-15 09:00:42 +0000
+++ b/sql/sql_executor.cc 2012-01-04 13:53:33 +0000
@@ -43,10 +43,9 @@ using std::max;
using std::min;
static void disable_sorted_access(JOIN_TAB* join_tab);
-static int return_zero_rows(JOIN *join, select_result *res,TABLE_LIST *tables,
- List<Item> &fields, bool send_row,
- ulonglong select_options, const char *info,
- Item *having);
+static void return_zero_rows(JOIN *join, List<Item> &fields);
+static void save_const_null_info(JOIN *join, table_map *save_nullinfo);
+static void restore_const_null_info(JOIN *join, table_map save_nullinfo);
static int do_select(JOIN *join,List<Item> *fields,TABLE *tmp_table,
Procedure *proc);
@@ -197,12 +196,7 @@ JOIN::exec()
if (zero_result_cause)
{
- (void) return_zero_rows(this, result, select_lex->leaf_tables,
- *columns_list,
- send_row_on_empty_set(),
- select_options,
- zero_result_cause,
- having);
+ return_zero_rows(this, *columns_list);
DBUG_VOID_RETURN;
}
@@ -1354,42 +1348,51 @@ static void update_const_equal_items(Ite
}
}
+/**
+ For some reason (impossible WHERE clause etc), the tables cannot
+ possibly contain any rows that will be in the result. This function
+ is used to return with a result based on no matching rows (i.e., an
+ empty result or one row with aggregates calculated without using
+ rows in the case of implicit grouping) before the execution of
+ nested loop join.
-static int
-return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables,
- List<Item> &fields, bool send_row, ulonglong select_options,
- const char *info, Item *having)
+ @param join The join that does not produce a row
+ @param fields Fields in result
+*/
+static void
+return_zero_rows(JOIN *join, List<Item> &fields)
{
DBUG_ENTER("return_zero_rows");
join->join_free();
- if (send_row)
- {
- for (TABLE_LIST *table= tables; table; table= table->next_leaf)
- mark_as_null_row(table->table); // All fields are NULL
- if (having && having->val_int() == 0)
- send_row=0;
- }
- if (!(result->send_result_set_metadata(fields,
- Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)))
+ if (!(join->result->send_result_set_metadata(fields,
+ Protocol::SEND_NUM_ROWS |
+ Protocol::SEND_EOF)))
{
bool send_error= FALSE;
- if (send_row)
+ if (join->send_row_on_empty_set())
{
+ // Mark tables as containing only NULL values
+ for (uint tableno= 0; tableno < join->tables; tableno++)
+ mark_as_null_row((join->join_tab+tableno)->table);
+
+ // Calculate aggregate functions for no rows
List_iterator_fast<Item> it(fields);
Item *item;
while ((item= it++))
- item->no_rows_in_result();
- send_error= result->send_data(fields);
+ item->no_rows_in_result();
+
+ if (!join->having || join->having->val_int())
+ send_error= join->result->send_data(fields);
}
if (!send_error)
- result->send_eof(); // Should be safe
+ join->result->send_eof(); // Should be safe
}
/* Update results for FOUND_ROWS */
join->thd->set_examined_row_count(0);
join->thd->limit_found_rows= 0;
- DBUG_RETURN(0);
+ DBUG_VOID_RETURN;
}
/**
@@ -1542,12 +1545,33 @@ do_select(JOIN *join,List<Item> *fields,
}
else if (join->send_row_on_empty_set())
{
+ table_map save_nullinfo= 0;
+ /*
+ If this is a subquery, we need to save and later restore
+ the const table NULL info before clearing the tables
+ because the following executions of the subquery do not
+ reevaluate constant fields. @see save_const_null_info
+ and restore_const_null_info
+ */
+ if (join->select_lex->master_unit()->item && join->const_tables)
+ save_const_null_info(join, &save_nullinfo);
+
+ // Mark tables as containing only NULL values
+ join->clear();
+
+ // Calculate aggregate functions for no rows
+ List<Item> *columns_list= (procedure ? &join->procedure_fields_list :
+ fields);
+ List_iterator_fast<Item> it(*columns_list);
+ Item *item;
+ while ((item= it++))
+ item->no_rows_in_result();
+
if (!join->having || join->having->val_int())
- {
- List<Item> *columns_list= (procedure ? &join->procedure_fields_list :
- fields);
rc= join->result->send_data(*columns_list);
- }
+
+ if (save_nullinfo)
+ restore_const_null_info(join, save_nullinfo);
}
/*
An error can happen when evaluating the conds
@@ -2640,8 +2664,8 @@ join_read_const_table(JOIN_TAB *tab, POS
do
{
embedded= embedding;
- if (embedded->on_expr)
- update_const_equal_items(embedded->on_expr, tab);
+ if (embedded->join_cond())
+ update_const_equal_items(embedded->join_cond(), tab);
embedding= embedded->embedding;
}
while (embedding &&
@@ -3388,12 +3412,25 @@ end_send_group(JOIN *join, JOIN_TAB *joi
}
else
{
- if (!join->first_record)
- {
+ table_map save_nullinfo= 0;
+ if (!join->first_record)
+ {
+ /*
+ If this is a subquery, we need to save and later restore
+ the const table NULL info before clearing the tables
+ because the following executions of the subquery do not
+ reevaluate constant fields. @see save_const_null_info
+ and restore_const_null_info
+ */
+ if (join->select_lex->master_unit()->item && join->const_tables)
+ save_const_null_info(join, &save_nullinfo);
+
+ // Mark tables as containing only NULL values
+ join->clear();
+
+ // Calculate aggregate functions for no rows
List_iterator_fast<Item> it(*join->fields);
Item *item;
- /* No matching rows for group function */
- join->clear();
while ((item= it++))
item->no_rows_in_result();
@@ -3411,6 +3448,9 @@ end_send_group(JOIN *join, JOIN_TAB *joi
if (join->rollup_send_data((uint) (idx+1)))
error= 1;
}
+ if (save_nullinfo)
+ restore_const_null_info(join, save_nullinfo);
+
}
if (error > 0)
DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
@@ -3669,11 +3709,34 @@ end_write_group(JOIN *join, JOIN_TAB *jo
int send_group_parts= join->send_group_parts;
if (idx < send_group_parts)
{
- if (!join->first_record)
- {
- /* No matching rows for group function */
- join->clear();
- }
+ table_map save_nullinfo= 0;
+ if (!join->first_record)
+ {
+ // Dead code or we need a test case for this branch
+ DBUG_ASSERT(false);
+ /*
+ If this is a subquery, we need to save and later restore
+ the const table NULL info before clearing the tables
+ because the following executions of the subquery do not
+ reevaluate constant fields. @see save_const_null_info
+ and restore_const_null_info
+ */
+ if (join->select_lex->master_unit()->item && join->const_tables)
+ save_const_null_info(join, &save_nullinfo);
+
+ // Mark tables as containing only NULL values
+ join->clear();
+
+ // Calculate aggregate functions for no rows
+ List<Item> *columns_list= (join->procedure ?
+ &join->procedure_fields_list :
+ join->fields);
+ List_iterator_fast<Item> it(*columns_list);
+ Item *item;
+ while ((item= it++))
+ item->no_rows_in_result();
+
+ }
copy_sum_funcs(join->sum_funcs,
join->sum_funcs_end[send_group_parts]);
if (!join->having || join->having->val_int())
@@ -3691,6 +3754,9 @@ end_write_group(JOIN *join, JOIN_TAB *jo
if (join->rollup_write_data((uint) (idx+1), table))
DBUG_RETURN(NESTED_LOOP_ERROR);
}
+ if (save_nullinfo)
+ restore_const_null_info(join, save_nullinfo);
+
if (end_of_records)
DBUG_RETURN(NESTED_LOOP_OK);
}
@@ -4705,6 +4771,79 @@ change_refs_to_tmp_fields(THD *thd, Ref_
/**
+ Save NULL-row info for constant tables. Used in conjunction with
+ restore_const_null_info() to restore constant table null_row and
+ status values after temporarily marking rows as NULL. This is only
+ done for const tables in subqueries because these values are not
+ recalculated on next execution of the subquery.
+
+ @param join The join for which const tables are about to be
+ marked as containing only NULL values
+ @param[out] save_nullinfo Const tables that have null_row=false and
+ STATUS_NULL_ROW set are tagged in this
+ table_map so that the value can be
+ restored by restore_const_null_info()
+
+ @see mark_as_null_row
+ @see restore_const_null_info
+*/
+static void save_const_null_info(JOIN *join, table_map *save_nullinfo)
+{
+ DBUG_ASSERT(join->const_tables);
+
+ for (uint tableno= 0; tableno < join->const_tables; tableno++)
+ {
+ TABLE *tbl= (join->join_tab+tableno)->table;
+ /*
+ tbl->status and tbl->null_row must be in sync: either both set
+ or none set. Otherwise, an additional table_map parameter is
+ needed to save/restore_const_null_info() these separately
+ */
+ DBUG_ASSERT(tbl->null_row ? (tbl->status & STATUS_NULL_ROW) :
+ !(tbl->status & STATUS_NULL_ROW));
+
+ if (!tbl->null_row)
+ *save_nullinfo|= tbl->map;
+ }
+}
+
+/**
+ Restore NULL-row info for constant tables. Used in conjunction with
+ save_const_null_info() to restore constant table null_row and status
+ values after temporarily marking rows as NULL. This is only done for
+ const tables in subqueries because these values are not recalculated
+ on next execution of the subquery.
+
+ @param join The join for which const tables have been
+ marked as containing only NULL values
+ @param save_nullinfo Const tables that had null_row=false and
+ STATUS_NULL_ROW set when
+ save_const_null_info() was called
+
+ @see mark_as_null_row
+ @see save_const_null_info
+*/
+static void restore_const_null_info(JOIN *join, table_map save_nullinfo)
+{
+ DBUG_ASSERT(join->const_tables && save_nullinfo);
+
+ for (uint tableno= 0; tableno < join->const_tables; tableno++)
+ {
+ TABLE *tbl= (join->join_tab+tableno)->table;
+ if ((save_nullinfo & tbl->map))
+ {
+ /*
+ The table had null_row=false and STATUS_NULL_ROW set when
+ save_const_null_info was called
+ */
+ tbl->null_row= false;
+ tbl->status&= ~STATUS_NULL_ROW;
+ }
+ }
+}
+
+
+/**
@} (end of group Query_Executor)
*/
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2011-12-16 11:20:13 +0000
+++ b/sql/sql_lex.cc 2012-01-05 10:17:03 +0000
@@ -2361,10 +2361,10 @@ static void print_table_array(THD *thd,
else
str->append(STRING_WITH_LEN(" join "));
curr->print(thd, str, query_type); // Print table
- if (curr->on_expr) // Print join condition
+ if (curr->join_cond()) // Print join condition
{
str->append(STRING_WITH_LEN(" on("));
- curr->on_expr->print(str, query_type);
+ curr->join_cond()->print(str, query_type);
str->append(')');
}
}
@@ -3525,10 +3525,10 @@ static void fix_prepare_info_in_table_li
{
for (; tbl; tbl= tbl->next_local)
{
- if (tbl->on_expr)
+ if (tbl->join_cond())
{
- tbl->prep_on_expr= tbl->on_expr;
- tbl->on_expr= tbl->on_expr->copy_andor_structure(thd);
+ tbl->prep_join_cond= tbl->join_cond();
+ tbl->set_join_cond(tbl->join_cond()->copy_andor_structure(thd));
}
fix_prepare_info_in_table_list(thd, tbl->merge_underlying_list);
}
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2011-12-16 11:20:13 +0000
+++ b/sql/sql_optimizer.cc 2012-01-05 10:17:03 +0000
@@ -266,7 +266,7 @@ JOIN::optimize()
*/
if (!tbl->embedding)
{
- Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
+ Item *prune_cond= tbl->join_cond()? tbl->join_cond() : conds;
tbl->table->no_partitions_used= prune_partitions(thd, tbl->table,
prune_cond);
}
@@ -1660,17 +1660,18 @@ static Item *build_equal_items(THD *thd,
while ((table= li++))
{
- if (table->on_expr)
+ if (table->join_cond())
{
List<TABLE_LIST> *nested_join_list= table->nested_join ?
&table->nested_join->join_list : NULL;
/*
- We can modify table->on_expr because its old value will
+ We can modify table->join_cond() because its old value will
be restored before re-execution of PS/SP.
*/
- table->on_expr= build_equal_items(thd, table->on_expr, inherited,
- nested_join_list,
- &table->cond_equal);
+ table->set_join_cond(build_equal_items(thd, table->join_cond(),
+ inherited,
+ nested_join_list,
+ &table->cond_equal));
}
}
}
@@ -2292,9 +2293,9 @@ simplify_joins(JOIN *join, List<TABLE_LI
If the element of join_list is a nested join apply
the procedure to its nested join list first.
*/
- if (table->on_expr)
+ if (table->join_cond())
{
- Item *expr= table->on_expr;
+ Item *expr= table->join_cond();
/*
If an on expression E is attached to the table,
check all null rejected predicates in this expression.
@@ -2308,12 +2309,12 @@ simplify_joins(JOIN *join, List<TABLE_LI
&expr, changelog))
DBUG_RETURN(true);
- if (!table->prep_on_expr || expr != table->on_expr)
+ if (!table->prep_join_cond || expr != table->join_cond())
{
DBUG_ASSERT(expr);
- table->on_expr= expr;
- if (!(table->prep_on_expr= expr->copy_andor_structure(join->thd)))
+ table->set_join_cond(expr);
+ if (!(table->prep_join_cond= expr->copy_andor_structure(join->thd)))
DBUG_RETURN(true);
}
}
@@ -2327,8 +2328,8 @@ simplify_joins(JOIN *join, List<TABLE_LI
}
else
{
- if (!table->prep_on_expr)
- table->prep_on_expr= table->on_expr;
+ if (!table->prep_join_cond)
+ table->prep_join_cond= table->join_cond();
used_tables= table->table->map;
if (conds)
not_null_tables= conds->not_null_tables();
@@ -2351,14 +2352,14 @@ simplify_joins(JOIN *join, List<TABLE_LI
*changelog|= OUTER_JOIN_TO_INNER;
table->outer_join= 0;
}
- if (table->on_expr)
+ if (table->join_cond())
{
*changelog|= JOIN_COND_TO_WHERE;
/* Add ON expression to the WHERE or upper-level ON condition. */
if (conds)
{
Item_cond_and *new_cond=
- static_cast<Item_cond_and*>(and_conds(conds, table->on_expr));
+ static_cast<Item_cond_and*>(and_conds(conds, table->join_cond()));
if (!new_cond)
DBUG_RETURN(true);
conds= new_cond;
@@ -2377,18 +2378,19 @@ simplify_joins(JOIN *join, List<TABLE_LI
on_expr isn't necessarily the second argument anymore,
since fix_fields may have merged it into an existing AND expr.
*/
- if (arg == table->on_expr)
+ if (arg == table->join_cond())
join->thd->
- change_item_tree_place(&table->on_expr, lit.ref());
+ change_item_tree_place(table->join_cond_ref(), lit.ref());
}
}
else
{
- conds= table->on_expr;
+ conds= table->join_cond();
/* In case on_expr has a pending rollback in THD::change_list */
- join->thd->change_item_tree_place(&table->on_expr, &conds);
+ join->thd->change_item_tree_place(table->join_cond_ref(), &conds);
}
- table->prep_on_expr= table->on_expr= 0;
+ table->prep_join_cond= NULL;
+ table->set_join_cond(NULL);
}
}
@@ -2399,9 +2401,9 @@ simplify_joins(JOIN *join, List<TABLE_LI
Only inner tables of non-convertible outer joins
remain with on_expr.
*/
- if (table->on_expr)
+ if (table->join_cond())
{
- table->dep_tables|= table->on_expr->used_tables();
+ table->dep_tables|= table->join_cond()->used_tables();
if (table->embedding)
{
table->dep_tables&= ~table->embedding->nested_join->used_tables;
@@ -2409,7 +2411,8 @@ simplify_joins(JOIN *join, List<TABLE_LI
Embedding table depends on tables used
in embedded on expressions.
*/
- table->embedding->on_expr_dep_tables|= table->on_expr->used_tables();
+ table->embedding->on_expr_dep_tables|=
+ table->join_cond()->used_tables();
}
else
table->dep_tables&= ~table->table->map;
@@ -2420,7 +2423,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
/* The order of tables is reverse: prev_table follows table */
if (prev_table->straight || straight_join)
prev_table->dep_tables|= used_tables;
- if (prev_table->on_expr)
+ if (prev_table->join_cond())
{
prev_table->dep_tables|= table->on_expr_dep_tables;
table_map prev_used_tables= prev_table->nested_join ?
@@ -2437,7 +2440,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
For example it might happen if RAND() function
is used in JOIN ON clause.
*/
- if (!((prev_table->on_expr->used_tables() & ~RAND_TABLE_BIT) &
+ if (!((prev_table->join_cond()->used_tables() & ~RAND_TABLE_BIT) &
~prev_used_tables))
prev_table->dep_tables|= used_tables;
}
@@ -2463,7 +2466,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
DBUG_RETURN(true);
*changelog|= SEMIJOIN;
}
- else if (nested_join && !table->on_expr)
+ else if (nested_join && !table->join_cond())
{
*changelog|= PAREN_REMOVAL;
TABLE_LIST *tbl;
@@ -2472,6 +2475,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
{
tbl->embedding= table->embedding;
tbl->join_list= table->join_list;
+ tbl->dep_tables|= table->dep_tables;
}
li.replace(nested_join->join_list);
}
@@ -2550,7 +2554,7 @@ static uint build_bitmap_for_nested_join
if (nested_join->join_list.elements != 1)
{
/* Don't assign bits to sj-nests */
- if (table->on_expr)
+ if (table->join_cond())
nested_join->nj_map= (nested_join_map) 1 << first_unused++;
first_unused= build_bitmap_for_nested_joins(&nested_join->join_list,
first_unused);
@@ -2738,7 +2742,7 @@ make_join_statistics(JOIN *join, TABLE_L
table->file->stats.records= 2;
table->quick_condition_rows= table->file->stats.records;
- s->on_expr_ref= &tables->on_expr;
+ s->on_expr_ref= tables->join_cond_ref();
if (tables->in_outer_join_nest())
{
@@ -5036,7 +5040,7 @@ static void add_key_fields_for_nj(JOIN *
{
if (table->nested_join)
{
- if (!table->on_expr)
+ if (!table->join_cond())
{
/* It's a semi-join nest. Walk into it as if it wasn't a nest */
have_another= TRUE;
@@ -5047,11 +5051,11 @@ static void add_key_fields_for_nj(JOIN *
add_key_fields_for_nj(join, table, end, and_level, sargables);
}
else
- if (!table->on_expr)
+ if (!table->join_cond())
tables |= table->table->map;
}
- if (nested_join_table->on_expr)
- add_key_fields(join, end, and_level, nested_join_table->on_expr, tables,
+ if (nested_join_table->join_cond())
+ add_key_fields(join, end, and_level, nested_join_table->join_cond(), tables,
sargables);
}
@@ -5251,7 +5255,7 @@ add_group_and_distinct_keys(JOIN *join,
@param join_tab Array in tablenr_order
@param tables Number of tables in join
@param cond WHERE condition (note that the function analyzes
- join_tab[i]->on_expr too)
+ join_tab[i]->join_cond() too)
@param normal_tables Tables not inner w.r.t some outer join (ones
for which we can make ref access based the WHERE
clause)
@@ -5528,7 +5532,7 @@ make_outerjoin_info(JOIN *join)
is in the query above.)
*/
tab->last_inner= tab->first_inner= tab;
- tab->on_expr_ref= &tbl->on_expr;
+ tab->on_expr_ref= tbl->join_cond_ref();
tab->cond_equal= tbl->cond_equal;
if (embedding)
tab->first_upper= embedding->nested_join->first_nested;
@@ -5536,7 +5540,7 @@ make_outerjoin_info(JOIN *join)
for ( ; embedding ; embedding= embedding->embedding)
{
/* Ignore sj-nests: */
- if (!embedding->on_expr)
+ if (!embedding->join_cond())
continue;
NESTED_JOIN *nested_join= embedding->nested_join;
if (!nested_join->counter_)
@@ -5546,7 +5550,7 @@ make_outerjoin_info(JOIN *join)
Save reference to it in the nested join structure.
*/
nested_join->first_nested= tab;
- tab->on_expr_ref= &embedding->on_expr;
+ tab->on_expr_ref= embedding->join_cond_ref();
tab->cond_equal= tbl->cond_equal;
if (embedding->embedding)
tab->first_upper= embedding->embedding->nested_join->first_nested;
@@ -5918,9 +5922,9 @@ static void fix_list_after_tbl_changes(s
TABLE_LIST *table;
while ((table= it++))
{
- if (table->on_expr)
- table->on_expr->fix_after_pullout(parent_select, removed_select,
- &table->on_expr);
+ if (table->join_cond())
+ table->join_cond()->fix_after_pullout(parent_select, removed_select,
+ table->join_cond_ref());
if (table->nested_join)
fix_list_after_tbl_changes(parent_select, removed_select,
&table->nested_join->join_list);
@@ -6062,8 +6066,8 @@ static bool convert_subquery_to_semijoin
wrap_nest->outer_join= outer_tbl->outer_join;
outer_tbl->outer_join= 0;
- wrap_nest->on_expr= outer_tbl->on_expr;
- outer_tbl->on_expr= NULL;
+ wrap_nest->set_join_cond(outer_tbl->join_cond());
+ outer_tbl->set_join_cond(NULL);
List_iterator<TABLE_LIST> li(*wrap_nest->join_list);
TABLE_LIST *tbl;
@@ -6257,11 +6261,12 @@ static bool convert_subquery_to_semijoin
if (emb_tbl_nest)
{
/* Inject sj_on_expr into the parent's ON condition */
- emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,
- sj_nest->sj_on_expr);
- if (emb_tbl_nest->on_expr == NULL)
+ emb_tbl_nest->set_join_cond(and_items(emb_tbl_nest->join_cond(),
+ sj_nest->sj_on_expr));
+ if (emb_tbl_nest->join_cond() == NULL)
DBUG_RETURN(TRUE);
- emb_tbl_nest->on_expr->fix_fields(parent_join->thd, &emb_tbl_nest->on_expr);
+ emb_tbl_nest->join_cond()->fix_fields(parent_join->thd,
+ emb_tbl_nest->join_cond_ref());
}
else
{
@@ -6376,7 +6381,7 @@ bool JOIN::flatten_subqueries()
// joins.
for (TABLE_LIST *tbl= select_lex->leaf_tables; tbl; tbl=tbl->next_leaf)
{
- if (tbl->on_expr || tbl->in_outer_join_nest())
+ if (tbl->join_cond() || tbl->in_outer_join_nest())
{
subq= sj_subselects.begin();
arena= thd->activate_stmt_arena_if_needed(&backup);
@@ -6405,7 +6410,7 @@ bool JOIN::flatten_subqueries()
subq++)
{
Item **tree= ((*subq)->embedding_join_nest == NULL) ?
- &conds : &((*subq)->embedding_join_nest->on_expr);
+ &conds : ((*subq)->embedding_join_nest->join_cond_ref());
if (replace_subcondition(this, tree, *subq, new Item_int(1), FALSE))
DBUG_RETURN(TRUE); /* purecov: inspected */
}
@@ -6460,8 +6465,7 @@ skip_conversion:
const bool subquery_in_join_clause= (*subq)->embedding_join_nest != NULL;
Item **tree= subquery_in_join_clause ?
- &((*subq)->embedding_join_nest->on_expr) :
- &conds;
+ ((*subq)->embedding_join_nest->join_cond_ref()) : &conds;
if (replace_subcondition(this, tree, *subq, substitute, do_fix_fields))
DBUG_RETURN(TRUE);
(*subq)->substitution= NULL;
@@ -6470,10 +6474,10 @@ skip_conversion:
{
if (subquery_in_join_clause)
{
- tree= &((*subq)->embedding_join_nest->prep_on_expr);
+ tree= &((*subq)->embedding_join_nest->prep_join_cond);
/*
Some precaution is needed when dealing with PS/SP:
- fix_prepare_info_in_table_list() sets prep_on_expr, but only for
+ fix_prepare_info_in_table_list() sets prep_join_cond, but only for
tables, not for join nest objects. This is instead populated in
simplify_joins(), which is called after this function. The case
where *tree is NULL is handled by this procedure.
@@ -7334,7 +7338,7 @@ static bool make_join_select(JOIN *join,
/*
We can't call sel->cond->fix_fields,
- as it will break tab->on_expr if it's AND condition
+ as it will break tab->join_cond() if it's AND condition
(fix_fields currently removes extra AND/OR levels).
Yet attributes of the just built condition are not needed.
Thus we call sel->cond->quick_fix_field for safety.
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2011-12-09 08:59:22 +0000
+++ b/sql/sql_parse.cc 2011-12-16 14:39:02 +0000
@@ -6644,19 +6644,14 @@ push_new_name_resolution_context(THD *th
@param b the second operand of a JOIN ... ON
@param expr the condition to be added to the ON clause
-
- @retval
- FALSE if there was some error
- @retval
- TRUE if all is OK
*/
void add_join_on(TABLE_LIST *b, Item *expr)
{
if (expr)
{
- if (!b->on_expr)
- b->on_expr= expr;
+ if (!b->join_cond())
+ b->set_join_cond(expr);
else
{
/*
@@ -6664,9 +6659,9 @@ void add_join_on(TABLE_LIST *b, Item *ex
right and left join. If called later, it happens if we add more
than one condition to the ON clause.
*/
- b->on_expr= new Item_cond_and(b->on_expr,expr);
+ b->set_join_cond(new Item_cond_and(b->join_cond(), expr));
}
- b->on_expr->top_level_item();
+ b->join_cond()->top_level_item();
}
}
@@ -6681,7 +6676,7 @@ void add_join_on(TABLE_LIST *b, Item *ex
setup_conds() creates a list of equal condition between all fields
of the same name for NATURAL JOIN or the fields in 'using_fields'
for JOIN ... USING. The list of equality conditions is stored
- either in b->on_expr, or in JOIN::conds, depending on whether there
+ either in b->join_cond(), or in JOIN::conds, depending on whether there
was an outer join.
EXAMPLE
=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc 2011-12-14 12:32:55 +0000
+++ b/sql/sql_planner.cc 2011-12-16 14:39:02 +0000
@@ -3763,7 +3763,7 @@ void Optimize_table_order::backout_nj_sj
for (;last_emb != NULL; last_emb= last_emb->embedding)
{
- if (last_emb->on_expr)
+ if (last_emb->join_cond())
{
NESTED_JOIN *nest= last_emb->nested_join;
DBUG_ASSERT(nest->counter_ > 0);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-12-16 11:20:13 +0000
+++ b/sql/sql_select.cc 2012-01-05 10:17:03 +0000
@@ -3209,18 +3209,6 @@ ORDER *simple_remove_const(ORDER *order,
}
-/*
- used only in JOIN::clear
-*/
-static void clear_tables(JOIN *join)
-{
- /*
- must clear only the non-const tables, as const tables
- are not re-calculated.
- */
- for (uint i=join->const_tables ; i < join->tables ; i++)
- mark_as_null_row(join->all_tables[i]); // All fields are NULL
-}
/*
@@ -4593,7 +4581,9 @@ bool JOIN::rollup_make_fields(List<Item>
void JOIN::clear()
{
- clear_tables(this);
+ for (uint tableno= 0; tableno < this->tables; tableno++)
+ mark_as_null_row((join_tab+tableno)->table);
+
copy_fields(&tmp_table_param);
if (sum_funcs)
=== modified file 'sql/sql_tmp_table.cc'
--- a/sql/sql_tmp_table.cc 2011-12-15 15:15:37 +0000
+++ b/sql/sql_tmp_table.cc 2012-01-04 09:51:39 +0000
@@ -464,8 +464,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
uint temp_pool_slot=MY_BIT_NONE;
uint fieldnr= 0;
ulong reclength, string_total_length;
- bool using_unique_constraint= 0;
- bool use_packed_rows= 0;
+ bool using_unique_constraint= false;
+ bool use_packed_rows= false;
bool not_all_columns= !(select_options & TMP_TABLE_ALL_COLUMNS);
char *tmpname,path[FN_REFLEN];
uchar *pos, *group_buff, *bitmaps;
@@ -527,10 +527,10 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
*/
(*tmp->item)->marker= 4;
if ((*tmp->item)->max_length >= CONVERT_IF_BIGGER_TO_BLOB)
- using_unique_constraint=1;
+ using_unique_constraint= true;
}
if (param->group_length >= MAX_BLOB_WIDTH)
- using_unique_constraint=1;
+ using_unique_constraint= true;
if (group)
distinct=0; // Can't use distinct
}
@@ -750,6 +750,14 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
*blob_field++= fieldnr;
blob_count++;
}
+
+ if (new_field->real_type() == MYSQL_TYPE_STRING ||
+ new_field->real_type() == MYSQL_TYPE_VARCHAR)
+ {
+ string_count++;
+ string_total_length+= new_field->pack_length();
+ }
+
if (item->marker == 4 && item->maybe_null)
{
group_null_items++;
@@ -800,7 +808,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
if (group &&
(param->group_parts > table->file->max_key_parts() ||
param->group_length > table->file->max_key_length()))
- using_unique_constraint=1;
+ using_unique_constraint= true;
}
else
{
@@ -836,7 +844,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
(string_total_length >= STRING_TOTAL_LENGTH_TO_PACK_ROWS &&
(reclength / string_total_length <= RATIO_TO_PACK_ROWS ||
string_total_length / string_count >= AVG_STRING_LENGTH_TO_PACK_ROWS)))
- use_packed_rows= 1;
+ use_packed_rows= true;
if (!use_packed_rows)
share->db_create_options&= ~HA_OPTION_PACK_RECORD;
@@ -956,6 +964,10 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
field->real_type() == MYSQL_TYPE_STRING &&
length >= MIN_STRING_LENGTH_TO_PACK_ROWS)
recinfo->type=FIELD_SKIP_ENDSPACE;
+ else if (use_packed_rows &&
+ field->real_type() == MYSQL_TYPE_VARCHAR &&
+ length >= MIN_STRING_LENGTH_TO_PACK_ROWS)
+ recinfo->type= FIELD_VARCHAR;
else
recinfo->type=FIELD_NORMAL;
if (!--hidden_field_count)
@@ -1732,7 +1744,10 @@ bool create_myisam_tmp_table(TABLE *tabl
start_recinfo,
share->uniques, &uniquedef,
&create_info,
- HA_CREATE_TMP_TABLE)))
+ HA_CREATE_TMP_TABLE |
+ ((share->db_create_options & HA_OPTION_PACK_RECORD) ?
+ HA_PACK_RECORD : 0)
+ )))
{
table->file->print_error(error,MYF(0)); /* purecov: inspected */
table->db_stat=0;
=== modified file 'sql/table.cc'
--- a/sql/table.cc 2011-11-28 07:42:18 +0000
+++ b/sql/table.cc 2011-12-16 14:39:02 +0000
@@ -3741,8 +3741,8 @@ bool TABLE_LIST::prep_where(THD *thd, It
this expression will not be moved to WHERE condition (i.e. will
be clean correctly for PS/SP)
*/
- tbl->on_expr= and_conds(tbl->on_expr,
- where->copy_andor_structure(thd));
+ tbl->set_join_cond(and_conds(tbl->join_cond(),
+ where->copy_andor_structure(thd)));
break;
}
}
@@ -3784,8 +3784,8 @@ merge_on_conds(THD *thd, TABLE_LIST *tab
Item *cond= NULL;
DBUG_PRINT("info", ("alias: %s", table->alias));
- if (table->on_expr)
- cond= table->on_expr->copy_andor_structure(thd);
+ if (table->join_cond())
+ cond= table->join_cond()->copy_andor_structure(thd);
if (!table->nested_join)
DBUG_RETURN(cond);
List_iterator<TABLE_LIST> li(table->nested_join->join_list);
@@ -5446,8 +5446,9 @@ void TABLE_LIST::reinit_before_use(THD *
do
{
embedded= parent_embedding;
- if (embedded->prep_on_expr)
- embedded->on_expr= embedded->prep_on_expr->copy_andor_structure(thd);
+ if (embedded->prep_join_cond)
+ embedded->
+ set_join_cond(embedded->prep_join_cond->copy_andor_structure(thd));
parent_embedding= embedded->embedding;
}
while (parent_embedding &&
=== modified file 'sql/table.h'
--- a/sql/table.h 2011-12-15 12:12:14 +0000
+++ b/sql/table.h 2011-12-20 12:04:31 +0000
@@ -1514,7 +1514,24 @@ struct TABLE_LIST
TABLE_LIST *next_global, **prev_global;
char *db, *alias, *table_name, *schema_table_name;
char *option; /* Used by cache index */
- Item *on_expr; /* Used with outer join */
+
+private:
+ Item *m_join_cond; /* Used with outer join */
+public:
+ Item **join_cond_ref() { return &m_join_cond; }
+ Item *join_cond() { return m_join_cond; }
+ Item *set_join_cond(Item *val)
+ { return m_join_cond= val; }
+ /*
+ The structure of the join condition presented in the member above
+ can be changed during certain optimizations. This member
+ contains a snapshot of AND-OR structure of the join condition
+ made after permanent transformations of the parse tree, and is
+ used to restore the join condition before every reexecution of a prepared
+ statement or stored procedure.
+ */
+ Item *prep_join_cond;
+
Item *sj_on_expr; /* Synthesized semijoin condition */
/*
(Valid only for semi-join nests) Bitmap of tables that are within the
@@ -1526,15 +1543,6 @@ struct TABLE_LIST
Item_exists_subselect *sj_subq_pred;
Semijoin_mat_exec *sj_mat_exec;
- /*
- The structure of ON expression presented in the member above
- can be changed during certain optimizations. This member
- contains a snapshot of AND-OR structure of the ON expression
- made after permanent transformations of the parse tree, and is
- used to restore ON clause before every reexecution of a prepared
- statement or stored procedure.
- */
- Item *prep_on_expr;
COND_EQUAL *cond_equal; /* Used with outer join */
/*
During parsing - left operand of NATURAL/USING join where 'this' is
@@ -2327,7 +2335,6 @@ inline void mark_as_null_row(TABLE *tabl
{
table->null_row=1;
table->status|=STATUS_NULL_ROW;
- memset(table->null_flags, 255, table->s->null_bytes);
}
bool is_simple_order(ORDER *order);
=== modified file 'storage/myisam/mi_create.c'
--- a/storage/myisam/mi_create.c 2011-08-29 12:08:58 +0000
+++ b/storage/myisam/mi_create.c 2012-01-04 09:51:39 +0000
@@ -462,7 +462,6 @@ int mi_create(const char *name,uint keys
key_del[i]=HA_OFFSET_ERROR;
unique_key_parts=0;
- offset=reclength-uniques*MI_UNIQUE_HASH_LENGTH;
for (i=0, uniquedef=uniquedefs ; i < uniques ; i++ , uniquedef++)
{
uniquedef->key=keys+i;
@@ -727,7 +726,7 @@ int mi_create(const char *name,uint keys
#endif
}
/* Create extra keys for unique definitions */
- offset=reclength-uniques*MI_UNIQUE_HASH_LENGTH;
+ offset= real_reclength - uniques * MI_UNIQUE_HASH_LENGTH;
memset(&tmp_keydef, 0, sizeof(tmp_keydef));
memset(&tmp_keyseg, 0, sizeof(tmp_keyseg));
for (i=0; i < uniques ; i++)
=== modified file 'unittest/gunit/bounded_queue-t.cc'
--- a/unittest/gunit/bounded_queue-t.cc 2010-12-23 11:03:09 +0000
+++ b/unittest/gunit/bounded_queue-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
#include <algorithm>
=== modified file 'unittest/gunit/bounds_checked_array-t.cc'
--- a/unittest/gunit/bounds_checked_array-t.cc 2011-08-26 08:16:16 +0000
+++ b/unittest/gunit/bounds_checked_array-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/cost_estimate-t.cc'
--- a/unittest/gunit/cost_estimate-t.cc 2011-11-08 11:37:54 +0000
+++ b/unittest/gunit/cost_estimate-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/dbug-t.cc'
--- a/unittest/gunit/dbug-t.cc 2010-10-18 11:31:18 +0000
+++ b/unittest/gunit/dbug-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/dynarray-t.cc'
--- a/unittest/gunit/dynarray-t.cc 2011-05-18 08:29:46 +0000
+++ b/unittest/gunit/dynarray-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/field-t.cc'
--- a/unittest/gunit/field-t.cc 2011-11-18 11:49:13 +0000
+++ b/unittest/gunit/field-t.cc 2011-12-20 12:04:31 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
@@ -70,6 +69,17 @@ static void compareMysqlTime(const MYSQL
EXPECT_EQ(first.time_type, second.time_type);
}
+class Mock_table : public TABLE
+{
+public:
+ Mock_table(THD *thd)
+ {
+ null_row= false;
+ read_set= 0;
+ in_use= thd;
+ }
+};
+
// A mock Protocol class to be able to test Field::send_binary
// It just verifies that store_time has been passed what is expected
class Mock_protocol : public Protocol
@@ -257,6 +267,8 @@ TEST_F(FieldTest, FieldTimef)
// Not testing store(const char, uint, const CHARSET_INFO *, enum_check_fields)
// it requires a mock table
+ Mock_table m_table(thd());
+ f->table= &m_table;
struct timeval tv;
int warnings= 0;
EXPECT_EQ(0, f->get_timestamp(&tv, &warnings));
=== modified file 'unittest/gunit/filesort_buffer-t.cc'
--- a/unittest/gunit/filesort_buffer-t.cc 2011-11-07 15:32:36 +0000
+++ b/unittest/gunit/filesort_buffer-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/get_diagnostics-t.cc'
--- a/unittest/gunit/get_diagnostics-t.cc 2011-11-23 09:14:10 +0000
+++ b/unittest/gunit/get_diagnostics-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/gunit_test_main.cc'
--- a/unittest/gunit/gunit_test_main.cc 2011-10-11 04:27:52 +0000
+++ b/unittest/gunit/gunit_test_main.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/item-t.cc'
--- a/unittest/gunit/item-t.cc 2011-11-17 13:41:28 +0000
+++ b/unittest/gunit/item-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/mdl-t.cc'
--- a/unittest/gunit/mdl-t.cc 2011-05-19 09:47:59 +0000
+++ b/unittest/gunit/mdl-t.cc 2011-12-20 09:51:05 +0000
@@ -22,8 +22,7 @@
The code below should hopefully be (mostly) self-explanatory.
*/
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/mdl_mytap-t.cc'
--- a/unittest/gunit/mdl_mytap-t.cc 2011-05-19 09:47:59 +0000
+++ b/unittest/gunit/mdl_mytap-t.cc 2011-12-20 09:51:05 +0000
@@ -21,8 +21,7 @@
numbers in case of failures.
*/
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <string>
#include <iostream>
=== modified file 'unittest/gunit/my_regex-t.cc'
--- a/unittest/gunit/my_regex-t.cc 2010-12-23 11:03:09 +0000
+++ b/unittest/gunit/my_regex-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/opt_range-t.cc'
--- a/unittest/gunit/opt_range-t.cc 2011-05-13 09:36:13 +0000
+++ b/unittest/gunit/opt_range-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/opt_trace-t.cc'
--- a/unittest/gunit/opt_trace-t.cc 2011-10-05 13:16:38 +0000
+++ b/unittest/gunit/opt_trace-t.cc 2011-12-20 09:51:05 +0000
@@ -18,6 +18,7 @@
Unit test of the Optimizer trace API (WL#5257)
*/
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
@@ -80,10 +81,15 @@ void do_check_json_compliance(const char
#endif
}
+extern "C"
+void my_error_handler(uint error, const char *str, myf MyFlags);
+
+
class TraceContentTest : public ::testing::Test
{
public:
Opt_trace_context trace;
+ static bool oom; ///< whether we got an OOM error from opt trace
protected:
static void SetUpTestCase()
{
@@ -96,18 +102,20 @@ protected:
// Setting debug flags triggers enter/exit trace, so redirect to /dev/null
DBUG_SET("o," IF_WIN("NUL", "/dev/null"));
}
- static bool oom; ///< whether we got an OOM error from opt trace
- static void my_error_handler(uint error, const char *str, myf MyFlags)
- {
- const uint EE= static_cast<uint>(EE_OUTOFMEMORY);
- EXPECT_EQ(EE, error);
- if (error == EE)
- oom= true;
- }
+
};
+bool TraceContentTest::oom;
+
+
+void my_error_handler(uint error, const char *str, myf MyFlags)
+{
+ const uint EE= static_cast<uint>(EE_OUTOFMEMORY);
+ EXPECT_EQ(EE, error);
+ if (error == EE)
+ TraceContentTest::oom= true;
+}
-bool TraceContentTest::oom;
TEST_F(TraceContentTest, ConstructAndDestruct)
{
=== modified file 'unittest/gunit/sql_list-t.cc'
--- a/unittest/gunit/sql_list-t.cc 2011-10-06 11:06:34 +0000
+++ b/unittest/gunit/sql_list-t.cc 2011-12-20 09:51:05 +0000
@@ -20,8 +20,7 @@
http://code.google.com/p/googletest/wiki/GoogleTestPrimer
*/
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/sql_plist-t.cc'
--- a/unittest/gunit/sql_plist-t.cc 2011-04-13 11:31:44 +0000
+++ b/unittest/gunit/sql_plist-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/stdcxx-t.cc'
--- a/unittest/gunit/stdcxx-t.cc 2011-11-23 09:14:10 +0000
+++ b/unittest/gunit/stdcxx-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/tap_event_listener.cc'
--- a/unittest/gunit/tap_event_listener.cc 2011-06-30 15:50:45 +0000
+++ b/unittest/gunit/tap_event_listener.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/test_utils.cc'
--- a/unittest/gunit/test_utils.cc 2011-07-28 10:54:44 +0000
+++ b/unittest/gunit/test_utils.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/thread_utils-t.cc'
--- a/unittest/gunit/thread_utils-t.cc 2011-06-30 15:50:45 +0000
+++ b/unittest/gunit/thread_utils-t.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
=== modified file 'unittest/gunit/thread_utils.cc'
--- a/unittest/gunit/thread_utils.cc 2011-06-30 15:50:45 +0000
+++ b/unittest/gunit/thread_utils.cc 2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
#include "my_config.h"
#include <gtest/gtest.h>
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (tor.didriksen:3440 to 3449) | Tor Didriksen | 9 Jan |
