diff options
33 files changed, 637 insertions, 113 deletions
diff --git a/BUILD/check-cpu b/BUILD/check-cpu index 55f4e62327b..3fded0a680f 100755 --- a/BUILD/check-cpu +++ b/BUILD/check-cpu @@ -114,6 +114,10 @@ check_cpu () { *i386*i486*) cpu_arg="pentium-m"; ;; + #Core 2 Duo + *Intel*Core\(TM\)2*) + cpu_arg="nocona"; + ;; # Intel ia64 *Itanium*) diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 3bd7b2ccc15..e0afaaef201 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -57,3 +57,33 @@ select 3 into @v1; explain select 3 into @v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +create table t1(f1 int, f2 int); +insert into t1 values (1,1); +create view v1 as select * from t1 where f1=1; +explain extended select * from v1 where f2=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 1 +explain extended select * from t1 where 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 0 +explain extended select * from t1 where 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 1 +explain extended select * from t1 having 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` having 0 +explain extended select * from t1 having 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` having 1 +drop view v1; +drop table t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index d9ca9e80e44..fad9a7157e1 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -398,4 +398,76 @@ WHERE t3.a=t1.a AND t3.a=t2.a; 3 3 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1(a BIGINT UNSIGNED); +INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF); +SELECT * FROM t1 WHERE a=-1 OR a=-2 ; +a +SELECT * FROM t1 WHERE a IN (-1, -2); +a +CREATE TABLE t2 (a BIGINT UNSIGNED); +insert into t2 values(13491727406643098568), +(0x7fffffefffffffff), +(0x7ffffffeffffffff), +(0x7fffffffefffffff), +(0x7ffffffffeffffff), +(0x7fffffffffefffff), +(0x7ffffffffffeffff), +(0x7fffffffffffefff), +(0x7ffffffffffffeff), +(0x7fffffffffffffef), +(0x7ffffffffffffffe), +(0x7fffffffffffffff), +(0x8000000000000000), +(0x8000000000000001), +(0x8000000000000002), +(0x8000000000000300), +(0x8000000000000400), +(0x8000000000000401), +(0x8000000000004001), +(0x8000000000040001), +(0x8000000000400001), +(0x8000000004000001), +(0x8000000040000001), +(0x8000000400000001), +(0x8000004000000001), +(0x8000040000000001); +SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42); +HEX(a) +BB3C3E98175D33C8 +SELECT HEX(a) FROM t2 WHERE a IN +(0xBB3C3E98175D33C8, +0x7fffffffffffffff, +0x8000000000000000, +0x8000000000000400, +0x8000000000000401, +42); +HEX(a) +BB3C3E98175D33C8 +7FFFFFFFFFFFFFFF +8000000000000000 +8000000000000400 +8000000000000401 +SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001); +HEX(a) +7FFFFFFFFFFFFFFF +8000000000000001 +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff); +HEX(a) +7FFFFFFFFFFFFFFE +7FFFFFFFFFFFFFFF +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc'); +HEX(a) +7FFFFFFFFFFFFFFE +7FFFFFFFFFFFFFFF +CREATE TABLE t3 (a BIGINT UNSIGNED); +INSERT INTO t3 VALUES (9223372036854775551); +SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); +HEX(a) +CREATE TABLE t4 (a DATE); +INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29'); +SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); +a +Warnings: +Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1 +DROP TABLE t1,t2,t3,t4; End of 5.0 tests diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index e716a89132c..94149d8eae0 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1954,4 +1954,10 @@ A B tire # # 1 ## ## 2 DROP TABLE t1; +SELECT UNHEX('G'); +UNHEX('G') +NULL +SELECT UNHEX('G') IS NULL; +UNHEX('G') IS NULL +1 End of 5.0 tests diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 43832bdbccc..c3fbdb3b3bf 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -79,7 +79,7 @@ explain extended select * from t1 where 1 xor 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 select - a from t1; - a -1 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 0185394cdad..0f28ade6523 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -950,3 +950,11 @@ NULL 2 3 DROP TABLE t1,t2,t3,t4; +create table t1 (a int, b int, c int); +insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9); +select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc; +a ratio +1 0.5000 +19 1.3333 +9 2.6667 +drop table t1; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 36bfebdb29d..34f2aa94000 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5765,4 +5765,17 @@ func_8407_b() 1500 drop function func_8407_a| drop function func_8407_b| +DROP FUNCTION IF EXISTS bug25373| +CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER +LANGUAGE SQL DETERMINISTIC +RETURN p1;| +CREATE TABLE t3 (f1 INT, f2 FLOAT)| +INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)| +SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP| +SUM(f2) bug25373(f1) +6.3000000715256 1 +15 2 +21.300000071526 NULL +DROP FUNCTION bug25373| +DROP TABLE t3| drop table t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 7ff4b9bcf16..eb6172da470 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -421,7 +421,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` +Note 1003 select 1 AS `1` from `test`.`t1` where 1 drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, @@ -1180,7 +1180,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1190,7 +1190,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', @@ -1532,7 +1532,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); @@ -1540,7 +1540,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); @@ -1548,7 +1548,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); @@ -1556,7 +1556,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1618,7 +1618,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 UNION t1 system NULL NULL NULL NULL 1 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index b3c5760e2c9..748c2644eb9 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -377,6 +377,10 @@ create table t1(f1 int, `*f2` int); insert into t1 values (1,1); update t1 set `*f2`=1; drop table t1; +create table t1(f1 int); +update t1 set f2=1 order by f2; +ERROR 42S22: Unknown column 'f2' in 'order clause' +drop table t1; CREATE TABLE t1 ( request_id int unsigned NOT NULL auto_increment, user_id varchar(12) default NULL, diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index efce0cdf3b5..04cf37f457a 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -51,4 +51,19 @@ set names latin1; select 3 into @v1; explain select 3 into @v1; +# +# Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were +# optimized away. +# +create table t1(f1 int, f2 int); +insert into t1 values (1,1); +create view v1 as select * from t1 where f1=1; +explain extended select * from v1 where f2=1; +explain extended select * from t1 where 0; +explain extended select * from t1 where 1; +explain extended select * from t1 having 0; +explain extended select * from t1 having 1; +drop view v1; +drop table t1; + # End of 5.0 tests. diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 54b81bed133..f9749662ec1 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -298,4 +298,66 @@ SELECT STRAIGHT_JOIN DROP TABLE t1,t2,t3,t4; +# +# BUG#19342: IN works incorrectly for BIGINT UNSIGNED values +# +CREATE TABLE t1(a BIGINT UNSIGNED); +INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF); + +SELECT * FROM t1 WHERE a=-1 OR a=-2 ; +SELECT * FROM t1 WHERE a IN (-1, -2); + +CREATE TABLE t2 (a BIGINT UNSIGNED); +insert into t2 values(13491727406643098568), + (0x7fffffefffffffff), + (0x7ffffffeffffffff), + (0x7fffffffefffffff), + (0x7ffffffffeffffff), + (0x7fffffffffefffff), + (0x7ffffffffffeffff), + (0x7fffffffffffefff), + (0x7ffffffffffffeff), + (0x7fffffffffffffef), + (0x7ffffffffffffffe), + (0x7fffffffffffffff), + (0x8000000000000000), + (0x8000000000000001), + (0x8000000000000002), + (0x8000000000000300), + (0x8000000000000400), + (0x8000000000000401), + (0x8000000000004001), + (0x8000000000040001), + (0x8000000000400001), + (0x8000000004000001), + (0x8000000040000001), + (0x8000000400000001), + (0x8000004000000001), + (0x8000040000000001); + +SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42); + +SELECT HEX(a) FROM t2 WHERE a IN +(0xBB3C3E98175D33C8, + 0x7fffffffffffffff, + 0x8000000000000000, + 0x8000000000000400, + 0x8000000000000401, + 42); + +SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001); +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff); +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc'); + +CREATE TABLE t3 (a BIGINT UNSIGNED); +INSERT INTO t3 VALUES (9223372036854775551); + +SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); + +CREATE TABLE t4 (a DATE); +INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29'); +SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); + +DROP TABLE t1,t2,t3,t4; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 7cf7ef2cab6..b074e6139e9 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1030,4 +1030,10 @@ SELECT REPEAT( '#', tire ) AS A, DROP TABLE t1; +# +# Bug #26537: UNHEX() IS NULL comparison fails +# +SELECT UNHEX('G'); +SELECT UNHEX('G') IS NULL; + --echo End of 5.0 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 5533f273b0d..a0a1147336c 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -669,3 +669,11 @@ SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a) ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b; DROP TABLE t1,t2,t3,t4; + +# +# Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result. +# +create table t1 (a int, b int, c int); +insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9); +select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc; +drop table t1; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 277a38e18e6..9af24ee0337 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -6739,6 +6739,21 @@ drop function func_8407_a| drop function func_8407_b| # +# Bug#25373: Stored functions wasn't compared correctly which leads to a wrong +# result. +# +--disable_warnings +DROP FUNCTION IF EXISTS bug25373| +--disable_warnings +CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER +LANGUAGE SQL DETERMINISTIC +RETURN p1;| +CREATE TABLE t3 (f1 INT, f2 FLOAT)| +INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)| +SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP| +DROP FUNCTION bug25373| +DROP TABLE t3| +# # NOTE: The delimiter is `|`, and not `;`. It is changed to `;` # at the end of the file! # diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 23ee75d61ea..6cec940d286 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -306,6 +306,14 @@ create table t1(f1 int, `*f2` int); insert into t1 values (1,1); update t1 set `*f2`=1; drop table t1; + +# +# Bug#25126: Wrongly resolved field leads to a crash +# +create table t1(f1 int); +--error 1054 +update t1 set f2=1 order by f2; +drop table t1; # End of 4.1 tests # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 90a3c6a1e2d..1b229298896 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3163,4 +3163,44 @@ drop view view_24532_a; drop view view_24532_b; drop table table_24532; +# +# Bug#26560: view using subquery with a reference to an outer alias +# + +CREATE TABLE t1 ( + lid int NOT NULL PRIMARY KEY, + name char(10) NOT NULL +); +INSERT INTO t1 (lid, name) VALUES + (1, 'YES'), (2, 'NO'); + +CREATE TABLE t2 ( + id int NOT NULL PRIMARY KEY, + gid int NOT NULL, + lid int NOT NULL, + dt date +); +INSERT INTO t2 (id, gid, lid, dt) VALUES + (1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'), + (3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02'); + +SELECT DISTINCT t2.gid AS lgid, + (SELECT t1.name FROM t1, t2 + WHERE t1.lid = t2.lid AND t2.gid = lgid + ORDER BY t2.dt DESC LIMIT 1 + ) as clid + FROM t2; + +CREATE VIEW v1 AS +SELECT DISTINCT t2.gid AS lgid, + (SELECT t1.name FROM t1, t2 + WHERE t1.lid = t2.lid AND t2.gid = lgid + ORDER BY t2.dt DESC LIMIT 1 + ) as clid + FROM t2; +SELECT * FROM v1; + +DROP VIEW v1; +DROP table t1,t2; + --echo End of 5.0 tests. diff --git a/sql/item.cc b/sql/item.cc index 92008a344ef..8e1389dd33b 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3320,7 +3320,7 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select) ORDER *group_list= (ORDER*) select->group_list.first; bool ambiguous_fields= FALSE; uint counter; - bool not_used; + enum_resolution_type resolution; /* Search for a column or derived column named as 'ref' in the SELECT @@ -3328,8 +3328,10 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select) */ if (!(select_ref= find_item_in_list(ref, *(select->get_item_list()), &counter, REPORT_EXCEPT_NOT_FOUND, - ¬_used))) + &resolution))) return NULL; /* Some error occurred. */ + if (resolution == RESOLVED_AGAINST_ALIAS) + ref->alias_name_used= TRUE; /* If this is a non-aggregated field inside HAVING, search in GROUP BY. */ if (select->having_fix_field && !ref->with_sum_func && group_list) @@ -3630,9 +3632,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) *ref= NULL; // Don't call set_properties() rf= (place == IN_HAVING ? new Item_ref(context, ref, (char*) table_name, - (char*) field_name) : + (char*) field_name, alias_name_used) : new Item_direct_ref(context, ref, (char*) table_name, - (char*) field_name)); + (char*) field_name, alias_name_used)); *ref= save; if (!rf) return -1; @@ -3750,12 +3752,14 @@ bool Item_field::fix_fields(THD *thd, Item **reference) if (thd->lex->current_select->is_item_list_lookup) { uint counter; - bool not_used; + enum_resolution_type resolution; Item** res= find_item_in_list(this, thd->lex->current_select->item_list, &counter, REPORT_EXCEPT_NOT_FOUND, - ¬_used); + &resolution); if (!res) return 1; + if (resolution == RESOLVED_AGAINST_ALIAS) + alias_name_used= TRUE; if (res != (Item **)not_found_item) { if ((*res)->type() == Item::FIELD_ITEM) @@ -4909,10 +4913,12 @@ Item *Item_field::update_value_transformer(byte *select_arg) Item_ref::Item_ref(Name_resolution_context *context_arg, Item **item, const char *table_name_arg, - const char *field_name_arg) + const char *field_name_arg, + bool alias_name_used_arg) :Item_ident(context_arg, NullS, table_name_arg, field_name_arg), result_field(0), ref(item) { + alias_name_used= alias_name_used_arg; /* This constructor used to create some internals references over fixed items */ @@ -5195,11 +5201,13 @@ void Item_ref::set_properties() */ with_sum_func= (*ref)->with_sum_func; unsigned_flag= (*ref)->unsigned_flag; + fixed= 1; + if (alias_name_used) + return; if ((*ref)->type() == FIELD_ITEM) alias_name_used= ((Item_ident *) (*ref))->alias_name_used; else alias_name_used= TRUE; // it is not field, so it is was resolved by alias - fixed= 1; } @@ -5217,7 +5225,7 @@ void Item_ref::print(String *str) if (ref) { if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF && - ref_type() != OUTER_REF && name && alias_name_used) + !table_name && name && alias_name_used) { THD *thd= current_thd; append_identifier(thd, str, name, (uint) strlen(name)); diff --git a/sql/item.h b/sql/item.h index 833bebdee7e..2a121523423 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1774,7 +1774,10 @@ public: Item_hex_string(const char *str,uint str_length); enum Type type() const { return VARBIN_ITEM; } double val_real() - { DBUG_ASSERT(fixed == 1); return (double) Item_hex_string::val_int(); } + { + DBUG_ASSERT(fixed == 1); + return (double) (ulonglong) Item_hex_string::val_int(); + } longlong val_int(); bool basic_const_item() const { return 1; } String *val_str(String*) { DBUG_ASSERT(fixed == 1); return &str_value; } @@ -1848,7 +1851,8 @@ public: with Bar, and if we have a more broader set of problems like this. */ Item_ref(Name_resolution_context *context_arg, Item **item, - const char *table_name_arg, const char *field_name_arg); + const char *table_name_arg, const char *field_name_arg, + bool alias_name_used_arg= FALSE); /* Constructor need to process subselect with temporary tables (see Item) */ Item_ref(THD *thd, Item_ref *item) @@ -1923,8 +1927,11 @@ class Item_direct_ref :public Item_ref public: Item_direct_ref(Name_resolution_context *context_arg, Item **item, const char *table_name_arg, - const char *field_name_arg) - :Item_ref(context_arg, item, table_name_arg, field_name_arg) {} + const char *field_name_arg, + bool alias_name_used_arg= FALSE) + :Item_ref(context_arg, item, table_name_arg, + field_name_arg, alias_name_used_arg) + {} /* Constructor need to process subselect with temporary tables (see Item) */ Item_direct_ref(THD *thd, Item_direct_ref *item) : Item_ref(thd, item) {} diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index edae81b8e2d..e032974fdea 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2155,9 +2155,100 @@ void Item_func_coalesce::fix_length_and_dec() Classes and function for the IN operator ****************************************************************************/ -static int cmp_longlong(void *cmp_arg, longlong *a,longlong *b) +/* + Determine which of the signed longlong arguments is bigger + + SYNOPSIS + cmp_longs() + a_val left argument + b_val right argument + + DESCRIPTION + This function will compare two signed longlong arguments + and will return -1, 0, or 1 if left argument is smaller than, + equal to or greater than the right argument. + + RETURN VALUE + -1 left argument is smaller than the right argument. + 0 left argument is equal to the right argument. + 1 left argument is greater than the right argument. +*/ +static inline int cmp_longs (longlong a_val, longlong b_val) { - return *a < *b ? -1 : *a == *b ? 0 : 1; + return a_val < b_val ? -1 : a_val == b_val ? 0 : 1; +} + + +/* + Determine which of the unsigned longlong arguments is bigger + + SYNOPSIS + cmp_ulongs() + a_val left argument + b_val right argument + + DESCRIPTION + This function will compare two unsigned longlong arguments + and will return -1, 0, or 1 if left argument is smaller than, + equal to or greater than the right argument. + + RETURN VALUE + -1 left argument is smaller than the right argument. + 0 left argument is equal to the right argument. + 1 left argument is greater than the right argument. +*/ +static inline int cmp_ulongs (ulonglong a_val, ulonglong b_val) +{ + return a_val < b_val ? -1 : a_val == b_val ? 0 : 1; +} + + +/* + Compare two integers in IN value list format (packed_longlong) + + SYNOPSIS + cmp_longlong() + cmp_arg an argument passed to the calling function (qsort2) + a left argument + b right argument + + DESCRIPTION + This function will compare two integer arguments in the IN value list + format and will return -1, 0, or 1 if left argument is smaller than, + equal to or greater than the right argument. + It's used in sorting the IN values list and finding an element in it. + Depending on the signedness of the arguments cmp_longlong() will + compare them as either signed (using cmp_longs()) or unsigned (using + cmp_ulongs()). + + RETURN VALUE + -1 left argument is smaller than the right argument. + 0 left argument is equal to the right argument. + 1 left argument is greater than the right argument. +*/ +int cmp_longlong(void *cmp_arg, + in_longlong::packed_longlong *a, + in_longlong::packed_longlong *b) +{ + if (a->unsigned_flag != b->unsigned_flag) + { + /* + One of the args is unsigned and is too big to fit into the + positive signed range. Report no match. + */ + if (a->unsigned_flag && ((ulonglong) a->val) > LONGLONG_MAX || + b->unsigned_flag && ((ulonglong) b->val) > LONGLONG_MAX) + return a->unsigned_flag ? 1 : -1; + /* + Although the signedness differs both args can fit into the signed + positive range. Make them signed and compare as usual. + */ + return cmp_longs (a->val, b->val); + } + if (a->unsigned_flag) + return cmp_ulongs ((ulonglong) a->val, (ulonglong) b->val); + else + return cmp_longs (a->val, b->val); } static int cmp_double(void *cmp_arg, double *a,double *b) @@ -2282,19 +2373,23 @@ void in_row::set(uint pos, Item *item) } in_longlong::in_longlong(uint elements) - :in_vector(elements,sizeof(longlong),(qsort2_cmp) cmp_longlong, 0) + :in_vector(elements,sizeof(packed_longlong),(qsort2_cmp) cmp_longlong, 0) {} void in_longlong::set(uint pos,Item *item) { - ((longlong*) base)[pos]=item->val_int(); + struct packed_longlong *buff= &((packed_longlong*) base)[pos]; + + buff->val= item->val_int(); + buff->unsigned_flag= item->unsigned_flag; } byte *in_longlong::get_value(Item *item) { - tmp= item->val_int(); + tmp.val= item->val_int(); if (item->null_value) return 0; + tmp.unsigned_flag= item->unsigned_flag; return (byte*) &tmp; } @@ -2615,6 +2710,31 @@ void Item_func_in::fix_length_and_dec() */ if (const_itm && !nulls_in_row()) { + /* + IN must compare INT/DATE/DATETIME/TIMESTAMP columns and constants + as int values (the same way as equality does). + So we must check here if the column on the left and all the constant + values on the right can be compared as integers and adjust the + comparison type accordingly. + */ + if (args[0]->real_item()->type() == FIELD_ITEM && + thd->lex->sql_command != SQLCOM_CREATE_VIEW && + thd->lex->sql_command != SQLCOM_SHOW_CREATE && + cmp_type != INT_RESULT) + { + Field *field= ((Item_field*) (args[0]->real_item()))->field; + if (field->can_be_compared_as_longlong()) + { + bool all_converted= TRUE; + for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++) + { + if (!convert_constant_item (thd, field, &arg[0])) + all_converted= FALSE; + } + if (all_converted) + cmp_type= INT_RESULT; + } + } switch (cmp_type) { case STRING_RESULT: array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 319c6c2a36f..f6c6f612c5b 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -820,7 +820,16 @@ public: class in_longlong :public in_vector { - longlong tmp; + /* + Here we declare a temporary variable (tmp) of the same type as the + elements of this vector. tmp is used in finding if a given value is in + the list. + */ + struct packed_longlong + { + longlong val; + longlong unsigned_flag; // Use longlong, not bool, to preserve alignment + } tmp; public: in_longlong(uint elements); void set(uint pos,Item *item); @@ -836,8 +845,12 @@ public: } void value_to_item(uint pos, Item *item) { - ((Item_int*)item)->value= ((longlong*)base)[pos]; + ((Item_int*) item)->value= ((packed_longlong*) base)[pos].val; + ((Item_int*) item)->unsigned_flag= (my_bool) + ((packed_longlong*) base)[pos].unsigned_flag; } + + friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b); }; class in_double :public in_vector diff --git a/sql/item_func.cc b/sql/item_func.cc index e41bf25e8e9..e8ecd6b8f1c 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -409,8 +409,13 @@ bool Item_func::eq(const Item *item, bool binary_cmp) const if (item->type() != FUNC_ITEM) return 0; Item_func *item_func=(Item_func*) item; - if (arg_count != item_func->arg_count || - func_name() != item_func->func_name()) + Item_func::Functype func_type; + if ((func_type= functype()) != item_func->functype() || + arg_count != item_func->arg_count || + (func_type != Item_func::FUNC_SP && + func_name() != item_func->func_name()) || + (func_type == Item_func::FUNC_SP && + my_strcasecmp(system_charset_info, func_name(), item_func->func_name()))) return 0; for (uint i=0; i < arg_count ; i++) if (!args[i]->eq(item_func->args[i], binary_cmp)) diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 60547d00a5c..778ea6e9496 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -605,7 +605,11 @@ class Item_func_unhex :public Item_str_func { String tmp_value; public: - Item_func_unhex(Item *a) :Item_str_func(a) {} + Item_func_unhex(Item *a) :Item_str_func(a) + { + /* there can be bad hex strings */ + maybe_null= 1; + } const char *func_name() const { return "unhex"; } String *val_str(String *); void fix_length_and_dec() diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 1ffbc1243e9..c590429047f 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -1020,9 +1020,29 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, table_map read_tables, COND *conds, bool allow_null_cond, int *error); extern Item **not_found_item; + +/* + This enumeration type is used only by the function find_item_in_list + to return the info on how an item has been resolved against a list + of possibly aliased items. + The item can be resolved: + - against an alias name of the list's element (RESOLVED_AGAINST_ALIAS) + - against non-aliased field name of the list (RESOLVED_WITH_NO_ALIAS) + - against an aliased field name of the list (RESOLVED_BEHIND_ALIAS) + - ignoring the alias name in cases when SQL requires to ignore aliases + (e.g. when the resolved field reference contains a table name or + when the resolved item is an expression) (RESOLVED_IGNORING_ALIAS) +*/ +enum enum_resolution_type { + NOT_RESOLVED=0, + RESOLVED_IGNORING_ALIAS, + RESOLVED_BEHIND_ALIAS, + RESOLVED_WITH_NO_ALIAS, + RESOLVED_AGAINST_ALIAS +}; Item ** find_item_in_list(Item *item, List<Item> &items, uint *counter, find_item_error_report_type report_error, - bool *unaliased); + enum_resolution_type *resolution); bool get_key_map_from_key_list(key_map *map, TABLE *table, List<String> *index_list); bool insert_fields(THD *thd, Name_resolution_context *context, @@ -1078,7 +1098,8 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table, st_table_list *TABLE_LIST::*link, const char *db_name, const char *table_name); -TABLE_LIST *unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list); +TABLE_LIST *unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, + bool check_alias); TABLE **find_temporary_table(THD *thd, const char *db, const char *table_name); bool close_temporary_table(THD *thd, const char *db, const char *table_name); void close_temporary(TABLE *table, bool delete_table); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 6f114165fa6..77bb1d9642b 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -849,6 +849,7 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table, thd thread handle table table which should be checked table_list list of tables + check_alias whether to check tables' aliases NOTE: to exclude derived tables from check we use following mechanism: a) during derived table processing set THD::derived_tables_processing @@ -876,10 +877,11 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table, 0 if table is unique */ -TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list) +TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, + bool check_alias) { TABLE_LIST *res; - const char *d_name, *t_name; + const char *d_name, *t_name, *t_alias; DBUG_ENTER("unique_table"); DBUG_PRINT("enter", ("table alias: %s", table->alias)); @@ -907,6 +909,7 @@ TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list) } d_name= table->db; t_name= table->table_name; + t_alias= table->alias; DBUG_PRINT("info", ("real table: %s.%s", d_name, t_name)); for (;;) @@ -914,6 +917,9 @@ TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list) if (((! (res= find_table_in_global_list(table_list, d_name, t_name))) && (! (res= mysql_lock_have_duplicate(thd, table, table_list)))) || ((!res->table || res->table != table->table) && + (!check_alias || !(lower_case_table_names ? + my_strcasecmp(files_charset_info, t_alias, res->alias) : + strcmp(t_alias, res->alias))) && res->select_lex && !res->select_lex->exclude_from_table_unique_test && !res->prelocking_placeholder)) break; @@ -3654,10 +3660,13 @@ find_field_in_tables(THD *thd, Item_ident *item, return not_found_item, report other errors, return 0 IGNORE_ERRORS Do not report errors, return 0 if error - unaliased Set to true if item is field which was found - by original field name and not by its alias - in item list. Set to false otherwise. - + resolution Set to the resolution type if the item is found + (it says whether the item is resolved + against an alias name, + or as a field name without alias, + or as a field hidden by alias, + or ignoring alias) + RETURN VALUES 0 Item is not found or item is not unique, error message is reported @@ -3673,7 +3682,8 @@ Item **not_found_item= (Item**) 0x1; Item ** find_item_in_list(Item *find, List<Item> &items, uint *counter, - find_item_error_report_type report_error, bool *unaliased) + find_item_error_report_type report_error, + enum_resolution_type *resolution) { List_iterator<Item> li(items); Item **found=0, **found_unaliased= 0, *item; @@ -3687,10 +3697,9 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter, */ bool is_ref_by_name= 0; uint unaliased_counter; - LINT_INIT(unaliased_counter); // Dependent on found_unaliased - *unaliased= FALSE; + *resolution= NOT_RESOLVED; is_ref_by_name= (find->type() == Item::FIELD_ITEM || find->type() == Item::REF_ITEM); @@ -3757,63 +3766,77 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter, } found_unaliased= li.ref(); unaliased_counter= i; + *resolution= RESOLVED_IGNORING_ALIAS; if (db_name) break; // Perfect match } } - else if (!my_strcasecmp(system_charset_info, item_field->name, - field_name)) - { - /* - If table name was not given we should scan through aliases - (or non-aliased fields) first. We are also checking unaliased - name of the field in then next else-if, to be able to find - instantly field (hidden by alias) if no suitable alias (or - non-aliased field) was found. - */ - if (found) - { - if ((*found)->eq(item, 0)) - continue; // Same field twice - if (report_error != IGNORE_ERRORS) - my_error(ER_NON_UNIQ_ERROR, MYF(0), - find->full_name(), current_thd->where); - return (Item**) 0; - } - found= li.ref(); - *counter= i; - } - else if (!my_strcasecmp(system_charset_info, item_field->field_name, - field_name)) + else { - /* - We will use un-aliased field or react on such ambiguities only if - we won't be able to find aliased field. - Again if we have ambiguity with field outside of select list - we should prefer fields from select list. - */ - if (found_unaliased) + int fname_cmp= my_strcasecmp(system_charset_info, + item_field->field_name, + field_name); + if (!my_strcasecmp(system_charset_info, + item_field->name,field_name)) { - if ((*found_unaliased)->eq(item, 0)) - continue; // Same field twice - found_unaliased_non_uniq= 1; + /* + If table name was not given we should scan through aliases + and non-aliased fields first. We are also checking unaliased + name of the field in then next else-if, to be able to find + instantly field (hidden by alias) if no suitable alias or + non-aliased field was found. + */ + if (found) + { + if ((*found)->eq(item, 0)) + continue; // Same field twice + if (report_error != IGNORE_ERRORS) + my_error(ER_NON_UNIQ_ERROR, MYF(0), + find->full_name(), current_thd->where); + return (Item**) 0; + } + found= li.ref(); + *counter= i; + *resolution= fname_cmp ? RESOLVED_AGAINST_ALIAS: + RESOLVED_WITH_NO_ALIAS; } - else + else if (!fname_cmp) { + /* + We will use non-aliased field or react on such ambiguities only if + we won't be able to find aliased field. + Again if we have ambiguity with field outside of select list + we should prefer fields from select list. + */ + if (found_unaliased) + { + if ((*found_unaliased)->eq(item, 0)) + continue; // Same field twice + found_unaliased_non_uniq= 1; + } found_unaliased= li.ref(); unaliased_counter= i; } } } - else if (!table_name && (find->eq(item,0) || - is_ref_by_name && find->name && item->name && - !my_strcasecmp(system_charset_info, - item->name,find->name))) - { - found= li.ref(); - *counter= i; - break; - } + else if (!table_name) + { + if (is_ref_by_name && find->name && item->name && + !my_strcasecmp(system_charset_info,item->name,find->name)) + { + found= li.ref(); + *counter= i; + *resolution= RESOLVED_AGAINST_ALIAS; + break; + } + else if (find->eq(item,0)) + { + found= li.ref(); + *counter= i; + *resolution= RESOLVED_IGNORING_ALIAS; + break; + } + } } if (!found) { @@ -3828,7 +3851,7 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter, { found= found_unaliased; *counter= unaliased_counter; - *unaliased= TRUE; + *resolution= RESOLVED_BEHIND_ALIAS; } } if (found) @@ -4606,12 +4629,15 @@ bool setup_fields(THD *thd, Item **ref_pointer_array, bool save_set_query_id= thd->set_query_id; nesting_map save_allow_sum_func= thd->lex->allow_sum_func; List_iterator<Item> it(fields); + bool save_is_item_list_lookup; DBUG_ENTER("setup_fields"); thd->set_query_id=set_query_id; if (allow_sum_func) thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level; thd->where= THD::DEFAULT_WHERE; + save_is_item_list_lookup= thd->lex->current_select->is_item_list_lookup; + thd->lex->current_select->is_item_list_lookup= 0; /* To prevent fail on forward lookup we fill it with zerows, @@ -4634,6 +4660,7 @@ bool setup_fields(THD *thd, Item **ref_pointer_array, if (!item->fixed && item->fix_fields(thd, it.ref()) || (item= *(it.ref()))->check_cols(1)) { + thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; thd->lex->allow_sum_func= save_allow_sum_func; thd->set_query_id= save_set_query_id; DBUG_RETURN(TRUE); /* purecov: inspected */ @@ -4646,6 +4673,7 @@ bool setup_fields(THD *thd, Item **ref_pointer_array, thd->used_tables|= item->used_tables(); thd->lex->current_select->cur_pos_in_select_list++; } + thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS; thd->lex->allow_sum_func= save_allow_sum_func; @@ -5142,6 +5170,8 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, */ bool it_is_update= (select_lex == &thd->lex->select_lex) && thd->lex->which_check_option_applicable(); + bool save_is_item_list_lookup= select_lex->is_item_list_lookup; + select_lex->is_item_list_lookup= 0; DBUG_ENTER("setup_conds"); if (select_lex->conds_processed_with_permanent_arena || @@ -5216,9 +5246,11 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, select_lex->where= *conds; select_lex->conds_processed_with_permanent_arena= 1; } + thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; DBUG_RETURN(test(thd->net.report_error)); err_no_arena: + select_lex->is_item_list_lookup= save_is_item_list_lookup; DBUG_RETURN(1); } diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 749ee04493b..fe89f0f28f0 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -371,7 +371,7 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) } { TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, table_list, table_list->next_global))) + if ((duplicate= unique_table(thd, table_list, table_list->next_global, 0))) { update_non_unique_table_error(table_list, "DELETE", duplicate); DBUG_RETURN(TRUE); @@ -468,7 +468,7 @@ bool mysql_multi_delete_prepare(THD *thd) { TABLE_LIST *duplicate; if ((duplicate= unique_table(thd, target_tbl->correspondent_table, - lex->query_tables))) + lex->query_tables, 0))) { update_non_unique_table_error(target_tbl->correspondent_table, "DELETE", duplicate); diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index c7eb68ede07..a5f6f08973d 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1073,7 +1073,7 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, { Item *fake_conds= 0; TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, table_list, table_list->next_global))) + if ((duplicate= unique_table(thd, table_list, table_list->next_global, 1))) { update_non_unique_table_error(table_list, "INSERT", duplicate); DBUG_RETURN(TRUE); @@ -2453,7 +2453,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) query */ if (!(lex->current_select->options & OPTION_BUFFER_RESULT) && - unique_table(thd, table_list, table_list->next_global)) + unique_table(thd, table_list, table_list->next_global, 0)) { /* Using same table for INSERT and SELECT */ lex->current_select->options|= OPTION_BUFFER_RESULT; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 4c0ab7a8e54..3be844b6761 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1206,6 +1206,7 @@ void st_select_lex::init_select() is_correlated= 0; cur_pos_in_select_list= UNDEF_POS; non_agg_fields.empty(); + cond_value= having_value= Item::COND_UNDEF; inner_refs_list.empty(); } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b8be619c0c8..de7de0d46e9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -488,6 +488,8 @@ public: Item *where, *having; /* WHERE & HAVING clauses */ Item *prep_where; /* saved WHERE clause for prepared statement processing */ Item *prep_having;/* saved HAVING clause for prepared statement processing */ + /* Saved values of the WHERE and HAVING clauses*/ + Item::cond_result cond_value, having_value; /* point on lex in which it was created, used in view subquery detection */ st_lex *parent_lex; enum olap_type olap; diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 7ed1a900fba..3f67a0c3f5d 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -176,7 +176,7 @@ bool mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, table is marked to be 'used for insert' in which case we should never mark this table as as 'const table' (ie, one that has only one row). */ - if (unique_table(thd, table_list, table_list->next_global)) + if (unique_table(thd, table_list, table_list->next_global, 0)) { my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name); DBUG_RETURN(TRUE); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 03c37e4dba9..b503e147624 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3023,7 +3023,7 @@ mysql_execute_command(THD *thd) if (!(create_info.options & HA_LEX_CREATE_TMP_TABLE)) { TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, create_table, select_tables))) + if ((duplicate= unique_table(thd, create_table, select_tables, 0))) { update_non_unique_table_error(create_table, "CREATE", duplicate); res= 1; @@ -3039,7 +3039,7 @@ mysql_execute_command(THD *thd) tab= tab->next_local) { TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, tab, select_tables))) + if ((duplicate= unique_table(thd, tab, select_tables, 0))) { update_non_unique_table_error(tab, "CREATE", duplicate); res= 1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5202f35f4de..06352d48154 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -396,6 +396,7 @@ JOIN::prepare(Item ***rref_pointer_array, join_list= &select_lex->top_join_list; union_part= (unit_arg->first_select()->next_select() != 0); + thd->lex->current_select->is_item_list_lookup= 1; /* If we have already executed SELECT, then it have not sense to prevent its table from update (see unique_table()) @@ -455,6 +456,17 @@ JOIN::prepare(Item ***rref_pointer_array, select_lex->fix_prepare_information(thd, &conds, &having); + if (order) + { + ORDER *ord; + for (ord= order; ord; ord= ord->next) + { + Item *item= *ord->item; + if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM) + item->split_sum_func(thd, ref_pointer_array, all_fields); + } + } + if (having && having->with_sum_func) having->split_sum_func2(thd, ref_pointer_array, all_fields, &having, TRUE); @@ -748,7 +760,6 @@ JOIN::optimize() } { - Item::cond_result having_value; having= optimize_cond(this, having, join_list, &having_value); if (thd->net.report_error) { @@ -756,6 +767,10 @@ JOIN::optimize() DBUG_PRINT("error",("Error from optimize_cond")); DBUG_RETURN(1); } + if (select_lex->where) + select_lex->cond_value= cond_value; + if (select_lex->having) + select_lex->having_value= having_value; if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) @@ -896,6 +911,7 @@ JOIN::optimize() conds->update_used_tables(); DBUG_EXECUTE("where", print_where(conds, "after substitute_best_equal");); } + /* Permorm the the optimization on fields evaluation mentioned above for all on expressions. @@ -7605,6 +7621,9 @@ static COND* substitute_for_best_equal_field(COND *cond, break; } } + if (!((Item_cond*)cond)->argument_list()->elements) + cond= new Item_int((int32)cond->val_bool()); + } else if (cond->type() == Item::FUNC_ITEM && ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) @@ -13158,7 +13177,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, Item **select_item; /* The corresponding item from the SELECT clause. */ Field *from_field; /* The corresponding field from the FROM clause. */ uint counter; - bool unaliased; + enum_resolution_type resolution; /* Local SP variables may be int but are expressions, not positions. @@ -13181,7 +13200,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, } /* Lookup the current GROUP/ORDER field in the SELECT clause. */ select_item= find_item_in_list(order_item, fields, &counter, - REPORT_EXCEPT_NOT_FOUND, &unaliased); + REPORT_EXCEPT_NOT_FOUND, &resolution); if (!select_item) return TRUE; /* The item is not unique, or some other error occured. */ @@ -13195,7 +13214,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, original field name, we should additionaly check if we have conflict for this name (in case if we would perform lookup in all tables). */ - if (unaliased && !order_item->fixed && + if (resolution == RESOLVED_BEHIND_ALIAS && !order_item->fixed && order_item->fix_fields(thd, order->item)) return TRUE; @@ -13265,16 +13284,11 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, We check order_item->fixed because Item_func_group_concat can put arguments for which fix_fields already was called. */ - thd->lex->current_select->is_item_list_lookup= 1; if (!order_item->fixed && (order_item->fix_fields(thd, order->item) || (order_item= *order->item)->check_cols(1) || thd->is_fatal_error)) - { - thd->lex->current_select->is_item_list_lookup= 0; return TRUE; /* Wrong field. */ - } - thd->lex->current_select->is_item_list_lookup= 0; uint el= all_fields.elements; all_fields.push_front(order_item); /* Add new field to field list. */ @@ -13429,7 +13443,7 @@ setup_new_fields(THD *thd, List<Item> &fields, thd->set_query_id=1; // Not really needed, but... uint counter; - bool not_used; + enum_resolution_type not_used; for (; new_field ; new_field= new_field->next) { if ((item= find_item_in_list(*new_field->item, fields, &counter, @@ -15328,10 +15342,13 @@ void st_select_lex::print(THD *thd, String *str) Item *cur_where= where; if (join) cur_where= join->conds; - if (cur_where) + if (cur_where || cond_value != Item::COND_UNDEF) { str->append(STRING_WITH_LEN(" where ")); - cur_where->print(str); + if (cur_where) + cur_where->print(str); + else + str->append(cond_value != Item::COND_FALSE ? "1" : "0"); } // group by & olap @@ -15357,10 +15374,13 @@ void st_select_lex::print(THD *thd, String *str) if (join) cur_having= join->having; - if (cur_having) + if (cur_having || having_value != Item::COND_UNDEF) { str->append(STRING_WITH_LEN(" having ")); - cur_having->print(str); + if (cur_having) + cur_having->print(str); + else + str->append(having_value != Item::COND_FALSE ? "1" : "0"); } if (order_list.elements) diff --git a/sql/sql_select.h b/sql/sql_select.h index a17d7fcb362..27ca633fdb5 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -292,7 +292,7 @@ public: bool need_tmp, hidden_group_fields; DYNAMIC_ARRAY keyuse; - Item::cond_result cond_value; + Item::cond_result cond_value, having_value; List<Item> all_fields; // to store all fields that used in query //Above list changed to use temporary table List<Item> tmp_all_fields1, tmp_all_fields2, tmp_all_fields3; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index e203d8ebd4a..27d38114885 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -640,7 +640,7 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list, /* Check that we are not using table that we are updating in a sub select */ { TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, table_list, table_list->next_global))) + if ((duplicate= unique_table(thd, table_list, table_list->next_global, 0))) { update_non_unique_table_error(table_list, "UPDATE", duplicate); my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name); @@ -867,7 +867,7 @@ reopen_tables: tl->lock_type != TL_READ_NO_INSERT) { TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, tl, table_list))) + if ((duplicate= unique_table(thd, tl, table_list, 0))) { update_non_unique_table_error(table_list, "UPDATE", duplicate); DBUG_RETURN(TRUE); @@ -1086,7 +1086,7 @@ static bool safe_update_on_fly(THD *thd, JOIN_TAB *join_tab, List<Item> *fields) { TABLE *table= join_tab->table; - if (unique_table(thd, table_ref, all_tables)) + if (unique_table(thd, table_ref, all_tables, 0)) return 0; switch (join_tab->type) { case JT_SYSTEM: |