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

With DISTINCTROW, NULLIF((-TRUE), c0) returns inconsistent results (-1 vs 1) between direct query and CTE/derived table rewrite.

    XMLWordPrintable

Details

    • Bug
    • Status: Needs Feedback (View Workflow)
    • Major
    • Resolution: Unresolved
    • 12.3.2
    • None
    • Optimizer

    Description

          1. Problem Description

      When `DISTINCTROW` is used, the expression `NULLIF((- TRUE), c0)` returns different results in a direct query versus a query rewritten with a CTE/derived table: the direct query returns `-1`, while the rewritten query returns `1`. Without `DISTINCTROW`, both return `-1`.

          1. How to Reproduce
            Execute the following SQL:

      ```sql
      DROP DATABASE IF EXISTS vp_nullif;
      CREATE DATABASE vp_nullif;
      USE vp_nullif;

      CREATE TABLE source (
      c0 DATETIME NOT NULL,
      UNIQUE KEY(c0)
      ) ENGINE=InnoDB;

      INSERT INTO source VALUES
      ('2000-01-01 00:00:01'),
      ('2000-01-01 00:00:02');

      CREATE TABLE l (
      vp_rowid BIGINT NOT NULL PRIMARY KEY,
      c0 DATETIME NOT NULL
      ) ENGINE=MyISAM;

      CREATE TABLE r (
      vp_rowid BIGINT NOT NULL PRIMARY KEY
      ) ENGINE=MyISAM;

      INSERT INTO l VALUES
      (1,'2000-01-01 00:00:01'),
      (2,'2000-01-01 00:00:02');

      INSERT INTO r VALUES (1),(2);

      – Direct query
      SELECT DISTINCTROW c0, c0, NULLIF((- TRUE), c0)
      FROM source;

      – Rewritten query (using derived table)
      SELECT DISTINCTROW c0, c0, NULLIF((- TRUE), c0)
      FROM (SELECT c0 FROM source) AS derived;
      ```

          1. Expected Behavior
            Whether `DISTINCTROW` is used or not, and whether the query is rewritten with a CTE/derived table or not, `NULLIF((- TRUE), c0)` should return `-1` (since `c0` is a `DATETIME` and can never equal `-1`).
          1. Actual Behavior
      • Direct query (with `DISTINCTROW`): third column returns `-1`
      • Rewritten query (with `DISTINCTROW` and derived table): third column returns `1`
      • Without `DISTINCTROW`, both the direct query and the rewritten query return `-1`

      Attachments

        Activity

          People

            shipjain Shipra Jain
            Annie Annie liu
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.