From 6c8b5256c61653d315de5d7f91f2bfd1f2503c54 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 14 Feb 2007 18:35:59 +0200 Subject: Bug#19717: The blackhole engine is returning an OK flag in index_read(), whereas it must return HA_ERR_END_OF_FILE instead (as there are by definition no rows in a table of that engine. mysql-test/r/blackhole.result: Bug#19717: test case mysql-test/t/blackhole.test: Bug#19717: test case sql/ha_blackhole.cc: Bug#19717: return no rows instead of success. --- mysql-test/r/blackhole.result | 11 +++++++++++ mysql-test/t/blackhole.test | 15 +++++++++++++++ sql/ha_blackhole.cc | 2 +- 3 files changed, 27 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result index 140d7e73d48..0ee5f326e0f 100644 --- a/mysql-test/r/blackhole.result +++ b/mysql-test/r/blackhole.result @@ -123,3 +123,14 @@ master-bin.000001 # Query 1 # use `test`; create table t3 like t1 master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t3 master-bin.000001 # Query 1 # use `test`; replace into t1 select * from t3 drop table t1,t2,t3; +CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE; +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 ADD INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD UNIQUE INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD PRIMARY KEY(a); +DELETE FROM t1 WHERE a=10; +DROP TABLE t1; diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test index e40b84eb5cd..4bafad2d777 100644 --- a/mysql-test/t/blackhole.test +++ b/mysql-test/t/blackhole.test @@ -127,4 +127,19 @@ show binlog events; drop table t1,t2,t3; +# +#Bug#19717: DELETE Query Error on BLACKHOLE when using WHERE on column with UNIQUE INDEX +# +CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE; +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 ADD INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD UNIQUE INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD PRIMARY KEY(a); +DELETE FROM t1 WHERE a=10; +DROP TABLE t1; + # End of 4.1 tests diff --git a/sql/ha_blackhole.cc b/sql/ha_blackhole.cc index 61a8658be53..3f4285ec595 100644 --- a/sql/ha_blackhole.cc +++ b/sql/ha_blackhole.cc @@ -180,7 +180,7 @@ int ha_blackhole::index_read(byte * buf, const byte * key, uint key_len, enum ha_rkey_function find_flag) { DBUG_ENTER("ha_blackhole::index_read"); - DBUG_RETURN(0); + DBUG_RETURN(HA_ERR_END_OF_FILE); } -- cgit v1.2.1 From 6ae94723ca07c0938d25105d6180c96bc6abeaae Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 14 Feb 2007 22:06:41 -0800 Subject: Fixed bug #25971: indexes on text columns were ignored when ref accesses were evaluated. According to the new rules for string comparison partial indexes on text columns can be used in the same cases when partial indexes on varchar columns can be used. mysql-test/r/endspace.result: Adjusted results after the fix for bug #25971. mysql-test/r/innodb.result: Adjusted results after the fix for bug #25971. mysql-test/r/myisam.result: Adjusted results after the fix for bug #25971. mysql-test/r/select.result: Added a test case for bug #25971. mysql-test/r/type_blob.result: Adjusted results after the fix for bug #25971. mysql-test/t/select.test: Added a test case for bug #25971. --- mysql-test/r/endspace.result | 2 +- mysql-test/r/innodb.result | 2 +- mysql-test/r/myisam.result | 2 +- mysql-test/r/select.result | 148 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/r/type_blob.result | 4 +- mysql-test/t/select.test | 46 +++++++++++++ sql/sql_select.cc | 8 +-- 7 files changed, 200 insertions(+), 12 deletions(-) diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result index 0e68418a80f..003ee7ffd5e 100644 --- a/mysql-test/r/endspace.result +++ b/mysql-test/r/endspace.result @@ -98,7 +98,7 @@ concat('|', text1, '|') |teststring | explain select concat('|', text1, '|') from t1 where text1='teststring '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range key1 key1 22 NULL 2 Using where +1 SIMPLE t1 ref key1 key1 22 const 2 Using where select concat('|', text1, '|') from t1 where text1 like 'teststring_%'; concat('|', text1, '|') |teststring | diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 38d71ac7a42..496c8aa90d2 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1991,7 +1991,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where; Using index explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where; Using index diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 0f6e0ad537a..83359048a8c 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1071,7 +1071,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where; Using index explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where; Using index diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index f50f4859d48..c3132a1b5f6 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3785,4 +3785,152 @@ case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co; i c co 1111111111111111111 1111111111111111111 1111111111111111111 +CREATE TABLE t1 (name varchar(255)); +CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +name n +bb 1 +aa 2 +cc 3 +cc  4 +cc 5 +bb 6 +cc 7 +SELECT * FROM t2 ORDER BY name; +name n +aa 2 +bb 1 +bb 6 +cc  4 +cc 3 +cc 5 +cc 7 +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; +name LENGTH(name) n +aa 2 2 +bb 2 1 +bb 3 6 +cc  4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref name name 6 const 3 Using where +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +name LENGTH(name) n +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +name LENGTH(name) n +cc 5 3 +cc  4 4 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +name LENGTH(name) n +cc  4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ref name name 6 test.t1.name 2 +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +name name n +ccc NULL NULL +bb bb 1 +bb bb 6 +cc cc 3 +cc cc 5 +cc cc 7 +aa aa 2 +aa aa 2 +DROP TABLE t1,t2; +CREATE TABLE t1 (name text); +CREATE TABLE t2 (name text, n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +name n +bb 1 +aa 2 +cc 3 +cc  4 +cc 5 +bb 6 +cc 7 +SELECT * FROM t2 ORDER BY name; +name n +aa 2 +bb 1 +bb 6 +cc  4 +cc 3 +cc 5 +cc 7 +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; +name LENGTH(name) n +aa 2 2 +bb 2 1 +bb 3 6 +cc  4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref name name 6 const 3 Using where +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +name LENGTH(name) n +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +name LENGTH(name) n +cc 5 3 +cc  4 4 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +name LENGTH(name) n +cc  4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ref name name 6 test.t1.name 2 +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +name name n +ccc NULL NULL +bb bb 1 +bb bb 6 +cc cc 3 +cc cc 5 +cc cc 7 +aa aa 2 +aa aa 2 +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 73b67a2241e..b28f404c641 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -610,12 +610,12 @@ create table t1 (id integer primary key auto_increment, txt text, index txt_inde insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); select * from t1 where txt='Chevy' or txt is NULL; id txt -3 NULL 1 Chevy 2 Chevy +3 NULL explain select * from t1 where txt='Chevy' or txt is NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range txt_index txt_index 23 NULL 2 Using where +1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where select * from t1 where txt='Chevy '; id txt 1 Chevy diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index c4737814137..ea5fadb2e1b 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3253,4 +3253,50 @@ select case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co; +# +# Bug #22971: indexes on text columns are ignored for ref accesses +# + +CREATE TABLE t1 (name varchar(255)); +CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +SELECT * FROM t2 ORDER BY name; +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; + +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; + +DROP TABLE t1,t2; + +CREATE TABLE t1 (name text); +CREATE TABLE t2 (name text, n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +SELECT * FROM t2 ORDER BY name; +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; + +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; + +DROP TABLE t1,t2; + --echo End of 5.0 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ed108828909..93f5092fd60 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2854,15 +2854,9 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond, /* We can't use indexes if the effective collation of the operation differ from the field collation. - - We also cannot use index on a text column, as the column may - contain 'x' 'x\t' 'x ' and 'read_next_same' will stop after - 'x' when searching for WHERE col='x ' */ if (field->cmp_type() == STRING_RESULT && - (((Field_str*)field)->charset() != cond->compare_collation() || - ((*value)->type() != Item::NULL_ITEM && - (field->flags & BLOB_FLAG) && !field->binary()))) + ((Field_str*)field)->charset() != cond->compare_collation()) return; } } -- cgit v1.2.1 From 652a090a6f26ec60acebc36018a0a827a57442f1 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 16 Feb 2007 13:56:06 +0200 Subject: BUG#20420: optimizer reports wrong keys on left join with IN When checking if an IN predicate can be evaluated using a key the optimizer makes sure that all the arguments of IN are of the same result type. To assure that it check whether Item_func_in::array is filled in. However Item_func_in::array is set if the types are the same AND all the arguments are compile time constants. Fixed by introducing Item_func_in::arg_types_compatible flag to allow correct checking of the desired condition. mysql-test/r/func_in.result: BUG#20420: optimizer reports wrong keys on left join with IN - reenabled the test case mysql-test/t/func_in.test: BUG#20420: optimizer reports wrong keys on left join with IN - reenabled the test case sql/item_cmpfunc.cc: BUG#20420: optimizer reports wrong keys on left join with IN Check the IN argument types in a correct way sql/item_cmpfunc.h: BUG#20420: optimizer reports wrong keys on left join with IN Check the IN argument types in a correct way sql/opt_range.cc: BUG#20420: optimizer reports wrong keys on left join with IN Check the IN argument types in a correct way --- mysql-test/r/func_in.result | 44 ++++++++++++++++++++++++++++ mysql-test/t/func_in.test | 71 ++++++++++++++++++++++----------------------- sql/item_cmpfunc.cc | 22 ++++++++------ sql/item_cmpfunc.h | 8 ++++- sql/opt_range.cc | 4 +-- 5 files changed, 101 insertions(+), 48 deletions(-) diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 9b09cc0a43c..cf3aefa44e6 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -351,6 +351,50 @@ some_id 1 2 drop table t1; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102); +CREATE TABLE t3 (a int PRIMARY KEY); +INSERT INTO t3 VALUES (1),(2),(3),(4); +CREATE TABLE t4 (a int PRIMARY KEY,b int); +INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002), +(1003,1003),(1004,1004); +EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 +JOIN t1 ON t3.a=t1.a +JOIN t2 ON t3.a=t2.a +JOIN t4 WHERE t4.a IN (t1.b, t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1) +SELECT STRAIGHT_JOIN * FROM t3 +JOIN t1 ON t3.a=t1.a +JOIN t2 ON t3.a=t2.a +JOIN t4 WHERE t4.a IN (t1.b, t2.b); +a a b a b a b +3 3 1 3 2 1 1 +3 3 1 3 2 2 2 +4 4 1 4 2 1 1 +4 4 1 4 2 2 2 +EXPLAIN SELECT STRAIGHT_JOIN +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +FROM t3, t1, t2 +WHERE t3.a=t1.a AND t3.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index +SELECT STRAIGHT_JOIN +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +FROM t3, t1, t2 +WHERE t3.a=t1.a AND t3.a=t2.a; +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +3 +3 +DROP TABLE t1,t2,t3,t4; End of 5.0 tests create table t1(f1 char(1)); insert into t1 values ('a'),('b'),('1'); diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 2a2d5535915..1528c337c3e 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -254,44 +254,43 @@ select some_id from t1 where some_id not in(-4,-1,-4); select some_id from t1 where some_id not in(-4,-1,3423534,2342342); drop table t1; -# TODO:Disabled until re-resolution of bug #20420 for 5.1. -# Results must be the same as in 5.0 -## -## BUG#20420: optimizer reports wrong keys on left join with IN -## -#CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); -#INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); # -#CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); -#INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102); +# BUG#20420: optimizer reports wrong keys on left join with IN # -#CREATE TABLE t3 (a int PRIMARY KEY); -#INSERT INTO t3 VALUES (1),(2),(3),(4); -# -#CREATE TABLE t4 (a int PRIMARY KEY,b int); -#INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),(1003,1003),(1004,1004); -# -#EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 -# JOIN t1 ON t3.a=t1.a -# JOIN t2 ON t3.a=t2.a -# JOIN t4 WHERE t4.a IN (t1.b, t2.b); -# -#SELECT STRAIGHT_JOIN * FROM t3 -# JOIN t1 ON t3.a=t1.a -# JOIN t2 ON t3.a=t2.a -# JOIN t4 WHERE t4.a IN (t1.b, t2.b); -# -#EXPLAIN SELECT STRAIGHT_JOIN -# (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) -# FROM t3, t1, t2 -# WHERE t3.a=t1.a AND t3.a=t2.a; -# -#SELECT STRAIGHT_JOIN -# (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) -# FROM t3, t1, t2 -# WHERE t3.a=t1.a AND t3.a=t2.a; -# -#DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); + +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102); + +CREATE TABLE t3 (a int PRIMARY KEY); +INSERT INTO t3 VALUES (1),(2),(3),(4); + +CREATE TABLE t4 (a int PRIMARY KEY,b int); +INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002), + (1003,1003),(1004,1004); + +EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 + JOIN t1 ON t3.a=t1.a + JOIN t2 ON t3.a=t2.a + JOIN t4 WHERE t4.a IN (t1.b, t2.b); + +SELECT STRAIGHT_JOIN * FROM t3 + JOIN t1 ON t3.a=t1.a + JOIN t2 ON t3.a=t2.a + JOIN t4 WHERE t4.a IN (t1.b, t2.b); + +EXPLAIN SELECT STRAIGHT_JOIN + (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) + FROM t3, t1, t2 + WHERE t3.a=t1.a AND t3.a=t2.a; + +SELECT STRAIGHT_JOIN + (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) + FROM t3, t1, t2 + WHERE t3.a=t1.a AND t3.a=t2.a; + +DROP TABLE t1,t2,t3,t4; --echo End of 5.0 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 3a887e6dda6..26303606948 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2484,6 +2484,7 @@ void Item_func_in::fix_length_and_dec() THD *thd= current_thd; uint found_types= 0; uint type_cnt= 0, i; + Item_result cmp_type; left_result_type= args[0]->result_type(); found_types= collect_cmp_types(args, arg_count); @@ -2498,25 +2499,28 @@ void Item_func_in::fix_length_and_dec() for (i= 0; i <= (uint)DECIMAL_RESULT; i++) { if (found_types & 1 << i) + { (type_cnt)++; + cmp_type= (Item_result) i; + } } + + if (type_cnt == 1) + { + if (cmp_type == STRING_RESULT && + agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1)) + return; + arg_types_compatible= TRUE; + } + /* Row item with NULLs inside can return NULL or FALSE => they can't be processed as static */ if (type_cnt == 1 && const_itm && !nulls_in_row()) { - uint tmp_type; - Item_result cmp_type; - /* Only one cmp type was found. Extract it here */ - for (tmp_type= 0; found_types - 1; found_types>>= 1) - tmp_type++; - cmp_type= (Item_result)tmp_type; - switch (cmp_type) { case STRING_RESULT: - if (agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1)) - return; array=new in_string(arg_count - 1,(qsort2_cmp) srtcmp_in, cmp_collation.collation); break; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 899d29d4786..518da1105be 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1041,12 +1041,18 @@ public: */ in_vector *array; bool have_null; + /* + true when all arguments of the IN clause are of compatible types + and can be used safely as comparisons for key conditions + */ + bool arg_types_compatible; Item_result left_result_type; cmp_item *cmp_items[5]; /* One cmp_item for each result type */ DTCollation cmp_collation; Item_func_in(List &list) - :Item_func_opt_neg(list), array(0), have_null(0) + :Item_func_opt_neg(list), array(0), have_null(0), + arg_types_compatible(FALSE) { bzero(&cmp_items, sizeof(cmp_items)); allowed_arg_cols= 0; // Fetch this value from first argument diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 7f58e0359c4..b969e8f2cd2 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -4928,8 +4928,8 @@ static SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Item_func *cond_func, type. Tree won't be built for values with different result types, so we check it here to avoid unnecessary work. */ - if (!func->array) - break; + if (!func->arg_types_compatible) + break; if (inv) { -- cgit v1.2.1 From d5813a06f67d6478fed95aa9d10c44e1c1eb1076 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 16 Feb 2007 19:39:28 +0300 Subject: Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. If the UPDATE values of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE statement contains fields from the SELECT part and the select employs a temporary table then those fields will contain wrong values because they aren't corrected to get data from the temporary table. The solution is to add these fields to the selects all_fields list, to store pointers to those fields in the selects ref_pointer_array and to access them via Item_ref objects. The substitution for Item_ref objects is done in the new function called Item_field::update_value_transformer(). It is called through the item->transform() mechanism at the end of the select_insert::prepare() function. sql/item.cc: Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. The new method Item_field::update_value_transformer() is added. It substitutes fields in the update values list for references (Item_ref objects) to them. sql/item.h: Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. The update_value_transformer() method is added to the Item and to the Item_field classes. sql/sql_insert.cc: Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. Traverse update values and substitute fields from the select for references (Item_ref objects) to them. sql/sql_select.cc: Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. Traverse update values and substitute fields from the select for references (Item_ref objects) to them. mysql-test/r/insert_select.result: Added a test case for bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. mysql-test/t/insert_select.test: Added a test case for bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. --- mysql-test/r/insert_select.result | 13 +++++++++++ mysql-test/t/insert_select.test | 15 +++++++++++++ sql/item.cc | 45 +++++++++++++++++++++++++++++++++++++++ sql/item.h | 2 ++ sql/sql_insert.cc | 16 ++++++++++++++ sql/sql_select.cc | 6 +++--- 6 files changed, 94 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 2e0acf303c2..92b3ea0e42b 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -731,3 +731,16 @@ select @@identity; @@identity 0 drop table t1; +CREATE TABLE t1 (f1 INT, f2 INT ); +CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT); +INSERT INTO t1 VALUES (1,1),(2,2),(10,10); +INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1; +INSERT INTO t2 (f1, f2) +SELECT f1, f1 FROM t2 src WHERE f1 < 2 +ON DUPLICATE KEY UPDATE f1 = 100 + src.f1; +SELECT * FROM t2; +f1 f2 +101 1 +2 2 +10 10 +DROP TABLE t1, t2; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 5c60fc8e1f0..31508b3d6c4 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -292,3 +292,18 @@ select @@identity; insert ignore t1(f2) select 1; select @@identity; drop table t1; + +# +# Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE +# +CREATE TABLE t1 (f1 INT, f2 INT ); +CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT); +INSERT INTO t1 VALUES (1,1),(2,2),(10,10); +INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1; +INSERT INTO t2 (f1, f2) + SELECT f1, f1 FROM t2 src WHERE f1 < 2 + ON DUPLICATE KEY UPDATE f1 = 100 + src.f1; +SELECT * FROM t2; +DROP TABLE t1, t2; + + diff --git a/sql/item.cc b/sql/item.cc index 95001809e9a..23193e0990c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4809,6 +4809,51 @@ void Item_field::update_null_value() } +/* + Add the field to the select list and substitute it for the reference to + the field. + + SYNOPSIS + Item_field::update_value_transformer() + select_arg current select + + DESCRIPTION + If the field doesn't belong to the table being inserted into then it is + added to the select list, pointer to it is stored in the ref_pointer_array + of the select and the field itself is substituted for the Item_ref object. + This is done in order to get correct values from update fields that + belongs to the SELECT part in the INSERT .. SELECT .. ON DUPLICATE KEY + UPDATE statement. + + RETURN + 0 if error occured + ref if all conditions are met + this field otherwise +*/ + +Item *Item_field::update_value_transformer(byte *select_arg) +{ + SELECT_LEX *select= (SELECT_LEX*)select_arg; + DBUG_ASSERT(fixed); + + if (field->table != select->context.table_list->table && + type() != Item::TRIGGER_FIELD_ITEM) + { + List *all_fields= &select->join->all_fields; + Item **ref_pointer_array= select->ref_pointer_array; + int el= all_fields->elements; + Item_ref *ref; + + ref_pointer_array[el]= (Item*)this; + all_fields->push_front((Item*)this); + ref= new Item_ref(&select->context, ref_pointer_array + el, + table_name, field_name); + return ref; + } + return this; +} + + Item_ref::Item_ref(Name_resolution_context *context_arg, Item **item, const char *table_name_arg, const char *field_name_arg) diff --git a/sql/item.h b/sql/item.h index 6c41aa09f80..c254578f780 100644 --- a/sql/item.h +++ b/sql/item.h @@ -817,6 +817,7 @@ public: virtual Item_field *filed_for_view_update() { return 0; } virtual Item *neg_transformer(THD *thd) { return NULL; } + virtual Item *update_value_transformer(byte *select_arg) { return this; } virtual Item *safe_charset_converter(CHARSET_INFO *tocs); void delete_self() { @@ -1295,6 +1296,7 @@ public: Item_field *filed_for_view_update() { return this; } Item *safe_charset_converter(CHARSET_INFO *tocs); int fix_outer_field(THD *thd, Field **field, Item **reference); + virtual Item *update_value_transformer(byte *select_arg); friend class Item_default_value; friend class Item_insert_value; friend class st_select_lex_unit; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 39d7f8e9b58..542f47294e5 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2388,7 +2388,23 @@ select_insert::prepare(List &values, SELECT_LEX_UNIT *u) next_name_resolution_table= ctx_state.save_next_local; } res= res || setup_fields(thd, 0, *info.update_values, 1, 0, 0); + if (!res) + { + /* + Traverse the update values list and substitute fields from the + select for references (Item_ref objects) to them. This is done in + order to get correct values from those fields when the select + employs a temporary table. + */ + List_iterator li(*info.update_values); + Item *item; + while ((item= li++)) + { + item->transform(&Item::update_value_transformer, + (byte*)lex->current_select); + } + } /* Restore the current context. */ ctx_state.restore_state(context, table_list); } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 05ee0d77c1f..f4fec36aa53 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -482,6 +482,9 @@ JOIN::prepare(Item ***rref_pointer_array, } } + if (!procedure && result && result->prepare(fields_list, unit_arg)) + goto err; /* purecov: inspected */ + /* Init join struct */ count_field_types(&tmp_table_param, all_fields, 0); ref_pointer_array_size= all_fields.elements*sizeof(Item*); @@ -495,9 +498,6 @@ JOIN::prepare(Item ***rref_pointer_array, goto err; } #endif - if (!procedure && result && result->prepare(fields_list, unit_arg)) - goto err; /* purecov: inspected */ - if (select_lex->olap == ROLLUP_TYPE && rollup_init()) goto err; if (alloc_func_list()) -- cgit v1.2.1 From 4d7334c821bb3ff8b89c4715c5008e831512006d Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 16 Feb 2007 14:30:25 -0800 Subject: Adjusted results after fix for bug 25971. --- mysql-test/r/bdb.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index 33398d1ddd7..382fbf750c4 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -1442,7 +1442,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 NULL # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where -- cgit v1.2.1 From d7992c88c6e60c92e6736cd5c95790923f79a59d Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 16 Feb 2007 15:43:50 -0800 Subject: Adjusted results after fix for bug 25971. --- mysql-test/r/bdb.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index 382fbf750c4..91c385112b4 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -1442,7 +1442,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where -- cgit v1.2.1 From a97fd193715799843f02228a01bba24e8b98fa44 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 19 Feb 2007 14:39:37 +0200 Subject: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. Several problems fixed: 1. There was a "catch-all" context initialization in setup_tables() that was causing the table that we insert into to be visible in the SELECT part of an INSERT .. SELECT .. statement with no tables in its FROM clause. This was making sure all the under-initialized contexts in various parts of the code are not left uninitialized. Fixed by removing the "catch-all" statement and initializing the context in the parser. 2. Incomplete name resolution context when resolving the right-hand values in the ON DUPLICATE KEY UPDATE ... part of an INSERT ... SELECT ... caused columns from NATURAL JOIN/JOIN USING table references in the FROM clause of the select to be unavailable. Fixed by establishing a proper name resolution context. 3. When setting up the special name resolution context for problem 2 there was no check for cases where an aggregate function without a GROUP BY effectively takes the column from the SELECT part of an INSERT ... SELECT unavailable for ON DUPLICATE KEY UPDATE. Fixed by checking for that condition when setting up the name resolution context. mysql-test/r/insert_update.result: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - test case mysql-test/t/insert_update.test: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - test case sql/item.h: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - save_next_local is not referenced any more outside class methods sql/sql_base.cc: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - removed a "catch-all" code to cater for correct context initialization sql/sql_help.cc: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - fixed the name resolution context initialization sql/sql_insert.cc: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - Fixed the context of resolving the values in INSERT SELECT ON UPDATE sql/sql_prepare.cc: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - Correct context for name resolution of prepared INSERT .. SELECT sql/sql_union.cc: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - fixed the name resolution context initialization sql/sql_yacc.yy: Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving. - Set the context here instead of setup_tables() --- mysql-test/r/insert_update.result | 17 +++++++++ mysql-test/t/insert_update.test | 23 ++++++++++++ sql/item.h | 7 +++- sql/sql_base.cc | 17 +-------- sql/sql_help.cc | 2 + sql/sql_insert.cc | 77 ++++++++++++++++++++------------------- sql/sql_prepare.cc | 21 ++++------- sql/sql_union.cc | 2 + sql/sql_yacc.yy | 7 +++- 9 files changed, 105 insertions(+), 68 deletions(-) diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 6be37450835..f658ff06624 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -219,3 +219,20 @@ SELECT * FROM t1; a b 45 2 DROP TABLE t1; +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +INSERT INTO t1 SELECT 1, j; +ERROR 42S22: Unknown column 'j' in 'field list' +DROP TABLE t1; +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT, c INT); +INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 +ON DUPLICATE KEY UPDATE j= a; +DROP TABLE t1,t2,t3; +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t2 VALUES (1), (3); +INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; +ERROR 42S22: Unknown column 'a' in 'field list' +DROP TABLE t1,t2; diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index b3813864464..4581cc7a875 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -139,3 +139,26 @@ INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = SELECT * FROM t1; DROP TABLE t1; + +# +# Bug#25831: Deficiencies in INSERT ... SELECT ... field name resolving. +# +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +--error ER_BAD_FIELD_ERROR +INSERT INTO t1 SELECT 1, j; +DROP TABLE t1; + +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT, c INT); +INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 + ON DUPLICATE KEY UPDATE j= a; +DROP TABLE t1,t2,t3; + +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t2 VALUES (1), (3); +--error ER_BAD_FIELD_ERROR +INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; +DROP TABLE t1,t2; diff --git a/sql/item.h b/sql/item.h index 62d6127f10b..e6be2f223da 100644 --- a/sql/item.h +++ b/sql/item.h @@ -325,10 +325,10 @@ private: TABLE_LIST *save_first_name_resolution_table; TABLE_LIST *save_next_name_resolution_table; bool save_resolve_in_select_list; + TABLE_LIST *save_next_local; public: Name_resolution_context_state() {} /* Remove gcc warning */ - TABLE_LIST *save_next_local; public: /* Save the state of a name resolution context. */ @@ -355,6 +355,11 @@ public: next_name_resolution_table= save_next_name_resolution_table; context->resolve_in_select_list= save_resolve_in_select_list; } + + TABLE_LIST *get_first_name_resolution_table() + { + return save_first_name_resolution_table; + } }; /*************************************************************************/ diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0949d4aa331..304a16dc62e 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4498,21 +4498,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context, uint tablenr= 0; DBUG_ENTER("setup_tables"); - /* - Due to the various call paths that lead to setup_tables() it may happen - that context->table_list and context->first_name_resolution_table can be - NULL (this is typically done when creating TABLE_LISTs internally). - TODO: - Investigate all cases when this my happen, initialize the name resolution - context correctly in all those places, and remove the context reset below. - */ - if (!context->table_list || !context->first_name_resolution_table) - { - /* Test whether the context is in a consistent state. */ - DBUG_ASSERT(!context->first_name_resolution_table && !context->table_list); - context->table_list= context->first_name_resolution_table= tables; - } - + DBUG_ASSERT ((select_insert && !tables->next_name_resolution_table) || !tables || + (context->table_list && context->first_name_resolution_table)); /* this is used for INSERT ... SELECT. For select we setup tables except first (and its underlying tables) diff --git a/sql/sql_help.cc b/sql/sql_help.cc index 78349a6ef0d..ba7f1a534ea 100644 --- a/sql/sql_help.cc +++ b/sql/sql_help.cc @@ -656,6 +656,8 @@ bool mysqld_help(THD *thd, const char *mask) Init tables and fields to be usable from items tables do not contain VIEWs => we can pass 0 as conds */ + thd->lex->select_lex.context.table_list= + thd->lex->select_lex.context.first_name_resolution_table= &tables[0]; setup_tables(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, tables, 0, &leaves, FALSE); diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index fb59aeea8e7..75caba84716 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -966,6 +966,8 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, DBUG_PRINT("enter", ("table_list 0x%lx, table 0x%lx, view %d", (ulong)table_list, (ulong)table, (int)insert_into_view)); + /* INSERT should have a SELECT or VALUES clause */ + DBUG_ASSERT (!select_insert || !values); /* For subqueries in VALUES() we should not see the table in which we are @@ -998,44 +1000,40 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, select_insert)) DBUG_RETURN(TRUE); - /* Save the state of the current name resolution context. */ - ctx_state.save_state(context, table_list); - - /* - Perform name resolution only in the first table - 'table_list', - which is the table that is inserted into. - */ - table_list->next_local= 0; - context->resolve_in_table_list_only(table_list); /* Prepare the fields in the statement. */ - if (values && - !(res= check_insert_fields(thd, context->table_list, fields, *values, - !insert_into_view, &map) || - setup_fields(thd, 0, *values, 0, 0, 0)) && - duplic == DUP_UPDATE) + if (values) { - select_lex->no_wrap_view_item= TRUE; - res= check_update_fields(thd, context->table_list, update_fields, &map); - select_lex->no_wrap_view_item= FALSE; + /* if we have INSERT ... VALUES () we cannot have a GROUP BY clause */ + DBUG_ASSERT (!select_lex->group_list.elements); + + /* Save the state of the current name resolution context. */ + ctx_state.save_state(context, table_list); + /* - When we are not using GROUP BY we can refer to other tables in the - ON DUPLICATE KEY part. - */ - if (select_lex->group_list.elements == 0) + Perform name resolution only in the first table - 'table_list', + which is the table that is inserted into. + */ + table_list->next_local= 0; + context->resolve_in_table_list_only(table_list); + + if (!(res= check_insert_fields(thd, context->table_list, fields, *values, + !insert_into_view, &map) || + setup_fields(thd, 0, *values, 0, 0, 0)) + && duplic == DUP_UPDATE) { - context->table_list->next_local= ctx_state.save_next_local; - /* first_name_resolution_table was set by resolve_in_table_list_only() */ - context->first_name_resolution_table-> - next_name_resolution_table= ctx_state.save_next_local; + select_lex->no_wrap_view_item= TRUE; + res= check_update_fields(thd, context->table_list, update_fields, &map); + select_lex->no_wrap_view_item= FALSE; } + + /* Restore the current context. */ + ctx_state.restore_state(context, table_list); + if (!res) res= setup_fields(thd, 0, update_values, 1, 0, 0); } - /* Restore the current context. */ - ctx_state.restore_state(context, table_list); - if (res) DBUG_RETURN(res); @@ -2355,7 +2353,6 @@ select_insert::prepare(List &values, SELECT_LEX_UNIT *u) if (info.handle_duplicates == DUP_UPDATE) { - /* Save the state of the current name resolution context. */ Name_resolution_context *context= &lex->select_lex.context; Name_resolution_context_state ctx_state; @@ -2371,16 +2368,20 @@ select_insert::prepare(List &values, SELECT_LEX_UNIT *u) *info.update_fields, &map); lex->select_lex.no_wrap_view_item= FALSE; /* - When we are not using GROUP BY we can refer to other tables in the - ON DUPLICATE KEY part + When we are not using GROUP BY and there are no ungrouped aggregate functions + we can refer to other tables in the ON DUPLICATE KEY part. + We use next_name_resolution_table descructively, so check it first (views?) */ - if (lex->select_lex.group_list.elements == 0) - { - context->table_list->next_local= ctx_state.save_next_local; - /* first_name_resolution_table was set by resolve_in_table_list_only() */ - context->first_name_resolution_table-> - next_name_resolution_table= ctx_state.save_next_local; - } + DBUG_ASSERT (!table_list->next_name_resolution_table); + if (lex->select_lex.group_list.elements == 0 && + !lex->select_lex.with_sum_func) + /* + We must make a single context out of the two separate name resolution contexts : + the INSERT table and the tables in the SELECT part of INSERT ... SELECT. + To do that we must concatenate the two lists + */ + table_list->next_name_resolution_table= ctx_state.get_first_name_resolution_table(); + res= res || setup_fields(thd, 0, *info.update_values, 1, 0, 0); /* Restore the current context. */ diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 002b1d52331..3b46d613c93 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1571,21 +1571,16 @@ error: static bool mysql_insert_select_prepare_tester(THD *thd) { - TABLE_LIST *first; - bool res; SELECT_LEX *first_select= &thd->lex->select_lex; + TABLE_LIST *second_table= ((TABLE_LIST*)first_select->table_list.first)-> + next_local; + /* Skip first table, which is the table we are inserting in */ - first_select->table_list.first= (byte*)(first= - ((TABLE_LIST*)first_select-> - table_list.first)->next_local); - res= mysql_insert_select_prepare(thd); - /* - insert/replace from SELECT give its SELECT_LEX for SELECT, - and item_list belong to SELECT - */ - thd->lex->select_lex.context.resolve_in_select_list= TRUE; - thd->lex->select_lex.context.table_list= first; - return res; + first_select->table_list.first= (byte *) second_table; + thd->lex->select_lex.context.table_list= + thd->lex->select_lex.context.first_name_resolution_table= second_table; + + return mysql_insert_select_prepare(thd); } diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 8b7dde2f818..0632d88875e 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -147,6 +147,8 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd) fake_select_lex->table_list.link_in_list((byte *)&result_table_list, (byte **) &result_table_list.next_local); + fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table= + fake_select_lex->get_table_list(); for (ORDER *order= (ORDER *)global_parameters->order_list.first; order; order=order->next) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 92640ea58d6..15682385cfe 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4188,8 +4188,13 @@ select_into: | select_from into; select_from: - FROM join_table_list where_clause group_clause having_clause + FROM join_table_list where_clause group_clause having_clause opt_order_clause opt_limit_clause procedure_clause + { + Select->context.table_list= + Select->context.first_name_resolution_table= + (TABLE_LIST *) Select->table_list.first; + } | FROM DUAL_SYM where_clause opt_limit_clause /* oracle compatibility: oracle always requires FROM clause, and DUAL is system table without fields. -- cgit v1.2.1 From f8855142d7a498c540f8f92ab1095a1038aeb079 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 21 Feb 2007 23:00:32 +0300 Subject: Bug#23800: Outer fields in correlated subqueries is used in a temporary table created for sorting. Any outer reference in a subquery was represented by an Item_field object. If the outer select employs a temporary table all such fields should be replaced with fields from that temporary table in order to point to the actual data. This replacement wasn't done and that resulted in a wrong subquery evaluation and a wrong result of the whole query. Now any outer field is represented by two objects - Item_field placed in the outer select and Item_outer_ref in the subquery. Item_field object is processed as a normal field and the reference to it is saved in the ref_pointer_array. Thus the Item_outer_ref is always references the correct field. The original field is substituted for a reference in the Item_field::fix_outer_field() function. New function called fix_inner_refs() is added to fix fields referenced from inner selects and to fix references (Item_ref objects) to these fields. The new Item_outer_ref class is a descendant of the Item_direct_ref class. It additionally stores a reference to the original field and designed to behave more like a field. sql/item.cc: Bug#23800: Correlated sub query returning incorrect results when operated upon. Now all outer fields are substituted with references to them (Item_outer_ref objects) in the Item_field::fix_outer_field() function. The original field is saved in the Item_outer_ref object. sql/item.h: Bug#23800: Correlated sub query returning incorrect results when operated upon. Added the Item_outer_ref class. sql/mysql_priv.h: Bug#23800: Correlated sub query returning incorrect results when operated upon. Added the fix_inner_refs() function prototype. sql/sql_delete.cc: Bug#23800: Correlated sub query returning incorrect results when operated upon. Added call to the fix_inner_refs() function. sql/sql_select.cc: Bug#23800: Correlated sub query returning incorrect results when operated upon. The new function called fix_inner_refs() is added. mysql-test/r/subselect.result: Added a test case for bug#23800: Correlated sub query returning incorrect results when operated upon. sql/sql_update.cc: Bug#23800: Correlated sub query returning incorrect results when operated upon. Added call to the fix_inner_refs() function. mysql-test/r/subselect3.result: Corrected test cases result after fix for bug#23800: Correlated sub query returning incorrect results when operated upon. mysql-test/t/subselect.test: Added a test case for bug#23800: Correlated sub query returning incorrect results when operated upon. sql/sql_lex.cc: Bug#23800: Correlated sub query returning incorrect results when operated upon. Added cleanup of the inner_refs_list. sql/sql_lex.h: Bug#23800: Correlated sub query returning incorrect results when operated upon. The inner_refs_list is added to the SELECT_LEX class. --- mysql-test/r/subselect.result | 60 +++++++++++++++++++++++++----- mysql-test/r/subselect3.result | 22 +++++------ mysql-test/t/subselect.test | 29 ++++++++++++++- sql/item.cc | 80 +++++++++++++++++++++++++++++++++++----- sql/item.h | 40 ++++++++++++++++++-- sql/mysql_priv.h | 2 + sql/sql_delete.cc | 6 +++ sql/sql_lex.cc | 1 + sql/sql_lex.h | 3 +- sql/sql_select.cc | 83 ++++++++++++++++++++++++++++++++++++++---- sql/sql_update.cc | 5 +++ 11 files changed, 290 insertions(+), 41 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 06f8c019265..7177b4e4432 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: -Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4` select * from t3 where exists (select * from t2 where t2.b=t3.a); a @@ -313,8 +313,8 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where NULL UNION RESULT ALL NULL NULL NULL NULL NULL Warnings: -Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 -Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row @@ -330,9 +330,9 @@ patient_uq clinic_uq explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index +2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 Using where; Using index Warnings: -Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column 'a' in field list is ambiguous @@ -868,7 +868,7 @@ explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Warnings: -Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1` select (select a+1) from t1; @@ -1741,9 +1741,9 @@ Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `tes explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 Using where; Using index Warnings: -Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); @@ -2279,7 +2279,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where Warnings: -Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)) drop table t1; CREATE TABLE t1 (t1_a int); @@ -3712,3 +3712,45 @@ bb 2 cc 3 dd 1 DROP TABLE t1,t2,t3; +CREATE TABLE t1(f1 int); +CREATE TABLE t2(f2 int, f21 int, f3 timestamp); +INSERT INTO t1 VALUES (1),(1),(2),(2); +INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11"); +SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1; +sq +2 +4 +SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2; +tt +2 +2 +PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1'; +EXECUTE stmt1; +sq +2 +4 +EXECUTE stmt1; +sq +2 +4 +DEALLOCATE PREPARE stmt1; +SELECT f2, AVG(f21), +(SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test +FROM t2 GROUP BY f2; +f2 AVG(f21) test +1 1.0000 2004-02-29 11:11:11 +2 2.0000 2004-02-29 11:11:11 +DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); +INSERT INTO t1 VALUES +(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), +(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), +(3,2,'k'), (3,1,'l'), (1,9,'m'); +SELECT a, MAX(b), +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test +FROM t1 GROUP BY a; +a MAX(b) test +1 9 m +2 3 h +3 4 i +DROP TABLE t1; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 29143b9e504..b975ea8cbdc 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -29,7 +29,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +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`.`oref` AS `oref`,(`test`.`t2`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond(((`test`.`t2`.`a`) = (max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2` explain extended select a, oref from t2 @@ -38,7 +38,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +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`.`oref` AS `oref` from `test`.`t2` where (`test`.`t2`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having ((`test`.`t2`.`a`) = (max(`test`.`t1`.`ie`))))) select a, oref, a in ( select max(ie) from t1 where oref=t2.oref group by grp union @@ -91,7 +91,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,(`test`.`t2`.`a`,(((`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond((`test`.`t1`.`a`))))) AS `Z` from `test`.`t2` flush status; select oref, a from t2 where a in (select a from t1 where oref=t2.oref); @@ -156,7 +156,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where Warnings: -Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,(`test`.`t3`.`a`,(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((((`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond((`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); @@ -184,7 +184,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where Warnings: -Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,(`test`.`t3`.`a`,(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); @@ -240,7 +240,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +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`,((`test`.`t2`.`a`,`test`.`t2`.`b`),(((`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond((((`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond((((`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond((`test`.`t1`.`a`)) and trigcond((`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; a b oref Z @@ -257,7 +257,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 Using where Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +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`,((`test`.`t2`.`a`,`test`.`t2`.`b`),(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((((`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond((((`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond((`test`.`t1`.`a`)) and trigcond((`test`.`t1`.`b`))))) AS `Z` from `test`.`t2` select a,b, oref, (a,b) in (select a,b from t1,t4 where c=t2.oref) Z @@ -302,7 +302,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,((`test`.`t2`.`a`,`test`.`t2`.`b`),(((`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond((((`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond((((`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond((`test`.`t1`.`ie1`)) and trigcond((`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10)) drop table t1, t2; create table t1 (oref char(4), grp int, ie int); @@ -432,7 +432,7 @@ alter table t1 add index idx(oref,ie); explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 7 -2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key +2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 t2.oref,func 4 Using where; Using index; Full scan on NULL key select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; oref a Z ee NULL NULL @@ -457,7 +457,7 @@ group by grp having min(ie) > 1) Z from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 7 -2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort +2 DEPENDENT SUBQUERY t1 ref idx idx 5 t2.oref 2 Using where; Using temporary; Using filesort select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp having min(ie) > 1) Z @@ -572,7 +572,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 7 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,((`test`.`t2`.`a`,`test`.`t2`.`b`),(((`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond((((`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond((((`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond((`test`.`t1`.`ie1`)) and trigcond((`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` drop table t1,t2; create table t1 (oref char(4), grp int, ie int primary key); diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d8478bae258..11cec291d8c 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2600,4 +2600,31 @@ SELECT * FROM t1 UNION SELECT c from t2 WHERE c=t1.c); -DROP TABLE t1,t2,t3; +DROP TABLE t1,t2,t3; +# +# Bug#23800: Outer fields in correlated subqueries is used in a temporary +# table created for sorting. +# +CREATE TABLE t1(f1 int); +CREATE TABLE t2(f2 int, f21 int, f3 timestamp); +INSERT INTO t1 VALUES (1),(1),(2),(2); +INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11"); +SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1; +SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2; +PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1'; +EXECUTE stmt1; +EXECUTE stmt1; +DEALLOCATE PREPARE stmt1; +SELECT f2, AVG(f21), + (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test + FROM t2 GROUP BY f2; +DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); +INSERT INTO t1 VALUES + (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), + (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), + (3,2,'k'), (3,1,'l'), (1,9,'m'); +SELECT a, MAX(b), + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test + FROM t1 GROUP BY a; +DROP TABLE t1; diff --git a/sql/item.cc b/sql/item.cc index 95001809e9a..54aaf573525 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1609,7 +1609,7 @@ void Item_ident_for_show::make_field(Send_field *tmp_field) Item_field::Item_field(Field *f) :Item_ident(0, NullS, *f->table_name, f->field_name), item_equal(0), no_const_subst(0), - have_privileges(0), any_privileges(0) + have_privileges(0), any_privileges(0), fixed_as_field(0) { set_field(f); /* @@ -1623,7 +1623,7 @@ Item_field::Item_field(THD *thd, Name_resolution_context *context_arg, Field *f) :Item_ident(context_arg, f->table->s->db, *f->table_name, f->field_name), item_equal(0), no_const_subst(0), - have_privileges(0), any_privileges(0) + have_privileges(0), any_privileges(0), fixed_as_field(0) { /* We always need to provide Item_field with a fully qualified field @@ -1662,7 +1662,7 @@ Item_field::Item_field(Name_resolution_context *context_arg, const char *field_name_arg) :Item_ident(context_arg, db_arg,table_name_arg,field_name_arg), field(0), result_field(0), item_equal(0), no_const_subst(0), - have_privileges(0), any_privileges(0) + have_privileges(0), any_privileges(0), fixed_as_field(0) { collation.set(DERIVATION_IMPLICIT); } @@ -1675,7 +1675,8 @@ Item_field::Item_field(THD *thd, Item_field *item) item_equal(item->item_equal), no_const_subst(item->no_const_subst), have_privileges(item->have_privileges), - any_privileges(item->any_privileges) + any_privileges(item->any_privileges), + fixed_as_field(item->fixed_as_field) { collation.set(DERIVATION_IMPLICIT); } @@ -3484,8 +3485,46 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) } if (*from_field != view_ref_found) { + prev_subselect_item->used_tables_cache|= (*from_field)->table->map; prev_subselect_item->const_item_cache= 0; + if (!last_checked_context->select_lex->having_fix_field && + !fixed_as_field) + { + Item_outer_ref *rf; + Query_arena *arena= 0, backup; + /* + Each outer field is replaced for an Item_outer_ref object. + This is done in order to get correct results when the outer + select employs a temporary table. + The original fields are saved in the inner_fields_list of the + outer select. This list is created by the following reasons: + 1. We can't add field items to the outer select list directly + because the outer select hasn't been fully fixed yet. + 2. We need a location to refer to in the Item_ref object + so the inner_fields_list is used as such temporary + reference storage. + The new Item_outer_ref object replaces the original field and is + also saved in the inner_refs_list of the outer select. Here + it is only created. It can be fixed only after the original + field has been fixed and this is done in the fix_inner_refs() + function. + */ + set_field(*from_field); + arena= thd->activate_stmt_arena_if_needed(&backup); + rf= new Item_outer_ref(context, this); + if (!rf) + { + if (arena) + thd->restore_active_arena(arena, &backup); + return -1; + } + *reference= rf; + select->inner_refs_list.push_back(rf); + if (arena) + thd->restore_active_arena(arena, &backup); + fixed_as_field= 1; + } if (thd->lex->in_sum_func && thd->lex->in_sum_func->nest_level == thd->lex->current_select->nest_level) @@ -3612,7 +3651,7 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) { mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, - this, this); + this, (Item_ident*)*reference); if (last_checked_context->select_lex->having_fix_field) { Item_ref *rf; @@ -4818,8 +4857,7 @@ Item_ref::Item_ref(Name_resolution_context *context_arg, /* This constructor used to create some internals references over fixed items */ - DBUG_ASSERT(ref != 0); - if (*ref && (*ref)->fixed) + if (ref && *ref && (*ref)->fixed) set_properties(); } @@ -5119,7 +5157,7 @@ void Item_ref::print(String *str) if (ref) { if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF && - name && alias_name_used) + ref_type() != OUTER_REF && name && alias_name_used) { THD *thd= current_thd; append_identifier(thd, str, name, (uint) strlen(name)); @@ -5367,7 +5405,7 @@ bool Item_direct_ref::get_date(TIME *ltime,uint fuzzydate) /* - Prepare referenced view viewld then call usual Item_direct_ref::fix_fields + Prepare referenced field then call usual Item_direct_ref::fix_fields SYNOPSIS Item_direct_view_ref::fix_fields() @@ -5390,6 +5428,30 @@ bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference) return Item_direct_ref::fix_fields(thd, reference); } +/* + Prepare referenced outer field then call usual Item_direct_ref::fix_fields + + SYNOPSIS + Item_outer_ref::fix_fields() + thd thread handler + reference reference on reference where this item stored + + RETURN + FALSE OK + TRUE Error +*/ + +bool Item_outer_ref::fix_fields(THD *thd, Item **reference) +{ + DBUG_ASSERT(*ref); + /* outer_field->check_cols() will be made in Item_direct_ref::fix_fields */ + outer_field->fixed_as_field= 1; + if (!outer_field->fixed && + (outer_field->fix_fields(thd, reference))) + return TRUE; + return Item_direct_ref::fix_fields(thd, reference); +} + /* Compare two view column references for equality. diff --git a/sql/item.h b/sql/item.h index 6c41aa09f80..c2bc483e87b 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1214,7 +1214,7 @@ public: uint have_privileges; /* field need any privileges (for VIEW creation) */ bool any_privileges; - + bool fixed_as_field; Item_field(Name_resolution_context *context_arg, const char *db_arg,const char *table_name_arg, const char *field_name_arg); @@ -1817,7 +1817,7 @@ class Item_ref :public Item_ident protected: void set_properties(); public: - enum Ref_Type { REF, DIRECT_REF, VIEW_REF }; + enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF }; Field *result_field; /* Save result here */ Item **ref; Item_ref(Name_resolution_context *context_arg, @@ -1878,7 +1878,7 @@ public: (*ref)->get_tmp_table_item(thd)); } table_map used_tables() const - { + { return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); } table_map not_null_tables() const { return (*ref)->not_null_tables(); } @@ -1951,6 +1951,40 @@ public: }; +class Item_outer_ref :public Item_direct_ref +{ +public: + Item_field *outer_field; + Item_outer_ref(Name_resolution_context *context_arg, + Item_field *outer_field_arg) + :Item_direct_ref(context_arg, 0, outer_field_arg->table_name, + outer_field_arg->field_name), + outer_field(outer_field_arg) + { + ref= (Item**)&outer_field; + set_properties(); + fixed= 0; + } + void cleanup() + { + ref= (Item**)&outer_field; + fixed= 0; + Item_direct_ref::cleanup(); + outer_field->cleanup(); + } + void save_in_result_field(bool no_conversions) + { + outer_field->save_org_in_field(result_field); + } + bool fix_fields(THD *, Item **); + table_map used_tables() const + { + return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT; + } + virtual Ref_Type ref_type() { return OUTER_REF; } +}; + + class Item_in_subselect; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 1dd3c9dceca..792afeca59e 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -747,6 +747,8 @@ int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, List &fields, List &all_fields, ORDER *order, bool *hidden_group_fields); +bool fix_inner_refs(THD *thd, List &all_fields, SELECT_LEX *select, + Item **ref_pointer_array); bool handle_select(THD *thd, LEX *lex, select_result *result, ulong setup_tables_done_option); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 94d753eb703..749ee04493b 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -353,6 +353,7 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) Item *fake_conds= 0; SELECT_LEX *select_lex= &thd->lex->select_lex; DBUG_ENTER("mysql_prepare_delete"); + List all_fields; thd->lex->allow_sum_func= 0; if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, @@ -376,6 +377,11 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) DBUG_RETURN(TRUE); } } + + if (select_lex->inner_refs_list.elements && + fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array)) + DBUG_RETURN(-1); + select_lex->fix_prepare_information(thd, conds, &fake_conds); DBUG_RETURN(FALSE); } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index ce76c35b33c..c3e18394c41 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1192,6 +1192,7 @@ void st_select_lex::init_select() is_correlated= 0; cur_pos_in_select_list= UNDEF_POS; non_agg_fields.empty(); + inner_refs_list.empty(); } /* diff --git a/sql/sql_lex.h b/sql/sql_lex.h index ae2b0d30a9c..f8d8e6efcb2 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -547,7 +547,8 @@ public: bool braces; /* SELECT ... UNION (SELECT ... ) <- this braces */ /* TRUE when having fix field called in processing of this SELECT */ bool having_fix_field; - + /* List of references to fields referenced from inner selects */ + List inner_refs_list; /* Number of Item_sum-derived objects in this SELECT */ uint n_sum_items; /* Number of Item_sum-derived objects in children and descendant SELECTs */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 63f2604a934..2f2eb0ecca1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -268,6 +268,70 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, } +/* + Fix fields referenced from inner selects. + + SYNOPSIS + fix_inner_refs() + thd Thread handle + all_fields List of all fields used in select + select Current select + ref_pointer_array Array of references to Items used in current select + + DESCRIPTION + The function fixes fields referenced from inner selects and + also fixes references (Item_ref objects) to these fields. Each field + is fixed as a usual hidden field of the current select - it is added + to the all_fields list and the pointer to it is saved in the + ref_pointer_array if latter is provided. + After the field has been fixed we proceed with fixing references + (Item_ref objects) to this field from inner subqueries. If the + ref_pointer_array is provided then Item_ref objects is set to + reference element in that array with the pointer to the field. + + RETURN + TRUE an error occured + FALSE ok +*/ + +bool +fix_inner_refs(THD *thd, List &all_fields, SELECT_LEX *select, + Item **ref_pointer_array) +{ + Item_outer_ref *ref; + bool res= FALSE; + List_iterator ref_it(select->inner_refs_list); + while ((ref= ref_it++)) + { + Item_field *item= ref->outer_field; + /* + TODO: this field item already might be present in the select list. + In this case instead of adding new field item we could use an + existing one. The change will lead to less operations for copying fields, + smaller temporary tables and less data passed through filesort. + */ + if (ref_pointer_array) + { + int el= all_fields.elements; + ref_pointer_array[el]= (Item*)item; + /* Add the field item to the select list of the current select. */ + all_fields.push_front((Item*)item); + /* + If it's needed reset each Item_ref item that refers this field with + a new reference taken from ref_pointer_array. + */ + ref->ref= ref_pointer_array + el; + } + if (!ref->fixed && ref->fix_fields(thd, 0)) + { + res= TRUE; + break; + } + thd->used_tables|= item->used_tables(); + } + return res; +} + /* Function to setup clauses without sum functions */ @@ -395,6 +459,10 @@ JOIN::prepare(Item ***rref_pointer_array, if (having && having->with_sum_func) having->split_sum_func2(thd, ref_pointer_array, all_fields, &having, TRUE); + if (select_lex->inner_refs_list.elements && + fix_inner_refs(thd, all_fields, select_lex, ref_pointer_array)) + DBUG_RETURN(-1); + if (select_lex->inner_sum_func_list) { Item_sum *end=select_lex->inner_sum_func_list; @@ -5133,13 +5201,15 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables, key_part->length, keyuse->val); } - else if (keyuse->val->type() == Item::FIELD_ITEM) + else if (keyuse->val->type() == Item::FIELD_ITEM || + (keyuse->val->type() == Item::REF_ITEM && + ((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF) ) return new store_key_field(thd, key_part->field, key_buff + maybe_null, maybe_null ? key_buff : 0, key_part->length, - ((Item_field*) keyuse->val)->field, + ((Item_field*) keyuse->val->real_item())->field, keyuse->val->full_name()); return new store_key_item(thd, key_part->field, @@ -8697,7 +8767,8 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, type they needed to be handled separately. */ if ((type= item->field_type()) == MYSQL_TYPE_DATETIME || - type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE) + type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE || + type == MYSQL_TYPE_TIMESTAMP) new_field= item->tmp_table_field_from_field_type(table); /* Make sure that the blob fits into a Field_varstring which has @@ -8804,8 +8875,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, Item *orig_item= 0; if (type != Item::FIELD_ITEM && - item->real_item()->type() == Item::FIELD_ITEM && - !((Item_ref *) item)->depended_from) + item->real_item()->type() == Item::FIELD_ITEM) { orig_item= item; item= item->real_item(); @@ -13465,8 +13535,7 @@ count_field_types(TMP_TABLE_PARAM *param, List &fields, { Item::Type type=field->type(); Item::Type real_type= field->real_item()->type(); - if (type == Item::FIELD_ITEM || (real_type == Item::FIELD_ITEM && - !((Item_ref *) field)->depended_from)) + if (real_type == Item::FIELD_ITEM) param->field_count++; else if (real_type == Item::SUM_FUNC_ITEM) { diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 1db77f8704c..60b7ac1df1c 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -134,6 +134,7 @@ int mysql_update(THD *thd, READ_RECORD info; SELECT_LEX *select_lex= &thd->lex->select_lex; bool need_reopen; + List all_fields; DBUG_ENTER("mysql_update"); LINT_INIT(timestamp_query_id); @@ -226,6 +227,10 @@ int mysql_update(THD *thd, DBUG_RETURN(1); /* purecov: inspected */ } + if (select_lex->inner_refs_list.elements && + fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array)) + DBUG_RETURN(-1); + if (conds) { Item::cond_result cond_value; -- cgit v1.2.1 From ddb5a5636cfac14560d4a15c608b0c6f0e3477cd Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Feb 2007 14:11:01 +0100 Subject: Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it doesn't select. This bug was fixed along with bug #16861: User defined variable can have a wrong value if a tmp table was used. There the fix consisted of Item_func_set_user_var overloading the method Item::save_in_field. Consider the query from the test case: INSERT INTO foo( bar, baz ) SELECT bar, @newBaz := 1 + baz FROM foo WHERE quux <= 0.1; Here the assignment expression '@newBaz := 1 + baz' is represented by an Item_func_set_user_var. Its member method save_in_field, which writes the value of this assignment into the result field, writes the val_xxx() value, which is not updated at this point. In the fix introduced by the patch, the save_in_field method reads the actual variable value instead. See also comment for ChangeSet@1.2368.1.3, 2007-01-09 23:24:56+03:00, evgen@moonbone.local +4 -0 and comment for Item_func_set_user_var::save_in_field (item_func.cc) mysql-test/r/update.result: BUG#24010 The correct, and expected, result mysql-test/t/update.test: BUG#24010 The test case for this bug. When the bug is active, the INSERT ... SELECT statement will try to insert the values <1, 2> which gives an error --- mysql-test/r/update.result | 19 +++++++++++++++++++ mysql-test/t/update.test | 26 ++++++++++++++++++++++++++ 2 files changed, 45 insertions(+) diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 9e2bc52657a..b3c5760e2c9 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -434,3 +434,22 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 21 DROP TABLE t1; +CREATE TABLE t1 ( +a INT(11), +quux decimal( 31, 30 ), +UNIQUE KEY bar (a), +KEY quux (quux) +); +INSERT INTO +t1 ( a, quux ) +VALUES +( 1, 1 ), +( 2, 0.1 ); +INSERT INTO t1( a ) +SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1; +SELECT * FROM t1; +a quux +1 1.000000000000000000000000000000 +2 0.100000000000000000000000000000 +3 NULL +DROP TABLE t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 3ce7ef72670..23ee75d61ea 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -342,3 +342,29 @@ UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999; show status like '%Handler_read%'; DROP TABLE t1; + +# +# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it +# doesn't select +# +CREATE TABLE t1 ( + + a INT(11), + quux decimal( 31, 30 ), + + UNIQUE KEY bar (a), + KEY quux (quux) +); + +INSERT INTO + t1 ( a, quux ) +VALUES + ( 1, 1 ), + ( 2, 0.1 ); + +INSERT INTO t1( a ) + SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1; + +SELECT * FROM t1; + +DROP TABLE t1; -- cgit v1.2.1 From b1e1f77f1ffcbd1f3f9d07d8da598061ea2cb75c Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 24 Feb 2007 23:04:15 +0300 Subject: item.cc: Post fix for bug#23800. The Item_field constructor now increases the select_n_where_fields counter. sql_yacc.yy: Post fix for bug#23800. Take into account fields that might be added by subselects. sql_lex.h: Post fix for bug#23800. Added the select_n_where_fields variable to the st_select_lex class. sql_lex.cc: Post fix for bug#23800. Initialization of the select_n_where_fields variable. sql/sql_lex.cc: Post fix for bug#23800. Initialization of the select_n_where_fields variable. sql/sql_lex.h: Post fix for bug#23800. Added the select_n_where_fields variable to the st_select_lex class. sql/item.cc: Post fix for bug#23800. The Item_field constructor now increases the select_n_where_fields counter. sql/sql_yacc.yy: Post fix for bug#23800. Take into account fields that might be added by subselects. --- sql/item.cc | 3 +++ sql/sql_lex.cc | 2 ++ sql/sql_lex.h | 5 +++++ sql/sql_yacc.yy | 6 ++++++ 4 files changed, 16 insertions(+) diff --git a/sql/item.cc b/sql/item.cc index a57f3c0fa96..9149a1240ea 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1664,7 +1664,10 @@ Item_field::Item_field(Name_resolution_context *context_arg, field(0), result_field(0), item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0), fixed_as_field(0) { + SELECT_LEX *select= current_thd->lex->current_select; collation.set(DERIVATION_IMPLICIT); + if (select && select->parsing_place != IN_HAVING) + select->select_n_where_fields++; } // Constructor need to process subselect with temporary tables (see Item) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c3e18394c41..cd26bfbc6f8 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1151,6 +1151,7 @@ void st_select_lex::init_query() cond_count= between_count= with_wild= 0; conds_processed_with_permanent_arena= 0; ref_pointer_array= 0; + select_n_where_fields= 0; select_n_having_items= 0; subquery_in_having= explicit_limit= 0; is_item_list_lookup= 0; @@ -1550,6 +1551,7 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) (Item **)arena->alloc(sizeof(Item*) * (n_child_sum_items + item_list.elements + select_n_having_items + + select_n_where_fields + order_group_num)*5)) == 0; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f8d8e6efcb2..d2171f745c1 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -530,6 +530,11 @@ public: uint select_n_having_items; uint cond_count; /* number of arguments of and/or/xor in where/having/on */ uint between_count; /* number of between predicates in where/having/on */ + /* + Number of fields used in select list or where clause of current select + and all inner subselects. + */ + uint select_n_where_fields; enum_parsing_place parsing_place; /* where we are parsing expression */ bool with_sum_func; /* sum function indicator */ /* diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d5b3aadbf39..2e9cc662ba8 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9275,6 +9275,12 @@ subselect_end: lex->current_select = lex->current_select->return_after_parsing(); lex->nest_level--; lex->current_select->n_child_sum_items += child->n_sum_items; + /* + A subselect can add fields to an outer select. Reserve space for + them. + */ + lex->current_select->select_n_where_fields+= + child->select_n_where_fields; }; /************************************************************************** -- cgit v1.2.1 From 84e68927195baf4a0f1704745be73f00db1086b0 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 25 Feb 2007 23:02:11 +0300 Subject: item.h: Post fix for bug#23800. Copy the table name of an Item_outer_ref to the conventional memory. sql/item.h: Post fix for bug#23800. Copy the table name of an Item_outer_ref to the conventional memory. --- sql/item.h | 1 + 1 file changed, 1 insertion(+) diff --git a/sql/item.h b/sql/item.h index ec6d4fabae5..93b43c8b61a 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1968,6 +1968,7 @@ public: outer_field_arg->field_name), outer_field(outer_field_arg) { + table_name= my_strdup(outer_field_arg->table_name, MYF(MY_WME)); ref= (Item**)&outer_field; set_properties(); fixed= 0; -- cgit v1.2.1 From 90d4f80922c7f8873ed8c415303f8d7276f5e32d Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 26 Feb 2007 09:01:43 +0300 Subject: item.cc, item.h: Post fix for bug#23800. sql/item.h: Post fix for bug#23800. sql/item.cc: Post fix for bug#23800. --- sql/item.cc | 1 + sql/item.h | 1 - 2 files changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/item.cc b/sql/item.cc index 9149a1240ea..cc41b83f8d9 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5497,6 +5497,7 @@ bool Item_outer_ref::fix_fields(THD *thd, Item **reference) if (!outer_field->fixed && (outer_field->fix_fields(thd, reference))) return TRUE; + table_name= outer_field->table_name; return Item_direct_ref::fix_fields(thd, reference); } diff --git a/sql/item.h b/sql/item.h index 93b43c8b61a..ec6d4fabae5 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1968,7 +1968,6 @@ public: outer_field_arg->field_name), outer_field(outer_field_arg) { - table_name= my_strdup(outer_field_arg->table_name, MYF(MY_WME)); ref= (Item**)&outer_field; set_properties(); fixed= 0; -- cgit v1.2.1 From bab42f674d95186ccc7a3fdb4901fbb0b00a1b96 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 26 Feb 2007 16:52:54 +0400 Subject: after merge fix --- mysql-test/r/insert_select.result | 14 -------------- mysql-test/r/subselect.result | 12 ++++++------ mysql-test/t/insert_select.test | 13 ------------- mysql-test/t/subselect.test | 4 +--- sql/sql_insert.cc | 6 ++++-- 5 files changed, 11 insertions(+), 38 deletions(-) diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 77420890053..18a0ed1a1cb 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -717,20 +717,6 @@ select * from t1; f1 f2 1 2 drop table t1; -create table t1(f1 int primary key auto_increment, f2 int unique); -insert into t1(f2) values(1); -select @@identity; -@@identity -1 -insert ignore t1(f2) values(1); -select @@identity; -@@identity -0 -insert ignore t1(f2) select 1; -select @@identity; -@@identity -0 -drop table t1; CREATE TABLE t1 (f1 INT, f2 INT ); CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT); INSERT INTO t1 VALUES (1,1),(2,2),(10,10); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 7c6657d0960..43abef692e9 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -328,9 +328,9 @@ patient_uq clinic_uq 1 2 2 2 explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 Using where; Using index +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where +2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)) @@ -1739,9 +1739,9 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not((`test`.`t1`.`id`,(((`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))) explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 Using where; Using index +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 93a7a3e5ab8..0f9a0ca4872 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -280,19 +280,6 @@ insert into t1 values (1,1) on duplicate key update f2=2; select * from t1; drop table t1; -# -# Bug#23170: LAST_INSERT_ID isn't reset to 0 in INSERT .. SELECT if no rows -# were inserted. -# -create table t1(f1 int primary key auto_increment, f2 int unique); -insert into t1(f2) values(1); -select @@identity; -insert ignore t1(f2) values(1); -select @@identity; -insert ignore t1(f2) select 1; -select @@identity; -drop table t1; - # # Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index abf6273ad7e..0ce3d1d78c6 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2599,8 +2599,6 @@ SELECT * FROM t1 WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 UNION SELECT c from t2 WHERE c=t1.c); - - DROP TABLE t1,t2,t3; # @@ -2610,7 +2608,7 @@ CREATE TABLE t1 (s1 char(1)); INSERT INTO t1 VALUES ('a'); SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); DROP TABLE t1; -DROP TABLE t1,t2,t3; + # # Bug#23800: Outer fields in correlated subqueries is used in a temporary # table created for sorting. diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 3501675f09f..f939b6371df 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2502,9 +2502,11 @@ select_insert::prepare(List &values, SELECT_LEX_UNIT *u) the INSERT table and the tables in the SELECT part of INSERT ... SELECT. To do that we must concatenate the two lists */ - table_list->next_name_resolution_table= ctx_state.get_first_name_resolution_table(); + table_list->next_name_resolution_table= + ctx_state.get_first_name_resolution_table(); - res= res || setup_fields(thd, 0, *info.update_values, MARK_COLUMNS_READ, 0, 0); + res= res || setup_fields(thd, 0, *info.update_values, + MARK_COLUMNS_READ, 0, 0); if (!res) { /* -- cgit v1.2.1 From 79a6e0fefe427c137c1c62d7b06b109cba5a18c0 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 26 Feb 2007 19:49:24 +0400 Subject: after merge fix --- mysql-test/r/mix2_myisam.result | 2 +- mysql-test/r/update.result | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index 45b4784251a..569eefb34ec 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -1532,7 +1532,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where; Using index explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where; Using index diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 2f8434b6cd2..a40d3451a62 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -432,7 +432,7 @@ Handler_read_key 3 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 -Handler_read_rnd_next 21 +Handler_read_rnd_next 0 DROP TABLE t1; CREATE TABLE t1 ( a INT(11), -- cgit v1.2.1 From 6e133525340b9f550e940a6198a3253865411941 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 27 Feb 2007 12:16:41 +0400 Subject: after merge fix --- mysql-test/r/binlog_row_blackhole.result | 11 +++++++++++ 1 file changed, 11 insertions(+) diff --git a/mysql-test/r/binlog_row_blackhole.result b/mysql-test/r/binlog_row_blackhole.result index a02aea4ea49..f370232e2c3 100644 --- a/mysql-test/r/binlog_row_blackhole.result +++ b/mysql-test/r/binlog_row_blackhole.result @@ -122,6 +122,17 @@ master-bin.000001 # Query 1 # use `test`; alter table t1 add b int master-bin.000001 # Query 1 # use `test`; alter table t1 drop b master-bin.000001 # Query 1 # use `test`; create table t3 like t1 drop table t1,t2,t3; +CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE; +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 ADD INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD UNIQUE INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD PRIMARY KEY(a); +DELETE FROM t1 WHERE a=10; +DROP TABLE t1; reset master; create table t1 (a int) engine=blackhole; set autocommit=0; -- cgit v1.2.1 From 3a520a785eeee973e79d1f6e482e270e24a20c56 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 27 Feb 2007 12:37:17 +0400 Subject: removed unused variable --- sql/sql_select.cc | 1 - 1 file changed, 1 deletion(-) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a20e1f06147..5202f35f4de 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -13534,7 +13534,6 @@ count_field_types(TMP_TABLE_PARAM *param, List &fields, param->quick_group=1; while ((field=li++)) { - Item::Type type=field->type(); Item::Type real_type= field->real_item()->type(); if (real_type == Item::FIELD_ITEM) param->field_count++; -- cgit v1.2.1 From 175507b766c471d7545165857debc0cd55d04422 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 27 Feb 2007 11:27:04 +0200 Subject: Fixes for compiler warnings. include/my_dbug.h: Added macro for fixing compiler warnings. sql/field.cc: Fixed compiler warnings. sql/ha_ndbcluster.cc: Fixed compiler warnings. sql/ha_ndbcluster_binlog.cc: Fixed compiler warnings. Changed some tabs into spaces. sql/rpl_injector.h: Fixed compiler warnings. sql/sql_binlog.cc: Fixed compiler warnings. sql/sql_repl.cc: Fixed compiler warnings. sql/sql_table.cc: Fixed compiler warnings. storage/myisammrg/ha_myisammrg.cc: Fixed compiler warnings. strings/ctype-ucs2.c: Fixed compiler warnings. strings/ctype-utf8.c: Fixed compiler warnings. support-files/compiler_warnings.supp: Added suppressed warnings. --- include/my_dbug.h | 2 + sql/field.cc | 2 + sql/ha_ndbcluster.cc | 33 +++++---- sql/ha_ndbcluster_binlog.cc | 135 ++++++++++++++++++----------------- sql/rpl_injector.h | 2 + sql/sql_binlog.cc | 2 +- sql/sql_repl.cc | 2 + sql/sql_table.cc | 2 +- storage/myisammrg/ha_myisammrg.cc | 4 ++ strings/ctype-ucs2.c | 2 + strings/ctype-utf8.c | 2 + support-files/compiler_warnings.supp | 10 ++- 12 files changed, 117 insertions(+), 81 deletions(-) diff --git a/include/my_dbug.h b/include/my_dbug.h index 0a529cb47de..0541cf00c29 100644 --- a/include/my_dbug.h +++ b/include/my_dbug.h @@ -80,6 +80,7 @@ extern FILE *_db_fp_(void); #define DBUG_ASSERT(A) assert(A) #define DBUG_EXPLAIN(buf,len) _db_explain_(0, (buf),(len)) #define DBUG_EXPLAIN_INITIAL(buf,len) _db_explain_init_((buf),(len)) +#define IF_DBUG(A) A #else /* No debugger */ #define DBUG_ENTER(a1) @@ -106,6 +107,7 @@ extern FILE *_db_fp_(void); #define DBUG_UNLOCK_FILE #define DBUG_EXPLAIN(buf,len) #define DBUG_EXPLAIN_INITIAL(buf,len) +#define IF_DBUG(A) #endif #ifdef __cplusplus } diff --git a/sql/field.cc b/sql/field.cc index 867edc6f9dd..5f1e0541ffb 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -6297,10 +6297,12 @@ int Field_string::cmp(const char *a_ptr, const char *b_ptr) void Field_string::sort_string(char *to,uint length) { +#ifndef DBUG_OFF uint tmp= my_strnxfrm(field_charset, (uchar*) to, length, (uchar*) ptr, field_length); DBUG_ASSERT(tmp == length); +#endif } diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc index 393856e62ba..dac25900b17 100644 --- a/sql/ha_ndbcluster.cc +++ b/sql/ha_ndbcluster.cc @@ -2764,10 +2764,12 @@ int ha_ndbcluster::write_row(byte *record) { Ndb *ndb= get_ndb(); Uint64 next_val= (Uint64) table->next_number_field->val_int() + 1; +#ifndef DBUG_OFF char buff[22]; DBUG_PRINT("info", ("Trying to set next auto increment value to %s", llstr(next_val, buff))); +#endif Ndb_tuple_id_range_guard g(m_share); if (ndb->setAutoIncrementValue(m_table, g.range, next_val, TRUE) == -1) @@ -3999,7 +4001,7 @@ int ha_ndbcluster::end_bulk_insert() } else { - int res= trans->restart(); + IF_DBUG(int res=) trans->restart(); DBUG_ASSERT(res == 0); } } @@ -4717,7 +4719,7 @@ static int create_ndb_column(NDBCOL &col, // Set autoincrement if (field->flags & AUTO_INCREMENT_FLAG) { - char buff[22]; + IF_DBUG(char buff[22]); col.setAutoIncrement(TRUE); ulonglong value= info->auto_increment_value ? info->auto_increment_value : (ulonglong) 1; @@ -5388,7 +5390,7 @@ int ha_ndbcluster::rename_table(const char *from, const char *to) { DBUG_PRINT("NDB_SHARE", ("%s temporary use_count: %u", share->key, share->use_count)); - int r= rename_share(share, to); + IF_DBUG(int r=) rename_share(share, to); DBUG_ASSERT(r == 0); } #endif @@ -5409,7 +5411,7 @@ int ha_ndbcluster::rename_table(const char *from, const char *to) #ifdef HAVE_NDB_BINLOG if (share) { - int r= rename_share(share, from); + IF_DBUG(int r=) rename_share(share, from); DBUG_ASSERT(r == 0); /* ndb_share reference temporary free */ DBUG_PRINT("NDB_SHARE", ("%s temporary free use_count: %u", @@ -7268,7 +7270,7 @@ uint ndb_get_commitcount(THD *thd, char *dbname, char *tabname, if (share->commit_count != 0) { *commit_count= share->commit_count; - char buff[22]; + IF_DBUG(char buff[22]); DBUG_PRINT("info", ("Getting commit_count: %s from share", llstr(share->commit_count, buff))); pthread_mutex_unlock(&share->mutex); @@ -7304,7 +7306,7 @@ uint ndb_get_commitcount(THD *thd, char *dbname, char *tabname, pthread_mutex_lock(&share->mutex); if (share->commit_count_lock == lock) { - char buff[22]; + IF_DBUG(char buff[22]); DBUG_PRINT("info", ("Setting commit_count to %s", llstr(stat.commit_count, buff))); share->commit_count= stat.commit_count; @@ -7363,7 +7365,8 @@ ndbcluster_cache_retrieval_allowed(THD *thd, bool is_autocommit= !(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)); char *dbname= full_name; char *tabname= dbname+strlen(dbname)+1; - char buff[22], buff2[22]; + IF_DBUG(char buff[22]); + IF_DBUG(char buff2[22]); DBUG_ENTER("ndbcluster_cache_retrieval_allowed"); DBUG_PRINT("enter", ("dbname: %s, tabname: %s, is_autocommit: %d", dbname, tabname, is_autocommit)); @@ -7430,7 +7433,7 @@ ha_ndbcluster::register_query_cache_table(THD *thd, ulonglong *engine_data) { Uint64 commit_count; - char buff[22]; + IF_DBUG(char buff[22]); bool is_autocommit= !(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)); DBUG_ENTER("ha_ndbcluster::register_query_cache_table"); DBUG_PRINT("enter",("dbname: %s, tabname: %s, is_autocommit: %d", @@ -7875,7 +7878,10 @@ ndb_get_table_statistics(ha_ndbcluster* file, bool report_error, Ndb* ndb, const int retries= 10; int reterr= 0; int retry_sleep= 30 * 1000; /* 30 milliseconds */ - char buff[22], buff2[22], buff3[22], buff4[22]; + IF_DBUG(char buff[22]); + IF_DBUG(char buff2[22]); + IF_DBUG(char buff3[22]); + IF_DBUG(char buff4[22]); DBUG_ENTER("ndb_get_table_statistics"); DBUG_PRINT("enter", ("table: %s", ndbtab->getName())); @@ -8693,7 +8699,8 @@ pthread_handler_t ndb_util_thread_func(void *arg __attribute__((unused))) ndb_get_table_statistics(NULL, FALSE, ndb, ndbtab_g.get_table(), &stat) == 0) { - char buff[22], buff2[22]; + IF_DBUG(char buff[22]); + IF_DBUG(char buff2[22]); DBUG_PRINT("info", ("Table: %s commit_count: %s rows: %s", share->key, @@ -9545,7 +9552,7 @@ void ndb_serialize_cond(const Item *item, void *arg) DBUG_PRINT("info", ("INT_ITEM")); if (context->expecting(Item::INT_ITEM)) { - Item_int *int_item= (Item_int *) item; + IF_DBUG(Item_int *int_item= (Item_int *) item); DBUG_PRINT("info", ("value %ld", (long) int_item->value)); NDB_ITEM_QUALIFICATION q; q.value_type= Item::INT_ITEM; @@ -9572,7 +9579,7 @@ void ndb_serialize_cond(const Item *item, void *arg) DBUG_PRINT("info", ("REAL_ITEM")); if (context->expecting(Item::REAL_ITEM)) { - Item_float *float_item= (Item_float *) item; + IF_DBUG(Item_float *float_item= (Item_float *) item); DBUG_PRINT("info", ("value %f", float_item->value)); NDB_ITEM_QUALIFICATION q; q.value_type= Item::REAL_ITEM; @@ -9620,7 +9627,7 @@ void ndb_serialize_cond(const Item *item, void *arg) DBUG_PRINT("info", ("DECIMAL_ITEM")); if (context->expecting(Item::DECIMAL_ITEM)) { - Item_decimal *decimal_item= (Item_decimal *) item; + IF_DBUG(Item_decimal *decimal_item= (Item_decimal *) item); DBUG_PRINT("info", ("value %f", decimal_item->val_real())); NDB_ITEM_QUALIFICATION q; q.value_type= Item::DECIMAL_ITEM; diff --git a/sql/ha_ndbcluster_binlog.cc b/sql/ha_ndbcluster_binlog.cc index ea5a2deaeb3..dc2bea0c6f4 100644 --- a/sql/ha_ndbcluster_binlog.cc +++ b/sql/ha_ndbcluster_binlog.cc @@ -1829,15 +1829,15 @@ ndb_binlog_thread_handle_schema_event(THD *thd, Ndb *ndb, // fall through case SOT_CREATE_TABLE: pthread_mutex_lock(&LOCK_open); - if (ndbcluster_check_if_local_table(schema->db, schema->name)) - { - DBUG_PRINT("info", ("NDB binlog: Skipping locally defined table '%s.%s'", - schema->db, schema->name)); + if (ndbcluster_check_if_local_table(schema->db, schema->name)) + { + DBUG_PRINT("info", ("NDB binlog: Skipping locally defined table '%s.%s'", + schema->db, schema->name)); sql_print_error("NDB binlog: Skipping locally defined table '%s.%s' from " "binlog schema event '%s' from node %d. ", schema->db, schema->name, schema->query, schema->node_id); - } + } else if (ndb_create_table_from_engine(thd, schema->db, schema->name)) { sql_print_error("NDB binlog: Could not discover table '%s.%s' from " @@ -1854,27 +1854,27 @@ ndb_binlog_thread_handle_schema_event(THD *thd, Ndb *ndb, log_query= 1; break; case SOT_DROP_DB: - /* Drop the database locally if it only contains ndb tables */ - if (! ndbcluster_check_if_local_tables_in_db(thd, schema->db)) - { - run_query(thd, schema->query, - schema->query + schema->query_length, - TRUE, /* print error */ - TRUE); /* don't binlog the query */ - /* binlog dropping database after any table operations */ - post_epoch_log_list->push_back(schema, mem_root); - /* acknowledge this query _after_ epoch completion */ - post_epoch_unlock= 1; - } - else - { - /* Database contained local tables, leave it */ - sql_print_error("NDB binlog: Skipping drop database '%s' since it contained local tables " + /* Drop the database locally if it only contains ndb tables */ + if (! ndbcluster_check_if_local_tables_in_db(thd, schema->db)) + { + run_query(thd, schema->query, + schema->query + schema->query_length, + TRUE, /* print error */ + TRUE); /* don't binlog the query */ + /* binlog dropping database after any table operations */ + post_epoch_log_list->push_back(schema, mem_root); + /* acknowledge this query _after_ epoch completion */ + post_epoch_unlock= 1; + } + else + { + /* Database contained local tables, leave it */ + sql_print_error("NDB binlog: Skipping drop database '%s' since it contained local tables " "binlog schema event '%s' from node %d. ", schema->db, schema->query, schema->node_id); - log_query= 1; - } + log_query= 1; + } break; case SOT_CREATE_DB: /* fall through */ @@ -2121,18 +2121,18 @@ ndb_binlog_thread_handle_schema_event_post_epoch(THD *thd, share= 0; } pthread_mutex_lock(&LOCK_open); - if (ndbcluster_check_if_local_table(schema->db, schema->name)) - { - DBUG_PRINT("info", ("NDB binlog: Skipping locally defined table '%s.%s'", - schema->db, schema->name)); + if (ndbcluster_check_if_local_table(schema->db, schema->name)) + { + DBUG_PRINT("info", ("NDB binlog: Skipping locally defined table '%s.%s'", + schema->db, schema->name)); sql_print_error("NDB binlog: Skipping locally defined table '%s.%s' from " "binlog schema event '%s' from node %d. ", schema->db, schema->name, schema->query, schema->node_id); - } + } else if (ndb_create_table_from_engine(thd, schema->db, schema->name)) - { - sql_print_error("NDB binlog: Could not discover table '%s.%s' from " + { + sql_print_error("NDB binlog: Could not discover table '%s.%s' from " "binlog schema event '%s' from node %d. my_errno: %d", schema->db, schema->name, schema->query, schema->node_id, my_errno); @@ -2260,7 +2260,7 @@ int ndb_add_ndb_binlog_index(THD *thd, void *_row) { TABLE_LIST *p_binlog_tables= &binlog_tables; close_tables_for_reopen(thd, &p_binlog_tables); - ndb_binlog_index= 0; + ndb_binlog_index= 0; continue; } sql_print_error("NDB Binlog: Unable to lock table ndb_binlog_index"); @@ -3225,15 +3225,17 @@ ndb_binlog_thread_handle_data_event(Ndb *ndb, NdbEventOperation *pOp, if (share->flags & NSF_BLOB_FLAG) { my_ptrdiff_t ptrdiff= 0; - int ret= get_ndb_blobs_value(table, share->ndb_value[0], - blobs_buffer[0], blobs_buffer_size[0], - ptrdiff); + IF_DBUG(int ret =) get_ndb_blobs_value(table, share->ndb_value[0], + blobs_buffer[0], + blobs_buffer_size[0], + ptrdiff); DBUG_ASSERT(ret == 0); } ndb_unpack_record(table, share->ndb_value[0], &b, table->record[0]); - int ret= trans.write_row(::server_id, - injector::transaction::table(table, TRUE), - &b, n_fields, table->record[0]); + IF_DBUG(int ret=) trans.write_row(::server_id, + injector::transaction::table(table, + TRUE), + &b, n_fields, table->record[0]); DBUG_ASSERT(ret == 0); } break; @@ -3251,27 +3253,29 @@ ndb_binlog_thread_handle_data_event(Ndb *ndb, NdbEventOperation *pOp, n= 0; /* use the primary key only as it save time and space and it is the only thing needed to log the delete - */ + */ else n= 1; /* we use the before values since we don't have a primary key since the mysql server does not handle the hidden primary key - */ + */ if (share->flags & NSF_BLOB_FLAG) { my_ptrdiff_t ptrdiff= table->record[n] - table->record[0]; - int ret= get_ndb_blobs_value(table, share->ndb_value[n], - blobs_buffer[n], blobs_buffer_size[n], - ptrdiff); + IF_DBUG(int ret =) get_ndb_blobs_value(table, share->ndb_value[n], + blobs_buffer[n], + blobs_buffer_size[n], + ptrdiff); DBUG_ASSERT(ret == 0); } ndb_unpack_record(table, share->ndb_value[n], &b, table->record[n]); DBUG_EXECUTE("info", print_records(table, table->record[n]);); - int ret= trans.delete_row(::server_id, - injector::transaction::table(table, TRUE), - &b, n_fields, table->record[n]); + IF_DBUG(int ret =) trans.delete_row(::server_id, + injector::transaction::table(table, + TRUE), + &b, n_fields, table->record[n]); DBUG_ASSERT(ret == 0); } break; @@ -3283,9 +3287,10 @@ ndb_binlog_thread_handle_data_event(Ndb *ndb, NdbEventOperation *pOp, if (share->flags & NSF_BLOB_FLAG) { my_ptrdiff_t ptrdiff= 0; - int ret= get_ndb_blobs_value(table, share->ndb_value[0], - blobs_buffer[0], blobs_buffer_size[0], - ptrdiff); + IF_DBUG(int ret =) get_ndb_blobs_value(table, share->ndb_value[0], + blobs_buffer[0], + blobs_buffer_size[0], + ptrdiff); DBUG_ASSERT(ret == 0); } ndb_unpack_record(table, share->ndb_value[0], @@ -3296,7 +3301,7 @@ ndb_binlog_thread_handle_data_event(Ndb *ndb, NdbEventOperation *pOp, /* since table has a primary key, we can do a write using only after values - */ + */ trans.write_row(::server_id, injector::transaction::table(table, TRUE), &b, n_fields, table->record[0]);// after values } @@ -3305,22 +3310,24 @@ ndb_binlog_thread_handle_data_event(Ndb *ndb, NdbEventOperation *pOp, /* mysql server cannot handle the ndb hidden key and therefore needs the before image as well - */ + */ if (share->flags & NSF_BLOB_FLAG) { my_ptrdiff_t ptrdiff= table->record[1] - table->record[0]; - int ret= get_ndb_blobs_value(table, share->ndb_value[1], - blobs_buffer[1], blobs_buffer_size[1], - ptrdiff); + IF_DBUG(int ret =) get_ndb_blobs_value(table, share->ndb_value[1], + blobs_buffer[1], + blobs_buffer_size[1], + ptrdiff); DBUG_ASSERT(ret == 0); } ndb_unpack_record(table, share->ndb_value[1], &b, table->record[1]); DBUG_EXECUTE("info", print_records(table, table->record[1]);); - int ret= trans.update_row(::server_id, - injector::transaction::table(table, TRUE), - &b, n_fields, - table->record[1], // before values - table->record[0]);// after values + IF_DBUG(int ret =) trans.update_row(::server_id, + injector::transaction::table(table, + TRUE), + &b, n_fields, + table->record[1], // before values + table->record[0]);// after values DBUG_ASSERT(ret == 0); } } @@ -3850,7 +3857,7 @@ restart: continue; } TABLE *table= share->table; - const LEX_STRING &name= table->s->table_name; + IF_DBUG(const LEX_STRING &name= table->s->table_name); if ((event_types & (NdbDictionary::Event::TE_INSERT | NdbDictionary::Event::TE_UPDATE | NdbDictionary::Event::TE_DELETE)) == 0) @@ -3867,7 +3874,7 @@ restart: } DBUG_PRINT("info", ("use_table: %.*s", name.length, name.str)); injector::transaction::table tbl(table, TRUE); - int ret= trans.use_table(::server_id, tbl); + IF_DBUG(int ret=) trans.use_table(::server_id, tbl); DBUG_ASSERT(ret == 0); } } @@ -3877,10 +3884,10 @@ restart: { TABLE *table= ndb_apply_status_share->table; - const LEX_STRING& name=table->s->table_name; + IF_DBUG(const LEX_STRING& name= table->s->table_name); DBUG_PRINT("info", ("use_table: %.*s", name.length, name.str)); injector::transaction::table tbl(table, TRUE); - int ret= trans.use_table(::server_id, tbl); + IF_DBUG(int ret=) trans.use_table(::server_id, tbl); DBUG_ASSERT(ret == 0); // Set all fields non-null. @@ -3945,7 +3952,7 @@ restart: else { // set injector_ndb database/schema from table internal name - int ret= + IF_DBUG(int ret=) i_ndb->setDatabaseAndSchemaName(pOp->getEvent()->getTable()); DBUG_ASSERT(ret == 0); ndb_binlog_thread_handle_non_data_event(thd, i_ndb, pOp, row); @@ -3979,7 +3986,7 @@ restart: /* note! pOp is not referring to an event in the next epoch or is == 0 - */ + */ #ifdef RUN_NDB_BINLOG_TIMER write_timer.stop(); #endif diff --git a/sql/rpl_injector.h b/sql/rpl_injector.h index 8b08c0672c9..61c2e0ecebc 100644 --- a/sql/rpl_injector.h +++ b/sql/rpl_injector.h @@ -284,12 +284,14 @@ public: */ int check_state(enum_state const target_state) { +#ifndef DBUG_OFF static char const *state_name[] = { "START_STATE", "TABLE_STATE", "ROW_STATE", "STATE_COUNT" }; DBUG_ASSERT(0 <= target_state && target_state <= STATE_COUNT); DBUG_PRINT("info", ("In state %s", state_name[m_state])); +#endif if (m_state <= target_state && target_state <= m_state + 1 && m_state < STATE_COUNT) diff --git a/sql/sql_binlog.cc b/sql/sql_binlog.cc index d8f12375258..b0a54bec664 100644 --- a/sql/sql_binlog.cc +++ b/sql/sql_binlog.cc @@ -163,7 +163,7 @@ void mysql_client_binlog_statement(THD* thd) (ulong) uint4korr(bufptr+EVENT_LEN_OFFSET))); #endif ev->thd= thd; - if (int err= ev->exec_event(thd->rli_fake)) + if (IF_DBUG(int err= ) ev->exec_event(thd->rli_fake)) { DBUG_PRINT("error", ("exec_event() returned: %d", err)); /* diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 17163fb1940..1a8446a86e9 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -23,7 +23,9 @@ int max_binlog_dump_events = 0; // unlimited my_bool opt_sporadic_binlog_dump_fail = 0; +#ifndef DBUG_OFF static int binlog_dump_count = 0; +#endif /* fake_rotate_event() builds a fake (=which does not exist physically in any diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 36f0acd8f67..da953060d04 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4818,7 +4818,7 @@ bool mysql_create_like_table(THD* thd, TABLE_LIST* table, else unlock_dst_table= TRUE; - int result= store_create_info(thd, table, &query, create_info); + IF_DBUG(int result=)store_create_info(thd, table, &query, create_info); DBUG_ASSERT(result == 0); // store_create_info() always return 0 write_bin_log(thd, TRUE, query.ptr(), query.length()); diff --git a/storage/myisammrg/ha_myisammrg.cc b/storage/myisammrg/ha_myisammrg.cc index 4392a456f60..ae7c6703b52 100644 --- a/storage/myisammrg/ha_myisammrg.cc +++ b/storage/myisammrg/ha_myisammrg.cc @@ -460,6 +460,8 @@ void ha_myisammrg::update_create_info(HA_CREATE_INFO *create_info) { TABLE_LIST *ptr; LEX_STRING db, name; + db.length= 0; + db.str= 0; if (!(ptr = (TABLE_LIST *) thd->calloc(sizeof(TABLE_LIST)))) goto err; @@ -570,6 +572,8 @@ void ha_myisammrg::append_create_info(String *packet) open_table++) { LEX_STRING db, name; + db.length= 0; + db.str= 0; split_file_name(open_table->table->filename, &db, &name); if (open_table != first) packet->append(','); diff --git a/strings/ctype-ucs2.c b/strings/ctype-ucs2.c index e09b653fd62..d3b65aa1643 100644 --- a/strings/ctype-ucs2.c +++ b/strings/ctype-ucs2.c @@ -322,6 +322,7 @@ static int my_strncasecmp_ucs2(CHARSET_INFO *cs, const char *te=t+len; MY_UNICASE_INFO **uni_plane= cs->caseinfo; LINT_INIT(s_wc); + LINT_INIT(t_wc); while ( s < se && t < te ) { @@ -1385,6 +1386,7 @@ int my_strnncoll_ucs2_bin(CHARSET_INFO *cs, const uchar *se=s+slen; const uchar *te=t+tlen; LINT_INIT(s_wc); + LINT_INIT(t_wc); while ( s < se && t < te ) { diff --git a/strings/ctype-utf8.c b/strings/ctype-utf8.c index 827c274e84a..0536d445533 100644 --- a/strings/ctype-utf8.c +++ b/strings/ctype-utf8.c @@ -2313,6 +2313,7 @@ static int my_strnncoll_utf8(CHARSET_INFO *cs, const uchar *te=t+tlen; MY_UNICASE_INFO **uni_plane= cs->caseinfo; LINT_INIT(s_wc); + LINT_INIT(t_wc); while ( s < se && t < te ) { @@ -2383,6 +2384,7 @@ static int my_strnncollsp_utf8(CHARSET_INFO *cs, const uchar *se= s+slen, *te= t+tlen; MY_UNICASE_INFO **uni_plane= cs->caseinfo; LINT_INIT(s_wc); + LINT_INIT(t_wc); #ifndef VARCHAR_WITH_DIFF_ENDSPACE_ARE_DIFFERENT_FOR_UNIQUE diff_if_only_endspace_difference= 0; diff --git a/support-files/compiler_warnings.supp b/support-files/compiler_warnings.supp index f81d3885731..2f4dcddf737 100644 --- a/support-files/compiler_warnings.supp +++ b/support-files/compiler_warnings.supp @@ -33,17 +33,23 @@ db_vrfy.c : .*comparison is always false due to limited range of data type.* # Ignore all conversion warnings on windows 64 # (Is safe as we are not yet supporting strings >= 2G) # -.* : conversion from 'size_t' to .*int'.* +.* : conversion from '.*size_t' to .*int'.* .* : conversion from '__int64' to .*int'.* +.* : conversion from '__int64' to uint8'.* +.* : conversion from '__int64' to uint32'.* +.* : conversion from '.*size_t' to 'TaoCrypt::word32'.* +.* : conversion from '.*size_t' to 'u.*long'.* # # The following should be fixed by the ndb team # .*/ndb/.* : .*used uninitialized in this function.* +.*/ndb/.* : .*unused variable.* +.*/ndb/.* : .*defined but not used.* # # Unexplanable (?) stuff # listener.cc : .*conversion from 'SOCKET' to 'int'.* -net_serv.c : .*conversion from 'SOCKET' to 'int'.* +net_serv.cc : .*conversion from 'SOCKET' to 'int'.* mi_packrec.c : .*result of 32-bit shift implicitly converted to 64 bits.* : 567 -- cgit v1.2.1 From 3956f5911e86bb9610f502832f5103530053e7ec Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 27 Feb 2007 19:31:49 +0200 Subject: Remove compiler warnings mysql-test/mysql-test-run.pl: Fix warning when using --extern sql/field.cc: Fix wrong fix sql/ha_ndbcluster.cc: Better fixes to remove compiler warnings sql/ha_ndbcluster_binlog.cc: Better fixes to remove compiler warnings sql/log.cc: Fix compiler warnings sql/sql_parse.cc: Indentation fix sql/sql_table.cc: Indentation fixes storage/myisammrg/ha_myisammrg.cc: Fix compiler warnings storage/ndb/src/ndbapi/NdbEventOperationImpl.cpp: Fix compiler warnings support-files/compiler_warnings.supp: Suppress all 'safe' warnings, as detected by win64 win/README: Fixed typo --- mysql-test/mysql-test-run.pl | 30 +++++++-------- server-tools/instance-manager/user_map.cc | 8 ++-- sql/field.cc | 8 ++-- sql/ha_ndbcluster.cc | 48 ++++++++++++++---------- sql/ha_ndbcluster_binlog.cc | 8 +++- sql/log.cc | 2 +- sql/sql_parse.cc | 4 +- sql/sql_table.cc | 8 ++-- storage/myisammrg/ha_myisammrg.cc | 7 ++-- storage/ndb/src/ndbapi/NdbEventOperationImpl.cpp | 3 +- support-files/compiler_warnings.supp | 17 +++++++-- win/README | 2 +- 12 files changed, 85 insertions(+), 60 deletions(-) diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 33836837d53..c105a69e861 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -409,24 +409,24 @@ sub main () { { next if $test->{skip}; - $need_ndbcluster||= $test->{ndb_test}; - $need_im||= $test->{component_id} eq 'im'; - - # Count max number of slaves used by a test case - if ( $test->{slave_num} > $max_slave_num) + if (!$opt_extern) { - $max_slave_num= $test->{slave_num}; - mtr_error("Too many slaves") if $max_slave_num > 3; - } + $need_ndbcluster||= $test->{ndb_test}; + $need_im||= $test->{component_id} eq 'im'; - # Count max number of masters used by a test case - if ( $test->{master_num} > $max_master_num) - { - $max_master_num= $test->{master_num}; - mtr_error("Too many masters") if $max_master_num > 2; - mtr_error("Too few masters") if $max_master_num < 1; - } + # Count max number of slaves used by a test case + if ( $test->{slave_num} > $max_slave_num) { + $max_slave_num= $test->{slave_num}; + mtr_error("Too many slaves") if $max_slave_num > 3; + } + # Count max number of masters used by a test case + if ( $test->{master_num} > $max_master_num) { + $max_master_num= $test->{master_num}; + mtr_error("Too many masters") if $max_master_num > 2; + mtr_error("Too few masters") if $max_master_num < 1; + } + } $use_innodb||= $test->{'innodb_test'}; } diff --git a/server-tools/instance-manager/user_map.cc b/server-tools/instance-manager/user_map.cc index 48cce142db6..832a8cee6b9 100644 --- a/server-tools/instance-manager/user_map.cc +++ b/server-tools/instance-manager/user_map.cc @@ -24,8 +24,8 @@ User::User(const LEX_STRING *user_name_arg, const char *password) { - user_length= strmake(user, user_name_arg->str, USERNAME_LENGTH + 1) - user; - + user_length= (uint8) (strmake(user, user_name_arg->str, + USERNAME_LENGTH + 1) - user); set_password(password); } @@ -59,7 +59,7 @@ int User::init(const char *line) password= name_end + 1; } - user_length= name_end - name_begin; + user_length= (uint8) (name_end - name_begin); if (user_length > USERNAME_LENGTH) { log_error("User name is too long (%d). Max length: %d. " @@ -70,7 +70,7 @@ int User::init(const char *line) return 1; } - password_length= strlen(password); + password_length= (int) strlen(password); if (password_length > SCRAMBLED_PASSWORD_CHAR_LENGTH) { log_error("Password is too long (%d). Max length: %d." diff --git a/sql/field.cc b/sql/field.cc index 5f1e0541ffb..5d4dbe9a416 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -6297,12 +6297,10 @@ int Field_string::cmp(const char *a_ptr, const char *b_ptr) void Field_string::sort_string(char *to,uint length) { -#ifndef DBUG_OFF - uint tmp= my_strnxfrm(field_charset, - (uchar*) to, length, - (uchar*) ptr, field_length); + IF_DBUG(uint tmp=) my_strnxfrm(field_charset, + (uchar*) to, length, + (uchar*) ptr, field_length); DBUG_ASSERT(tmp == length); -#endif } diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc index dac25900b17..9c3959f3feb 100644 --- a/sql/ha_ndbcluster.cc +++ b/sql/ha_ndbcluster.cc @@ -4719,7 +4719,9 @@ static int create_ndb_column(NDBCOL &col, // Set autoincrement if (field->flags & AUTO_INCREMENT_FLAG) { - IF_DBUG(char buff[22]); +#ifndef DBUG_OFF + char buff[22]; +#endif col.setAutoIncrement(TRUE); ulonglong value= info->auto_increment_value ? info->auto_increment_value : (ulonglong) 1; @@ -5411,8 +5413,8 @@ int ha_ndbcluster::rename_table(const char *from, const char *to) #ifdef HAVE_NDB_BINLOG if (share) { - IF_DBUG(int r=) rename_share(share, from); - DBUG_ASSERT(r == 0); + IF_DBUG(int ret=) rename_share(share, from); + DBUG_ASSERT(ret == 0); /* ndb_share reference temporary free */ DBUG_PRINT("NDB_SHARE", ("%s temporary free use_count: %u", share->key, share->use_count)); @@ -7270,7 +7272,9 @@ uint ndb_get_commitcount(THD *thd, char *dbname, char *tabname, if (share->commit_count != 0) { *commit_count= share->commit_count; - IF_DBUG(char buff[22]); +#ifndef DBUG_OFF + char buff[22]; +#endif DBUG_PRINT("info", ("Getting commit_count: %s from share", llstr(share->commit_count, buff))); pthread_mutex_unlock(&share->mutex); @@ -7306,7 +7310,9 @@ uint ndb_get_commitcount(THD *thd, char *dbname, char *tabname, pthread_mutex_lock(&share->mutex); if (share->commit_count_lock == lock) { - IF_DBUG(char buff[22]); +#ifndef DBUG_OFF + char buff[22]; +#endif DBUG_PRINT("info", ("Setting commit_count to %s", llstr(stat.commit_count, buff))); share->commit_count= stat.commit_count; @@ -7365,8 +7371,9 @@ ndbcluster_cache_retrieval_allowed(THD *thd, bool is_autocommit= !(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)); char *dbname= full_name; char *tabname= dbname+strlen(dbname)+1; - IF_DBUG(char buff[22]); - IF_DBUG(char buff2[22]); +#ifndef DBUG_OFF + char buff[22], buff2[22]; +#endif DBUG_ENTER("ndbcluster_cache_retrieval_allowed"); DBUG_PRINT("enter", ("dbname: %s, tabname: %s, is_autocommit: %d", dbname, tabname, is_autocommit)); @@ -7433,7 +7440,9 @@ ha_ndbcluster::register_query_cache_table(THD *thd, ulonglong *engine_data) { Uint64 commit_count; - IF_DBUG(char buff[22]); +#ifndef DBUG_OFF + char buff[22]; +#endif bool is_autocommit= !(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)); DBUG_ENTER("ha_ndbcluster::register_query_cache_table"); DBUG_PRINT("enter",("dbname: %s, tabname: %s, is_autocommit: %d", @@ -7878,10 +7887,9 @@ ndb_get_table_statistics(ha_ndbcluster* file, bool report_error, Ndb* ndb, const int retries= 10; int reterr= 0; int retry_sleep= 30 * 1000; /* 30 milliseconds */ - IF_DBUG(char buff[22]); - IF_DBUG(char buff2[22]); - IF_DBUG(char buff3[22]); - IF_DBUG(char buff4[22]); +#ifndef DBUG_OFF + char buff[22], buff2[22], buff3[22], buff4[22]; +#endif DBUG_ENTER("ndb_get_table_statistics"); DBUG_PRINT("enter", ("table: %s", ndbtab->getName())); @@ -8699,8 +8707,9 @@ pthread_handler_t ndb_util_thread_func(void *arg __attribute__((unused))) ndb_get_table_statistics(NULL, FALSE, ndb, ndbtab_g.get_table(), &stat) == 0) { - IF_DBUG(char buff[22]); - IF_DBUG(char buff2[22]); +#ifndef DBUG_OFF + char buff[22], buff2[22]; +#endif DBUG_PRINT("info", ("Table: %s commit_count: %s rows: %s", share->key, @@ -9552,8 +9561,8 @@ void ndb_serialize_cond(const Item *item, void *arg) DBUG_PRINT("info", ("INT_ITEM")); if (context->expecting(Item::INT_ITEM)) { - IF_DBUG(Item_int *int_item= (Item_int *) item); - DBUG_PRINT("info", ("value %ld", (long) int_item->value)); + DBUG_PRINT("info", ("value %ld", + (long) ((Item_int*) item)->value)); NDB_ITEM_QUALIFICATION q; q.value_type= Item::INT_ITEM; curr_cond->ndb_item= new Ndb_item(NDB_VALUE, q, item); @@ -9579,8 +9588,7 @@ void ndb_serialize_cond(const Item *item, void *arg) DBUG_PRINT("info", ("REAL_ITEM")); if (context->expecting(Item::REAL_ITEM)) { - IF_DBUG(Item_float *float_item= (Item_float *) item); - DBUG_PRINT("info", ("value %f", float_item->value)); + DBUG_PRINT("info", ("value %f", ((Item_float*) item)->value)); NDB_ITEM_QUALIFICATION q; q.value_type= Item::REAL_ITEM; curr_cond->ndb_item= new Ndb_item(NDB_VALUE, q, item); @@ -9627,8 +9635,8 @@ void ndb_serialize_cond(const Item *item, void *arg) DBUG_PRINT("info", ("DECIMAL_ITEM")); if (context->expecting(Item::DECIMAL_ITEM)) { - IF_DBUG(Item_decimal *decimal_item= (Item_decimal *) item); - DBUG_PRINT("info", ("value %f", decimal_item->val_real())); + DBUG_PRINT("info", ("value %f", + ((Item_decimal*) item)->val_real())); NDB_ITEM_QUALIFICATION q; q.value_type= Item::DECIMAL_ITEM; curr_cond->ndb_item= new Ndb_item(NDB_VALUE, q, item); diff --git a/sql/ha_ndbcluster_binlog.cc b/sql/ha_ndbcluster_binlog.cc index dc2bea0c6f4..73363328078 100644 --- a/sql/ha_ndbcluster_binlog.cc +++ b/sql/ha_ndbcluster_binlog.cc @@ -3857,7 +3857,9 @@ restart: continue; } TABLE *table= share->table; - IF_DBUG(const LEX_STRING &name= table->s->table_name); +#ifndef DBUG_OFF + const LEX_STRING &name= table->s->table_name; +#endif if ((event_types & (NdbDictionary::Event::TE_INSERT | NdbDictionary::Event::TE_UPDATE | NdbDictionary::Event::TE_DELETE)) == 0) @@ -3884,8 +3886,10 @@ restart: { TABLE *table= ndb_apply_status_share->table; - IF_DBUG(const LEX_STRING& name= table->s->table_name); +#ifndef DBUG_OFF + const LEX_STRING& name= table->s->table_name; DBUG_PRINT("info", ("use_table: %.*s", name.length, name.str)); +#endif injector::transaction::table tbl(table, TRUE); IF_DBUG(int ret=) trans.use_table(::server_id, tbl); DBUG_ASSERT(ret == 0); diff --git a/sql/log.cc b/sql/log.cc index 59fa08a4217..e25f008e90c 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -1742,7 +1742,7 @@ void setup_windows_event_source() /* Register EventMessageFile */ dwError = RegSetValueEx(hRegKey, "EventMessageFile", 0, REG_EXPAND_SZ, - (PBYTE) szPath, strlen(szPath)+1); + (PBYTE) szPath, (DWORD) (strlen(szPath) + 1)); /* Register supported event types */ dwTypes= (EVENTLOG_ERROR_TYPE | EVENTLOG_WARNING_TYPE | diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index ce7f34e3a06..11eb510d6c8 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3268,7 +3268,9 @@ end_with_restore_list: We WANT to write and we CAN write. ! we write after unlocking the table. */ - /* Presumably, RESET and binlog writing doesn't require synchronization */ + /* + Presumably, RESET and binlog writing doesn't require synchronization + */ if (!lex->no_write_to_binlog && write_to_binlog) { if (mysql_bin_log.is_open()) diff --git a/sql/sql_table.cc b/sql/sql_table.cc index da953060d04..0697fdd79b4 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -41,7 +41,7 @@ static int copy_data_between_tables(TABLE *from,TABLE *to, static bool prepare_blob_field(THD *thd, create_field *sql_field); static bool check_engine(THD *thd, const char *table_name, - HA_CREATE_INFO *create_info); + HA_CREATE_INFO *create_info); static int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, List *fields, List *keys, bool tmp_table, @@ -4818,7 +4818,8 @@ bool mysql_create_like_table(THD* thd, TABLE_LIST* table, else unlock_dst_table= TRUE; - IF_DBUG(int result=)store_create_info(thd, table, &query, create_info); + IF_DBUG(int result=) store_create_info(thd, table, &query, + create_info); DBUG_ASSERT(result == 0); // store_create_info() always return 0 write_bin_log(thd, TRUE, query.ptr(), query.length()); @@ -6645,7 +6646,8 @@ view_err: thd->query, thd->query_length, db, table_name); - DBUG_ASSERT(!(mysql_bin_log.is_open() && thd->current_stmt_binlog_row_based && + DBUG_ASSERT(!(mysql_bin_log.is_open() && + thd->current_stmt_binlog_row_based && (create_info->options & HA_LEX_CREATE_TMP_TABLE))); write_bin_log(thd, TRUE, thd->query, thd->query_length); diff --git a/storage/myisammrg/ha_myisammrg.cc b/storage/myisammrg/ha_myisammrg.cc index ae7c6703b52..d9e7e1d5700 100644 --- a/storage/myisammrg/ha_myisammrg.cc +++ b/storage/myisammrg/ha_myisammrg.cc @@ -460,8 +460,7 @@ void ha_myisammrg::update_create_info(HA_CREATE_INFO *create_info) { TABLE_LIST *ptr; LEX_STRING db, name; - db.length= 0; - db.str= 0; + LINT_INIT(db.str); if (!(ptr = (TABLE_LIST *) thd->calloc(sizeof(TABLE_LIST)))) goto err; @@ -572,8 +571,8 @@ void ha_myisammrg::append_create_info(String *packet) open_table++) { LEX_STRING db, name; - db.length= 0; - db.str= 0; + LINT_INIT(db.str); + split_file_name(open_table->table->filename, &db, &name); if (open_table != first) packet->append(','); diff --git a/storage/ndb/src/ndbapi/NdbEventOperationImpl.cpp b/storage/ndb/src/ndbapi/NdbEventOperationImpl.cpp index cdba96e7503..52760dbbd36 100644 --- a/storage/ndb/src/ndbapi/NdbEventOperationImpl.cpp +++ b/storage/ndb/src/ndbapi/NdbEventOperationImpl.cpp @@ -1338,6 +1338,7 @@ operator<<(NdbOut& out, const Gci_container& gci) return out; } +#ifdef VM_TRACE static NdbOut& operator<<(NdbOut& out, const Gci_container_pod& gci) @@ -1346,7 +1347,7 @@ operator<<(NdbOut& out, const Gci_container_pod& gci) out << *ptr; return out; } - +#endif static Gci_container* diff --git a/support-files/compiler_warnings.supp b/support-files/compiler_warnings.supp index 2f4dcddf737..f27b196aa1b 100644 --- a/support-files/compiler_warnings.supp +++ b/support-files/compiler_warnings.supp @@ -33,12 +33,18 @@ db_vrfy.c : .*comparison is always false due to limited range of data type.* # Ignore all conversion warnings on windows 64 # (Is safe as we are not yet supporting strings >= 2G) # -.* : conversion from '.*size_t' to .*int'.* .* : conversion from '__int64' to .*int'.* -.* : conversion from '__int64' to uint8'.* -.* : conversion from '__int64' to uint32'.* +.* : conversion from '__int64' to 'uint8'.* +.* : conversion from '__int64' to 'uint32'.* +.* : conversion from '__int64' to 'u.*long'.* +.* : conversion from '__int64' to 'long'.* +.* : conversion from '__int64' to 'off_t'.* +.* : conversion from '.*size_t' to .*int'.* .* : conversion from '.*size_t' to 'TaoCrypt::word32'.* .* : conversion from '.*size_t' to 'u.*long'.* +.* : conversion from '.*size_t' to 'uint32'.* +.* : conversion from '.*size_t' to 'off_t'.* +.* : conversion from '.*size_t' to 'size_s'.* # # The following should be fixed by the ndb team @@ -53,3 +59,8 @@ db_vrfy.c : .*comparison is always false due to limited range of data type.* listener.cc : .*conversion from 'SOCKET' to 'int'.* net_serv.cc : .*conversion from 'SOCKET' to 'int'.* mi_packrec.c : .*result of 32-bit shift implicitly converted to 64 bits.* : 567 + +# +# Wrong compiler warnings +# +.* : .*no matching operator delete found; memory will not be freed if initialization throws an exception.* diff --git a/win/README b/win/README index b52e8134aba..9218b63b05e 100644 --- a/win/README +++ b/win/README @@ -88,5 +88,5 @@ may be necessary to clean the build tree to remove any stale objects. 2. To use Visual C++ Express Edition you also need to install the Platform SDK. Please see this link: http://msdn.microsoft.com/vstudio/express/visualc/usingpsdk/ -At step 4 you only need to add the libraries advapi32.lib and user32.lib to +At step 5 you only need to add the libraries advapi32.lib and user32.lib to the file "corewin_express.vsprops" in order to avoid link errors. -- cgit v1.2.1 From ae72e394502e13b854b6e9bb00889fa6b69a7ed9 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 27 Feb 2007 20:06:37 +0200 Subject: Fix (last) compiler warnings storage/innobase/pars/lexyy.c: Fix compiler warnings (fix is also in pars0lex.l) support-files/compiler_warnings.supp: Extra safety to ensure we really get rid of warning :) --- storage/innobase/pars/lexyy.c | 2 +- support-files/compiler_warnings.supp | 1 + 2 files changed, 2 insertions(+), 1 deletion(-) diff --git a/storage/innobase/pars/lexyy.c b/storage/innobase/pars/lexyy.c index 70daf261186..b65de138573 100644 --- a/storage/innobase/pars/lexyy.c +++ b/storage/innobase/pars/lexyy.c @@ -1017,7 +1017,7 @@ YY_RULE_SETUP yylval = sym_tab_add_bound_lit(pars_sym_tab_global, yytext + 1, &type); - return(type); + return((int) type); } YY_BREAK case 4: diff --git a/support-files/compiler_warnings.supp b/support-files/compiler_warnings.supp index f27b196aa1b..babc482976d 100644 --- a/support-files/compiler_warnings.supp +++ b/support-files/compiler_warnings.supp @@ -19,6 +19,7 @@ sql_yacc.cc : .*switch statement contains 'default' but no 'case' labels.* # pars0grm.tab.c: .*'yyerrorlab' : unreferenced label.* _flex_tmp.c: .*not enough actual parameters for macro 'yywrap'.* +pars0lex.l: .*conversion from 'ulint' to 'int', possible loss of data.* # # bdb is not critical to keep up to date -- cgit v1.2.1 From b68a22019e63dbaf7d1918b86c8a456a48dcdc3c Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 27 Feb 2007 22:01:03 +0300 Subject: BUG#26117 "index_merge sort-union over partitioned table crashes" Before the fix: ha_partition objects had ha_partition::m_part_info==NULL and that caused crash After: - The new ha_partition::clone() function makes the clones use parent's m_part_info value. - The parent ha_partition object remains responsible for deallocation of m_part_info. mysql-test/r/partition_innodb.result: BUG#26117 "index_merge sort-union over partitioned table crashes" - Testcase mysql-test/t/partition_innodb.test: BUG#26117 "index_merge sort-union over partitioned table crashes" - Testcase --- mysql-test/r/partition_innodb.result | 54 +++++++++++++++++++++++++++++++++ mysql-test/t/partition_innodb.test | 58 ++++++++++++++++++++++++++++++++++++ sql/ha_partition.cc | 21 ++++++++++--- sql/ha_partition.h | 6 ++++ 4 files changed, 135 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 8619d0909ee..ffc39820340 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -74,3 +74,57 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */ drop table t1; +create table t1 +( +id int unsigned auto_increment, +time datetime not null, +first_name varchar(40), +last_name varchar(50), +primary key (id, time), +index first_index (first_name), +index last_index (last_name) +) engine=Innodb partition by range (to_days(time)) ( +partition p1 values less than (to_days('2007-02-07')), +partition p2 values less than (to_days('2007-02-08')), +partition p3 values less than MAXVALUE +); +insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'), +('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'), +('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'), +('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'), +('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'), +('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'), +('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'), +('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'), +('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'), +('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'), +('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'), +('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'), +('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'), +('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'), +('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'), +('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'), +('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'), +('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'), +('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'), +('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'), +('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'), +('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'), +('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'), +('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'), +('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'), +('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'), +('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'), +('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'), +('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'), +('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'), +('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'), +('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'), +('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'), +('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'), +('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'), +('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'), +('2007-02-07', 'Ernest', 'Greg'); +SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; +id time first_name last_name +drop table t1; diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 782e204742f..f4320c5c56a 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -75,4 +75,62 @@ alter table t1 engine = x; show create table t1; drop table t1; +# BUG#26117: index_merge sort-union over partitioned table crashes + +create table t1 +( + id int unsigned auto_increment, + time datetime not null, + first_name varchar(40), + last_name varchar(50), + primary key (id, time), + index first_index (first_name), + index last_index (last_name) +) engine=Innodb partition by range (to_days(time)) ( + partition p1 values less than (to_days('2007-02-07')), + partition p2 values less than (to_days('2007-02-08')), + partition p3 values less than MAXVALUE +); + +insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'), +('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'), +('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'), +('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'), +('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'), +('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'), +('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'), +('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'), +('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'), +('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'), +('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'), +('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'), +('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'), +('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'), +('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'), +('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'), +('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'), +('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'), +('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'), +('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'), +('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'), +('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'), +('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'), +('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'), +('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'), +('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'), +('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'), +('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'), +('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'), +('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'), +('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'), +('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'), +('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'), +('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'), +('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'), +('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'), +('2007-02-07', 'Ernest', 'Greg'); + +SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; + +drop table t1; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 87d24207dcd..d54e65c804a 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -158,7 +158,7 @@ static uint alter_table_flags(uint flags __attribute__((unused))) ha_partition::ha_partition(handlerton *hton, TABLE_SHARE *share) :handler(hton, share), m_part_info(NULL), m_create_handler(FALSE), - m_is_sub_partitioned(0) + m_is_sub_partitioned(0), is_clone(FALSE) { DBUG_ENTER("ha_partition::ha_partition(table)"); init_handler_variables(); @@ -180,8 +180,7 @@ ha_partition::ha_partition(handlerton *hton, TABLE_SHARE *share) ha_partition::ha_partition(handlerton *hton, partition_info *part_info) :handler(hton, NULL), m_part_info(part_info), m_create_handler(TRUE), - m_is_sub_partitioned(m_part_info->is_sub_partitioned()) - + m_is_sub_partitioned(m_part_info->is_sub_partitioned()), is_clone(FALSE) { DBUG_ENTER("ha_partition::ha_partition(part_info)"); init_handler_variables(); @@ -2320,6 +2319,19 @@ err_handler: DBUG_RETURN(error); } +handler *ha_partition::clone(MEM_ROOT *mem_root) +{ + handler *new_handler= get_new_handler(table->s, mem_root, table->s->db_type); + ((ha_partition*)new_handler)->m_part_info= m_part_info; + ((ha_partition*)new_handler)->is_clone= TRUE; + if (new_handler && !new_handler->ha_open(table, + table->s->normalized_path.str, + table->db_stat, + HA_OPEN_IGNORE_IF_LOCKED)) + return new_handler; + return NULL; +} + /* Close handler object @@ -2346,7 +2358,8 @@ int ha_partition::close(void) DBUG_ENTER("ha_partition::close"); delete_queue(&m_queue); - bitmap_free(&(m_part_info->used_partitions)); + if (!is_clone) + bitmap_free(&(m_part_info->used_partitions)); file= m_file; repeat: diff --git a/sql/ha_partition.h b/sql/ha_partition.h index 4fdf325fa06..1069dd058d5 100644 --- a/sql/ha_partition.h +++ b/sql/ha_partition.h @@ -132,7 +132,13 @@ private: THR_LOCK_DATA lock; /* MySQL lock */ PARTITION_SHARE *share; /* Shared lock info */ + /* + TRUE <=> this object was created with ha_partition::clone and doesn't + "own" the m_part_info structure. + */ + bool is_clone; public: + handler *clone(MEM_ROOT *mem_root); virtual void set_part_info(partition_info *part_info) { m_part_info= part_info; -- cgit v1.2.1 From 8010d9872d16dde48c08bd3caf03c839401785a3 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 28 Feb 2007 13:25:49 +0400 Subject: valgrind error fix --- sql/ha_partition.cc | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index d54e65c804a..952a305c5a4 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -2261,9 +2261,12 @@ int ha_partition::open(const char *name, int mode, uint test_if_locked) } /* Initialise the bitmap we use to determine what partitions are used */ - if (bitmap_init(&(m_part_info->used_partitions), NULL, m_tot_parts, TRUE)) - DBUG_RETURN(1); - bitmap_set_all(&(m_part_info->used_partitions)); + if (!is_clone) + { + if (bitmap_init(&(m_part_info->used_partitions), NULL, m_tot_parts, TRUE)) + DBUG_RETURN(1); + bitmap_set_all(&(m_part_info->used_partitions)); + } /* Recalculate table flags as they may change after open */ m_table_flags= m_file[0]->table_flags(); -- cgit v1.2.1 From 793a137ddd737cd98d8d33efe54bcc218fd60c3f Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 28 Feb 2007 15:44:57 +0400 Subject: added initialization of 'cmp_type' variable(to remove compiler warning) --- sql/item_cmpfunc.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index b99ea78a48f..415c91772fc 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2568,7 +2568,7 @@ void Item_func_in::fix_length_and_dec() THD *thd= current_thd; uint found_types= 0; uint type_cnt= 0, i; - Item_result cmp_type; + Item_result cmp_type= STRING_RESULT; left_result_type= args[0]->result_type(); found_types= collect_cmp_types(args, arg_count); -- cgit v1.2.1