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.
>
>