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

Problems with LIKE using a parenthesized expression in the pattern

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2, 10.3
    • 10.4
    • Parser
    • 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

              serg Sergei Golubchik
              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.