-- tests for tidscans
CREATE TABLE tidscan(id integer);
-- only insert a few rows, we don't want to spill onto a second table page
INSERT INTO tidscan VALUES (1), (2), (3);
-- show ctids
-- XXX since rows come from different datanodes, some TIDs can be duplicated in
-- XL
SELECT ctid, * FROM tidscan;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,1) |  3
(3 rows)

-- ctid equality - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
               QUERY PLAN                
-----------------------------------------
 Remote Fast Query Execution
   Node/s: datanode_1, datanode_2
   ->  Tid Scan on tidscan
         TID Cond: (ctid = '(0,1)'::tid)
(4 rows)

SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
 ctid  | id 
-------+----
 (0,1) |  1
 (0,1) |  3
(2 rows)

EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
               QUERY PLAN                
-----------------------------------------
 Remote Fast Query Execution
   Node/s: datanode_1, datanode_2
   ->  Tid Scan on tidscan
         TID Cond: ('(0,1)'::tid = ctid)
(4 rows)

SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,1) |  3
(2 rows)

-- ctid = ScalarArrayOp - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
                         QUERY PLAN                          
-------------------------------------------------------------
 Remote Fast Query Execution
   Node/s: datanode_1, datanode_2
   ->  Tid Scan on tidscan
         TID Cond: (ctid = ANY ('{"(0,1)","(0,2)"}'::tid[]))
(4 rows)

SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,1) |  3
(3 rows)

-- ctid != ScalarArrayOp - can't be implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
                         QUERY PLAN                         
------------------------------------------------------------
 Remote Fast Query Execution
   Node/s: datanode_1, datanode_2
   ->  Seq Scan on tidscan
         Filter: (ctid <> ANY ('{"(0,1)","(0,2)"}'::tid[]))
(4 rows)

SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,1) |  3
(3 rows)

-- tid equality extracted from sub-AND clauses
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan
WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Remote Fast Query Execution
   Node/s: datanode_1, datanode_2
   ->  Tid Scan on tidscan
         TID Cond: ((ctid = ANY ('{"(0,2)","(0,3)"}'::tid[])) OR (ctid = '(0,1)'::tid))
         Filter: (((id = 3) AND (ctid = ANY ('{"(0,2)","(0,3)"}'::tid[]))) OR ((ctid = '(0,1)'::tid) AND (id = 1)))
(5 rows)

SELECT ctid, * FROM tidscan
WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
 ctid  | id 
-------+----
 (0,1) |  1
(1 row)

-- exercise backward scan and rewind
BEGIN;
DECLARE c CURSOR FOR
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
FETCH ALL FROM c;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,1) |  3
(3 rows)

-- XL does not support BACKWARD scan of RemoteSubplan/RemoteSubquery and
-- hence the next statement fails
FETCH BACKWARD 1 FROM c;
ERROR:  cursor can only scan forward
HINT:  Declare it with SCROLL option to enable backward scan.
FETCH FIRST FROM c;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- tidscan via CURRENT OF
BEGIN;
DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan;
FETCH NEXT FROM c; -- skip one row
 ctid  | id 
-------+----
 (0,1) |  1
(1 row)

FETCH NEXT FROM c;
 ctid  | id 
-------+----
 (0,2) |  2
(1 row)

-- perform update
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
-- XL does not support WHERE CURRENT OF and hence the next
-- statement fails
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ERROR:  WHERE CURRENT OF clause not yet supported
FETCH NEXT FROM c;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
-- perform update
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
SELECT * FROM tidscan;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
-- position cursor past any rows
FETCH NEXT FROM c;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
-- should error out
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
DROP TABLE tidscan;