MySQL Bugs: #74813: QUERY REWRITE: NULL IS NOT RECOGNIZED BY THE PLUGIN AS A LITERAL

Bug #74813	QUERY REWRITE: NULL IS NOT RECOGNIZED BY THE PLUGIN AS A LITERAL
Submitted:	12 Nov 2014 9:41	Modified:	16 Jan 2015 15:51
Reporter:	John Embretsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The WL#7589 (Query Rewrite Plugin) text says:

"The syntax for specifying both patterns and replacement has been chosen to be the same as is currently used for prepared statements. (...) Any query that can be parsed as a prepared statement can be used as a pattern or replacement."

Any restrictions regarding NULL values do not seem to be documented in the WL spec, so it is expected that any valid SELECT-based prepared statement will also work with the Rewriter plugin. This is not the case. Consider the following examples, where it is demonstrated that what works with prepared statement does not work (i.e. rule pattern is not matched) with the plugin:

Demo preparation:
-----------------
mysql> CREATE TABLE BB (a INT, b INT);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO BB VALUES (1, 1), (2, NULL);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

Using NULL with Prepared Statement:
-----------------------------------
-- Assuming that the plugin is not installed, or that 
-- no enabled rules are matching the given query.

mysql> PREPARE prep FROM 'SELECT * FROM BB WHERE (? <> 1) IS NULL';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @literal = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE PREP USING @literal;
Empty set (0.00 sec)

mysql> SET @literal = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE PREP USING @literal;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

Using NULL with Rewriter plugin:
--------------------------------
mysql> source lib/plugin/install_rewriter_plugin.sql
...
mysql> INSERT INTO query_rewrite.rewrite_rules
    ->         (pattern, pattern_database, replacement)
    ->  VALUES ('SELECT * FROM BB WHERE (? <> 1) IS NULL',
    ->          'test',
    ->          'SELECT a AS queryrewritten FROM BB WHERE (? <> 1) IS NULL');
Query OK, 1 row affected (0.46 sec)

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM BB WHERE (1 <> 1) IS NULL;
Empty set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'SELECT * FROM BB WHERE (1 <> 1) IS NULL' rewritten to 'SELECT a AS queryrewritten FROM BB WHERE (1 <> 1) IS NULL' by plugin: Rewriter. |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM BB WHERE (NULL <> 1) IS NULL;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

As demonstrated, the last query is not rewritten, indicating that the
NULL value in (NULL <> 1) is not interpreted as a literal by the plugin.

Verified against bzr mysql-trunk-wl7589 revid martin.hansson@oracle.com-20141111105247-5oey1vhajy8y1c9t.

How to repeat:
-- Create demo table
CREATE TABLE BB (a INT, b INT);
INSERT INTO BB VALUES (1, 1), (2, NULL);

-- Show Prepared Statement behavior with NULL as literal
PREPARE prep FROM 'SELECT * FROM BB WHERE (? <> 1) IS NULL';
SET @literal = 1;
EXECUTE PREP USING @literal;
SET @literal = NULL;
EXECUTE PREP USING @literal;

-- Show plugin behavior with NULL as literal
source lib/plugin/install_rewriter_plugin.sql
INSERT INTO query_rewrite.rewrite_rules
        (pattern, pattern_database, replacement)
 VALUES ('SELECT * FROM BB WHERE (? <> 1) IS NULL',
         'test',
         'SELECT a AS queryrewritten FROM BB WHERE (? <> 1) IS NULL');
CALL query_rewrite.flush_rewrite_rules();

SELECT * FROM BB WHERE (1 <> 1) IS NULL;
SHOW WARNINGS;
SELECT * FROM BB WHERE (NULL <> 1) IS NULL;

-- Bonus (Prepared Statement with matching rewrite rule):

PREPARE newprep FROM 'SELECT * FROM BB WHERE (? <> 1) IS NULL';
SHOW WARNINGS;
SET @literal = 1;
EXECUTE PREP USING @literal;
SET @literal = NULL;
EXECUTE PREP USING @literal;

Posted by developer:
 
For completeness, here is a demonstration of the combination of Prepared Statement and matching Rewriter rule:

Using null with Prepared Statement + Rewriter plugin
----------------------------------------------------

If there is an enabled rule matching the given prepared statement,
the statement is rewritten at prepare time and the NULL is accpeted
as a literal at execution time:

mysql> PREPARE newprep FROM 'SELECT * FROM BB WHERE (? <> 1) IS NULL';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Statement prepared

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'SELECT * FROM BB WHERE (? <> 1) IS NULL' rewritten to 'SELECT a AS queryrewritten FROM BB WHERE (? <> 1) IS NULL' by plugin: Rewriter. |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @literal = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE newprep USING @literal;
Empty set (0.00 sec)

mysql> SET @literal = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE newprep USING @literal;
+----------------+
| queryrewritten |
+----------------+
|              1 |
|              2 |
+----------------+
2 rows in set (0.00 sec)

In this case it works as expected.

Posted by developer:
 
Treating this as a feature request is likely fine with me, as long as the limitation will be properly documented.
QA will have to implement workarounds in the tests, but it should be doable.
Note that plugin end users may still see it as a bug, and not care what the underlying root cause is.

Noted in 5.7.6 changelog.

NULL as an expression was not recognized as a literal for calculation
of Performance Schema statement digests.

Noted in 5.6.24 changelog.