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

Problems with LIKE using a parenthesized expression in the pattern

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3
    • Fix Version/s: 10.3, 10.4
    • Component/s: Parser
    • Labels:
      None

      Description

      The problem is repeatable with virtual columns

      I create some arbitrary table:

      CREATE OR REPLACE TABLE t1 (a int);
      SHOW TABLES LIKE 't1';
      

      +---------------------+
      | Tables_in_test (t1) |
      +---------------------+
      | t1                  |
      +---------------------+
      

      Now I overwrite the structure of t1, using a virtual column with LIKE (expr):

      CREATE OR REPLACE TABLE t1 (a VARCHAR(100), b INT AS (a LIKE (10+1)));
      SHOW TABLES LIKE 't1';
      

      It returns the following output:

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+ 1' at line 1
      MariaDB [test]> SHOW TABLES LIKE 't1';
      Empty set (0.00 sec)
      

      Notice, some error happened in CREATE. But the table t1 is gone!

      This is wrong. In case if there is something wrong with the table definition, the old table should not be dropped.

      The problem is repeatable with VIEWs

      CREATE OR REPLACE VIEW v1 AS SELECT 3 LIKE (1+3) AS c;
      

      Query OK, 0 rows affected (0.01 sec)
      

      SHOW CREATE VIEW v1;
      

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+ 3 AS `c`' at line 1
      

      The reason of the problem

      The problem happens because:

      a LIKE (10+1)
      

      is internally translated to:

      a LIKE 10+1
      

      without parentheses, which further cannot be understood by the parser.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              bar Alexander Barkov
              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.