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)
Locked
