[jira] [Created] (IGNITE-12862) Subquery Limit

["Leng Sheng Hong (Jira)" <ji...@apache.org>]

Leng Sheng Hong created IGNITE-12862:
----------------------------------------

             Summary: Subquery Limit
                 Key: IGNITE-12862
                 URL: https://issues.apache.org/jira/browse/IGNITE-12862
             Project: Ignite
          Issue Type: Bug
          Components: sql
    Affects Versions: 2.8
         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
            Reporter: Leng Sheng Hong


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:
{code:java}
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
{code}
However With Limit clause:
{code:java}
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
{code}
The server will return an error:
{code:java}
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]
{code}
The tables are created with partitioned template with backups of 2, except for the role table which is created with template=replicated

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)