[MDEV-31424] White space in a query seems to affect parsing Created: 2023-06-07  Updated: 2023-06-12  Resolved: 2023-06-12

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.5.19
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alessandro Vesely Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

mariadb Ver 15.1 Distrib 10.5.19-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper


 Description   

It is not "subquery", just "query".

Unbelievably, I was entering a query that someone sent me by email. It was split in several lines, which I copied and pasted in a client terminal. It replied with an error. I could spot no errors, and the query was very similar to a previous one which only had a single find_in_set condition. So I started recalling the query by ↑(up arrow) and removing a space. The last attempt succeeded. Here's the last but one and the last:

MariaDB [mail]>  Select m.id as messageid, d.domain as SpfDomain     from message_in as m, domain as d, msg_ref as r     where     r.domain = d.id and r.message_in = m.id and find_in_set('spf', r.auth)>0    and find_in_set('aligned', r.auth)>0 limit 2;
 
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 ' and find_in_set('aligned', r.auth)>0 limit 2' at line 1
 
MariaDB [mail]> 
MariaDB [mail]>  Select m.id as messageid, d.domain as SpfDomain     from message_in as m, domain as d, msg_ref as r     where     r.domain = d.id and r.message_in = m.id and find_in_set('spf', r.auth)>0   and find_in_set('aligned', r.auth)>0 limit 2;
 
+-----------+------------------------+
 
| messageid | SpfDomain              |
 
+-----------+------------------------+
 
|   1229557 | mailop.org             |
 
|   1229558 | studiolegalemantini.it |
 
+-----------+------------------------+
 
2 rows in set (0.001 sec)

The difference is a single space before the last `and`.

The database is depicted here

 Comments   
Comment by Alice Sherepa [ 2023-06-12 ]

that is because that was not a whitespace, but a a non-breaking space:

MariaDB [test_zfilter]>  ;
 
ERROR: No query specified
 
 
 
MariaDB [test_zfilter]>  ;
 
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
 
MariaDB [test_zfilter]> select hex(' ');
 
+-----------+
 
| hex(' ')  |
 
+-----------+
 
| C2A0      |
 
+-----------+
 
1 row in set (0,000 sec)

Comment by Alessandro Vesely [ 2023-06-12 ]

Nearly. There were CRs:

00000000  20 53 65 6c 65 63 74 20  6d 2e 69 64 20 61 73 20  | Select m.id as |
 
00000010  6d 65 73 73 61 67 65 69  64 2c 20 64 2e 64 6f 6d  |messageid, d.dom|
 
00000020  61 69 6e 20 61 73 20 53  70 66 44 6f 6d 61 69 6e  |ain as SpfDomain|
 
00000030  0d 0a 20 20 20 66 72 6f  6d 20 6d 65 73 73 61 67  |..   from messag|
 
00000040  65 5f 69 6e 20 61 73 20  6d 2c 20 64 6f 6d 61 69  |e_in as m, domai|
 
00000050  6e 20 61 73 20 64 2c 20  6d 73 67 5f 72 65 66 20  |n as d, msg_ref |
 
00000060  61 73 20 72 0d 0a 20 20  20 77 68 65 72 65 0d 0a  |as r..   where..|
 
00000070  20 20 20 72 2e 64 6f 6d  61 69 6e 20 3d 20 64 2e  |   r.domain = d.|
 
00000080  69 64 20 61 6e 64 20 72  2e 6d 65 73 73 61 67 65  |id and r.message|
 
00000090  5f 69 6e 20 3d 20 6d 2e  69 64 0d 0a c2 a0 20 20  |_in = m.id....  |
 
000000a0  61 6e 64 20 66 69 6e 64  5f 69 6e 5f 73 65 74 28  |and find_in_set(|
 
000000b0  27 73 70 66 27 2c 20 72  2e 61 75 74 68 29 3e 30  |'spf', r.auth)>0|
 
000000c0  0d 0a 20 c2 a0 20 61 6e  64 20 66 69 6e 64 5f 69  |.. .. and find_i|
 
000000d0  6e 5f 73 65 74 28 27 61  6c 69 67 6e 65 64 27 2c  |n_set('aligned',|
 
000000e0  20 72 2e 61 75 74 68 29  3e 30 3b 0d 0a 0d 0a     | r.auth)>0;....|

However, I couldn't reproduce the error on a simpler table.

Generated at Thu Feb 08 10:23:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.