-- -- SELECT -- --Testcase 43: CREATE EXTENSION sqlite_fdw; --Testcase 44: CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test_core.db'); --Testcase 45: CREATE FOREIGN TABLE onek ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name ) SERVER sqlite_svr; --Testcase 46: CREATE FOREIGN TABLE onek2 ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name ) SERVER sqlite_svr; --Testcase 47: CREATE FOREIGN TABLE INT8_TBL( q1 int8 OPTIONS (key 'true'), q2 int8 OPTIONS (key 'true') ) SERVER sqlite_svr; --Testcase 48: CREATE FOREIGN TABLE person ( name text, age int4, location point ) SERVER sqlite_svr; -- btree index -- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1 -- --Testcase 1: SELECT * FROM onek WHERE onek.unique1 < 10 ORDER BY onek.unique1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx (10 rows) -- -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 -- --Testcase 2: SELECT onek.unique1, onek.stringu1 FROM onek WHERE onek.unique1 < 20 ORDER BY unique1 using >; unique1 | stringu1 ---------+---------- 19 | TAAAAA 18 | SAAAAA 17 | RAAAAA 16 | QAAAAA 15 | PAAAAA 14 | OAAAAA 13 | NAAAAA 12 | MAAAAA 11 | LAAAAA 10 | KAAAAA 9 | JAAAAA 8 | IAAAAA 7 | HAAAAA 6 | GAAAAA 5 | FAAAAA 4 | EAAAAA 3 | DAAAAA 2 | CAAAAA 1 | BAAAAA 0 | AAAAAA (20 rows) -- -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 -- --Testcase 3: SELECT onek.unique1, onek.stringu1 FROM onek WHERE onek.unique1 > 980 ORDER BY stringu1 using <; unique1 | stringu1 ---------+---------- 988 | AMAAAA 989 | BMAAAA 990 | CMAAAA 991 | DMAAAA 992 | EMAAAA 993 | FMAAAA 994 | GMAAAA 995 | HMAAAA 996 | IMAAAA 997 | JMAAAA 998 | KMAAAA 999 | LMAAAA 981 | TLAAAA 982 | ULAAAA 983 | VLAAAA 984 | WLAAAA 985 | XLAAAA 986 | YLAAAA 987 | ZLAAAA (19 rows) -- -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | -- sort +1d -2 +0nr -1 -- --Testcase 4: SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 > 980 ORDER BY string4 using <, unique1 using >; unique1 | string4 ---------+--------- 999 | AAAAxx 995 | AAAAxx 983 | AAAAxx 982 | AAAAxx 981 | AAAAxx 998 | HHHHxx 997 | HHHHxx 993 | HHHHxx 990 | HHHHxx 986 | HHHHxx 996 | OOOOxx 991 | OOOOxx 988 | OOOOxx 987 | OOOOxx 985 | OOOOxx 994 | VVVVxx 992 | VVVVxx 989 | VVVVxx 984 | VVVVxx (19 rows) -- -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | -- sort +1dr -2 +0n -1 -- --Testcase 5: SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 > 980 ORDER BY string4 using >, unique1 using <; unique1 | string4 ---------+--------- 984 | VVVVxx 989 | VVVVxx 992 | VVVVxx 994 | VVVVxx 985 | OOOOxx 987 | OOOOxx 988 | OOOOxx 991 | OOOOxx 996 | OOOOxx 986 | HHHHxx 990 | HHHHxx 993 | HHHHxx 997 | HHHHxx 998 | HHHHxx 981 | AAAAxx 982 | AAAAxx 983 | AAAAxx 995 | AAAAxx 999 | AAAAxx (19 rows) -- -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | -- sort +0nr -1 +1d -2 -- --Testcase 6: SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 < 20 ORDER BY unique1 using >, string4 using <; unique1 | string4 ---------+--------- 19 | OOOOxx 18 | VVVVxx 17 | HHHHxx 16 | OOOOxx 15 | VVVVxx 14 | AAAAxx 13 | OOOOxx 12 | AAAAxx 11 | OOOOxx 10 | AAAAxx 9 | HHHHxx 8 | HHHHxx 7 | VVVVxx 6 | OOOOxx 5 | HHHHxx 4 | HHHHxx 3 | VVVVxx 2 | OOOOxx 1 | OOOOxx 0 | OOOOxx (20 rows) -- -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | -- sort +0n -1 +1dr -2 -- --Testcase 7: SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 < 20 ORDER BY unique1 using <, string4 using >; unique1 | string4 ---------+--------- 0 | OOOOxx 1 | OOOOxx 2 | OOOOxx 3 | VVVVxx 4 | HHHHxx 5 | HHHHxx 6 | OOOOxx 7 | VVVVxx 8 | HHHHxx 9 | HHHHxx 10 | AAAAxx 11 | OOOOxx 12 | AAAAxx 13 | OOOOxx 14 | AAAAxx 15 | VVVVxx 16 | OOOOxx 17 | HHHHxx 18 | VVVVxx 19 | OOOOxx (20 rows) -- -- test partial btree indexes -- -- As of 7.2, planner probably won't pick an indexscan without stats, -- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan -- followed by sort, because that could hide index ordering problems. -- --ANALYZE onek2; SET enable_seqscan TO off; SET enable_bitmapscan TO off; SET enable_sort TO off; -- -- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1 -- --Testcase 8: SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10 order by onek2.unique1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx (10 rows) -- -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 -- --Testcase 9: SELECT onek2.unique1, onek2.stringu1 FROM onek2 WHERE onek2.unique1 < 20 ORDER BY unique1 using >; unique1 | stringu1 ---------+---------- 19 | TAAAAA 18 | SAAAAA 17 | RAAAAA 16 | QAAAAA 15 | PAAAAA 14 | OAAAAA 13 | NAAAAA 12 | MAAAAA 11 | LAAAAA 10 | KAAAAA 9 | JAAAAA 8 | IAAAAA 7 | HAAAAA 6 | GAAAAA 5 | FAAAAA 4 | EAAAAA 3 | DAAAAA 2 | CAAAAA 1 | BAAAAA 0 | AAAAAA (20 rows) -- -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 -- --Testcase 10: SELECT onek2.unique1, onek2.stringu1 FROM onek2 WHERE onek2.unique1 > 980 ORDER BY onek2.unique1; unique1 | stringu1 ---------+---------- 981 | TLAAAA 982 | ULAAAA 983 | VLAAAA 984 | WLAAAA 985 | XLAAAA 986 | YLAAAA 987 | ZLAAAA 988 | AMAAAA 989 | BMAAAA 990 | CMAAAA 991 | DMAAAA 992 | EMAAAA 993 | FMAAAA 994 | GMAAAA 995 | HMAAAA 996 | IMAAAA 997 | JMAAAA 998 | KMAAAA 999 | LMAAAA (19 rows) RESET enable_seqscan; RESET enable_bitmapscan; RESET enable_sort; --Testcase 11: SELECT two, stringu1, ten, string4 INTO TABLE tmp FROM onek; -- -- awk '{print $1,$2;}' person.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | -- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data -- -- SELECT name, age FROM person*; ??? check if different --Testcase 12: SELECT p.name, p.age FROM person* p; name | age ---------+----- mike | 40 joe | 20 sally | 34 sandra | 19 alex | 30 sue | 50 denise | 24 sarah | 88 teresa | 38 nan | 28 leah | 68 wendy | 78 melissa | 28 joan | 18 mary | 8 jane | 58 liza | 38 jean | 28 jenifer | 38 juanita | 58 susan | 78 zena | 98 martie | 88 chris | 78 pat | 18 zola | 58 louise | 98 edna | 18 bertha | 88 sumi | 38 koko | 88 gina | 18 rean | 48 sharon | 78 paula | 68 julie | 68 belinda | 38 karen | 48 carina | 58 diane | 18 esther | 98 trudy | 88 fanny | 8 carmen | 78 lita | 25 pamela | 48 sandy | 38 trisha | 88 uma | 78 velma | 68 (50 rows) -- -- awk '{print $1,$2;}' person.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | -- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data | -- sort +1nr -2 -- --Testcase 13: SELECT p.name, p.age FROM person* p ORDER BY age using >, name; name | age ---------+----- esther | 98 louise | 98 zena | 98 bertha | 88 koko | 88 martie | 88 sarah | 88 trisha | 88 trudy | 88 carmen | 78 chris | 78 sharon | 78 susan | 78 uma | 78 wendy | 78 julie | 68 leah | 68 paula | 68 velma | 68 carina | 58 jane | 58 juanita | 58 zola | 58 sue | 50 karen | 48 pamela | 48 rean | 48 mike | 40 belinda | 38 jenifer | 38 liza | 38 sandy | 38 sumi | 38 teresa | 38 sally | 34 alex | 30 jean | 28 melissa | 28 nan | 28 lita | 25 denise | 24 joe | 20 sandra | 19 diane | 18 edna | 18 gina | 18 joan | 18 pat | 18 fanny | 8 mary | 8 (50 rows) -- -- Test some cases involving whole-row Var referencing a subquery -- --Testcase 49: select foo from (select 1 offset 0) as foo; foo ----- (1) (1 row) --Testcase 50: select foo from (select null offset 0) as foo; foo ----- () (1 row) --Testcase 51: select foo from (select 'xyzzy',1,null offset 0) as foo; foo ------------ (xyzzy,1,) (1 row) -- -- Test VALUES lists -- --Testcase 14: select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) WHERE onek.unique1 = v.i and onek.stringu1 = v.j; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+-------- 147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA 931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA (2 rows) -- a more complex case -- looks like we're coding lisp :-) --Testcase 15: select * from onek, (values ((select i from (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i) order by i asc limit 1))) bar (i) where onek.unique1 = bar.i; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+--- 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2 (1 row) -- try VALUES in a subquery --Testcase 16: select * from onek where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99)) order by unique1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx 99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx (3 rows) -- VALUES is also legal as a standalone query or a set-operation member --Testcase 17: VALUES (1,2), (3,4+4), (7,77.7); column1 | column2 ---------+--------- 1 | 2 3 | 8 7 | 77.7 (3 rows) --Testcase 52: VALUES (1,2), (3,4+4), (7,77.7) UNION ALL SELECT 2+2, 57 UNION ALL TABLE int8_tbl; column1 | column2 ------------------+------------------- 1 | 2 3 | 8 7 | 77.7 4 | 57 123 | 456 123 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (9 rows) -- -- Test ORDER BY options -- --Testcase 53: CREATE FOREIGN TABLE foo (f1 int options (key 'true')) SERVER sqlite_svr; --Testcase 18: INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); --Testcase 19: SELECT * FROM foo ORDER BY f1; f1 ---- 1 3 7 10 42 (7 rows) --Testcase 20: SELECT * FROM foo ORDER BY f1 ASC; -- same thing f1 ---- 1 3 7 10 42 (7 rows) --Testcase 21: SELECT * FROM foo ORDER BY f1 NULLS FIRST; f1 ---- 1 3 7 10 42 (7 rows) --Testcase 22: SELECT * FROM foo ORDER BY f1 DESC; f1 ---- 42 10 7 3 1 (7 rows) --Testcase 23: SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; f1 ---- 42 10 7 3 1 (7 rows) -- Skip, cannot create index on foreign table -- check if indexscans do the right things --CREATE INDEX fooi ON foo (f1); --SET enable_sort = false; --SELECT * FROM foo ORDER BY f1; --SELECT * FROM foo ORDER BY f1 NULLS FIRST; --SELECT * FROM foo ORDER BY f1 DESC; --SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; --DROP INDEX fooi; --CREATE INDEX fooi ON foo (f1 DESC); --SELECT * FROM foo ORDER BY f1; --SELECT * FROM foo ORDER BY f1 NULLS FIRST; --SELECT * FROM foo ORDER BY f1 DESC; --SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; --DROP INDEX fooi; --CREATE INDEX fooi ON foo (f1 DESC NULLS LAST); --SELECT * FROM foo ORDER BY f1; --SELECT * FROM foo ORDER BY f1 NULLS FIRST; --SELECT * FROM foo ORDER BY f1 DESC; --SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; -- -- Test planning of some cases with partial indexes -- -- partial index is usable --Testcase 24: explain (costs off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN --------------------------------------- Foreign Scan on onek2 Filter: (stringu1 = 'ATAAAA'::name) (2 rows) --Testcase 25: select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx (1 row) -- actually run the query with an analyze to use the partial index --Testcase 54: explain (costs off, analyze on, timing off, summary off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------------------------------- Foreign Scan on onek2 (actual rows=1 loops=1) Filter: (stringu1 = 'ATAAAA'::name) (2 rows) --Testcase 27: explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN --------------------------------------- Foreign Scan on onek2 Filter: (stringu1 = 'ATAAAA'::name) (2 rows) --Testcase 28: select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; unique2 --------- 11 (1 row) -- partial index predicate implies clause, so no need for retest --Testcase 29: explain (costs off) select * from onek2 where unique2 = 11 and stringu1 < 'B'; QUERY PLAN ---------------------------------- Foreign Scan on onek2 Filter: (stringu1 < 'B'::name) (2 rows) --Testcase 30: select * from onek2 where unique2 = 11 and stringu1 < 'B'; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx (1 row) --Testcase 31: explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; QUERY PLAN ---------------------------------- Foreign Scan on onek2 Filter: (stringu1 < 'B'::name) (2 rows) --Testcase 32: select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; unique2 --------- 11 (1 row) -- but if it's an update target, must retest anyway --Testcase 33: explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; QUERY PLAN ---------------------------------------- LockRows -> Foreign Scan on onek2 Filter: (stringu1 < 'B'::name) (3 rows) --Testcase 34: select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; unique2 --------- 11 (1 row) -- partial index is not applicable --Testcase 35: explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; QUERY PLAN ---------------------------------- Foreign Scan on onek2 Filter: (stringu1 < 'C'::name) (2 rows) --Testcase 36: select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; unique2 --------- 11 (1 row) -- partial index implies clause, but bitmap scan must recheck predicate anyway SET enable_indexscan TO off; --Testcase 37: explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; QUERY PLAN ---------------------------------- Foreign Scan on onek2 Filter: (stringu1 < 'B'::name) (2 rows) --Testcase 38: select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; unique2 --------- 11 (1 row) RESET enable_indexscan; -- check multi-index cases too --Testcase 39: explain (costs off) select unique1, unique2 from onek2 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; QUERY PLAN ---------------------------------- Foreign Scan on onek2 Filter: (stringu1 < 'B'::name) (2 rows) --Testcase 40: select unique1, unique2 from onek2 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; unique1 | unique2 ---------+--------- 494 | 11 0 | 998 (2 rows) --Testcase 41: explain (costs off) select unique1, unique2 from onek2 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; QUERY PLAN -------------------------------------------------------------------------- Foreign Scan on onek2 Filter: (((unique2 = 11) AND (stringu1 < 'B'::name)) OR (unique1 = 0)) (2 rows) --Testcase 42: select unique1, unique2 from onek2 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; unique1 | unique2 ---------+--------- 494 | 11 0 | 998 (2 rows) -- -- Test some corner cases that have been known to confuse the planner -- -- ORDER BY on a constant doesn't really need any sorting --Testcase 55: SELECT 1 AS x ORDER BY x; x --- 1 (1 row) -- But ORDER BY on a set-valued expression does --Testcase 56: create function sillysrf(int) returns setof int as 'values (1),(10),(2),($1)' language sql immutable; --Testcase 57: select sillysrf(42); sillysrf ---------- 1 10 2 42 (4 rows) --Testcase 58: select sillysrf(-1) order by 1; sillysrf ---------- -1 1 2 10 (4 rows) --Testcase 59: drop function sillysrf(int); -- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict -- (see bug #5084) --Testcase 60: select * from (values (2),(null),(1)) v(k) where k = k order by k; k --- 1 2 (2 rows) --Testcase 61: select * from (values (2),(null),(1)) v(k) where k = k; k --- 2 1 (2 rows) -- Test partitioned tables with no partitions, which should be handled the -- same as the non-inheritance case when expanding its RTE. --Testcase 62: create table list_parted_tbl (a int,b int) partition by list (a); --Testcase 63: create table list_parted_tbl1 partition of list_parted_tbl for values in (1) partition by list(b); --Testcase 64: explain (costs off) select * from list_parted_tbl; QUERY PLAN -------------------------- Result One-Time Filter: false (2 rows) --Testcase 65: drop table list_parted_tbl; --Testcase 66: DROP FOREIGN TABLE onek; --Testcase 67: DROP FOREIGN TABLE onek2; --Testcase 68: DROP FOREIGN TABLE int8_tbl; --Testcase 69: DROP FOREIGN TABLE person; --Testcase 70: DROP FOREIGN TABLE foo; --Testcase 71: DROP SERVER sqlite_svr; --Testcase 72: DROP EXTENSION sqlite_fdw CASCADE;