Bug Report: v14 rewriting query with group by and results in syntax error

[jbursey]

Overview of the Issue

We are in the process of upgrading from Vitess 5 to Vitess 14 and have a query encountering a failure due to what we believe is a rewritng bug. This query worked without issue in Vitess5, but now does fails with an invalid use of GROUP BY.

Relevant slack thread: https://vitess.slack.com/archives/C0PQY0PTK/p1680121160038269

Reproduction Steps

1. Create a schema

CREATE TABLE `rss_email_attempts` (
  `portal_id` bigint(20) unsigned NOT NULL,
  `id` bigint(20) unsigned NOT NULL,
  `rss_email_id` bigint(20) unsigned NOT NULL,
  `attempted_at` bigint(20) unsigned NOT NULL,
  `content_id` bigint(20) unsigned DEFAULT NULL,
  `result` tinyint(3) unsigned DEFAULT NULL COMMENT 'Maps to java `RssResult` enum, denoting the result type of an attempt',
  `task_queue_uid` varchar(60) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `stop_key` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `meta` json NOT NULL,
  PRIMARY KEY (`portal_id`,`id`),
  KEY `rss_attempt` (`portal_id`,`rss_email_id`,`attempted_at`,`result`),
  KEY `attempt_result_index` (`portal_id`,`rss_email_id`,`task_queue_uid`,`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

2. Run a query like below through a vtgate

SELECT /*+ MAX_EXECUTION_TIME(30000) */ COUNT(distinct att.task_queue_uid) AS counter FROM ( SELECT task_queue_uid, MAX(attempted_at) AS attempted_at, MAX(result) AS result FROM ( SELECT task_queue_uid, attempted_at, result FROM rss_email_attempts WHERE portal_id = 22711 AND rss_email_id = 3248408779 AND result IN (1,2,3,4,6,7,8,9,10,11,12,14,15,16,17,100,101,102,103,104,105,107,106,200,201,202,203,204) ) f GROUP BY task_queue_uid) AS att WHERE (att.attempted_at BETWEEN 1524628800000 AND 1679976000000)

3. Observe that this will get transformed and result in a syntax error

replica: vttablet: rpc error: code = InvalidArgument desc = In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column \'KEYSPACE.rss_email_attempts.task_queue_uid\'; this is incompatible with sql_mode=only_full_group_by (errno 1140) (sqlstate 42000)

Binary Version

vttablet

/opt/vitess/bin/vttablet --version
Version: 14.0.4-SNAPSHOT (Git revision 240f947fa9d32cc05c0e074d4693d007471aa8d4 branch 'HEAD') built on Mon Mar 27 16:17:19 UTC 2023 by root@buildkitsandbox using go1.18.7 linux/amd64

vtgate

Version: 14.0.4-SNAPSHOT (Git revision 240f947fa9d32cc05c0e074d4693d007471aa8d4 branch 'HEAD') built on Mon Mar 27 16:17:19 UTC 2023 by root@buildkitsandbox using go1.18.7 linux/amd64

Operating System and Environment details

os

NAME="CentOS Stream"
VERSION="8"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Stream 8"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://centos.org/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_SUPPORT_PRODUCT_VERSION="CentOS Stream"

kernel

Linux 5.15.86-hs36.el8.x86_64

arch

Linux 5.15.86-hs36.el8.x86_64 #1 SMP Thu Mar 2 01:02:06 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

Log Fragments

Full error

.replica: vttablet: rpc error: code = InvalidArgument desc = In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column \'KEYSPACE.rss_email_attempts.task_queue_uid\'; this is incompatible with sql_mode=only_full_group_by (errno 1140) (sqlstate 42000): Sql: \"select /*+ MAX_EXECUTION_TIME(30000) */ COUNT(distinct att.task_queue_uid) as counter from (select task_queue_uid, MAX(attempted_at) as attempted_at, MAX(result) as result from (select task_queue_uid, attempted_at, result from rss_email_attempts where portal_id = :vtg1 and rss_email_id = :vtg2 and result in ::vtg3 having MAX(attempted_at) between :vtg4 and :vtg5) as f group by task_queue_uid) as att