On 10/12/2017 03:46 PM, Marko Tiikkaja wrote:
The subquery:
select n from tv limit 1
could in theory return any row due to the lack of ORDER BY. What I'm
guessing happened is that you're seeing a synchronized sequential scan
in follow-up queries. Add an ORDER BY.
Bang on . After adding order by clause - i am getting same result
consistently. but why i got the different result after canceling the
query only?
test=# \c f2
You are now connected to database "f2" as user "centos".
f2=# create table tv(n int,n1 char(100));
CREATE TABLE
f2=# insert into tv values (generate_series(1,1000000),'aaa');
INSERT 0 1000000
f2=# insert into tv values (generate_series(10000,1000000),'a');
INSERT 0 990001
f2=# analyze tv;
ANALYZE
f2=# vacuum tv;
VACUUM
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
---
1
(1 row)
f2=#
even after restarting the server , i am getting the same result.
now after canceling the operation , next time - result is coming different ?
f2=# SELECT * FROM ( SELECT n from tv where n!=ALL (select * from
(select n from tv) c)) as c ;
^CCancel request sent
ERROR: canceling statement due to user request
f2=# SELECT * FROM ( SELECT n from tv where n= (select * from
(select n from tv limit 1) c)) as c ;
n
------
3713
(1 row)
--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
