[MDEV-7602] [Draft] Wrong result - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Duplicate
Affects Version/s: 5.5(EOL), 10.0(EOL)
Fix Version/s: N/A
Component/s: Optimizer
Labels:
None

Description

Note: check that it's the different from ~~MDEV-7599~~

--source include/have_innodb.inc

 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=InnoDB

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 B (

                        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=InnoDB

 INSERT /*! IGNORE */ INTO B (

                                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, 1, '1900-01-01', '1900-01-01', NULL, NULL, '2001-11-04 19:07:55.051133', '2001-11-04 19:07:55.051133', 'k', 'k')

CREATE TABLE CC (

                        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

INSERT /*! IGNORE */ INTO CC (

                                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, 1, '2007-04-28', '2007-04-28', '00:00:00', '00:00:00', '2004-06-06 04:22:12.000192', '2004-06-06 04:22:12.000192', 'v', 'v'),(0, 7, '2006-04-05', '2006-04-05', '00:00:00', '00:00:00', '2005-11-13 01:12:31.021082', '2005-11-13 01:12:31.021082', 's', 's'),(9, 4, '2002-02-02', '2002-02-02', '06:35:17.037303', '06:35:17.037303', '2002-05-04 01:50:00.024276', '2002-05-04 01:50:00.024276', 'l', 'l'),(3, 7, '2002-06-07', '2002-06-07', '18:07:14.025498', '18:07:14.025498', '2004-10-27 10:28:45.046664', '2004-10-27 10:28:45.046664', 'y', 'y'),(4, 0, NULL, NULL, '20:36:52.065053', '20:36:52.065053', '2006-07-22 05:24:23.041014', '2006-07-22 05:24:23.041014', 'c', 'c'),(2, 2, '2003-01-13', '2003-01-13', '21:29:07.028157', '21:29:07.028157', '2002-05-16 21:34:03.028739', '2002-05-16 21:34:03.028739', 'i', 'i'),(5, 9, '2006-07-07', '2006-07-07', '23:45:57.065341', '23:45:57.065341', '2008-04-17 10:45:30.062522', '2008-04-17 10:45:30.062522', 'h', 'h'),(3, 4, NULL, NULL, '22:54:57.019206', '22:54:57.019206', '2009-04-21 02:58:02.057217', '2009-04-21 02:58:02.057217', 'q', 'q'),(1, 0, '2002-06-15', '2002-06-15', '18:45:09.027088', '18:45:09.027088', '2008-01-11 11:01:51.039461', '2008-01-11 11:01:51.039461', 'a', 'a'),(3, 9, '2006-07-09', '2006-07-09', '14:30:46.060041', '14:30:46.060041', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'v', 'v'),(6, 1, '2001-04-17', '2001-04-17', '19:23:43.025212', '19:23:43.025212', '2007-05-17 18:24:57.045885', '2007-05-17 18:24:57.045885', 'u', 'u'),(7, 3, '2005-12-22', '2005-12-22', '03:39:30.055578', '03:39:30.055578', '2007-08-07 00:00:00', '2007-08-07 00:00:00', 's', 's'),(5, 8, '2009-05-03', '2009-05-03', '23:37:52.052689', '23:37:52.052689', '2001-08-28 00:00:00', '2001-08-28 00:00:00', 'y', 'y'),(1, 8, '2003-05-28', '2003-05-28', '16:59:30.014202', '16:59:30.014202', '2004-04-16 00:27:28.004808', '2004-04-16 00:27:28.004808', 'z', 'z'),(204, 18, NULL, NULL, '22:21:15.045229', '22:21:15.045229', '2005-05-03 07:06:22.043458', '2005-05-03 07:06:22.043458', 'h', 'h'),(224, 84, '2001-11-03', '2001-11-03', '12:24:37.027412', '12:24:37.027412', '2009-03-11 17:09:50.020423', '2009-03-11 17:09:50.020423', 'p', 'p'),(9, 6, '1900-01-01', '1900-01-01', '15:02:08.033859', '15:02:08.033859', '2007-12-08 01:54:28.065294', '2007-12-08 01:54:28.065294', 'e', 'e'),(5, 3, '2000-02-17', '2000-02-17', NULL, NULL, '2009-07-28 18:19:54.023801', '2009-07-28 18:19:54.023801', 'i', 'i'),(0, 6, '2001-01-23', '2001-01-23', '08:23:30.053364', '08:23:30.053364', '2008-06-08 00:00:00', '2008-06-08 00:00:00', 'y', 'y'),(3, 6, NULL, NULL, '08:32:22.001720', '08:32:22.001720', '2005-02-09 09:20:26.053964', '2005-02-09 09:20:26.053964', 'w', 'w')

let $query =  SELECT alias2.`col_int_key` AS field1 FROM ( C AS alias1 INNER JOIN C AS alias2 ON (( alias2.`pk` >= alias1.`pk` ) AND (alias2.`pk` <= alias1.`pk` ) ) ) WHERE alias1.`col_int_key` NOT IN ( SELECT MAX( SQ1_alias1.`pk` ) AS SQ1_field1 FROM ( CC AS SQ1_alias1 LEFT JOIN ( C AS SQ1_alias2 LEFT OUTER JOIN B AS SQ1_alias3 ON (SQ1_alias3.`col_int_key` = SQ1_alias2.`col_int_key` ) ) ON (SQ1_alias3.`col_varchar_key` = SQ1_alias2.`col_varchar_nokey` ) ) WHERE SQ1_alias2.`pk` <> 9 ) GROUP BY field1;

eval $query;

SET SESSION optimizer_switch = REPLACE( @@optimizer_switch, "=on", "=off" );

SET SESSION optimizer_switch = "in_to_exists=on";

eval $query;

field1
SET SESSION optimizer_switch = REPLACE( @@optimizer_switch, "=on", "=off" );
SET SESSION optimizer_switch = "in_to_exists=on";
...
field1
1
2
4
6
7
8
9
24

Also with SET SESSION optimizer_switch='materialization=off,in_to_exists=on';

5.5 revno 4433, 10.0 revno 4587

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2015-02-17 15:39

Updated:: 2017-05-13 16:43

Resolved:: 2015-02-19 15:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server