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

Select with multiple conditions (one of them IS NULL) using a composite index against a federated table returns empty result set

    XMLWordPrintable

Details

    Description

      Appends earlier versions as well.

      Put together:

      • a federated table
      • a composite index
      • a select with condition column IS NULL and at least one other condition, both in the composite index
        and the result will be an empty result set, even if there should have been rows returned.

      Bug does not occur, when:

      • single column index is used or
      • no index is used or
      • condition IS NULL is only condition or
      • condition IS NOT NULL is used instead

      Test case:

      -- use this server as remote and local server for test-case
      -- bug behaviour is the same with 'real' remote server
      DROP SERVER IF EXISTS local_server;
      CREATE SERVER local_server
      FOREIGN DATA WRAPPER mysql
      OPTIONS (
        HOST '127.0.0.1',
        PORT 3306,
        USER 'USER',
        PASSWORD 'PASS',
        DATABASE 'test'
      );
       
      -- create sample table and fill with data
      DROP TABLE IF EXISTS test.remote_table;
      CREATE TABLE test.remote_table (
        a TINYINT DEFAULT NULL,
        b TINYINT NOT NULL,
        KEY _ab (a,b),
        KEY _ba (b,a),
        KEY _a (a),
        KEY _b (b)
      ) AS
      SELECT NULL AS a, 1 AS b
      UNION
      SELECT 2 AS a, 3 AS b;
       
      -- create federated table pointing to table above
      DROP TABLE IF EXISTS test.local_table;
      CREATE TABLE test.local_table (
        a TINYINT DEFAULT NULL,
        b TINYINT NOT NULL,
        KEY _ab (a,b),
        KEY _ba (b,a),
        KEY _a (a),
        KEY _b (b)
      ) ENGINE=federated CONNECTION='local_server/remote_table';
       
      -- remote table: OK
      SELECT * FROM test.remote_table
      WHERE a IS NULL AND b BETWEEN 1 AND 3;
      -- returns 1 row
       
      -- not using index: OK
      SELECT * FROM test.local_table USE INDEX ()
      WHERE a IS NULL AND b BETWEEN 1 AND 3;
      -- returns 1 row
       
      -- use column index a: OK
      SELECT * FROM test.local_table USE INDEX (_a)
      WHERE a IS NULL AND b BETWEEN 1 AND 3;
      -- returns 1 row
       
      -- use column index b: OK
      SELECT * FROM test.local_table USE INDEX (_b)
      WHERE a IS NULL AND b BETWEEN 1 AND 3;
      -- returns 1 row
       
      -- use composite index, a is NOT null: OK
      SELECT * FROM test.local_table USE INDEX (_ab)
      WHERE a IS NOT NULL AND b BETWEEN 1 AND 3;
      -- returns 1 row
       
      -- use composite index, a=2: OK
      SELECT * FROM test.local_table USE INDEX (_ab)
      WHERE a=2 AND b BETWEEN 1 AND 3;
      -- returns 1 row
       
      -- use composite index, only one condition: OK
      SELECT * FROM test.local_table USE INDEX (_ab)
      WHERE a IS NULL;
      -- returns 1 row
       
      -- use composite index, a first column, a is null: WRONG
      SELECT * FROM test.local_table USE INDEX (_ab)
      WHERE a IS NULL AND b BETWEEN 1 AND 3;
      -- returns no rows
      -- should return 1 row
       
      -- use composite index, a last column, a is null: WRONG
      SELECT * FROM test.local_table USE INDEX (_ba)
      WHERE a IS NULL AND b BETWEEN 1 AND 3;
      -- returns no rows
      -- should return 1 row
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            wollewalda Wolfgang Walther
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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