summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_in.result8
-rw-r--r--mysql-test/r/order_by.result8
-rw-r--r--mysql-test/r/view.result37
-rw-r--r--mysql-test/t/func_in.test6
-rw-r--r--mysql-test/t/order_by.test8
-rw-r--r--mysql-test/t/view.test40
-rw-r--r--sql/item.cc26
-rw-r--r--sql/item.h10
-rw-r--r--sql/mysql_priv.h22
-rw-r--r--sql/sql_base.cc121
-rw-r--r--sql/sql_select.cc19
11 files changed, 234 insertions, 71 deletions
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 36bcc6f1711..fad9a7157e1 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -463,5 +463,11 @@ CREATE TABLE t3 (a BIGINT UNSIGNED);
INSERT INTO t3 VALUES (9223372036854775551);
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
HEX(a)
-DROP TABLE t1,t2,t3;
+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/order_by.result b/mysql-test/r/order_by.result
index e81d46c9199..0b1edfd3e8f 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -926,3 +926,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/view.result b/mysql-test/r/view.result
index 50b41e1352f..cfa4f7cbba5 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3135,4 +3135,41 @@ code COUNT(DISTINCT country)
100 2
DROP VIEW v1;
DROP TABLE t1;
+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;
+lgid clid
+1 NO
+2 YES
+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;
+lgid clid
+1 NO
+2 YES
+DROP VIEW v1;
+DROP table t1,t2;
End of 5.0 tests.
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 7ba54747d4b..f9749662ec1 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -354,6 +354,10 @@ INSERT INTO t3 VALUES (9223372036854775551);
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
-DROP TABLE t1,t2,t3;
+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/order_by.test b/mysql-test/t/order_by.test
index 012b38ff8b7..a3aa2c0081d 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -640,3 +640,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/view.test b/mysql-test/t/view.test
index 33e381af476..32776e85e85 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3058,4 +3058,44 @@ SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id);
DROP VIEW v1;
DROP TABLE t1;
+#
+# 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 257687ebaaf..c5d8a62761c 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,
- &not_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,
- &not_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)
@@ -4898,10 +4902,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
*/
@@ -5184,11 +5190,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;
}
@@ -5206,7 +5214,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 d792f95055d..2a121523423 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1851,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)
@@ -1926,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/mysql_priv.h b/sql/mysql_priv.h
index 7c9b43f23b9..cf5e9799fae 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1008,9 +1008,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,
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index ceba24eccfa..c2950eceb0e 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3468,10 +3468,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
@@ -3487,7 +3490,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;
@@ -3501,10 +3505,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);
@@ -3571,63 +3574,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)
{
@@ -3642,7 +3659,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)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 575a8a18da0..fc67ffac2fb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -456,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);
@@ -13166,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.
@@ -13189,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. */
@@ -13203,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;
@@ -13432,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,
View Lorry Controller Status