[MDEV-25397] JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS upon query with JOIN Created: 2021-04-12  Updated: 2021-04-21  Resolved: 2021-04-13

Status: Closed
Project: MariaDB Server
Component/s: JSON, Optimizer
Affects Version/s: N/A
Fix Version/s: 10.6.0

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-17399 Add support for JSON_TABLE Closed

 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)



 Comments   
Comment by Sergei Petrunia [ 2021-04-13 ]

The error is produced here in JOIN::prepare:

  
    /*
 
      Check if there are references to un-aggregated columns when computing 
      aggregate functions with implicit grouping (there is no GROUP BY).
 
    */
 
=>  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && !group_list &&                                                                                             
        !(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

Comment by Sergei Petrunia [ 2021-04-13 ]

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);

Generated at Thu Feb 08 09:37:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.