diff options
47 files changed, 662 insertions, 245 deletions
diff --git a/extra/yassl/src/crypto_wrapper.cpp b/extra/yassl/src/crypto_wrapper.cpp index bdd5f60c4ea..afb492c83c5 100644 --- a/extra/yassl/src/crypto_wrapper.cpp +++ b/extra/yassl/src/crypto_wrapper.cpp @@ -954,7 +954,10 @@ x509* PemToDer(FILE* file, CertType type, EncryptedInfo* info) } } // get blank line - if (fgets(line, sizeof(line), file)) + if (fgets(line,sizeof(line), file) == 0) + { + /* Impossible case */ + } begin = ftell(file); } diff --git a/include/m_ctype.h b/include/m_ctype.h index 37e15894432..969cb0058ac 100644 --- a/include/m_ctype.h +++ b/include/m_ctype.h @@ -71,7 +71,7 @@ struct unicase_info_st extern MY_UNICASE_INFO *const my_unicase_default[256]; extern MY_UNICASE_INFO *const my_unicase_turkish[256]; -extern MY_UNICASE_INFO *my_unicase_mysql500[256]; +extern MY_UNICASE_INFO *const my_unicase_mysql500[256]; #define MY_UCA_MAX_CONTRACTION 4 #define MY_UCA_MAX_WEIGHT_SIZE 8 @@ -356,6 +356,7 @@ extern struct charset_info_st my_charset_ucs2_bin; extern struct charset_info_st my_charset_ucs2_general_ci; extern struct charset_info_st my_charset_ucs2_general_mysql500_ci; extern struct charset_info_st my_charset_ucs2_unicode_ci; +extern struct charset_info_st my_charset_ucs2_general_mysql500_ci; extern struct charset_info_st my_charset_ujis_bin; extern struct charset_info_st my_charset_ujis_japanese_ci; extern struct charset_info_st my_charset_utf16_bin; diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index b4da56cda6f..6b20add1bed 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5415,6 +5415,19 @@ foo DROP TABLE t1; +CREATE TABLE t1 ( b TEXT NOT NULL ) ENGINE=MYISAM; +INSERT INTO t1 VALUES ('x'),('y'); +CREATE TABLE t2 ( a VARCHAR(1) NOT NULL ) ENGINE=CSV; +INSERT INTO t2 VALUES ('r'),('t'); +SELECT * FROM t2 ORDER BY a; +a +r +t +SELECT * FROM t1 LEFT JOIN t2 ON ( b = a ); +b a +x NULL +y NULL +drop table t1,t2; # # Test for the following cases # 1) integers and strings enclosed in quotes diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5a3d547d6b3..32081c13376 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2007,5 +2007,44 @@ a b gc SET SESSION optimizer_switch= @save_optimizer_switch; DROP VIEW v; DROP TABLE t1,t2; +# +# LP BUG#968720 crash due to converting to materialized and +# natural join made only once +# +SET @save968720_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; +CREATE TABLE t1 (a int, INDEX(a)); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (a int, INDEX(a)); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +SELECT * FROM t1; +a +1 +1 +DELETE FROM t1; +INSERT INTO t1 VALUES (1); +PREPARE stmt FROM " +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +"; +EXECUTE stmt; +SELECT * FROM t1; +a +1 +1 +EXECUTE stmt; +SELECT * FROM t1; +a +1 +1 +1 +1 +drop table t1,t2; +set optimizer_switch=@save968720_optimizer_switch; +# +# end of 5.3 tests +# set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index d4a071d2d63..bfc6b72caa1 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -885,18 +885,12 @@ DROP TABLE t1; select last_day('2005-00-00'); last_day('2005-00-00') NULL -Warnings: -Warning 1292 Incorrect datetime value: '2005-00-00' select last_day('2005-00-01'); last_day('2005-00-01') NULL -Warnings: -Warning 1292 Incorrect datetime value: '2005-00-01' select last_day('2005-01-00'); last_day('2005-01-00') -NULL -Warnings: -Warning 1292 Incorrect datetime value: '2005-01-00' +2005-01-31 select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) @@ -1435,7 +1429,7 @@ NULL # DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); Warnings: -Warning 1292 Truncated incorrect date value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0' # # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY # SIMILAR TO '2009-10-00' @@ -1469,9 +1463,9 @@ TO_DAYs(DATE'2009-10-00'), TO_DAYs(STR_TO_DATE('200910','%Y%m')); DATE('20091000') 2009-10-00 STR_TO_DATE('200910','%Y%m') 2009-10-00 -LAST_DAY('2009-10-00') NULL -LAST_DAY(DATE('2009-10-00')) NULL -LAST_DAY(DATE'2009-10-00') NULL +LAST_DAY('2009-10-00') 2009-10-31 +LAST_DAY(DATE('2009-10-00')) 2009-10-31 +LAST_DAY(DATE'2009-10-00') 2009-10-31 LAST_DAY(STR_TO_DATE('200910','%Y%m')) 2009-10-31 WEEK('2009-10-00') NULL WEEK(DATE('2009-10-00')) NULL @@ -1499,18 +1493,9 @@ Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 -Message Truncated incorrect date value: '2009-10-00' -Level Warning -Code 1292 -Message Incorrect datetime value: '2009-10-00' -Level Warning -Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 -Message Truncated incorrect date value: '2009-10-00' -Level Warning -Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 @@ -1520,7 +1505,7 @@ Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 -Message Truncated incorrect date value: '2009-10-00' +Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' @@ -1532,7 +1517,7 @@ Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 -Message Truncated incorrect date value: '2009-10-00' +Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' @@ -1544,7 +1529,7 @@ Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 -Message Truncated incorrect date value: '2009-10-00' +Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' @@ -1556,7 +1541,7 @@ Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 -Message Truncated incorrect date value: '2009-10-00' +Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' @@ -1592,9 +1577,9 @@ TO_DAYs(DATE'0000-01-00'), TO_DAYs(STR_TO_DATE('000001','%Y%m')); DATE('00000100') 0000-01-00 STR_TO_DATE('000001','%Y%m') 0000-01-00 -LAST_DAY('0000-01-00') NULL -LAST_DAY(DATE('0000-01-00')) NULL -LAST_DAY(DATE'0000-01-00') NULL +LAST_DAY('0000-01-00') 0000-01-31 +LAST_DAY(DATE('0000-01-00')) 0000-01-31 +LAST_DAY(DATE'0000-01-00') 0000-01-31 LAST_DAY(STR_TO_DATE('000001','%Y%m')) 0000-01-31 WEEK('0000-01-00') NULL WEEK(DATE('0000-01-00')) NULL @@ -1622,18 +1607,9 @@ Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 -Message Truncated incorrect date value: '0000-01-00' -Level Warning -Code 1292 -Message Incorrect datetime value: '0000-01-00' -Level Warning -Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 -Message Truncated incorrect date value: '0000-01-00' -Level Warning -Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 @@ -1643,7 +1619,7 @@ Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 -Message Truncated incorrect date value: '0000-01-00' +Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' @@ -1655,7 +1631,7 @@ Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 -Message Truncated incorrect date value: '0000-01-00' +Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' @@ -1667,7 +1643,7 @@ Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 -Message Truncated incorrect date value: '0000-01-00' +Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' @@ -1679,7 +1655,7 @@ Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 -Message Truncated incorrect date value: '0000-01-00' +Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' @@ -1912,7 +1888,7 @@ select timestampadd(week, 1, date("0000-00-00")); timestampadd(week, 1, date("0000-00-00")) NULL Warnings: -Warning 1292 Truncated incorrect date value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' drop table t1; create table t1 (f2 time not null, f3 datetime, f4 int not null, f5 timestamp); insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00'); diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index d926ebcfc2b..fa8471eb1cb 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5261,8 +5261,8 @@ SET join_cache_level=0; EXPLAIN SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 2 2 DERIVED t2 ALL NULL NULL NULL NULL 2 @@ -5480,8 +5480,8 @@ EXPLAIN SELECT * FROM t1 WHERE t1.i IN (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using where SELECT * FROM t1 WHERE t1.i IN @@ -5494,8 +5494,8 @@ EXPLAIN SELECT * FROM t1 WHERE t1.i IN (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE t1.i IN diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index da2f59f375e..fc7e229603c 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -148,13 +148,13 @@ insert INTO t1 VALUES (110); ERROR HY000: Table has no partition for value 110 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where @@ -163,16 +163,16 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # Bug#50104: Partitioned table with just 1 partion works with fk diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index b59ec858e98..b1e4cb669ed 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -260,11 +260,11 @@ INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); -ERROR 22007: Truncated incorrect date value: '2004-10-00' +ERROR 22007: Incorrect datetime value: '2004-10-0' INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); -ERROR 22007: Truncated incorrect date value: '2004-00-10' +ERROR 22007: Incorrect datetime value: '2004-0-10' INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); -ERROR 22007: Truncated incorrect date value: '0000-00-00' +ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' @@ -287,13 +287,13 @@ INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); -ERROR 22007: Truncated incorrect date value: '2004-10-00' +ERROR 22007: Incorrect datetime value: '2004-10-0' INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); -ERROR 22007: Truncated incorrect date value: '2004-00-10' +ERROR 22007: Incorrect datetime value: '2004-0-10' INSERT INTO t1 (col1) VALUES('2004-0-10'); ERROR 22007: Incorrect date value: '2004-0-10' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); -ERROR 22007: Truncated incorrect date value: '0000-00-00' +ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-0 15:30' @@ -1137,9 +1137,9 @@ ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 insert into t1 values (0.0,0.0,0.0); ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 insert into t1 (col1) values (convert('0000-00-00',date)); -ERROR 22007: Truncated incorrect date value: '0000-00-00' +ERROR 22007: Incorrect datetime value: '0000-00-00' insert into t1 (col1) values (cast('0000-00-00' as date)); -ERROR 22007: Truncated incorrect date value: '0000-00-00' +ERROR 22007: Incorrect datetime value: '0000-00-00' set sql_mode='no_zero_date'; insert into t1 values (0,0,0); Warnings: @@ -1164,7 +1164,7 @@ Warnings: Warning 1264 Out of range value for column 'col1' at row 1 insert ignore into t1 (col1) values (cast('0000-00-00' as date)); Warnings: -Warning 1292 Truncated incorrect date value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' insert into t1 select * from t1; ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 alter table t1 modify col1 datetime; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 0c071f1adfc..26faf465749 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5595,8 +5595,8 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 24 Using where -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED it2 ALL NULL NULL NULL NULL 4 2 MATERIALIZED it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index d5d75b68628..014ff5b5810 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1144,9 +1144,9 @@ insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; LooseScan -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) drop table t1, t3, t4; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int) as select * from t0 where a < 5; @@ -1155,11 +1155,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off'; set @@max_heap_table_size= 16384; explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary -1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 PRIMARY A ALL NULL NULL NULL NULL 10 1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) -1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) -1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (flat, BNL join) flush status; select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); count(*) diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index d794d35cf1a..a62048db506 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1154,9 +1154,9 @@ insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; LooseScan -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) drop table t1, t3, t4; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int) as select * from t0 where a < 5; @@ -1165,11 +1165,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off'; set @@max_heap_table_size= 16384; explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY E ALL NULL NULL NULL NULL 5 Using where; Start temporary -1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) -1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (incremental, BNL join) -1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY D hash_ALL NULL #hash#$hj 5 test.E.a 10 Using where; End temporary; Using join buffer (incremental, BNLH join) +1 PRIMARY A ALL NULL NULL NULL NULL 10 +1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 PRIMARY E ALL NULL NULL NULL NULL 5 Using where; Start temporary; Using join buffer (incremental, BNL join) +1 PRIMARY D hash_ALL NULL #hash#$hj 5 test.E.a 10 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (incremental, BNL join) flush status; select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); count(*) diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index ecafe57f47a..8a43852a159 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5597,9 +5597,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join) -1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) +1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(ot4) DROP TABLE IF EXISTS ot1, ot4, it2, it3; # # Bug#729039: NULL keys used to evaluate subquery diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index e85f2ccf1e1..d8169de9130 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -5601,8 +5601,8 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 24 Using where -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED it2 ALL NULL NULL NULL NULL 4 2 MATERIALIZED it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index c767ee1663e..ca834b21df5 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -760,16 +760,16 @@ 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 t2 ALL NULL NULL NULL NULL 6 100.00 Start temporary -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; FirstMatch(t1) 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 `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) show warnings; Level Code Message Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` 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 @@ -2168,10 +2168,10 @@ INSERT INTO t5 VALUES (7,0),(9,0); explain SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; LooseScan +1 PRIMARY t5 index a a 10 NULL 2 Using index; Start temporary 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where; FirstMatch(t5) -1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL b NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); a 0 @@ -2250,11 +2250,11 @@ alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Start temporary +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary 1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) -1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary -1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as SELECT diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 835403bb5b1..97e36cd705c 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -49,9 +49,9 @@ a b 19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL b NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -69,9 +69,9 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t3 ref b b 5 test.t1.a 1 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -95,13 +95,17 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL b NULL NULL NULL # +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED t0 ALL NULL NULL NULL NULL # select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0 1 1 1 1 +10 10 10 10 +11 11 11 11 +12 12 12 12 +13 13 13 13 2 2 2 2 3 3 3 3 4 4 4 4 @@ -110,10 +114,6 @@ a b pk1 pk2 7 7 7 7 8 8 8 8 9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); @@ -745,8 +745,8 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; # @@ -991,8 +991,8 @@ WHERE alias2.b = alias1.a AND (alias1.b >= alias1.a OR alias2.b = 'z')); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 19 -1 PRIMARY t2 ref a a 4 test.alias1.a 1 +1 PRIMARY t2 ALL a NULL NULL NULL 38 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where 2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where SELECT * FROM t2 diff --git a/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff b/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff index 30df3c42a0d..c32f52fe0c2 100644 --- a/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff +++ b/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff @@ -1,29 +1,8 @@ ---- r/subselect_sj2_jcl6.result 2012-03-28 20:10:41.000000000 +0200 -+++ r/subselect_sj2_jcl6,innodb_plugin.reject 2012-03-28 20:21:14.000000000 +0200 -@@ -81,7 +81,7 @@ - explain select * from t3 where b in (select a from t1); - id select_type table type possible_keys key key_len ref rows Extra - 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 --1 PRIMARY t3 ref b b 5 test.t1.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -+1 PRIMARY t3 ref b b 5 test.t1.a 1 - 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where - select * from t3 where b in (select a from t1); - a b pk1 pk2 pk3 -@@ -107,7 +107,7 @@ - explain select * from t3 where b in (select a from t0); - id select_type table type possible_keys key key_len ref rows Extra - 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 --1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -+1 PRIMARY t3 ref b b 5 test.t0.a 1 - 2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where - select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); - a b pk1 pk2 -@@ -1006,9 +1006,9 @@ - (alias1.b >= alias1.a OR alias2.b = 'z')); - id select_type table type possible_keys key key_len ref rows Extra - 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 19 --1 PRIMARY t2 ref a a 4 test.alias1.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -+1 PRIMARY t2 ref a a 4 test.alias1.a 1 +--- r/subselect_sj2_jcl6.result 2012-04-07 12:45:03.000000000 +0200 ++++ r/subselect_sj2_jcl6,innodb_plugin.reject 2012-04-07 13:10:38.000000000 +0200 +@@ -1009,7 +1009,7 @@ + 1 PRIMARY t2 ALL a NULL NULL NULL 38 + 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where -2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index c4c8a858b68..e4030b8138b 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -60,9 +60,9 @@ a b 19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL b NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -80,9 +80,9 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t3 ref b b 5 test.t1.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -106,13 +106,17 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL b NULL NULL NULL # +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED t0 ALL NULL NULL NULL NULL # select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0 1 1 1 1 +10 10 10 10 +11 11 11 11 +12 12 12 12 +13 13 13 13 2 2 2 2 3 3 3 3 4 4 4 4 @@ -121,10 +125,6 @@ a b pk1 pk2 7 7 7 7 8 8 8 8 9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); @@ -759,9 +759,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2); Using join buffer (incremental, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)); Using join buffer (incremental, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 @@ -1005,8 +1006,8 @@ WHERE alias2.b = alias1.a AND (alias1.b >= alias1.a OR alias2.b = 'z')); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 19 -1 PRIMARY t2 ref a a 4 test.alias1.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t2 ALL a NULL NULL NULL 38 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where 2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT * FROM t2 diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index b535f37ee90..f69befc8608 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -51,9 +51,9 @@ a b 19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL b NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -71,9 +71,9 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t3 ref b b 5 test.t1.a 1 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -97,13 +97,17 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL b NULL NULL NULL # +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED t0 ALL NULL NULL NULL NULL # select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0 1 1 1 1 +10 10 10 10 +11 11 11 11 +12 12 12 12 +13 13 13 13 2 2 2 2 3 3 3 3 4 4 4 4 @@ -112,10 +116,6 @@ a b pk1 pk2 7 7 7 7 8 8 8 8 9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); @@ -747,8 +747,8 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; # @@ -993,8 +993,8 @@ WHERE alias2.b = alias1.a AND (alias1.b >= alias1.a OR alias2.b = 'z')); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 19 -1 PRIMARY t2 ref a a 4 test.alias1.a 1 +1 PRIMARY t2 ALL a NULL NULL NULL 38 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where 2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where SELECT * FROM t2 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 38c7f2ff554..19f3baac5f2 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -773,16 +773,16 @@ 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 t2 ALL NULL NULL NULL NULL 6 100.00 Start temporary -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 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 `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) show warnings; Level Code Message Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` 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 @@ -2182,10 +2182,10 @@ INSERT INTO t5 VALUES (7,0),(9,0); explain SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; LooseScan -1 PRIMARY t4 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where; FirstMatch(t5) -1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t5 index a a 10 NULL 2 Using index; Start temporary +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL b NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; End temporary; Using join buffer (incremental, BNL join) SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); a 0 @@ -2264,11 +2264,11 @@ alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 -1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) -1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) -1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary; Using join buffer (incremental, BNL join) +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Start temporary +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) 1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as SELECT @@ -2892,8 +2892,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort -1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) @@ -2906,8 +2906,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort -1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 5729e86ce70..dc1d9b407fd 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -505,15 +505,15 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index; Start temporary 1 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`)))))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`)))))))) explain extended select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -1087,11 +1087,11 @@ create index it1a on t1(a); 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 <subquery2> ALL distinct_key NULL NULL NULL 6 100.00 -1 PRIMARY t1 ref it1a it1a 4 test.t2.c 2 100.00 Using index +1 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 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)) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` >= 20)) select a from t1 where a in (select c from t2 where d >= 20); a 2 diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result index 2a3768c8c50..c7e04225ffe 100644 --- a/mysql-test/r/subselect_sj_nonmerged.result +++ b/mysql-test/r/subselect_sj_nonmerged.result @@ -77,8 +77,8 @@ explain select * from t4 where t4.a in (select max(t2.a) from t1, t2 group by t2.b) and t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) 1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 12 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 9b6ae60b3b5..8939baf1864 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4628,6 +4628,45 @@ NULL NULL 1 0 NULL NULL 1 0 DROP VIEW v2; DROP TABLE t1, t2, t3; +# +# BUG#915222: Valgrind complains or crashes with INSERT SELECT +# within a trigger that uses a view +# +CREATE TABLE t1 (a char(1)); +CREATE TABLE t2 (d int, e char(1)); +INSERT INTO t2 VALUES (13,'z'); +CREATE TRIGGER tr AFTER UPDATE ON t2 +FOR EACH ROW +REPLACE INTO t3 +SELECT f, a AS alias FROM t3, v; +CREATE TABLE t3 (f int, g char(8)); +CREATE VIEW v AS SELECT a, e FROM t2, t1; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +DROP TRIGGER tr; +DROP VIEW v; +DROP TABLE t1,t2,t3; +# +# BUG#972943: Assertion failure with INSERT SELECT within a trigger +# that uses derived table and materialized view +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,0), (2,8); +CREATE ALGORITHM=TEMPTABLE VIEW v1 +AS SELECT * FROM t1; +CREATE TABLE t2 (c int); +CREATE TABLE t3 (d int, e int); +CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW +INSERT INTO t3 +SELECT t1.* +FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1 +WHERE t1.a = 3 OR t1.a > 5; +INSERT INTO t2 VALUES (1); +DROP TRIGGER tr; +DROP VIEW v1; +DROP TABLE t1,t2,t3; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test index 8b48c1723fd..70566da4cfa 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test +++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test @@ -141,7 +141,7 @@ perl; use strict; use Cwd; my $log_error= $ENV{'LOG_ERROR'} or die "LOG_ERROR not set"; - open(FILE, "$log_error") or die("Unable to open '$log_error' from directory " . cwd() . "\n"); + open(FILE, "$log_error") or die("Unable to open '$log_error' from directory " . cwd() . " :$! \n"); my $binlog_counter= $ENV{'BINLOG_COUNTER1'} or die "BINLOG_COUNTER1 not set"; my $count = () = grep(/$binlog_counter/g,<FILE>); # Grep the timestamp value from the error file. diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 148964d8d96..768a21912a2 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1820,6 +1820,21 @@ INSERT INTO t1 VALUES(-1); SELECT * FROM t1; DROP TABLE t1; +# +# Bug#970528 +# Server crashes in my_strnncollsp_simple on LEFT JOIN with CSV table, +# TEXT field +# + +CREATE TABLE t1 ( b TEXT NOT NULL ) ENGINE=MYISAM; +INSERT INTO t1 VALUES ('x'),('y'); + +CREATE TABLE t2 ( a VARCHAR(1) NOT NULL ) ENGINE=CSV; +INSERT INTO t2 VALUES ('r'),('t'); + +SELECT * FROM t2 ORDER BY a; +SELECT * FROM t1 LEFT JOIN t2 ON ( b = a ); +drop table t1,t2; # # Bug #40814 CSV engine does not parse \X characters when they occur in unquoted fields diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index d1ed2ff5ba6..03d308b6c45 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1380,6 +1380,43 @@ SET SESSION optimizer_switch= @save_optimizer_switch; DROP VIEW v; DROP TABLE t1,t2; +--echo # +--echo # LP BUG#968720 crash due to converting to materialized and +--echo # natural join made only once +--echo # + +SET @save968720_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; + +CREATE TABLE t1 (a int, INDEX(a)); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (a int, INDEX(a)); +INSERT INTO t2 VALUES (1), (2); + +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +SELECT * FROM t1; + +DELETE FROM t1; +INSERT INTO t1 VALUES (1); + +PREPARE stmt FROM " +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +"; +EXECUTE stmt; +SELECT * FROM t1; +EXECUTE stmt; +SELECT * FROM t1; + +drop table t1,t2; +set optimizer_switch=@save968720_optimizer_switch; + +--echo # +--echo # end of 5.3 tests +--echo # + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 04d1f9e9d9c..92fc500cf55 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -78,13 +78,7 @@ insert into t3 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; -# -# InnoDB does not use join buffer here, XtraDB does -# (despite the comment above which says "no join buffering", -# because it does not hold when this file is included -# into subselect_sj2_jcl6.test) -# -#--replace_regex /Using join buffer// +--replace_column 9 # explain select * from t3 where b in (select a from t0); select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 79c733ed791..6208ae7a057 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4546,6 +4546,61 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM DROP VIEW v2; DROP TABLE t1, t2, t3; +--echo # +--echo # BUG#915222: Valgrind complains or crashes with INSERT SELECT +--echo # within a trigger that uses a view +--echo # + +CREATE TABLE t1 (a char(1)); + +CREATE TABLE t2 (d int, e char(1)); + +INSERT INTO t2 VALUES (13,'z'); + +CREATE TRIGGER tr AFTER UPDATE ON t2 + FOR EACH ROW + REPLACE INTO t3 + SELECT f, a AS alias FROM t3, v; + +CREATE TABLE t3 (f int, g char(8)); + +CREATE VIEW v AS SELECT a, e FROM t2, t1; + +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; + +DROP TRIGGER tr; +DROP VIEW v; +DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#972943: Assertion failure with INSERT SELECT within a trigger +--echo # that uses derived table and materialized view +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,0), (2,8); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 + AS SELECT * FROM t1; + +CREATE TABLE t2 (c int); +CREATE TABLE t3 (d int, e int); + +CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW + INSERT INTO t3 + SELECT t1.* + FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1 + WHERE t1.a = 3 OR t1.a > 5; + +INSERT INTO t2 VALUES (1); + +DROP TRIGGER tr; +DROP VIEW v1; +DROP TABLE t1,t2,t3; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh index b4efbdcbe63..1dd6b787c43 100644 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@ -1,6 +1,6 @@ #!/bin/sh # Copyright (c) 2000, 2011, Oracle and/or its affiliates. -# Copyright (c) 2009-2011 Monty Program Ab +# Copyright (c) 2009, 2011, Monty Program Ab # # 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 diff --git a/sql/filesort.cc b/sql/filesort.cc index e2bd83f1ac4..bec43d98e9e 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -1,5 +1,5 @@ /* - Copyright (c) 2000, 2011, Oracle and/or its affiliates. + Copyright (c) 2000, 2011, Oracle and/or its affiliates. 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 diff --git a/sql/item.cc b/sql/item.cc index 8eb05d49d5d..4fdec8eaca0 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1001,6 +1001,21 @@ void Item::set_name(const char *str, uint length, CHARSET_INFO *cs) } +void Item::set_name_for_rollback(THD *thd, const char *str, uint length, + CHARSET_INFO *cs) +{ + char *old_name, *new_name; + old_name= name; + set_name(str, length, cs); + new_name= name; + if (old_name != new_name) + { + name= old_name; + thd->change_item_tree((Item **) &name, (Item *) new_name); + } +} + + /** @details This function is called when: diff --git a/sql/item.h b/sql/item.h index cf13f501c69..a28cc402d2d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -656,6 +656,8 @@ public: #endif } /*lint -e1509 */ void set_name(const char *str, uint length, CHARSET_INFO *cs); + void set_name_for_rollback(THD *thd, const char *str, uint length, + CHARSET_INFO *cs); void rename(char *new_name); void init_make_field(Send_field *tmp_field,enum enum_field_types type); virtual void cleanup(); diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index bfd74767669..b1829c1892a 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2531,7 +2531,8 @@ void Item_in_subselect::update_used_tables() { Item_subselect::update_used_tables(); left_expr->update_used_tables(); - used_tables_cache |= left_expr->used_tables(); + //used_tables_cache |= left_expr->used_tables(); + used_tables_cache= Item_subselect::used_tables() | left_expr->used_tables(); } diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 633158ae06c..857d9bc2080 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2435,7 +2435,7 @@ bool Item_time_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) bool Item_date_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) { - if (get_arg0_date(ltime, TIME_FUZZY_DATE)) + if (get_arg0_date(ltime, fuzzy_date & ~TIME_TIME_ONLY)) return 1; ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0; @@ -3145,7 +3145,7 @@ bool Item_func_str_to_date::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) bool Item_func_last_day::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) { - if (get_arg0_date(ltime, fuzzy_date & ~TIME_FUZZY_DATE) || + if (get_arg0_date(ltime, fuzzy_date) || (ltime->month == 0)) return (null_value=1); uint month_idx= ltime->month-1; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 202e97dc190..56b76f5982e 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1475,6 +1475,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) sj_nest->embedding= emb_tbl_nest; sj_nest->alias= (char*) "(sj-nest)"; sj_nest->sj_subq_pred= subq_pred; + sj_nest->original_subq_pred_used_tables= subq_pred->used_tables() | + subq_pred->left_expr->used_tables(); /* Nests do not participate in those 'chains', so: */ /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/ emb_join_list->push_back(sj_nest); diff --git a/sql/password.c b/sql/password.c index f4ff3156bec..1a12a81f7c6 100644 --- a/sql/password.c +++ b/sql/password.c @@ -503,7 +503,7 @@ check_scramble(const uchar *scramble_arg, const char *message, mysql_sha1_reset(&sha1_context); mysql_sha1_input(&sha1_context, buf, SHA1_HASH_SIZE); mysql_sha1_result(&sha1_context, hash_stage2_reassured); - return memcmp(hash_stage2, hash_stage2_reassured, SHA1_HASH_SIZE); + return test(memcmp(hash_stage2, hash_stage2_reassured, SHA1_HASH_SIZE)); } diff --git a/sql/protocol.cc b/sql/protocol.cc index 63b945f7078..10c85939986 100644 --- a/sql/protocol.cc +++ b/sql/protocol.cc @@ -1,5 +1,5 @@ -/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. - Copyright (c) 2008-2011 Monty Program Ab +/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. + Copyright (c) 2008, 2011, Monty Program Ab 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 diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 110f140afe0..624fc87815c 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6162,15 +6162,22 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list, */ if (*ref && !(*ref)->is_autogenerated_name) { - if (register_tree_change && - thd->stmt_arena->is_stmt_prepare_or_first_stmt_execute()) - arena= thd->activate_stmt_arena_if_needed(&backup); - item->set_name((*ref)->name, (*ref)->name_length, - system_charset_info); - item->real_item()->set_name((*ref)->name, (*ref)->name_length, - system_charset_info); - if (arena) - thd->restore_active_arena(arena, &backup); + if (register_tree_change) + { + item->set_name_for_rollback(thd, (*ref)->name, + (*ref)->name_length, + system_charset_info); + item->real_item()->set_name_for_rollback(thd, (*ref)->name, + (*ref)->name_length, + system_charset_info); + } + else + { + item->set_name((*ref)->name, (*ref)->name_length, + system_charset_info); + item->real_item()->set_name((*ref)->name, (*ref)->name_length, + system_charset_info); + } } if (register_tree_change) thd->change_item_tree(ref, item); @@ -7452,6 +7459,14 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, */ result= FALSE; + /* + Save the lists made during natural join matching (because + the matching done only once but we need the list in case + of prepared statements). + */ + table_ref_1->persistent_used_items= table_ref_1->used_items; + table_ref_2->persistent_used_items= table_ref_2->used_items; + err: if (arena) thd->restore_active_arena(arena, &backup); diff --git a/sql/sql_list.h b/sql/sql_list.h index 6cc05ff1f62..e3a04c36071 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -260,6 +260,7 @@ public: last= &first; return tmp->info; } + /* Remove from this list elements that are contained in the passed list. We assume that the passed list is a tail of this list (that is, the whole diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d905516b075..338b0475626 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3131,6 +3131,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, table->pos_in_table_list= tables; error= tables->fetch_number_of_rows(); +#ifdef WITH_PARTITION_STORAGE_ENGINE + const bool no_partitions_used= table->no_partitions_used; +#else + const bool no_partitions_used= FALSE; +#endif + DBUG_EXECUTE_IF("bug11747970_raise_error", { if (!error) @@ -3162,13 +3168,10 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (*s->on_expr_ref) { /* s is the only inner table of an outer join */ -#ifdef WITH_PARTITION_STORAGE_ENGINE - if (!table->is_filled_at_execution() && - (!table->file->stats.records || table->no_partitions_used) && !embedding) -#else if (!table->is_filled_at_execution() && - !table->file->stats.records && !embedding) -#endif + ((!table->file->stats.records && + (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) || + no_partitions_used) && !embedding) { // Empty table s->dependent= 0; // Ignore LEFT JOIN depend. no_rows_const_tables |= table->map; @@ -3208,16 +3211,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (inside_an_outer_join) continue; } -#ifdef WITH_PARTITION_STORAGE_ENGINE - const bool no_partitions_used= table->no_partitions_used; -#else - const bool no_partitions_used= FALSE; -#endif - if (!table->is_filled_at_execution() && - (table->s->system || table->file->stats.records <= 1 || + if (!table->is_filled_at_execution() && + (table->s->system || + (table->file->stats.records <= 1 && + (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) || no_partitions_used) && !s->dependent && - (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) && !table->fulltext_searched && !join->no_const_tables) { set_position(join,const_count++,s,(KEYUSE*) 0); @@ -5766,6 +5765,114 @@ best_access_path(JOIN *join, } +/* + Find JOIN_TAB's embedding (i.e, parent) subquery. + - For merged semi-joins, tables inside the semi-join nest have their + semi-join nest as parent. We intentionally ignore results of table + pullout action here. + - For non-merged semi-joins (JTBM tabs), the embedding subquery is the + JTBM join tab itself. +*/ + +static TABLE_LIST* get_emb_subq(JOIN_TAB *tab) +{ + TABLE_LIST *tlist= tab->table->pos_in_table_list; + if (tlist->jtbm_subselect) + return tlist; + TABLE_LIST *embedding= tlist->embedding; + if (!embedding || !embedding->sj_subq_pred) + return NULL; + return embedding; +} + + +/* + Choose initial table order that "helps" semi-join optimizations. + + The idea is that we should start with the order that is the same as the one + we would have had if we had semijoin=off: + - Top-level tables go first + - subquery tables are grouped together by the subquery they are in, + - subquery tables are attached where the subquery predicate would have been + attached if we had semi-join off. + + This function relies on join_tab_cmp()/join_tab_cmp_straight() to produce + certain pre-liminary ordering, see compare_embedding_subqueries() for its + description. +*/ + +static void choose_initial_table_order(JOIN *join) +{ + TABLE_LIST *emb_subq; + JOIN_TAB **tab= join->best_ref + join->const_tables; + JOIN_TAB **tabs_end= tab + join->table_count - join->const_tables; + /* Find where the top-level JOIN_TABs end and subquery JOIN_TABs start */ + for (; tab != tabs_end; tab++) + { + if ((emb_subq= get_emb_subq(*tab))) + break; + } + uint n_subquery_tabs= tabs_end - tab; + + if (!n_subquery_tabs) + return; + + /* Copy the subquery JOIN_TABs to a separate array */ + JOIN_TAB *subquery_tabs[MAX_TABLES]; + memcpy(subquery_tabs, tab, sizeof(JOIN_TAB*) * n_subquery_tabs); + + JOIN_TAB **last_top_level_tab= tab; + JOIN_TAB **subq_tab= subquery_tabs; + JOIN_TAB **subq_tabs_end= subquery_tabs + n_subquery_tabs; + TABLE_LIST *cur_subq_nest= NULL; + for (; subq_tab < subq_tabs_end; subq_tab++) + { + if (get_emb_subq(*subq_tab)!= cur_subq_nest) + { + /* + Reached the part of subquery_tabs that covers tables in some subquery. + */ + cur_subq_nest= get_emb_subq(*subq_tab); + + /* Determine how many tables the subquery has */ + JOIN_TAB **last_tab_for_subq; + for (last_tab_for_subq= subq_tab; + last_tab_for_subq < subq_tabs_end && + get_emb_subq(*last_tab_for_subq) == cur_subq_nest; + last_tab_for_subq++) {} + uint n_subquery_tables= last_tab_for_subq - subq_tab; + + /* + Walk the original array and find where this subquery would have been + attached to + */ + table_map need_tables= cur_subq_nest->original_subq_pred_used_tables; + need_tables &= ~(join->const_table_map | PSEUDO_TABLE_BITS); + for (JOIN_TAB **top_level_tab= join->best_ref + join->const_tables; + top_level_tab < last_top_level_tab; + //top_level_tab < join->best_ref + join->table_count; + top_level_tab++) + { + need_tables &= ~(*top_level_tab)->table->map; + /* Check if this is the place where subquery should be attached */ + if (!need_tables) + { + /* Move away the top-level tables that are after top_level_tab */ + uint top_tail_len= last_top_level_tab - top_level_tab - 1; + memmove(top_level_tab + 1 + n_subquery_tables, top_level_tab + 1, + sizeof(JOIN_TAB*)*top_tail_len); + last_top_level_tab += n_subquery_tables; + memcpy(top_level_tab + 1, subq_tab, sizeof(JOIN_TAB*)*n_subquery_tables); + break; + } + } + DBUG_ASSERT(!need_tables); + subq_tab += n_subquery_tables - 1; + } + } +} + + /** Selects and invokes a search strategy for an optimal query plan. @@ -5821,9 +5928,21 @@ choose_plan(JOIN *join, table_map join_tables) */ jtab_sort_func= straight_join ? join_tab_cmp_straight : join_tab_cmp; } + + /* + psergey-todo: if we're not optimizing an SJM nest, + - sort that outer tables are first, and each sjm nest follows + - then, put each [sjm_table1, ... sjm_tableN] sub-array right where + WHERE clause pushdown would have put it. + */ my_qsort2(join->best_ref + join->const_tables, join->table_count - join->const_tables, sizeof(JOIN_TAB*), jtab_sort_func, (void*)join->emb_sjm_nest); + + if (!join->emb_sjm_nest) + { + choose_initial_table_order(join); + } join->cur_sj_inner_tables= 0; if (straight_join) @@ -5863,6 +5982,64 @@ choose_plan(JOIN *join, table_map join_tables) } +/* + Compare two join tabs based on the subqueries they are from. + - top-level join tabs go first + - then subqueries are ordered by their select_id (we're using this + criteria because we need a cross-platform, deterministic ordering) + + @return + 0 - equal + -1 - jt1 < jt2 + 1 - jt1 > jt2 +*/ + +static int compare_embedding_subqueries(JOIN_TAB *jt1, JOIN_TAB *jt2) +{ + /* Determine if the first table is originally from a subquery */ + TABLE_LIST *tbl1= jt1->table->pos_in_table_list; + uint tbl1_select_no; + if (tbl1->jtbm_subselect) + { + tbl1_select_no= + tbl1->jtbm_subselect->unit->first_select()->select_number; + } + else if (tbl1->embedding && tbl1->embedding->sj_subq_pred) + { + tbl1_select_no= + tbl1->embedding->sj_subq_pred->unit->first_select()->select_number; + } + else + tbl1_select_no= 1; /* Top-level */ + + /* Same for the second table */ + TABLE_LIST *tbl2= jt2->table->pos_in_table_list; + uint tbl2_select_no; + if (tbl2->jtbm_subselect) + { + tbl2_select_no= + tbl2->jtbm_subselect->unit->first_select()->select_number; + } + else if (tbl2->embedding && tbl2->embedding->sj_subq_pred) + { + tbl2_select_no= + tbl2->embedding->sj_subq_pred->unit->first_select()->select_number; + } + else + tbl2_select_no= 1; /* Top-level */ + + /* + Put top-level tables in front. Tables from within subqueries must follow, + grouped by their owner subquery. We don't care about the order that + subquery groups are in, because choose_initial_table_order() will re-order + the groups. + */ + if (tbl1_select_no != tbl2_select_no) + return tbl1_select_no > tbl2_select_no ? 1 : -1; + return 0; +} + + /** Compare two JOIN_TAB objects based on the number of accessed records. @@ -5879,6 +6056,9 @@ choose_plan(JOIN *join, table_map join_tables) 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 + + As for subuqueries, this function must produce order that can be fed to + choose_initial_table_order(). @retval 1 if first is bigger @@ -5893,7 +6073,15 @@ join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2) { JOIN_TAB *jt1= *(JOIN_TAB**) ptr1; JOIN_TAB *jt2= *(JOIN_TAB**) ptr2; + int cmp; + if ((cmp= compare_embedding_subqueries(jt1, jt2)) != 0) + return cmp; + /* + After that, + take care about ordering imposed by LEFT JOIN constraints, + possible [eq]ref accesses, and numbers of matching records in the table. + */ if (jt1->dependent & jt2->table->map) return 1; if (jt2->dependent & jt1->table->map) @@ -5924,6 +6112,10 @@ join_tab_cmp_straight(const void *dummy, const void* ptr1, const void* ptr2) DBUG_ASSERT(!jt1->emb_sj_nest); DBUG_ASSERT(!jt2->emb_sj_nest); + int cmp; + if ((cmp= compare_embedding_subqueries(jt1, jt2)) != 0) + return cmp; + if (jt1->dependent & jt2->table->map) return 1; if (jt2->dependent & jt1->table->map) @@ -15230,6 +15422,7 @@ free_tmp_table(THD *thd, TABLE *entry) else entry->file->ha_delete_table(entry->s->table_name.str); delete entry->file; + entry->file= 0; } /* free blobs */ diff --git a/sql/table.cc b/sql/table.cc index fa5fff365d7..dc8ab3c6b42 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4043,7 +4043,21 @@ bool TABLE_LIST::create_field_translation(THD *thd) Query_arena *arena= thd->stmt_arena, backup; bool res= FALSE; - used_items.empty(); + if (thd->stmt_arena->is_conventional() || + thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()) + { + /* initialize lists */ + used_items.empty(); + persistent_used_items.empty(); + } + else + { + /* + Copy the list created by natural join procedure because the procedure + will not be repeated. + */ + used_items= persistent_used_items; + } if (field_translation) { @@ -5107,7 +5121,7 @@ Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref, if (view->table && view->table->maybe_null) item->maybe_null= TRUE; /* Save item in case we will need to fall back to materialization. */ - view->used_items.push_back(item); + view->used_items.push_front(item); DBUG_RETURN(item); } @@ -6678,7 +6692,11 @@ bool TABLE_LIST::change_refs_to_fields() if (!materialized_items[idx]) return TRUE; } - ref->ref= materialized_items + idx; + /* + We need to restore the pointers after the execution of the + prepared statement. + */ + thd->change_item_tree((Item **)&ref->ref, (Item*)materialized_items + idx); } return FALSE; diff --git a/sql/table.h b/sql/table.h index 13b0274b3aa..7b50caae536 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1574,6 +1574,8 @@ struct TABLE_LIST /* If this is a non-jtbm semi-join nest: corresponding subselect predicate */ Item_in_subselect *sj_subq_pred; + table_map original_subq_pred_used_tables; + /* If this is a jtbm semi-join object: corresponding subselect predicate */ Item_in_subselect *jtbm_subselect; /* TODO: check if this can be joined with tablenr_exec */ @@ -1853,7 +1855,13 @@ struct TABLE_LIST /* TRUE <=> don't prepare this derived table/view as it should be merged.*/ bool skip_prepare_derived; + /* + Items created by create_view_field and collected to change them in case + of materialization of the view/derived table + */ List<Item> used_items; + /* Sublist (tail) of persistent used_items */ + List<Item> persistent_used_items; Item **materialized_items; /* View creation context. */ diff --git a/sql/unireg.h b/sql/unireg.h index 50aaa103b34..f8317a89c8c 100644 --- a/sql/unireg.h +++ b/sql/unireg.h @@ -2,7 +2,7 @@ #define UNIREG_INCLUDED /* - Copyright (c) 2000, 2010, Oracle and/or its affiliates. + Copyright (c) 2000, 2011, Oracle and/or its affiliates. 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 diff --git a/storage/csv/ha_tina.cc b/storage/csv/ha_tina.cc index 9091c101183..d31e5ee8d89 100644 --- a/storage/csv/ha_tina.cc +++ b/storage/csv/ha_tina.cc @@ -961,6 +961,7 @@ int ha_tina::open(const char *name, int mode, uint open_options) */ thr_lock_data_init(&share->lock, &lock, (void*) this); ref_length= sizeof(my_off_t); + init_alloc_root(&blobroot, BLOB_MEMROOT_ALLOC_SIZE, 0); share->lock.get_status= tina_get_status; share->lock.update_status= tina_update_status; @@ -978,6 +979,7 @@ int ha_tina::close(void) { int rc= 0; DBUG_ENTER("ha_tina::close"); + free_root(&blobroot, MYF(0)); rc= mysql_file_close(data_file, MYF(0)); DBUG_RETURN(free_share(share) || rc); } @@ -1196,7 +1198,7 @@ int ha_tina::rnd_init(bool scan) current_position= next_position= 0; stats.records= 0; - records_is_known= 0; + records_is_known= found_end_of_file= 0; chain_ptr= chain; DBUG_RETURN(0); @@ -1246,6 +1248,7 @@ int ha_tina::rnd_next(uchar *buf) stats.records++; rc= 0; end: + found_end_of_file= (rc == HA_ERR_END_OF_FILE); MYSQL_READ_ROW_DONE(rc); DBUG_RETURN(rc); } @@ -1345,8 +1348,7 @@ int ha_tina::rnd_end() my_off_t file_buffer_start= 0; DBUG_ENTER("ha_tina::rnd_end"); - free_root(&blobroot, MYF(0)); - records_is_known= 1; + records_is_known= found_end_of_file; if ((chain_ptr - chain) > 0) { @@ -1759,6 +1761,13 @@ int ha_tina::check(THD* thd, HA_CHECK_OPT* check_opt) } +int ha_tina::reset(void) +{ + free_root(&blobroot, MYF(0)); + return 0; +} + + bool ha_tina::check_if_incompatible_data(HA_CREATE_INFO *info, uint table_changes) { diff --git a/storage/csv/ha_tina.h b/storage/csv/ha_tina.h index d024b31c127..88af2c9652c 100644 --- a/storage/csv/ha_tina.h +++ b/storage/csv/ha_tina.h @@ -81,7 +81,7 @@ class ha_tina: public handler uchar chain_alloced; uint32 chain_size; uint local_data_file_version; /* Saved version of the data file used */ - bool records_is_known; + bool records_is_known, found_end_of_file; MEM_ROOT blobroot; private: @@ -155,6 +155,7 @@ public: bool auto_repair() const { return 1; } void position(const uchar *record); int info(uint); + int reset(); int extra(enum ha_extra_function operation); int delete_all_rows(void); int create(const char *name, TABLE *form, HA_CREATE_INFO *create_info); diff --git a/storage/heap/hp_write.c b/storage/heap/hp_write.c index 0169d12392d..8bfecc7747c 100644 --- a/storage/heap/hp_write.c +++ b/storage/heap/hp_write.c @@ -105,7 +105,7 @@ int hp_rb_write_key(HP_INFO *info, HP_KEYDEF *keyinfo, const uchar *record, uchar *recpos) { heap_rb_param custom_arg; - uint old_allocated; + size_t old_allocated; custom_arg.keyseg= keyinfo->seg; custom_arg.key_length= hp_rb_make_key(keyinfo, info->recbuf, record, recpos); diff --git a/strings/ctype-utf8.c b/strings/ctype-utf8.c index 466114f9c6b..88bab1fac76 100644 --- a/strings/ctype-utf8.c +++ b/strings/ctype-utf8.c @@ -1678,7 +1678,7 @@ MY_UNICASE_INFO *const my_unicase_default[256]={ /* Reproduce old utf8_general_ci behaviour before we fixed Bug#27877. */ -MY_UNICASE_INFO *my_unicase_mysql500[256]={ +MY_UNICASE_INFO *const my_unicase_mysql500[256]={ plane00_mysql500, plane01, plane02, plane03, plane04, plane05, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, diff --git a/strings/decimal.c b/strings/decimal.c index 1a99816b914..51c4457d934 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -1,5 +1,5 @@ /* Copyright (c) 2004, 2011, Oracle and/or its affiliates. - Copyright (c) 2009-2011, Monty Program Ab + Copyright (c) 2009, 2011, Monty Program Ab 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 |