[IGNITE-12862] Subquery Limit Failed to Parse

• Type: Bug
• Status: Open
• Priority: Blocker
• Resolution: Unresolved
• Affects Version/s: 2.8
• Component/s: sql

Environment

Mac OS 10.15.1

Run on a single node and also tried a cluster of 3 server nodes

SQL is being executed with JDBC thin client and also DBeaver with distributedJoins=true

• Assignee: Unassigned
• Reporter: Leng Sheng Hong
• Created: 04/Apr/20 10:22
• Updated: 04/Apr/20 10:27

Description

I am trying to test a query involving SQL distributed joins with subquery. However it seems to have some bugs when I have Limit clause in the subquery. (user <-> role has a many to many relationship)

Without LIMIT clause it works:

SELECT 
	"user".id, "user".name,
	"role".id as "role_id", "role".role_name as "role_name", 
	"address".id as "address_id", "address".street1 as "address_street1", 
	"address".street2 as "address_street2"
FROM (SELECT * FROM "user" ORDER BY "user".id ASC) AS "user"
LEFT JOIN "address" on "address".user_id = "user".id
LEFT JOIN "user_has_role" on  "user_has_role".user_id = "user".id
LEFT JOIN "role" on "role".id = "user_has_role".role_id
ORDER BY "user".id ASC

However With Limit clause:

SELECT 
	"user".id, "user".name,
	"role".id as "role_id", "role".role_name as "role_name", 
	"address".id as "address_id", "address".street1 as "address_street1", 
	"address".street2 as "address_street2"
FROM (SELECT * FROM "user" ORDER BY "user".id ASC LIMIT 10) AS "user"
LEFT JOIN "address" on "address".user_id = "user".id
LEFT JOIN "user_has_role" on  "user_has_role".user_id = "user".id
LEFT JOIN "role" on "role".id = "user_has_role".role_id
ORDER BY "user".id ASC

The server will return an error:

SQL Error [1001] [42000]: Failed to parse query. Column "user__Z1.ID" not found; SQL statement:
SELECT
"__Z2"."USER_ID" "__C1_0",
"__Z2"."ID" "__C1_1",
"__Z4"."ROLE_NAME" "__C1_2",
"__Z4"."ID" "__C1_3",
"__Z2"."STREET2" "__C1_4",
"__Z2"."STREET1" "__C1_5"
FROM "PUBLIC"."address" "__Z2" 
 LEFT OUTER JOIN "PUBLIC"."user_has_role" "__Z3" 
 ON "__Z3"."USER_ID" = "user__Z1"."ID" 
 LEFT OUTER JOIN "PUBLIC"."role" "__Z4" 
 ON "__Z4"."ID" = "__Z3"."ROLE_ID"
ORDER BY 1 [42122-199]

The tables are created with partitioned template with backups of 2, except for the role table which is created with template=replicated