Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
SET sql_mode='ONLY_FULL_GROUP_BY'; |
CREATE TABLE t (a TEXT); |
INSERT INTO t VALUES ('{}'),('[]'); |
SELECT SUM(o) FROM t JOIN JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) jt; |
|
|
# Cleanup
|
DROP TABLE t; |
|
bb-10.6-mdev17399-hf 160bd1691 |
MariaDB [test]> SELECT SUM(o) FROM t JOIN JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) jt; |
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause |
Works on MySQL:
|
MySQL 8.0.23 |
MySQL [test]> SELECT SUM(o) FROM t JOIN JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) jt; |
+--------+ |
| SUM(o) | |
+--------+ |
| 2 |
|
+--------+ |
1 row in set (0.001 sec) |
Attachments
Issue Links
- relates to
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-
Activity
Ok a similar query which uses non-aggregated field outside JSON_TABLE doesn't fail because of this:
/* |
Need to stave the value, so we can turn off only any new non_agg_field_used
|
additions coming from the WHERE
|
*/
|
const bool saved_non_agg_field_used= select->non_agg_field_used(); |
DBUG_ENTER("setup_without_group"); |
|
thd->lex->allow_sum_func.clear_bit(select->nest_level);
|
res= setup_conds(thd, tables, leaves, conds);
|
if (thd->lex->current_select->first_cond_optimization) |
{
|
if (!res && *conds && ! thd->lex->current_select->merged_into) |
(*reserved)= (*conds)->exists2in_reserved_items();
|
else |
(*reserved)= 0;
|
}
|
|
/* it's not wrong to have non-aggregated columns in a WHERE */ |
=> select->set_non_agg_field_used(saved_non_agg_field_used);
|
The error is produced here in JOIN::prepare:
Check if there are references to un-aggregated columns when computingaggregate functions with implicit grouping (there is no GROUP BY).*/!(select_lex->master_unit()->item &&select_lex->master_unit()->item->is_in_predicate() &&select_lex->master_unit()->item->get_IN_subquery()->test_set_strategy(SUBS_MAXMIN_INJECTED)) &&select_lex->non_agg_field_used() &&select_lex->agg_func_used()){my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,ER_THD(thd, ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));DBUG_RETURN(-1);select_lex->non_agg_field_used()= true, select_lex->agg_func_used()= true