Select query not working as expected

classic Classic list List threaded Threaded
7 messages Options
Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

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:



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.

ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]>:
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.

Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

Re: Select query not working as expected

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 <[hidden email]>
Sent: Thursday, November 28, 2019 10:11 PM
To: [hidden email] <[hidden email]>
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 <[hidden email]>:
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.

ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]>:
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 <[hidden email]>
Sent: Thursday, November 28, 2019 10:11 PM
To: [hidden email] <[hidden email]>
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 <[hidden email]>:
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.

Ivan Pavlukhin Ivan Pavlukhin
Reply | Threaded
Open this post in threaded view
|

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


пт, 29 нояб. 2019 г. в 14:52, Ilya Kasnacheev <[hidden email]>:
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 <[hidden email]>:
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 <[hidden email]>
Sent: Thursday, November 28, 2019 10:11 PM
To: [hidden email] <[hidden email]>
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 <[hidden email]>:
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
Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

Re: Select query not working as expected

Thank you for the information Ivan

Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Ivan Pavlukhin <[hidden email]>
Sent: Saturday, November 30, 2019 12:01 PM
To: [hidden email] <[hidden email]>
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].


пт, 29 нояб. 2019 г. в 14:52, Ilya Kasnacheev <[hidden email]>:
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 <[hidden email]>:
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 <[hidden email]>
Sent: Thursday, November 28, 2019 10:11 PM
To: [hidden email] <[hidden email]>
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 <[hidden email]>:
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
Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

Re: Select query not working as expected

In reply to this post by ilya.kasnacheev
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,


Hyderabad.


From: Ilya Kasnacheev <[hidden email]>
Sent: Friday, November 29, 2019 5:22 PM
To: Shravya Nethula <[hidden email]>
Cc: [hidden email] <[hidden email]>
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 <[hidden email]>:
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 <[hidden email]>
Sent: Thursday, November 28, 2019 10:11 PM
To: [hidden email] <[hidden email]>
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 <[hidden email]>:
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.