Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15555

select from DUAL where false yielding wrong result when in a IN

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.2.13, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 5.5.60
    • Optimizer
    • docker with mariadb:latest (host is a debian testing)

    Description

      10.2.13-MariaDB (tested on docker latest)

      MariaDB [(none)]> SELECT 1 from DUAL WHERE 1 != 1;
      		 
      Empty set (0.00 sec)

      Ok.

      Exhibit A :

      MariaDB [(none)]> SELECT 2 IN (SELECT 1 from DUAL WHERE 1 != 1);
      		 
      +----------------------------------------+
      		 
      | 2 IN (SELECT 1 from DUAL WHERE 1 != 1) |
      		 
      +----------------------------------------+
      		 
      |                                      0 |
      		 
      +----------------------------------------+
      		 
       
      		 
      1 row in set (0.00 sec)

      Ok.
      2 is indeed not in the empty set

      Exhibit B :

      MariaDB [(none)]> SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
      		 
      +----------------------------------------+
      		 
      | 2 IN (SELECT 2 from DUAL WHERE 1 != 1) |
      		 
      +----------------------------------------+
      		 
      |                                      1 |
      		 
      +----------------------------------------+
      		 
       
      		 
      1 row in set (0.00 sec)

      Not ok.
      I thought 2 was not in emptyset. why changing the emptyset did affect that ?

      Exhibit B looks like a DUAL bug, as :

      • it returns 0 if we replace DUAL by any other table
      • it returns 0 if I use a mysql backend instead

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Reproducible on all of MariaDB 5.5-10.3 and MySQL 5.5, 5.6; seems to be fixed in MySQL 5.7.

          MariaDB 10.3

          		 
          EXPLAIN EXTENDED SELECT 2 IN (SELECT 2 from DUAL WHERE 0);
          		 
          id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
          		 
          1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
          		 
          Warnings:
          		 
          Note	1249	Select 2 was reduced during optimization
          		 
          Note	1003	select 2 = 2 AS `2 IN (SELECT 2 from DUAL WHERE 0)`

          MySQL 5.7

          		 
          EXPLAIN EXTENDED SELECT 2 IN (SELECT 2 from DUAL WHERE 0);
          		 
          id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
          		 
          1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
          		 
          2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
          		 
          Warnings:
          		 
          Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
          		 
          Note	1003	/* select#1 */ select <in_optimizer>(2,<exists>(/* select#2 */ select 2 from DUAL  where 0)) AS `2 IN (SELECT 2 from DUAL WHERE 0)`

          elenst Elena Stepanova added a comment - Reproducible on all of MariaDB 5.5-10.3 and MySQL 5.5, 5.6; seems to be fixed in MySQL 5.7. MariaDB 10.3 EXPLAIN EXTENDED SELECT 2 IN ( SELECT 2 from DUAL WHERE 0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select 2 = 2 AS `2 IN ( SELECT 2 from DUAL WHERE 0)` MySQL 5.7 EXPLAIN EXTENDED SELECT 2 IN ( SELECT 2 from DUAL WHERE 0); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. Note 1003 /* select#1 */ select <in_optimizer>(2,<exists>( /* select#2 */ select 2 from DUAL where 0)) AS `2 IN ( SELECT 2 from DUAL WHERE 0)`
          psergei Sergei Petrunia added a comment -

          Ok to push.

          psergei Sergei Petrunia added a comment - Ok to push.

          People

            varun Varun Gupta (Inactive)
            Nicolas Rolin
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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