[MXS-3402] UNION in subquery causes query parsing to fail Created: 2021-02-10  Updated: 2023-09-14  Resolved: 2023-09-14

Status: Closed
Project: MariaDB MaxScale
Component/s: qc_sqlite
Affects Version/s: 2.3.20
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: danielforever Assignee: Johan Wikman
Resolution: Won't Fix Votes: 0
Labels: None

Sprint: MXS-SPRINT-190

 Description   

The following SQL statements fail to parse due to the fact that the SELECT statements are surrounded by an extra set of parentheses.

./compare -v 3 -0 qc_sqlite -s "SELECT * FROM ((SELECT @@last_insert_id) UNION (SELECT @@last_insert_id)) t"
 
(0): SELECT * FROM ((SELECT @@last_insert_id) UNION (SELECT @@last_insert_id)) t
 
qc_parse                 : Ok : QC_QUERY_TOKENIZED
 
qc_get_type_mask         : Ok : QUERY_TYPE_READ
 
qc_get_operation         : Ok : QUERY_OP_SELECT
 
qc_get_created_table_name: Ok : NULL
 
qc_is_drop_table_query   : Ok : 0
 
qc_get_table_names       : Ok : 
qc_get_table_names(full) : Ok : 
qc_query_has_clause      : Ok : 0
 
qc_get_database_names    : Ok : 
qc_get_prepare_name      : Ok : NULL
 
qc_get_field_info        : Ok : 
qc_get_function_info     : Ok :

Moving the UNION out from the subselect or removing the parentheses around it solves the problem:

./compare -v 3 -0 qc_sqlite -s "SELECT * FROM (SELECT @@last_insert_id UNION SELECT @@last_insert_id) t"
 
(0): SELECT * FROM (SELECT @@last_insert_id UNION SELECT @@last_insert_id) t
 
qc_parse                 : Ok : QC_QUERY_PARSED
 
qc_get_type_mask         : Ok : QUERY_TYPE_READ|QUERY_TYPE_MASTER_READ
 
qc_get_operation         : Ok : QUERY_OP_SELECT
 
qc_get_created_table_name: Ok : NULL
 
qc_is_drop_table_query   : Ok : 0
 
qc_get_table_names       : Ok : 
qc_get_table_names(full) : Ok : 
qc_query_has_clause      : Ok : 0
 
qc_get_database_names    : Ok : 
qc_get_prepare_name      : Ok : NULL
 
qc_get_field_info        : Ok : *
 
qc_get_function_info     : Ok : 
 
 
./compare -v 3 -0 qc_sqlite -s "(SELECT @@last_insert_id) UNION (SELECT @@last_insert_id)"
 
(0): (SELECT @@last_insert_id) UNION (SELECT @@last_insert_id)
 
qc_parse                 : Ok : QC_QUERY_PARTIALLY_PARSED
 
qc_get_type_mask         : Ok : QUERY_TYPE_READ|QUERY_TYPE_MASTER_READ
 
qc_get_operation         : Ok : QUERY_OP_SELECT
 
qc_get_created_table_name: Ok : NULL
 
qc_is_drop_table_query   : Ok : 0
 
qc_get_table_names       : Ok : 
qc_get_table_names(full) : Ok : 
qc_query_has_clause      : Ok : 0
 
qc_get_database_names    : Ok : 
qc_get_prepare_name      : Ok : NULL
 
qc_get_field_info        : Ok : 
qc_get_function_info     : Ok :

Original description:

first one is:

select * from (SELECT s.hrdocid  FROM scheduleteacher s   where s.orgid = @OrgId UNION SELECT u.hrdocId  FROM `user` u          WHERE u.orgid = @OrgId) t;

second one is:

select * from ((SELECT s.hrdocid  FROM scheduleteacher s   where s.orgid = @OrgId) UNION (SELECT u.hrdocId  FROM `user` u          WHERE u.orgid = @OrgId)) t

for 2.2:
user variable in the first one can be parsed, but not the second one.

for 2.3:

it seems that both SQLs are not parsed correctly.



 Comments   
Comment by Johan Wikman [ 2021-02-10 ]

Could you be more specific regarding what you mean by are not parsed correctly?

Comment by danielforever [ 2021-02-10 ]

The query type from the query classifier is: QUERY_TYPE_READ

However, the session logic needs QUERY_TYPE_USERVAR_READ to be returned as well to be able to route the SQL to the master node.

Comment by markus makela [ 2021-02-15 ]

daniel_xu_forever do you use use_sql_variables_in=master?

Comment by danielforever [ 2021-02-18 ]

yes, use_sql_variables_in=master is our default setting

Comment by markus makela [ 2021-02-18 ]

OK, so this means it fails to detect the user variable in the query and proceeds to route it to a slave.

Comment by danielforever [ 2021-02-19 ]

Exactly, will you plan to fix this in near future?

Comment by danielforever [ 2021-04-29 ]

@markus makela any update on this?

Comment by markus makela [ 2021-04-29 ]

No updates yet. I'll update the description of this issue to more accurately describe what the actual problem is.

Comment by Johan Wikman [ 2023-09-14 ]

So the problem is that the sqlite3 parser does not support extra parenthesis around the SELECTs of a UNION. For instance:

sqlite> select * from ((select 1) UNION (select 1)) t;
 
Error: near "UNION": syntax error
 
sqlite> select * from (select 1 UNION select 1) t;
 
1

Adding support for that seems to require a considerable amount of changes in the grammar; so considerable that the work it would entail is currently hard to justify.

Generated at Thu Feb 08 04:21:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.