--SET log_min_messages TO DEBUG1; --SET client_min_messages TO DEBUG1; --Testcase 001: CREATE EXTENSION sqlite_fdw; --Testcase 002: CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlite_fdw_test/common.db'); --Testcase 003: CREATE SERVER sqlite2 FOREIGN DATA WRAPPER sqlite_fdw; --Testcase 02: CREATE FOREIGN TABLE "type_BIT"( "i" int OPTIONS (key 'true'), "b" bit(6)) SERVER sqlite_svr OPTIONS (table 'type_BIT'); --Testcase 03: DROP FOREIGN TABLE IF EXISTS "type_BIT+"; NOTICE: foreign table "type_BIT+" does not exist, skipping --Testcase 04: CREATE FOREIGN TABLE "type_BIT+"( "i" int OPTIONS (key 'true'), "b" bit(6), "t" text, "l" smallint, "bi" bigint OPTIONS (column_name 'b')) SERVER sqlite_svr OPTIONS (table 'type_BIT+'); --Testcase 05: type mismatch INSERT INTO "type_BIT" ("i", "b") VALUES (1, 1); ERROR: column "b" is of type bit but expression is of type integer LINE 1: INSERT INTO "type_BIT" ("i", "b") VALUES (1, 1); ^ HINT: You will need to rewrite or cast the expression. --Testcase 06: type mismatch INSERT INTO "type_BIT" ("i", "b") VALUES (2, 2); ERROR: column "b" is of type bit but expression is of type integer LINE 1: INSERT INTO "type_BIT" ("i", "b") VALUES (2, 2); ^ HINT: You will need to rewrite or cast the expression. --Testcase 07: improper data length INSERT INTO "type_BIT" ("i", "b") VALUES (3, '1'); ERROR: bit string length 1 does not match type bit(6) --Testcase 08: improper data length INSERT INTO "type_BIT" ("i", "b") VALUES (4, '10'); ERROR: bit string length 2 does not match type bit(6) --Testcase 09: improper data length INSERT INTO "type_BIT" ("i", "b") VALUES (5, '101'); ERROR: bit string length 3 does not match type bit(6) --Testcase 10: INSERT INTO "type_BIT" ("i", "b") VALUES (6, '110110'); --Testcase 11: INSERT INTO "type_BIT" ("i", "b") VALUES (7, '111001'); --Testcase 12: INSERT INTO "type_BIT" ("i", "b") VALUES (8, '110000'); --Testcase 13: INSERT INTO "type_BIT" ("i", "b") VALUES (9, '100001'); --Testcase 14: type mismatch with proper data length INSERT INTO "type_BIT" ("i", "b") VALUES (10, 53); ERROR: column "b" is of type bit but expression is of type integer LINE 1: INSERT INTO "type_BIT" ("i", "b") VALUES (10, 53); ^ HINT: You will need to rewrite or cast the expression. --Testcase 15: SELECT * FROM "type_BIT+"; i | b | t | l | bi ---+--------+---------+---+---- 6 | 110110 | integer | 2 | 54 7 | 111001 | integer | 2 | 57 8 | 110000 | integer | 2 | 48 9 | 100001 | integer | 2 | 33 (4 rows) --Testcase 16: SELECT * FROM "type_BIT" WHERE b < '110110'; i | b ---+-------- 8 | 110000 9 | 100001 (2 rows) --Testcase 17: SELECT * FROM "type_BIT" WHERE b > '110110'; i | b ---+-------- 7 | 111001 (1 row) --Testcase 18: SELECT * FROM "type_BIT" WHERE b = '110110'; i | b ---+-------- 6 | 110110 (1 row) --Testcase 20: CREATE FOREIGN TABLE "type_VARBIT"( "i" int OPTIONS (key 'true'), "b" varbit(70)) SERVER sqlite_svr OPTIONS (table 'type_VARBIT'); --Testcase 21: DROP FOREIGN TABLE IF EXISTS "type_VARBIT+"; NOTICE: foreign table "type_VARBIT+" does not exist, skipping --Testcase 22: CREATE FOREIGN TABLE "type_VARBIT+"( "i" int OPTIONS (key 'true'), "b" varbit(70), "t" text, "l" smallint) SERVER sqlite_svr OPTIONS (table 'type_VARBIT+'); --Testcase 23: INSERT INTO "type_VARBIT" ("i", "b") VALUES (1, '1'); --Testcase 24: INSERT INTO "type_VARBIT" ("i", "b") VALUES (2, '10'); --Testcase 25: INSERT INTO "type_VARBIT" ("i", "b") VALUES (3, '11'); --Testcase 26: INSERT INTO "type_VARBIT" ("i", "b") VALUES (4, '100'); --Testcase 27: INSERT INTO "type_VARBIT" ("i", "b") VALUES (5, '101'); --Testcase 28: INSERT INTO "type_VARBIT" ("i", "b") VALUES (6, '110110'); --Testcase 29: INSERT INTO "type_VARBIT" ("i", "b") VALUES (7, '111001'); --Testcase 30: INSERT INTO "type_VARBIT" ("i", "b") VALUES (8, '110000'); --Testcase 31: INSERT INTO "type_VARBIT" ("i", "b") VALUES (9, '100001'); --Testcase 32: INSERT INTO "type_VARBIT" ("i", "b") VALUES (10, '0100100101011001010010101000111110110101101101111011000101010'); --Testcase 33: INSERT INTO "type_VARBIT" ("i", "b") VALUES (11, '01001001010110010100101010001111101101011011011110110001010101'); --Testcase 34: SELECT * FROM "type_VARBIT+"; i | b | t | l ----+---------------------------------------------------------------+---------+---- 1 | 1 | integer | 1 2 | 10 | integer | 1 3 | 11 | integer | 1 4 | 100 | integer | 1 5 | 101 | integer | 1 6 | 110110 | integer | 2 7 | 111001 | integer | 2 8 | 110000 | integer | 2 9 | 100001 | integer | 2 10 | 100100101011001010010101000111110110101101101111011000101010 | integer | 18 11 | 1001001010110010100101010001111101101011011011110110001010101 | integer | 19 (11 rows) --Testcase 35: SELECT * FROM "type_VARBIT+" WHERE b < '110110'; i | b | t | l ---+--------+---------+--- 1 | 1 | integer | 1 2 | 10 | integer | 1 3 | 11 | integer | 1 4 | 100 | integer | 1 5 | 101 | integer | 1 8 | 110000 | integer | 2 9 | 100001 | integer | 2 (7 rows) --Testcase 36: SELECT * FROM "type_VARBIT+" WHERE b > '110110'; i | b | t | l ----+---------------------------------------------------------------+---------+---- 7 | 111001 | integer | 2 10 | 100100101011001010010101000111110110101101101111011000101010 | integer | 18 11 | 1001001010110010100101010001111101101011011011110110001010101 | integer | 19 (3 rows) --Testcase 37: SELECT * FROM "type_VARBIT+" WHERE b = '110110'; i | b | t | l ---+--------+---------+--- 6 | 110110 | integer | 2 (1 row) --Testcase 38: INSERT INTO "type_VARBIT" ("i", "b") VALUES (12, '010010010101100101001010100011111011010110110111101100010101010'); --Testcase 39: INSERT INTO "type_VARBIT" ("i", "b") VALUES (13, '0100100101011001010010101000111110110101101101111011000101010101'); --Testcase 40: very long bit string, expected ERROR, 65 bits INSERT INTO "type_VARBIT" ("i", "b") VALUES (14, '01001001010110010100101010001111101101011011011110110001010101010'); ERROR: SQLite FDW dosens't support very long bit/varbit data HINT: bit length 65, maximum 64 --Testcase 41: SELECT * FROM "type_VARBIT+" WHERE "i" > 10; i | b | t | l ----+-----------------------------------------------------------------+---------+---- 11 | 1001001010110010100101010001111101101011011011110110001010101 | integer | 19 12 | 10010010101100101001010100011111011010110110111101100010101010 | integer | 19 13 | 100100101011001010010101000111110110101101101111011000101010101 | integer | 19 (3 rows) --Testcase 42: SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" | b2."b" "res" FROM "type_BIT" b1 INNER JOIN "type_BIT" b2 ON true; i₁ | b₁ | i₂ | b₂ | res ----+--------+----+--------+-------- 6 | 110110 | 6 | 110110 | 110110 6 | 110110 | 7 | 111001 | 111111 6 | 110110 | 8 | 110000 | 110110 6 | 110110 | 9 | 100001 | 110111 7 | 111001 | 6 | 110110 | 111111 7 | 111001 | 7 | 111001 | 111001 7 | 111001 | 8 | 110000 | 111001 7 | 111001 | 9 | 100001 | 111001 8 | 110000 | 6 | 110110 | 110110 8 | 110000 | 7 | 111001 | 111001 8 | 110000 | 8 | 110000 | 110000 8 | 110000 | 9 | 100001 | 110001 9 | 100001 | 6 | 110110 | 110111 9 | 100001 | 7 | 111001 | 111001 9 | 100001 | 8 | 110000 | 110001 9 | 100001 | 9 | 100001 | 100001 (16 rows) --Testcase 43: SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" & b2."b" "res" FROM "type_BIT" b1 INNER JOIN "type_BIT" b2 ON true; i₁ | b₁ | i₂ | b₂ | res ----+--------+----+--------+-------- 6 | 110110 | 6 | 110110 | 110110 6 | 110110 | 7 | 111001 | 110000 6 | 110110 | 8 | 110000 | 110000 6 | 110110 | 9 | 100001 | 100000 7 | 111001 | 6 | 110110 | 110000 7 | 111001 | 7 | 111001 | 111001 7 | 111001 | 8 | 110000 | 110000 7 | 111001 | 9 | 100001 | 100001 8 | 110000 | 6 | 110110 | 110000 8 | 110000 | 7 | 111001 | 110000 8 | 110000 | 8 | 110000 | 110000 8 | 110000 | 9 | 100001 | 100000 9 | 100001 | 6 | 110110 | 100000 9 | 100001 | 7 | 111001 | 100001 9 | 100001 | 8 | 110000 | 100000 9 | 100001 | 9 | 100001 | 100001 (16 rows) --Testcase 44: SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" # b2."b" "res" FROM "type_BIT" b1 INNER JOIN "type_BIT" b2 ON true; i₁ | b₁ | i₂ | b₂ | res ----+--------+----+--------+-------- 6 | 110110 | 6 | 110110 | 000000 6 | 110110 | 7 | 111001 | 001111 6 | 110110 | 8 | 110000 | 000110 6 | 110110 | 9 | 100001 | 010111 7 | 111001 | 6 | 110110 | 001111 7 | 111001 | 7 | 111001 | 000000 7 | 111001 | 8 | 110000 | 001001 7 | 111001 | 9 | 100001 | 011000 8 | 110000 | 6 | 110110 | 000110 8 | 110000 | 7 | 111001 | 001001 8 | 110000 | 8 | 110000 | 000000 8 | 110000 | 9 | 100001 | 010001 9 | 100001 | 6 | 110110 | 010111 9 | 100001 | 7 | 111001 | 011000 9 | 100001 | 8 | 110000 | 010001 9 | 100001 | 9 | 100001 | 000000 (16 rows) --Testcase 45: SELECT "i", "b", "b" >> 2 "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 001101 7 | 111001 | 001110 8 | 110000 | 001100 9 | 100001 | 001000 (4 rows) --Testcase 46: SELECT "i", "b", "b" << 3 "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 110000 7 | 111001 | 001000 8 | 110000 | 000000 9 | 100001 | 001000 (4 rows) --Testcase 47: SELECT "i", "b", ~ "b" "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 001001 7 | 111001 | 000110 8 | 110000 | 001111 9 | 100001 | 011110 (4 rows) --Testcase 48: EXPLAIN VERBOSE SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" | b2."b" "res" FROM "type_BIT" b1 INNER JOIN "type_BIT" b2 ON true; QUERY PLAN -------------------------------------------------------------------------------------------- Nested Loop (cost=20.00..77960.48 rows=4901796 width=58) Output: b1.i, b1.b, b2.i, b2.b, (b1.b | b2.b) -> Foreign Scan on public."type_BIT" b1 (cost=10.00..2214.00 rows=2214 width=13) Output: b1.i, b1.b SQLite query: SELECT `i`, `b` FROM main."type_BIT" -> Materialize (cost=10.00..2225.07 rows=2214 width=13) Output: b2.i, b2.b -> Foreign Scan on public."type_BIT" b2 (cost=10.00..2214.00 rows=2214 width=13) Output: b2.i, b2.b SQLite query: SELECT `i`, `b` FROM main."type_BIT" (10 rows) --Testcase 49: EXPLAIN VERBOSE SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" & b2."b" "res" FROM "type_BIT" b1 INNER JOIN "type_BIT" b2 ON true; QUERY PLAN -------------------------------------------------------------------------------------------- Nested Loop (cost=20.00..77960.48 rows=4901796 width=58) Output: b1.i, b1.b, b2.i, b2.b, (b1.b & b2.b) -> Foreign Scan on public."type_BIT" b1 (cost=10.00..2214.00 rows=2214 width=13) Output: b1.i, b1.b SQLite query: SELECT `i`, `b` FROM main."type_BIT" -> Materialize (cost=10.00..2225.07 rows=2214 width=13) Output: b2.i, b2.b -> Foreign Scan on public."type_BIT" b2 (cost=10.00..2214.00 rows=2214 width=13) Output: b2.i, b2.b SQLite query: SELECT `i`, `b` FROM main."type_BIT" (10 rows) --Testcase 50: EXPLAIN VERBOSE SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" # b2."b" "res" FROM "type_BIT" b1 INNER JOIN "type_BIT" b2 ON true; QUERY PLAN -------------------------------------------------------------------------------------------- Nested Loop (cost=20.00..77960.48 rows=4901796 width=58) Output: b1.i, b1.b, b2.i, b2.b, (b1.b # b2.b) -> Foreign Scan on public."type_BIT" b1 (cost=10.00..2214.00 rows=2214 width=13) Output: b1.i, b1.b SQLite query: SELECT `i`, `b` FROM main."type_BIT" -> Materialize (cost=10.00..2225.07 rows=2214 width=13) Output: b2.i, b2.b -> Foreign Scan on public."type_BIT" b2 (cost=10.00..2214.00 rows=2214 width=13) Output: b2.i, b2.b SQLite query: SELECT `i`, `b` FROM main."type_BIT" (10 rows) --Testcase 51: EXPLAIN VERBOSE SELECT "i", "b", "b" >> 2 "res" FROM "type_BIT"; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2219.53 rows=2214 width=45) Output: i, b, (b >> 2) SQLite query: SELECT `i`, `b` FROM main."type_BIT" (3 rows) --Testcase 52: EXPLAIN VERBOSE SELECT "i", "b", "b" << 3 "res" FROM "type_BIT"; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2219.53 rows=2214 width=45) Output: i, b, (b << 3) SQLite query: SELECT `i`, `b` FROM main."type_BIT" (3 rows) --Testcase 53: EXPLAIN VERBOSE SELECT "i", "b", ~ "b" "res" FROM "type_BIT"; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2219.53 rows=2214 width=45) Output: i, b, (~ b) SQLite query: SELECT `i`, `b` FROM main."type_BIT" (3 rows) --Testcase 54: SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" | b2."b" "res" FROM "type_VARBIT" b1 INNER JOIN "type_VARBIT" b2 ON true; ERROR: cannot OR bit strings of different sizes --Testcase 55: SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" & b2."b" "res" FROM "type_VARBIT" b1 INNER JOIN "type_VARBIT" b2 ON true; ERROR: cannot AND bit strings of different sizes --Testcase 56: SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" # b2."b" "res" FROM "type_VARBIT" b1 INNER JOIN "type_VARBIT" b2 ON true; ERROR: cannot XOR bit strings of different sizes --Testcase 57: SELECT "i", "b", "b" >> 2 "res" FROM "type_VARBIT"; i | b | res ----+-----------------------------------------------------------------+----------------------------------------------------------------- 1 | 1 | 0 2 | 10 | 00 3 | 11 | 00 4 | 100 | 001 5 | 101 | 001 6 | 110110 | 001101 7 | 111001 | 001110 8 | 110000 | 001100 9 | 100001 | 001000 10 | 100100101011001010010101000111110110101101101111011000101010 | 001001001010110010100101010001111101101011011011110110001010 11 | 1001001010110010100101010001111101101011011011110110001010101 | 0010010010101100101001010100011111011010110110111101100010101 12 | 10010010101100101001010100011111011010110110111101100010101010 | 00100100101011001010010101000111110110101101101111011000101010 13 | 100100101011001010010101000111110110101101101111011000101010101 | 001001001010110010100101010001111101101011011011110110001010101 (13 rows) --Testcase 58: SELECT "i", "b", "b" << 3 "res" FROM "type_VARBIT"; i | b | res ----+-----------------------------------------------------------------+----------------------------------------------------------------- 1 | 1 | 0 2 | 10 | 00 3 | 11 | 00 4 | 100 | 000 5 | 101 | 000 6 | 110110 | 110000 7 | 111001 | 001000 8 | 110000 | 000000 9 | 100001 | 001000 10 | 100100101011001010010101000111110110101101101111011000101010 | 100101011001010010101000111110110101101101111011000101010000 11 | 1001001010110010100101010001111101101011011011110110001010101 | 1001010110010100101010001111101101011011011110110001010101000 12 | 10010010101100101001010100011111011010110110111101100010101010 | 10010101100101001010100011111011010110110111101100010101010000 13 | 100100101011001010010101000111110110101101101111011000101010101 | 100101011001010010101000111110110101101101111011000101010101000 (13 rows) --Testcase 59: SELECT "i", "b", ~ "b" "res" FROM "type_VARBIT"; i | b | res ----+-----------------------------------------------------------------+----------------------------------------------------------------- 1 | 1 | 0 2 | 10 | 01 3 | 11 | 00 4 | 100 | 011 5 | 101 | 010 6 | 110110 | 001001 7 | 111001 | 000110 8 | 110000 | 001111 9 | 100001 | 011110 10 | 100100101011001010010101000111110110101101101111011000101010 | 011011010100110101101010111000001001010010010000100111010101 11 | 1001001010110010100101010001111101101011011011110110001010101 | 0110110101001101011010101110000010010100100100001001110101010 12 | 10010010101100101001010100011111011010110110111101100010101010 | 01101101010011010110101011100000100101001001000010011101010101 13 | 100100101011001010010101000111110110101101101111011000101010101 | 011011010100110101101010111000001001010010010000100111010101010 (13 rows) --Testcase 60: EXPLAIN VERBOSE SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" | b2."b" "res" FROM "type_VARBIT" b1 INNER JOIN "type_VARBIT" b2 ON true; QUERY PLAN ----------------------------------------------------------------------------------------------- Nested Loop (cost=20.00..53330.55 rows=3312400 width=74) Output: b1.i, b1.b, b2.i, b2.b, ((b1.b)::"bit" | (b2.b)::"bit") -> Foreign Scan on public."type_VARBIT" b1 (cost=10.00..1820.00 rows=1820 width=21) Output: b1.i, b1.b SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" -> Materialize (cost=10.00..1829.10 rows=1820 width=21) Output: b2.i, b2.b -> Foreign Scan on public."type_VARBIT" b2 (cost=10.00..1820.00 rows=1820 width=21) Output: b2.i, b2.b SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" (10 rows) --Testcase 61: EXPLAIN VERBOSE SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" & b2."b" "res" FROM "type_VARBIT" b1 INNER JOIN "type_VARBIT" b2 ON true; QUERY PLAN ----------------------------------------------------------------------------------------------- Nested Loop (cost=20.00..53330.55 rows=3312400 width=74) Output: b1.i, b1.b, b2.i, b2.b, ((b1.b)::"bit" & (b2.b)::"bit") -> Foreign Scan on public."type_VARBIT" b1 (cost=10.00..1820.00 rows=1820 width=21) Output: b1.i, b1.b SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" -> Materialize (cost=10.00..1829.10 rows=1820 width=21) Output: b2.i, b2.b -> Foreign Scan on public."type_VARBIT" b2 (cost=10.00..1820.00 rows=1820 width=21) Output: b2.i, b2.b SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" (10 rows) --Testcase 62: EXPLAIN VERBOSE SELECT b1."i" "i₁", b1."b" "b₁", b2."i" "i₂", b2."b" "b₂", b1."b" # b2."b" "res" FROM "type_VARBIT" b1 INNER JOIN "type_VARBIT" b2 ON true; QUERY PLAN ----------------------------------------------------------------------------------------------- Nested Loop (cost=20.00..53330.55 rows=3312400 width=74) Output: b1.i, b1.b, b2.i, b2.b, ((b1.b)::"bit" # (b2.b)::"bit") -> Foreign Scan on public."type_VARBIT" b1 (cost=10.00..1820.00 rows=1820 width=21) Output: b1.i, b1.b SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" -> Materialize (cost=10.00..1829.10 rows=1820 width=21) Output: b2.i, b2.b -> Foreign Scan on public."type_VARBIT" b2 (cost=10.00..1820.00 rows=1820 width=21) Output: b2.i, b2.b SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" (10 rows) --Testcase 63: EXPLAIN VERBOSE SELECT "i", "b", "b" >> 2 "res" FROM "type_VARBIT"; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public."type_VARBIT" (cost=10.00..1824.55 rows=1820 width=53) Output: i, b, ((b)::"bit" >> 2) SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" (3 rows) --Testcase 64: EXPLAIN VERBOSE SELECT "i", "b", "b" << 3 "res" FROM "type_VARBIT"; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public."type_VARBIT" (cost=10.00..1824.55 rows=1820 width=53) Output: i, b, ((b)::"bit" << 3) SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" (3 rows) --Testcase 65: EXPLAIN VERBOSE SELECT "i", "b", ~ "b" "res" FROM "type_VARBIT"; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public."type_VARBIT" (cost=10.00..1824.55 rows=1820 width=53) Output: i, b, (~ (b)::"bit") SQLite query: SELECT `i`, `b` FROM main."type_VARBIT" (3 rows) --Testcase 66: SELECT "i", "b", "b" & B'101011' "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 100010 7 | 111001 | 101001 8 | 110000 | 100000 9 | 100001 | 100001 (4 rows) --Testcase 67: SELECT "i", "b", "b" | B'101011' "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 111111 7 | 111001 | 111011 8 | 110000 | 111011 9 | 100001 | 101011 (4 rows) --Testcase 68: SELECT "i", "b", "b" # B'101011' "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 011101 7 | 111001 | 010010 8 | 110000 | 011011 9 | 100001 | 001010 (4 rows) --Testcase 69: SELECT "i", "b" FROM "type_BIT" WHERE ("b" & B'101011') IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 70: SELECT "i", "b" FROM "type_BIT" WHERE ("b" | B'101011') IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 71: SELECT "i", "b" FROM "type_BIT" WHERE ("b" # B'101011') IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 72: SELECT "i", "b" FROM "type_BIT" WHERE ("b" >> 1) IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 73: SELECT "i", "b" FROM "type_BIT" WHERE ("b" << 2) IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 74: SELECT "i", "b" FROM "type_BIT" WHERE (~ "b") IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 75: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" & B'101011') IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` & 43) IS NOT NULL)) (3 rows) --Testcase 76: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" | B'101011') IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` | 43) IS NOT NULL)) (3 rows) --Testcase 77: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" # B'101011') IS NOT NULL; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b Filter: (("type_BIT".b # '101011'::"bit") IS NOT NULL) SQLite query: SELECT `i`, `b` FROM main."type_BIT" (4 rows) --Testcase 78: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" >> 1) IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` >> 1) IS NOT NULL)) (3 rows) --Testcase 79: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" << 2) IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` << 2) IS NOT NULL)) (3 rows) --Testcase 80: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE (~ "b") IS NOT NULL; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((~ `b`) IS NOT NULL)) (3 rows) --Testcase 81: SELECT "i", "b", "b" & B'101011' "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 100010 7 | 111001 | 101001 8 | 110000 | 100000 9 | 100001 | 100001 (4 rows) --Testcase 82: SELECT "i", "b", "b" | B'101011' "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 111111 7 | 111001 | 111011 8 | 110000 | 111011 9 | 100001 | 101011 (4 rows) --Testcase 83: SELECT "i", "b", "b" # B'101011' "res" FROM "type_BIT"; i | b | res ---+--------+-------- 6 | 110110 | 011101 7 | 111001 | 010010 8 | 110000 | 011011 9 | 100001 | 001010 (4 rows) --Testcase 84: SELECT "i", "b" FROM "type_BIT" WHERE ("b" & B'101011') IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 85: SELECT "i", "b" FROM "type_BIT" WHERE ("b" | B'101011') IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 86: SELECT "i", "b" FROM "type_BIT" WHERE ("b" # B'101011') IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 87: SELECT "i", "b" FROM "type_BIT" WHERE ("b" >> 1) IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 88: SELECT "i", "b" FROM "type_BIT" WHERE ("b" << 2) IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 89: SELECT "i", "b" FROM "type_BIT" WHERE (~ "b") IS NOT NULL; i | b ---+-------- 6 | 110110 7 | 111001 8 | 110000 9 | 100001 (4 rows) --Testcase 90: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" & B'101011') IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` & 43) IS NOT NULL)) (3 rows) --Testcase 91: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" | B'101011') IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` | 43) IS NOT NULL)) (3 rows) --Testcase 92: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" # B'101011') IS NOT NULL; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b Filter: (("type_BIT".b # '101011'::"bit") IS NOT NULL) SQLite query: SELECT `i`, `b` FROM main."type_BIT" (4 rows) --Testcase 93: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" >> 1) IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` >> 1) IS NOT NULL)) (3 rows) --Testcase 94: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE ("b" << 2) IS NOT NULL; QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((`b` << 2) IS NOT NULL)) (3 rows) --Testcase 95: EXPLAIN VERBOSE SELECT "i", "b" FROM "type_BIT" WHERE (~ "b") IS NOT NULL; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan on public."type_BIT" (cost=10.00..2203.00 rows=2203 width=13) Output: i, b SQLite query: SELECT `i`, `b` FROM main."type_BIT" WHERE (((~ `b`) IS NOT NULL)) (3 rows) --Testcase 005: DROP EXTENSION sqlite_fdw CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to server sqlite_svr drop cascades to foreign table "type_BIT" drop cascades to foreign table "type_BIT+" drop cascades to foreign table "type_VARBIT" drop cascades to foreign table "type_VARBIT+" drop cascades to server sqlite2