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

|| operator is not understand by "like" in Oracle

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.9, 10.3(EOL)
    • 10.3.11
    • Parser
    • None
    • All OS

    Description

      with sql_mode=Oracle, the following query

      select * from dual where 'ab' like 'a'||'%';

      failed with
      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 '||'%'' at line 1

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17374 Shift/reduce conflicts because of SOUNDS_SYM, ESCAPE_SYM, USER_SYM not given precedence

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16156 PIPES_AS_CONCAT does not work well

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16186 Concatenation operator || returns wrong results in sql_mode=ORACLE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17406 Problems with LIKE using a parenthesized expression in the pattern

          • Major - Major loss of function.
          • Open

          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
          links to

          Web Link Operator precedence is Oracle

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            Thanks for the report!
            I can confirm, that error is returned, but it works, when I add parentheses.

            MariaDB [test]> SELECT  '1a' like '1'||'a';
            		 
            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 '||'a'' at line 1
            		 
             
            		 
            MariaDB [test]> SELECT  '1a' like ('1'||'a');
            		 
            +----------------------+
            		 
            | '1a' like ('1'||'a') |
            		 
            +----------------------+
            		 
            |                    1 |
            		 
            +----------------------+
            		 
            1 row in set (0.000 sec)
            		 
             
            		 
            MariaDB [test]> select 1 from dual where 'ab' like ('a'||'%');
            		 
            +---+
            		 
            | 1 |
            		 
            +---+
            		 
            | 1 |
            		 
            +---+
            		 
            1 row in set (0.000 sec)

            alice Alice Sherepa added a comment - Thanks for the report! I can confirm, that error is returned, but it works, when I add parentheses. MariaDB [test]> SELECT '1a' like '1'||'a'; 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 '||'a'' at line 1   MariaDB [test]> SELECT '1a' like ('1'||'a'); +----------------------+ | '1a' like ('1'||'a') | +----------------------+ | 1 | +----------------------+ 1 row in set (0.000 sec)   MariaDB [test]> select 1 from dual where 'ab' like ('a'||'%'); +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.000 sec)
            bar Alexander Barkov added a comment - - edited

            Results returned by the Oracle database:

            – One row:
            SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1;
            SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1;

            SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11;
            SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11;

            SELECT 'x' FROM DUAL WHERE 1 LIKE +1;
            SELECT 'x' FROM DUAL WHERE -1 LIKE -1;
            SELECT 'x' FROM DUAL WHERE 1 LIKE (1);

            SELECT 'x' FROM DUAL WHERE 1 LIKE 1+0;
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1-0;

            SELECT 'x' FROM DUAL WHERE 1+0 LIKE 1;
            SELECT 'x' FROM DUAL WHERE 1+0 LIKE 1;

            SELECT 'x' FROM DUAL WHERE 1 LIKE 1*1;
            SELECT 'x' FROM DUAL WHERE 1 LIKE 2/2;

            SELECT 'x' FROM DUAL WHERE 1*2 LIKE 2;
            SELECT 'x' FROM DUAL WHERE 2/2 LIKE 1;

            SELECT 'x' FROM DUAL WHERE 1 LIKE CAST(1 AS VARCHAR(10));

            SELECT 'x' FROM DUAL WHERE 1 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END;
            SELECT 'x' FROM DUAL WHERE 1 LIKE COALESCE(1+0,1);

            – Error: missing right parenthesis
            SELECT 'x' FROM DUAL WHERE 1*(2 LIKE 2);
            SELECT 'x' FROM DUAL WHERE 1/(2 LIKE 2);
            SELECT 'x' FROM DUAL WHERE 1+(2 LIKE 2);
            SELECT 'x' FROM DUAL WHERE 1-(2 LIKE 2);
            SELECT 'x' FROM DUAL WHERE 1 LIKE (1 IN (1,2,3));
            SELECT 'x' FROM DUAL WHERE 1 LIKE (1 NOT IN (1,2,3));
            SELECT 'x' FROM DUAL WHERE 1 LIKE (1 BETWEEN 1 AND 1);
            SELECT 'x' FROM DUAL WHERE 1 LIKE (1 NOT BETWEEN 1 AND 1);
            SELECT 'x' FROM DUAL WHERE 1||(1 LIKE 1);

            – Error: SQL command not properly ended" error
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 IN (1,2,3);
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 NOT IN (1,2,3);
            SELECT 'x' FROM DUAL WHERE (1 LIKE 1) IN (1,2,3);
            SELECT 'x' FROM DUAL WHERE (1 LIKE 1) NOT IN (1,2,3);
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 BETWEEN 1 AND 1;
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 NOT BETWEEN 1 AND 1;
            SELECT 'x' FROM DUAL WHERE (1 LIKE 1) BETWEEN 1 AND 1;
            SELECT 'x' FROM DUAL WHERE (1 LIKE 1) NOT BETWEEN 1 AND 1;
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 IS NULL;
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 IS NOT NULL;
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 (IS NULL);
            SELECT 'x' FROM DUAL WHERE 1 LIKE 1 (NOT NULL);
            SELECT 'x' FROM DUAL WHERE (1 LIKE 1) IS NULL;
            SELECT 'x' FROM DUAL WHERE (1 LIKE 1) IS NOT NULL;
            SELECT 'x' FROM DUAL WHERE (1 LIKE 1)||1;

            Note: Oracle supports additive and multiplicative expressions in the right (the pattern) argument of the LIKE predicate. They also should be enabled.

            bar Alexander Barkov added a comment - - edited Results returned by the Oracle database: – One row: SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1; SELECT 'x' FROM DUAL WHERE 11 LIKE 1||1; SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11; SELECT 'x' FROM DUAL WHERE 1||1 LIKE 11; SELECT 'x' FROM DUAL WHERE 1 LIKE +1; SELECT 'x' FROM DUAL WHERE -1 LIKE -1; SELECT 'x' FROM DUAL WHERE 1 LIKE (1); SELECT 'x' FROM DUAL WHERE 1 LIKE 1+0; SELECT 'x' FROM DUAL WHERE 1 LIKE 1-0; SELECT 'x' FROM DUAL WHERE 1+0 LIKE 1; SELECT 'x' FROM DUAL WHERE 1+0 LIKE 1; SELECT 'x' FROM DUAL WHERE 1 LIKE 1*1; SELECT 'x' FROM DUAL WHERE 1 LIKE 2/2; SELECT 'x' FROM DUAL WHERE 1*2 LIKE 2; SELECT 'x' FROM DUAL WHERE 2/2 LIKE 1; SELECT 'x' FROM DUAL WHERE 1 LIKE CAST(1 AS VARCHAR(10)); SELECT 'x' FROM DUAL WHERE 1 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END; SELECT 'x' FROM DUAL WHERE 1 LIKE COALESCE(1+0,1); – Error: missing right parenthesis SELECT 'x' FROM DUAL WHERE 1*(2 LIKE 2); SELECT 'x' FROM DUAL WHERE 1/(2 LIKE 2); SELECT 'x' FROM DUAL WHERE 1+(2 LIKE 2); SELECT 'x' FROM DUAL WHERE 1-(2 LIKE 2); SELECT 'x' FROM DUAL WHERE 1 LIKE (1 IN (1,2,3)); SELECT 'x' FROM DUAL WHERE 1 LIKE (1 NOT IN (1,2,3)); SELECT 'x' FROM DUAL WHERE 1 LIKE (1 BETWEEN 1 AND 1); SELECT 'x' FROM DUAL WHERE 1 LIKE (1 NOT BETWEEN 1 AND 1); SELECT 'x' FROM DUAL WHERE 1||(1 LIKE 1); – Error: SQL command not properly ended" error SELECT 'x' FROM DUAL WHERE 1 LIKE 1 IN (1,2,3); SELECT 'x' FROM DUAL WHERE 1 LIKE 1 NOT IN (1,2,3); SELECT 'x' FROM DUAL WHERE (1 LIKE 1) IN (1,2,3); SELECT 'x' FROM DUAL WHERE (1 LIKE 1) NOT IN (1,2,3); SELECT 'x' FROM DUAL WHERE 1 LIKE 1 BETWEEN 1 AND 1; SELECT 'x' FROM DUAL WHERE 1 LIKE 1 NOT BETWEEN 1 AND 1; SELECT 'x' FROM DUAL WHERE (1 LIKE 1) BETWEEN 1 AND 1; SELECT 'x' FROM DUAL WHERE (1 LIKE 1) NOT BETWEEN 1 AND 1; SELECT 'x' FROM DUAL WHERE 1 LIKE 1 IS NULL; SELECT 'x' FROM DUAL WHERE 1 LIKE 1 IS NOT NULL; SELECT 'x' FROM DUAL WHERE 1 LIKE 1 (IS NULL); SELECT 'x' FROM DUAL WHERE 1 LIKE 1 (NOT NULL); SELECT 'x' FROM DUAL WHERE (1 LIKE 1) IS NULL; SELECT 'x' FROM DUAL WHERE (1 LIKE 1) IS NOT NULL; SELECT 'x' FROM DUAL WHERE (1 LIKE 1)||1; Note: Oracle supports additive and multiplicative expressions in the right (the pattern) argument of the LIKE predicate. They also should be enabled.

            People

              bar Alexander Barkov
              halfspawn Jérôme Brauge
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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