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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.2.13
    • Fix Version/s: 5.5.60
    • Component/s: Optimizer
    • Labels:
    • Environment:
      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

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              Nicolas Rolin
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: