--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;