{question}
How can I ensure I receive an error message when I use an illegal command as the following:
Column 'column_name' from project list is used outside an aggregate function and does not appear in the GROUP BY clause
It seems like SingleStore is tolerant of illegal commands and only counts warnings.
{question}
{answer}
In SingleStore we allow you to execute an illegal query, by default we only add a warning, in the query's first run you will see in the result a number of warnings > 0.
The warning will show only in the first run, so if you have run it already just clean it from the plancache and rerun it to see the warning, as follow:
memsql> select cob_date,country,city from test.testgroupby group by 1,2 order by 1,2;
+------------+---------+---------+
| cob_date | country | city |
+------------+---------+---------+
| 2022-10-01 | UK | Georgia |
| 2022-10-01 | USA | Georgia |
| 2022-10-02 | UK | Dublin |
| 2022-10-02 | USA | Georgia |
+------------+---------+---------+
4 rows in set, 1 warning (0.02 sec)
memsql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 2295 | Column 'testgroupby.city' from project list is used outside an aggregate function and does not appear in the GROUP BY clause. This is illegal because there may be multiple possible values for the field. If it is in fact uniquely determined, or if you wish to use one of the many possible values, you can use the ANY_VALUE aggregate function. |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
To get an error notification in this case instead of a warning you need to add the option ofONLY_FULL_GROUP_BY
to your sql_mode
setting.
TheONLY_FULL_GROUP_BY
: Throws an error when fields in the SELECT list, HAVING condition, or ORDER BY list are not in a GROUP BY clause or are outside of an aggregate function. This is because the result set could include multiple possible values for the non-grouped, non-aggregated field.
How do we add this ONLY_FULL_GROUP_BY
to sql_mode
setting? It will be added to the existing setting, in the example below to STRICT_ALL_TABLES
.
Once you add this option to the sql_mode
, you will get an error instead of a warning as the following example:
memsql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
-- I reconnected to the DB, on the same session you won't see the change.
memsql> select @@sql_mode;
+--------------------------------------+
| @@sql_mode |
+--------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+--------------------------------------+
1 row in set (0.02 sec)
memsql> select cob_date,country,city from test.testgroupby group by 1,2 order by 1,2;
ERROR 2295 (HY000): Column 'testgroupby.city' from project list is used outside an aggregate function and does not appear in the GROUP BY clause. This is illegal because there may be multiple possible values for the field. If it is in fact uniquely determined, or if you wish to use one of the many possible values, you can use the ANY_VALUE aggregate function.
The correct query should be:
memsql> select cob_date,country,city from test.testgroupby group by 1,2,3 order by 1,2;
+------------+---------+---------+
| cob_date | country | city |
+------------+---------+---------+
| 2022-10-01 | UK | Dublin |
| 2022-10-01 | UK | Georgia |
| 2022-10-01 | USA | Georgia |
| 2022-10-01 | USA | Dublin |
| 2022-10-02 | UK | Georgia |
| 2022-10-02 | UK | Dublin |
| 2022-10-02 | USA | Dublin |
| 2022-10-02 | USA | Georgia |
+------------+---------+---------+
8 rows in set (0.03 sec)
For more detail check sql_mode.
{answer}