summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_gconcat.result4
-rw-r--r--mysql-test/r/func_group.result24
-rw-r--r--mysql-test/r/subselect.result4
-rw-r--r--mysql-test/t/func_group.test14
-rw-r--r--sql/opt_sum.cc4
-rw-r--r--sql/sql_select.cc20
6 files changed, 55 insertions, 15 deletions
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 2c79b8f8ab1..db0125b7d4f 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -559,14 +559,14 @@ COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
DROP TABLE t1,t2;
select * from (select group_concat('c') from DUAL) t;
group_concat('c')
-NULL
+c
create table t1 ( a int not null default 0);
select * from (select group_concat(a) from t1) t2;
group_concat(a)
NULL
select group_concat('x') UNION ALL select 1;
group_concat('x')
-NULL
+x
1
drop table t1;
CREATE TABLE t1 (id int, a varchar(9));
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 932ef133087..04f6ebe6398 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -794,7 +794,7 @@ min(7)
NULL
select min(7) from DUAL;
min(7)
-NULL
+7
explain select min(7) from t2m join t1m;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
@@ -809,7 +809,7 @@ max(7)
NULL
select max(7) from DUAL;
max(7)
-NULL
+7
explain select max(7) from t2m join t1m;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
@@ -848,7 +848,7 @@ min(7)
NULL
select min(7) from DUAL;
min(7)
-NULL
+7
explain select min(7) from t2i join t1i;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
@@ -864,7 +864,7 @@ max(7)
NULL
select max(7) from DUAL;
max(7)
-NULL
+7
explain select max(7) from t2i join t1i;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
@@ -942,3 +942,19 @@ EXPLAIN SELECT MAX(b) FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
+SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT t1.b))
+1
+1
+SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT 12))
+1
+1
+SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12),
+COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
+GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
+AVG(2) BIT_AND(2) BIT_OR(2) BIT_XOR(2) COUNT(*) COUNT(12) COUNT(DISTINCT 12) MIN(2) MAX(2) STD(2) VARIANCE(2) SUM(2) GROUP_CONCAT(2) GROUP_CONCAT(DISTINCT 2)
+2.0000 2 2 2 1 1 1 2 2 0.0000 0.0000 2 2 2
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 983ad628425..e6b86980fbb 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1001,7 +1001,7 @@ INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
select * from t1;
i
-1
+2
drop table t1;
CREATE TABLE t1 (a int(1));
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
@@ -1193,7 +1193,7 @@ UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
ERROR 42S02: Unknown table 't' in field list
select * from t1;
i
-1
+3
drop table t1;
CREATE TABLE t1 (
id int(11) default NULL
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index f8a3ed0f25e..18cb5d0a430 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -617,4 +617,18 @@ SELECT MAX(b) FROM t1;
EXPLAIN SELECT MAX(b) FROM t1;
DROP TABLE t1;
+#
+# Bug #16792 query with subselect, join, and group not returning proper values
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
+
+SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
+# an attempt to test all aggregate function with no table.
+SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12),
+ COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
+ GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
+DROP TABLE t1;
+
# End of 4.1 tests
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index b53fbfd3f80..bc98c96b5a8 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -182,7 +182,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds)
Type of range for the key part for this field will be
returned in range_fl.
*/
- if ((outer_tables & table->map) ||
+ if (table->file->inited || (outer_tables & table->map) ||
!find_key_for_maxmin(0, &ref, item_field->field, conds,
&range_fl, &prefix_len))
{
@@ -269,7 +269,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds)
Type of range for the key part for this field will be
returned in range_fl.
*/
- if ((outer_tables & table->map) ||
+ if (table->file->inited || (outer_tables & table->map) ||
!find_key_for_maxmin(1, &ref, item_field->field, conds,
&range_fl, &prefix_len))
{
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 605ef49bb07..6a3b9fe54cf 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1131,7 +1131,7 @@ JOIN::exec()
DBUG_VOID_RETURN;
}
- if (!tables_list)
+ if (!tables_list && (tables || !select_lex->with_sum_func))
{ // Only test of functions
if (select_options & SELECT_DESCRIBE)
select_describe(this, FALSE, FALSE, FALSE,
@@ -1170,7 +1170,12 @@ JOIN::exec()
thd->examined_row_count= 0;
DBUG_VOID_RETURN;
}
- thd->limit_found_rows= thd->examined_row_count= 0;
+ /*
+ don't reset the found rows count if there're no tables
+ as FOUND_ROWS() may be called.
+ */
+ if (tables)
+ thd->limit_found_rows= thd->examined_row_count= 0;
if (zero_result_cause)
{
@@ -1209,7 +1214,8 @@ JOIN::exec()
having= tmp_having;
select_describe(this, need_tmp,
order != 0 && !skip_sort_order,
- select_distinct);
+ select_distinct,
+ !tables ? "No tables used" : NullS);
DBUG_VOID_RETURN;
}
@@ -6028,9 +6034,12 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure)
else
end_select=end_send;
}
- join->join_tab[join->tables-1].next_select=end_select;
+ if (join->tables)
+ {
+ join->join_tab[join->tables-1].next_select=end_select;
- join_tab=join->join_tab+join->const_tables;
+ join_tab=join->join_tab+join->const_tables;
+ }
join->send_records=0;
if (join->tables == join->const_tables)
{
@@ -6048,6 +6057,7 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure)
}
else
{
+ DBUG_ASSERT(join_tab);
error= sub_select(join,join_tab,0);
if (error >= 0)
error= sub_select(join,join_tab,1);
View Lorry Controller Status