You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Shravya Nethula <sh...@aline-consulting.com> on 2019/12/02 05:41:19 UTC
Re: Select query not working as expected

Thank you for the information Ivan


Regards,

Shravya Nethula,

BigData Developer,

[cid:bc102604-8d21-4411-9c69-7f63b77c7fb5]

Hyderabad.

________________________________
From: Ivan Pavlukhin <vo...@gmail.com>
Sent: Saturday, November 30, 2019 12:01 PM
To: user@ignite.apache.org <us...@ignite.apache.org>
Subject: Re: Select query not working as expected

Hi Shravya,

I suppose I managed to get expected result after rewriting query to:
SELECT Person.id, name, age, city_id FROM Person JOIN City on City.id = 1001

Hope it will help you as well.

The problem here seems to be default "colocated" query processing. Basically subselect "SELECT city_id FROM city WHERE id=1001" is executed locally on each node and only one node has city with id 1001. You can read more about colocated query processing in [1].

[1] https://apacheignite-sql.readme.io/docs/distributed-joins

пт, 29 нояб. 2019 г. в 14:52, Ilya Kasnacheev <il...@gmail.com>>:
Hello!

Why do you have both city and SQL_PUBLIC_CITY cache? Where is the cache for person table?

Do you get same results when connecting to cluster via sqlline?

Regards,
--
Ilya Kasnacheev


пт, 29 нояб. 2019 г. в 11:54, Shravya Nethula <sh...@aline-consulting.com>>:
Hi Ilya,

Yes the city_id is not null for row 1001. Please find the screenshots below:
[cid:16eb700bb61af0eeb463]

[cid:16eb700bb6165611be14]


Regards,

Shravya Nethula,

BigData Developer,

[cid:16eb700bb613b6a47d65]

Hyderabad.

________________________________
From: Ilya Kasnacheev <il...@gmail.com>>
Sent: Thursday, November 28, 2019 10:11 PM
To: user@ignite.apache.org<ma...@ignite.apache.org> <us...@ignite.apache.org>>
Subject: Re: Select query not working as expected

Hello!

Are you actually sure that city_id is not null for this row of city table?

Regards,
--
Ilya Kasnacheev


чт, 28 нояб. 2019 г. в 15:47, Shravya Nethula <sh...@aline-consulting.com>>:
Hi,

I am trying to select same city_id value for all the rows in "person" table,  using the following query:
SELECT id, name, age, (SELECT city_id FROM city WHERE id=1001) AS cityId FROM person
But it is not giving results as expected. Please find the screenshot below:

[cid:16eb700bb60ba2d63e81]

Is the select query wrong?
Is there any syntax error?
Or is there any other way in which I can achieve this?





Regards,

Shravya Nethula,

BigData Developer,

[cid:16eb700bb616bbc7ef62]

Hyderabad.


--
Best regards,
Ivan Pavlukhin