[MDEV-7844] [Draft] Wrong result - JIRA

Details

Type: Bug
Status: Open
Priority: Minor
Resolution: Unresolved
Affects Version/s: 5.3.13, 10.1, 10.0, 5.5
Fix Version/s: None
Component/s: Optimizer
Labels:
- verified

Description

Different results with a default optimizer_switch vs all "ON" values. See 'b' value in the second result set.

CREATE TABLE A (
                        pk INTEGER AUTO_INCREMENT,
                        col_int_nokey INTEGER NOT NULL,
                        col_int_key INTEGER NOT NULL,

                        col_date_key DATE NOT NULL,
                        col_date_nokey DATE NOT NULL,

                        col_time_key TIME NOT NULL,
                        col_time_nokey TIME NOT NULL,

                        col_datetime_key DATETIME NOT NULL,
                        col_datetime_nokey DATETIME NOT NULL,

                        col_varchar_key VARCHAR(1) NOT NULL,
                        col_varchar_nokey VARCHAR(1) NOT NULL,

                        PRIMARY KEY (pk),
                        KEY (col_int_key),
                        KEY (col_date_key),
                        KEY (col_time_key),
                        KEY (col_datetime_key),
                        KEY (col_varchar_key, col_int_key)
                )  ENGINE=MyISAM
;

CREATE ALGORITHM=TEMPTABLE VIEW view_A AS SELECT * FROM A
;

CREATE TABLE C (
                        pk INTEGER AUTO_INCREMENT,
                        col_int_nokey INTEGER NOT NULL,
                        col_int_key INTEGER NOT NULL,

                        col_date_key DATE NOT NULL,
                        col_date_nokey DATE NOT NULL,

                        col_time_key TIME NOT NULL,
                        col_time_nokey TIME NOT NULL,

                        col_datetime_key DATETIME NOT NULL,
                        col_datetime_nokey DATETIME NOT NULL,

                        col_varchar_key VARCHAR(1) NOT NULL,
                        col_varchar_nokey VARCHAR(1) NOT NULL,

                        PRIMARY KEY (pk),
                        KEY (col_int_key),
                        KEY (col_date_key),
                        KEY (col_time_key),
                        KEY (col_datetime_key),
                        KEY (col_varchar_key, col_int_key)
                )  ENGINE=MyISAM
;

INSERT /*! IGNORE */ INTO C (
                                col_int_key, col_int_nokey,
                                col_date_key, col_date_nokey,
                                col_time_key, col_time_nokey,
                                col_datetime_key, col_datetime_nokey,
                                col_varchar_key, col_varchar_nokey
                        ) VALUES (0, 4, NULL, NULL, '21:22:34.025509', '21:22:34.025509', '2002-02-13 17:30:06.013935', '2002-02-13 17:30:06.013935', 'j', 'j'),(8, 6, '2004-09-18', '2004-09-18', '10:50:38.059966', '10:50:38.059966', '2008-09-27 00:34:58.026613', '2008-09-27 00:34:58.026613', 'v', 'v'),(1, 3, '2009-12-01', '2009-12-01', '00:21:38.058143', '00:21:38.058143', '2007-05-28 00:00:00', '2007-05-28 00:00:00', 'c', 'c'),(8, 5, '2004-12-17', '2004-12-17', '04:08:02.046897', '04:08:02.046897', '2009-07-25 09:21:20.064099', '2009-07-25 09:21:20.064099', 'm', 'm'),(9, 3, '2000-03-14', '2000-03-14', '16:25:11.040240', '16:25:11.040240', '2002-01-16 00:00:00', '2002-01-16 00:00:00', 'd', 'd'),(24, 246, '2000-10-08', '2000-10-08', '10:14:58.018534', '10:14:58.018534', '2006-10-12 04:32:53.031976', '2006-10-12 04:32:53.031976', 'd', 'd'),(6, 2, '2006-05-25', '2006-05-25', '19:47:59.011283', '19:47:59.011283', '2001-02-15 03:08:38.035426', '2001-02-15 03:08:38.035426', 'y', 'y'),(1, 9, '2008-01-23', '2008-01-23', '11:14:24.032949', '11:14:24.032949', '2004-10-02 20:31:15.022553', '2004-10-02 20:31:15.022553', 't', 't'),(6, 3, '2007-06-18', '2007-06-18', NULL, NULL, '2002-08-20 22:48:00.035785', '2002-08-20 22:48:00.035785', 'd', 'd'),(2, 8, '2002-10-13', '2002-10-13', '00:00:00', '00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 's', 's'),(4, 1, '1900-01-01', '1900-01-01', '15:57:25.019666', '15:57:25.019666', '2005-08-15 00:00:00', '2005-08-15 00:00:00', 'r', 'r'),(8, 8, NULL, NULL, '07:05:51.006712', '07:05:51.006712', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'm', 'm'),(4, 8, '2006-03-09', '2006-03-09', '19:22:21.057406', '19:22:21.057406', '2008-05-16 08:09:06.002924', '2008-05-16 08:09:06.002924', 'b', 'b'),(4, 5, '2001-06-05', '2001-06-05', '03:53:16.001370', '03:53:16.001370', '2001-01-20 12:47:23.022022', '2001-01-20 12:47:23.022022', 'x', 'x'),(7, 7, '2006-05-28', '2006-05-28', '09:16:38.034570', '09:16:38.034570', '2008-07-02 00:00:00', '2008-07-02 00:00:00', 'g', 'g'),(4, 5, '2001-04-19', '2001-04-19', '15:37:26.028315', '15:37:26.028315', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'p', 'p'),(1, 1, '1900-01-01', '1900-01-01', '00:00:00', '00:00:00', '2002-12-08 11:34:58.001571', '2002-12-08 11:34:58.001571', 'q', 'q'),(9, 6, '2004-08-20', '2004-08-20', '05:03:03.047452', '05:03:03.047452', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'w', 'w'),(4, 2, '2004-10-10', '2004-10-10', '02:59:24.063764', '02:59:24.063764', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'd', 'd'),(8, 9, '2000-04-02', '2000-04-02', '00:01:58.064243', '00:01:58.064243', '2002-08-25 20:35:06.064634', '2002-08-25 20:35:06.064634', 'e', 'e')
;

CREATE TABLE BB (
                        pk INTEGER AUTO_INCREMENT,
                        col_int_nokey INTEGER NOT NULL,
                        col_int_key INTEGER NOT NULL,

                        col_date_key DATE NOT NULL,
                        col_date_nokey DATE NOT NULL,

                        col_time_key TIME NOT NULL,
                        col_time_nokey TIME NOT NULL,

                        col_datetime_key DATETIME NOT NULL,
                        col_datetime_nokey DATETIME NOT NULL,

                        col_varchar_key VARCHAR(1) NOT NULL,
                        col_varchar_nokey VARCHAR(1) NOT NULL,

                        PRIMARY KEY (pk),
                        KEY (col_int_key),
                        KEY (col_date_key),
                        KEY (col_time_key),
                        KEY (col_datetime_key),
                        KEY (col_varchar_key, col_int_key)
                )  AUTO_INCREMENT=10 ENGINE=MyISAM
;
INSERT /*! IGNORE */ INTO BB (
                                col_int_key, col_int_nokey,
                                col_date_key, col_date_nokey,
                                col_time_key, col_time_nokey,
                                col_datetime_key, col_datetime_nokey,
                                col_varchar_key, col_varchar_nokey
                        ) VALUES (7, 8, '1900-01-01', '1900-01-01', '06:17:39.011342', '06:17:39.011342', '2003-08-21 00:00:00', '2003-08-21 00:00:00', 'b', 'b')
;

CREATE TABLE DD (
                        pk INTEGER AUTO_INCREMENT,
                        col_int_nokey INTEGER NOT NULL,
                        col_int_key INTEGER NOT NULL,

                        col_date_key DATE NOT NULL,
                        col_date_nokey DATE NOT NULL,

                        col_time_key TIME NOT NULL,
                        col_time_nokey TIME NOT NULL,

                        col_datetime_key DATETIME NOT NULL,
                        col_datetime_nokey DATETIME NOT NULL,

                        col_varchar_key VARCHAR(1) NOT NULL,
                        col_varchar_nokey VARCHAR(1) NOT NULL,

                        PRIMARY KEY (pk),
                        KEY (col_int_key),
                        KEY (col_date_key),
                        KEY (col_time_key),
                        KEY (col_datetime_key),
                        KEY (col_varchar_key, col_int_key)
                )  AUTO_INCREMENT=10 ENGINE=MyISAM
;

CREATE ALGORITHM=TEMPTABLE VIEW view_DD AS SELECT * FROM DD
;

INSERT /*! IGNORE */ INTO DD (
				col_int_key, col_int_nokey,
				col_date_key, col_date_nokey,
				col_time_key, col_time_nokey,
				col_datetime_key, col_datetime_nokey,
				col_varchar_key, col_varchar_nokey
			) VALUES (4, 2, '2000-04-16', '2000-04-16', '17:02:34.039583', '17:02:34.039583', '2009-08-04 02:49:10.056227', '2009-08-04 02:49:10.056227', 'u', 'u'),(5, 6, '1900-01-01', '1900-01-01', '12:31:32.028677', '12:31:32.028677', '2004-05-24 22:22:06.018760', '2004-05-24 22:22:06.018760', 'b', 'b'),(0, 6, '2005-05-14', '2005-05-14', '16:49:31.016033', '16:49:31.016033', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'c', 'c'),(140, 94, '2001-05-23', '2001-05-23', NULL, NULL, '2003-07-28 07:31:49.017347', '2003-07-28 07:31:49.017347', 'f', 'f'),(230, 10, '2000-06-04', '2000-06-04', '20:23:27.064309', '20:23:27.064309', '2000-02-04 21:03:36.035667', '2000-02-04 21:03:36.035667', 'g', 'g'),(2, 8, '1900-01-01', '1900-01-01', '10:31:09.060099', '10:31:09.060099', '2005-05-22 11:48:12.006803', '2005-05-22 11:48:12.006803', 'q', 'q'),(110, 43, '2008-03-18', '2008-03-18', '09:58:35.047713', '09:58:35.047713', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'n', 'n'),(8, 1, '2007-02-25', '2007-02-25', '05:39:28.000598', '05:39:28.000598', '2005-05-06 00:53:37.060865', '2005-05-06 00:53:37.060865', 'c', 'c'),(5, 6, '2001-11-22', '2001-11-22', NULL, NULL, '2002-08-08 23:43:46.056337', '2002-08-08 23:43:46.056337', 'x', 'x'),(8, 5, NULL, NULL, NULL, NULL, '2009-10-26 04:07:22.028954', '2009-10-26 04:07:22.028954', 'n', 'n'),(5, 1, '2001-08-07', '2001-08-07', '14:06:09.012538', '14:06:09.012538', '2005-03-12 22:55:23.019225', '2005-03-12 22:55:23.019225', 'r', 'r'),(70, 195, '2000-08-03', '2000-08-03', NULL, NULL, '2008-05-03 22:34:19.033691', '2008-05-03 22:34:19.033691', 'j', 'j'),(0, 1, '2007-04-21', '2007-04-21', '17:43:27.028093', '17:43:27.028093', '2001-10-12 13:30:14.029440', '2001-10-12 13:30:14.029440', 'l', 'l'),(9, 0, '2000-06-13', '2000-06-13', '12:23:15.064054', '12:23:15.064054', '2007-10-07 03:53:06.024988', '2007-10-07 03:53:06.024988', 'h', 'h'),(3, 7, NULL, NULL, '19:35:06.041215', '19:35:06.041215', '2005-12-27 20:58:56.015074', '2005-12-27 20:58:56.015074', 'z', 'z'),(9, 1, '2004-07-08', '2004-07-08', '10:16:08.014698', '10:16:08.014698', '2009-05-16 18:44:21.015037', '2009-05-16 18:44:21.015037', 'z', 'z'),(3, 4, '2003-06-01', '2003-06-01', NULL, NULL, '2008-10-02 00:00:00', '2008-10-02 00:00:00', 'd', 'd'),(22, 76, NULL, NULL, '16:51:15.052888', '16:51:15.052888', '2001-03-20 00:00:00', '2001-03-20 00:00:00', 'l', 'l'),(1, 6, '1900-01-01', '1900-01-01', '00:00:00', '00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 's', 's'),(3, 7, '2007-01-22', '2007-01-22', NULL, NULL, '2000-01-10 23:19:37.057226', '2000-01-10 23:19:37.057226', 'k', 'k'),(3, 6, '2006-10-19', '2006-10-19', '21:46:20.015143', '21:46:20.015143', '2008-06-21 16:56:37.031068', '2008-06-21 16:56:37.031068', 'l', 'l'),(8, 6, '2005-02-27', '2005-02-27', '00:00:00', '00:00:00', '2002-03-01 00:34:37.056078', '2002-03-01 00:34:37.056078', 't', 't'),(4, 1, '2001-09-10', '2001-09-10', '03:34:16.005635', '03:34:16.005635', '2003-02-05 18:41:00.057022', '2003-02-05 18:41:00.057022', 'n', 'n'),(1, 5, '2002-09-06', '2002-09-06', '11:25:31.054280', '11:25:31.054280', '2004-01-18 13:28:24.015145', '2004-01-18 13:28:24.015145', 'z', 'z'),(3, 2, '2005-01-15', '2005-01-15', '17:00:53.002618', '17:00:53.002618', '2002-12-03 01:06:27.058188', '2002-12-03 01:06:27.058188', 'f', 'f'),(3, 1, '1900-01-01', '1900-01-01', '23:32:47.030246', '23:32:47.030246', '2006-03-03 02:58:59.053446', '2006-03-03 02:58:59.053446', 'q', 'q'),(63, 185, '2006-12-20', '2006-12-20', '00:00:00', '00:00:00', '2002-07-01 10:13:39.005488', '2002-07-01 10:13:39.005488', 'l', 'l'),(4, 3, '2002-04-17', '2002-04-17', '12:15:05.055865', '12:15:05.055865', '2009-01-13 20:47:01.041701', '2009-01-13 20:47:01.041701', 'a', 'a'),(8, 7, '2004-01-10', '2004-01-10', '22:26:38.014842', '22:26:38.014842', '2008-07-18 13:35:29.034789', '2008-07-18 13:35:29.034789', 'c', 'c'),(6, 8, '2008-03-24', '2008-03-24', NULL, NULL, '2001-03-15 02:44:57.020155', '2001-03-15 02:44:57.020155', 'c', 'c'),(4, 1, '2007-08-18', '2007-08-18', '00:00:00', '00:00:00', '2000-06-25 14:03:55.041030', '2000-06-25 14:03:55.041030', 'i', 'i'),(9, 3, '2005-07-22', '2005-07-22', NULL, NULL, '2005-12-13 21:32:03.002308', '2005-12-13 21:32:03.002308', 'r', 'r'),(2, 8, '2008-07-12', '2008-07-12', '08:59:37.015774', '08:59:37.015774', '2009-03-18 19:27:29.010965', '2009-03-18 19:27:29.010965', 'g', 'g'),(118, 159, '2003-08-18', '2003-08-18', '07:19:50.042445', '07:19:50.042445', '2004-04-10 02:50:59.039685', '2004-04-10 02:50:59.039685', 'l', 'l'),(10, 112, '2002-02-26', '2002-02-26', '03:51:46.034084', '03:51:46.034084', '2008-09-27 06:49:19.010041', '2008-09-27 06:49:19.010041', 'w', 'w'),(9, 0, '2007-07-26', '2007-07-26', '15:57:32.008689', '15:57:32.008689', '2006-02-04 15:10:41.026394', '2006-02-04 15:10:41.026394', 'b', 'b'),(9, 2, '1900-01-01', '1900-01-01', '02:57:08.020211', '02:57:08.020211', '2004-01-04 18:45:14.009551', '2004-01-04 18:45:14.009551', 'i', 'i'),(1, 1, '2000-07-26', '2000-07-26', '00:21:15.062227', '00:21:15.062227', '2009-04-04 02:52:09.028238', '2009-04-04 02:52:09.028238', 'o', 'o'),(6, 7, '2005-03-06', '2005-03-06', '23:35:02.000223', '23:35:02.000223', '2001-03-20 12:50:17.030931', '2001-03-20 12:50:17.030931', 'f', 'f'),(2, 5, '2005-04-16', '2005-04-16', '09:21:51.027414', '09:21:51.027414', '2001-11-15 10:51:46.055784', '2001-11-15 10:51:46.055784', 'a', 'a'),(2, 2, '2004-04-09', '2004-04-09', '19:21:02.051021', '19:21:02.051021', '2007-08-13 00:00:00', '2007-08-13 00:00:00', 'j', 'j'),(3, 5, '2003-05-13', '2003-05-13', '03:16:22.010540', '03:16:22.010540', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'k', 'k'),(0, 5, NULL, NULL, '00:00:00', '00:00:00', '2008-01-24 08:37:15.037092', '2008-01-24 08:37:15.037092', 'x', 'x'),(8, 2, '2000-05-27', '2000-05-27', '18:19:51.046500', '18:19:51.046500', '2003-03-02 10:18:02.018093', '2003-03-02 10:18:02.018093', 'r', 'r'),(5, 2, '2004-07-04', '2004-07-04', '01:42:17.058376', '01:42:17.058376', '2003-04-25 07:35:13.052361', '2003-04-25 07:35:13.052361', 't', 't'),(0, 9, '2006-03-07', '2006-03-07', '00:00:00', '00:00:00', '2006-07-11 23:31:13.000074', '2006-07-11 23:31:13.000074', 'p', 'p'),(1, 3, '2002-08-17', '2002-08-17', '20:32:51.005624', '20:32:51.005624', '2009-08-08 16:55:59.050875', '2009-08-08 16:55:59.050875', 'g', 'g'),(9, 2, '2003-06-08', '2003-06-08', '10:04:19.006049', '10:04:19.006049', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'q', 'q'),(4, 7, '1900-01-01', '1900-01-01', '06:39:26.028280', '06:39:26.028280', '2000-12-22 10:27:41.055415', '2000-12-22 10:27:41.055415', 'm', 'm'),(7, 4, '2003-10-09', '2003-10-09', '15:48:26.026425', '15:48:26.026425', '2002-07-20 00:00:00', '2002-07-20 00:00:00', 'l', 'l'),(6, 5, '2007-08-12', '2007-08-12', '02:17:23.047358', '02:17:23.047358', '2007-09-24 00:12:11.023263', '2007-09-24 00:12:11.023263', 'd', 'd'),(3, 3, '2008-12-13', '2008-12-13', '07:48:47.018455', '07:48:47.018455', '2009-01-05 06:13:21.028731', '2009-01-05 06:13:21.028731', 'n', 'n'),(8, 0, NULL, NULL, '09:09:14.016231', '09:09:14.016231', '2008-11-23 18:13:35.009145', '2008-11-23 18:13:35.009145', 'l', 'l'),(2, 1, '2003-07-27', '2003-07-27', '22:46:23.043413', '22:46:23.043413', '2002-12-19 00:00:00', '2002-12-19 00:00:00', 'c', 'c'),(5, 5, '2003-04-13', '2003-04-13', '19:42:42.062965', '19:42:42.062965', '2003-04-03 16:16:35.007240', '2003-04-03 16:16:35.007240', 'v', 'v'),(3, 0, '2007-04-25', '2007-04-25', '15:15:32.060903', '15:15:32.060903', '2009-04-13 13:44:41.038692', '2009-04-13 13:44:41.038692', 'z', 'z'),(4, 4, '2000-03-04', '2000-03-04', '02:50:26.005641', '02:50:26.005641', '2007-04-19 20:49:24.041115', '2007-04-19 20:49:24.041115', 'h', 'h'),(9, 0, '2001-08-03', '2001-08-03', '14:44:31.048452', '14:44:31.048452', '2002-11-15 23:36:04.018433', '2002-11-15 23:36:04.018433', 'y', 'y'),(0, 2, '2006-04-13', '2006-04-13', NULL, NULL, '2000-05-16 00:00:00', '2000-05-16 00:00:00', 'o', 'o'),(1, 2, '2006-04-22', '2006-04-22', '07:31:27.010279', '07:31:27.010279', '2002-06-08 06:51:27.006520', '2002-06-08 06:51:27.006520', 'h', 'h'),(5, 8, '2004-08-26', '2004-08-26', '10:48:57.047734', '10:48:57.047734', '2006-12-07 01:49:26.004924', '2006-12-07 01:49:26.004924', 'h', 'h'),(6, 8, '2002-11-26', '2002-11-26', NULL, NULL, '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'c', 'c'),(2, 5, '2002-01-07', '2002-01-07', '23:06:00.043106', '23:06:00.043106', '2000-09-27 16:26:23.061118', '2000-09-27 16:26:23.061118', 'm', 'm'),(0, 1, '2005-05-07', '2005-05-07', '05:56:02.042994', '05:56:02.042994', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 's', 's'),(0, 0, '2001-04-21', '2001-04-21', '10:44:22.058273', '10:44:22.058273', '2008-02-13 04:28:14.048775', '2008-02-13 04:28:14.048775', 't', 't'),(9, 7, '2000-09-01', '2000-09-01', '00:00:00', '00:00:00', '2000-03-10 06:17:52.019819', '2000-03-10 06:17:52.019819', 'r', 'r'),(8, 6, '2004-07-06', '2004-07-06', '01:44:26.053820', '01:44:26.053820', '2003-05-23 00:00:00', '2003-05-23 00:00:00', 'u', 'u'),(6, 0, NULL, NULL, NULL, NULL, '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'h', 'h'),(0, 6, '2001-02-21', '2001-02-21', '17:01:49.055506', '17:01:49.055506', '2003-10-27 12:45:52.048296', '2003-10-27 12:45:52.048296', 'p', 'p'),(0, 9, '2004-01-28', '2004-01-28', '16:27:14.025031', '16:27:14.025031', '2004-08-06 00:00:00', '2004-08-06 00:00:00', 'z', 'z'),(3, 5, '2008-10-07', '2008-10-07', NULL, NULL, '2001-10-05 05:13:39.009562', '2001-10-05 05:13:39.009562', 'h', 'h'),(5, 5, NULL, NULL, '10:34:22.060513', '10:34:22.060513', '2001-03-25 04:24:51.022917', '2001-03-25 04:24:51.022917', 'q', 'q'),(0, 6, '2008-03-04', '2008-03-04', '10:48:51.018074', '10:48:51.018074', '2006-08-20 11:54:04.031499', '2006-08-20 11:54:04.031499', 'l', 'l'),(0, 1, '2009-05-08', '2009-05-08', '16:55:06.047172', '16:55:06.047172', '2003-05-16 18:14:52.007997', '2003-05-16 18:14:52.007997', 'p', 'p'),(1, 1, '2005-03-19', '2005-03-19', '14:46:32.016751', '14:46:32.016751', '2009-10-24 13:39:35.004380', '2009-10-24 13:39:35.004380', 't', 't'),(8, 9, '2007-09-14', '2007-09-14', '16:26:50.011015', '16:26:50.011015', '2001-08-10 00:00:00', '2001-08-10 00:00:00', 'o', 'o'),(190, 249, '2007-02-10', '2007-02-10', '06:35:32.016208', '06:35:32.016208', '2001-09-06 10:15:08.020939', '2001-09-06 10:15:08.020939', 'o', 'o'),(0, 3, '2000-06-12', '2000-06-12', '02:28:38.054759', '02:28:38.054759', '2006-09-27 00:00:00', '2006-09-27 00:00:00', 'p', 'p'),(4, 6, '2002-07-06', '2002-07-06', NULL, NULL, '2002-02-27 20:12:29.034895', '2002-02-27 20:12:29.034895', 'e', 'e'),(2, 7, '2001-02-25', '2001-02-25', '04:52:29.020846', '04:52:29.020846', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'u', 'u'),(8, 5, '2000-04-27', '2000-04-27', '00:00:00', '00:00:00', '2005-08-18 00:00:00', '2005-08-18 00:00:00', 'z', 'z'),(0, 4, '2005-08-23', '2005-08-23', '09:11:04.023183', '09:11:04.023183', '2000-10-15 15:52:54.031717', '2000-10-15 15:52:54.031717', 'j', 'j'),(137, 24, '2006-01-07', '2006-01-07', '22:38:18.020988', '22:38:18.020988', '2007-11-22 13:52:22.063256', '2007-11-22 13:52:22.063256', 'e', 'e'),(7, 3, '2007-07-08', '2007-07-08', '13:23:13.025354', '13:23:13.025354', '2005-09-20 09:09:48.001476', '2005-09-20 09:09:48.001476', 'k', 'k'),(153, 179, '2000-01-06', '2000-01-06', '15:33:10.035584', '15:33:10.035584', '2008-01-11 17:35:34.052139', '2008-01-11 17:35:34.052139', 'd', 'd'),(7, 5, NULL, NULL, '09:20:10.042141', '09:20:10.042141', '2002-11-01 00:00:00', '2002-11-01 00:00:00', 'q', 'q'),(9, 7, '2002-02-22', '2002-02-22', '11:19:10.055363', '11:19:10.055363', '2009-06-27 00:00:00', '2009-06-27 00:00:00', 'j', 'j'),(2, 7, '2001-04-17', '2001-04-17', '08:08:41.049130', '08:08:41.049130', '2000-09-25 14:11:58.033070', '2000-09-25 14:11:58.033070', 'j', 'j'),(0, 1, '2005-06-26', '2005-06-26', '10:17:36.017583', '10:17:36.017583', '2004-01-05 04:49:39.026813', '2004-01-05 04:49:39.026813', 'e', 'e'),(6, 5, '2008-09-14', '2008-09-14', '00:00:00', '00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'f', 'f'),(6, 7, NULL, NULL, '01:05:02.058738', '01:05:02.058738', '2001-01-17 23:16:24.007068', '2001-01-17 23:16:24.007068', 'a', 'a'),(2, 9, '2001-12-20', '2001-12-20', NULL, NULL, '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'o', 'o'),(9, 5, '2001-11-05', '2001-11-05', '00:00:00', '00:00:00', '2008-07-15 00:00:00', '2008-07-15 00:00:00', 'y', 'y'),(4, 4, '1900-01-01', '1900-01-01', '15:32:04.044003', '15:32:04.044003', '2001-03-06 10:00:37.004537', '2001-03-06 10:00:37.004537', 'd', 'd'),(7, 8, '2009-02-13', '2009-02-13', '20:25:36.040004', '20:25:36.040004', '2004-08-15 00:00:00', '2004-08-15 00:00:00', 'z', 'z'),(6, 8, '2008-03-25', '2008-03-25', '21:33:53.013884', '21:33:53.013884', '2006-10-12 06:13:06.002020', '2006-10-12 06:13:06.002020', 's', 's'),(7, 2, '2002-10-10', '2002-10-10', '08:01:10.044566', '08:01:10.044566', '2004-06-04 00:00:00', '2004-06-04 00:00:00', 'k', 'k'),(1, 0, '1900-01-01', '1900-01-01', '16:01:13.031525', '16:01:13.031525', '2000-01-02 00:00:00', '2000-01-02 00:00:00', 'n', 'n'),(0, 6, '2002-05-05', '2002-05-05', '08:08:00.004315', '08:08:00.004315', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'r', 'r'),(8, 2, NULL, NULL, NULL, NULL, '2005-05-21 00:00:00', '2005-05-21 00:00:00', 'c', 'c')
;

let $query = 
SELECT table3.`col_varchar_nokey` AS field3 FROM DD AS table1 LEFT JOIN ( ( C AS table2 LEFT JOIN BB AS table3 ON table2.`pk` = table3.`pk` ) INNER JOIN A AS table4  ) ON table1.`col_int_key` = table2.`pk` ;

eval $query;
set optimizer_switch = REPLACE( @@optimizer_switch, "=off", "=on" );
eval $query;

Results

SELECT table3.`col_varchar_nokey` AS field3 FROM DD AS table1 LEFT JOIN ( ( C AS table2 LEFT JOIN BB AS table3 ON table2.`pk` = table3.`pk` ) INNER JOIN A AS table4  ) ON table1.`col_int_key` = table2.`pk` ;
field3
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
set optimizer_switch = REPLACE( @@optimizer_switch, "=off", "=on" );
Warnings:
Warning	1681	'engine_condition_pushdown=on' is deprecated and will be removed in a future release.
SELECT table3.`col_varchar_nokey` AS field3 FROM DD AS table1 LEFT JOIN ( ( C AS table2 LEFT JOIN BB AS table3 ON table2.`pk` = table3.`pk` ) INNER JOIN A AS table4  ) ON table1.`col_int_key` = table2.`pk` ;
field3
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
b
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

13.test
171 kB
2015-03-27 01:04
8.test
67 kB
2015-03-29 16:24

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-03-27 01:04

Another one (or maybe the same one, don't know) – attached as 13.test

Results

SELECT DISTINCT alias1.`col_int_key` AS ifield1, alias1.`col_int_key` AS field1 FROM ( ( SELECT SQ1_alias1.* FROM ( `view_C` AS SQ1_alias1 LEFT JOIN ( `E` AS SQ1_alias2 INNER JOIN `view_E` AS SQ1_alias3 ON (SQ1_alias3.`col_int_key` = SQ1_alias2.`col_int_nokey` ) ) ON (SQ1_alias3.`col_int_nokey` = SQ1_alias2.`pk` ) ) ) AS alias1 STRAIGHT_JOIN ( ( ( SELECT SQ2_alias1.* FROM ( `view_CC` AS SQ2_alias1 LEFT OUTER JOIN `view_DD` AS SQ2_alias2 ON (SQ2_alias2.`col_varchar_key` = SQ2_alias1.`col_varchar_nokey` ) ) ) AS alias2 STRAIGHT_JOIN ( SELECT SQ3_alias1.* FROM ( `view_C` AS SQ3_alias1 INNER JOIN `DD` AS SQ3_alias2 ON (SQ3_alias2.`pk` = SQ3_alias1.`pk` ) ) ) AS alias3 ON (alias3.`col_int_key` = alias2.`col_int_nokey` ) ) ) ON (( alias3.`pk` != alias2.`col_datetime_nokey` ) AND (alias3.`pk` = alias2.`col_date_key` ) ) ) WHERE alias1.`col_int_key` = 6 HAVING ifield1 >= 'rt' ORDER BY ifield1 /* QUERY_NO 10 CON_ID 9 */
;
ifield1	field1
SET SESSION optimizer_switch = REPLACE( @@optimizer_switch, "=on", "=off" ) ;
SET SESSION optimizer_switch = "in_to_exists=on";
SELECT DISTINCT alias1.`col_int_key` AS ifield1, alias1.`col_int_key` AS field1 FROM ( ( SELECT SQ1_alias1.* FROM ( `view_C` AS SQ1_alias1 LEFT JOIN ( `E` AS SQ1_alias2 INNER JOIN `view_E` AS SQ1_alias3 ON (SQ1_alias3.`col_int_key` = SQ1_alias2.`col_int_nokey` ) ) ON (SQ1_alias3.`col_int_nokey` = SQ1_alias2.`pk` ) ) ) AS alias1 STRAIGHT_JOIN ( ( ( SELECT SQ2_alias1.* FROM ( `view_CC` AS SQ2_alias1 LEFT OUTER JOIN `view_DD` AS SQ2_alias2 ON (SQ2_alias2.`col_varchar_key` = SQ2_alias1.`col_varchar_nokey` ) ) ) AS alias2 STRAIGHT_JOIN ( SELECT SQ3_alias1.* FROM ( `view_C` AS SQ3_alias1 INNER JOIN `DD` AS SQ3_alias2 ON (SQ3_alias2.`pk` = SQ3_alias1.`pk` ) ) ) AS alias3 ON (alias3.`col_int_key` = alias2.`col_int_nokey` ) ) ) ON (( alias3.`pk` != alias2.`col_datetime_nokey` ) AND (alias3.`pk` = alias2.`col_date_key` ) ) ) WHERE alias1.`col_int_key` = 6 HAVING ifield1 >= 'rt' ORDER BY ifield1 /* QUERY_NO 10 CON_ID 9 */
;
ifield1	field1
6	6

Show

Elena Stepanova added a comment - 2015-03-27 01:04 Another one (or maybe the same one, don't know) – attached as 13.test Results SELECT DISTINCT alias1.`col_int_key` AS ifield1, alias1.`col_int_key` AS field1 FROM ( ( SELECT SQ1_alias1.* FROM ( `view_C` AS SQ1_alias1 LEFT JOIN ( `E` AS SQ1_alias2 INNER JOIN `view_E` AS SQ1_alias3 ON (SQ1_alias3.`col_int_key` = SQ1_alias2.`col_int_nokey` ) ) ON (SQ1_alias3.`col_int_nokey` = SQ1_alias2.`pk` ) ) ) AS alias1 STRAIGHT_JOIN ( ( ( SELECT SQ2_alias1.* FROM ( `view_CC` AS SQ2_alias1 LEFT OUTER JOIN `view_DD` AS SQ2_alias2 ON (SQ2_alias2.`col_varchar_key` = SQ2_alias1.`col_varchar_nokey` ) ) ) AS alias2 STRAIGHT_JOIN ( SELECT SQ3_alias1.* FROM ( `view_C` AS SQ3_alias1 INNER JOIN `DD` AS SQ3_alias2 ON (SQ3_alias2.`pk` = SQ3_alias1.`pk` ) ) ) AS alias3 ON (alias3.`col_int_key` = alias2.`col_int_nokey` ) ) ) ON (( alias3.`pk` != alias2.`col_datetime_nokey` ) AND (alias3.`pk` = alias2.`col_date_key` ) ) ) WHERE alias1.`col_int_key` = 6 HAVING ifield1 >= 'rt' ORDER BY ifield1 /* QUERY_NO 10 CON_ID 9 */ ; ifield1 field1 SET SESSION optimizer_switch = REPLACE( @@optimizer_switch, "=on", "=off" ) ; SET SESSION optimizer_switch = "in_to_exists=on"; SELECT DISTINCT alias1.`col_int_key` AS ifield1, alias1.`col_int_key` AS field1 FROM ( ( SELECT SQ1_alias1.* FROM ( `view_C` AS SQ1_alias1 LEFT JOIN ( `E` AS SQ1_alias2 INNER JOIN `view_E` AS SQ1_alias3 ON (SQ1_alias3.`col_int_key` = SQ1_alias2.`col_int_nokey` ) ) ON (SQ1_alias3.`col_int_nokey` = SQ1_alias2.`pk` ) ) ) AS alias1 STRAIGHT_JOIN ( ( ( SELECT SQ2_alias1.* FROM ( `view_CC` AS SQ2_alias1 LEFT OUTER JOIN `view_DD` AS SQ2_alias2 ON (SQ2_alias2.`col_varchar_key` = SQ2_alias1.`col_varchar_nokey` ) ) ) AS alias2 STRAIGHT_JOIN ( SELECT SQ3_alias1.* FROM ( `view_C` AS SQ3_alias1 INNER JOIN `DD` AS SQ3_alias2 ON (SQ3_alias2.`pk` = SQ3_alias1.`pk` ) ) ) AS alias3 ON (alias3.`col_int_key` = alias2.`col_int_nokey` ) ) ) ON (( alias3.`pk` != alias2.`col_datetime_nokey` ) AND (alias3.`pk` = alias2.`col_date_key` ) ) ) WHERE alias1.`col_int_key` = 6 HAVING ifield1 >= 'rt' ORDER BY ifield1 /* QUERY_NO 10 CON_ID 9 */ ; ifield1 field1 6 6

Hide

Permalink

Elena Stepanova added a comment - 2015-03-27 01:08

In general, we get plenty diffs with DisableOptimizations and EnableOptimizations transformers, optimizer.yy grammar, both simple gendata and optimizer.zz

Show

Elena Stepanova added a comment - 2015-03-27 01:08 In general, we get plenty diffs with DisableOptimizations and EnableOptimizations transformers, optimizer.yy grammar, both simple gendata and optimizer.zz

Hide

Permalink

Elena Stepanova added a comment - 2015-03-29 16:24

Different result on the 2nd execution of PS – attached as 8.test

Show

Elena Stepanova added a comment - 2015-03-29 16:24 Different result on the 2nd execution of PS – attached as 8.test

Hide

Permalink

Elena Stepanova added a comment - 2015-04-10 19:19

Different result with and without derived_merge. 5.3-10.1.

CREATE TABLE DD (
                        pk INTEGER AUTO_INCREMENT,
                        col_int_nokey INTEGER NOT NULL,
                        col_int_key INTEGER NOT NULL,

                        col_date_key DATE NOT NULL,
                        col_date_nokey DATE NOT NULL,

                        col_time_key TIME NOT NULL,
                        col_time_nokey TIME NOT NULL,

                        col_datetime_key DATETIME NOT NULL,
                        col_datetime_nokey DATETIME NOT NULL,

                        col_varchar_key VARCHAR(1) NOT NULL,
                        col_varchar_nokey VARCHAR(1) NOT NULL,

                        PRIMARY KEY (pk),
                        KEY (col_int_key),
                        KEY (col_date_key),
                        KEY (col_time_key),
                        KEY (col_datetime_key),
                        KEY (col_varchar_key, col_int_key)
                )  AUTO_INCREMENT=10 ENGINE=InnoDB
;

CREATE ALGORITHM=MERGE VIEW view_DD AS SELECT * FROM DD;

INSERT /*! IGNORE */ INTO DD (
				col_int_key, col_int_nokey,
				col_date_key, col_date_nokey,
				col_time_key, col_time_nokey,
				col_datetime_key, col_datetime_nokey,
				col_varchar_key, col_varchar_nokey
			) VALUES (7, 5, NULL, NULL, '09:20:10.042141', '09:20:10.042141', '2002-11-01 00:00:00', '2002-11-01 00:00:00', 'q', 'q'),(9, 7, '2002-02-22', '2002-02-22', '11:19:10.055363', '11:19:10.055363', '2009-06-27 00:00:00', '2009-06-27 00:00:00', 'j', 'j'),(2, 7, '2001-04-17', '2001-04-17', '08:08:41.049130', '08:08:41.049130', '2000-09-25 14:11:58.033070', '2000-09-25 14:11:58.033070', 'j', 'j'),(0, 1, '2005-06-26', '2005-06-26', '10:17:36.017583', '10:17:36.017583', '2004-01-05 04:49:39.026813', '2004-01-05 04:49:39.026813', 'e', 'e'),(6, 5, '2008-09-14', '2008-09-14', '00:00:00', '00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'f', 'f'),(6, 7, NULL, NULL, '01:05:02.058738', '01:05:02.058738', '2001-01-17 23:16:24.007068', '2001-01-17 23:16:24.007068', 'a', 'a'),(2, 9, '2001-12-20', '2001-12-20', NULL, NULL, '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'o', 'o'),(9, 5, '2001-11-05', '2001-11-05', '00:00:00', '00:00:00', '2008-07-15 00:00:00', '2008-07-15 00:00:00', 'y', 'y'),(4, 4, '1900-01-01', '1900-01-01', '15:32:04.044003', '15:32:04.044003', '2001-03-06 10:00:37.004537', '2001-03-06 10:00:37.004537', 'd', 'd'),(7, 8, '2009-02-13', '2009-02-13', '20:25:36.040004', '20:25:36.040004', '2004-08-15 00:00:00', '2004-08-15 00:00:00', 'z', 'z'),(6, 8, '2008-03-25', '2008-03-25', '21:33:53.013884', '21:33:53.013884', '2006-10-12 06:13:06.002020', '2006-10-12 06:13:06.002020', 's', 's'),(7, 2, '2002-10-10', '2002-10-10', '08:01:10.044566', '08:01:10.044566', '2004-06-04 00:00:00', '2004-06-04 00:00:00', 'k', 'k'),(1, 0, '1900-01-01', '1900-01-01', '16:01:13.031525', '16:01:13.031525', '2000-01-02 00:00:00', '2000-01-02 00:00:00', 'n', 'n'),(0, 6, '2002-05-05', '2002-05-05', '08:08:00.004315', '08:08:00.004315', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'r', 'r'),(8, 2, NULL, NULL, NULL, NULL, '2005-05-21 00:00:00', '2005-05-21 00:00:00', 'c', 'c')
;

let $query = 
SELECT MIN( `col_int_nokey`) AS field1, `col_varchar_nokey` AS cfield1, `col_datetime_key` AS field2 FROM ( SELECT SQ1_alias1.* FROM ( `view_DD` AS SQ1_alias1, `DD` AS SQ1_alias2 ) ) AS alias1 WHERE alias1.`col_date_key` = 'm' AND ( alias1.`col_varchar_key` LIKE '%a%' OR alias1.`col_varchar_key` LIKE '%b%') GROUP BY cfield1, field2
;

eval $query;
SET SESSION optimizer_switch='derived_merge=off';
eval $query;

Show

Elena Stepanova added a comment - 2015-04-10 19:19 Different result with and without derived_merge. 5.3-10.1. CREATE TABLE DD ( pk INTEGER AUTO_INCREMENT, col_int_nokey INTEGER NOT NULL, col_int_key INTEGER NOT NULL, col_date_key DATE NOT NULL, col_date_nokey DATE NOT NULL, col_time_key TIME NOT NULL, col_time_nokey TIME NOT NULL, col_datetime_key DATETIME NOT NULL, col_datetime_nokey DATETIME NOT NULL, col_varchar_key VARCHAR (1) NOT NULL, col_varchar_nokey VARCHAR (1) NOT NULL, PRIMARY KEY (pk), KEY (col_int_key), KEY (col_date_key), KEY (col_time_key), KEY (col_datetime_key), KEY (col_varchar_key, col_int_key) ) AUTO_INCREMENT=10 ENGINE=InnoDB ; CREATE ALGORITHM=MERGE VIEW view_DD AS SELECT * FROM DD; INSERT /*! IGNORE */ INTO DD ( col_int_key, col_int_nokey, col_date_key, col_date_nokey, col_time_key, col_time_nokey, col_datetime_key, col_datetime_nokey, col_varchar_key, col_varchar_nokey ) VALUES (7, 5, NULL, NULL, '09:20:10.042141', '09:20:10.042141', '2002-11-01 00:00:00', '2002-11-01 00:00:00', 'q', 'q'),(9, 7, '2002-02-22', '2002-02-22', '11:19:10.055363', '11:19:10.055363', '2009-06-27 00:00:00', '2009-06-27 00:00:00', 'j', 'j'),(2, 7, '2001-04-17', '2001-04-17', '08:08:41.049130', '08:08:41.049130', '2000-09-25 14:11:58.033070', '2000-09-25 14:11:58.033070', 'j', 'j'),(0, 1, '2005-06-26', '2005-06-26', '10:17:36.017583', '10:17:36.017583', '2004-01-05 04:49:39.026813', '2004-01-05 04:49:39.026813', 'e', 'e'),(6, 5, '2008-09-14', '2008-09-14', '00:00:00', '00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'f', 'f'),(6, 7, NULL, NULL, '01:05:02.058738', '01:05:02.058738', '2001-01-17 23:16:24.007068', '2001-01-17 23:16:24.007068', 'a', 'a'),(2, 9, '2001-12-20', '2001-12-20', NULL, NULL, '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'o', 'o'),(9, 5, '2001-11-05', '2001-11-05', '00:00:00', '00:00:00', '2008-07-15 00:00:00', '2008-07-15 00:00:00', 'y', 'y'),(4, 4, '1900-01-01', '1900-01-01', '15:32:04.044003', '15:32:04.044003', '2001-03-06 10:00:37.004537', '2001-03-06 10:00:37.004537', 'd', 'd'),(7, 8, '2009-02-13', '2009-02-13', '20:25:36.040004', '20:25:36.040004', '2004-08-15 00:00:00', '2004-08-15 00:00:00', 'z', 'z'),(6, 8, '2008-03-25', '2008-03-25', '21:33:53.013884', '21:33:53.013884', '2006-10-12 06:13:06.002020', '2006-10-12 06:13:06.002020', 's', 's'),(7, 2, '2002-10-10', '2002-10-10', '08:01:10.044566', '08:01:10.044566', '2004-06-04 00:00:00', '2004-06-04 00:00:00', 'k', 'k'),(1, 0, '1900-01-01', '1900-01-01', '16:01:13.031525', '16:01:13.031525', '2000-01-02 00:00:00', '2000-01-02 00:00:00', 'n', 'n'),(0, 6, '2002-05-05', '2002-05-05', '08:08:00.004315', '08:08:00.004315', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'r', 'r'),(8, 2, NULL, NULL, NULL, NULL, '2005-05-21 00:00:00', '2005-05-21 00:00:00', 'c', 'c') ; let $query = SELECT MIN( `col_int_nokey`) AS field1, `col_varchar_nokey` AS cfield1, `col_datetime_key` AS field2 FROM ( SELECT SQ1_alias1.* FROM ( `view_DD` AS SQ1_alias1, `DD` AS SQ1_alias2 ) ) AS alias1 WHERE alias1.`col_date_key` = 'm' AND ( alias1.`col_varchar_key` LIKE '%a%' OR alias1.`col_varchar_key` LIKE '%b%') GROUP BY cfield1, field2 ; eval $query; SET SESSION optimizer_switch='derived_merge=off'; eval $query;

[Draft] Wrong result

Details

Description

Gliffy Diagrams

Attachments

Attachments

Activity

People

Dates

Agile