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

CREATE VIEW changes the result of an expression combining NOT, LIKE, and IN

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11, 11.4, 11.8, 12.3, 13.1, 12.3.2
    • 10.11, 11.4, 11.8, 12.3, 13.1
    • Parser, Views
    • MariaDB Server 12.3.2 on Windows 11 64-bit.
    • Creating a view could change the result of an expression combining NOT, LIKE, and IN.

    Description

      Description

      The same expression returns different results when evaluated directly and when evaluated through a view.
      Direct evaluation returns 0. After the expression is stored in a view, selecting the view returns 1. SHOW CREATE VIEW also displays the expression with a different operator grouping from the original query.
      This appears to be a wrong-result issue in view expression serialization or reconstruction. It may be related to MDEV-29231, which also concerns view definitions and LIKE/IN operator precedence. This reproducer does not use an ESCAPE clause; please advise whether it should be treated as a separate issue or as another case of the existing one.

      Repeat

      SELECT NOT 1 LIKE 2 IN (0, 1) AS direct_result;
      -- Result: 0
       
      CREATE VIEW v_not_like_in AS
      SELECT NOT 1 LIKE 2 IN (0, 1) AS p;
       
      SHOW CREATE VIEW v_not_like_in;
      -- The stored definition is shown with a different grouping, equivalent to:
      -- SELECT 1 LIKE (2 NOT IN (0, 1)) AS p
       
      SELECT p FROM v_not_like_in;
      -- Actual result: 1
      -- Expected result: 0, matching the direct evaluation above.
       
      DROP VIEW v_not_like_in;
      

      Attachments

        Issue Links

          Activity

            People

              raghunandan.bhat Raghunandan Bhat
              123 zhang hanshuo
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 0d
                  0d
                  Remaining:
                  Remaining Estimate - 2d
                  2d
                  Logged:
                  Time Spent - Not Specified
                  Not Specified

                  Git Integration

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