Re: 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