[MDEV-17359] || operator is not understand by "like" in Oracle Created: 2018-10-03  Updated: 2018-10-19  Resolved: 2018-10-19

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.3.9, 10.3
Fix Version/s: 10.3.11

Type: Bug Priority: Major
Reporter: Jérôme Brauge Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

All OS

Issue Links:
Blocks
is blocked by MDEV-17374 Shift/reduce conflicts because of SOU... Closed
Relates
relates to MDEV-16156 PIPES_AS_CONCAT does not work well Open
relates to MDEV-16186 Concatenation operator || returns wro... Closed
relates to MDEV-17406 Problems with LIKE using a parenthesi... Open
relates to MDEV-17408 VIEW is incorrectly defined for a com... Closed

 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

 Comments   
Comment by Alice Sherepa [ 2018-10-03 ]

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)

Comment by Alexander Barkov [ 2018-10-04 ]

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.

Generated at Thu Feb 08 08:35:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.