diff options
83 files changed, 1905 insertions, 591 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index 4d320964e39..8811bc4ef89 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -2693,15 +2693,18 @@ static void dump_table(char *table, char *db) plus 2 bytes for '0x' prefix. - In non-HEX mode we need up to 2 bytes per character, plus 2 bytes for leading and trailing '\'' characters. + Also we need to reserve 1 byte for terminating '\0'. */ - dynstr_realloc_checked(&extended_row,length * 2+2); + dynstr_realloc_checked(&extended_row,length * 2 + 2 + 1); if (opt_hex_blob && is_blob) { dynstr_append_checked(&extended_row, "0x"); extended_row.length+= mysql_hex_string(extended_row.str + extended_row.length, row[i], length); - extended_row.str[extended_row.length]= '\0'; + DBUG_ASSERT(extended_row.length+1 <= extended_row.max_length); + /* mysql_hex_string() already terminated string by '\0' */ + DBUG_ASSERT(extended_row.str[extended_row.length] == '\0'); } else { diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 63dc13a4b6d..a9c01f308f7 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -846,6 +846,25 @@ id 50 51 drop table t1; +set @orig_sql_mode = @@sql_mode; +set sql_mode="no_zero_date"; +create table t1(f1 int); +alter table t1 add column f2 datetime not null, add column f21 date not null; +insert into t1 values(1,'2000-01-01','2000-01-01'); +alter table t1 add column f3 datetime not null; +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'f3' at row 1 +alter table t1 add column f3 date not null; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'f3' at row 1 +alter table t1 add column f4 datetime not null default '2002-02-02', +add column f41 date not null; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'f41' at row 1 +alter table t1 add column f4 datetime not null default '2002-02-02', +add column f41 date not null default '2002-02-02'; +select * from t1; +f1 f2 f21 f4 f41 +1 2000-01-01 00:00:00 2000-01-01 2002-02-02 00:00:00 2002-02-02 +drop table t1; +set sql_mode= @orig_sql_mode; create table t1 (v varchar(32)); insert into t1 values ('def'),('abc'),('hij'),('3r4f'); select * from t1; @@ -1137,3 +1156,17 @@ Field Type Null Key Default Extra unsigned_int_field bigint(20) unsigned NO MUL char_field char(10) YES NULL DROP TABLE t2; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); +INSERT INTO t1 VALUES (1, 2, NULL); +SELECT * FROM t1; +f1 f2 f3 +1 2 NULL +ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; +SELECT * FROM t1; +f1 f3 f2 +1 NULL 2 +ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; +SELECT * FROM t1; +f1 f2 f3 +1 2 NULL +DROP TABLE t1; diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index b1ed1ea2bed..c0b16778f9c 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -39,10 +39,10 @@ t2 CREATE TABLE `t2` ( `Field_name` varbinary(255) NOT NULL DEFAULT '', `Min_value` varbinary(255) DEFAULT NULL, `Max_value` varbinary(255) DEFAULT NULL, - `Min_length` int(11) NOT NULL DEFAULT '0', - `Max_length` int(11) NOT NULL DEFAULT '0', - `Empties_or_zeros` int(11) NOT NULL DEFAULT '0', - `Nulls` int(11) NOT NULL DEFAULT '0', + `Min_length` bigint(11) NOT NULL DEFAULT '0', + `Max_length` bigint(11) NOT NULL DEFAULT '0', + `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0', + `Nulls` bigint(11) NOT NULL DEFAULT '0', `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '', `Std` varbinary(255) DEFAULT NULL, `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT '' @@ -58,10 +58,10 @@ t2 CREATE TABLE `t2` ( `Field_name` varbinary(255) NOT NULL DEFAULT '', `Min_value` varbinary(255) DEFAULT NULL, `Max_value` varbinary(255) DEFAULT NULL, - `Min_length` int(11) NOT NULL DEFAULT '0', - `Max_length` int(11) NOT NULL DEFAULT '0', - `Empties_or_zeros` int(11) NOT NULL DEFAULT '0', - `Nulls` int(11) NOT NULL DEFAULT '0', + `Min_length` bigint(11) NOT NULL DEFAULT '0', + `Max_length` bigint(11) NOT NULL DEFAULT '0', + `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0', + `Nulls` bigint(11) NOT NULL DEFAULT '0', `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '', `Std` varbinary(255) DEFAULT NULL, `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT '' @@ -81,10 +81,10 @@ t2 CREATE TABLE `t2` ( `Field_name` varbinary(255) NOT NULL DEFAULT '', `Min_value` varbinary(255) DEFAULT NULL, `Max_value` varbinary(255) DEFAULT NULL, - `Min_length` int(11) NOT NULL DEFAULT '0', - `Max_length` int(11) NOT NULL DEFAULT '0', - `Empties_or_zeros` int(11) NOT NULL DEFAULT '0', - `Nulls` int(11) NOT NULL DEFAULT '0', + `Min_length` bigint(11) NOT NULL DEFAULT '0', + `Max_length` bigint(11) NOT NULL DEFAULT '0', + `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0', + `Nulls` bigint(11) NOT NULL DEFAULT '0', `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '', `Std` varbinary(255) DEFAULT NULL, `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT '' diff --git a/mysql-test/r/archive_gis.result b/mysql-test/r/archive_gis.result index 71eeb063d59..901f8a9acc2 100644 --- a/mysql-test/r/archive_gis.result +++ b/mysql-test/r/archive_gis.result @@ -402,8 +402,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index e46b56f4215..68206c0dc0e 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -1434,8 +1434,8 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 3abba528164..8601e6890de 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1454,8 +1454,8 @@ insert into t1 values ('123'), ('456'); explain select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort; Using join cache -1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; substr(Z.a,-1) a 3 123 diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 750d542d14d..7da82ada121 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -57,8 +57,8 @@ a b a b 3 c 3 c explain select * from t1 as x1, (select * from t1) as x2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY x1 ALL NULL NULL NULL NULL 4 Using join cache -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +1 PRIMARY x1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer 2 DERIVED t1 ALL NULL NULL NULL NULL 4 drop table if exists t2,t3; select * from (select 1) as a; @@ -188,14 +188,14 @@ pla_id test 105 3 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY m2 ALL NULL NULL NULL NULL 9 Using join cache -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where +1 PRIMARY m2 ALL NULL NULL NULL NULL 9 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY m2 ALL NULL NULL NULL NULL 9 Using join cache -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where +1 PRIMARY m2 ALL NULL NULL NULL NULL 9 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; @@ -245,8 +245,8 @@ a a 2 2 explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using join cache -1 PRIMARY <derived4> ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 2 Using join buffer 4 DERIVED t1 ALL NULL NULL NULL NULL 2 5 UNION t1 ALL NULL NULL NULL NULL 2 NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL @@ -312,8 +312,8 @@ b 3.5000 explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using temporary; Using filesort -2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort; Using join cache -2 DERIVED y ALL NULL NULL NULL NULL 17 Using where +2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort +2 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer drop table t1; create table t2 (a int, b int, primary key (a)); insert into t2 values (1,7),(2,7); diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 3419ce150eb..002dbc6ccb5 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -174,8 +174,8 @@ INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary -1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index; Using join cache -1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct +1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index +1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 @@ -299,12 +299,12 @@ WHERE ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2)) AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary; Using join cache -1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct; Using join cache -1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct; Using join cache -1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Distinct -1 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index; Distinct; Using join cache -1 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Distinct +1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary +1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct; Using join buffer +1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct; Using join buffer +1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer +1 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index; Distinct +1 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer 1 SIMPLE t3_lj ref id id 4 test.j_lj_t3.id 1 Using where; Using index; Distinct SELECT DISTINCT t1.id @@ -514,8 +514,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary; Using join cache -1 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Distinct +1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary +1 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Distinct; Using join buffer EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2 WHERE t1_1.a = t1_2.a; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/func_date_add.result b/mysql-test/r/func_date_add.result index ac5709260fd..a7f2383848d 100644 --- a/mysql-test/r/func_date_add.result +++ b/mysql-test/r/func_date_add.result @@ -84,4 +84,15 @@ CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK 2006-10-03 +create table t1 (a int, b varchar(10)); +insert into t1 values (1, '2001-01-01'),(2, '2002-02-02'); +select '2007-01-01' + interval a day from t1; +'2007-01-01' + interval a day +2007-01-02 +2007-01-03 +select b + interval a day from t1; +b + interval a day +2001-01-02 +2002-02-04 +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index bb09217d9d7..3fd204c84c9 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -769,4 +769,51 @@ Warnings: Warning 1260 1 line(s) were cut by GROUP_CONCAT() SET group_concat_max_len = DEFAULT; DROP TABLE t1; +SET group_concat_max_len= 65535; +CREATE TABLE t1( a TEXT, b INTEGER ); +INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 ); +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +GROUP_CONCAT( a ORDER BY b ) +a,b +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b) +a,b +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +GROUP_CONCAT(DISTINCT a) +a,b +SET group_concat_max_len= 10; +SELECT GROUP_CONCAT(a ORDER BY b) FROM t1; +GROUP_CONCAT(a ORDER BY b) +a,b +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b) +a,b +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +GROUP_CONCAT(DISTINCT a) +a,b +SET group_concat_max_len= 65535; +CREATE TABLE t2( a TEXT ); +INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); +INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) ); +INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); +SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2; +LENGTH( GROUP_CONCAT( DISTINCT a ) ) +10001 +CREATE TABLE t3( a TEXT, b INT ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 ); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1; +LENGTH( GROUP_CONCAT( a ) ) +65534 +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2; +LENGTH( GROUP_CONCAT( a ) ) +65535 +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3; +LENGTH( GROUP_CONCAT( a ) ) +65535 +SET group_concat_max_len= DEFAULT; +DROP TABLE t1, t2, t3; End of 5.0 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 1f640dec0a4..fce01ffd171 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -613,8 +613,8 @@ id select_type table type possible_keys key key_len ref rows Extra explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index; Using join cache -1 SIMPLE t2 range k1 k1 3 NULL 4 Using where; Using index +1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index +1 SIMPLE t2 range k1 k1 3 NULL 4 Using where; Using index; Using join buffer explain select min(a4 - 0.01) from t1; id select_type table type possible_keys key key_len ref rows Extra @@ -650,8 +650,8 @@ id select_type table type possible_keys key key_len ref rows Extra explain select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range k2 k2 4 NULL 6 Using where; Using index; Using join cache -1 SIMPLE t1 index NULL PRIMARY 3 NULL 15 Using index +1 SIMPLE t2 range k2 k2 4 NULL 6 Using where; Using index +1 SIMPLE t1 index NULL PRIMARY 3 NULL 15 Using index; Using join buffer drop table t1, t2; create table t1 (a char(10)); insert into t1 values ('a'),('b'),('c'); diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result index dba546a72de..908e85c1652 100644 --- a/mysql-test/r/func_group_innodb.result +++ b/mysql-test/r/func_group_innodb.result @@ -78,8 +78,8 @@ min(7) 7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer select min(7) from t2i join t1i; min(7) NULL @@ -94,8 +94,8 @@ max(7) 7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer select max(7) from t2i join t1i; max(7) NULL diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 62486936e02..b50eb40182d 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -394,8 +394,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; diff --git a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result index ebd2468c9af..9e8c66722e3 100644 --- a/mysql-test/r/greedy_optimizer.result +++ b/mysql-test/r/greedy_optimizer.result @@ -120,72 +120,72 @@ select @@optimizer_search_depth; 63 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value @@ -200,72 +200,72 @@ select @@optimizer_search_depth; 0 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value @@ -276,24 +276,24 @@ select @@optimizer_search_depth; 1 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value @@ -303,10 +303,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -315,10 +315,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -327,10 +327,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -339,10 +339,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -352,72 +352,72 @@ select @@optimizer_search_depth; 62 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value @@ -432,72 +432,72 @@ select @@optimizer_search_depth; 0 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value @@ -508,24 +508,24 @@ select @@optimizer_search_depth; 1 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value @@ -535,10 +535,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -547,10 +547,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -559,10 +559,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -571,10 +571,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 @@ -584,72 +584,72 @@ select @@optimizer_search_depth; 62 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 807986c5f4e..e3cd90b9b3d 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -536,12 +536,12 @@ a b 1 1 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort; Using join cache -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using join cache -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary +1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer drop table t1,t2; create table t1 (a int, b int); insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index bc07b933895..fcd6c92585e 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2266,8 +2266,8 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index; Using join cache -1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index +1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index e8a37257782..f135d849f76 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -270,8 +270,8 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1 i1 4 const 1 Using join cache -1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where +1 SIMPLE t0 ref i1 i1 4 const 1 +1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra @@ -347,8 +347,8 @@ from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 < 500000 or A.key2 < 3) and (B.key1 < 500000 or B.key2 < 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where; Using join cache -1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where +1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where +1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where; Using join buffer select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 < 500000 or A.key2 < 3) @@ -361,8 +361,8 @@ from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 = 1 or A.key2 = 1) and (B.key1 = 1 or B.key2 = 1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where; Using join cache -1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where +1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where +1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where; Using join buffer select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 = 1 or A.key2 = 1) @@ -376,8 +376,8 @@ from t0 as A, t0 as B where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join cache -1 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where +1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where +1 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join buffer select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A, t0 as B where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 709246edcf1..745ec2e2248 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -289,8 +289,8 @@ explain select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE # ALL NULL NULL NULL NULL 2 Using join cache -1 SIMPLE # ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE # ALL NULL NULL NULL NULL 2 +1 SIMPLE # ALL NULL NULL NULL NULL 2 Using where; Using join buffer select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1; ROUTINE_NAME name diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result index 945eca234bb..7f54a78087b 100644 --- a/mysql-test/r/innodb_gis.result +++ b/mysql-test/r/innodb_gis.result @@ -402,8 +402,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 65e02df29de..ec911a8e3a5 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -184,8 +184,8 @@ min(7) 7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer select min(7) from t2i join t1i; min(7) NULL @@ -200,8 +200,8 @@ max(7) 7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer select max(7) from t2i join t1i; max(7) NULL diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 86c206195b1..fcb141a3510 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -403,8 +403,8 @@ EXPLAIN SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e ORDER BY t1.b, t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where; Using join buffer SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e ORDER BY t1.b, t1.c; e diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index d080b1a2a46..2dd9af8719e 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -228,8 +228,8 @@ LEFT JOIN t8 ON t7.b=t8.b AND t6.b < 10; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using join cache -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1 @@ -543,8 +543,8 @@ WHERE t0.a=1 AND t0.b=t1.b AND (t2.a >= 4 OR t2.c IS NULL); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +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 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 @@ -638,16 +638,16 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +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 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 t5 ALL NULL NULL NULL NULL 3 100.00 Using where 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 -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join cache -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t9.a,t9.b @@ -835,8 +835,8 @@ t2 ON t3.a=1 AND t2.b=t4.b WHERE t1.a <= 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +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 join buffer 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Warnings: @@ -849,8 +849,8 @@ LEFT JOIN (t1,t2) ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join cache -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: @@ -905,16 +905,16 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +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 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 t5 ALL NULL NULL NULL NULL 3 100.00 Using where 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 -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join cache -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t4(b); @@ -955,16 +955,16 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +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 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 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 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 -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join cache -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); @@ -1004,16 +1004,16 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +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 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 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 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 -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join cache -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t1(b); @@ -1062,8 +1062,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 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 -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join cache -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 62dfb36bb52..1e4fc91b8bd 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1239,3 +1239,18 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 6 DROP TABLE t1,t2; +CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL); +INSERT INTO t1 VALUES (1,0), (2,1); +CREATE TABLE t2 (d int PRIMARY KEY); +INSERT INTO t2 VALUES (1), (2), (3); +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Not exists +SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL; +c e d +1 0 NULL +SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL; +c e d +1 0 NULL +DROP TABLE t1,t2; diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result index 35aad39edbf..9d26bee4557 100644 --- a/mysql-test/r/key_diff.result +++ b/mysql-test/r/key_diff.result @@ -35,8 +35,8 @@ D E a a a a a a explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a NULL NULL NULL 5 Using join cache -1 SIMPLE t2 ALL b NULL NULL NULL 5 Using where +1 SIMPLE t1 ALL a NULL NULL NULL 5 +1 SIMPLE t2 ALL b NULL NULL NULL 5 Using where; Using join buffer select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; a b a b A B a a diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result index d198c943496..0d5cd171d2d 100644 --- a/mysql-test/r/kill.result +++ b/mysql-test/r/kill.result @@ -41,3 +41,87 @@ select 1; select RELEASE_LOCK("a"); RELEASE_LOCK("a") 1 +create table t1(f1 int); +create function bug27563() returns int(11) +deterministic +begin +declare continue handler for sqlstate '70100' set @a:= 'killed'; +declare continue handler for sqlexception set @a:= 'exception'; +set @a= get_lock("lock27563", 10); +return 1; +end| +select get_lock("lock27563",10); +get_lock("lock27563",10) +1 +insert into t1 values (bug27563()); +ERROR 70100: Query execution was interrupted +select @a; +@a +NULL +select * from t1; +f1 +insert into t1 values(0); +update t1 set f1= bug27563(); +ERROR 70100: Query execution was interrupted +select @a; +@a +NULL +select * from t1; +f1 +0 +insert into t1 values(1); +delete from t1 where bug27563() is null; +ERROR 70100: Query execution was interrupted +select @a; +@a +NULL +select * from t1; +f1 +0 +1 +select * from t1 where f1= bug27563(); +ERROR 70100: Query execution was interrupted +select @a; +@a +NULL +create procedure proc27563() +begin +declare continue handler for sqlstate '70100' set @a:= 'killed'; +declare continue handler for sqlexception set @a:= 'exception'; +select get_lock("lock27563",10); +select "shouldn't be selected"; +end| +call proc27563(); +get_lock("lock27563",10) +NULL +ERROR 70100: Query execution was interrupted +select @a; +@a +NULL +create table t2 (f2 int); +create trigger trg27563 before insert on t1 for each row +begin +declare continue handler for sqlstate '70100' set @a:= 'killed'; +declare continue handler for sqlexception set @a:= 'exception'; +set @a:= get_lock("lock27563",10); +insert into t2 values(1); +end| +insert into t1 values(2),(3); +ERROR 70100: Query execution was interrupted +select @a; +@a +NULL +select * from t1; +f1 +0 +1 +select * from t2; +f2 +select release_lock("lock27563"); +release_lock("lock27563") +1 +drop table t1, t2; +drop function bug27563; +drop procedure proc27563; +PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40 WHERE a1=a2 AND a2=a3 AND a3=a4 AND a4=a5 AND a5=a6 AND a6=a7 AND a7=a8 AND a8=a9 AND a9=a10 AND a10=a11 AND a11=a12 AND a12=a13 AND a13=a14 AND a14=a15 AND a15=a16 AND a16=a17 AND a17=a18 AND a18=a19 AND a19=a20 AND a20=a21 AND a21=a22 AND a22=a23 AND a23=a24 AND a24=a25 AND a25=a26 AND a26=a27 AND a27=a28 AND a28=a29 AND a29=a30 AND a30=a31 AND a31=a32 AND a32=a33 AND a33=a34 AND a34=a35 AND a35=a36 AND a36=a37 AND a37=a38 AND a38=a39 AND a39=a40 '; +EXECUTE stmt; diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 34e961395c4..d33fb038b79 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -130,3 +130,14 @@ def v3 renamed 8 12 0 Y 32896 0 63 renamed drop table t1; drop view v1,v2,v3; +select a.* from (select 2147483648 as v_large) a; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def a v_large v_large 8 10 10 N 32769 0 63 +v_large +2147483648 +select a.* from (select 214748364 as v_small) a; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def a v_small v_small 3 9 9 N 32769 0 63 +v_small +214748364 +End of 5.0 tests diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index e9d798d998d..73661897ee1 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -345,12 +345,12 @@ t1 1 c_2 1 c A 5 NULL NULL YES BTREE t1 1 c_2 2 a A 5 NULL NULL BTREE explain select * from t1,t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 2 Using join cache -1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where +1 SIMPLE t2 ALL a NULL NULL NULL 2 +1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer explain select * from t1,t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 2 Using join cache -1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where +1 SIMPLE t2 ALL a NULL NULL NULL 2 +1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 @@ -361,8 +361,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref b b 5 test.t2.b 1 Using where explain select * from t1,t2 force index(c) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join cache -1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where @@ -1768,6 +1768,32 @@ create table t3 (c1 int) engine=myisam pack_keys=default; create table t4 (c1 int) engine=myisam pack_keys=2; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 1 drop table t1, t2, t3; +CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM; +INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5); +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +a +1 +ALTER TABLE t1 DISABLE KEYS; +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +a +1 +SELECT a FROM t1 USE INDEX (inx) WHERE a=1; +a +1 +SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1; +b +1 +SELECT b FROM t1 USE INDEX (uinx) WHERE b=1; +b +1 +SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1; +a +1 +ALTER TABLE t1 ENABLE KEYS; +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +a +1 +DROP TABLE t1; End of 5.0 tests create table t1 (a int not null, key `a` (a) key_block_size=1024); show create table t1; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 657f364f53b..b6a0e506eda 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -3309,6 +3309,17 @@ drop user user1; drop user user2; drop database mysqldump_test_db; # +# Bug #28522: buffer overrun by '\0' byte using --hex-blob. +# +CREATE TABLE t1 (c1 INT, c2 LONGBLOB); +INSERT INTO t1 SET c1=11, c2=REPEAT('q',509); +CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` longblob +); +INSERT INTO `t1` VALUES (11,0x7171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171); +DROP TABLE t1; +# # End of 5.0 tests # drop table if exists t1; diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result index df281230c9e..8d1dcc4ef18 100644 --- a/mysql-test/r/ndb_condition_pushdown.result +++ b/mysql-test/r/ndb_condition_pushdown.result @@ -1730,8 +1730,8 @@ pk1 attr1 attr2 attr3 attr4 explain select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where with pushed condition; Using temporary; Using filesort; Using join cache -1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where with pushed condition +1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where with pushed condition; Using temporary; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where with pushed condition; Using join buffer select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1; pk1 attr1 attr2 attr3 pk1 attr1 attr2 attr3 attr4 0 0 0 a 0 0 0 0 a @@ -1746,8 +1746,8 @@ pk1 attr1 attr2 attr3 attr4 explain select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using temporary; Using filesort; Using join cache -1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where +1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using temporary; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where; Using join buffer select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1; pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4 2 2 9223372036854775804 2 c 2 2 9223372036854775804 2 c diff --git a/mysql-test/r/ndb_gis.result b/mysql-test/r/ndb_gis.result index c01c1673e44..3af7b71d72d 100644 --- a/mysql-test/r/ndb_gis.result +++ b/mysql-test/r/ndb_gis.result @@ -402,8 +402,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; @@ -952,8 +952,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index f5544b4d115..67c45b698ce 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -696,8 +696,8 @@ CREATE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; DESC v1; Field Type Null Key Default Extra -a int(11) YES 0 -LENGTH(a) int(10) YES NULL +a bigint(11) YES NULL +LENGTH(a) bigint(10) YES NULL COUNT(*) bigint(21) NO 0 SELECT * FROM v1; a LENGTH(a) COUNT(*) diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index c2cb7426a74..108264b3bf2 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -220,28 +220,28 @@ insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); update t1 set y=x; explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache -1 SIMPLE t2 range x x 5 NULL 2 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache -1 SIMPLE t2 range x x 5 NULL 2 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache -1 SIMPLE t2 range x x 5 NULL 3 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache -1 SIMPLE t2 range x x 5 NULL 3 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache -1 SIMPLE t2 range x x 5 NULL 2 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache -1 SIMPLE t2 range x x 5 NULL 2 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer explain select count(*) from t1 where x in (1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref x x 5 const 1 Using where; Using index @@ -255,13 +255,13 @@ CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref j1 j1 4 const 1 Using index; Using join cache -1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index +1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer explain select * from t1 force index(i1), t2 force index(j1) where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref j1 j1 4 const 1 Using index; Using join cache -1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index +1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer DROP TABLE t1,t2; CREATE TABLE t1 ( a int(11) default NULL, diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 684aee864b3..d3d7988316e 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -376,8 +376,8 @@ a b a b c 3 2 3 1 3 EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index NULL PRIMARY 8 NULL 6 100.00 Using index; Using join cache -1 SIMPLE t2 index NULL PRIMARY 12 NULL 7 100.00 Using where; Using index +1 SIMPLE t1 index NULL PRIMARY 8 NULL 6 100.00 Using index +1 SIMPLE t2 index NULL PRIMARY 12 NULL 7 100.00 Using where; Using index; Using join buffer Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t1`.`a` - 1) = (`test`.`t2`.`a` - 1)) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); diff --git a/mysql-test/r/rpl_view.result b/mysql-test/r/rpl_view.result index f5ddb9e13ba..0d862a2a912 100644 --- a/mysql-test/r/rpl_view.result +++ b/mysql-test/r/rpl_view.result @@ -95,4 +95,10 @@ a b 1 6 drop table t1; drop view v1; +CREATE TABLE t1(a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v1 AS SELECT * FROM t1; +ERROR 42S01: Table 'v1' already exists +DROP VIEW v1; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 1d6ef6fee5c..92ceca2162f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1430,8 +1430,8 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 @@ -2362,16 +2362,16 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where; Using join cache -1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where +1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer select * from t1 left join t2 on a=c where d in (4); a b c d 3 2 3 4 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where; Using join cache -1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where +1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer select * from t1 left join t2 on a=c where d = 4; a b c d 3 2 3 4 @@ -2716,8 +2716,8 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort; Using join cache -1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index +1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort +1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; CREATE TABLE t1 (a int, INDEX idx(a)); @@ -2737,8 +2737,8 @@ INSERT INTO t2 VALUES (1,NULL), (2,10); ALTER TABLE t1 ENABLE KEYS; EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index b b 5 NULL 2 Using index; Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 index b b 5 NULL 2 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; a b a b 1 NULL 1 1 @@ -2747,8 +2747,8 @@ a b a b 2 10 4 10 EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index b b 5 NULL 2 Using index; Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 index b b 5 NULL 2 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; a b a b 1 NULL 1 1 @@ -2901,12 +2901,12 @@ a 4 EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using join cache -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer DROP TABLE t1,t2; select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0 diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 34945f0cb60..86ede7a8f00 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4910,7 +4910,7 @@ create table t3 as select * from v1| show create table t3| Table Create Table t3 CREATE TABLE `t3` ( - `j` int(11) DEFAULT NULL + `j` bigint(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t3| j @@ -6272,3 +6272,14 @@ count(*) 3 drop table t1,t2; drop function bug27354; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12; +CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1; +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`metered`(`t1`.`a`) AS `metered` from `t1` +DROP VIEW v1; +DROP FUNCTION metered; +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 47b86d1a039..1f1a6ec9e22 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -1431,8 +1431,8 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result index e19dcb712cf..e77fcefeafd 100644 --- a/mysql-test/r/ssl_compress.result +++ b/mysql-test/r/ssl_compress.result @@ -1434,8 +1434,8 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 3dc1795a65a..1ad47a3c465 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -904,8 +904,8 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) 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 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index; Using join cache -2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; @@ -1354,8 +1354,8 @@ a explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index; Using join cache -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) insert into t1 values (3,31); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index eca6cd3315c..2f3ea31a2a4 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -254,8 +254,8 @@ select a,b, oref, from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 -2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key; Using join cache -2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2` @@ -742,3 +742,21 @@ x 0 0 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(-1), (65),(66); +CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); +INSERT INTO t2 VALUES (65),(66); +SELECT a FROM t1 WHERE a NOT IN (65,66); +a +1 +-1 +SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); +a +1 +-1 +EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index 70ef98af420..fe63ad6d69e 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1755,6 +1755,13 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; End of 4.1 tests +create table t1(f1 set('a','b'), index(f1)); +insert into t1 values(''),(''),('a'),('b'); +select * from t1 where f1=''; +f1 + + +drop table t1; create table t1(russian enum('E','F','EÿF','FÿE') NOT NULL DEFAULT'E'); show create table t1; Table Create Table diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 855c452cb21..fc6955f11d2 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1475,6 +1475,12 @@ Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 +create table t1 (s varchar(100)); +insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875); +drop table t1; +SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b; +a b +0.9999999999999800000000000000 0.9999999999999800000000000000 End of 5.0 tests select cast(143.481 as decimal(4,1)); cast(143.481 as decimal(4,1)) diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index acf237bf125..6fc630ec33c 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -499,8 +499,8 @@ a b explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using join cache -2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index +2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index +2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index; Using join buffer NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain (select * from t1 where a=1) union (select * from t1 where b=1); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 5e9c62a1fb3..6fd7b39f226 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -317,3 +317,39 @@ SHOW COUNT(*) WARNINGS; SHOW COUNT(*) ERRORS; @@session.error_count 1 +create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1)); +insert into t1 values +(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6), +(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6), +(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6); +select @a:=f1, count(f1) from t1 group by 1 desc; +@a:=f1 count(f1) +4 1 +3 2 +2 1 +1 4 +select @a:=f1, count(f1) from t1 group by 1 asc; +@a:=f1 count(f1) +1 4 +2 1 +3 2 +4 1 +select @a:=f2, count(f2) from t1 group by 1 desc; +@a:=f2 count(f2) +d 1 +c 2 +b 1 +a 4 +select @a:=f3, count(f3) from t1 group by 1 desc; +@a:=f3 count(f3) +4.5 1 +3.5 2 +2.5 1 +1.5 4 +select @a:=f4, count(f4) from t1 group by 1 desc; +@a:=f4 count(f4) +4.6 1 +3.6 2 +2.6 1 +1.6 4 +drop table t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index d1098020b25..5ed06855f19 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2317,8 +2317,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v2 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index; Using join cache -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer DROP VIEW v1,v2; DROP TABLE t1,t2,t3; create table t1 (f1 int); @@ -2771,7 +2771,7 @@ CREATE TABLE t1 (i int, j int); CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; DESCRIBE v1; Field Type Null Key Default Extra -COALESCE(i,j) int(11) YES NULL +COALESCE(i,j) bigint(11) YES NULL CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; DESCRIBE t2; Field Type Null Key Default Extra @@ -3359,6 +3359,94 @@ SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col` DROP VIEW v1; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, c INT DEFAULT 0); +INSERT INTO t1 (a) VALUES (1), (2); +INSERT INTO t2 (b) VALUES (1), (2); +CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 +WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; +SELECT * FROM v1; +b c +1 0 +2 0 +UPDATE v1 SET c=1 WHERE b=1; +SELECT * FROM v1; +b c +1 1 +2 0 +DROP VIEW v1; +DROP TABLE t1,t2; +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int, c int DEFAULT 0); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +CREATE VIEW v1 AS +SELECT t2.c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +UPDATE v1 SET c=1; +DROP VIEW v1; +DROP TABLE t1,t2; +CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); +CREATE TABLE t2 (a2 INT); +CREATE TABLE t3 (a3 INT); +CREATE TABLE t4 (a4 INT); +INSERT INTO t1 (a1) VALUES (1),(2); +INSERT INTO t2 (a2) VALUES (1),(2); +INSERT INTO t3 (a3) VALUES (1),(2); +INSERT INTO t4 (a4) VALUES (1),(2); +CREATE VIEW v1 AS +SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 +WITH CHECK OPTION; +SELECT * FROM v1; +a1 c +1 0 +2 0 +UPDATE v1 SET c=3; +ERROR HY000: CHECK OPTION failed 'test.v1' +PREPARE t FROM 'UPDATE v1 SET c=3'; +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v1' +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v1' +INSERT INTO v1(a1, c) VALUES (3, 3); +ERROR HY000: CHECK OPTION failed 'test.v1' +UPDATE v1 SET c=1 WHERE a1=1; +SELECT * FROM v1; +a1 c +1 1 +2 0 +SELECT * FROM t1; +a1 c +1 1 +2 0 +CREATE VIEW v2 AS SELECT t1.a1, t1.c +FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) +JOIN (t3 JOIN t4 ON t3.a3=t4.a4) +ON t2.a2=t3.a3 WITH CHECK OPTION; +SELECT * FROM v2; +a1 c +1 1 +2 0 +UPDATE v2 SET c=3; +ERROR HY000: CHECK OPTION failed 'test.v2' +PREPARE t FROM 'UPDATE v2 SET c=3'; +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v2' +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v2' +INSERT INTO v2(a1, c) VALUES (3, 3); +ERROR HY000: CHECK OPTION failed 'test.v2' +UPDATE v2 SET c=2 WHERE a1=1; +SELECT * FROM v2; +a1 c +1 2 +2 0 +SELECT * FROM t1; +a1 c +1 2 +2 0 +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3,t4; End of 5.0 tests. DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 7d3e9bba533..fa50d75b8d2 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -631,6 +631,30 @@ insert into t1 values (null); select * from t1; drop table t1; + +# +# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the +# NO_ZERO_DATE mode. +# +set @orig_sql_mode = @@sql_mode; +set sql_mode="no_zero_date"; +create table t1(f1 int); +alter table t1 add column f2 datetime not null, add column f21 date not null; +insert into t1 values(1,'2000-01-01','2000-01-01'); +--error 1292 +alter table t1 add column f3 datetime not null; +--error 1292 +alter table t1 add column f3 date not null; +--error 1292 +alter table t1 add column f4 datetime not null default '2002-02-02', + add column f41 date not null; +alter table t1 add column f4 datetime not null default '2002-02-02', + add column f41 date not null default '2002-02-02'; +select * from t1; +drop table t1; +set sql_mode= @orig_sql_mode; + +# # Some additional tests for new, faster alter table. Note that most of the # whole alter table code is being tested all around the test suite already. # @@ -866,3 +890,15 @@ ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL; DESCRIBE t2; DROP TABLE t2; + +# +# Bug#28427: Columns were renamed instead of moving by ALTER TABLE. +# +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); +INSERT INTO t1 VALUES (1, 2, NULL); +SELECT * FROM t1; +ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; +SELECT * FROM t1; +ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 7217d00a1bb..ab2859a5346 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -10,7 +10,13 @@ select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1; select ~5, cast(~5 as signed); explain extended select ~5, cast(~5 as signed); select cast(5 as unsigned) -6.0; -select cast(NULL as signed), cast(1/0 as signed); +select cast(NULL as signed), cast(1/0 as signed); +# +# Bug #28250: Run-Time Check Failure #3 - The variable 'value' is being used +# without being def +# +# The following line causes Run-Time Check Failure on +# binaries built with Visual C++ 2005 select cast(NULL as unsigned), cast(1/0 as unsigned); select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A"; select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME); diff --git a/mysql-test/t/func_date_add.test b/mysql-test/t/func_date_add.test index b575eeececa..fc5a5cb2823 100644 --- a/mysql-test/t/func_date_add.test +++ b/mysql-test/t/func_date_add.test @@ -77,4 +77,14 @@ SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH; SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR; SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; +# +# Bug#28450: The Item_date_add_interval in select list may fail the field +# type assertion. +# +create table t1 (a int, b varchar(10)); +insert into t1 values (1, '2001-01-01'),(2, '2002-02-02'); +select '2007-01-01' + interval a day from t1; +select b + interval a day from t1; +drop table t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 4e965d7e8c9..b24015ebb99 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -520,5 +520,35 @@ SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; SET group_concat_max_len = DEFAULT; DROP TABLE t1; +# Bug #23856:GROUP_CONCAT and ORDER BY: junk from previous rows for query on I_S +# +SET group_concat_max_len= 65535; +CREATE TABLE t1( a TEXT, b INTEGER ); +INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 ); +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +SET group_concat_max_len= 10; +SELECT GROUP_CONCAT(a ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; + +SET group_concat_max_len= 65535; +CREATE TABLE t2( a TEXT ); +INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); +INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) ); +INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); +SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2; + +CREATE TABLE t3( a TEXT, b INT ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 ); +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1; +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2; +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3; + +SET group_concat_max_len= DEFAULT; +DROP TABLE t1, t2, t3; --echo End of 5.0 tests diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 0a29b4cb325..708a618f10f 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -851,3 +851,19 @@ SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; show status like 'Handler_read%'; DROP TABLE t1,t2; + +# +# Bug 28571: outer join with false on condition over constant tables +# + +CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL); +INSERT INTO t1 VALUES (1,0), (2,1); +CREATE TABLE t2 (d int PRIMARY KEY); +INSERT INTO t2 VALUES (1), (2), (3); + +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL; +SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL; +SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL; + +DROP TABLE t1,t2; + diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index cf0895f63c4..959938dcb03 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -117,3 +117,188 @@ reap; select 1; connection con1; select RELEASE_LOCK("a"); + +# +# Bug#27563: Stored functions and triggers wasn't throwing an error when killed. +# +create table t1(f1 int); +delimiter |; +create function bug27563() returns int(11) +deterministic +begin + declare continue handler for sqlstate '70100' set @a:= 'killed'; + declare continue handler for sqlexception set @a:= 'exception'; + set @a= get_lock("lock27563", 10); + return 1; +end| +delimiter ;| +# Test stored functions +# Test INSERT +connection con1; +select get_lock("lock27563",10); +connection con2; +let $ID= `select connection_id()`; +send insert into t1 values (bug27563()); +real_sleep 2; +connection con1; +disable_query_log; +eval kill query $ID; +enable_query_log; +connection con2; +--error 1317 +reap; +select @a; +connection con1; +select * from t1; + +# Test UPDATE +insert into t1 values(0); +connection con2; +send update t1 set f1= bug27563(); +real_sleep 2; +connection con1; +disable_query_log; +eval kill query $ID; +enable_query_log; +connection con2; +--error 1317 +reap; +select @a; +connection con1; +select * from t1; + +# Test DELETE +insert into t1 values(1); +connection con2; +send delete from t1 where bug27563() is null; +real_sleep 2; +connection con1; +disable_query_log; +eval kill query $ID; +enable_query_log; +connection con2; +--error 1317 +reap; +select @a; +connection con1; +select * from t1; + +# Test SELECT +connection con2; +send select * from t1 where f1= bug27563(); +real_sleep 2; +connection con1; +disable_query_log; +eval kill query $ID; +enable_query_log; +connection con2; +--error 1317 +reap; +select @a; + +# Test PROCEDURE +connection con2; +delimiter |; +create procedure proc27563() +begin + declare continue handler for sqlstate '70100' set @a:= 'killed'; + declare continue handler for sqlexception set @a:= 'exception'; + select get_lock("lock27563",10); + select "shouldn't be selected"; +end| +delimiter ;| +send call proc27563(); +real_sleep 2; +connection con1; +disable_query_log; +eval kill query $ID; +enable_query_log; +connection con2; +--error 1317 +reap; +select @a; + +# Test TRIGGERS +connection con2; +create table t2 (f2 int); +delimiter |; +create trigger trg27563 before insert on t1 for each row +begin + declare continue handler for sqlstate '70100' set @a:= 'killed'; + declare continue handler for sqlexception set @a:= 'exception'; + set @a:= get_lock("lock27563",10); + insert into t2 values(1); +end| +delimiter ;| +send insert into t1 values(2),(3); +real_sleep 2; +connection con1; +disable_query_log; +eval kill query $ID; +enable_query_log; +connection con2; +--error 1317 +reap; +select @a; +connection con1; +select * from t1; +select * from t2; + +# Cleanup +select release_lock("lock27563"); +drop table t1, t2; +drop function bug27563; +drop procedure proc27563; + +# +# Bug#28598: mysqld crash when killing a long-running explain query. +# +--disable_query_log +connection con1; +let $ID= `select connection_id()`; +let $tab_count= 40; + +let $i= $tab_count; +while ($i) +{ + eval CREATE TABLE t$i (a$i int, KEY(a$i)); + eval INSERT INTO t$i VALUES (1),(2),(3),(4),(5),(6),(7); + dec $i ; +} +set session optimizer_search_depth=0; + +let $i=$tab_count; +while ($i) +{ + let $a= a$i; + let $t= t$i; + dec $i; + if ($i) + { + let $comma=,; + let $from=$comma$t$from; + let $where=a$i=$a $and $where; + } + if (!$i) + { + let $from=FROM $t$from; + let $where=WHERE $where; + } + let $and=AND; +} + +--enable_query_log +eval PREPARE stmt FROM 'EXPLAIN SELECT * $from $where'; +send EXECUTE stmt; +--disable_query_log + +connection con2; +real_sleep 2; +eval kill query $ID; +let $i= $tab_count; +while ($i) +{ + eval DROP TABLE t$i; + dec $i ; +} +--enable_query_log diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index a6ebfdc14c1..df4acec2021 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -81,3 +81,14 @@ drop view v1,v2,v3; --disable_metadata # End of 4.1 tests + +# +# Bug #28492: subselect returns LONG in >5.0.24a and LONGLONG in <=5.0.24a +# +--enable_metadata +select a.* from (select 2147483648 as v_large) a; +select a.* from (select 214748364 as v_small) a; +--disable_metadata + + +--echo End of 5.0 tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 8f7357d1c98..e66534f0cb9 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1129,8 +1129,26 @@ create table t3 (c1 int) engine=myisam pack_keys=default; create table t4 (c1 int) engine=myisam pack_keys=2; drop table t1, t2, t3; + +# +# Bug#28476: force index on a disabled myisam index gives error 124 +# +CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM; +INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5); +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +ALTER TABLE t1 DISABLE KEYS; +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +SELECT a FROM t1 USE INDEX (inx) WHERE a=1; +SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1; +SELECT b FROM t1 USE INDEX (uinx) WHERE b=1; +SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1; +ALTER TABLE t1 ENABLE KEYS; +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +DROP TABLE t1; + --echo End of 5.0 tests + # # Test of key_block_size # diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index a6f93e759dc..487e16bbe46 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1528,7 +1528,14 @@ drop user user2; drop database mysqldump_test_db; +--echo # +--echo # Bug #28522: buffer overrun by '\0' byte using --hex-blob. +--echo # +CREATE TABLE t1 (c1 INT, c2 LONGBLOB); +INSERT INTO t1 SET c1=11, c2=REPEAT('q',509); +--exec $MYSQL_DUMP --skip-create --compact --hex-blob test t1 +DROP TABLE t1; --echo # --echo # End of 5.0 tests diff --git a/mysql-test/t/rpl_view.test b/mysql-test/t/rpl_view.test index 3eff8f7550a..51ad319b212 100644 --- a/mysql-test/t/rpl_view.test +++ b/mysql-test/t/rpl_view.test @@ -152,4 +152,16 @@ drop view v1; sync_slave_with_master; +# +# BUG#28244 CREATE VIEW breaks replication when view exists +# +connection master; +CREATE TABLE t1(a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +--error ER_TABLE_EXISTS_ERROR +CREATE VIEW v1 AS SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; +sync_slave_with_master; + --echo End of 5.0 tests diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 5ccdfcf26b5..08a01be0fd2 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -7232,3 +7232,23 @@ select count(*) from t1 /* must be 3 */; drop table t1,t2; drop function bug27354; + +# +# Bug #28605: SHOW CREATE VIEW with views using stored_procedures no longer +# showing SP names. +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12; + +CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1; + +SHOW CREATE VIEW v1; + +DROP VIEW v1; +DROP FUNCTION metered; +DROP TABLE t1; + + +--echo End of 5.0 tests diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 14dbb72de7b..2f844c9cc21 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -571,3 +571,21 @@ SELECT (t1.id IN (SELECT t2.id FROM t2,t3 FROM t1; DROP TABLE t1,t2,t3; + +# +# Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated +# subquery +# +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(-1), (65),(66); + +CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); +INSERT INTO t2 VALUES (65),(66); + +SELECT a FROM t1 WHERE a NOT IN (65,66); +SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); +EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); + +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test index 9be1b7fa10f..200fd063b99 100644 --- a/mysql-test/t/type_enum.test +++ b/mysql-test/t/type_enum.test @@ -139,6 +139,14 @@ drop table t1; --echo End of 4.1 tests # +# Bug#28729: Field_enum wrongly reported an error while storing an empty string. +# +create table t1(f1 set('a','b'), index(f1)); +insert into t1 values(''),(''),('a'),('b'); +select * from t1 where f1=''; +drop table t1; + +# # Bug#24660 "enum" field type definition problem # create table t1(russian enum('E','F','EÿF','FÿE') NOT NULL DEFAULT'E'); diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 0a52bdaa8e6..e052ecfa82b 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1159,6 +1159,19 @@ select cast(a as DECIMAL(3,2)), count(*) UNION select 12.1234 ) t group by 1; +# +# Bug #28361 Buffer overflow in DECIMAL code on Windows +# + +create table t1 (s varchar(100)); +insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875); +drop table t1; + +# +# Bug #27984 Long Decimal Maths produces truncated results +# + +SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b; --echo End of 5.0 tests # diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 70f57fdf283..3a3e8f88f83 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -222,3 +222,18 @@ drop table t1,t2; insert into city 'blah'; SHOW COUNT(*) WARNINGS; SHOW COUNT(*) ERRORS; + +# +# Bug#28494: Grouping by Item_func_set_user_var produces incorrect result. +# +create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1)); +insert into t1 values + (1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6), + (3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6), + (3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6); +select @a:=f1, count(f1) from t1 group by 1 desc; +select @a:=f1, count(f1) from t1 group by 1 asc; +select @a:=f2, count(f2) from t1 group by 1 desc; +select @a:=f3, count(f3) from t1 group by 1 desc; +select @a:=f4, count(f4) from t1 group by 1 desc; +drop table t1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 51c862f692d..68817b0771a 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3225,6 +3225,93 @@ CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; SHOW CREATE VIEW v1; DROP VIEW v1; +# +# Bug #28716: CHECK OPTION expression is evaluated over expired record buffers +# when VIEW is updated via temporary tables +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, c INT DEFAULT 0); +INSERT INTO t1 (a) VALUES (1), (2); +INSERT INTO t2 (b) VALUES (1), (2); +CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 + WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; +SELECT * FROM v1; +UPDATE v1 SET c=1 WHERE b=1; +SELECT * FROM v1; +DROP VIEW v1; +DROP TABLE t1,t2; + +# +# Bug #28561: update on multi-table view with CHECK OPTION and +# a subquery in WHERE condition +# + +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int, c int DEFAULT 0); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); + +CREATE VIEW v1 AS + SELECT t2.c FROM t1, t2 + WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; + +UPDATE v1 SET c=1; + +DROP VIEW v1; +DROP TABLE t1,t2; + +# +# Bug #27827: CHECK OPTION ignores ON conditions when updating +# a multi-table view with CHECK OPTION. +# + +CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); +CREATE TABLE t2 (a2 INT); +CREATE TABLE t3 (a3 INT); +CREATE TABLE t4 (a4 INT); +INSERT INTO t1 (a1) VALUES (1),(2); +INSERT INTO t2 (a2) VALUES (1),(2); +INSERT INTO t3 (a3) VALUES (1),(2); +INSERT INTO t4 (a4) VALUES (1),(2); + +CREATE VIEW v1 AS + SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 + WITH CHECK OPTION; +SELECT * FROM v1; +--error 1369 +UPDATE v1 SET c=3; +PREPARE t FROM 'UPDATE v1 SET c=3'; +--error 1369 +EXECUTE t; +--error 1369 +EXECUTE t; +--error 1369 +INSERT INTO v1(a1, c) VALUES (3, 3); +UPDATE v1 SET c=1 WHERE a1=1; +SELECT * FROM v1; +SELECT * FROM t1; + +CREATE VIEW v2 AS SELECT t1.a1, t1.c + FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) + JOIN (t3 JOIN t4 ON t3.a3=t4.a4) + ON t2.a2=t3.a3 WITH CHECK OPTION; +SELECT * FROM v2; +--error 1369 +UPDATE v2 SET c=3; +PREPARE t FROM 'UPDATE v2 SET c=3'; +--error 1369 +EXECUTE t; +--error 1369 +EXECUTE t; +--error 1369 +INSERT INTO v2(a1, c) VALUES (3, 3); +UPDATE v2 SET c=2 WHERE a1=1; +SELECT * FROM v2; +SELECT * FROM t1; + +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3,t4; + --echo End of 5.0 tests. # diff --git a/sql/field.cc b/sql/field.cc index 6fd88e4e1ac..29882d1b8ed 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -7878,6 +7878,8 @@ int Field_enum::store(const char *from,uint length,CHARSET_INFO *cs) tmp=0; set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1); } + if (!table->in_use->count_cuted_fields) + err= 0; } else set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, 1); diff --git a/sql/field.h b/sql/field.h index 54a061276b6..6c77eded2d1 100644 --- a/sql/field.h +++ b/sql/field.h @@ -744,7 +744,7 @@ public: void sort_string(uchar *buff,uint length); uint32 pack_length() const { return 4; } void sql_type(String &str) const; - uint32 max_display_length() { return 11; } + uint32 max_display_length() { return MY_INT32_NUM_DECIMAL_DIGITS; } }; @@ -1173,6 +1173,11 @@ public: class Field_varstring :public Field_longstr { public: + /* + The maximum space available in a Field_varstring, in bytes. See + length_bytes. + */ + static const uint MAX_SIZE= UINT_MAX16; /* Store number of bytes used to store length (1 or 2) */ uint32 length_bytes; Field_varstring(uchar *ptr_arg, diff --git a/sql/field_conv.cc b/sql/field_conv.cc index e920fe70f26..63810c8b113 100644 --- a/sql/field_conv.cc +++ b/sql/field_conv.cc @@ -534,7 +534,21 @@ void Copy_field::set(uchar *to,Field *from) } +/* + To do: + + If 'save\ is set to true and the 'from' is a blob field, do_copy is set to + do_save_blob rather than do_conv_blob. The only differences between them + appears to be: + - do_save_blob allocates and uses an intermediate buffer before calling + Field_blob::store. Is this in order to trigger the call to + well_formed_copy_nchars, by changing the pointer copy->tmp.ptr()? + That call will take place anyway in all known cases. + + - The above causes a truncation to MAX_FIELD_WIDTH. Is this the intended + effect? Truncation is handled by well_formed_copy_nchars anyway. + */ void Copy_field::set(Field *to,Field *from,bool save) { if (to->type() == MYSQL_TYPE_NULL) diff --git a/sql/handler.h b/sql/handler.h index 952e7351a68..6a16dd96f49 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -852,7 +852,15 @@ public: ulonglong max_index_file_length; ulonglong delete_length; /* Free bytes */ ulonglong auto_increment_value; - ha_rows records; /* Estimated records in table */ + /* + The number of records in the table. + 0 - means the table has exactly 0 rows + other - if (table_flags() & HA_STATS_RECORDS_IS_EXACT) + the value is the exact number of records in the table + else + it is an estimate + */ + ha_rows records; ha_rows deleted; /* Deleted records */ ulong mean_rec_length; /* physical reclength */ time_t create_time; /* When table was created */ diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 6842febea4d..b8117f53d6b 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -819,7 +819,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, { value= item->val_int(); *is_null= item->null_value; - if (item->field_type() == MYSQL_TYPE_DATE) + /* + Item_date_add_interval may return MYSQL_TYPE_STRING as the result + field type. To detect that the DATE value has been returned we + compare it with 1000000L - any DATE value should be less than it. + */ + if (item->field_type() == MYSQL_TYPE_DATE || value < 100000000L) value*= 1000000L; } else diff --git a/sql/item_func.cc b/sql/item_func.cc index 27500b259e0..b0346cc5224 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -993,6 +993,8 @@ longlong Item_func_unsigned::val_int() my_decimal tmp, *dec= args[0]->val_decimal(&tmp); if (!(null_value= args[0]->null_value)) my_decimal2int(E_DEC_FATAL_ERROR, dec, 1, &value); + else + value= 0; return value; } else if (args[0]->cast_to_int_type() != STRING_RESULT || @@ -3797,6 +3799,23 @@ Item_func_set_user_var::fix_length_and_dec() /* + Mark field in read_map + + NOTES + This is used by filesort to register used fields in a a temporary + column read set or to register used fields in a view +*/ + +bool Item_func_set_user_var::register_field_in_read_map(uchar *arg) +{ + TABLE *table= (TABLE *) arg; + if (result_field->table == table || !table) + bitmap_set_bit(result_field->table->read_set, result_field->field_index); + return 0; +} + + +/* Set value to user variable. SYNOPSYS @@ -4032,7 +4051,8 @@ bool Item_func_set_user_var::check(bool use_result_field) { DBUG_ENTER("Item_func_set_user_var::check"); - DBUG_ASSERT(!use_result_field || result_field); + if (use_result_field && !result_field) + use_result_field= FALSE; switch (cached_result_type) { case REAL_RESULT: @@ -4176,6 +4196,40 @@ my_decimal *Item_func_set_user_var::val_decimal(my_decimal *val) } +double Item_func_set_user_var::val_result() +{ + DBUG_ASSERT(fixed == 1); + check(TRUE); + update(); // Store expression + return entry->val_real(&null_value); +} + +longlong Item_func_set_user_var::val_int_result() +{ + DBUG_ASSERT(fixed == 1); + check(TRUE); + update(); // Store expression + return entry->val_int(&null_value); +} + +String *Item_func_set_user_var::str_result(String *str) +{ + DBUG_ASSERT(fixed == 1); + check(TRUE); + update(); // Store expression + return entry->val_str(&null_value, str, decimals); +} + + +my_decimal *Item_func_set_user_var::val_decimal_result(my_decimal *val) +{ + DBUG_ASSERT(fixed == 1); + check(TRUE); + update(); // Store expression + return entry->val_decimal(&null_value, val); +} + + void Item_func_set_user_var::print(String *str) { str->append(STRING_WITH_LEN("(@")); @@ -4258,9 +4312,11 @@ void Item_func_set_user_var::make_field(Send_field *tmp_field) TRUE Error */ -int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions) +int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions, + bool can_use_result_field) { - bool use_result_field= (result_field && result_field != field); + bool use_result_field= (!can_use_result_field ? 0 : + (result_field && result_field != field)); int error; /* Update the value of the user variable */ @@ -5189,10 +5245,11 @@ Item_func_sp::func_name() const { THD *thd= current_thd; /* Calculate length to avoid reallocation of string for sure */ - uint len= ((m_name->m_explicit_name ? m_name->m_db.length : 0 + + uint len= (((m_name->m_explicit_name ? m_name->m_db.length : 0) + m_name->m_name.length)*2 + //characters*quoting 2 + // ` and ` - 1 + // . + (m_name->m_explicit_name ? + 3 : 0) + // '`', '`' and '.' for the db 1 + // end of string ALIGN_SIZE(1)); // to avoid String reallocation String qname((char *)alloc_root(thd->mem_root, len), len, @@ -5322,6 +5379,8 @@ Item_func_sp::execute() { null_value= 1; context->process_error(thd); + if (thd->killed) + thd->send_kill_message(); return TRUE; } diff --git a/sql/item_func.h b/sql/item_func.h index 60205170166..8fc68f93e12 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1236,6 +1236,10 @@ public: longlong val_int(); String *val_str(String *str); my_decimal *val_decimal(my_decimal *); + double val_result(); + longlong val_int_result(); + String *str_result(String *str); + my_decimal *val_decimal_result(my_decimal *); bool update_hash(void *ptr, uint length, enum Item_result type, CHARSET_INFO *cs, Derivation dv, bool unsigned_arg); bool send(Protocol *protocol, String *str_arg); @@ -1248,7 +1252,14 @@ public: void print(String *str); void print_as_stmt(String *str); const char *func_name() const { return "set_user_var"; } - int save_in_field(Field *field, bool no_conversions); + int save_in_field(Field *field, bool no_conversions, + bool can_use_result_field); + int save_in_field(Field *field, bool no_conversions) + { + return save_in_field(field, no_conversions, 1); + } + void save_org_in_field(Field *field) { (void)save_in_field(field, 1, 0); } + bool register_field_in_read_map(uchar *arg); }; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 9494d1dc2ee..000abb313a0 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -818,6 +818,11 @@ bool Item_in_subselect::val_bool() if (exec()) { reset(); + /* + Must mark the IN predicate as NULL so as to make sure an enclosing NOT + predicate will return FALSE. See the comments in + subselect_uniquesubquery_engine::copy_ref_key for further details. + */ null_value= 1; return 0; } @@ -1979,10 +1984,38 @@ int subselect_uniquesubquery_engine::scan_table() DESCRIPTION Copy ref key and check for null parts in it. + Depending on the nullability and conversion problems this function + recognizes and processes the following states : + 1. Partial match on top level. This means IN has a value of FALSE + regardless of the data in the subquery table. + Detected by finding a NULL in the left IN operand of a top level + expression. + We may actually skip reading the subquery, so return TRUE to skip + the table scan in subselect_uniquesubquery_engine::exec and make + the value of the IN predicate a NULL (that is equal to FALSE on + top level). + 2. No exact match when IN is nested inside another predicate. + Detected by finding a NULL in the left IN operand when IN is not + a top level predicate. + We cannot have an exact match. But we must proceed further with a + table scan to find out if it's a partial match (and IN has a value + of NULL) or no match (and IN has a value of FALSE). + So we return FALSE to continue with the scan and see if there are + any record that would constitute a partial match (as we cannot + determine that from the index). + 3. Error converting the left IN operand to the column type of the + right IN operand. This counts as no match (and IN has the value of + FALSE). We mark the subquery table cursor as having no more rows + (to ensure that the processing that follows will not find a match) + and return FALSE, so IN is not treated as returning NULL. + RETURN - FALSE - ok, index lookup key without keys copied. - TRUE - an error occured while copying the key + FALSE - The value of the IN predicate is not known. Proceed to find the + value of the IN predicate using the determined values of + null_keypart and table->status. + TRUE - IN predicate has a value of NULL. Stop the processing right there + and return NULL to the outer predicates. */ bool subselect_uniquesubquery_engine::copy_ref_key() @@ -2002,13 +2035,37 @@ bool subselect_uniquesubquery_engine::copy_ref_key() function. */ null_keypart= (*copy)->null_key; - bool top_level= ((Item_in_subselect *) item)->is_top_level_item(); - if (null_keypart && !top_level) - break; - if ((tab->ref.key_err) & 1 || (null_keypart && top_level)) + if (null_keypart) + { + bool top_level= ((Item_in_subselect *) item)->is_top_level_item(); + if (top_level) + { + /* Partial match on top level */ + DBUG_RETURN(1); + } + else + { + /* No exact match when IN is nested inside another predicate */ + break; + } + } + + /* + Check if the error is equal to STORE_KEY_FATAL. This is not expressed + using the store_key::store_key_result enum because ref.key_err is a + boolean and we want to detect both TRUE and STORE_KEY_FATAL from the + space of the union of the values of [TRUE, FALSE] and + store_key::store_key_result. + TODO: fix the variable an return types. + */ + if (tab->ref.key_err & 1) { + /* + Error converting the left IN operand to the column type of the right + IN operand. + */ tab->table->status= STATUS_NOT_FOUND; - DBUG_RETURN(1); + break; } } DBUG_RETURN(0); @@ -2051,10 +2108,20 @@ int subselect_uniquesubquery_engine::exec() int error; TABLE *table= tab->table; empty_result_set= TRUE; + table->status= 0; /* TODO: change to use of 'full_scan' here? */ if (copy_ref_key()) DBUG_RETURN(1); + if (table->status) + { + /* + We know that there will be no rows even if we scan. + Can be set in copy_ref_key. + */ + ((Item_in_subselect *) item)->value= 0; + DBUG_RETURN(0); + } if (null_keypart) DBUG_RETURN(scan_table()); diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 69148d81b9a..08ce47573e7 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -431,7 +431,7 @@ Field *Item_sum::create_tmp_field(bool group, TABLE *table, break; case STRING_RESULT: if (max_length/collation.collation->mbmaxlen <= 255 || - convert_blob_length >=UINT_MAX16 || + convert_blob_length > Field_varstring::MAX_SIZE || !convert_blob_length) return make_string_field(table); field= new Field_varstring(convert_blob_length, maybe_null, @@ -3290,14 +3290,20 @@ bool Item_func_group_concat::setup(THD *thd) tmp_table_param->force_copy_fields= force_copy_fields; DBUG_ASSERT(table == 0); /* + Currently we have to force conversion of BLOB values to VARCHAR's + if we are to store them in TREE objects used for ORDER BY and + DISTINCT. This leads to truncation if the BLOB's size exceeds + Field_varstring::MAX_SIZE. + */ + if (arg_count_order > 0 || distinct) + set_if_smaller(tmp_table_param->convert_blob_length, + Field_varstring::MAX_SIZE); + /* We have to create a temporary table to get descriptions of fields (types, sizes and so on). Note that in the table, we first have the ORDER BY fields, then the field list. - - We need to set set_sum_field in true for storing value of blob in buffer - of a record instead of a pointer of one. */ if (!(table= create_tmp_table(thd, tmp_table_param, all_fields, (ORDER*) 0, 0, TRUE, diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 3bc63eaf5f9..6f38a1acc67 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2058,27 +2058,6 @@ bool Item_date_add_interval::get_date(MYSQL_TIME *ltime, uint fuzzy_date) if ((null_value= date_add_interval(ltime, int_type, interval))) return 1; - - /* Adjust cached_field_type according to the detected type. */ - if (cached_field_type == MYSQL_TYPE_STRING) - { - switch (ltime->time_type) - { - case MYSQL_TIMESTAMP_DATE: - cached_field_type= MYSQL_TYPE_DATE; - break; - case MYSQL_TIMESTAMP_DATETIME: - cached_field_type= MYSQL_TYPE_DATETIME; - break; - case MYSQL_TIMESTAMP_TIME: - cached_field_type= MYSQL_TYPE_TIME; - break; - default: - /* Shouldn't get here. */ - DBUG_ASSERT(0); - break; - } - } return 0; } diff --git a/sql/my_decimal.h b/sql/my_decimal.h index d6cefb3e080..f9ba99a4509 100644 --- a/sql/my_decimal.h +++ b/sql/my_decimal.h @@ -36,13 +36,17 @@ C_MODE_END /* maximum length of buffer in our big digits (uint32) */ #define DECIMAL_BUFF_LENGTH 9 + +/* the number of digits that my_decimal can possibly contain */ +#define DECIMAL_MAX_POSSIBLE_PRECISION (DECIMAL_BUFF_LENGTH * 9) + /* maximum guaranteed precision of number in decimal digits (number of our digits * number of decimal digits in one our big digit - number of decimal - digits in one our big digit decreased on 1 (because we always put decimal + digits in one our big digit decreased by 1 (because we always put decimal point on the border of our big digits)) */ -#define DECIMAL_MAX_PRECISION ((DECIMAL_BUFF_LENGTH * 9) - 8*2) +#define DECIMAL_MAX_PRECISION (DECIMAL_MAX_POSSIBLE_PRECISION - 8*2) #define DECIMAL_MAX_SCALE 30 #define DECIMAL_NOT_SPECIFIED 31 @@ -50,7 +54,7 @@ C_MODE_END maximum length of string representation (number of maximum decimal digits + 1 position for sign + 1 position for decimal point) */ -#define DECIMAL_MAX_STR_LENGTH (DECIMAL_MAX_PRECISION + 2) +#define DECIMAL_MAX_STR_LENGTH (DECIMAL_MAX_POSSIBLE_PRECISION + 2) /* maximum size of packet length */ diff --git a/sql/mysqld.cc b/sql/mysqld.cc index be704625e92..8442d67515b 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3813,6 +3813,11 @@ we force server id to 2, but this MySQL server will not act as a slave."); freopen(log_error_file,"a+",stderr); FreeConsole(); // Remove window } + else + { + /* Don't show error dialog box when on foreground: it stops the server */ + SetErrorMode(SEM_NOOPENFILEERRORBOX | SEM_FAILCRITICALERRORS); + } #endif /* diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 3d5f2f0f50d..bbf281caee0 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1342,6 +1342,9 @@ err_with_cleanup: free_root(&call_mem_root, MYF(0)); thd->spcont= octx; + if (thd->killed) + thd->send_kill_message(); + DBUG_RETURN(err_status); } diff --git a/sql/sql_base.cc b/sql/sql_base.cc index b21dc9d47f8..26d23d1ea57 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6811,7 +6811,7 @@ fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors) table= (*ptr)->table; table->auto_increment_field_not_null= FALSE; } - while ((field = *ptr++)) + while ((field = *ptr++) && !thd->net.report_error) { value=v++; table= field->table; diff --git a/sql/sql_class.h b/sql/sql_class.h index 7729b597098..d9dc4117319 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2308,6 +2308,11 @@ class multi_update :public select_result_interceptor List <Item> *fields, *values; List <Item> **fields_for_table, **values_for_table; uint table_count; + /* + List of tables referenced in the CHECK OPTION condition of + the updated view excluding the updated table. + */ + List <TABLE> unupdated_check_opt_tables; Copy_field *copy_field; enum enum_duplicates handle_duplicates; bool do_update, trans_safe; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c6ef16d46a3..cb988049b4c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -49,15 +49,15 @@ static int sort_keyuse(KEYUSE *a,KEYUSE *b); static void set_position(JOIN *join,uint index,JOIN_TAB *table,KEYUSE *key); static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, table_map used_tables); -static void choose_plan(JOIN *join,table_map join_tables); +static bool choose_plan(JOIN *join,table_map join_tables); static void best_access_path(JOIN *join, JOIN_TAB *s, THD *thd, table_map remaining_tables, uint idx, double record_count, double read_time); static void optimize_straight_join(JOIN *join, table_map join_tables); -static void greedy_search(JOIN *join, table_map remaining_tables, +static bool greedy_search(JOIN *join, table_map remaining_tables, uint depth, uint prune_level); -static void best_extension_by_limited_search(JOIN *join, +static bool best_extension_by_limited_search(JOIN *join, table_map remaining_tables, uint idx, double record_count, double read_time, uint depth, @@ -69,7 +69,7 @@ static int join_tab_cmp_straight(const void* ptr1, const void* ptr2); TODO: 'find_best' is here only temporarily until 'greedy_search' is tested and approved. */ -static void find_best(JOIN *join,table_map rest_tables,uint index, +static bool find_best(JOIN *join,table_map rest_tables,uint index, double record_count,double read_time); static uint cache_record_length(JOIN *join,uint index); static double prev_record_reads(JOIN *join, uint idx, table_map found_ref); @@ -2755,7 +2755,8 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds, if (join->const_tables != join->tables) { optimize_keyuse(join, keyuse_array); - choose_plan(join, all_table_map & ~join->const_table_map); + if (choose_plan(join, all_table_map & ~join->const_table_map)) + DBUG_RETURN(TRUE); } else { @@ -4404,11 +4405,12 @@ best_access_path(JOIN *join, the array 'join->best_positions', and the cost of the plan in 'join->best_read'. - RETURN - None + RETURN VALUES + FALSE ok + TRUE Fatal error */ -static void +static bool choose_plan(JOIN *join, table_map join_tables) { uint search_depth= join->thd->variables.optimizer_search_depth; @@ -4441,14 +4443,16 @@ choose_plan(JOIN *join, table_map join_tables) the greedy version. Will be removed when greedy_search is approved. */ join->best_read= DBL_MAX; - find_best(join, join_tables, join->const_tables, 1.0, 0.0); + if (find_best(join, join_tables, join->const_tables, 1.0, 0.0)) + DBUG_RETURN(TRUE); } else { if (search_depth == 0) /* Automatically determine a reasonable value for 'search_depth' */ search_depth= determine_search_depth(join); - greedy_search(join, join_tables, search_depth, prune_level); + if (greedy_search(join, join_tables, search_depth, prune_level)) + DBUG_RETURN(TRUE); } } @@ -4456,7 +4460,7 @@ choose_plan(JOIN *join, table_map join_tables) Store the cost of this query into a user variable */ join->thd->status_var.last_query_cost= join->best_read; - DBUG_VOID_RETURN; + DBUG_RETURN(FALSE); } @@ -4684,11 +4688,12 @@ optimize_straight_join(JOIN *join, table_map join_tables) In the future, 'greedy_search' might be extended to support other implementations of 'best_extension', e.g. some simpler quadratic procedure. - RETURN - None + RETURN VALUES + FALSE ok + TRUE Fatal error */ -static void +static bool greedy_search(JOIN *join, table_map remaining_tables, uint search_depth, @@ -4710,8 +4715,9 @@ greedy_search(JOIN *join, do { /* Find the extension of the current QEP with the lowest cost */ join->best_read= DBL_MAX; - best_extension_by_limited_search(join, remaining_tables, idx, record_count, - read_time, search_depth, prune_level); + if (best_extension_by_limited_search(join, remaining_tables, idx, record_count, + read_time, search_depth, prune_level)) + DBUG_RETURN(TRUE); if (size_remain <= search_depth) { @@ -4722,7 +4728,7 @@ greedy_search(JOIN *join, DBUG_EXECUTE("opt", print_plan(join, join->tables, record_count, read_time, read_time, "optimal");); - DBUG_VOID_RETURN; + DBUG_RETURN(FALSE); } /* select the first table in the optimal extension as most promising */ @@ -4867,11 +4873,12 @@ greedy_search(JOIN *join, The parameter 'search_depth' provides control over the recursion depth, and thus the size of the resulting optimal plan. - RETURN - None + RETURN VALUES + FALSE ok + TRUE Fatal error */ -static void +static bool best_extension_by_limited_search(JOIN *join, table_map remaining_tables, uint idx, @@ -4880,11 +4887,12 @@ best_extension_by_limited_search(JOIN *join, uint search_depth, uint prune_level) { + DBUG_ENTER("best_extension_by_limited_search"); + THD *thd= join->thd; if (thd->killed) // Abort - return; + DBUG_RETURN(TRUE); - DBUG_ENTER("best_extension_by_limited_search"); DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx, "SOFAR:");); @@ -4966,15 +4974,14 @@ best_extension_by_limited_search(JOIN *join, if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) ) { /* Recursively expand the current partial plan */ swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); - best_extension_by_limited_search(join, - remaining_tables & ~real_table_bit, - idx + 1, - current_record_count, - current_read_time, - search_depth - 1, - prune_level); - if (thd->killed) - DBUG_VOID_RETURN; + if (best_extension_by_limited_search(join, + remaining_tables & ~real_table_bit, + idx + 1, + current_record_count, + current_read_time, + search_depth - 1, + prune_level)) + DBUG_RETURN(TRUE); swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); } else @@ -5003,19 +5010,26 @@ best_extension_by_limited_search(JOIN *join, restore_prev_nj_state(s); } } - DBUG_VOID_RETURN; + DBUG_RETURN(FALSE); } /* TODO: this function is here only temporarily until 'greedy_search' is tested and accepted. + + RETURN VALUES + FALSE ok + TRUE Fatal error */ -static void +static bool find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, double read_time) { + DBUG_ENTER("find_best"); THD *thd= join->thd; + if (thd->killed) + DBUG_RETURN(TRUE); if (!rest_tables) { DBUG_PRINT("best",("read_time: %g record_count: %g",read_time, @@ -5032,10 +5046,10 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, sizeof(POSITION)*idx); join->best_read= read_time - 0.001; } - return; + DBUG_RETURN(FALSE); } if (read_time+record_count/(double) TIME_FOR_COMPARE >= join->best_read) - return; /* Found better before */ + DBUG_RETURN(FALSE); /* Found better before */ JOIN_TAB *s; double best_record_count=DBL_MAX,best_read_time=DBL_MAX; @@ -5068,10 +5082,9 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, best_read_time=current_read_time; } swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); - find_best(join,rest_tables & ~real_table_bit,idx+1, - current_record_count,current_read_time); - if (thd->killed) - return; + if (find_best(join,rest_tables & ~real_table_bit,idx+1, + current_record_count,current_read_time)) + DBUG_RETURN(TRUE); swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); } restore_prev_nj_state(s); @@ -5079,6 +5092,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, break; // Don't test all combinations } } + DBUG_RETURN(FALSE); } @@ -6099,13 +6113,39 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } /* - Push down all predicates from on expressions. - Each of these predicated are guarded by a variable + Push down conditions from all on expressions. + Each of these conditions are guarded by a variable that turns if off just before null complemented row for - outer joins is formed. Thus, the predicates from an + outer joins is formed. Thus, the condition from an 'on expression' are guaranteed not to be checked for the null complemented row. */ + + /* First push down constant conditions from on expressions */ + for (JOIN_TAB *join_tab= join->join_tab+join->const_tables; + join_tab < join->join_tab+join->tables ; join_tab++) + { + if (*join_tab->on_expr_ref) + { + JOIN_TAB *cond_tab= join_tab->first_inner; + COND *tmp= make_cond_for_table(*join_tab->on_expr_ref, + join->const_table_map, + (table_map) 0); + if (!tmp) + continue; + tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl); + if (!tmp) + DBUG_RETURN(1); + tmp->quick_fix_field(); + cond_tab->select_cond= !cond_tab->select_cond ? tmp : + new Item_cond_and(cond_tab->select_cond,tmp); + if (!cond_tab->select_cond) + DBUG_RETURN(1); + cond_tab->select_cond->quick_fix_field(); + } + } + + /* Push down non-constant conditions from on expressions */ JOIN_TAB *last_tab= tab; while (first_inner_tab && first_inner_tab->last_inner == last_tab) { @@ -6553,7 +6593,6 @@ void JOIN::cleanup(bool full) for (tab= join_tab, end= tab+tables; tab != end; tab++) tab->cleanup(); table= 0; - tables= 0; } else { @@ -8992,7 +9031,7 @@ Field *create_tmp_field_from_field(THD *thd, Field *org_field, Make sure that the blob fits into a Field_varstring which has 2-byte lenght. */ - if (convert_blob_length && convert_blob_length < UINT_MAX16 && + if (convert_blob_length && convert_blob_length <= Field_varstring::MAX_SIZE && (org_field->flags & BLOB_FLAG)) new_field= new Field_varstring(convert_blob_length, org_field->maybe_null(), @@ -9060,8 +9099,13 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, item->name, item->decimals, TRUE); break; case INT_RESULT: - /* Select an integer type with the minimal fit precision */ - if (item->max_length > MY_INT32_NUM_DECIMAL_DIGITS) + /* + Select an integer type with the minimal fit precision. + MY_INT32_NUM_DECIMAL_DIGITS is sign inclusive, don't consider the sign. + Values with MY_INT32_NUM_DECIMAL_DIGITS digits may or may not fit into + Field_long : make them Field_longlong. + */ + if (item->max_length >= (MY_INT32_NUM_DECIMAL_DIGITS - 1)) new_field=new Field_longlong(item->max_length, maybe_null, item->name, item->unsigned_flag); else @@ -9085,7 +9129,8 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, 2-byte lenght. */ else if (item->max_length/item->collation.collation->mbmaxlen > 255 && - convert_blob_length < UINT_MAX16 && convert_blob_length) + convert_blob_length <= Field_varstring::MAX_SIZE && + convert_blob_length) new_field= new Field_varstring(convert_blob_length, maybe_null, item->name, table->s, item->collation.collation); @@ -15531,8 +15576,8 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, break; } } - if (tab->next_select == sub_select_cache) - extra.append(STRING_WITH_LEN("; Using join cache")); + if (i > 0 && tab[-1].next_select == sub_select_cache) + extra.append(STRING_WITH_LEN("; Using join buffer")); /* Skip initial "; "*/ const char *str= extra.ptr(); diff --git a/sql/sql_table.cc b/sql/sql_table.cc index bb0f7649272..3d8c0875452 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -37,7 +37,8 @@ static int copy_data_between_tables(TABLE *from,TABLE *to, List<create_field> &create, bool ignore, uint order_num, ORDER *order, ha_rows *copied,ha_rows *deleted, - enum enum_enable_or_disable keys_onoff); + enum enum_enable_or_disable keys_onoff, + bool error_if_not_empty); static bool prepare_blob_field(THD *thd, create_field *sql_field); static bool check_engine(THD *, const char *, HA_CREATE_INFO *); @@ -6069,14 +6070,18 @@ view_err: need_copy_table= ALTER_TABLE_DATA_CHANGED; else { + enum_compare_tables_result need_copy_table_res; /* Check how much the tables differ. */ if (compare_tables(table, alter_info, create_info, order_num, - &need_copy_table, + &need_copy_table_res, &key_info_buffer, &index_drop_buffer, &index_drop_count, &index_add_buffer, &index_add_count)) goto err; + + if (need_copy_table == ALTER_TABLE_METADATA_ONLY) + need_copy_table= need_copy_table_res; } /* @@ -6311,7 +6316,8 @@ view_err: error= copy_data_between_tables(table, new_table, alter_info->create_list, ignore, order_num, order, &copied, &deleted, - alter_info->keys_onoff); + alter_info->keys_onoff, + error_if_not_empty); } else { @@ -6663,6 +6669,38 @@ err1: VOID(quick_rm_table(new_db_type, new_db, tmp_name, FN_IS_TMP)); err: + /* + No default value was provided for a DATE/DATETIME field, the + current sql_mode doesn't allow the '0000-00-00' value and + the table to be altered isn't empty. + Report error here. + */ + if (error_if_not_empty && thd->row_count) + { + const char *f_val= 0; + enum enum_mysql_timestamp_type t_type= MYSQL_TIMESTAMP_DATE; + switch (new_datetime_field->sql_type) + { + case MYSQL_TYPE_DATE: + case MYSQL_TYPE_NEWDATE: + f_val= "0000-00-00"; + t_type= MYSQL_TIMESTAMP_DATE; + break; + case MYSQL_TYPE_DATETIME: + f_val= "0000-00-00 00:00:00"; + t_type= MYSQL_TIMESTAMP_DATETIME; + break; + default: + /* Shouldn't get here. */ + DBUG_ASSERT(0); + } + bool save_abort_on_warning= thd->abort_on_warning; + thd->abort_on_warning= TRUE; + make_truncated_value_warning(thd, MYSQL_ERROR::WARN_LEVEL_ERROR, + f_val, strlength(f_val), t_type, + new_datetime_field->field_name); + thd->abort_on_warning= save_abort_on_warning; + } if (name_lock) { pthread_mutex_lock(&LOCK_open); @@ -6692,7 +6730,8 @@ copy_data_between_tables(TABLE *from,TABLE *to, uint order_num, ORDER *order, ha_rows *copied, ha_rows *deleted, - enum enum_enable_or_disable keys_onoff) + enum enum_enable_or_disable keys_onoff, + bool error_if_not_empty) { int error; Copy_field *copy,*copy_end; @@ -6804,6 +6843,12 @@ copy_data_between_tables(TABLE *from,TABLE *to, break; } thd->row_count++; + /* Return error if source table isn't empty. */ + if (error_if_not_empty) + { + error= 1; + break; + } if (to->next_number_field) { if (auto_increment_field_copied) diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 66f69ae0421..d9bf6b4d918 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1124,6 +1124,7 @@ int multi_update::prepare(List<Item> ¬_used_values, List_iterator_fast<Item> field_it(*fields); List_iterator_fast<Item> value_it(*values); uint i, max_fields; + uint leaf_table_count= 0; DBUG_ENTER("multi_update::prepare"); thd->count_cuted_fields= CHECK_FIELD_WARN; @@ -1157,6 +1158,7 @@ int multi_update::prepare(List<Item> ¬_used_values, { /* TODO: add support of view of join support */ TABLE *table=table_ref->table; + leaf_table_count++; if (tables_to_update & table->map) { TABLE_LIST *tl= (TABLE_LIST*) thd->memdup((char*) table_ref, @@ -1218,7 +1220,7 @@ int multi_update::prepare(List<Item> ¬_used_values, /* Allocate copy fields */ max_fields=0; for (i=0 ; i < table_count ; i++) - set_if_bigger(max_fields, fields_for_table[i]->elements); + set_if_bigger(max_fields, fields_for_table[i]->elements + leaf_table_count); copy_field= new Copy_field[max_fields]; DBUG_RETURN(thd->is_fatal_error != 0); } @@ -1309,13 +1311,22 @@ multi_update::initialize_tables(JOIN *join) trans_safe= transactional_tables= main_table->file->has_transactions(); table_to_update= 0; + /* Any update has at least one pair (field, value) */ + DBUG_ASSERT(fields->elements); + /* + Only one table may be modified by UPDATE of an updatable view. + For an updatable view first_table_for_update indicates this + table. + For a regular multi-update it refers to some updated table. + */ + TABLE *first_table_for_update= ((Item_field *) fields->head())->field->table; + /* Create a temporary table for keys to all tables, except main table */ for (table_ref= update_tables; table_ref; table_ref= table_ref->next_local) { TABLE *table=table_ref->table; uint cnt= table_ref->shared; - Item_field *ifield; - List<Item> temp_fields= *fields_for_table[cnt]; + List<Item> temp_fields; ORDER group; TMP_TABLE_PARAM *tmp_param; @@ -1332,27 +1343,56 @@ multi_update::initialize_tables(JOIN *join) } table->prepare_for_position(); + if (table == first_table_for_update && table_ref->check_option) + { + table_map unupdated_tables= table_ref->check_option->used_tables() & + ~first_table_for_update->map; + for (TABLE_LIST *tbl_ref =leaves; + unupdated_tables && tbl_ref; + tbl_ref= tbl_ref->next_leaf) + { + if (unupdated_tables & tbl_ref->table->map) + unupdated_tables&= ~tbl_ref->table->map; + else + continue; + if (unupdated_check_opt_tables.push_back(tbl_ref->table)) + DBUG_RETURN(1); + } + } + tmp_param= tmp_table_param+cnt; + /* Create a temporary table to store all fields that are changed for this table. The first field in the temporary table is a pointer to the - original row so that we can find and update it + original row so that we can find and update it. For the updatable + VIEW a few following fields are rowids of tables used in the CHECK + OPTION condition. */ - /* ok to be on stack as this is not referenced outside of this func */ - Field_string offset(table->file->ref_length, 0, "offset", - &my_charset_bin); - offset.init(table); - /* - The field will be converted to varstring when creating tmp table if - table to be updated was created by mysql 4.1. Deny this. - */ - offset.can_alter_field_type= 0; - if (!(ifield= new Item_field(((Field *) &offset)))) - DBUG_RETURN(1); - ifield->maybe_null= 0; - if (temp_fields.push_front(ifield)) - DBUG_RETURN(1); + List_iterator_fast<TABLE> tbl_it(unupdated_check_opt_tables); + TABLE *tbl= table; + do + { + Field_string *field= new Field_string(tbl->file->ref_length, 0, + tbl->alias, &my_charset_bin); + if (!field) + DBUG_RETURN(1); + field->init(tbl); + /* + The field will be converted to varstring when creating tmp table if + table to be updated was created by mysql 4.1. Deny this. + */ + field->can_alter_field_type= 0; + Item_field *ifield= new Item_field((Field *) field); + if (!ifield) + DBUG_RETURN(1); + ifield->maybe_null= 0; + if (temp_fields.push_back(ifield)) + DBUG_RETURN(1); + } while ((tbl= tbl_it++)); + + temp_fields.concat(fields_for_table[cnt]); /* Make an unique key over the first field to avoid duplicated updates */ bzero((char*) &group, sizeof(group)); @@ -1504,11 +1544,26 @@ bool multi_update::send_data(List<Item> ¬_used_values) { int error; TABLE *tmp_table= tmp_tables[offset]; - table->file->position(table->record[0]); - fill_record(thd, tmp_table->field+1, *values_for_table[offset], 1); - /* Store pointer to row */ - memcpy((char*) tmp_table->field[0]->ptr, - (char*) table->file->ref, table->file->ref_length); + /* + For updatable VIEW store rowid of the updated table and + rowids of tables used in the CHECK OPTION condition. + */ + uint field_num= 0; + List_iterator_fast<TABLE> tbl_it(unupdated_check_opt_tables); + TABLE *tbl= table; + do + { + tbl->file->position(tbl->record[0]); + memcpy((char*) tmp_table->field[field_num]->ptr, + (char*) tbl->file->ref, tbl->file->ref_length); + field_num++; + } while ((tbl= tbl_it++)); + + /* Store regular updated fields in the row. */ + fill_record(thd, + tmp_table->field + 1 + unupdated_check_opt_tables.elements, + *values_for_table[offset], 1); + /* Write row, ignoring duplicated updates to a row */ error= tmp_table->file->ha_write_row(tmp_table->record[0]); if (error != HA_ERR_FOUND_DUPP_KEY && error != HA_ERR_FOUND_DUPP_UNIQUE) @@ -1558,9 +1613,10 @@ void multi_update::send_error(uint errcode,const char *err) int multi_update::do_updates(bool from_send_error) { TABLE_LIST *cur_table; - int local_error; + int local_error= 0; ha_rows org_updated; TABLE *table, *tmp_table; + List_iterator_fast<TABLE> check_opt_it(unupdated_check_opt_tables); DBUG_ENTER("do_updates"); do_update= 0; // Don't retry this function @@ -1568,8 +1624,8 @@ int multi_update::do_updates(bool from_send_error) DBUG_RETURN(0); for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local) { - uchar *ref_pos; bool can_compare_record; + uint offset= cur_table->shared; table = cur_table->table; if (table == table_to_update) @@ -1580,11 +1636,20 @@ int multi_update::do_updates(bool from_send_error) (void) table->file->ha_rnd_init(0); table->file->extra(HA_EXTRA_NO_CACHE); + check_opt_it.rewind(); + while(TABLE *tbl= check_opt_it++) + { + if (tbl->file->ha_rnd_init(1)) + goto err; + tbl->file->extra(HA_EXTRA_CACHE); + } + /* Setup copy functions to copy fields from temporary table */ - List_iterator_fast<Item> field_it(*fields_for_table[cur_table->shared]); - Field **field= tmp_table->field+1; // Skip row pointer + List_iterator_fast<Item> field_it(*fields_for_table[offset]); + Field **field= tmp_table->field + + 1 + unupdated_check_opt_tables.elements; // Skip row pointers Copy_field *copy_field_ptr= copy_field, *copy_field_end; for ( ; *field ; field++) { @@ -1601,7 +1666,6 @@ int multi_update::do_updates(bool from_send_error) bitmap_is_subset(table->write_set, table->read_set)); - ref_pos= tmp_table->field[0]->ptr; for (;;) { if (thd->killed && trans_safe) @@ -1614,8 +1678,20 @@ int multi_update::do_updates(bool from_send_error) continue; // May happen on dup key goto err; } - if ((local_error= table->file->rnd_pos(table->record[0], ref_pos))) - goto err; + + /* call rnd_pos() using rowids from temporary table */ + check_opt_it.rewind(); + TABLE *tbl= table; + uint field_num= 0; + do + { + if((local_error= + tbl->file->rnd_pos(tbl->record[0], + (uchar *) tmp_table->field[field_num]->ptr))) + goto err; + field_num++; + } while((tbl= check_opt_it++)); + table->status|= STATUS_UPDATED; store_record(table,record[1]); @@ -1666,6 +1742,10 @@ int multi_update::do_updates(bool from_send_error) } (void) table->file->ha_rnd_end(); (void) tmp_table->file->ha_rnd_end(); + check_opt_it.rewind(); + while (TABLE *tbl= check_opt_it++) + tbl->file->ha_rnd_end(); + } DBUG_RETURN(0); @@ -1679,6 +1759,9 @@ err: err2: (void) table->file->ha_rnd_end(); (void) tmp_table->file->ha_rnd_end(); + check_opt_it.rewind(); + while (TABLE *tbl= check_opt_it++) + tbl->file->ha_rnd_end(); if (updated != org_updated) { diff --git a/sql/sql_view.cc b/sql/sql_view.cc index c86083e52fc..8b0092feac8 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -692,6 +692,7 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, char dir_buff[FN_REFLEN], path_buff[FN_REFLEN]; const char *endp; LEX_STRING dir, file, path; + int error= 0; DBUG_ENTER("mysql_register_view"); /* print query */ @@ -702,9 +703,56 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, lex->unit.print(&str); thd->variables.sql_mode|= sql_mode; } - str.append('\0'); DBUG_PRINT("info", ("View: %s", str.ptr())); + /* fill structure */ + view->query.str= str.c_ptr_safe(); + view->query.length= str.length(); + view->source.str= thd->query + thd->lex->create_view_select_start; + endp= view->source.str; + endp= skip_rear_comments(endp, thd->query + thd->query_length); + view->source.length= endp - view->source.str; + view->file_version= 1; + view->calc_md5(md5); + view->md5.str= md5; + view->md5.length= 32; + can_be_merged= lex->can_be_merged(); + if (lex->create_view_algorithm == VIEW_ALGORITHM_MERGE && + !lex->can_be_merged()) + { + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_VIEW_MERGE, + ER(ER_WARN_VIEW_MERGE)); + lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; + } + view->algorithm= lex->create_view_algorithm; + view->definer.user= lex->definer->user; + view->definer.host= lex->definer->host; + view->view_suid= lex->create_view_suid; + view->with_check= lex->create_view_check; + if ((view->updatable_view= (can_be_merged && + view->algorithm != VIEW_ALGORITHM_TMPTABLE))) + { + /* TODO: change here when we will support UNIONs */ + for (TABLE_LIST *tbl= (TABLE_LIST *)lex->select_lex.table_list.first; + tbl; + tbl= tbl->next_local) + { + if ((tbl->view && !tbl->updatable_view) || tbl->schema_table) + { + view->updatable_view= 0; + break; + } + for (TABLE_LIST *up= tbl; up; up= up->embedding) + { + if (up->outer_join) + { + view->updatable_view= 0; + goto loop_out; + } + } + } + } +loop_out: /* print file name */ dir.length= build_table_filename(dir_buff, sizeof(dir_buff), view->db, "", "", 0); @@ -736,16 +784,21 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, if (mode == VIEW_CREATE_NEW) { my_error(ER_TABLE_EXISTS_ERROR, MYF(0), view->alias); - DBUG_RETURN(-1); + error= -1; + goto err; } if (!(parser= sql_parse_prepare(&path, thd->mem_root, 0))) - DBUG_RETURN(1); + { + error= 1; + goto err; + } if (!parser->ok() || !is_equal(&view_type, parser->type())) { my_error(ER_WRONG_OBJECT, MYF(0), view->db, view->table_name, "VIEW"); - DBUG_RETURN(-1); + error= -1; + goto err; } /* @@ -758,7 +811,8 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, view_parameters + revision_number_position, 1, &file_parser_dummy_hook)) { - DBUG_RETURN(thd->net.report_error? -1 : 0); + error= thd->net.report_error? -1 : 0; + goto err; } } else @@ -766,58 +820,11 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, if (mode == VIEW_ALTER) { my_error(ER_NO_SUCH_TABLE, MYF(0), view->db, view->alias); - DBUG_RETURN(-1); - } - } - } - /* fill structure */ - view->query.str= (char*)str.ptr(); - view->query.length= str.length()-1; // we do not need last \0 - view->source.str= thd->query + thd->lex->create_view_select_start; - endp= view->source.str; - endp= skip_rear_comments(thd->charset(), endp, thd->query + thd->query_length); - view->source.length= endp - view->source.str; - view->file_version= 1; - view->calc_md5(md5); - view->md5.str= md5; - view->md5.length= 32; - can_be_merged= lex->can_be_merged(); - if (lex->create_view_algorithm == VIEW_ALGORITHM_MERGE && - !lex->can_be_merged()) - { - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_VIEW_MERGE, - ER(ER_WARN_VIEW_MERGE)); - lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; - } - view->algorithm= lex->create_view_algorithm; - view->definer.user= lex->definer->user; - view->definer.host= lex->definer->host; - view->view_suid= lex->create_view_suid; - view->with_check= lex->create_view_check; - if ((view->updatable_view= (can_be_merged && - view->algorithm != VIEW_ALGORITHM_TMPTABLE))) - { - /* TODO: change here when we will support UNIONs */ - for (TABLE_LIST *tbl= (TABLE_LIST *)lex->select_lex.table_list.first; - tbl; - tbl= tbl->next_local) - { - if ((tbl->view && !tbl->updatable_view) || tbl->schema_table) - { - view->updatable_view= 0; - break; - } - for (TABLE_LIST *up= tbl; up; up= up->embedding) - { - if (up->outer_join) - { - view->updatable_view= 0; - goto loop_out; - } + error= -1; + goto err; } } } -loop_out: /* Check that table of main select do not used in subqueries. @@ -842,15 +849,23 @@ loop_out: !view->updatable_view) { my_error(ER_VIEW_NONUPD_CHECK, MYF(0), view->db, view->table_name); - DBUG_RETURN(-1); + error= -1; + goto err; } if (sql_create_definition_file(&dir, &file, view_file_type, (uchar*)view, view_parameters, num_view_backups)) { - DBUG_RETURN(thd->net.report_error? -1 : 1); + error= thd->net.report_error? -1 : 1; + goto err; } DBUG_RETURN(0); +err: + view->query.str= NULL; + view->query.length= 0; + view->md5.str= NULL; + view->md5.length= 0; + DBUG_RETURN(error); } diff --git a/sql/table.cc b/sql/table.cc index a70b5bd4bef..745f3a2a34e 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -2914,6 +2914,47 @@ bool st_table_list::prep_where(THD *thd, Item **conds, /* + Merge ON expressions for a view + + SYNOPSIS + merge_on_conds() + thd thread handle + table table for the VIEW + is_cascaded TRUE <=> merge ON expressions from underlying views + + DESCRIPTION + This function returns the result of ANDing the ON expressions + of the given view and all underlying views. The ON expressions + of the underlying views are added only if is_cascaded is TRUE. + + RETURN + Pointer to the built expression if there is any. + Otherwise and in the case of a failure NULL is returned. +*/ + +static Item * +merge_on_conds(THD *thd, TABLE_LIST *table, bool is_cascaded) +{ + DBUG_ENTER("merge_on_conds"); + + Item *cond= NULL; + DBUG_PRINT("info", ("alias: %s", table->alias)); + if (table->on_expr) + cond= table->on_expr->copy_andor_structure(thd); + if (!table->nested_join) + DBUG_RETURN(cond); + List_iterator<TABLE_LIST> li(table->nested_join->join_list); + while (TABLE_LIST *tbl= li++) + { + if (tbl->view && !is_cascaded) + continue; + cond= and_conds(cond, merge_on_conds(thd, tbl, is_cascaded)); + } + DBUG_RETURN(cond); +} + + +/* Prepare check option expression of table SYNOPSIS @@ -2929,8 +2970,8 @@ bool st_table_list::prep_where(THD *thd, Item **conds, VIEW_CHECK_LOCAL option. NOTE - This method build check options for every call - (usual execution or every SP/PS call) + This method builds check option condition to use it later on + every call (usual execution or every SP/PS call). This method have to be called after WHERE preparation (st_table_list::prep_where) @@ -2942,38 +2983,42 @@ bool st_table_list::prep_where(THD *thd, Item **conds, bool st_table_list::prep_check_option(THD *thd, uint8 check_opt_type) { DBUG_ENTER("st_table_list::prep_check_option"); + bool is_cascaded= check_opt_type == VIEW_CHECK_CASCADED; for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { /* see comment of check_opt_type parameter */ - if (tbl->view && - tbl->prep_check_option(thd, - ((check_opt_type == VIEW_CHECK_CASCADED) ? - VIEW_CHECK_CASCADED : - VIEW_CHECK_NONE))) - { + if (tbl->view && tbl->prep_check_option(thd, (is_cascaded ? + VIEW_CHECK_CASCADED : + VIEW_CHECK_NONE))) DBUG_RETURN(TRUE); - } } - if (check_opt_type) + if (check_opt_type && !check_option_processed) { - Item *item= 0; + Query_arena *arena= thd->stmt_arena, backup; + arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test + if (where) { DBUG_ASSERT(where->fixed); - item= where->copy_andor_structure(thd); + check_option= where->copy_andor_structure(thd); } - if (check_opt_type == VIEW_CHECK_CASCADED) + if (is_cascaded) { for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { if (tbl->check_option) - item= and_conds(item, tbl->check_option); + check_option= and_conds(check_option, tbl->check_option); } } - if (item) - thd->change_item_tree(&check_option, item); + check_option= and_conds(check_option, + merge_on_conds(thd, this, is_cascaded)); + + if (arena) + thd->restore_active_arena(arena, &backup); + check_option_processed= TRUE; + } if (check_option) @@ -3078,7 +3123,7 @@ void st_table_list::cleanup_items() check CHECK OPTION condition SYNOPSIS - check_option() + st_table_list::view_check_option() ignore_failure ignore check option fail RETURN diff --git a/sql/table.h b/sql/table.h index c4c7da7d0c2..77807530ef1 100644 --- a/sql/table.h +++ b/sql/table.h @@ -911,6 +911,8 @@ typedef struct st_table_list bool compact_view_format; /* Use compact format for SHOW CREATE VIEW */ /* view where processed */ bool where_processed; + /* TRUE <=> VIEW CHECK OPTION expression has been processed */ + bool check_option_processed; /* FRMTYPE_ERROR if any type is acceptable */ enum frm_type_enum required_type; handlerton *db_type; /* table_type for handler */ diff --git a/strings/decimal.c b/strings/decimal.c index ac82e7b16d6..f1753c619d1 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -1523,9 +1523,10 @@ decimal_round(decimal_t *from, decimal_t *to, int scale, dec1 *p0= buf0+intg0+max(frac1, frac0); dec1 *p1= buf1+intg1+max(frac1, frac0); - to->buf[0]= 0; while (buf0 < p0) *(--p1) = *(--p0); + if (unlikely(intg1 > intg0)) + to->buf[0]= 0; intg0= intg1; buf0=to->buf; |