3447 Marc Alff 2012-02-24 [merge]
Merge mysql-trunk --> mysql-trunk-wl5767
added:
mysql-test/include/greedy_search_drop_tables.inc
mysql-test/include/greedy_search_load_tables.inc
mysql-test/include/print_greedy_search_count.inc
mysql-test/r/greedy_search.result
mysql-test/t/greedy_search.test
sql/merge_sort.h
unittest/gunit/join_tab_sort-t.cc
modified:
mysql-test/include/select.inc
mysql-test/r/ctype_binary.result
mysql-test/r/ctype_cp1251.result
mysql-test/r/ctype_latin1.result
mysql-test/r/ctype_ucs.result
mysql-test/r/ctype_utf8.result
mysql-test/r/func_str.result
mysql-test/r/func_time.result
mysql-test/r/join_cache_bka.result
mysql-test/r/join_cache_bka_nixbnl.result
mysql-test/r/join_cache_bkaunique.result
mysql-test/r/join_cache_bnl.result
mysql-test/r/join_cache_nojb.result
mysql-test/r/join_nested.result
mysql-test/r/join_nested_bka.result
mysql-test/r/join_nested_bka_nixbnl.result
mysql-test/r/metadata.result
mysql-test/r/mysql_client_test_embedded.result
mysql-test/r/ps_2myisam.result
mysql-test/r/ps_3innodb.result
mysql-test/r/ps_4heap.result
mysql-test/r/ps_5merge.result
mysql-test/r/select_all.result
mysql-test/r/select_all_bka.result
mysql-test/r/select_all_bka_nixbnl.result
mysql-test/r/select_icp_mrr.result
mysql-test/r/select_icp_mrr_bka.result
mysql-test/r/select_icp_mrr_bka_nixbnl.result
mysql-test/r/select_none.result
mysql-test/r/select_none_bka.result
mysql-test/r/select_none_bka_nixbnl.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/type_temporal_fractional.result
mysql-test/r/type_timestamp.result
mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
mysql-test/suite/opt_trace/r/subquery_no_prot.result
mysql-test/suite/opt_trace/r/subquery_ps_prot.result
mysql-test/suite/parts/t/partition_alter4_myisam.test
mysql-test/suite/rpl/t/rpl_row_event_max_size.test
mysql-test/t/disabled.def
mysql-test/t/func_str.test
mysql-test/t/mysql_client_test_embedded.test
mysql-test/t/type_timestamp.test
sql/field.cc
sql/item.cc
sql/item_func.cc
sql/item_strfunc.cc
sql/item_timefunc.cc
sql/item_timefunc.h
sql/mysqld.cc
sql/sql_class.h
sql/sql_const.h
sql/sql_executor.cc
sql/sql_planner.cc
sql/sql_planner.h
sql/sql_select.h
unittest/gunit/CMakeLists.txt
3446 Marc Alff 2012-02-24
Code cleanup, continued
modified:
include/mysql/psi/psi.h
include/mysql/psi/psi_abi_v1.h.pp
mysql-test/suite/perfschema/r/statement_digest_long_query.result
storage/perfschema/pfs.cc
storage/perfschema/pfs_digest.cc
storage/perfschema/pfs_digest.h
storage/perfschema/pfs_engine_table.cc
storage/perfschema/pfs_instr.cc
storage/perfschema/pfs_instr.h
storage/perfschema/table_esms_by_digest.cc
storage/perfschema/table_events_statements.cc
storage/perfschema/table_events_statements.h
storage/perfschema/table_helper.cc
=== added file 'mysql-test/include/greedy_search_drop_tables.inc'
--- a/mysql-test/include/greedy_search_drop_tables.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/greedy_search_drop_tables.inc 2012-02-23 11:22:34 +0000
@@ -0,0 +1,3 @@
+DROP TABLE tbl10, tbl100;
+DROP TABLE t10_1,t10_2,t10_3,t10_4,t10_5,t10_6,t10_7,t10_8,t10_9;
+DROP TABLE t100_1,t100_2,t100_3,t100_4,t100_5,t100_6,t100_7,t100_8,t100_9;
=== added file 'mysql-test/include/greedy_search_load_tables.inc'
--- a/mysql-test/include/greedy_search_load_tables.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/greedy_search_load_tables.inc 2012-02-23 11:22:34 +0000
@@ -0,0 +1,35 @@
+--disable_query_log
+--disable_result_log
+
+# tbl10 and tbl100 are only used to fill the tables used during testing
+CREATE TABLE tbl10(
+ k INT NOT NULL AUTO_INCREMENT,
+ i INT,
+ PRIMARY KEY(k)
+);
+INSERT INTO tbl10(i) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
+
+CREATE TABLE tbl100 LIKE tbl10;
+INSERT INTO tbl100(i) SELECT x.i FROM tbl10 AS x, tbl10 AS y;
+
+let $i= 1;
+WHILE ($i < 10)
+{
+ --let $stmt= CREATE TABLE t10_$i (pk int PRIMARY KEY AUTO_INCREMENT, colidx int, col int, index (colidx))
+ --eval $stmt;
+ --let $stmt= INSERT INTO t10_$i (colidx, col) SELECT k, k FROM tbl10
+ --eval $stmt;
+ # Update index statistics
+ ANALYZE TABLE t10_$i;
+
+ --let $stmt= CREATE TABLE t100_$i (pk int PRIMARY KEY AUTO_INCREMENT, colidx int, col int, index (colidx))
+ --eval $stmt;
+ --let $stmt= INSERT INTO t100_$i (colidx, col) SELECT a.k, a.k FROM tbl10 a JOIN tbl10 b;
+ --eval $stmt;
+ # Update index statistics
+ ANALYZE TABLE t10_$i;
+
+ inc $i;
+}
+--enable_result_log
+--enable_query_log
=== added file 'mysql-test/include/print_greedy_search_count.inc'
--- a/mysql-test/include/print_greedy_search_count.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/print_greedy_search_count.inc 2012-02-23 11:22:34 +0000
@@ -0,0 +1,25 @@
+# include/print_greedy_search_count.inc
+#
+# SUMMARY
+#
+# $query should be assigned a select statement.
+# The query will be EXPLAINed and the number of
+# partial plans greedy search went through to
+# create the QEP is printed.
+#
+# USAGE
+#
+# let $query= <query>;
+# --source include/print_greedy_search_count.inc
+#
+# EXAMPLE
+# t/greedy_search.test
+#
+
+eval EXPLAIN $query;
+
+let $greedy_search_partial_plans=
+ query_get_value(SHOW STATUS LIKE 'Opt_partial_plans', Value, 1);
+
+--echo ### Opt_partial_plans: $greedy_search_partial_plans
+FLUSH STATUS;
=== modified file 'mysql-test/include/select.inc'
--- a/mysql-test/include/select.inc 2011-12-15 11:54:45 +0000
+++ b/mysql-test/include/select.inc 2012-02-23 13:27:28 +0000
@@ -4292,3 +4292,12 @@ DROP VIEW view_t1;
--echo # End of test BUG#63020
+--echo #
+--echo # Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+--echo #
+
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+DROP TABLE t1;
+
+--echo # End of test BUG#13571700
\ No newline at end of file
=== modified file 'mysql-test/r/ctype_binary.result'
--- a/mysql-test/r/ctype_binary.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_binary.result 2012-02-23 16:32:32 +0000
@@ -2818,8 +2818,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def field_str1 254 29 10 Y 128 31 63
def field1_str2 254 29 19 Y 128 31 63
-def field_date 10 10 10 Y 160 0 63
-def field_datetime 12 19 19 Y 160 0 63
+def field_date 10 10 10 Y 128 0 63
+def field_datetime 12 19 19 Y 128 0 63
field_str1 field1_str2 field_date field_datetime
2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00
SELECT
=== modified file 'mysql-test/r/ctype_cp1251.result'
--- a/mysql-test/r/ctype_cp1251.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_cp1251.result 2012-02-23 16:32:32 +0000
@@ -3210,8 +3210,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def field_str1 254 29 10 Y 0 31 51
def field1_str2 254 29 19 Y 0 31 51
-def field_date 10 10 10 Y 160 0 63
-def field_datetime 12 19 19 Y 160 0 63
+def field_date 10 10 10 Y 128 0 63
+def field_datetime 12 19 19 Y 128 0 63
field_str1 field1_str2 field_date field_datetime
2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00
SELECT
=== modified file 'mysql-test/r/ctype_latin1.result'
--- a/mysql-test/r/ctype_latin1.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_latin1.result 2012-02-23 16:32:32 +0000
@@ -3237,8 +3237,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def field_str1 254 29 10 Y 0 31 8
def field1_str2 254 29 19 Y 0 31 8
-def field_date 10 10 10 Y 160 0 63
-def field_datetime 12 19 19 Y 160 0 63
+def field_date 10 10 10 Y 128 0 63
+def field_datetime 12 19 19 Y 128 0 63
field_str1 field1_str2 field_date field_datetime
2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00
SELECT
=== modified file 'mysql-test/r/ctype_ucs.result'
--- a/mysql-test/r/ctype_ucs.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_ucs.result 2012-02-23 16:32:32 +0000
@@ -4137,8 +4137,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def field_str1 254 29 10 Y 0 31 8
def field1_str2 254 29 19 Y 0 31 8
-def field_date 10 10 10 Y 160 0 63
-def field_datetime 12 19 19 Y 160 0 63
+def field_date 10 10 10 Y 128 0 63
+def field_datetime 12 19 19 Y 128 0 63
field_str1 field1_str2 field_date field_datetime
2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00
SELECT
=== modified file 'mysql-test/r/ctype_utf8.result'
--- a/mysql-test/r/ctype_utf8.result 2012-01-23 10:02:54 +0000
+++ b/mysql-test/r/ctype_utf8.result 2012-02-23 16:32:32 +0000
@@ -5064,8 +5064,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def field_str1 254 87 10 Y 0 31 33
def field1_str2 254 87 19 Y 0 31 33
-def field_date 10 10 10 Y 160 0 63
-def field_datetime 12 19 19 Y 160 0 63
+def field_date 10 10 10 Y 128 0 63
+def field_datetime 12 19 19 Y 128 0 63
field_str1 field1_str2 field_date field_datetime
2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00
SELECT
=== modified file 'mysql-test/r/func_str.result'
--- a/mysql-test/r/func_str.result 2012-01-25 15:49:57 +0000
+++ b/mysql-test/r/func_str.result 2012-02-23 10:08:33 +0000
@@ -119,7 +119,7 @@ substring_index('aaaaaaaaa1','aaa',-3)
aaaaaa1
select substring_index('aaaaaaaaa1','aaa',-4);
substring_index('aaaaaaaaa1','aaa',-4)
-
+aaaaaaaaa1
select substring_index('the king of thethe hill','the',-2);
substring_index('the king of thethe hill','the',-2)
the hill
@@ -4464,5 +4464,28 @@ EXECUTE stmt;
COLLATION(space(2))
latin2_general_ci
#
+# Bug#11829861: SUBSTRING_INDEX() RESULTS IN MISSING CHARACTERS WHEN USED
+# INSIDE LOWER()
+#
+SET @user_at_host = 'root@stripped';
+SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
+LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1))
+mytinyhost-pc.local
+# End of test BUG#11829861
+#
+# Bug#42404: SUBSTRING_INDEX() RESULTS ARE INCONSISTENT
+#
+CREATE TABLE t (i INT NOT NULL, c CHAR(255) NOT NULL);
+INSERT INTO t VALUES (0,'.www.mysql.com'),(1,'.wwwmysqlcom');
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t WHERE i = 1;
+i SUBSTRING_INDEX(c, '.', -2)
+1 .wwwmysqlcom
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t;
+i SUBSTRING_INDEX(c, '.', -2)
+0 mysql.com
+1 .wwwmysqlcom
+DROP TABLE t;
+# End of test BUG#42404
+#
# End of 5.6 tests
#
=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result 2012-01-24 11:57:25 +0000
+++ b/mysql-test/r/func_time.result 2012-02-23 16:32:32 +0000
@@ -929,10 +929,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `now() - now()` bigint(20) unsigned NOT NULL DEFAULT '0',
- `curtime() - curtime()` int(10) NOT NULL DEFAULT '0',
- `sec_to_time(1) + 0` bigint(12) DEFAULT NULL,
- `from_unixtime(1) + 0` bigint(20) unsigned DEFAULT NULL
+ `now() - now()` bigint(16) NOT NULL DEFAULT '0',
+ `curtime() - curtime()` int(9) NOT NULL DEFAULT '0',
+ `sec_to_time(1) + 0` int(9) DEFAULT NULL,
+ `from_unixtime(1) + 0` bigint(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
SELECT SEC_TO_TIME(3300000);
=== added file 'mysql-test/r/greedy_search.result'
--- a/mysql-test/r/greedy_search.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/greedy_search.result 2012-02-23 11:22:34 +0000
@@ -0,0 +1,194 @@
+#
+# TEST 1
+# Greedy search iteration test for 16-way join: star schema
+#
+# Creation of 16 tables hidden
+#
+SET SESSION optimizer_search_depth = 25;
+FLUSH STATUS;
+#
+# 16-way join - all 15 fact tables joined on column with key
+#
+EXPLAIN SELECT *
+FROM vehicles
+JOIN models ON vehicles.model_id = models.id_pk
+JOIN subtypes ON vehicles.subtype_id = subtypes.id_pk
+JOIN colors ON vehicles.color_id = colors.id_pk
+JOIN heating ON vehicles.heating_id = heating.id_pk
+JOIN windows ON vehicles.window_id = windows.id_pk
+JOIN fuels ON vehicles.fuel_id = fuels.id_pk
+JOIN transmissions ON vehicles.transmission_id = transmissions.id_pk
+JOIN steerings ON vehicles.steering_id = steerings.id_pk
+JOIN interiors ON vehicles.interior_id = interiors.id_pk
+JOIN drives ON vehicles.drive_id = drives.id_pk
+JOIN wheels ON vehicles.wheels_id = wheels.id_pk
+JOIN engine ON vehicles.engine_id = engine.id_pk
+JOIN price_ranges ON vehicles.price_range_id = price_ranges.id_pk
+JOIN countries ON vehicles.assembled_in = countries.id_pk
+JOIN brands ON models.brand_id = brands.id_pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE vehicles ALL NULL NULL NULL NULL 80 Using where
+1 SIMPLE subtypes eq_ref PRIMARY PRIMARY 4 test.vehicles.subtype_id 1
+1 SIMPLE heating eq_ref PRIMARY PRIMARY 4 test.vehicles.heating_id 1
+1 SIMPLE windows eq_ref PRIMARY PRIMARY 4 test.vehicles.window_id 1
+1 SIMPLE fuels eq_ref PRIMARY PRIMARY 4 test.vehicles.fuel_id 1
+1 SIMPLE transmissions eq_ref PRIMARY PRIMARY 4 test.vehicles.transmission_id 1
+1 SIMPLE steerings eq_ref PRIMARY PRIMARY 4 test.vehicles.steering_id 1
+1 SIMPLE drives eq_ref PRIMARY PRIMARY 4 test.vehicles.drive_id 1
+1 SIMPLE wheels eq_ref PRIMARY PRIMARY 4 test.vehicles.wheels_id 1
+1 SIMPLE engine eq_ref PRIMARY PRIMARY 4 test.vehicles.engine_id 1
+1 SIMPLE price_ranges eq_ref PRIMARY PRIMARY 4 test.vehicles.price_range_id 1
+1 SIMPLE colors eq_ref PRIMARY PRIMARY 4 test.vehicles.color_id 1
+1 SIMPLE interiors eq_ref PRIMARY PRIMARY 4 test.vehicles.interior_id 1
+1 SIMPLE countries eq_ref PRIMARY PRIMARY 4 test.vehicles.assembled_in 1
+1 SIMPLE models eq_ref PRIMARY,brand_id PRIMARY 4 test.vehicles.model_id 1 Using where
+1 SIMPLE brands eq_ref PRIMARY PRIMARY 4 test.models.brand_id 1
+### Opt_partial_plans: 50922
+FLUSH STATUS;
+#
+# 16-way join - 10 fact tables joined on column with key and
+# 5 fact tables joined on column without key
+#
+EXPLAIN SELECT *
+FROM vehicles
+JOIN models ON vehicles.model_id = models.id_nokey
+JOIN subtypes ON vehicles.subtype_id = subtypes.id_pk
+JOIN colors ON vehicles.color_id = colors.id_pk
+JOIN heating ON vehicles.heating_id = heating.id_nokey
+JOIN windows ON vehicles.window_id = windows.id_pk
+JOIN fuels ON vehicles.fuel_id = fuels.id_pk
+JOIN transmissions ON vehicles.transmission_id = transmissions.id_nokey
+JOIN steerings ON vehicles.steering_id = steerings.id_pk
+JOIN interiors ON vehicles.interior_id = interiors.id_pk
+JOIN drives ON vehicles.drive_id = drives.id_pk
+JOIN wheels ON vehicles.wheels_id = wheels.id_nokey
+JOIN engine ON vehicles.engine_id = engine.id_pk
+JOIN price_ranges ON vehicles.price_range_id = price_ranges.id_pk
+JOIN countries ON vehicles.assembled_in = countries.id_pk
+JOIN brands ON models.brand_id = brands.id_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE brands ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE models ref brand_id brand_id 5 test.brands.id_nokey 4
+1 SIMPLE heating ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop)
+1 SIMPLE transmissions ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop)
+1 SIMPLE wheels ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop)
+1 SIMPLE vehicles ALL NULL NULL NULL NULL 80 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE windows eq_ref PRIMARY PRIMARY 4 test.vehicles.window_id 1
+1 SIMPLE fuels eq_ref PRIMARY PRIMARY 4 test.vehicles.fuel_id 1
+1 SIMPLE steerings eq_ref PRIMARY PRIMARY 4 test.vehicles.steering_id 1
+1 SIMPLE drives eq_ref PRIMARY PRIMARY 4 test.vehicles.drive_id 1
+1 SIMPLE engine eq_ref PRIMARY PRIMARY 4 test.vehicles.engine_id 1
+1 SIMPLE price_ranges eq_ref PRIMARY PRIMARY 4 test.vehicles.price_range_id 1
+1 SIMPLE interiors eq_ref PRIMARY PRIMARY 4 test.vehicles.interior_id 1
+1 SIMPLE countries eq_ref PRIMARY PRIMARY 4 test.vehicles.assembled_in 1
+1 SIMPLE subtypes eq_ref PRIMARY PRIMARY 4 test.vehicles.subtype_id 1
+1 SIMPLE colors eq_ref PRIMARY PRIMARY 4 test.vehicles.color_id 1
+### Opt_partial_plans: 483932
+FLUSH STATUS;
+select @@optimizer_search_depth;
+@@optimizer_search_depth
+25
+select @@optimizer_prune_level;
+@@optimizer_prune_level
+1
+DROP TABLE vehicles, models, subtypes, colors, heating, windows,
+fuels, transmissions, steerings, interiors, drives,
+price_ranges, countries, brands, wheels, engine;
+#
+# TEST 2
+# Greedy search iteration test for chain of tables
+#
+#
+# Chain test a: colidx):(pk,colidx):(pk,colidx)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.pk JOIN t10_2 ON t100_1.colidx = t10_2.pk JOIN t100_2 ON t10_2.colidx = t100_2.pk JOIN t10_3 ON t100_2.colidx = t10_3.pk JOIN t100_3 ON t10_3.colidx = t100_3.pk JOIN t10_4 ON t100_3.colidx = t10_4.pk JOIN t100_4 ON t10_4.colidx = t100_4.pk JOIN t10_5 ON t100_4.colidx = t10_5.pk JOIN t100_5 ON t10_5.colidx = t100_5.pk JOIN t10_6 ON t100_5.colidx = t10_6.pk JOIN t100_6 ON t10_6.colidx = t100_6.pk JOIN t10_7 ON t100_6.colidx = t10_7.pk JOIN t100_7 ON t10_7.colidx = t100_7.pk JOIN t10_8 ON t100_7.colidx = t10_8.pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10_1 ALL colidx NULL NULL NULL 10 Using where
+1 SIMPLE t100_1 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_1.colidx 1 Using where
+1 SIMPLE t10_2 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_1.colidx 1 Using where
+1 SIMPLE t100_2 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_2.colidx 1 Using where
+1 SIMPLE t10_3 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_2.colidx 1 Using where
+1 SIMPLE t100_3 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_3.colidx 1 Using where
+1 SIMPLE t10_4 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_3.colidx 1 Using where
+1 SIMPLE t100_4 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_4.colidx 1 Using where
+1 SIMPLE t10_5 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_4.colidx 1 Using where
+1 SIMPLE t100_5 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_5.colidx 1 Using where
+1 SIMPLE t10_6 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_5.colidx 1 Using where
+1 SIMPLE t100_6 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_6.colidx 1 Using where
+1 SIMPLE t10_7 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_6.colidx 1 Using where
+1 SIMPLE t100_7 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_7.colidx 1 Using where
+1 SIMPLE t10_8 eq_ref PRIMARY PRIMARY 4 test.t100_7.colidx 1
+### Opt_partial_plans: 3669
+FLUSH STATUS;
+#
+# Chain test b: (...,col):(colidx, col):(pk,col):(colidx,col):(pk,...)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.col = t100_1.colidx JOIN t10_2 ON t100_1.col = t10_2.pk JOIN t100_2 ON t10_2.col = t100_2.colidx JOIN t10_3 ON t100_2.col = t10_3.pk JOIN t100_3 ON t10_3.col = t100_3.colidx JOIN t10_4 ON t100_3.col = t10_4.pk JOIN t100_4 ON t10_4.col = t100_4.colidx JOIN t10_5 ON t100_4.col = t10_5.pk JOIN t100_5 ON t10_5.col = t100_5.colidx JOIN t10_6 ON t100_5.col = t10_6.pk JOIN t100_6 ON t10_6.col = t100_6.colidx JOIN t10_7 ON t100_6.col = t10_7.pk JOIN t100_7 ON t10_7.col = t100_7.colidx JOIN t10_8 ON t100_7.col = t10_8.pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10_1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t100_1 ref colidx colidx 5 test.t10_1.col 10 Using where
+1 SIMPLE t10_2 eq_ref PRIMARY PRIMARY 4 test.t100_1.col 1 Using where
+1 SIMPLE t100_2 ref colidx colidx 5 test.t10_2.col 10 Using where
+1 SIMPLE t10_3 eq_ref PRIMARY PRIMARY 4 test.t100_2.col 1 Using where
+1 SIMPLE t100_3 ref colidx colidx 5 test.t10_3.col 10 Using where
+1 SIMPLE t10_4 eq_ref PRIMARY PRIMARY 4 test.t100_3.col 1 Using where
+1 SIMPLE t100_4 ref colidx colidx 5 test.t10_4.col 10 Using where
+1 SIMPLE t10_5 eq_ref PRIMARY PRIMARY 4 test.t100_4.col 1 Using where
+1 SIMPLE t100_5 ref colidx colidx 5 test.t10_5.col 10 Using where
+1 SIMPLE t10_6 eq_ref PRIMARY PRIMARY 4 test.t100_5.col 1 Using where
+1 SIMPLE t100_6 ref colidx colidx 5 test.t10_6.col 10 Using where
+1 SIMPLE t10_7 eq_ref PRIMARY PRIMARY 4 test.t100_6.col 1 Using where
+1 SIMPLE t100_7 ref colidx colidx 5 test.t10_7.col 10 Using where
+1 SIMPLE t10_8 eq_ref PRIMARY PRIMARY 4 test.t100_7.col 1
+### Opt_partial_plans: 692133
+FLUSH STATUS;
+#
+# Chain test c: (...,colidx):(col, pk):(col,colidx):(col,...)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.col JOIN t10_2 ON t100_1.pk = t10_2.col JOIN t100_2 ON t10_2.colidx = t100_2.col JOIN t10_3 ON t100_2.pk = t10_3.col JOIN t100_3 ON t10_3.colidx = t100_3.col JOIN t10_4 ON t100_3.pk = t10_4.col JOIN t100_4 ON t10_4.colidx = t100_4.col JOIN t10_5 ON t100_4.pk = t10_5.col JOIN t100_5 ON t10_5.colidx = t100_5.col JOIN t10_6 ON t100_5.pk = t10_6.col JOIN t100_6 ON t10_6.colidx = t100_6.col JOIN t10_7 ON t100_6.pk = t10_7.col JOIN t100_7 ON t10_7.colidx = t100_7.col JOIN t10_8 ON t100_7.pk = t10_8.col;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10_8 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t100_7 eq_ref PRIMARY PRIMARY 4 test.t10_8.col 1 Using where
+1 SIMPLE t10_7 ref colidx colidx 5 test.t100_7.col 1 Using where
+1 SIMPLE t100_6 eq_ref PRIMARY PRIMARY 4 test.t10_7.col 1 Using where
+1 SIMPLE t10_6 ref colidx colidx 5 test.t100_6.col 1 Using where
+1 SIMPLE t100_5 eq_ref PRIMARY PRIMARY 4 test.t10_6.col 1 Using where
+1 SIMPLE t10_5 ref colidx colidx 5 test.t100_5.col 1 Using where
+1 SIMPLE t100_4 eq_ref PRIMARY PRIMARY 4 test.t10_5.col 1 Using where
+1 SIMPLE t10_4 ref colidx colidx 5 test.t100_4.col 1 Using where
+1 SIMPLE t100_3 eq_ref PRIMARY PRIMARY 4 test.t10_4.col 1 Using where
+1 SIMPLE t10_3 ref colidx colidx 5 test.t100_3.col 1 Using where
+1 SIMPLE t100_2 eq_ref PRIMARY PRIMARY 4 test.t10_3.col 1 Using where
+1 SIMPLE t10_2 ref colidx colidx 5 test.t100_2.col 1 Using where
+1 SIMPLE t100_1 eq_ref PRIMARY PRIMARY 4 test.t10_2.col 1 Using where
+1 SIMPLE t10_1 ref colidx colidx 5 test.t100_1.col 1
+### Opt_partial_plans: 12025
+FLUSH STATUS;
+#
+# Chain test d: (...,colidx):(pk, col):(pk,colidx):(pk,col):(pk,...)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.pk JOIN t10_2 ON t100_1.col = t10_2.pk JOIN t100_2 ON t10_2.colidx = t100_2.pk JOIN t10_3 ON t100_2.col = t10_3.pk JOIN t100_3 ON t10_3.colidx = t100_3.pk JOIN t10_4 ON t100_3.col = t10_4.pk JOIN t100_4 ON t10_4.colidx = t100_4.pk JOIN t10_5 ON t100_4.col = t10_5.pk JOIN t100_5 ON t10_5.colidx = t100_5.pk JOIN t10_6 ON t100_5.col = t10_6.pk JOIN t100_6 ON t10_6.colidx = t100_6.pk JOIN t10_7 ON t100_6.col = t10_7.pk JOIN t100_7 ON t10_7.colidx = t100_7.pk JOIN t10_8 ON t100_7.col = t10_8.pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10_1 ALL colidx NULL NULL NULL 10 Using where
+1 SIMPLE t100_1 eq_ref PRIMARY PRIMARY 4 test.t10_1.colidx 1 Using where
+1 SIMPLE t10_2 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_1.col 1 Using where
+1 SIMPLE t100_2 eq_ref PRIMARY PRIMARY 4 test.t10_2.colidx 1 Using where
+1 SIMPLE t10_3 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_2.col 1 Using where
+1 SIMPLE t100_3 eq_ref PRIMARY PRIMARY 4 test.t10_3.colidx 1 Using where
+1 SIMPLE t10_4 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_3.col 1 Using where
+1 SIMPLE t100_4 eq_ref PRIMARY PRIMARY 4 test.t10_4.colidx 1 Using where
+1 SIMPLE t10_5 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_4.col 1 Using where
+1 SIMPLE t100_5 eq_ref PRIMARY PRIMARY 4 test.t10_5.colidx 1 Using where
+1 SIMPLE t10_6 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_5.col 1 Using where
+1 SIMPLE t100_6 eq_ref PRIMARY PRIMARY 4 test.t10_6.colidx 1 Using where
+1 SIMPLE t10_7 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_6.col 1 Using where
+1 SIMPLE t100_7 eq_ref PRIMARY PRIMARY 4 test.t10_7.colidx 1 Using where
+1 SIMPLE t10_8 eq_ref PRIMARY PRIMARY 4 test.t100_7.col 1
+### Opt_partial_plans: 1445
+FLUSH STATUS;
+#
+# Cleanup after TEST 2
+#
+DROP TABLE tbl10, tbl100;
+DROP TABLE t10_1,t10_2,t10_3,t10_4,t10_5,t10_6,t10_7,t10_8,t10_9;
+DROP TABLE t100_1,t100_2,t100_3,t100_4,t100_5,t100_6,t100_7,t100_8,t100_9;
=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result 2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bka.result 2012-02-23 11:22:34 +0000
@@ -2387,33 +2387,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 1 Using index; Start temporary
-1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Start temporary
+1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
1 PRIMARY t3 ref k1 k1 5 test.t1.c2_key 1 Using index; End temporary
SELECT t3.c1 FROM t3
WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result 2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result 2012-02-23 11:22:34 +0000
@@ -2387,33 +2387,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 1 Using index; Start temporary
-1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Using where
+1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Start temporary
+1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
1 PRIMARY t3 ref k1 k1 5 test.t1.c2_key 1 Using index; End temporary
SELECT t3.c1 FROM t3
WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result 2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result 2012-02-23 11:22:34 +0000
@@ -2388,33 +2388,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 1 Using index; Start temporary
-1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Start temporary
+1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
1 PRIMARY t3 ref k1 k1 5 test.t1.c2_key 1 Using index; End temporary
SELECT t3.c1 FROM t3
WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result 2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bnl.result 2012-02-23 11:22:34 +0000
@@ -2388,33 +2388,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 1 Using index; Start temporary
-1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Start temporary
+1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
1 PRIMARY t3 ref k1 k1 5 test.t1.c2_key 1 Using index; End temporary
SELECT t3.c1 FROM t3
WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result 2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_nojb.result 2012-02-23 11:22:34 +0000
@@ -2388,33 +2388,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 1 Using index; Start temporary
-1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Using where
+1 PRIMARY t1 ALL col_int_key NULL NULL NULL 1 Start temporary
+1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1)
1 PRIMARY t3 ref k1 k1 5 test.t1.c2_key 1 Using index; End temporary
SELECT t3.c1 FROM t3
WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result 2012-01-20 15:30:14 +0000
+++ b/mysql-test/r/join_nested.result 2012-02-23 11:22:34 +0000
@@ -546,8 +546,8 @@ id select_type table type possible_keys
1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop)
1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
=== modified file 'mysql-test/r/join_nested_bka.result'
--- a/mysql-test/r/join_nested_bka.result 2012-01-20 15:30:14 +0000
+++ b/mysql-test/r/join_nested_bka.result 2012-02-23 11:22:34 +0000
@@ -547,8 +547,8 @@ id select_type table type possible_keys
1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop)
1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
=== modified file 'mysql-test/r/join_nested_bka_nixbnl.result'
--- a/mysql-test/r/join_nested_bka_nixbnl.result 2012-01-20 15:30:14 +0000
+++ b/mysql-test/r/join_nested_bka_nixbnl.result 2012-02-23 11:22:34 +0000
@@ -547,8 +547,8 @@ id select_type table type possible_keys
1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00
1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where
1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where
=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result 2011-11-21 11:58:25 +0000
+++ b/mysql-test/r/metadata.result 2012-02-23 16:32:32 +0000
@@ -21,7 +21,7 @@ def test t1 t1 g g 5 4 0 Y 32768 3 63
def test t1 t1 h h 246 7 0 Y 32768 4 63
def test t1 t1 i i 13 4 0 Y 32864 0 63
def test t1 t1 j j 10 10 0 Y 128 0 63
-def test t1 t1 k k 7 19 0 N 9441 0 63
+def test t1 t1 k k 7 19 0 N 9345 0 63
def test t1 t1 l l 12 19 0 Y 128 0 63
def test t1 t1 m m 254 1 0 Y 256 0 8
def test t1 t1 n n 254 3 0 Y 2048 0 8
@@ -284,7 +284,7 @@ def test t1 t1 double_precision_col doub
def test t1 t1 dcol_uns dcol_uns 5 22 0 Y 32800 31 63
def test t1 t1 date_col date_col 10 10 0 Y 128 0 63
def test t1 t1 time_col time_col 11 10 0 Y 128 0 63
-def test t1 t1 timestamp_col timestamp_col 7 19 0 N 9441 0 63
+def test t1 t1 timestamp_col timestamp_col 7 19 0 N 9345 0 63
def test t1 t1 year_col year_col 13 4 0 Y 32864 0 63
def test t1 t1 datetime_col datetime_col 12 19 0 Y 128 0 63
def test t1 t1 char_col char_col 254 5 0 Y 0 0 8
@@ -332,12 +332,12 @@ def test t1 t1 dt3 dt3 12 23 0 Y 128 3 6
def test t1 t1 dt2 dt2 12 22 0 Y 128 2 63
def test t1 t1 dt1 dt1 12 21 0 Y 128 1 63
def test t1 t1 dt0 dt0 12 19 0 Y 128 0 63
-def test t1 t1 ts6 ts6 7 26 0 N 9441 6 63
-def test t1 t1 ts5 ts5 7 25 0 N 225 5 63
-def test t1 t1 ts4 ts4 7 24 0 N 225 4 63
-def test t1 t1 ts3 ts3 7 23 0 N 225 3 63
-def test t1 t1 ts2 ts2 7 22 0 N 225 2 63
-def test t1 t1 ts1 ts1 7 21 0 N 225 1 63
-def test t1 t1 ts0 ts0 7 19 0 N 225 0 63
+def test t1 t1 ts6 ts6 7 26 0 N 9345 6 63
+def test t1 t1 ts5 ts5 7 25 0 N 129 5 63
+def test t1 t1 ts4 ts4 7 24 0 N 129 4 63
+def test t1 t1 ts3 ts3 7 23 0 N 129 3 63
+def test t1 t1 ts2 ts2 7 22 0 N 129 2 63
+def test t1 t1 ts1 ts1 7 21 0 N 129 1 63
+def test t1 t1 ts0 ts0 7 19 0 N 129 0 63
t6 t5 t4 t3 t2 t1 t0 dt6 dt5 dt4 dt3 dt2 dt1 dt0 ts6 ts5 ts4 ts3 ts2 ts1 ts0
DROP TABLE t1;
=== modified file 'mysql-test/r/mysql_client_test_embedded.result'
--- a/mysql-test/r/mysql_client_test_embedded.result 2011-12-05 10:11:58 +0000
+++ b/mysql-test/r/mysql_client_test_embedded.result 2012-02-23 10:39:15 +0000
@@ -1,92 +1,2 @@
# Run the start/stop test 17 times (* 64 = 1088 restarts)
# --silent to avoid printing out server version etc.
-
-
-#####################################
-1 of (1/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (2/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (3/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (4/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (5/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (6/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (7/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (8/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (9/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (10/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (11/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (12/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (13/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (14/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (15/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (16/17): test_embedded_start_stop
-#####################################
-
-
-#####################################
-1 of (17/17): test_embedded_start_stop
-#####################################
-
-
-All '1' tests were successful (in '17' iterations)
-
-!!! SUCCESS !!!
=== modified file 'mysql-test/r/ps_2myisam.result'
--- a/mysql-test/r/ps_2myisam.result 2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_2myisam.result 2012-02-23 16:32:32 +0000
@@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 32768 4
def test t9 t9 c12 c12 246 10 6 Y 32768 4 63
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
-def test t9 t9 c15 c15 7 19 19 N 9441 0 63
+def test t9 t9 c15 c15 7 19 19 N 9345 0 63
def test t9 t9 c16 c16 11 10 8 Y 128 0 63
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
=== modified file 'mysql-test/r/ps_3innodb.result'
--- a/mysql-test/r/ps_3innodb.result 2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_3innodb.result 2012-02-23 16:32:32 +0000
@@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 32768 4
def test t9 t9 c12 c12 246 10 6 Y 32768 4 63
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
-def test t9 t9 c15 c15 7 19 19 N 9441 0 63
+def test t9 t9 c15 c15 7 19 19 N 9345 0 63
def test t9 t9 c16 c16 11 10 8 Y 128 0 63
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
=== modified file 'mysql-test/r/ps_4heap.result'
--- a/mysql-test/r/ps_4heap.result 2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_4heap.result 2012-02-23 16:32:32 +0000
@@ -64,7 +64,7 @@ def test t9 t9 c11 c11 246 9 6 Y 32768 4
def test t9 t9 c12 c12 246 10 6 Y 32768 4 63
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
-def test t9 t9 c15 c15 7 19 19 N 9441 0 63
+def test t9 t9 c15 c15 7 19 19 N 9345 0 63
def test t9 t9 c16 c16 11 10 8 Y 128 0 63
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
=== modified file 'mysql-test/r/ps_5merge.result'
--- a/mysql-test/r/ps_5merge.result 2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_5merge.result 2012-02-23 16:32:32 +0000
@@ -106,7 +106,7 @@ def test t9 t9 c11 c11 246 9 6 Y 32768 4
def test t9 t9 c12 c12 246 10 6 Y 32768 4 63
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
-def test t9 t9 c15 c15 7 19 19 N 9441 0 63
+def test t9 t9 c15 c15 7 19 19 N 9345 0 63
def test t9 t9 c16 c16 11 10 8 Y 128 0 63
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
@@ -3132,7 +3132,7 @@ def test t9 t9 c11 c11 246 9 6 Y 32768 4
def test t9 t9 c12 c12 246 10 6 Y 32768 4 63
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
-def test t9 t9 c15 c15 7 19 19 N 9441 0 63
+def test t9 t9 c15 c15 7 19 19 N 9345 0 63
def test t9 t9 c16 c16 11 10 8 Y 128 0 63
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
=== modified file 'mysql-test/r/select_all.result'
--- a/mysql-test/r/select_all.result 2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_all.result 2012-02-23 13:27:28 +0000
@@ -5026,4 +5026,13 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_all_bka.result'
--- a/mysql-test/r/select_all_bka.result 2011-12-16 10:57:51 +0000
+++ b/mysql-test/r/select_all_bka.result 2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
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-12-16 10:57:51 +0000
+++ b/mysql-test/r/select_all_bka_nixbnl.result 2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result 2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_icp_mrr.result 2012-02-23 13:27:28 +0000
@@ -5026,4 +5026,13 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_icp_mrr_bka.result'
--- a/mysql-test/r/select_icp_mrr_bka.result 2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_icp_mrr_bka.result 2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_icp_mrr_bka_nixbnl.result'
--- a/mysql-test/r/select_icp_mrr_bka_nixbnl.result 2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_icp_mrr_bka_nixbnl.result 2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_none.result'
--- a/mysql-test/r/select_none.result 2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_none.result 2012-02-23 13:27:28 +0000
@@ -5025,4 +5025,13 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_none_bka.result'
--- a/mysql-test/r/select_none_bka.result 2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_none_bka.result 2012-02-23 13:27:28 +0000
@@ -5026,5 +5026,14 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/select_none_bka_nixbnl.result'
--- a/mysql-test/r/select_none_bka_nixbnl.result 2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_none_bka_nixbnl.result 2012-02-23 13:27:28 +0000
@@ -5026,5 +5026,14 @@ f1
DROP TABLE t1;
DROP VIEW view_t1;
# End of test BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a COUNT(*)
+NULL 0
+DROP TABLE t1;
+# End of test BUG#13571700
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/subquery_all.result 2012-02-23 11:22:34 +0000
@@ -904,10 +904,10 @@ a t1.a in (select t2.a from t2,t3 where
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 SUBQUERY t2 index a a 5 NULL 3 100.00 Using index
-2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00
+2 SUBQUERY t2 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`a` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result 2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/subquery_all_bka.result 2012-02-23 11:22:34 +0000
@@ -905,10 +905,10 @@ a t1.a in (select t2.a from t2,t3 where
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 SUBQUERY t2 index a a 5 NULL 3 100.00 Using index
-2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop)
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00
+2 SUBQUERY t2 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`a` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result 2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/subquery_mat_all.result 2012-02-23 11:22:34 +0000
@@ -630,10 +630,10 @@ insert into t2 values (1,10);
explain extended
select a from t1 where a in (select c from t2 where d >= 20);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index
-1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Using where; Materialize
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Using where; Materialize; Scan
+1 PRIMARY t1 ref it1a it1a 4 test.t2.c 2 100.00 Using index
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t2`.`d` >= 20))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20))
select a from t1 where a in (select c from t2 where d >= 20);
a
2
@@ -682,12 +682,12 @@ explain extended
select a from t1
where a in (select c from t2 where d >= some(select e from t3 where b=e));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 index it1a,iab iab 8 NULL 7 100.00 Using index
-1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Start temporary
+1 PRIMARY t1 ref it1a,iab iab 4 test.t2.c 1 100.00 Using where; Using index; End temporary
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
select a from t1
where a in (select c from t2 where d >= some(select e from t3 where b=e));
a
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-02-23 11:22:34 +0000
@@ -5039,9 +5039,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5053,14 +5053,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6630,8 +6630,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
@@ -7532,8 +7532,8 @@ id select_type table type possible_keys
1 PRIMARY GP2 system NULL NULL NULL NULL 1
1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary
-2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t3 index col_varchar_key col_varchar_key 4 NULL 1 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
SELECT GP1.id
FROM t1 AS GP1 JOIN t3 AS GP2
ON GP2.col_varchar_key <> GP1.col_varchar_nokey
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
@@ -7533,8 +7533,8 @@ id select_type table type possible_keys
1 PRIMARY GP2 system NULL NULL NULL NULL 1
1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary
-2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t3 index col_varchar_key col_varchar_key 4 NULL 1 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
SELECT GP1.id
FROM t1 AS GP1 JOIN t3 AS GP2
ON GP2.col_varchar_key <> GP1.col_varchar_nokey
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End temporary
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
@@ -7534,8 +7534,8 @@ id select_type table type possible_keys
1 PRIMARY GP2 system NULL NULL NULL NULL 1
1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary
-2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t3 index col_varchar_key col_varchar_key 4 NULL 1 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
SELECT GP1.id
FROM t1 AS GP1 JOIN t3 AS GP2
ON GP2.col_varchar_key <> GP1.col_varchar_nokey
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
@@ -7533,8 +7533,8 @@ id select_type table type possible_keys
1 PRIMARY GP2 system NULL NULL NULL NULL 1
1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary
-2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t3 index col_varchar_key col_varchar_key 4 NULL 1 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
SELECT GP1.id
FROM t1 AS GP1 JOIN t3 AS GP2
ON GP2.col_varchar_key <> GP1.col_varchar_nokey
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
@@ -7534,8 +7534,8 @@ id select_type table type possible_keys
1 PRIMARY GP2 system NULL NULL NULL NULL 1
1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary
-2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t3 index col_varchar_key col_varchar_key 4 NULL 1 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
SELECT GP1.id
FROM t1 AS GP1 JOIN t3 AS GP2
ON GP2.col_varchar_key <> GP1.col_varchar_nokey
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; FirstMatch(t1)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; FirstMatch(t1)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; FirstMatch(t1)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-02-23 11:22:34 +0000
@@ -5042,9 +5042,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5056,14 +5056,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6633,8 +6633,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
@@ -7535,8 +7535,8 @@ id select_type table type possible_keys
1 PRIMARY GP2 system NULL NULL NULL NULL 1
1 PRIMARY GP1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 index col_varchar_key col_varchar_key 4 NULL 1 Using index; Start temporary
-2 DEPENDENT SUBQUERY t3 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY t3 index col_varchar_key col_varchar_key 4 NULL 1 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
SELECT GP1.id
FROM t1 AS GP1 JOIN t3 AS GP2
ON GP2.col_varchar_key <> GP1.col_varchar_nokey
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End temporary
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ref t1_IDX t1_IDX 3 test.t3.EMPNUM 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-02-23 11:22:34 +0000
@@ -5042,9 +5042,9 @@ WHERE PNUM IN
FROM t2
WHERE PTYPE = 'Design'));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
@@ -5056,14 +5056,14 @@ WHERE EMPNUM IN
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary
-1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (Block Nested Loop)
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; End temporary; Using join buffer (Block Nested Loop)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Start temporary
+1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
@@ -6633,8 +6633,8 @@ AND grandparent1.col_varchar_key IS NOT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 20 Start temporary
-2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where
-2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index; End temporary
+2 DEPENDENT SUBQUERY parent2 eq_ref PRIMARY PRIMARY 4 test.parent1.pk 1 Using index
+2 DEPENDENT SUBQUERY grandparent1 ref col_varchar_key col_varchar_key 3 test.parent1.col_varchar_nokey 1 Using where; End temporary
SELECT *
FROM t1
WHERE g1 NOT IN
=== modified file 'mysql-test/r/type_temporal_fractional.result'
--- a/mysql-test/r/type_temporal_fractional.result 2012-02-17 13:55:18 +0000
+++ b/mysql-test/r/type_temporal_fractional.result 2012-02-23 16:32:32 +0000
@@ -3104,13 +3104,13 @@ MAX(t4) + 1, MAX(t5) + 1, MAX(t6) + 1
FROM t1;
SHOW COLUMNS FROM t2;
Field Type Null Key Default Extra
-MAX(t0) + 1 bigint(12) YES NULL
-MAX(t1) + 1 decimal(13,1) YES NULL
-MAX(t2) + 1 decimal(14,2) YES NULL
-MAX(t3) + 1 decimal(15,3) YES NULL
-MAX(t4) + 1 decimal(16,4) YES NULL
-MAX(t5) + 1 decimal(17,5) YES NULL
-MAX(t6) + 1 decimal(18,6) YES NULL
+MAX(t0) + 1 int(9) YES NULL
+MAX(t1) + 1 decimal(9,1) YES NULL
+MAX(t2) + 1 decimal(10,2) YES NULL
+MAX(t3) + 1 decimal(11,3) YES NULL
+MAX(t4) + 1 decimal(12,4) YES NULL
+MAX(t5) + 1 decimal(13,5) YES NULL
+MAX(t6) + 1 decimal(14,6) YES NULL
SELECT * FROM t2;
MAX(t0) + 1 MAX(t1) + 1 MAX(t2) + 1 MAX(t3) + 1 MAX(t4) + 1 MAX(t5) + 1 MAX(t6) + 1
101011 101011.9 101011.99 101011.999 101011.9999 101011.99999 101011.999999
@@ -3123,13 +3123,13 @@ MAX(t4) + 1.1, MAX(t5) + 1.1, MAX(t6) +
FROM t1;
SHOW COLUMNS FROM t2;
Field Type Null Key Default Extra
-MAX(t0) + 1.1 decimal(12,1) YES NULL
-MAX(t1) + 1.1 decimal(13,1) YES NULL
-MAX(t2) + 1.1 decimal(14,2) YES NULL
-MAX(t3) + 1.1 decimal(15,3) YES NULL
-MAX(t4) + 1.1 decimal(16,4) YES NULL
-MAX(t5) + 1.1 decimal(17,5) YES NULL
-MAX(t6) + 1.1 decimal(18,6) YES NULL
+MAX(t0) + 1.1 decimal(9,1) YES NULL
+MAX(t1) + 1.1 decimal(9,1) YES NULL
+MAX(t2) + 1.1 decimal(10,2) YES NULL
+MAX(t3) + 1.1 decimal(11,3) YES NULL
+MAX(t4) + 1.1 decimal(12,4) YES NULL
+MAX(t5) + 1.1 decimal(13,5) YES NULL
+MAX(t6) + 1.1 decimal(14,6) YES NULL
SELECT * FROM t2;
MAX(t0) + 1.1 MAX(t1) + 1.1 MAX(t2) + 1.1 MAX(t3) + 1.1 MAX(t4) + 1.1 MAX(t5) + 1.1 MAX(t6) + 1.1
101011.1 101012.0 101012.09 101012.099 101012.0999 101012.09999 101012.099999
@@ -3597,8 +3597,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `i` decimal(18,6) DEFAULT NULL,
- `d` decimal(18,6) DEFAULT NULL,
+ `i` decimal(14,6) DEFAULT NULL,
+ `d` decimal(14,6) DEFAULT NULL,
`f` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, t2;
@@ -3627,30 +3627,30 @@ FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `t0 + 1` bigint(12) DEFAULT NULL,
- `t3 + 1` decimal(15,3) DEFAULT NULL,
- `t6 + 1` decimal(18,6) DEFAULT NULL,
- `t0 - 1` bigint(12) DEFAULT NULL,
- `t3 - 1` decimal(15,3) DEFAULT NULL,
- `t6 - 1` decimal(18,6) DEFAULT NULL,
- `t0 * 1` bigint(12) DEFAULT NULL,
- `t3 * 1` decimal(15,3) DEFAULT NULL,
- `t6 * 1` decimal(18,6) DEFAULT NULL,
- `t0 / 1` decimal(14,4) DEFAULT NULL,
- `t3 / 1` decimal(18,7) DEFAULT NULL,
- `t6 / 1` decimal(21,10) DEFAULT NULL,
- `TIME'10:10:10' + 1` bigint(12) NOT NULL DEFAULT '0',
- `TIME'10:10:10.123' + 1` decimal(15,3) NOT NULL DEFAULT '0.000',
- `TIME'10:10:10.123456' + 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
- `TIME'10:10:10' - 1` bigint(12) NOT NULL DEFAULT '0',
- `TIME'10:10:10.123' - 1` decimal(15,3) NOT NULL DEFAULT '0.000',
- `TIME'10:10:10.123456' - 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
- `TIME'10:10:10' * 1` bigint(12) NOT NULL DEFAULT '0',
- `TIME'10:10:10.123' * 1` decimal(15,3) NOT NULL DEFAULT '0.000',
- `TIME'10:10:10.123456' * 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
- `TIME'10:10:10' / 1` decimal(14,4) DEFAULT NULL,
- `TIME'10:10:10.123' / 1` decimal(18,7) DEFAULT NULL,
- `TIME'10:10:10.123456' / 1` decimal(21,10) DEFAULT NULL
+ `t0 + 1` int(9) DEFAULT NULL,
+ `t3 + 1` decimal(11,3) DEFAULT NULL,
+ `t6 + 1` decimal(14,6) DEFAULT NULL,
+ `t0 - 1` int(9) DEFAULT NULL,
+ `t3 - 1` decimal(11,3) DEFAULT NULL,
+ `t6 - 1` decimal(14,6) DEFAULT NULL,
+ `t0 * 1` int(9) DEFAULT NULL,
+ `t3 * 1` decimal(11,3) DEFAULT NULL,
+ `t6 * 1` decimal(14,6) DEFAULT NULL,
+ `t0 / 1` decimal(11,4) DEFAULT NULL,
+ `t3 / 1` decimal(14,7) DEFAULT NULL,
+ `t6 / 1` decimal(17,10) DEFAULT NULL,
+ `TIME'10:10:10' + 1` int(9) NOT NULL DEFAULT '0',
+ `TIME'10:10:10.123' + 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+ `TIME'10:10:10.123456' + 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+ `TIME'10:10:10' - 1` int(9) NOT NULL DEFAULT '0',
+ `TIME'10:10:10.123' - 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+ `TIME'10:10:10.123456' - 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+ `TIME'10:10:10' * 1` int(9) NOT NULL DEFAULT '0',
+ `TIME'10:10:10.123' * 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+ `TIME'10:10:10.123456' * 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+ `TIME'10:10:10' / 1` decimal(11,4) DEFAULT NULL,
+ `TIME'10:10:10.123' / 1` decimal(14,7) DEFAULT NULL,
+ `TIME'10:10:10.123456' / 1` decimal(17,10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
@@ -3702,16 +3702,16 @@ Table Create Table
t2 CREATE TABLE `t2` (
`MIN(t0)` time DEFAULT NULL,
`MAX(t0)` time DEFAULT NULL,
- `AVG(t0)` decimal(14,4) DEFAULT NULL,
- `SUM(t0)` decimal(32,0) DEFAULT NULL,
+ `AVG(t0)` decimal(11,4) DEFAULT NULL,
+ `SUM(t0)` decimal(29,0) DEFAULT NULL,
`MIN(t3)` time(3) DEFAULT NULL,
`MAX(t3)` time(3) DEFAULT NULL,
- `AVG(t3)` decimal(18,7) DEFAULT NULL,
- `SUM(t3)` decimal(36,3) DEFAULT NULL,
+ `AVG(t3)` decimal(14,7) DEFAULT NULL,
+ `SUM(t3)` decimal(32,3) DEFAULT NULL,
`MIN(t6)` time(6) DEFAULT NULL,
`MAX(t6)` time(6) DEFAULT NULL,
- `AVG(t6)` decimal(21,10) DEFAULT NULL,
- `SUM(t6)` decimal(39,6) DEFAULT NULL
+ `AVG(t6)` decimal(17,10) DEFAULT NULL,
+ `SUM(t6)` decimal(35,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
@@ -8041,13 +8041,13 @@ MAX(t4) + 1, MAX(t5) + 1, MAX(t6) + 1
FROM t1;
SHOW COLUMNS FROM t2;
Field Type Null Key Default Extra
-MAX(t0) + 1 bigint(21) YES NULL
-MAX(t1) + 1 decimal(22,1) YES NULL
-MAX(t2) + 1 decimal(23,2) YES NULL
-MAX(t3) + 1 decimal(24,3) YES NULL
-MAX(t4) + 1 decimal(25,4) YES NULL
-MAX(t5) + 1 decimal(26,5) YES NULL
-MAX(t6) + 1 decimal(27,6) YES NULL
+MAX(t0) + 1 bigint(16) YES NULL
+MAX(t1) + 1 decimal(16,1) YES NULL
+MAX(t2) + 1 decimal(17,2) YES NULL
+MAX(t3) + 1 decimal(18,3) YES NULL
+MAX(t4) + 1 decimal(19,4) YES NULL
+MAX(t5) + 1 decimal(20,5) YES NULL
+MAX(t6) + 1 decimal(21,6) YES NULL
SELECT * FROM t2;
MAX(t0) + 1 MAX(t1) + 1 MAX(t2) + 1 MAX(t3) + 1 MAX(t4) + 1 MAX(t5) + 1 MAX(t6) + 1
20010101101011 20010101101011.9 20010101101011.99 20010101101011.999 20010101101011.9999 20010101101011.99999 20010101101011.999999
@@ -8060,13 +8060,13 @@ MAX(t4) + 1.1, MAX(t5) + 1.1, MAX(t6) +
FROM t1;
SHOW COLUMNS FROM t2;
Field Type Null Key Default Extra
-MAX(t0) + 1.1 decimal(21,1) YES NULL
-MAX(t1) + 1.1 decimal(22,1) YES NULL
-MAX(t2) + 1.1 decimal(23,2) YES NULL
-MAX(t3) + 1.1 decimal(24,3) YES NULL
-MAX(t4) + 1.1 decimal(25,4) YES NULL
-MAX(t5) + 1.1 decimal(26,5) YES NULL
-MAX(t6) + 1.1 decimal(27,6) YES NULL
+MAX(t0) + 1.1 decimal(16,1) YES NULL
+MAX(t1) + 1.1 decimal(16,1) YES NULL
+MAX(t2) + 1.1 decimal(17,2) YES NULL
+MAX(t3) + 1.1 decimal(18,3) YES NULL
+MAX(t4) + 1.1 decimal(19,4) YES NULL
+MAX(t5) + 1.1 decimal(20,5) YES NULL
+MAX(t6) + 1.1 decimal(21,6) YES NULL
SELECT * FROM t2;
MAX(t0) + 1.1 MAX(t1) + 1.1 MAX(t2) + 1.1 MAX(t3) + 1.1 MAX(t4) + 1.1 MAX(t5) + 1.1 MAX(t6) + 1.1
20010101101011.1 20010101101012.0 20010101101012.09 20010101101012.099 20010101101012.0999 20010101101012.09999 20010101101012.099999
@@ -8470,8 +8470,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `i` decimal(27,6) DEFAULT NULL,
- `d` decimal(27,6) DEFAULT NULL,
+ `i` decimal(21,6) DEFAULT NULL,
+ `d` decimal(21,6) DEFAULT NULL,
`f` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, t2;
@@ -8500,30 +8500,30 @@ FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `t0 + 1` bigint(21) DEFAULT NULL,
- `t3 + 1` decimal(24,3) DEFAULT NULL,
- `t6 + 1` decimal(27,6) DEFAULT NULL,
- `t0 - 1` bigint(21) DEFAULT NULL,
- `t3 - 1` decimal(24,3) DEFAULT NULL,
- `t6 - 1` decimal(27,6) DEFAULT NULL,
- `t0 * 1` bigint(21) DEFAULT NULL,
- `t3 * 1` decimal(24,3) DEFAULT NULL,
- `t6 * 1` decimal(27,6) DEFAULT NULL,
- `t0 / 1` decimal(23,4) DEFAULT NULL,
- `t3 / 1` decimal(27,7) DEFAULT NULL,
- `t6 / 1` decimal(30,10) DEFAULT NULL,
- `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(23,4) DEFAULT NULL,
- `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(27,7) DEFAULT NULL,
- `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(30,10) DEFAULT NULL
+ `t0 + 1` bigint(16) DEFAULT NULL,
+ `t3 + 1` decimal(18,3) DEFAULT NULL,
+ `t6 + 1` decimal(21,6) DEFAULT NULL,
+ `t0 - 1` bigint(16) DEFAULT NULL,
+ `t3 - 1` decimal(18,3) DEFAULT NULL,
+ `t6 - 1` decimal(21,6) DEFAULT NULL,
+ `t0 * 1` bigint(16) DEFAULT NULL,
+ `t3 * 1` decimal(18,3) DEFAULT NULL,
+ `t6 * 1` decimal(21,6) DEFAULT NULL,
+ `t0 / 1` decimal(18,4) DEFAULT NULL,
+ `t3 / 1` decimal(21,7) DEFAULT NULL,
+ `t6 / 1` decimal(24,10) DEFAULT NULL,
+ `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(16) NOT NULL DEFAULT '0',
+ `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(16) NOT NULL DEFAULT '0',
+ `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(16) NOT NULL DEFAULT '0',
+ `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(18,4) DEFAULT NULL,
+ `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(21,7) DEFAULT NULL,
+ `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(24,10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
@@ -8575,16 +8575,16 @@ Table Create Table
t2 CREATE TABLE `t2` (
`MIN(t0)` datetime DEFAULT NULL,
`MAX(t0)` datetime DEFAULT NULL,
- `AVG(t0)` decimal(23,4) DEFAULT NULL,
- `SUM(t0)` decimal(41,0) DEFAULT NULL,
+ `AVG(t0)` decimal(18,4) DEFAULT NULL,
+ `SUM(t0)` decimal(36,0) DEFAULT NULL,
`MIN(t3)` datetime(3) DEFAULT NULL,
`MAX(t3)` datetime(3) DEFAULT NULL,
- `AVG(t3)` decimal(27,7) DEFAULT NULL,
- `SUM(t3)` decimal(45,3) DEFAULT NULL,
+ `AVG(t3)` decimal(21,7) DEFAULT NULL,
+ `SUM(t3)` decimal(39,3) DEFAULT NULL,
`MIN(t6)` datetime(6) DEFAULT NULL,
`MAX(t6)` datetime(6) DEFAULT NULL,
- `AVG(t6)` decimal(30,10) DEFAULT NULL,
- `SUM(t6)` decimal(48,6) DEFAULT NULL
+ `AVG(t6)` decimal(24,10) DEFAULT NULL,
+ `SUM(t6)` decimal(42,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
@@ -13161,8 +13161,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `i` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `d` decimal(27,6) NOT NULL DEFAULT '0.000000',
+ `i` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `d` decimal(21,6) NOT NULL DEFAULT '0.000000',
`f` double NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, t2;
@@ -13191,30 +13191,30 @@ FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `t0 + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `t3 + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `t6 + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `t0 - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `t3 - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `t6 - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `t0 * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `t3 * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `t6 * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `t0 / 1` decimal(23,4) DEFAULT NULL,
- `t3 / 1` decimal(27,7) DEFAULT NULL,
- `t6 / 1` decimal(30,10) DEFAULT NULL,
- `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
- `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
- `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
- `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(23,4) DEFAULT NULL,
- `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(27,7) DEFAULT NULL,
- `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(30,10) DEFAULT NULL
+ `t0 + 1` bigint(16) NOT NULL DEFAULT '0',
+ `t3 + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `t6 + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `t0 - 1` bigint(16) NOT NULL DEFAULT '0',
+ `t3 - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `t6 - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `t0 * 1` bigint(16) NOT NULL DEFAULT '0',
+ `t3 * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `t6 * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `t0 / 1` decimal(18,4) DEFAULT NULL,
+ `t3 / 1` decimal(21,7) DEFAULT NULL,
+ `t6 / 1` decimal(24,10) DEFAULT NULL,
+ `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(16) NOT NULL DEFAULT '0',
+ `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(16) NOT NULL DEFAULT '0',
+ `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(16) NOT NULL DEFAULT '0',
+ `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+ `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+ `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(18,4) DEFAULT NULL,
+ `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(21,7) DEFAULT NULL,
+ `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(24,10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
@@ -13266,16 +13266,16 @@ Table Create Table
t2 CREATE TABLE `t2` (
`MIN(t0)` datetime DEFAULT NULL,
`MAX(t0)` datetime DEFAULT NULL,
- `AVG(t0)` decimal(23,4) DEFAULT NULL,
- `SUM(t0)` decimal(41,0) DEFAULT NULL,
+ `AVG(t0)` decimal(18,4) DEFAULT NULL,
+ `SUM(t0)` decimal(36,0) DEFAULT NULL,
`MIN(t3)` datetime(3) DEFAULT NULL,
`MAX(t3)` datetime(3) DEFAULT NULL,
- `AVG(t3)` decimal(27,7) DEFAULT NULL,
- `SUM(t3)` decimal(45,3) DEFAULT NULL,
+ `AVG(t3)` decimal(21,7) DEFAULT NULL,
+ `SUM(t3)` decimal(39,3) DEFAULT NULL,
`MIN(t6)` datetime(6) DEFAULT NULL,
`MAX(t6)` datetime(6) DEFAULT NULL,
- `AVG(t6)` decimal(30,10) DEFAULT NULL,
- `SUM(t6)` decimal(48,6) DEFAULT NULL
+ `AVG(t6)` decimal(24,10) DEFAULT NULL,
+ `SUM(t6)` decimal(42,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
=== modified file 'mysql-test/r/type_timestamp.result'
--- a/mysql-test/r/type_timestamp.result 2012-01-31 15:16:16 +0000
+++ b/mysql-test/r/type_timestamp.result 2012-02-23 16:32:32 +0000
@@ -622,3 +622,30 @@ MAX(dt) = '2011-01-06 12:34:30'
1
DROP TABLE t1;
End of 5.5 tests
+#
+# Start of 5.6 tests
+#
+#
+# Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
+#
+CREATE TABLE t1 (
+`c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+`c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
+);
+INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
+SELECT c2-c1 FROM t1;
+c2-c1
+-30389003988
+SELECT * FROM t1;
+c1 c2
+2003-05-16 23:53:29 2000-01-27 23:13:41
+SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29';
+TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29'
+-30389003988
+SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29');
+TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29')
+-30389003988
+DROP TABLE t1;
+#
+# End of 5.6 tests
+#
=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2012-02-21 10:31:44 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2012-02-23 11:22:34 +0000
@@ -1259,77 +1259,137 @@ ON table2 .col_int_key = table1 .col_int
"considered_execution_plans": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "usable": false,
- "chosen": false
- },
- {
"access_type": "scan",
- "rows": 4,
- "cost": 2.0068,
+ "rows": 6,
+ "cost": 2.019,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.8068,
- "rows_for_plan": 4,
+ "cost_for_plan": 3.219,
+ "rows_for_plan": 6,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 4.493,
+ "cost": 6.5395,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.2071,
+ "cost": 2.208,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 7.414,
- "rows_for_plan": 12,
+ "cost_for_plan": 9.0271,
+ "rows_for_plan": 18,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 18.819,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0209,
+ "rows": 3,
+ "cost": 2.2118,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 23.835,
- "rows_for_plan": 72,
+ "cost_for_plan": 22.039,
+ "rows_for_plan": 54,
"semijoin_strategy_choice": [
{
- "strategy": "DuplicatesWeedout",
- "cost": 41.435,
+ "strategy": "FirstMatch",
+ "recalculate_access_paths_and_cost": {
+ "tables": [
+ {
+ "database": "test",
+ "table": "table1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 6.5395,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 13.241,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ },
+ {
+ "database": "test",
+ "table": "table2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 12.679,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 26.482,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recalculate_access_paths_and_cost */,
+ "cost": 28.838,
"rows": 6,
- "duplicate_tables_left": true,
"chosen": true
+ },
+ {
+ "strategy": "MaterializeLookup",
+ "cost": 16.233,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": true
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 36.039,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": false
}
] /* semijoin_strategy_choice */,
"chosen": true
@@ -1338,53 +1398,119 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 6.5395,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0194,
+ "rows": 3,
+ "cost": 2.208,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.6262,
- "rows_for_plan": 24,
+ "cost_for_plan": 9.0271,
+ "rows_for_plan": 18,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 24.958,
+ "cost": 18.819,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.2135,
+ "cost": 2.2118,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 26.24,
- "rows_for_plan": 72,
+ "cost_for_plan": 22.039,
+ "rows_for_plan": 54,
"semijoin_strategy_choice": [
{
- "strategy": "DuplicatesWeedout",
- "cost": 43.84,
+ "strategy": "FirstMatch",
+ "recalculate_access_paths_and_cost": {
+ "tables": [
+ {
+ "database": "test",
+ "table": "table2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 6.5395,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 13.241,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ },
+ {
+ "database": "test",
+ "table": "table1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 12.679,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 26.482,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recalculate_access_paths_and_cost */,
+ "cost": 28.838,
"rows": 6,
- "duplicate_tables_left": true,
"chosen": true
+ },
+ {
+ "strategy": "MaterializeLookup",
+ "cost": 16.233,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": true
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 36.039,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": false
}
] /* semijoin_strategy_choice */,
"pruned_by_cost": true
@@ -1395,14 +1521,13 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
- "rows": 2e308,
- "cost": 0,
+ "usable": false,
"chosen": false
},
{
@@ -1420,50 +1545,50 @@ ON table2 .col_int_key = table1 .col_int
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 4.493,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.2071,
+ "rows": 6,
+ "cost": 2.0194,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 7.414,
- "rows_for_plan": 12,
+ "cost_for_plan": 9.6262,
+ "rows_for_plan": 24,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 24.958,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0209,
+ "rows": 3,
+ "cost": 2.2135,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 23.835,
+ "cost_for_plan": 26.24,
"rows_for_plan": 72,
"semijoin_strategy_choice": [
{
"strategy": "DuplicatesWeedout",
- "cost": 41.435,
+ "cost": 43.84,
"rows": 6,
"duplicate_tables_left": true,
"chosen": true
@@ -1475,50 +1600,50 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 4.493,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0194,
+ "rows": 3,
+ "cost": 2.2071,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.6262,
- "rows_for_plan": 24,
+ "cost_for_plan": 7.414,
+ "rows_for_plan": 12,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 24.958,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.2135,
+ "rows": 6,
+ "cost": 2.0209,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 26.24,
+ "cost_for_plan": 23.835,
"rows_for_plan": 72,
"semijoin_strategy_choice": [
{
"strategy": "DuplicatesWeedout",
- "cost": 43.84,
+ "cost": 41.435,
"rows": 6,
"duplicate_tables_left": true,
"chosen": true
@@ -1532,258 +1657,133 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2e308,
+ "cost": 0,
+ "chosen": false
+ },
+ {
"access_type": "scan",
- "rows": 6,
- "cost": 2.019,
+ "rows": 4,
+ "cost": 2.0068,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 3.219,
- "rows_for_plan": 6,
+ "cost_for_plan": 2.8068,
+ "rows_for_plan": 4,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 6.5395,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.208,
+ "rows": 6,
+ "cost": 2.0194,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.0271,
- "rows_for_plan": 18,
+ "cost_for_plan": 9.6262,
+ "rows_for_plan": 24,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 18.819,
+ "cost": 24.958,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.2118,
+ "cost": 2.2135,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 22.039,
- "rows_for_plan": 54,
+ "cost_for_plan": 26.24,
+ "rows_for_plan": 72,
"semijoin_strategy_choice": [
{
- "strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "database": "test",
- "table": "table1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 6.5395,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 13.241,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- },
- {
- "database": "test",
- "table": "table2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 12.679,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 26.482,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */,
- "cost": 28.838,
- "rows": 6,
- "chosen": true
- },
- {
- "strategy": "MaterializeLookup",
- "cost": 16.233,
- "rows": 6,
- "duplicate_tables_left": false,
- "chosen": true
- },
- {
"strategy": "DuplicatesWeedout",
- "cost": 36.039,
+ "cost": 43.84,
"rows": 6,
- "duplicate_tables_left": false,
- "chosen": false
+ "duplicate_tables_left": true,
+ "chosen": true
}
] /* semijoin_strategy_choice */,
- "chosen": true
+ "pruned_by_cost": true
}
] /* rest_of_plan */
},
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 6.5395,
+ "cost": 4.493,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.208,
+ "cost": 2.2071,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.0271,
- "rows_for_plan": 18,
+ "cost_for_plan": 7.414,
+ "rows_for_plan": 12,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 18.819,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.2118,
+ "rows": 6,
+ "cost": 2.0209,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 22.039,
- "rows_for_plan": 54,
+ "cost_for_plan": 23.835,
+ "rows_for_plan": 72,
"semijoin_strategy_choice": [
{
- "strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "database": "test",
- "table": "table2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 6.5395,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 13.241,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- },
- {
- "database": "test",
- "table": "table1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 12.679,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 26.482,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */,
- "cost": 28.838,
- "rows": 6,
- "chosen": true
- },
- {
- "strategy": "MaterializeLookup",
- "cost": 16.233,
- "rows": 6,
- "duplicate_tables_left": false,
- "chosen": true
- },
- {
"strategy": "DuplicatesWeedout",
- "cost": 36.039,
+ "cost": 41.435,
"rows": 6,
- "duplicate_tables_left": false,
- "chosen": false
+ "duplicate_tables_left": true,
+ "chosen": true
}
] /* semijoin_strategy_choice */,
"pruned_by_cost": true
=== modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2012-02-21 10:31:44 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2012-02-23 11:22:34 +0000
@@ -1259,77 +1259,137 @@ ON table2 .col_int_key = table1 .col_int
"considered_execution_plans": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "usable": false,
- "chosen": false
- },
- {
"access_type": "scan",
- "rows": 4,
- "cost": 2.0068,
+ "rows": 6,
+ "cost": 2.019,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.8068,
- "rows_for_plan": 4,
+ "cost_for_plan": 3.219,
+ "rows_for_plan": 6,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 4.493,
+ "cost": 6.5395,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.2071,
+ "cost": 2.208,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 7.414,
- "rows_for_plan": 12,
+ "cost_for_plan": 9.0271,
+ "rows_for_plan": 18,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 18.819,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0209,
+ "rows": 3,
+ "cost": 2.2118,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 23.835,
- "rows_for_plan": 72,
+ "cost_for_plan": 22.039,
+ "rows_for_plan": 54,
"semijoin_strategy_choice": [
{
- "strategy": "DuplicatesWeedout",
- "cost": 41.435,
+ "strategy": "FirstMatch",
+ "recalculate_access_paths_and_cost": {
+ "tables": [
+ {
+ "database": "test",
+ "table": "table1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 6.5395,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 13.241,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ },
+ {
+ "database": "test",
+ "table": "table2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 12.679,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 26.482,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recalculate_access_paths_and_cost */,
+ "cost": 28.838,
"rows": 6,
- "duplicate_tables_left": true,
"chosen": true
+ },
+ {
+ "strategy": "MaterializeLookup",
+ "cost": 16.233,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": true
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 36.039,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": false
}
] /* semijoin_strategy_choice */,
"chosen": true
@@ -1338,53 +1398,119 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 6.5395,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0194,
+ "rows": 3,
+ "cost": 2.208,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.6262,
- "rows_for_plan": 24,
+ "cost_for_plan": 9.0271,
+ "rows_for_plan": 18,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 24.958,
+ "cost": 18.819,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.2135,
+ "cost": 2.2118,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 26.24,
- "rows_for_plan": 72,
+ "cost_for_plan": 22.039,
+ "rows_for_plan": 54,
"semijoin_strategy_choice": [
{
- "strategy": "DuplicatesWeedout",
- "cost": 43.84,
+ "strategy": "FirstMatch",
+ "recalculate_access_paths_and_cost": {
+ "tables": [
+ {
+ "database": "test",
+ "table": "table2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 6.5395,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 13.241,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ },
+ {
+ "database": "test",
+ "table": "table1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 12.679,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "rows": 3,
+ "cost": 26.482,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recalculate_access_paths_and_cost */,
+ "cost": 28.838,
"rows": 6,
- "duplicate_tables_left": true,
"chosen": true
+ },
+ {
+ "strategy": "MaterializeLookup",
+ "cost": 16.233,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": true
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 36.039,
+ "rows": 6,
+ "duplicate_tables_left": false,
+ "chosen": false
}
] /* semijoin_strategy_choice */,
"pruned_by_cost": true
@@ -1395,14 +1521,13 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
- "rows": 2e308,
- "cost": 0,
+ "usable": false,
"chosen": false
},
{
@@ -1420,50 +1545,50 @@ ON table2 .col_int_key = table1 .col_int
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 4.493,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.2071,
+ "rows": 6,
+ "cost": 2.0194,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 7.414,
- "rows_for_plan": 12,
+ "cost_for_plan": 9.6262,
+ "rows_for_plan": 24,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 24.958,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0209,
+ "rows": 3,
+ "cost": 2.2135,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 23.835,
+ "cost_for_plan": 26.24,
"rows_for_plan": 72,
"semijoin_strategy_choice": [
{
"strategy": "DuplicatesWeedout",
- "cost": 41.435,
+ "cost": 43.84,
"rows": 6,
"duplicate_tables_left": true,
"chosen": true
@@ -1475,50 +1600,50 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2,
+ "cost": 4.493,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
- "rows": 6,
- "cost": 2.0194,
+ "rows": 3,
+ "cost": 2.2071,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.6262,
- "rows_for_plan": 24,
+ "cost_for_plan": 7.414,
+ "rows_for_plan": 12,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 24.958,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.2135,
+ "rows": 6,
+ "cost": 2.0209,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 26.24,
+ "cost_for_plan": 23.835,
"rows_for_plan": 72,
"semijoin_strategy_choice": [
{
"strategy": "DuplicatesWeedout",
- "cost": 43.84,
+ "cost": 41.435,
"rows": 6,
"duplicate_tables_left": true,
"chosen": true
@@ -1532,258 +1657,133 @@ ON table2 .col_int_key = table1 .col_int
},
{
"database": "test",
- "table": "where_subselect_20070",
+ "table": "table2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_int_key",
+ "rows": 2e308,
+ "cost": 0,
+ "chosen": false
+ },
+ {
"access_type": "scan",
- "rows": 6,
- "cost": 2.019,
+ "rows": 4,
+ "cost": 2.0068,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 3.219,
- "rows_for_plan": 6,
+ "cost_for_plan": 2.8068,
+ "rows_for_plan": 4,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 6.5395,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.208,
+ "rows": 6,
+ "cost": 2.0194,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.0271,
- "rows_for_plan": 18,
+ "cost_for_plan": 9.6262,
+ "rows_for_plan": 24,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 18.819,
+ "cost": 24.958,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.2118,
+ "cost": 2.2135,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 22.039,
- "rows_for_plan": 54,
+ "cost_for_plan": 26.24,
+ "rows_for_plan": 72,
"semijoin_strategy_choice": [
{
- "strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "database": "test",
- "table": "table1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 6.5395,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 13.241,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- },
- {
- "database": "test",
- "table": "table2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 12.679,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 26.482,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */,
- "cost": 28.838,
- "rows": 6,
- "chosen": true
- },
- {
- "strategy": "MaterializeLookup",
- "cost": 16.233,
- "rows": 6,
- "duplicate_tables_left": false,
- "chosen": true
- },
- {
"strategy": "DuplicatesWeedout",
- "cost": 36.039,
+ "cost": 43.84,
"rows": 6,
- "duplicate_tables_left": false,
- "chosen": false
+ "duplicate_tables_left": true,
+ "chosen": true
}
] /* semijoin_strategy_choice */,
- "chosen": true
+ "pruned_by_cost": true
}
] /* rest_of_plan */
},
{
"database": "test",
- "table": "table2",
+ "table": "table1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "col_int_key",
"rows": 2,
- "cost": 6.5395,
+ "cost": 4.493,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.208,
+ "cost": 2.2071,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 9.0271,
- "rows_for_plan": 18,
+ "cost_for_plan": 7.414,
+ "rows_for_plan": 12,
"semijoin_strategy_choice": [
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
"database": "test",
- "table": "table1",
+ "table": "where_subselect_20070",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 18.819,
- "chosen": true
- },
- {
"access_type": "scan",
"using_join_cache": true,
- "rows": 3,
- "cost": 2.2118,
+ "rows": 6,
+ "cost": 2.0209,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 22.039,
- "rows_for_plan": 54,
+ "cost_for_plan": 23.835,
+ "rows_for_plan": 72,
"semijoin_strategy_choice": [
{
- "strategy": "FirstMatch",
- "recalculate_access_paths_and_cost": {
- "tables": [
- {
- "database": "test",
- "table": "table2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 6.5395,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 13.241,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- },
- {
- "database": "test",
- "table": "table1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_int_key",
- "rows": 2,
- "cost": 12.679,
- "chosen": true
- },
- {
- "access_type": "scan",
- "rows": 3,
- "cost": 26.482,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recalculate_access_paths_and_cost */,
- "cost": 28.838,
- "rows": 6,
- "chosen": true
- },
- {
- "strategy": "MaterializeLookup",
- "cost": 16.233,
- "rows": 6,
- "duplicate_tables_left": false,
- "chosen": true
- },
- {
"strategy": "DuplicatesWeedout",
- "cost": 36.039,
+ "cost": 41.435,
"rows": 6,
- "duplicate_tables_left": false,
- "chosen": false
+ "duplicate_tables_left": true,
+ "chosen": true
}
] /* semijoin_strategy_choice */,
"pruned_by_cost": true
=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2012-02-16 13:09:08 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2012-02-23 11:22:34 +0000
@@ -1563,16 +1563,10 @@ field4,field5,field6 {
"considered_execution_plans": [
{
"database": "test",
- "table": "sq1_alias2",
+ "table": "sq1_alias3",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_varchar_key",
- "usable": false,
- "chosen": false
- },
- {
"access_type": "scan",
"rows": 3,
"cost": 2.0342,
@@ -1585,14 +1579,21 @@ field4,field5,field6 {
"rest_of_plan": [
{
"database": "test",
- "table": "sq1_alias3",
+ "table": "sq1_alias2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_varchar_key",
+ "rows": 1,
+ "cost": 4.1,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.0345,
+ "cost": 2.0344,
"chosen": true
}
] /* considered_access_paths */
@@ -1605,10 +1606,16 @@ field4,field5,field6 {
},
{
"database": "test",
- "table": "sq1_alias3",
+ "table": "sq1_alias2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_varchar_key",
+ "usable": false,
+ "chosen": false
+ },
+ {
"access_type": "scan",
"rows": 3,
"cost": 2.0342,
@@ -1618,33 +1625,7 @@ field4,field5,field6 {
} /* best_access_path */,
"cost_for_plan": 2.6342,
"rows_for_plan": 3,
- "rest_of_plan": [
- {
- "database": "test",
- "table": "sq1_alias2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_varchar_key",
- "rows": 1,
- "cost": 4.1,
- "chosen": true
- },
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0344,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 6.4686,
- "rows_for_plan": 9,
- "pruned_by_cost": true
- }
- ] /* rest_of_plan */
+ "pruned_by_heuristic": true
}
] /* considered_execution_plans */
}
=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2012-02-16 13:09:08 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2012-02-23 11:22:34 +0000
@@ -1541,16 +1541,10 @@ field4,field5,field6 {
"considered_execution_plans": [
{
"database": "test",
- "table": "sq1_alias2",
+ "table": "sq1_alias3",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "ref",
- "index": "col_varchar_key",
- "usable": false,
- "chosen": false
- },
- {
"access_type": "scan",
"rows": 3,
"cost": 2.0342,
@@ -1563,14 +1557,21 @@ field4,field5,field6 {
"rest_of_plan": [
{
"database": "test",
- "table": "sq1_alias3",
+ "table": "sq1_alias2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_varchar_key",
+ "rows": 1,
+ "cost": 4.1,
+ "chosen": true
+ },
+ {
"access_type": "scan",
"using_join_cache": true,
"rows": 3,
- "cost": 2.0345,
+ "cost": 2.0344,
"chosen": true
}
] /* considered_access_paths */
@@ -1583,10 +1584,16 @@ field4,field5,field6 {
},
{
"database": "test",
- "table": "sq1_alias3",
+ "table": "sq1_alias2",
"best_access_path": {
"considered_access_paths": [
{
+ "access_type": "ref",
+ "index": "col_varchar_key",
+ "usable": false,
+ "chosen": false
+ },
+ {
"access_type": "scan",
"rows": 3,
"cost": 2.0342,
@@ -1596,33 +1603,7 @@ field4,field5,field6 {
} /* best_access_path */,
"cost_for_plan": 2.6342,
"rows_for_plan": 3,
- "rest_of_plan": [
- {
- "database": "test",
- "table": "sq1_alias2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "col_varchar_key",
- "rows": 1,
- "cost": 4.1,
- "chosen": true
- },
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 3,
- "cost": 2.0344,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 6.4686,
- "rows_for_plan": 9,
- "pruned_by_cost": true
- }
- ] /* rest_of_plan */
+ "pruned_by_heuristic": true
}
] /* considered_execution_plans */
}
=== modified file 'mysql-test/suite/parts/t/partition_alter4_myisam.test'
--- a/mysql-test/suite/parts/t/partition_alter4_myisam.test 2010-06-17 17:17:17 +0000
+++ b/mysql-test/suite/parts/t/partition_alter4_myisam.test 2012-02-23 11:51:35 +0000
@@ -45,6 +45,8 @@ let $more_pk_ui_tests= 0;
# Does not work with --embedded
--source include/not_embedded.inc
+# This test takes long time, so only run it with the --big mtr-flag.
+--source include/big_test.inc
#------------------------------------------------------------------------------#
# Engine specific settings and requirements
=== modified file 'mysql-test/suite/rpl/t/rpl_row_event_max_size.test'
--- a/mysql-test/suite/rpl/t/rpl_row_event_max_size.test 2012-01-30 15:01:17 +0000
+++ b/mysql-test/suite/rpl/t/rpl_row_event_max_size.test 2012-02-22 21:27:15 +0000
@@ -59,6 +59,9 @@ connection master;
CREATE TABLE t1 (a int not null auto_increment, data1 LONGBLOB,
data2 LONGBLOB, data3 LONGBLOB, PRIMARY KEY(a));
+--sync_slave_with_master
+--connection master
+
INSERT INTO t1 (data1, data2, data3) VALUES (repeat('a',1000000), repeat('a', 1000000), repeat('a', 1000000));
SELECT LENGTH(data1), LENGTH(data2), LENGTH(data3) FROM t1 WHERE a = 1;
=== modified file 'mysql-test/t/disabled.def'
--- a/mysql-test/t/disabled.def 2012-02-22 16:21:18 +0000
+++ b/mysql-test/t/disabled.def 2012-02-23 05:57:45 +0000
@@ -14,3 +14,4 @@ read_many_rows_innodb : Bug#11748886
sum_distinct-big : Bug#11764126 2010-11-15 mattiasj was not tested
archive-big : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc
log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists
+events_restart @windows : Bug#11748899 2012-02-23 hemant The test started failing on windows after the fix for bug#11748899 and followup patch didnot work.
=== modified file 'mysql-test/t/func_str.test'
--- a/mysql-test/t/func_str.test 2012-01-25 15:49:57 +0000
+++ b/mysql-test/t/func_str.test 2012-02-23 10:08:33 +0000
@@ -1688,5 +1688,28 @@ SET NAMES latin2;
EXECUTE stmt;
--echo #
+--echo # Bug#11829861: SUBSTRING_INDEX() RESULTS IN MISSING CHARACTERS WHEN USED
+--echo # INSIDE LOWER()
+--echo #
+
+SET @user_at_host = 'root@stripped';
+SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
+
+--echo # End of test BUG#11829861
+
+--echo #
+--echo # Bug#42404: SUBSTRING_INDEX() RESULTS ARE INCONSISTENT
+--echo #
+
+CREATE TABLE t (i INT NOT NULL, c CHAR(255) NOT NULL);
+INSERT INTO t VALUES (0,'.www.mysql.com'),(1,'.wwwmysqlcom');
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t WHERE i = 1;
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t;
+
+DROP TABLE t;
+
+--echo # End of test BUG#42404
+
+--echo #
--echo # End of 5.6 tests
--echo #
=== added file 'mysql-test/t/greedy_search.test'
--- a/mysql-test/t/greedy_search.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/greedy_search.test 2012-02-23 11:22:34 +0000
@@ -0,0 +1,423 @@
+--echo #
+--echo # TEST 1
+--echo # Greedy search iteration test for 16-way join: star schema
+--echo #
+--echo # Creation of 16 tables hidden
+--echo #
+
+--disable_result_log
+--disable_query_log
+
+let $brands=7;
+let $models_pr_brand=5;
+let $misc_properties_big=20;
+let $misc_properties_small=10;
+let $vehicles=100;
+
+eval
+CREATE TABLE brands (
+ id_pk int PRIMARY KEY,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$brands)
+{
+ inc $i;
+ eval INSERT INTO brands VALUES ($i, $i, concat('brand',$i));
+}
+
+eval
+CREATE TABLE models (
+ id_pk int PRIMARY KEY,
+ id_nokey int,
+ brand_id int,
+ name varchar(100),
+ INDEX(`brand_id`)
+);
+
+let $i=0;
+let $cnt=0;
+while ($i<$brands)
+{
+ inc $i;
+ let $j=0;
+ while ($j<$models_pr_brand)
+ {
+ inc $cnt;
+ inc $j;
+ eval INSERT INTO models VALUES ($cnt, $cnt, $i, concat('brandmodel',$cnt));
+ }
+}
+
+eval
+CREATE TABLE subtypes (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO subtypes VALUES ($i, $i, concat('subtype',$i));
+}
+
+eval
+CREATE TABLE colors (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_big)
+{
+ inc $i;
+ eval INSERT INTO colors VALUES ($i, $i, concat('color',$i));
+}
+
+eval
+CREATE TABLE heating (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO heating VALUES ($i, $i, concat('heating',$i));
+}
+
+eval
+CREATE TABLE windows (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO windows VALUES ($i, $i, concat('window',$i));
+}
+
+eval
+CREATE TABLE fuels (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO fuels VALUES ($i, $i, concat('fuel',$i));
+}
+
+eval
+CREATE TABLE transmissions (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO transmissions VALUES ($i, $i, concat('transmission',$i));
+}
+
+eval
+CREATE TABLE steerings (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO steerings VALUES ($i, $i, concat('steering',$i));
+}
+
+eval
+CREATE TABLE interiors (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_big)
+{
+ inc $i;
+ eval INSERT INTO interiors VALUES ($i, $i, concat('interior',$i));
+}
+
+eval
+CREATE TABLE drives (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO drives VALUES ($i, $i, concat('drive',$i));
+}
+
+eval
+CREATE TABLE wheels (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO wheels VALUES ($i, $i, concat('wheel',$i));
+}
+
+eval
+CREATE TABLE engine (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO engine VALUES ($i, $i, concat('engine',$i));
+}
+
+eval
+CREATE TABLE price_ranges (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+ inc $i;
+ eval INSERT INTO price_ranges VALUES ($i, $i, concat('price',$i));
+}
+
+eval
+CREATE TABLE countries (
+ id_pk int primary key,
+ id_nokey int,
+ name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_big)
+{
+ inc $i;
+ eval INSERT INTO countries VALUES ($i, $i, concat('country',$i));
+}
+
+eval
+CREATE TABLE vehicles (
+ id int primary key,
+ model_id int,
+ subtype_id int,
+ color_id int,
+ heating_id int,
+ window_id int,
+ fuel_id int,
+ transmission_id int,
+ steering_id int,
+ interior_id int,
+ drive_id int,
+ price_range_id int,
+ assembled_in int,
+ engine_id int,
+ wheels_id int
+);
+
+
+let $brands=7;
+let $models_pr_brand=3;
+let $misc_properties_big=20;
+let $misc_properties_small=$misc_properties_big/2;
+
+while ($i<$vehicles)
+{
+ inc $i;
+ eval INSERT INTO vehicles VALUES ($i, $i%2, $i%3, $i%4, $i%5, $i%6, $i%7,
+ $i, $i%2, $i%3, $i%4, $i%5, $i%6, $i%7,
+ $i );
+}
+--enable_result_log
+--enable_query_log
+
+SET SESSION optimizer_search_depth = 25;
+# print_greedy_search_count will do it's own FLUSH STATUS after
+# executing each query, but we need to reset counters before
+# the first query is executed as well.
+FLUSH STATUS;
+
+--echo #
+--echo # 16-way join - all 15 fact tables joined on column with key
+--echo #
+
+let $query=
+SELECT *
+FROM vehicles
+ JOIN models ON vehicles.model_id = models.id_pk
+ JOIN subtypes ON vehicles.subtype_id = subtypes.id_pk
+ JOIN colors ON vehicles.color_id = colors.id_pk
+ JOIN heating ON vehicles.heating_id = heating.id_pk
+ JOIN windows ON vehicles.window_id = windows.id_pk
+ JOIN fuels ON vehicles.fuel_id = fuels.id_pk
+ JOIN transmissions ON vehicles.transmission_id = transmissions.id_pk
+ JOIN steerings ON vehicles.steering_id = steerings.id_pk
+ JOIN interiors ON vehicles.interior_id = interiors.id_pk
+ JOIN drives ON vehicles.drive_id = drives.id_pk
+ JOIN wheels ON vehicles.wheels_id = wheels.id_pk
+ JOIN engine ON vehicles.engine_id = engine.id_pk
+ JOIN price_ranges ON vehicles.price_range_id = price_ranges.id_pk
+ JOIN countries ON vehicles.assembled_in = countries.id_pk
+ JOIN brands ON models.brand_id = brands.id_pk;
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # 16-way join - 10 fact tables joined on column with key and
+--echo # 5 fact tables joined on column without key
+--echo #
+
+let $query=
+SELECT *
+FROM vehicles
+ JOIN models ON vehicles.model_id = models.id_nokey
+ JOIN subtypes ON vehicles.subtype_id = subtypes.id_pk
+ JOIN colors ON vehicles.color_id = colors.id_pk
+ JOIN heating ON vehicles.heating_id = heating.id_nokey
+ JOIN windows ON vehicles.window_id = windows.id_pk
+ JOIN fuels ON vehicles.fuel_id = fuels.id_pk
+ JOIN transmissions ON vehicles.transmission_id = transmissions.id_nokey
+ JOIN steerings ON vehicles.steering_id = steerings.id_pk
+ JOIN interiors ON vehicles.interior_id = interiors.id_pk
+ JOIN drives ON vehicles.drive_id = drives.id_pk
+ JOIN wheels ON vehicles.wheels_id = wheels.id_nokey
+ JOIN engine ON vehicles.engine_id = engine.id_pk
+ JOIN price_ranges ON vehicles.price_range_id = price_ranges.id_pk
+ JOIN countries ON vehicles.assembled_in = countries.id_pk
+ JOIN brands ON models.brand_id = brands.id_nokey;
+--source include/print_greedy_search_count.inc
+
+select @@optimizer_search_depth;
+select @@optimizer_prune_level;
+
+DROP TABLE vehicles, models, subtypes, colors, heating, windows,
+ fuels, transmissions, steerings, interiors, drives,
+ price_ranges, countries, brands, wheels, engine;
+
+
+--echo #
+--echo # TEST 2
+--echo # Greedy search iteration test for chain of tables
+--echo #
+
+--source include/greedy_search_load_tables.inc
+
+# Explanation to the test
+#
+# A chain of tables is joined like this:
+# t1 JOIN t2 ON t1.<some_col>=t2.<some_col> JOIN t3 ON ...
+#
+# Different variants of table sizes and columns in the join conditions
+# are tested.
+#
+# The column names mean:
+# 'pk' - The column is primary key
+# 'colidx' - The column is indexed
+# 'col' - The column is not indexed
+#
+# The table names mean:
+# tx_y - table with x rows, y is simply used to get unique table names
+#
+# A comment explains each test. The notation used is
+# (...,tx_col_next):(ty_col_prev,...)
+# which means that table x is joined with table y with join condition
+# "ON tx.col_next = ty.col_prev" like this:
+# t1 JOIN t2 ON t1.col_next=t2.col_prev ...
+
+--echo #
+--echo # Chain test a: colidx):(pk,colidx):(pk,colidx)
+--echo #
+
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+ let $query= $query JOIN t100_$i ON t10_$i.colidx = t100_$i.pk;
+ let $j=$i;
+ inc $j;
+ let $query= $query JOIN t10_$j ON t100_$i.colidx = t10_$j.pk;
+ inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Chain test b: (...,col):(colidx, col):(pk,col):(colidx,col):(pk,...)
+--echo #
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+ let $query= $query JOIN t100_$i ON t10_$i.col = t100_$i.colidx;
+ let $j=$i;
+ inc $j;
+ let $query= $query JOIN t10_$j ON t100_$i.col = t10_$j.pk;
+ inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Chain test c: (...,colidx):(col, pk):(col,colidx):(col,...)
+--echo #
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+ let $query= $query JOIN t100_$i ON t10_$i.colidx = t100_$i.col;
+ let $j=$i;
+ inc $j;
+ let $query= $query JOIN t10_$j ON t100_$i.pk = t10_$j.col;
+ inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Chain test d: (...,colidx):(pk, col):(pk,colidx):(pk,col):(pk,...)
+--echo #
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+ let $query= $query JOIN t100_$i ON t10_$i.colidx = t100_$i.pk;
+ let $j=$i;
+ inc $j;
+ let $query= $query JOIN t10_$j ON t100_$i.col = t10_$j.pk;
+ inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Cleanup after TEST 2
+--echo #
+
+--source include/greedy_search_drop_tables.inc
=== modified file 'mysql-test/t/mysql_client_test_embedded.test'
--- a/mysql-test/t/mysql_client_test_embedded.test 2011-12-05 10:11:58 +0000
+++ b/mysql-test/t/mysql_client_test_embedded.test 2012-02-23 10:39:15 +0000
@@ -2,7 +2,7 @@
--echo # Run the start/stop test 17 times (* 64 = 1088 restarts)
--echo # --silent to avoid printing out server version etc.
---exec $MYSQL_CLIENT_TEST --silent --count=17 test_embedded_start_stop 2>&1
+--exec $MYSQL_CLIENT_TEST --silent --count=17 test_embedded_start_stop > $MYSQLTEST_VARDIR/log/test_embedded_start_stop.log 2>&1
# TODO: Test all other tests once.
# (Currently not done, since many fail with embedded server).
-#--exec $MYSQL_CLIENT_TEST 2>&1
+#--exec $MYSQL_CLIENT_TEST > $MYSQLTEST_VARDIR/log/mysql_client_test_embedded.log 2>&1
=== modified file 'mysql-test/t/type_timestamp.test'
--- a/mysql-test/t/type_timestamp.test 2012-01-31 15:16:16 +0000
+++ b/mysql-test/t/type_timestamp.test 2012-02-23 16:32:32 +0000
@@ -426,3 +426,26 @@ DROP TABLE t1;
--echo End of 5.5 tests
+
+--echo #
+--echo # Start of 5.6 tests
+--echo #
+
+--echo #
+--echo # Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
+--echo #
+CREATE TABLE t1 (
+ `c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
+);
+INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
+SELECT c2-c1 FROM t1;
+SELECT * FROM t1;
+SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29';
+SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29');
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 5.6 tests
+--echo #
=== modified file 'sql/field.cc'
--- a/sql/field.cc 2012-02-13 06:26:52 +0000
+++ b/sql/field.cc 2012-02-23 16:32:32 +0000
@@ -5144,8 +5144,6 @@ Field_temporal_with_date_and_time::conve
void Field_temporal_with_date_and_time::init_timestamp_flags()
{
- /* For 4.0 MYD and 4.0 InnoDB compatibility */
- flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | BINARY_FLAG;
if (unireg_check != NONE)
{
/*
@@ -5243,6 +5241,8 @@ Field_timestamp::Field_timestamp(uchar *
unireg_check_arg, field_name_arg, 0)
{
init_timestamp_flags();
+ /* For 4.0 MYD and 4.0 InnoDB compatibility */
+ flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
}
@@ -5253,6 +5253,8 @@ Field_timestamp::Field_timestamp(bool ma
NONE, field_name_arg, 0)
{
init_timestamp_flags();
+ /* For 4.0 MYD and 4.0 InnoDB compatibility */
+ flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
}
@@ -5429,8 +5431,6 @@ Field_timestampf::Field_timestampf(bool
maybe_null_arg ? (uchar*) "": 0, 0,
NONE, field_name_arg, dec_arg)
{
- /* For 4.0 MYD and 4.0 InnoDB compatibility */
- flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | BINARY_FLAG;
if (unireg_check != TIMESTAMP_DN_FIELD)
flags|= ON_UPDATE_NOW_FLAG;
}
@@ -9742,6 +9742,9 @@ bool Create_field::init(THD *thd, char *
}
break;
case MYSQL_TYPE_TIMESTAMP:
+ /* Add flags for TIMESTAMP for 4.0 MYD and 4.0 InnoDB compatibility */
+ flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+ /* Fall through */
case MYSQL_TYPE_TIMESTAMP2:
if (fld_length == NULL)
{
@@ -9759,7 +9762,7 @@ bool Create_field::init(THD *thd, char *
length= ((length+1)/2)*2;
length= min<ulong>(length, MAX_DATETIME_COMPRESSED_WIDTH);
}
- flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+
/*
Since we silently rewrite down to MAX_DATETIME_COMPRESSED_WIDTH bytes,
the parser should not raise errors unless bizzarely large.
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2012-02-16 11:31:37 +0000
+++ b/sql/item.cc 2012-02-23 16:32:32 +0000
@@ -617,6 +617,18 @@ uint Item::decimal_precision() const
unsigned_flag);
return min<uint>(prec, DECIMAL_MAX_PRECISION);
}
+ switch (field_type())
+ {
+ case MYSQL_TYPE_TIME:
+ return decimals + TIME_INT_DIGITS;
+ case MYSQL_TYPE_DATETIME:
+ case MYSQL_TYPE_TIMESTAMP:
+ return decimals + DATETIME_INT_DIGITS;
+ case MYSQL_TYPE_DATE:
+ return decimals + DATE_INT_DIGITS;
+ default:
+ break;
+ }
return min<uint>(max_char_length(), DECIMAL_MAX_PRECISION);
}
=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc 2012-02-17 13:55:18 +0000
+++ b/sql/item_func.cc 2012-02-23 16:32:32 +0000
@@ -596,6 +596,7 @@ void Item_func_numhybrid::fix_num_length
*/
void Item_func::count_datetime_length(Item **item, uint nitems)
{
+ unsigned_flag= 0;
decimals= 0;
if (field_type() != MYSQL_TYPE_DATE)
{
@@ -611,16 +612,13 @@ void Item_func::count_datetime_length(It
case MYSQL_TYPE_DATETIME:
case MYSQL_TYPE_TIMESTAMP:
len+= MAX_DATETIME_WIDTH;
- unsigned_flag= 1;
break;
case MYSQL_TYPE_DATE:
case MYSQL_TYPE_NEWDATE:
len+= MAX_DATE_WIDTH;
- unsigned_flag= 1;
break;
case MYSQL_TYPE_TIME:
len+= MAX_TIME_WIDTH;
- unsigned_flag= 0;
break;
default:
DBUG_ASSERT(0);
=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc 2012-02-22 08:57:27 +0000
+++ b/sql/item_strfunc.cc 2012-02-23 10:08:33 +0000
@@ -1566,10 +1566,12 @@ void Item_func_substr_index::fix_length_
String *Item_func_substr_index::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
+ char buff[MAX_FIELD_WIDTH];
+ String tmp(buff,sizeof(buff),system_charset_info);
String *res= args[0]->val_str(str);
- String *delimiter= args[1]->val_str(&tmp_value);
+ String *delimiter= args[1]->val_str(&tmp);
int32 count= (int32) args[2]->val_int();
- uint offset;
+ int offset;
if (args[0]->null_value || args[1]->null_value || args[2]->null_value)
{ // string and/or delim are null
@@ -1640,7 +1642,7 @@ String *Item_func_substr_index::val_str(
{ // start counting from the beginning
for (offset=0; ; offset+= delimiter_length)
{
- if ((int) (offset= res->strstr(*delimiter, offset)) < 0)
+ if ((offset= res->strstr(*delimiter, offset)) < 0)
return res; // Didn't find, return org string
if (!--count)
{
@@ -1654,14 +1656,14 @@ String *Item_func_substr_index::val_str(
/*
Negative index, start counting at the end
*/
- for (offset=res->length(); offset ;)
+ for (offset=res->length(); offset; )
{
/*
this call will result in finding the position pointing to one
address space less than where the found substring is located
in res
*/
- if ((int) (offset= res->strrstr(*delimiter, offset)) < 0)
+ if ((offset= res->strrstr(*delimiter, offset)) < 0)
return res; // Didn't find, return org string
/*
At this point, we've searched for the substring
@@ -1674,14 +1676,10 @@ String *Item_func_substr_index::val_str(
break;
}
}
+ if (count)
+ return res; // Didn't find, return org string
}
}
- /*
- We always mark tmp_value as const so that if val_str() is called again
- on this object, we don't disrupt the contents of tmp_value when it was
- derived from another String.
- */
- tmp_value.mark_as_const();
return (&tmp_value);
}
=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc 2012-02-16 09:51:14 +0000
+++ b/sql/item_timefunc.cc 2012-02-23 16:32:32 +0000
@@ -2235,11 +2235,9 @@ void Item_date_add_interval::fix_length_
uint8 dec= MY_MAX(args[0]->datetime_precision(), interval_dec);
fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH, dec);
cached_field_type= MYSQL_TYPE_DATETIME;
- unsigned_flag= 1;
}
else if (arg0_field_type == MYSQL_TYPE_DATE)
{
- unsigned_flag= 1;
if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH)
{
cached_field_type= MYSQL_TYPE_DATE;
@@ -2808,7 +2806,6 @@ void Item_func_add_time::fix_length_and_
else if (args[0]->is_temporal_with_date_and_time() || is_date)
{
cached_field_type= MYSQL_TYPE_DATETIME;
- unsigned_flag= 1;
uint8 dec= MY_MAX(args[0]->datetime_precision(), args[1]->time_precision());
fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH, dec);
}
@@ -3327,7 +3324,6 @@ void Item_func_str_to_date::fix_from_for
*/
cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;
cached_field_type= MYSQL_TYPE_DATETIME;
- unsigned_flag= 1;
fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH,
DATETIME_MAX_DECIMALS);
return;
@@ -3345,7 +3341,6 @@ void Item_func_str_to_date::fix_from_for
}
else if (time_part_used)
{
- unsigned_flag= 1;
if (date_part_used) /* DATETIME, no microseconds */
{
cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;
=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h 2012-01-31 15:16:16 +0000
+++ b/sql/item_timefunc.h 2012-02-23 16:32:32 +0000
@@ -588,11 +588,11 @@ class Item_date_func :public Item_tempor
{
public:
Item_date_func() :Item_temporal_func()
- { unsigned_flag= 1; }
+ { }
Item_date_func(Item *a) :Item_temporal_func(a)
- { unsigned_flag= 1; }
+ { }
Item_date_func(Item *a, Item *b) :Item_temporal_func(a, b)
- { unsigned_flag= 1; }
+ { }
enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
bool get_time(MYSQL_TIME *ltime)
{
@@ -636,13 +636,13 @@ class Item_datetime_func :public Item_te
{
public:
Item_datetime_func() :Item_temporal_func()
- { unsigned_flag= 1; }
+ { }
Item_datetime_func(Item *a) :Item_temporal_func(a)
- { unsigned_flag= 1; }
+ { }
Item_datetime_func(Item *a,Item *b) :Item_temporal_func(a,b)
- { unsigned_flag= 1; }
+ { }
Item_datetime_func(Item *a,Item *b, Item *c) :Item_temporal_func(a,b,c)
- { unsigned_flag= 1; }
+ { }
enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
double val_real() { return val_real_from_decimal(); }
String *val_str(String *str)
=== added file 'sql/merge_sort.h'
--- a/sql/merge_sort.h 1970-01-01 00:00:00 +0000
+++ b/sql/merge_sort.h 2012-02-23 11:22:34 +0000
@@ -0,0 +1,145 @@
+/* Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+*/
+
+#ifndef MERGE_SORT_INCLUDED
+#define MERGE_SORT_INCLUDED
+
+/**
+ @file
+
+ @brief
+ Merge sort and insert sort implementations. These sorting functions
+ are primarily intended for sorting of JOIN_TABs before the greedy
+ search algorithm is applied. Since the JOIN_TAB comparison functions
+ (Join_tab_compare*) are not transitive, the resulting order depends
+ on the sorting implementation to a certain degree.
+
+ Since the std::stable_sort and std::sort implementations differ
+ between platforms, the result of sorting JOIN_TABs may also differ.
+ In turn, the query execution plan would differ between platforms and
+ that is a problem with mtr tests (EXPLAIN output would vary).
+
+ If you intend to sort something transitive (which means almost
+ everything except JOIN_TABs) you should most likely use one of the
+ std sorting functions instead of this.
+*/
+
+#include "sql_select.h"
+#include <queue>
+
+/**
+ Sorts the elements in the range [first,last) into ascending order
+ using insertion sort.
+
+ @param first First element in an array of pointers to be sorted
+ @param last Element after the last element in an array of pointers
+ to be sorted
+ @param comp Comparison function object that, taking two pointers
+ of the same type as those contained in the range,
+ returns true if the first argument goes before the
+ second argument in the specific strict weak ordering
+ it defines, and false otherwise.
+
+ In our case comp should be a function object with an operator:
+
+ bool operator()(Element_type*, Element_type*)
+*/
+
+template<typename Element_type, typename Comp_func>
+void insert_sort(Element_type **first, Element_type **last, Comp_func comp)
+{
+ for (Element_type **high_water_mark= first+1;
+ high_water_mark < last;
+ high_water_mark++)
+ {
+ for (Element_type **cur= high_water_mark; cur > first ; cur--)
+ {
+ if (comp(*(cur-1), *cur))
+ break;
+
+ Element_type *tmp= *(cur-1);
+ *(cur-1)= *cur;
+ *cur= tmp;
+ }
+ }
+}
+
+
+/**
+ Sorts the elements in the range [first,last) into ascending order
+ using merge sort.
+
+ @param first First element in an array of pointers to be sorted
+ @param last Element after the last element in an array of pointers
+ to be sorted
+ @param comp Comparison function object that, taking two pointers
+ of the same type as those contained in the range,
+ returns true if the first argument goes before the
+ second argument in the specific strict weak ordering
+ it defines, and false otherwise.
+
+ In our case comp should be a function object with an operator:
+
+ bool operator()(Element_type*, Element_type*)
+*/
+
+template<typename Element_type, typename Comp_func>
+void merge_sort(Element_type **first, Element_type **last, Comp_func comp)
+{
+ const uint elements= last - first;
+
+ /*
+ Tests showed that the value 5 was a good number for JOIN_TAB
+ ordering, which is the primary use case for this function
+ */
+ if (elements < 5)
+ {
+ insert_sort(first, last, comp);
+ return;
+ }
+ Element_type **middle= first + (elements)/2;
+
+ merge_sort (first, middle, comp);
+ merge_sort (middle, last, comp);
+
+ std::queue<Element_type *> merged;
+
+ Element_type **cur1= first;
+ Element_type **cur2= middle;
+
+ for (uint i= 0; i < elements; i++)
+ {
+ DBUG_ASSERT (cur1 < middle || cur2 < last);
+
+ if (cur1 == middle)
+ merged.push(*cur2++);
+ else if (cur2 == last)
+ merged.push(*cur1++);
+ else if (comp(*cur1, *cur2))
+ merged.push(*cur1++);
+ else
+ merged.push(*cur2++);
+ }
+
+ Element_type **result= first;
+ while (!merged.empty())
+ {
+ *result++= merged.front();
+ merged.pop();
+ }
+}
+
+#endif /* MERGE_SORT_INCLUDED */
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2012-02-21 14:51:02 +0000
+++ b/sql/mysqld.cc 2012-02-23 11:22:34 +0000
@@ -7070,6 +7070,7 @@ SHOW_VAR status_vars[]= {
{"Opened_files", (char*) &my_file_total_opened, SHOW_LONG_NOFLUSH},
{"Opened_tables", (char*) offsetof(STATUS_VAR, opened_tables), SHOW_LONGLONG_STATUS},
{"Opened_table_definitions", (char*) offsetof(STATUS_VAR, opened_shares), SHOW_LONGLONG_STATUS},
+ {"Opt_partial_plans", (char*) offsetof(STATUS_VAR, opt_partial_plans), SHOW_LONGLONG_STATUS},
{"Prepared_stmt_count", (char*) &show_prepared_stmt_count, SHOW_FUNC},
#ifdef HAVE_QUERY_CACHE
{"Qcache_free_blocks", (char*) &query_cache.free_memory_blocks, SHOW_LONG_NOFLUSH},
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2012-02-22 08:57:27 +0000
+++ b/sql/sql_class.h 2012-02-23 11:22:34 +0000
@@ -836,6 +836,7 @@ typedef struct system_status_var
{
ulonglong created_tmp_disk_tables;
ulonglong created_tmp_tables;
+ ulonglong opt_partial_plans;
ulonglong ha_commit_count;
ulonglong ha_delete_count;
ulonglong ha_read_first_count;
=== modified file 'sql/sql_const.h'
--- a/sql/sql_const.h 2011-11-24 08:02:23 +0000
+++ b/sql/sql_const.h 2012-02-23 16:32:32 +0000
@@ -57,6 +57,10 @@
#define MAX_DATETIME_WIDTH 19 /* YYYY-MM-DD HH:MM:SS */
#define MAX_DATETIME_COMPRESSED_WIDTH 14 /* YYYYMMDDHHMMSS */
+#define DATE_INT_DIGITS 8 /* YYYYMMDD */
+#define TIME_INT_DIGITS 7 /* hhhmmss */
+#define DATETIME_INT_DIGITS 14 /* YYYYMMDDhhmmss */
+
#define MAX_TABLES (sizeof(table_map)*8-3) /* Max tables in join */
#define PARAM_TABLE_BIT (((table_map) 1) << (sizeof(table_map)*8-3))
#define OUTER_REF_TABLE_BIT (((table_map) 1) << (sizeof(table_map)*8-2))
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-02-21 10:31:44 +0000
+++ b/sql/sql_executor.cc 2012-02-23 13:27:28 +0000
@@ -1366,8 +1366,9 @@ return_zero_rows(JOIN *join, List<Item>
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);
+ for (TABLE_LIST *table= join->select_lex->leaf_tables; table;
+ table= table->next_leaf)
+ mark_as_null_row(table->table);
// Calculate aggregate functions for no rows
List_iterator_fast<Item> it(fields);
=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc 2012-01-16 12:51:06 +0000
+++ b/sql/sql_planner.cc 2012-02-23 11:22:34 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -29,6 +29,7 @@
#include "opt_range.h"
#include "opt_trace.h"
#include "sql_executor.h"
+#include "merge_sort.h"
#include <my_bit.h>
#include <algorithm>
@@ -37,106 +38,6 @@ using std::min;
static double prev_record_reads(JOIN *join, uint idx, table_map found_ref);
-/**
- Compare two JOIN_TAB objects based on the number of accessed records.
-
- @param ptr1 pointer to first JOIN_TAB object
- @param ptr2 pointer to second JOIN_TAB object
-
- NOTES
- The order relation implemented by join_tab_cmp() is not transitive,
- i.e. it is possible to choose such a, b and c that (a < b) && (b < c)
- but (c < a). This implies that result of a sort using the relation
- implemented by join_tab_cmp() depends on the order in which
- elements are compared, i.e. the result is implementation-specific.
- Example:
- a: dependent = 0x0 table->map = 0x1 found_records = 3 ptr = 0x907e6b0
- b: dependent = 0x0 table->map = 0x2 found_records = 3 ptr = 0x907e838
- c: dependent = 0x6 table->map = 0x10 found_records = 2 ptr = 0x907ecd0
-
- @retval
- 1 if first is bigger
- @retval
- -1 if second is bigger
- @retval
- 0 if equal
-*/
-
-static int
-join_tab_cmp(const void *, const void* ptr1, const void* ptr2)
-{
- JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
- JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
-
- if (jt1->dependent & jt2->table->map)
- return 1;
- if (jt2->dependent & jt1->table->map)
- return -1;
- if (jt1->found_records > jt2->found_records)
- return 1;
- if (jt1->found_records < jt2->found_records)
- return -1;
- return jt1 > jt2 ? 1 : (jt1 < jt2 ? -1 : 0);
-}
-
-
-/**
- Same as join_tab_cmp, but for use with SELECT_STRAIGHT_JOIN.
-*/
-
-static int
-join_tab_cmp_straight(const void *, const void* ptr1, const void* ptr2)
-{
- JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
- JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
-
- /*
- We don't do subquery flattening if the parent or child select has
- STRAIGHT_JOIN modifier. It is complicated to implement and the semantics
- is hardly useful.
- */
- DBUG_ASSERT(!jt1->emb_sj_nest);
- DBUG_ASSERT(!jt2->emb_sj_nest);
-
- if (jt1->dependent & jt2->table->map)
- return 1;
- if (jt2->dependent & jt1->table->map)
- return -1;
- return jt1 > jt2 ? 1 : (jt1 < jt2 ? -1 : 0);
-}
-
-
-/*
- Same as join_tab_cmp but tables from within the given semi-join nest go
- first. Used when optimizing semi-join materialization nests.
-*/
-
-static int
-join_tab_cmp_embedded_first(const void *emb, const void* ptr1, const void* ptr2)
-{
- const TABLE_LIST *emb_nest= (TABLE_LIST*) emb;
- JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
- JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
-
- if (jt1->emb_sj_nest == emb_nest && jt2->emb_sj_nest != emb_nest)
- return -1;
- if (jt1->emb_sj_nest != emb_nest && jt2->emb_sj_nest == emb_nest)
- return 1;
-
- if (jt1->dependent & jt2->table->map)
- return 1;
- if (jt2->dependent & jt1->table->map)
- return -1;
-
- if (jt1->found_records > jt2->found_records)
- return 1;
- if (jt1->found_records < jt2->found_records)
- return -1;
-
- return jt1 > jt2 ? 1 : (jt1 < jt2 ? -1 : 0);
-}
-
-
/*
This is a class for considering possible loose index scan optimizations.
It's usage pattern is as follows:
@@ -1149,7 +1050,6 @@ bool Optimize_table_order::choose_table_
}
reset_nj_counters(join->join_list);
- qsort2_cmp jtab_sort_func;
const bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN);
table_map join_tables; ///< The tables involved in order selection
@@ -1159,7 +1059,9 @@ bool Optimize_table_order::choose_table_
/* We're optimizing semi-join materialization nest, so put the
tables from this semi-join as first
*/
- jtab_sort_func= join_tab_cmp_embedded_first;
+ merge_sort(join->best_ref + join->const_tables,
+ join->best_ref + join->tables,
+ Join_tab_compare_embedded_first(emb_sjm_nest));
join_tables= emb_sjm_nest->sj_inner_tables;
}
else
@@ -1172,12 +1074,17 @@ bool Optimize_table_order::choose_table_
Apply heuristic: pre-sort all access plans with respect to the number of
records accessed.
*/
- jtab_sort_func= straight_join ? join_tab_cmp_straight : join_tab_cmp;
+ if (straight_join)
+ merge_sort(join->best_ref + join->const_tables,
+ join->best_ref + join->tables,
+ Join_tab_compare_straight());
+ else
+ merge_sort(join->best_ref + join->const_tables,
+ join->best_ref + join->tables,
+ Join_tab_compare_default());
+
join_tables= join->all_table_map & ~join->const_table_map;
}
- my_qsort2(join->best_ref + join->const_tables,
- join->tables - join->const_tables, sizeof(JOIN_TAB*),
- jtab_sort_func, (void*)emb_sjm_nest);
Opt_trace_object wrapper(&join->thd->opt_trace);
Opt_trace_array
@@ -1882,6 +1789,7 @@ bool Optimize_table_order::best_extensio
for (JOIN_TAB **pos= join->best_ref + idx; *pos; pos++)
{
+ status_var_increment(thd->status_var.opt_partial_plans);
JOIN_TAB *const s= *pos;
const table_map real_table_bit= s->table->map;
@@ -2250,6 +2158,7 @@ table_map Optimize_table_order::eq_ref_e
added_to_eq_ref_extension);
if (added_to_eq_ref_extension)
{
+ status_var_increment(thd->status_var.opt_partial_plans);
double current_record_count, current_read_time;
/* Add the cost of extending the plan with 's' */
=== modified file 'sql/sql_planner.h'
--- a/sql/sql_planner.h 2011-12-14 12:32:55 +0000
+++ b/sql/sql_planner.h 2012-02-23 11:22:34 +0000
@@ -1,7 +1,7 @@
#ifndef SQL_PLANNER_INCLUDED
#define SQL_PLANNER_INCLUDED
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -140,12 +140,6 @@ private:
double *newcount, double *newcost);
static uint determine_search_depth(uint search_depth, uint table_count);
- /**
- @todo: Add the static functions join_tab_cmp(), join_tab_cmp_straight() and
- join_tab_cmp_embedded_first() as static private member functions here.
- This is currently not possible because they are fed to my_qsort2(),
- which requires C linkage.
- */
};
void get_partial_join_cost(JOIN *join, uint n_tables, double *read_time_arg,
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2012-01-16 12:51:06 +0000
+++ b/sql/sql_select.h 2012-02-23 11:22:34 +0000
@@ -1,7 +1,7 @@
#ifndef SQL_SELECT_INCLUDED
#define SQL_SELECT_INCLUDED
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -33,6 +33,7 @@
#include "mem_root_array.h"
#include "sql_executor.h"
+#include <functional>
/**
Returns a constant of type 'type' with the 'A' lowest-weight bits set.
Example: LOWER_BITS(uint, 3) == 7.
@@ -654,6 +655,135 @@ st_join_table::st_join_table()
memset(&read_record, 0, sizeof(read_record));
}
+/**
+ "Less than" comparison function object used to compare two JOIN_TAB
+ objects based on a number of factors in this order:
+
+ - table before another table that depends on it (straight join,
+ outer join etc), then
+ - table before another table that depends on it to use a key
+ as access method, then
+ - table with smallest number of records first, then
+ - the table with lowest-value pointer (i.e., the one located
+ in the lowest memory address) first.
+
+ @param jt1 first JOIN_TAB object
+ @param jt2 second JOIN_TAB object
+
+ @note The order relation implemented by Join_tab_compare_default is not
+ transitive, i.e. it is possible to choose a, b and c such that
+ (a < b) && (b < c) but (c < a). This is the case in the
+ following example:
+
+ a: dependent = <none> found_records = 3
+ b: dependent = <none> found_records = 4
+ c: dependent = b found_records = 2
+
+ a < b: because a has fewer records
+ b < c: because c depends on b (e.g outer join dependency)
+ c < a: because c has fewer records
+
+ This implies that the result of a sort using the relation
+ implemented by Join_tab_compare_default () depends on the order in
+ which elements are compared, i.e. the result is
+ implementation-specific.
+
+ @return
+ true if jt1 is smaller than jt2, false otherwise
+*/
+class Join_tab_compare_default :
+ public std::binary_function<const JOIN_TAB*, const JOIN_TAB*, bool>
+{
+public:
+ bool operator()(const JOIN_TAB *jt1, const JOIN_TAB *jt2)
+ {
+ // Sorting distinct tables, so a table should not be compared with itself
+ DBUG_ASSERT(jt1 != jt2);
+
+ if (jt1->dependent & jt2->table->map)
+ return false;
+ if (jt2->dependent & jt1->table->map)
+ return true;
+
+ const bool jt1_keydep_jt2= jt1->key_dependent & jt2->table->map;
+ const bool jt2_keydep_jt1= jt2->key_dependent & jt1->table->map;
+
+ if (jt1_keydep_jt2 && !jt2_keydep_jt1)
+ return false;
+ if (jt2_keydep_jt1 && !jt1_keydep_jt2)
+ return true;
+
+ if (jt1->found_records > jt2->found_records)
+ return false;
+ if (jt1->found_records < jt2->found_records)
+ return true;
+
+ return jt1 < jt2;
+ }
+};
+
+/**
+ "Less than" comparison function object used to compare two JOIN_TAB
+ objects that are joined using STRAIGHT JOIN. For STRAIGHT JOINs,
+ the join order is dictated by the relative order of the tables in the
+ query which is reflected in JOIN_TAB::dependent. Table size and key
+ dependencies are ignored here.
+*/
+class Join_tab_compare_straight :
+ public std::binary_function<const JOIN_TAB*, const JOIN_TAB*, bool>
+{
+public:
+ bool operator()(const JOIN_TAB *jt1, const JOIN_TAB *jt2)
+ {
+ // Sorting distinct tables, so a table should not be compared with itself
+ DBUG_ASSERT(jt1 != jt2);
+
+ /*
+ We don't do subquery flattening if the parent or child select has
+ STRAIGHT_JOIN modifier. It is complicated to implement and the semantics
+ is hardly useful.
+ */
+ DBUG_ASSERT(!jt1->emb_sj_nest);
+ DBUG_ASSERT(!jt2->emb_sj_nest);
+
+ if (jt1->dependent & jt2->table->map)
+ return false;
+ if (jt2->dependent & jt1->table->map)
+ return true;
+
+ return jt1 < jt2;
+ }
+};
+
+/*
+ Same as Join_tab_compare_default but tables from within the given
+ semi-join nest go first. Used when optimizing semi-join
+ materialization nests.
+*/
+class Join_tab_compare_embedded_first :
+ public std::binary_function<const JOIN_TAB*, const JOIN_TAB*, bool>
+{
+private:
+ const TABLE_LIST *emb_nest;
+public:
+
+ Join_tab_compare_embedded_first(const TABLE_LIST *nest) : emb_nest(nest){}
+
+ bool operator()(const JOIN_TAB *jt1, const JOIN_TAB *jt2)
+ {
+ // Sorting distinct tables, so a table should not be compared with itself
+ DBUG_ASSERT(jt1 != jt2);
+
+ if (jt1->emb_sj_nest == emb_nest && jt2->emb_sj_nest != emb_nest)
+ return true;
+ if (jt1->emb_sj_nest != emb_nest && jt2->emb_sj_nest == emb_nest)
+ return false;
+
+ Join_tab_compare_default cmp;
+ return cmp(jt1,jt2);
+ }
+};
+
/**
=== modified file 'unittest/gunit/CMakeLists.txt'
--- a/unittest/gunit/CMakeLists.txt 2012-02-07 11:56:30 +0000
+++ b/unittest/gunit/CMakeLists.txt 2012-02-23 11:22:34 +0000
@@ -260,6 +260,7 @@ SET(SERVER_TESTS
sql_table
get_diagnostics
segfault
+ join_tab_sort
)
FOREACH(test ${TESTS})
=== added file 'unittest/gunit/join_tab_sort-t.cc'
--- a/unittest/gunit/join_tab_sort-t.cc 1970-01-01 00:00:00 +0000
+++ b/unittest/gunit/join_tab_sort-t.cc 2012-02-23 13:54:03 +0000
@@ -0,0 +1,291 @@
+/* Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ 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.
+#include "my_config.h"
+#include <gtest/gtest.h>
+
+#include "test_utils.h"
+
+#include "sql_select.h"
+#include "merge_sort.h"
+
+#include <vector>
+
+namespace {
+
+using my_testing::Server_initializer;
+using my_testing::Mock_error_handler;
+
+class JTSortTest : public ::testing::Test
+{
+protected:
+ static void SetUpTestCase()
+ {
+ Server_initializer::SetUpTestCase();
+ }
+
+ static void TearDownTestCase()
+ {
+ Server_initializer::TearDownTestCase();
+ }
+
+ virtual void SetUp()
+ {
+ initializer.SetUp();
+ }
+
+ virtual void TearDown()
+ {
+ initializer.TearDown();
+ }
+
+ Server_initializer initializer;
+};
+
+
+class MOCK_JOIN_TAB : public JOIN_TAB
+{
+public:
+ MOCK_JOIN_TAB(uint recs, uint table_no) : JOIN_TAB()
+ {
+ found_records= recs;
+ m_table.map= 1ULL << table_no;
+ this->table= &m_table;
+ }
+
+ TABLE m_table;
+};
+
+std::ostream &operator<<(std::ostream &s, const MOCK_JOIN_TAB &jt)
+{
+ return s << "{"
+ << jt.found_records << ", "
+ << jt.m_table.map
+ << "}";
+}
+
+
+TEST_F(JTSortTest, SimpleSortTest)
+{
+ MOCK_JOIN_TAB jt1(UINT_MAX, 0);
+ MOCK_JOIN_TAB jt2(2, 0);
+ MOCK_JOIN_TAB jt3(1, 0);
+ MOCK_JOIN_TAB jt4(10, 0);
+ MOCK_JOIN_TAB jt5(5, 0);
+
+ MOCK_JOIN_TAB *arr[5];
+ arr[0]= &jt1;
+ arr[1]= &jt2;
+ arr[2]= &jt3;
+ arr[3]= &jt4;
+ arr[4]= &jt5;
+
+ insert_sort(arr, arr+5, Join_tab_compare_default());
+
+ EXPECT_EQ(1U, arr[0]->found_records);
+ EXPECT_EQ(2U, arr[1]->found_records);
+ EXPECT_EQ(5U, arr[2]->found_records);
+ EXPECT_EQ(10U, arr[3]->found_records);
+ EXPECT_EQ(UINT_MAX, arr[4]->found_records);
+
+}
+
+
+TEST_F(JTSortTest, SortFoundRecordsTest)
+{
+ const int num_tables= 50;
+ MOCK_JOIN_TAB *arr[num_tables];
+
+ for (int i= 0; i < num_tables; i++)
+ arr[i]= new MOCK_JOIN_TAB(i, 0);
+
+ // MERGE SORT
+ std::random_shuffle(arr, arr + 50);
+ merge_sort(arr, arr + num_tables, Join_tab_compare_default());
+ for (int i= 1; i < num_tables; i++)
+ EXPECT_TRUE(arr[i]->found_records > arr[i-1]->found_records);
+
+ // INSERT SORT
+ std::random_shuffle(arr, arr + 50);
+ insert_sort(arr, arr + num_tables, Join_tab_compare_default());
+ for (int i= 1; i < num_tables; i++)
+ EXPECT_TRUE(arr[i]->found_records > arr[i-1]->found_records);
+
+ for (int i= 0; i < num_tables; i++)
+ {
+ delete arr[i];
+ }
+}
+
+
+TEST_F(JTSortTest, SortDependsTest)
+{
+ const int num_tables= 50;
+ MOCK_JOIN_TAB *arr[num_tables];
+
+ /*
+ dependency has higher precedence than found_records, so the tables
+ shall be ordered with decreasing number of records in this test
+ */
+ for (int i= 0; i < num_tables; i++)
+ {
+ arr[i]= new MOCK_JOIN_TAB(i, i);
+ for (int j= i+1; j < num_tables; j++)
+ arr[i]->dependent|= 1ULL << j;
+ }
+
+ // MERGE SORT
+ std::random_shuffle(arr, arr + num_tables);
+ merge_sort(arr, arr + num_tables, Join_tab_compare_default());
+ for (int i= 1; i < num_tables; i++)
+ EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records)
+ << "i: " << *(arr[i]) << " "
+ << "i-1: " << *(arr[i-1]);
+
+ // INSERT SORT
+ std::random_shuffle(arr, arr + num_tables);
+ insert_sort(arr, arr + num_tables, Join_tab_compare_default());
+ for (int i= 1; i < num_tables; i++)
+ EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records);
+
+ for (int i= 0; i < num_tables; i++)
+ {
+ delete arr[i];
+ }
+}
+
+
+TEST_F(JTSortTest, SortKeyDependsTest)
+{
+ const int num_tables= 50;
+ MOCK_JOIN_TAB *arr[num_tables];
+
+ /*
+ key_dependency has higher precedence than found_records, so the
+ tables shall be ordered with decreasing number of records in this
+ test
+ */
+ for (int i= 0; i < num_tables; i++)
+ {
+ arr[i]= new MOCK_JOIN_TAB(i, i);
+ for (int j= i+1; j < num_tables; j++)
+ arr[i]->key_dependent|= 1ULL << j;
+ }
+
+ // MERGE SORT
+ std::random_shuffle(arr, arr + num_tables);
+ merge_sort(arr, arr + num_tables, Join_tab_compare_default());
+ for (int i= 1; i < num_tables; i++)
+ EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records);
+
+ // INSERT SORT
+ std::random_shuffle(arr, arr + num_tables);
+ insert_sort(arr, arr + num_tables, Join_tab_compare_default());
+ for (int i= 1; i < num_tables; i++)
+ EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records);
+
+ for (int i= 0; i < num_tables; i++)
+ delete arr[i];
+}
+
+/*
+ Above, sorting for JOIN_TABs were tested. Below we check that the
+ sorting works for ints types as well.
+*/
+
+class Int_compare_ptr :
+ public std::binary_function<const int*, const int*, bool>
+{
+public:
+ bool operator()(const int *i1, const int *i2) const
+ {
+ return *i1 < *i2;
+ }
+};
+
+
+TEST_F(JTSortTest, SortIntTest)
+{
+ const uint ints_to_sort= 1000;
+
+ std::vector<int> arr;
+ std::vector<int*> arr_ptr;
+
+ arr.reserve(ints_to_sort);
+ arr_ptr.reserve(ints_to_sort);
+
+ for (uint i= 0; i < ints_to_sort; i++)
+ {
+ arr.push_back(i);
+ arr_ptr.push_back(&arr[i]);
+ }
+
+ EXPECT_TRUE(arr.size() == ints_to_sort);
+ EXPECT_TRUE(arr_ptr.size() == ints_to_sort);
+
+ // MERGE SORT
+ std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+ merge_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+ for (uint i= 0; i < arr_ptr.size(); i++)
+ EXPECT_TRUE(*arr_ptr[i] == (int)i);
+
+ // INSERT SORT
+ std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+ insert_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+ for (uint i= 0; i < arr_ptr.size(); i++)
+ EXPECT_TRUE(*arr_ptr[i] == (int)i);
+}
+
+
+TEST_F(JTSortTest, SortInt2Test)
+{
+ const uint ints_to_sort= 1000;
+
+ std::vector<int> arr;
+ std::vector<int*> arr_ptr;
+
+ arr.reserve(ints_to_sort);
+ arr_ptr.reserve(ints_to_sort);
+
+ for (uint i= 0; i < (ints_to_sort - 2); i++)
+ {
+ arr.push_back((i % 2) ? i : (i * -1));
+ arr_ptr.push_back(&arr[i]);
+ }
+
+ arr.push_back(INT_MAX32);
+ arr_ptr.push_back(&arr.back());
+
+ arr.push_back(INT_MIN32);
+ arr_ptr.push_back(&arr.back());
+
+ EXPECT_TRUE(arr.size() == ints_to_sort);
+ EXPECT_TRUE(arr_ptr.size() == ints_to_sort);
+
+ // MERGE SORT
+ std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+ merge_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+ for (uint i= 1; i < arr_ptr.size(); i++)
+ EXPECT_TRUE(*arr_ptr[i-1] < *arr_ptr[i]);
+
+ // INSERT SORT
+ std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+ insert_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+ for (uint i= 1; i < arr_ptr.size(); i++)
+ EXPECT_TRUE(*arr_ptr[i-1] < *arr_ptr[i]);
+}
+
+}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk-wl5767 branch (marc.alff:3446 to 3447) | Marc Alff | 24 Feb |
