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

Problems with LIKE using a parenthesized expression in the pattern

Details

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17408 VIEW is incorrectly defined for a combination of = and BETWEEN

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23656 view: removal of parentheses results in wrong result

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17359 || operator is not understand by "like" in Oracle

          • Major - Major loss of function.
          • Closed

          Activity

            serg Sergei Golubchik added a comment - 

            MariaDB [test]> CREATE OR REPLACE VIEW v1 AS SELECT (1 LIKE 2) BETWEEN 1 AND 2 AS c;
            		 
            Query OK, 0 rows affected (0.002 sec)
            		 
             
            		 
            MariaDB [test]> 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 'between 1 and 2 AS `c`' at line 1

            serg Sergei Golubchik added a comment - MariaDB [test]> CREATE OR REPLACE VIEW v1 AS SELECT (1 LIKE 2) BETWEEN 1 AND 2 AS c; Query OK, 0 rows affected (0.002 sec)   MariaDB [test]> 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 'between 1 and 2 AS `c`' at line 1

            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.