vtgate: group by json_extract() value fails to aggregate

[derekperkins]

Overview of the Issue

In a sharded keyspace, grouping by a json extracted column fails. I wouldn't expect that the full json field would be returned out to vtgate, just the extracted json value, but they are both returned, causing a group by error.

vttablet: rpc error: code = InvalidArgument desc = Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'keywords.searches__extractor__msgs.attributes' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (errno 1055) (sqlstate 42000) (CallerID: nozzleadmin): Sql: "/* ApplicationName=GoLand 2021.2.2 */ select json_extract(attributes, :vtg1) as error, count(*), weight_string(json_extract(attributes, :vtg1)) from searches__extractor__msgs group by errororder byerror asc", BindVars: {#maxLimit: "type:INT64 value:\"502\""vtg1: "type:VARBINARY value:\"$.last_error\""}

Reproduction Steps

Steps to reproduce this issue, example:

1. Deploy the following vschema:

   {
     "sharded": true,
     "vindexes": {
       "hash": {
         "type": "hash"
       },
     "tables": {
       "tbl_with_json": {
         "column_vindexes": [
           {
             "column": "user_id",
             "name": "hash"
           }
         ]
       }
     }
   }

2. Deploy the following schema:

   create table tbl_with_json(user_id bigint, some_json json, primary key(user_id));
   
   insert into tbl_with_json values ((1, '{"string_value": "str1"}'), (2, '{"string_value": "str2"}'), (3, '{"string_value": "str2"}'));

3. Run SELECT json_extract(attributes, '$.string_value') as val, count(*) FROM tbl_with_json GROUP BY val

4. View error

Binary version

vtgate: 10.0.2
vttablet: 11.0.0

Operating system and Environment details

GKE 1.20.9

[GuptaManan100]

This Issue tracks the regression that was introduced - #8855. The regression has been fixed and backported via #8856, #8865 and #8866.
However, the v3 planner does not support grouping for varchar columns. It is however supported in the Gen4 planner. This was tracked by the issue - #7707

[derekperkins]

Thanks for the explanation