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/11/28 12:47:15 UTC
Select query not working as expected
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:8255bcba-f678-4814-91e7-7b32c510a607]
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:58267690-e75f-4ea8-b011-dcc384a7f010]
Hyderabad.
Re: Select query not working as expected
Posted by Shravya Nethula <sh...@aline-consulting.com>.
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
Re: Select query not working as expected
Posted by Ivan Pavlukhin <vo...@gmail.com>.
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 <
> shravya.nethula@aline-consulting.com>:
>
>> Hi Ilya,
>>
>> Yes the city_id is not null for row 1001. Please find the screenshots
>> below:
>>
>>
>>
>>
>> Regards,
>>
>> Shravya Nethula,
>>
>> BigData Developer,
>>
>>
>> Hyderabad.
>>
>> ------------------------------
>> *From:* Ilya Kasnacheev <il...@gmail.com>
>> *Sent:* Thursday, November 28, 2019 10:11 PM
>> *To:* user@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 <
>> shravya.nethula@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:
>>
>>
>>
>> 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,
>>
>>
>> Hyderabad.
>>
>>
--
Best regards,
Ivan Pavlukhin
Re: Select query not working as expected
Posted by Shravya Nethula <sh...@aline-consulting.com>.
Hi Ilya,
SQL_PUBLIC_CITY cache is the actual SQL table
city cache stores some metaData regarding the SQL table
As you suggested, I will try with sqlLine.
Regards,
Shravya Nethula,
BigData Developer,
[cid:cf1f3083-f16d-467b-afb0-91141399ee5a]
Hyderabad.
________________________________
From: Ilya Kasnacheev <il...@gmail.com>
Sent: Friday, November 29, 2019 5:22 PM
To: Shravya Nethula <sh...@aline-consulting.com>
Cc: user@ignite.apache.org <us...@ignite.apache.org>
Subject: Re: Select query not working as expected
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.
Re: Select query not working as expected
Posted by 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 <
shravya.nethula@aline-consulting.com>:
> Hi Ilya,
>
> Yes the city_id is not null for row 1001. Please find the screenshots
> below:
>
>
>
>
> Regards,
>
> Shravya Nethula,
>
> BigData Developer,
>
>
> Hyderabad.
>
> ------------------------------
> *From:* Ilya Kasnacheev <il...@gmail.com>
> *Sent:* Thursday, November 28, 2019 10:11 PM
> *To:* user@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 <
> shravya.nethula@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:
>
>
>
> 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,
>
>
> Hyderabad.
>
>
Re: Select query not working as expected
Posted by Shravya Nethula <sh...@aline-consulting.com>.
Hi Ilya,
Yes the city_id is not null for row 1001. Please find the screenshots below:
[cid:2e484017-7078-4daa-9c77-21afb2ed2fb4]
[cid:c74aefbb-91cd-4906-b51c-b26696eaa2b3]
Regards,
Shravya Nethula,
BigData Developer,
[cid:fabe88b8-5e5c-4847-9d7f-40e3524b932f]
Hyderabad.
________________________________
From: Ilya Kasnacheev <il...@gmail.com>
Sent: Thursday, November 28, 2019 10:11 PM
To: user@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:16eb2e369f2ba2d63e81]
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:16eb2e369f36bbc7ef62]
Hyderabad.
Re: Select query not working as expected
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <
shravya.nethula@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:
>
>
>
> 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,
>
>
> Hyderabad.
>
>