Description:
WL#7589 (Query Rewrite plugins) is supposed to replace '?' placeholders with literals when a given pattern matches a query, and the pattern and replacement contains the same amount of literals, and are otherwise valid SQL.
The following example fails:
mysql> SELECT `col_date_key` AS field1
FROM `CC`
WHERE ( NULL, `col_datetime_key` ) IN (
SELECT `col_varchar` AS subfield11, `col_varchar_key` AS subfield12
FROM `E`
WHERE ( col_varchar, col_varchar ) IN (
('x', 'm'), ('a', 'z')
)
);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? ), ('a', 'm' ) ) ) /* TRANSFORM_OUTCOME_UNORDERED_MATCH */' at line 1
It seems that the first placeholder is not replaced, while the second one is.
The same exercise works with older WL#7589 code, for example from Dec 29 or Jan 6. Meaning, instead of an error, the Empty set is returned, which is what the replacement returns when placeholders are manually replaced with literals.
How to repeat:
The following MTR test should reproduce it:
--source suite/query_rewrite_plugins/include/have_plugin_rewriter.inc
--source suite/query_rewrite_plugins/include/install_rewriter.inc
--disable_warnings
DROP TABLE IF EXISTS `CC`;
DROP TABLE IF EXISTS `E`;
--disable_warnings
CREATE TABLE `CC` (
`col_datetime_key` datetime DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`pk` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`pk`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_date_key` (`col_date_key`)
);
CREATE TABLE `E` (
`col_varchar` varchar(1) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`pk` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`pk`),
KEY `col_varchar_key` (`col_varchar_key`)
);
INSERT INTO query_rewrite.rewrite_rules(pattern, pattern_database, replacement) VALUES (
' SELECT `col_date_key` AS field1 FROM `CC` WHERE ( NULL, `col_datetime_key` ) IN ( SELECT `col_varchar` AS subfield11, `col_varchar_key` AS subfield12 FROM `E` WHERE ( col_varchar, col_varchar ) IN ( (\'x\', ? ), (\'a\', ? ) ) ) ',
'test',
'/* TransformerRewrite: Rewritten */ /* GenTest::Transform::ConvertTablesToDerived */SELECT `col_date_key` AS field1 FROM ( SELECT * FROM `CC` ) AS derived2 WHERE ( NULL, `col_datetime_key` ) IN ( SELECT `col_varchar` AS subfield11, `col_varchar_key` AS subfield12 FROM ( SELECT * FROM `E` ) AS derived1 WHERE ( col_varchar, col_varchar ) IN ( (\'x\', ? ), (\'a\', ? ) ) ) /* TRANSFORM_OUTCOME_UNORDERED_MATCH */'
);
CALL query_rewrite.flush_rewrite_rules();
SELECT `col_date_key` AS field1 FROM `CC` WHERE ( NULL, `col_datetime_key` ) IN ( SELECT `col_varchar` AS subfield11, `col_varchar_key` AS subfield12 FROM `E` WHERE ( col_varchar, col_varchar ) IN ( ('x', 'm'), ('a', 'z') ) );
DROP TABLE CC;
DROP TABLE E;
The test will will be attached to this bug, in case bug report formatting mangles the statements.
