summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_by.result9
-rw-r--r--mysql-test/r/olap.result13
-rw-r--r--mysql-test/r/ps_1general.result12
-rw-r--r--mysql-test/r/ps_2myisam.result6
-rw-r--r--mysql-test/r/ps_3innodb.result6
-rw-r--r--mysql-test/r/ps_4heap.result6
-rw-r--r--mysql-test/r/ps_5merge.result12
-rw-r--r--mysql-test/t/group_by.test10
-rw-r--r--mysql-test/t/olap.test13
-rw-r--r--sql/item.cc9
-rw-r--r--sql/item_cmpfunc.h2
-rw-r--r--sql/sql_select.cc17
-rw-r--r--sql/sql_show.cc34
-rw-r--r--tests/mysql_client_test.c6
14 files changed, 86 insertions, 69 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 8287a042d60..4ad28091164 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -764,3 +764,12 @@ select date(left(f1+0,8)) from t1 group by 1;
date(left(f1+0,8))
2005-06-06
drop table t1;
+create table t1(f1 varchar(5) key);
+insert into t1 values (1),(2);
+select sql_buffer_result max(f1) is null from t1;
+max(f1) is null
+0
+select sql_buffer_result max(f1)+1 from t1;
+max(f1)+1
+3
+drop table t1;
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result
index 7178895cf80..65f7c649624 100644
--- a/mysql-test/r/olap.result
+++ b/mysql-test/r/olap.result
@@ -516,3 +516,16 @@ a b c count
1 NULL NULL 2
NULL NULL NULL 2
DROP TABLE t1;
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
+a a + 1 COUNT(*)
+1 2 1
+2 3 1
+NULL NULL 2
+SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
+a LENGTH(a) COUNT(*)
+1 1 1
+2 1 1
+NULL NULL 2
+DROP TABLE t1;
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index c4ccdf9eb34..49e858eca56 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -470,12 +470,12 @@ def id 8 3 1 N 32929 0 63
def select_type 253 19 6 N 1 31 8
def table 253 64 2 Y 0 31 8
def type 253 10 3 Y 0 31 8
-def possible_keys 252 4096 0 Y 0 31 8
+def possible_keys 253 4096 0 Y 0 31 8
def key 253 64 0 Y 0 31 8
def key_len 8 3 0 Y 32928 0 63
-def ref 252 1024 0 Y 0 31 8
+def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 252 255 14 N 1 31 8
+def Extra 253 255 14 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort
SET @arg00=1 ;
@@ -486,12 +486,12 @@ def id 8 3 1 N 32929 0 63
def select_type 253 19 6 N 1 31 8
def table 253 64 2 Y 0 31 8
def type 253 10 5 Y 0 31 8
-def possible_keys 252 4096 7 Y 0 31 8
+def possible_keys 253 4096 7 Y 0 31 8
def key 253 64 7 Y 0 31 8
def key_len 8 3 1 Y 32928 0 63
-def ref 252 1024 0 Y 0 31 8
+def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 252 255 27 N 1 31 8
+def Extra 253 255 27 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort
drop table if exists t2;
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 6ef61b05577..4b655cfb854 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -1153,12 +1153,12 @@ def id 8 3 1 N 32929 0 63
def select_type 253 19 6 N 1 31 8
def table 253 64 2 Y 0 31 8
def type 253 10 3 Y 0 31 8
-def possible_keys 252 4096 0 Y 0 31 8
+def possible_keys 253 4096 0 Y 0 31 8
def key 253 64 0 Y 0 31 8
def key_len 8 3 0 Y 32928 0 63
-def ref 252 1024 0 Y 0 31 8
+def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 252 255 0 N 1 31 8
+def Extra 253 255 0 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
drop table if exists t2 ;
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 96047ac3182..4d2a62887d6 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -1153,12 +1153,12 @@ def id 8 3 1 N 32929 0 63
def select_type 253 19 6 N 1 31 8
def table 253 64 2 Y 0 31 8
def type 253 10 3 Y 0 31 8
-def possible_keys 252 4096 0 Y 0 31 8
+def possible_keys 253 4096 0 Y 0 31 8
def key 253 64 0 Y 0 31 8
def key_len 8 3 0 Y 32928 0 63
-def ref 252 1024 0 Y 0 31 8
+def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 252 255 0 N 1 31 8
+def Extra 253 255 0 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
test_sequence
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index bff4b6a5ad8..a4919b664c4 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -1154,12 +1154,12 @@ def id 8 3 1 N 32929 0 63
def select_type 253 19 6 N 1 31 8
def table 253 64 2 Y 0 31 8
def type 253 10 3 Y 0 31 8
-def possible_keys 252 4096 0 Y 0 31 8
+def possible_keys 253 4096 0 Y 0 31 8
def key 253 64 0 Y 0 31 8
def key_len 8 3 0 Y 32928 0 63
-def ref 252 1024 0 Y 0 31 8
+def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 252 255 0 N 1 31 8
+def Extra 253 255 0 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
test_sequence
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 5ed6c10a47c..f98cc1b3cdf 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -1196,12 +1196,12 @@ def id 8 3 1 N 32929 0 63
def select_type 253 19 6 N 1 31 8
def table 253 64 2 Y 0 31 8
def type 253 10 3 Y 0 31 8
-def possible_keys 252 4096 0 Y 0 31 8
+def possible_keys 253 4096 0 Y 0 31 8
def key 253 64 0 Y 0 31 8
def key_len 8 3 0 Y 32928 0 63
-def ref 252 1024 0 Y 0 31 8
+def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 252 255 0 N 1 31 8
+def Extra 253 255 0 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
test_sequence
@@ -4210,12 +4210,12 @@ def id 8 3 1 N 32929 0 63
def select_type 253 19 6 N 1 31 8
def table 253 64 2 Y 0 31 8
def type 253 10 3 Y 0 31 8
-def possible_keys 252 4096 0 Y 0 31 8
+def possible_keys 253 4096 0 Y 0 31 8
def key 253 64 0 Y 0 31 8
def key_len 8 3 0 Y 32928 0 63
-def ref 252 1024 0 Y 0 31 8
+def ref 253 1024 0 Y 0 31 8
def rows 8 10 1 Y 32928 0 63
-def Extra 252 255 0 N 1 31 8
+def Extra 253 255 0 N 1 31 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
test_sequence
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 815da66c717..f14fab2d30e 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -600,4 +600,14 @@ insert into t1 values('2005-06-06');
select date(left(f1+0,8)) from t1 group by 1;
drop table t1;
+#
+# BUG#12695: Item_func_isnull::update_used_tables
+# did not update const_item_cache
+#
+create table t1(f1 varchar(5) key);
+insert into t1 values (1),(2);
+select sql_buffer_result max(f1) is null from t1;
+select sql_buffer_result max(f1)+1 from t1;
+drop table t1;
+
# End of 4.1 tests
diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test
index c9a16b897c6..76c62d14621 100644
--- a/mysql-test/t/olap.test
+++ b/mysql-test/t/olap.test
@@ -250,4 +250,17 @@ SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
DROP TABLE t1;
+#
+# Bug #11885: derived table specified by a subquery with
+# ROLLUP over expressions on not nullable group by attributes
+#
+
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
+SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
+
+DROP TABLE t1;
+
# End of 4.1 tests
diff --git a/sql/item.cc b/sql/item.cc
index 7aaa678ddb0..010189c321c 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1973,14 +1973,7 @@ void Item::make_field(Send_field *tmp_field)
void Item_empty_string::make_field(Send_field *tmp_field)
{
- enum_field_types type = FIELD_TYPE_VAR_STRING;
- if (max_length >= 16777216)
- type = FIELD_TYPE_LONG_BLOB;
- else if (max_length >= 65536)
- type = FIELD_TYPE_MEDIUM_BLOB;
- else if (max_length >= 256)
- type = FIELD_TYPE_BLOB;
- init_make_field(tmp_field, type);
+ init_make_field(tmp_field,FIELD_TYPE_VAR_STRING);
}
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 03ab9c5b34d..741ae90108a 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -826,7 +826,7 @@ public:
else
{
args[0]->update_used_tables();
- if (!(used_tables_cache=args[0]->used_tables()))
+ if ((const_item_cache= !(used_tables_cache= args[0]->used_tables())))
{
/* Remember if the value is always NULL or never NULL */
cached_value= (longlong) args[0]->is_null();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e7af2a1aa75..3de546fd619 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9277,6 +9277,8 @@ void free_underlaid_joins(THD *thd, SELECT_LEX *select)
The function replaces occurrences of group by fields in expr
by ref objects for these fields unless they are under aggregate
functions.
+ The function also corrects value of the the maybe_null attribute
+ for the items of all subexpressions containing group by fields.
IMPLEMENTATION
The function recursively traverses the tree of the expr expression,
@@ -9287,6 +9289,9 @@ void free_underlaid_joins(THD *thd, SELECT_LEX *select)
This substitution is needed GROUP BY queries with ROLLUP if
SELECT list contains expressions over group by attributes.
+ TODO: Some functions are not null-preserving. For those functions
+ updating of the maybe_null attribute is an overkill.
+
EXAMPLES
SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP
SELECT SUM(a)+a FROM t1 GROUP BY a WITH ROLLUP
@@ -9307,6 +9312,7 @@ static bool change_group_ref(THD *thd, Item_func *expr, ORDER *group_list,
arg != arg_end; arg++)
{
Item *item= *arg;
+ bool arg_changed= FALSE;
if (item->type() == Item::FIELD_ITEM || item->type() == Item::REF_ITEM)
{
ORDER *group_tmp;
@@ -9318,15 +9324,20 @@ static bool change_group_ref(THD *thd, Item_func *expr, ORDER *group_list,
if(!(new_item= new Item_ref(group_tmp->item, 0, item->name)))
return 1; // fatal_error is set
thd->change_item_tree(arg, new_item);
- *changed= TRUE;
+ arg_changed= TRUE;
}
}
}
else if (item->type() == Item::FUNC_ITEM)
{
- if (change_group_ref(thd, (Item_func *) item, group_list, changed))
+ if (change_group_ref(thd, (Item_func *) item, group_list, &arg_changed))
return 1;
}
+ if (arg_changed)
+ {
+ expr->maybe_null= 1;
+ *changed= TRUE;
+ }
}
}
return 0;
@@ -9389,7 +9400,7 @@ bool JOIN::rollup_init()
}
if (item->type() == Item::FUNC_ITEM)
{
- bool changed= 0;
+ bool changed= FALSE;
if (change_group_ref(thd, (Item_func *) item, group_list, &changed))
return 1;
/*
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 647dfe2ac4d..7cefd89c23c 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -638,33 +638,6 @@ int mysqld_extend_show_tables(THD *thd,const char *db,const char *wild)
DBUG_RETURN(0);
}
-/*
- returns the length of the longest type on the given table.
-
- This is used so that show fields will return the data using the proper
- lengths instead of forcing columns such as type to always return with a
- given length.
-*/
-
-uint get_longest_type_in_table(TABLE *table, const char *wild)
-{
- Field **ptr,*field;
- char tmp[MAX_FIELD_WIDTH];
- uint max_len= 0;
-
- for (ptr=table->field; (field= *ptr); ptr++)
- {
- if (!wild || !wild[0] ||
- !wild_case_compare(system_charset_info, field->field_name,wild))
- {
- String type(tmp,sizeof(tmp), system_charset_info);
- field->sql_type(type);
- max_len= max(max_len, type.length());
- }
- }
- return max_len;
-}
-
/***************************************************************************
** List all columns in a table_list->real_name
@@ -694,14 +667,9 @@ mysqld_show_fields(THD *thd, TABLE_LIST *table_list,const char *wild,
#ifndef NO_EMBEDDED_ACCESS_CHECKS
(void) get_table_grant(thd, table_list);
#endif
-
- /* we scan for the longest since long enum types can exceed 40 */
- uint max_len = get_longest_type_in_table(table, wild);
-
List<Item> field_list;
field_list.push_back(new Item_empty_string("Field",NAME_LEN));
- field_list.push_back(new Item_empty_string("Type",
- max_len > 40 ? max_len : 40));
+ field_list.push_back(new Item_empty_string("Type", 40));
if (verbose)
field_list.push_back(new Item_empty_string("Collation",40));
field_list.push_back(new Item_empty_string("Null",1));
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index ffcf5b6f34d..eadbd37f8f6 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -7182,7 +7182,7 @@ static void test_explain_bug()
verify_prepare_field(result, 3, "type", "", MYSQL_TYPE_VAR_STRING,
"", "", "", 10, 0);
- verify_prepare_field(result, 4, "possible_keys", "", MYSQL_TYPE_BLOB,
+ verify_prepare_field(result, 4, "possible_keys", "", MYSQL_TYPE_VAR_STRING,
"", "", "", NAME_LEN*64, 0);
verify_prepare_field(result, 5, "key", "", MYSQL_TYPE_VAR_STRING,
@@ -7195,13 +7195,13 @@ static void test_explain_bug()
(mysql_get_server_version(mysql) <= 50000 ? 3 : 4096),
0);
- verify_prepare_field(result, 7, "ref", "", MYSQL_TYPE_BLOB,
+ verify_prepare_field(result, 7, "ref", "", MYSQL_TYPE_VAR_STRING,
"", "", "", NAME_LEN*16, 0);
verify_prepare_field(result, 8, "rows", "", MYSQL_TYPE_LONGLONG,
"", "", "", 10, 0);
- verify_prepare_field(result, 9, "Extra", "", MYSQL_TYPE_BLOB,
+ verify_prepare_field(result, 9, "Extra", "", MYSQL_TYPE_VAR_STRING,
"", "", "", 255, 0);
mysql_free_result(result);
View Lorry Controller Status