Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.3(EOL)
-
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
-
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
- links to
Activity
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.
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 1MariaDB [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)