CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


INSERT INTO `B` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','f','f');

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `BB` VALUES (10,8,8,'2002-02-21','2002-02-21','18:27:58','18:27:58','1900-01-01 00:00:00','1900-01-01 00:00:00',NULL,NULL);



CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

INSERT INTO `C` VALUES (1,NULL,2,NULL,NULL,'11:28:45','11:28:45','2004-10-11 18:13:16','2004-10-11 18:13:16','w','w'),(2,7,9,'2001-09-19','2001-09-19','20:25:14','20:25:14',NULL,NULL,'m','m'),(3,9,3,'2004-09-12','2004-09-12','13:47:24','13:47:24','1900-01-01 00:00:00','1900-01-01 00:00:00','m','m'),(4,7,9,NULL,NULL,'19:24:11','19:24:11','2009-07-25 00:00:00','2009-07-25 00:00:00','k','k'),(5,4,NULL,'2002-07-19','2002-07-19','15:59:13','15:59:13',NULL,NULL,'r','r'),(6,2,9,'2002-12-16','2002-12-16','00:00:00','00:00:00','2008-07-27 00:00:00','2008-07-27 00:00:00','t','t'),(7,6,3,'2006-02-08','2006-02-08','15:15:04','15:15:04','2002-11-13 16:37:31','2002-11-13 16:37:31','j','j'),(8,8,8,'2006-08-28','2006-08-28','11:32:06','11:32:06','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),(9,NULL,8,'2001-04-14','2001-04-14','18:32:33','18:32:33','2003-12-10 00:00:00','2003-12-10 00:00:00','h','h'),(10,5,53,'2000-01-05','2000-01-05','15:19:25','15:19:25','2001-12-21 22:38:22','2001-12-21 22:38:22','o','o'),(11,NULL,0,'2003-12-06','2003-12-06','19:03:19','19:03:19','2008-12-13 23:16:44','2008-12-13 23:16:44',NULL,NULL),(12,6,5,'1900-01-01','1900-01-01','00:39:46','00:39:46','2005-08-15 12:39:41','2005-08-15 12:39:41','k','k'),(13,188,166,'2002-11-27','2002-11-27',NULL,NULL,NULL,NULL,'e','e'),(14,2,3,NULL,NULL,'00:00:00','00:00:00','2006-09-11 12:06:14','2006-09-11 12:06:14','n','n'),(15,1,0,'2003-05-27','2003-05-27','13:12:11','13:12:11','2007-12-15 12:39:34','2007-12-15 12:39:34','t','t'),(16,1,1,'2005-05-03','2005-05-03','04:56:48','04:56:48','2005-08-09 00:00:00','2005-08-09 00:00:00','c','c'),(17,0,9,'2001-04-18','2001-04-18','19:56:05','19:56:05','2001-09-02 22:50:02','2001-09-02 22:50:02','m','m'),(18,9,5,'2005-12-27','2005-12-27','19:35:19','19:35:19','2005-12-16 22:58:11','2005-12-16 22:58:11','y','y'),(19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f'),(20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d');

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

INSERT INTO `CC` VALUES (10,7,8,NULL,NULL,'01:27:35','01:27:35','2002-02-26 06:14:37','2002-02-26 06:14:37','v','v'),(11,1,9,'2006-06-14','2006-06-14','19:48:31','19:48:31','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'),(12,5,9,'2002-09-12','2002-09-12','00:00:00','00:00:00','2006-12-03 09:37:26','2006-12-03 09:37:26','a','a'),(13,3,186,'2005-02-15','2005-02-15','19:53:05','19:53:05','2008-05-26 12:27:10','2008-05-26 12:27:10','m','m'),(14,6,NULL,NULL,NULL,'19:18:56','19:18:56','2004-12-14 16:37:30','2004-12-14 16:37:30','y','y'),(15,92,2,'2008-11-04','2008-11-04','10:55:12','10:55:12','2003-02-11 21:19:41','2003-02-11 21:19:41','j','j'),(16,7,3,'2004-09-04','2004-09-04','00:25:00','00:25:00','2009-10-18 02:27:49','2009-10-18 02:27:49','d','d'),(17,NULL,0,'2006-06-05','2006-06-05','12:35:47','12:35:47','2000-09-26 07:45:57','2000-09-26 07:45:57','z','z'),(18,3,133,'1900-01-01','1900-01-01','19:53:03','19:53:03',NULL,NULL,'e','e'),(19,5,1,'1900-01-01','1900-01-01','17:53:30','17:53:30','2005-11-10 12:40:29','2005-11-10 12:40:29','h','h'),(20,1,8,'1900-01-01','1900-01-01','11:35:49','11:35:49','2009-04-25 00:00:00','2009-04-25 00:00:00','b','b'),(21,2,5,'2005-01-13','2005-01-13',NULL,NULL,'2002-11-27 00:00:00','2002-11-27 00:00:00','s','s'),(22,NULL,5,'2006-05-21','2006-05-21','06:01:40','06:01:40','2004-01-26 20:32:32','2004-01-26 20:32:32','e','e'),(23,1,8,'2003-09-08','2003-09-08','05:45:11','05:45:11','2007-10-26 11:41:40','2007-10-26 11:41:40','j','j'),(24,0,6,'2006-12-23','2006-12-23','00:00:00','00:00:00','2005-10-07 00:00:00','2005-10-07 00:00:00','e','e'),(25,210,51,'2006-10-15','2006-10-15','00:00:00','00:00:00','2000-07-15 05:00:34','2000-07-15 05:00:34','f','f'),(26,8,4,'2005-04-06','2005-04-06','06:11:01','06:11:01','2000-04-03 16:33:32','2000-04-03 16:33:32','v','v'),(27,7,7,'2008-04-07','2008-04-07','13:02:46','13:02:46',NULL,NULL,'x','x'),(28,5,6,'2006-10-10','2006-10-10','21:44:25','21:44:25','2001-04-25 01:26:12','2001-04-25 01:26:12','m','m'),(29,NULL,4,'1900-01-01','1900-01-01','22:43:58','22:43:58','2000-12-27 00:00:00','2000-12-27 00:00:00','c','c');


CREATE VIEW `view_C` AS select `C`.`pk` AS `pk`,`C`.`col_int_nokey` AS `col_int_nokey`,`C`.`col_int_key` AS `col_int_key`,`C`.`col_date_key` AS `col_date_key`,`C`.`col_date_nokey` AS `col_date_nokey`,`C`.`col_time_key` AS `col_time_key`,`C`.`col_time_nokey` AS `col_time_nokey`,`C`.`col_datetime_key` AS `col_datetime_key`,`C`.`col_datetime_nokey` AS `col_datetime_nokey`,`C`.`col_varchar_key` AS `col_varchar_key`,`C`.`col_varchar_nokey` AS `col_varchar_nokey` from `C` ;



CREATE TABLE IF NOT EXISTS trigger28270   SELECT    ( ( table1 . `col_int_nokey` ) * ( table2 . `pk` ) ) AS field1 FROM ( C AS table1 INNER JOIN CC AS table2 ON (table2 . `pk` = table1 . `col_int_key`  ) ) WHERE (   EXISTS ( SELECT DISTINCT  SUBQUERY1_t1 . `col_int_key` AS SUBQUERY1_field1 FROM ( BB AS SUBQUERY1_t1 STRAIGHT_JOIN ( C AS SUBQUERY1_t2 INNER JOIN B AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `col_int_nokey`  ) ) ON (SUBQUERY1_t3 . `col_int_nokey` = SUBQUERY1_t2 . `pk`  AND ( SUBQUERY1_t1 . `col_varchar_nokey` , SUBQUERY1_t1 . `col_varchar_nokey` )  IN ( SELECT   CHILD_SUBQUERY1_t1 . `col_varchar_nokey` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t1 . `col_varchar_key` AS CHILD_SUBQUERY1_field2 FROM ( view_C AS CHILD_SUBQUERY1_t1 RIGHT OUTER JOIN ( ( C AS CHILD_SUBQUERY1_t2 LEFT  JOIN C AS CHILD_SUBQUERY1_t3 ON (CHILD_SUBQUERY1_t3 . `col_varchar_key` = CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) ) ON (CHILD_SUBQUERY1_t3 . `pk` = CHILD_SUBQUERY1_t2 . `pk` ) ) WHERE ( CHILD_SUBQUERY1_t1 . `col_int_nokey` < 9 AND CHILD_SUBQUERY1_t1 . `pk` = CHILD_SUBQUERY1_t3 . `pk` )   ) ) ) WHERE SUBQUERY1_t3 . `col_varchar_nokey` != table1 . `col_varchar_key` AND SUBQUERY1_t2 . `col_varchar_nokey` >= SUBQUERY1_t2 . `col_varchar_nokey` ) ) OR ( table1 . `col_int_nokey` IS NOT NULL AND table1 . `col_varchar_nokey` IS NOT NULL )  GROUP BY field1 HAVING (field1 <= 40 OR field1 >= 'db')  LIMIT 0;



CREATE TABLE `trigger18270` (
  `f1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


 CREATE TRIGGER trigger1 BEFORE INSERT ON trigger18270 FOR EACH ROW INSERT INTO trigger28270   SELECT    ( ( table1 . `col_int_nokey` ) * ( table2 . `pk` ) ) AS field1 FROM ( C AS table1 INNER JOIN CC AS table2 ON (table2 . `pk` = table1 . `col_int_key`  ) ) WHERE (   EXISTS ( SELECT DISTINCT  SUBQUERY1_t1 . `col_int_key` AS SUBQUERY1_field1 FROM ( BB AS SUBQUERY1_t1 STRAIGHT_JOIN ( C AS SUBQUERY1_t2 INNER JOIN B AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `col_int_nokey`  ) ) ON (SUBQUERY1_t3 . `col_int_nokey` = SUBQUERY1_t2 . `pk`  AND ( SUBQUERY1_t1 . `col_varchar_nokey` , SUBQUERY1_t1 . `col_varchar_nokey` )  IN ( SELECT   CHILD_SUBQUERY1_t1 . `col_varchar_nokey` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t1 . `col_varchar_key` AS CHILD_SUBQUERY1_field2 FROM ( view_C AS CHILD_SUBQUERY1_t1 RIGHT OUTER JOIN ( ( C AS CHILD_SUBQUERY1_t2 LEFT  JOIN C AS CHILD_SUBQUERY1_t3 ON (CHILD_SUBQUERY1_t3 . `col_varchar_key` = CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) ) ON (CHILD_SUBQUERY1_t3 . `pk` = CHILD_SUBQUERY1_t2 . `pk` ) ) WHERE ( CHILD_SUBQUERY1_t1 . `col_int_nokey` < 9 AND CHILD_SUBQUERY1_t1 . `pk` = CHILD_SUBQUERY1_t3 . `pk` )   ) ) ) WHERE SUBQUERY1_t3 . `col_varchar_nokey` != table1 . `col_varchar_key` AND SUBQUERY1_t2 . `col_varchar_nokey` >= SUBQUERY1_t2 . `col_varchar_nokey` ) ) OR ( table1 . `col_int_nokey` IS NOT NULL AND table1 . `col_varchar_nokey` IS NOT NULL )  GROUP BY field1 HAVING (field1 <= 40 OR field1 >= 'db');


INSERT INTO trigger18270 VALUES (1);