summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_by.result31
-rw-r--r--mysql-test/r/view.result55
-rw-r--r--mysql-test/t/group_by.test39
-rw-r--r--mysql-test/t/view.test53
-rw-r--r--sql/item.cc1
-rw-r--r--sql/item.h6
-rw-r--r--sql/item_buff.cc4
-rw-r--r--sql/item_func.cc2
-rw-r--r--sql/sql_view.cc75
-rw-r--r--sql/sql_yacc.yy15
10 files changed, 223 insertions, 58 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 7e1aebac872..edad116028f 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -723,27 +723,6 @@ WHERE hostname LIKE '%aol%'
hostname no
cache-dtc-af05.proxy.aol.com 1
DROP TABLE t1;
-create table t1 (c1 char(3), c2 char(3));
-create table t2 (c3 char(3), c4 char(3));
-insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
-insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
-select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
-group by c2;
-c2
-aaa
-aaa
-Warnings:
-Warning 1052 Column 'c2' in group statement is ambiguous
-show warnings;
-Level Code Message
-Warning 1052 Column 'c2' in group statement is ambiguous
-select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
-group by t1.c1;
-c2
-aaa
-show warnings;
-Level Code Message
-drop table t1, t2;
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (1,2), (1,3);
SELECT a, b FROM t1 GROUP BY 'const';
@@ -762,3 +741,13 @@ SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
f id
20050501123000 1
DROP TABLE t1;
+CREATE TABLE t1 (id varchar(20) NOT NULL);
+INSERT INTO t1 VALUES ('trans1'), ('trans2');
+CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
+INSERT INTO t2 VALUES ('trans1', 'a problem');
+SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
+FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
+COUNT(DISTINCT(t1.id)) comment
+1 NULL
+1 a problem
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index b69787dbc87..cf3a1739710 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1771,3 +1771,58 @@ f1 f2
3 3
drop view v1;
drop table t1,t2,t3;
+create view v1 as select '\\','\\shazam';
+select * from v1;
+\ \shazam
+\ \shazam
+drop view v1;
+create view v1 as select '\'','\shazam';
+select * from v1;
+' shazam
+' shazam
+drop view v1;
+create view v1 as select 'k','K';
+select * from v1;
+k My_exp_K
+k K
+drop view v1;
+create table t1 (s1 int);
+create view v1 as select s1, 's1' from t1;
+select * from v1;
+s1 My_exp_s1
+drop view v1;
+create view v1 as select 's1', s1 from t1;
+select * from v1;
+My_exp_s1 s1
+drop view v1;
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+select * from v1;
+My_exp_1_s1 s1 My_exp_s1
+drop view v1;
+create view v1 as select 1 as My_exp_s1, 's1', s1 from t1;
+select * from v1;
+My_exp_s1 My_exp_1_s1 s1
+drop view v1;
+create view v1 as select 1 as s1, 's1', 's1' from t1;
+select * from v1;
+s1 My_exp_s1 My_exp_1_s1
+drop view v1;
+create view v1 as select 's1', 's1', 1 as s1 from t1;
+select * from v1;
+My_exp_1_s1 My_exp_s1 s1
+drop view v1;
+create view v1 as select s1, 's1', 's1' from t1;
+select * from v1;
+s1 My_exp_s1 My_exp_1_s1
+drop view v1;
+create view v1 as select 's1', 's1', s1 from t1;
+select * from v1;
+My_exp_1_s1 My_exp_s1 s1
+drop view v1;
+create view v1 as select 1 as s1, 's1', s1 from t1;
+ERROR 42S21: Duplicate column name 's1'
+create view v1 as select 's1', s1, 1 as s1 from t1;
+ERROR 42S21: Duplicate column name 's1'
+drop table t1;
+create view v1(k, K) as select 1,2;
+ERROR 42S21: Duplicate column name 'K'
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index f0546daf463..be8bdbe892e 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -543,30 +543,6 @@ SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
DROP TABLE t1;
#
-# Bug#11211: Ambiguous column reference in GROUP BY.
-#
-
-create table t1 (c1 char(3), c2 char(3));
-create table t2 (c3 char(3), c4 char(3));
-insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
-insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
-
-# query with ambiguous column reference 'c2'
---disable_ps_protocol
-select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
-group by c2;
-show warnings;
---enable_ps_protocol
-
-# this query has no ambiguity
-select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
-group by t1.c1;
-
-show warnings;
-
-drop table t1, t2;
-
-#
# Test for bug #8614: GROUP BY 'const' with DISTINCT
#
@@ -589,3 +565,18 @@ INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
DROP TABLE t1;
+
+#
+# Test for bug #11295: GROUP BY a BLOB column with COUNT(DISTINCT column1)
+# when the BLOB column takes NULL values
+#
+
+CREATE TABLE t1 (id varchar(20) NOT NULL);
+INSERT INTO t1 VALUES ('trans1'), ('trans2');
+CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
+INSERT INTO t2 VALUES ('trans1', 'a problem');
+
+SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
+ FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
+
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 27cd9404cbe..c97813e39ff 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1612,3 +1612,56 @@ insert into t3 select * from v1 order by 1;
select * from t3;
drop view v1;
drop table t1,t2,t3;
+
+#
+# Generation unique names for columns, and correct names check (BUG#7448)
+#
+# names with ' and \
+create view v1 as select '\\','\\shazam';
+select * from v1;
+drop view v1;
+create view v1 as select '\'','\shazam';
+select * from v1;
+drop view v1;
+# autogenerated names differ by case only
+create view v1 as select 'k','K';
+select * from v1;
+drop view v1;
+create table t1 (s1 int);
+# same autogenerated names
+create view v1 as select s1, 's1' from t1;
+select * from v1;
+drop view v1;
+create view v1 as select 's1', s1 from t1;
+select * from v1;
+drop view v1;
+# set name as one of expected autogenerated
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+select * from v1;
+drop view v1;
+create view v1 as select 1 as My_exp_s1, 's1', s1 from t1;
+select * from v1;
+drop view v1;
+# set name conflict with autogenerated names
+create view v1 as select 1 as s1, 's1', 's1' from t1;
+select * from v1;
+drop view v1;
+create view v1 as select 's1', 's1', 1 as s1 from t1;
+select * from v1;
+drop view v1;
+# underlying field name conflict with autogenerated names
+create view v1 as select s1, 's1', 's1' from t1;
+select * from v1;
+drop view v1;
+create view v1 as select 's1', 's1', s1 from t1;
+select * from v1;
+drop view v1;
+# underlying field name conflict with set name
+-- error 1060
+create view v1 as select 1 as s1, 's1', s1 from t1;
+-- error 1060
+create view v1 as select 's1', s1, 1 as s1 from t1;
+drop table t1;
+# set names differ by case only
+-- error 1060
+create view v1(k, K) as select 1,2;
diff --git a/sql/item.cc b/sql/item.cc
index 20e411afceb..e645d0db945 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -314,6 +314,7 @@ void *Item::operator new(size_t size, Item *reuse, uint *rsize)
Item::Item():
rsize(0), name(0), orig_name(0), name_length(0), fixed(0),
+ is_autogenerated_name(TRUE),
collation(&my_charset_bin, DERIVATION_COERCIBLE)
{
marker= 0;
diff --git a/sql/item.h b/sql/item.h
index 856434ab626..c912ad3f0a7 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -269,6 +269,8 @@ public:
my_bool unsigned_flag;
my_bool with_sum_func;
my_bool fixed; /* If item fixed with fix_fields */
+ my_bool is_autogenerated_name; /* indicate was name of this Item
+ autogenerated or set by user */
DTCollation collation;
// alloc & destruct is done as start of select using sql_alloc
@@ -288,7 +290,7 @@ public:
name=0;
#endif
} /*lint -e1509 */
- void set_name(const char *str,uint length, CHARSET_INFO *cs);
+ void set_name(const char *str, uint length, CHARSET_INFO *cs);
void rename(char *new_name);
void init_make_field(Send_field *tmp_field,enum enum_field_types type);
virtual void cleanup();
@@ -1166,7 +1168,7 @@ public:
collation.set(cs, dv);
str_value.set_or_copy_aligned(str,length,cs);
max_length= str_value.numchars()*cs->mbmaxlen;
- set_name(name_par,0,cs);
+ set_name(name_par, 0, cs);
decimals=NOT_FIXED_DEC;
// it is constant => can be used without fix_fields (and frequently used)
fixed= 1;
diff --git a/sql/item_buff.cc b/sql/item_buff.cc
index 7e7bbe51657..6652aaafb57 100644
--- a/sql/item_buff.cc
+++ b/sql/item_buff.cc
@@ -60,8 +60,8 @@ bool Cached_item_str::cmp(void)
String *res;
bool tmp;
- res=item->val_str(&tmp_value);
- res->length(min(res->length(), value.alloced_length()));
+ if ((res=item->val_str(&tmp_value)))
+ res->length(min(res->length(), value.alloced_length()));
if (null_value != item->null_value)
{
if ((null_value= item->null_value))
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 4166605ae11..57f68bbc2a0 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4620,7 +4620,7 @@ Item *get_system_var(THD *thd, enum_var_type var_type, const char *var_name,
if (!(item=var->item(thd, var_type, &null_lex_string)))
return 0; // Impossible
thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
- item->set_name(item_name, 0, system_charset_info); // Will use original name
+ item->set_name(item_name, 0, system_charset_info); // Will use original name
return item;
}
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 5152619fd85..0b351407c13 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -36,6 +36,61 @@ TYPELIB updatable_views_with_limit_typelib=
/*
+ Make a unique name for an anonymous view column
+ SYNOPSIS
+ target reference to the item for which a new name has to be made
+ item_list list of items within which we should check uniqueness of
+ the created name
+ last_element the last element of the list above
+
+ NOTE
+ Unique names are generated by adding 'My_exp_' to the old name of the
+ column. In case the name that was created this way already exists, we
+ add a numeric postfix to its end (i.e. "1") and increase the number
+ until the name becomes unique. If the generated name is longer than
+ NAME_LEN, it is truncated.
+*/
+
+static void make_unique_view_field_name(Item *target,
+ List<Item> &item_list,
+ Item *last_element)
+{
+ char *name= (target->orig_name ?
+ target->orig_name :
+ target->name);
+ uint name_len;
+ uint attempt= 0;
+ char buff[NAME_LEN+1];
+ for (;; attempt++)
+ {
+ Item *check;
+ List_iterator_fast<Item> itc(item_list);
+ bool ok= TRUE;
+
+ if (attempt)
+ name_len= my_snprintf(buff, NAME_LEN, "My_exp_%d_%s", attempt, name);
+ else
+ name_len= my_snprintf(buff, NAME_LEN, "My_exp_%s", name);
+
+ do
+ {
+ check= itc++;
+ if (check != target &&
+ my_strcasecmp(system_charset_info, buff, check->name) == 0)
+ {
+ ok= FALSE;
+ break;
+ }
+ } while (check != last_element);
+ if (ok)
+ break;
+ }
+
+ target->orig_name= target->name;
+ target->set_name(buff, name_len, system_charset_info);
+}
+
+/*
Creating/altering VIEW procedure
SYNOPSIS
@@ -240,24 +295,36 @@ bool mysql_create_view(THD *thd,
goto err;
}
while ((item= it++, name= nm++))
+ {
item->set_name(name->str, name->length, system_charset_info);
+ item->is_autogenerated_name= FALSE;
+ }
}
/* Test absence of duplicates names */
{
Item *item;
List_iterator_fast<Item> it(select_lex->item_list);
- it++;
while ((item= it++))
{
Item *check;
List_iterator_fast<Item> itc(select_lex->item_list);
+ /* treat underlying fields like set by user names */
+ if (item->real_item()->type() == Item::FIELD_ITEM)
+ item->is_autogenerated_name= FALSE;
while ((check= itc++) && check != item)
{
- if (strcmp(item->name, check->name) == 0)
+ if (my_strcasecmp(system_charset_info, item->name, check->name) == 0)
{
- my_error(ER_DUP_FIELDNAME, MYF(0), item->name);
- DBUG_RETURN(TRUE);
+ if (item->is_autogenerated_name)
+ make_unique_view_field_name(item, select_lex->item_list, item);
+ else if (check->is_autogenerated_name)
+ make_unique_view_field_name(check, select_lex->item_list, item);
+ else
+ {
+ my_error(ER_DUP_FIELDNAME, MYF(0), item->name);
+ DBUG_RETURN(TRUE);
+ }
}
}
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 00ef8b581f1..1b9176e2744 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4107,7 +4107,10 @@ select_item:
if (add_item_to_list(YYTHD, $2))
YYABORT;
if ($4.str)
- $2->set_name($4.str,$4.length,system_charset_info);
+ {
+ $2->set_name($4.str, $4.length, system_charset_info);
+ $2->is_autogenerated_name= FALSE;
+ }
else if (!$2->name) {
char *str = $1;
if (str[-1] == '`')
@@ -4913,9 +4916,12 @@ udf_expr:
remember_name expr remember_end select_alias
{
if ($4.str)
- $2->set_name($4.str,$4.length,system_charset_info);
+ {
+ $2->set_name($4.str, $4.length, system_charset_info);
+ $2->is_autogenerated_name= FALSE;
+ }
else
- $2->set_name($1,(uint) ($3 - $1), YYTHD->charset());
+ $2->set_name($1, (uint) ($3 - $1), YYTHD->charset());
$$= $2;
}
;
@@ -5691,7 +5697,8 @@ procedure_item:
if (add_proc_to_list(lex->thd, $2))
YYABORT;
if (!$2->name)
- $2->set_name($1,(uint) ((char*) lex->tok_end - $1), YYTHD->charset());
+ $2->set_name($1,(uint) ((char*) lex->tok_end - $1),
+ YYTHD->charset());
}
;
View Lorry Controller Status