{question}
When I tried to check a SQL warning, I was not able to. How do I check a warning for a query I ran a few steps back?
{question}
{answer}
The SQL warning is a part of the compilation which happens only on the first run.
The example below is missing a group by clause, which will result in a warning, but the second execution doesn't show the warning:
mysql> select sum(totalprice),orderkey from orders;
+-----------------+----------+
| sum(totalprice) | orderkey |
+-----------------+----------+
| NULL | NULL |
+-----------------+----------+
1 row in set, 1 warning (0.03 sec)
mysql> select sum(totalprice),orderkey from orders;
+-----------------+----------+
| sum(totalprice) | orderkey |
+-----------------+----------+
| NULL | NULL |
+-----------------+----------+
1 row in set (0.00 sec)
To be able to view the warning, you need to run SHOW WARNINGS immediately after the query. If you miss this window, you need to rerun the query for the 'first time', which means you will need to clear the query plan out of the plancache.
To do this, you first need to find the query plan_id from the plancache, which you can do with the query below:
mysql> select plan_id from information_schema.plancache where query_text like '%select sum(totalprice),orderkey from orders%';
+---------+
| plan_id |
+---------+
| 63 |
+---------+
1 row in set (0.02 sec)
Then drop this plan using the plan_id you found and rerun the query to trigger the warning, then finally, you can run show warnings to see the warning for that query execution:
mysql> DROP 63 FROM PLANCACHE;
Query OK, 0 rows affected (0.01 sec)
mysql> select sum(totalprice),orderkey from orders;
+-----------------+----------+
| sum(totalprice) | orderkey |
+-----------------+----------+
| NULL | NULL |
+-----------------+----------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 2295 | Column 'orders.orderkey' 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. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{answer}