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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5, 10.0, 10.1, 10.2.13, 10.2, 10.3
    • 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

          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.