Description:
Hi,
In the following test case, there are two equivalent queries: one is a normal SELECT, and another is a prepared statement. However, the normal SELECT triggers an error, but the prepared statement returns a result.
```
CREATE TABLE t0(c0 TEXT ) ;
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
INSERT INTO t0(c0) VALUES('');
INSERT INTO t1(c0) VALUES('');
SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(-112098196 AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)); -- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database7o.t0.c0' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SET @a = -112098196;
PREPARE prepare_query FROM 'SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(? AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0))';
EXECUTE prepare_query USING @a; -- NULL
DEALLOCATE PREPARE prepare_query;
```
How to repeat:
```
CREATE TABLE t0(c0 TEXT ) ;
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
INSERT INTO t0(c0) VALUES('');
INSERT INTO t1(c0) VALUES('');
SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(-112098196 AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)); -- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database7o.t0.c0' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SET @a = -112098196;
PREPARE prepare_query FROM 'SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(? AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0))';
EXECUTE prepare_query USING @a; -- NULL
DEALLOCATE PREPARE prepare_query;
```
Suggested fix:
These two queries should have the same behaviour.
Description: Hi, In the following test case, there are two equivalent queries: one is a normal SELECT, and another is a prepared statement. However, the normal SELECT triggers an error, but the prepared statement returns a result. ``` CREATE TABLE t0(c0 TEXT ) ; CREATE TABLE IF NOT EXISTS t1 LIKE t0; INSERT INTO t0(c0) VALUES(''); INSERT INTO t1(c0) VALUES(''); SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(-112098196 AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)); -- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database7o.t0.c0' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SET @a = -112098196; PREPARE prepare_query FROM 'SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(? AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0))'; EXECUTE prepare_query USING @a; -- NULL DEALLOCATE PREPARE prepare_query; ``` How to repeat: ``` CREATE TABLE t0(c0 TEXT ) ; CREATE TABLE IF NOT EXISTS t1 LIKE t0; INSERT INTO t0(c0) VALUES(''); INSERT INTO t1(c0) VALUES(''); SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(-112098196 AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)); -- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database7o.t0.c0' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SET @a = -112098196; PREPARE prepare_query FROM 'SELECT ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0)) AS ref0 FROM t1 LEFT JOIN t0 ON (NOT ((t0.c0) || ((CAST(? AS DECIMAL)) IS TRUE))) GROUP BY ( EXISTS (SELECT 1)) >= (LEAST(NULL, t0.c0))'; EXECUTE prepare_query USING @a; -- NULL DEALLOCATE PREPARE prepare_query; ``` Suggested fix: These two queries should have the same behaviour.