Query canonicalization does not take SQL mode into account

The following queries:

SELECT user, host FROM mysql.user WHERE "user" = 'foobar';

SELECT user, host FROM mysql.user WHERE 'user' = 'foobar';

both have the canonical form:

SELECT user, host FROM mysql.user WHERE ? = ?;

However, if the first query was this instead:

SELECT user, host FROM mysql.user WHERE `user` = "foobar";

The result would be:

SELECT user, host FROM mysql.user WHERE `user` = ?;

These two queries are only identical if ANSI_QUOTES SQL mode is not enabled. A common case where it is enabled is SQL_MODE=ORACLE.

The query canonical form computation must have distinct modes of operation depending on the value of ANSI_QUOTES. Otherwise the fields accessed by the query are not accurate and the reported access statistics for it are potentially over-inflated.

The actual effects of this are quite limited: the query type does not change if the value is a string constant or a field name and all important variables, when used as field names, would not affect the functionality of the query in any meaningful way.

The only actual problem that it will cause is if a temporary table is created and it's accessed when SQL_MODE=ORACLE is used.

SET SQL_MODE=ORACLE;

CREATE TEMPORARY TABLE t1(id INT) AS SELECT 1 id;

CREATE TABLE t2(id INT) AS SELECT 2 id;

SELECT * FROM "t1" WHERE id <> 0;

SELECT * FROM "t2" WHERE id <> 0;

In the example above, the access to "t2" is mistaken as a read from a temporary table as it uses the query classification information of that was generated by the first select.