[
https://issues.apache.org/jira/browse/IGNITE-12862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17075108#comment-17075108
]
Leng Sheng Hong commented on IGNITE-12862:
------------------------------------------
I realized that if I remove the address table which is Join on an index
(address.user_id) it will work with the LIMIT clause
{code:java}
SELECT "user".id, "user".name, "role".id as "role_id", "role".role_name as
"role_name" FROM (SELECT * FROM "user" ORDER BY "user".id ASC LIMIT 5) AS
"user" 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}
Is there anything missing from my configurations? The address table is created
with template=partition as well
> Subquery Limit Failed to Parse
> ------------------------------
>
> 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
> Priority: Blocker
>
> 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)
