Re: [HACKERS] Query started showing wrong result after Ctrl+c

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