--SET log_min_messages TO DEBUG1; --SET client_min_messages TO DEBUG1; --Testcase 129: CREATE EXTENSION sqlite_fdw; --Testcase 130: CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test.db'); --Testcase 131: CREATE FOREIGN TABLE department(department_id int OPTIONS (key 'true'), department_name text) SERVER sqlite_svr; --Testcase 132: CREATE FOREIGN TABLE employee(emp_id int OPTIONS (key 'true'), emp_name text, emp_dept_id int) SERVER sqlite_svr; --Testcase 133: CREATE FOREIGN TABLE empdata(emp_id int OPTIONS (key 'true'), emp_dat bytea) SERVER sqlite_svr; --Testcase 134: CREATE FOREIGN TABLE numbers(a int OPTIONS (key 'true'), b varchar(255)) SERVER sqlite_svr; --Testcase 135: CREATE FOREIGN TABLE multiprimary(a int, b int OPTIONS (key 'true'), c int OPTIONS(key 'true')) SERVER sqlite_svr; --Testcase 136: CREATE FOREIGN TABLE noprimary(a int, b text) SERVER sqlite_svr; --Testcase 1: SELECT * FROM department LIMIT 10; department_id | department_name ---------------+----------------- (0 rows) --Testcase 2: SELECT * FROM employee LIMIT 10; emp_id | emp_name | emp_dept_id --------+----------+------------- (0 rows) --Testcase 3: SELECT * FROM empdata LIMIT 10; emp_id | emp_dat --------+--------- (0 rows) --Testcase 4: INSERT INTO department VALUES(generate_series(1,100), 'dept - ' || generate_series(1,100)); --Testcase 5: INSERT INTO employee VALUES(generate_series(1,100), 'emp - ' || generate_series(1,100), generate_series(1,100)); --Testcase 6: INSERT INTO empdata VALUES(1, decode ('01234567', 'hex')); --Testcase 7: INSERT INTO numbers VALUES(1, 'One'); --Testcase 8: INSERT INTO numbers VALUES(2, 'Two'); --Testcase 9: INSERT INTO numbers VALUES(3, 'Three'); --Testcase 10: INSERT INTO numbers VALUES(4, 'Four'); --Testcase 11: INSERT INTO numbers VALUES(5, 'Five'); --Testcase 12: INSERT INTO numbers VALUES(6, 'Six'); --Testcase 13: INSERT INTO numbers VALUES(7, 'Seven'); --Testcase 14: INSERT INTO numbers VALUES(8, 'Eight'); --Testcase 15: INSERT INTO numbers VALUES(9, 'Nine'); --Testcase 16: SELECT count(*) FROM department; count ------- 100 (1 row) --Testcase 17: SELECT count(*) FROM employee; count ------- 100 (1 row) --Testcase 18: SELECT count(*) FROM empdata; count ------- 1 (1 row) --Testcase 19: EXPLAIN (COSTS FALSE) SELECT * FROM department d, employee e WHERE d.department_id = e.emp_dept_id LIMIT 10; QUERY PLAN -------------- Foreign Scan (1 row) --Testcase 20: EXPLAIN (COSTS FALSE) SELECT * FROM department d, employee e WHERE d.department_id IN (SELECT department_id FROM department) LIMIT 10; QUERY PLAN ------------------------------------------------------------------------- Limit -> Nested Loop -> Nested Loop Semi Join Join Filter: (d.department_id = department.department_id) -> Foreign Scan on department d -> Materialize -> Foreign Scan on department -> Materialize -> Foreign Scan on employee e (9 rows) --Testcase 21: SELECT * FROM department d, employee e WHERE d.department_id = e.emp_dept_id LIMIT 10; department_id | department_name | emp_id | emp_name | emp_dept_id ---------------+-----------------+--------+----------+------------- 1 | dept - 1 | 1 | emp - 1 | 1 2 | dept - 2 | 2 | emp - 2 | 2 3 | dept - 3 | 3 | emp - 3 | 3 4 | dept - 4 | 4 | emp - 4 | 4 5 | dept - 5 | 5 | emp - 5 | 5 6 | dept - 6 | 6 | emp - 6 | 6 7 | dept - 7 | 7 | emp - 7 | 7 8 | dept - 8 | 8 | emp - 8 | 8 9 | dept - 9 | 9 | emp - 9 | 9 10 | dept - 10 | 10 | emp - 10 | 10 (10 rows) --Testcase 22: SELECT * FROM department d, employee e WHERE d.department_id IN (SELECT department_id FROM department) ORDER BY d.department_id LIMIT 10; department_id | department_name | emp_id | emp_name | emp_dept_id ---------------+-----------------+--------+----------+------------- 1 | dept - 1 | 1 | emp - 1 | 1 1 | dept - 1 | 2 | emp - 2 | 2 1 | dept - 1 | 3 | emp - 3 | 3 1 | dept - 1 | 4 | emp - 4 | 4 1 | dept - 1 | 5 | emp - 5 | 5 1 | dept - 1 | 6 | emp - 6 | 6 1 | dept - 1 | 7 | emp - 7 | 7 1 | dept - 1 | 8 | emp - 8 | 8 1 | dept - 1 | 9 | emp - 9 | 9 1 | dept - 1 | 10 | emp - 10 | 10 (10 rows) --Testcase 23: SELECT * FROM empdata; emp_id | emp_dat --------+------------ 1 | \x01234567 (1 row) --Testcase 24: DELETE FROM employee WHERE emp_id = 10; --Testcase 25: SELECT COUNT(*) FROM department LIMIT 10; count ------- 100 (1 row) --Testcase 26: SELECT COUNT(*) FROM employee WHERE emp_id = 10; count ------- 0 (1 row) --Testcase 27: UPDATE employee SET emp_name = 'UPDATEd emp' WHERE emp_id = 20; --Testcase 28: SELECT emp_id, emp_name FROM employee WHERE emp_name like 'UPDATEd emp'; emp_id | emp_name --------+------------- 20 | UPDATEd emp (1 row) --Testcase 29: UPDATE empdata SET emp_dat = decode ('0123', 'hex'); --Testcase 30: SELECT * FROM empdata; emp_id | emp_dat --------+--------- 1 | \x0123 (1 row) --Testcase 31: SELECT * FROM employee LIMIT 10; emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 7 | emp - 7 | 7 8 | emp - 8 | 8 9 | emp - 9 | 9 11 | emp - 11 | 11 (10 rows) --Testcase 32: SELECT * FROM employee WHERE emp_id IN (1); emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 (1 row) --Testcase 33: SELECT * FROM employee WHERE emp_id IN (1,3,4,5); emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 (4 rows) --Testcase 34: SELECT * FROM employee WHERE emp_id IN (10000,1000); emp_id | emp_name | emp_dept_id --------+----------+------------- (0 rows) --Testcase 35: SELECT * FROM employee WHERE emp_id NOT IN (1) LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 (5 rows) --Testcase 36: SELECT * FROM employee WHERE emp_id NOT IN (1,3,4,5) LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 2 | emp - 2 | 2 6 | emp - 6 | 6 7 | emp - 7 | 7 8 | emp - 8 | 8 9 | emp - 9 | 9 (5 rows) --Testcase 37: SELECT * FROM employee WHERE emp_id NOT IN (10000,1000) LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 (5 rows) --Testcase 38: SELECT * FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM employee WHERE emp_id IN (1,10)); emp_id | emp_name | emp_dept_id --------+-------------+------------- 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 7 | emp - 7 | 7 8 | emp - 8 | 8 9 | emp - 9 | 9 11 | emp - 11 | 11 12 | emp - 12 | 12 13 | emp - 13 | 13 14 | emp - 14 | 14 15 | emp - 15 | 15 16 | emp - 16 | 16 17 | emp - 17 | 17 18 | emp - 18 | 18 19 | emp - 19 | 19 20 | UPDATEd emp | 20 21 | emp - 21 | 21 22 | emp - 22 | 22 23 | emp - 23 | 23 24 | emp - 24 | 24 25 | emp - 25 | 25 26 | emp - 26 | 26 27 | emp - 27 | 27 28 | emp - 28 | 28 29 | emp - 29 | 29 30 | emp - 30 | 30 31 | emp - 31 | 31 32 | emp - 32 | 32 33 | emp - 33 | 33 34 | emp - 34 | 34 35 | emp - 35 | 35 36 | emp - 36 | 36 37 | emp - 37 | 37 38 | emp - 38 | 38 39 | emp - 39 | 39 40 | emp - 40 | 40 41 | emp - 41 | 41 42 | emp - 42 | 42 43 | emp - 43 | 43 44 | emp - 44 | 44 45 | emp - 45 | 45 46 | emp - 46 | 46 47 | emp - 47 | 47 48 | emp - 48 | 48 49 | emp - 49 | 49 50 | emp - 50 | 50 51 | emp - 51 | 51 52 | emp - 52 | 52 53 | emp - 53 | 53 54 | emp - 54 | 54 55 | emp - 55 | 55 56 | emp - 56 | 56 57 | emp - 57 | 57 58 | emp - 58 | 58 59 | emp - 59 | 59 60 | emp - 60 | 60 61 | emp - 61 | 61 62 | emp - 62 | 62 63 | emp - 63 | 63 64 | emp - 64 | 64 65 | emp - 65 | 65 66 | emp - 66 | 66 67 | emp - 67 | 67 68 | emp - 68 | 68 69 | emp - 69 | 69 70 | emp - 70 | 70 71 | emp - 71 | 71 72 | emp - 72 | 72 73 | emp - 73 | 73 74 | emp - 74 | 74 75 | emp - 75 | 75 76 | emp - 76 | 76 77 | emp - 77 | 77 78 | emp - 78 | 78 79 | emp - 79 | 79 80 | emp - 80 | 80 81 | emp - 81 | 81 82 | emp - 82 | 82 83 | emp - 83 | 83 84 | emp - 84 | 84 85 | emp - 85 | 85 86 | emp - 86 | 86 87 | emp - 87 | 87 88 | emp - 88 | 88 89 | emp - 89 | 89 90 | emp - 90 | 90 91 | emp - 91 | 91 92 | emp - 92 | 92 93 | emp - 93 | 93 94 | emp - 94 | 94 95 | emp - 95 | 95 96 | emp - 96 | 96 97 | emp - 97 | 97 98 | emp - 98 | 98 99 | emp - 99 | 99 100 | emp - 100 | 100 (98 rows) --Testcase 39: SELECT * FROM employee WHERE emp_name NOT IN ('emp - 1', 'emp - 2') LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 7 | emp - 7 | 7 (5 rows) --Testcase 40: SELECT * FROM employee WHERE emp_name NOT IN ('emp - 10') LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 (5 rows) --Testcase 41: SELECT * FROM numbers WHERE (CASE WHEN a % 2 = 0 THEN 1 WHEN a % 5 = 0 THEN 1 ELSE 0 END) = 1; a | b ---+------- 2 | Two 4 | Four 5 | Five 6 | Six 8 | Eight (5 rows) --Testcase 42: SELECT * FROM numbers WHERE (CASE b WHEN 'Two' THEN 1 WHEN 'Six' THEN 1 ELSE 0 END) = 1; a | b ---+----- 2 | Two 6 | Six (2 rows) --Testcase 152: EXPLAIN VERBOSE SELECT * FROM numbers WHERE (round(abs(a)) = 1); QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..1.00 rows=1 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((round(abs(`a`)) = 1)) (3 rows) --Testcase 153: SELECT * FROM numbers WHERE (round(abs(a)) = 1); a | b ---+----- 1 | One (1 row) --Testcase 137: create or replace function test_param_WHERE() returns void as $$ DECLARE n varchar; BEGIN FOR x IN 1..9 LOOP --Testcase 138: SELECT b INTO n from numbers WHERE a=x; raise notice 'Found number %', n; end loop; return; END $$ LANGUAGE plpgsql; --Testcase 43: SELECT test_param_WHERE(); NOTICE: Found number One NOTICE: Found number Two NOTICE: Found number Three NOTICE: Found number Four NOTICE: Found number Five NOTICE: Found number Six NOTICE: Found number Seven NOTICE: Found number Eight NOTICE: Found number Nine test_param_where ------------------ (1 row) --Testcase 44: SELECT b from numbers WHERE a=1; b ----- One (1 row) --Testcase 45: EXPLAIN(COSTS OFF) SELECT b from numbers WHERE a=1; QUERY PLAN ------------------------- Foreign Scan on numbers (1 row) --Testcase 46: SELECT a FROM numbers WHERE b = (SELECT NULL::text); a --- (0 rows) --Testcase 47: PREPARE stmt1 (int, int) AS SELECT * FROM numbers WHERE a=$1 or a=$2; --Testcase 48: EXECUTE stmt1(1,2); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 49: EXECUTE stmt1(2,2); a | b ---+----- 2 | Two (1 row) --Testcase 50: EXECUTE stmt1(3,2); a | b ---+------- 2 | Two 3 | Three (2 rows) --Testcase 51: EXECUTE stmt1(4,2); a | b ---+------ 2 | Two 4 | Four (2 rows) -- generic plan --Testcase 52: EXECUTE stmt1(5,2); a | b ---+------ 2 | Two 5 | Five (2 rows) --Testcase 53: EXECUTE stmt1(6,2); a | b ---+----- 2 | Two 6 | Six (2 rows) --Testcase 54: EXECUTE stmt1(7,2); a | b ---+------- 2 | Two 7 | Seven (2 rows) --Testcase 55: DELETE FROM employee; --Testcase 56: DELETE FROM department; --Testcase 57: DELETE FROM empdata; --Testcase 58: DELETE FROM numbers; BEGIN; --Testcase 59: INSERT INTO numbers VALUES(1, 'One'); --Testcase 60: INSERT INTO numbers VALUES(2, 'Two'); COMMIT; --Testcase 61: SELECT * from numbers; a | b ---+----- 1 | One 2 | Two (2 rows) BEGIN; --Testcase 62: INSERT INTO numbers VALUES(3, 'Three'); ROLLBACK; --Testcase 63: SELECT * from numbers; a | b ---+----- 1 | One 2 | Two (2 rows) BEGIN; --Testcase 64: INSERT INTO numbers VALUES(4, 'Four'); SAVEPOINT my_savepoint; --Testcase 65: INSERT INTO numbers VALUES(5, 'Five'); ROLLBACK TO SAVEPOINT my_savepoint; --Testcase 66: INSERT INTO numbers VALUES(6, 'Six'); COMMIT; --Testcase 67: SELECT * from numbers; a | b ---+------ 1 | One 2 | Two 4 | Four 6 | Six (4 rows) -- duplicate key --Testcase 68: INSERT INTO numbers VALUES(1, 'One'); ERROR: failed to execute remote SQL: rc=19 UNIQUE constraint failed: numbers.b sql=INSERT INTO main."numbers"(`a`, `b`) VALUES (?, ?) --Testcase 69: DELETE from numbers; BEGIN; --Testcase 70: INSERT INTO numbers VALUES(1, 'One'); --Testcase 71: INSERT INTO numbers VALUES(2, 'Two'); COMMIT; -- violate unique constraint --Testcase 72: UPDATE numbers SET b='Two' WHERE a = 1; ERROR: failed to execute remote SQL: rc=19 UNIQUE constraint failed: numbers.b sql=UPDATE main."numbers" SET `b` = 'Two' WHERE ((`a` = 1)) --Testcase 73: SELECT * from numbers; a | b ---+----- 1 | One 2 | Two (2 rows) -- push down --Testcase 74: explain (verbose, costs off) SELECT * from numbers WHERE a = any(ARRAY[2,3,4,5]::int[]); QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.numbers Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` IN (2, 3, 4, 5)) (3 rows) -- (1,2,3) is pushed down --Testcase 75: explain (verbose, costs off) SELECT * from numbers WHERE a in (1,2,3) AND (1,2) < (a,5); QUERY PLAN ------------------------------------------------------------------------------ Foreign Scan on public.numbers Output: a, b Filter: (ROW(1, 2) < ROW(numbers.a, 5)) SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` IN (1, 2, 3)) (4 rows) --Testcase 76: explain (verbose, costs off) SELECT * from numbers WHERE a in (a+2*a,5); QUERY PLAN ------------------------------------------------------------------------------------------------------ Foreign Scan on public.numbers Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (((`a` = (`a` + (2 * `a`))) OR (`a` = 5))) (3 rows) --Testcase 77: explain (verbose, costs off) SELECT * from numbers WHERE a = any(ARRAY[1,2,a]::int[]); QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.numbers Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` IN (1, 2, `a`)) (3 rows) --Testcase 78: SELECT * from numbers WHERE a = any(ARRAY[2,3,4,5]::int[]); a | b ---+----- 2 | Two (1 row) --Testcase 79: SELECT * from numbers WHERE a = any(ARRAY[1,2,a]::int[]); a | b ---+----- 1 | One 2 | Two (2 rows) -- ANY with ARRAY expression --Testcase 154: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a = ANY(ARRAY[1, a + 1]); QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..2.00 rows=2 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` IN (1, (`a` + 1))) (3 rows) --Testcase 155: SELECT * FROM numbers WHERE a = ANY(ARRAY[1, a + 1]); a | b ---+----- 1 | One (1 row) --Testcase 156: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <> ANY(ARRAY[1, a + 1]); QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..150.00 rows=150 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` <> 1) OR (`a` <> (`a` + 1))) (3 rows) --Testcase 157: SELECT * FROM numbers WHERE a <> ANY(ARRAY[1, a + 1]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 158: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a >= ANY(ARRAY[1, a + 1]); QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` >= 1) OR (`a` >= (`a` + 1))) (3 rows) --Testcase 159: SELECT * FROM numbers WHERE a >= ANY(ARRAY[1, a + 1]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 160: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <= ANY(ARRAY[1, a + 1]); QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` <= 1) OR (`a` <= (`a` + 1))) (3 rows) --Testcase 161: SELECT * FROM numbers WHERE a <= ANY(ARRAY[1, a + 1]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 162: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a > ANY(ARRAY[1, a + 1]); QUERY PLAN -------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` > 1) OR (`a` > (`a` + 1))) (3 rows) --Testcase 163: SELECT * FROM numbers WHERE a > ANY(ARRAY[1, a + 1]); a | b ---+----- 2 | Two (1 row) --Testcase 164: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a < ANY(ARRAY[1, a + 1]); QUERY PLAN -------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` < 1) OR (`a` < (`a` + 1))) (3 rows) --Testcase 165: SELECT * FROM numbers WHERE a < ANY(ARRAY[1, a + 1]); a | b ---+----- 1 | One 2 | Two (2 rows) -- ANY with ARRAY const --Testcase 166: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a = ANY(ARRAY[1, 2]); QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..2.00 rows=2 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` IN (1, 2)) (3 rows) --Testcase 167: SELECT * FROM numbers WHERE a = ANY(ARRAY[1, 2]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 168: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <> ANY(ARRAY[1, 2]); QUERY PLAN ---------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..150.00 rows=150 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` <> 1 OR `a` <> 2) (3 rows) --Testcase 169: SELECT * FROM numbers WHERE a <> ANY(ARRAY[1, 2]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 170: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a >= ANY(ARRAY[1, 2]); QUERY PLAN ---------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` >= 1 OR `a` >= 2) (3 rows) --Testcase 171: SELECT * FROM numbers WHERE a >= ANY(ARRAY[1, 2]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 172: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <= ANY(ARRAY[1, 2]); QUERY PLAN ---------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` <= 1 OR `a` <= 2) (3 rows) --Testcase 173: SELECT * FROM numbers WHERE a <= ANY(ARRAY[1, 2]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 174: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a > ANY(ARRAY[1, 2]); QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` > 1 OR `a` > 2) (3 rows) --Testcase 175: SELECT * FROM numbers WHERE a > ANY(ARRAY[1, 2]); a | b ---+----- 2 | Two (1 row) --Testcase 176: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a < ANY(ARRAY[1, 2]); QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` < 1 OR `a` < 2) (3 rows) --Testcase 177: SELECT * FROM numbers WHERE a < ANY(ARRAY[1, 2]); a | b ---+----- 1 | One (1 row) --Testcase 210: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a = ANY('{1, 2, 3}'); QUERY PLAN ------------------------------------------------------------------------------ Foreign Scan on public.numbers (cost=10.00..3.00 rows=3 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` IN (1, 2, 3)) (3 rows) --Testcase 211: SELECT * FROM numbers WHERE a = ANY('{1, 2, 3}'); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 212: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <> ANY('{1, 2, 3}'); QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..150.00 rows=150 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` <> 1 OR `a` <> 2 OR `a` <> 3) (3 rows) --Testcase 213: SELECT * FROM numbers WHERE a <> ANY('{1, 2, 3}'); a | b ---+----- 1 | One 2 | Two (2 rows) -- ALL with ARRAY expression --Testcase 178: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a = ALL(ARRAY[1, a * 1]); QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..1.00 rows=1 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` = 1) AND (`a` = (`a` * 1))) (3 rows) --Testcase 179: SELECT * FROM numbers WHERE a = ALL(ARRAY[1, a * 1]); a | b ---+----- 1 | One (1 row) --Testcase 180: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <> ALL(ARRAY[1, a + 1]); QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..148.00 rows=148 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` NOT IN (1, (`a` + 1))) (3 rows) --Testcase 181: SELECT * FROM numbers WHERE a <> ALL(ARRAY[1, a + 1]); a | b ---+----- 2 | Two (1 row) --Testcase 182: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a >= ALL(ARRAY[1, a / 1]); QUERY PLAN ----------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` >= 1) AND (`a` >= (`a` / 1))) (3 rows) --Testcase 183: SELECT * FROM numbers WHERE a >= ALL(ARRAY[1, a / 1]); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 184: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <= ALL(ARRAY[1, a + 1]); QUERY PLAN ----------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` <= 1) AND (`a` <= (`a` + 1))) (3 rows) --Testcase 185: SELECT * FROM numbers WHERE a <= ALL(ARRAY[1, a + 1]); a | b ---+----- 1 | One (1 row) --Testcase 186: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a > ALL(ARRAY[1, a - 1]); QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` > 1) AND (`a` > (`a` - 1))) (3 rows) --Testcase 187: SELECT * FROM numbers WHERE a > ALL(ARRAY[1, a - 1]); a | b ---+----- 2 | Two (1 row) --Testcase 188: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a < ALL(ARRAY[2, a + 1]); QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((`a` < 2) AND (`a` < (`a` + 1))) (3 rows) --Testcase 189: SELECT * FROM numbers WHERE a < ALL(ARRAY[2, a + 1]); a | b ---+----- 1 | One (1 row) -- ALL with ARRAY const --Testcase 190: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a = ALL(ARRAY[1, 1]); QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..1.00 rows=1 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` = 1 AND `a` = 1) (3 rows) --Testcase 191: SELECT * FROM numbers WHERE a = ALL(ARRAY[1, 1]); a | b ---+----- 1 | One (1 row) --Testcase 192: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <> ALL(ARRAY[1, 3]); QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..148.00 rows=148 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` NOT IN (1, 3)) (3 rows) --Testcase 193: SELECT * FROM numbers WHERE a <> ALL(ARRAY[1, 3]); a | b ---+----- 2 | Two (1 row) --Testcase 194: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a >= ALL(ARRAY[1, 2]); QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` >= 1 AND `a` >= 2) (3 rows) --Testcase 195: SELECT * FROM numbers WHERE a >= ALL(ARRAY[1, 2]); a | b ---+----- 2 | Two (1 row) --Testcase 196: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a <= ALL(ARRAY[1, 2]); QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` <= 1 AND `a` <= 2) (3 rows) --Testcase 197: SELECT * FROM numbers WHERE a <= ALL(ARRAY[1, 2]); a | b ---+----- 1 | One (1 row) --Testcase 198: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a > ALL(ARRAY[0, 1]); QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` > 0 AND `a` > 1) (3 rows) --Testcase 199: SELECT * FROM numbers WHERE a > ALL(ARRAY[0, 1]); a | b ---+----- 2 | Two (1 row) --Testcase 200: EXPLAIN VERBOSE SELECT * FROM numbers WHERE a < ALL(ARRAY[2, 3]); QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`a` < 2 AND `a` < 3) (3 rows) --Testcase 201: SELECT * FROM numbers WHERE a < ALL(ARRAY[2, 3]); a | b ---+----- 1 | One (1 row) -- ANY/ALL with TEXT ARRAY const --Testcase 202: EXPLAIN VERBOSE SELECT * FROM numbers WHERE b = ANY(ARRAY['One', 'Two']); QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..2.00 rows=2 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`b` IN ('One', 'Two')) (3 rows) --Testcase 203: SELECT * FROM numbers WHERE b = ANY(ARRAY['One', 'Two']); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 204: EXPLAIN VERBOSE SELECT * FROM numbers WHERE b <> ALL(ARRAY['One', 'Four']); QUERY PLAN ---------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..148.00 rows=148 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`b` NOT IN ('One', 'Four')) (3 rows) --Testcase 205: SELECT * FROM numbers WHERE b <> ALL(ARRAY['One', 'Four']); a | b ---+----- 2 | Two (1 row) --Testcase 206: EXPLAIN VERBOSE SELECT * FROM numbers WHERE b > ANY(ARRAY['One', 'Two']); QUERY PLAN ---------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..83.00 rows=83 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`b` > 'One' OR `b` > 'Two') (3 rows) --Testcase 207: SELECT * FROM numbers WHERE b > ANY(ARRAY['One', 'Two']); a | b ---+----- 2 | Two (1 row) --Testcase 208: EXPLAIN VERBOSE SELECT * FROM numbers WHERE b > ALL(ARRAY['Four', 'Five']); QUERY PLAN ------------------------------------------------------------------------------------------- Foreign Scan on public.numbers (cost=10.00..17.00 rows=17 width=520) Output: a, b SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE (`b` > 'Four' AND `b` > 'Five') (3 rows) --Testcase 209: SELECT * FROM numbers WHERE b > ALL(ARRAY['Four', 'Five']); a | b ---+----- 1 | One 2 | Two (2 rows) --Testcase 80: INSERT INTO multiprimary VALUES(1,2,3); --Testcase 81: INSERT INTO multiprimary VALUES(1,2,4); --Testcase 82: UPDATE multiprimary SET b = 10 WHERE c = 3; --Testcase 83: SELECT * from multiprimary; a | b | c ---+----+--- 1 | 10 | 3 1 | 2 | 4 (2 rows) --Testcase 84: UPDATE multiprimary SET a = 10 WHERE a = 1; --Testcase 85: SELECT * from multiprimary; a | b | c ----+----+--- 10 | 10 | 3 10 | 2 | 4 (2 rows) --Testcase 86: UPDATE multiprimary SET a = 100, b=200, c=300 WHERE a=10 AND b=10; --Testcase 87: SELECT * from multiprimary; a | b | c -----+-----+----- 100 | 200 | 300 10 | 2 | 4 (2 rows) --Testcase 88: UPDATE multiprimary SET a = 1234; --Testcase 89: SELECT * from multiprimary; a | b | c ------+-----+----- 1234 | 200 | 300 1234 | 2 | 4 (2 rows) --Testcase 90: UPDATE multiprimary SET a = a+1, b=b+1 WHERE b=200 AND c=300; --Testcase 91: SELECT * from multiprimary; a | b | c ------+-----+----- 1235 | 201 | 300 1234 | 2 | 4 (2 rows) --Testcase 92: DELETE from multiprimary WHERE a = 1235; --Testcase 93: SELECT * from multiprimary; a | b | c ------+---+--- 1234 | 2 | 4 (1 row) --Testcase 94: DELETE from multiprimary WHERE b = 2; --Testcase 95: SELECT * from multiprimary; a | b | c ---+---+--- (0 rows) --Testcase 96: INSERT INTO multiprimary VALUES(1,2,3); --Testcase 97: INSERT INTO multiprimary VALUES(1,2,4); --Testcase 98: INSERT INTO multiprimary VALUES(1,10,20); --Testcase 99: INSERT INTO multiprimary VALUES(2,20,40); --Testcase 100: SELECT count(distinct a) from multiprimary; count ------- 2 (1 row) --Testcase 101: SELECT sum(b),max(b), min(b) from multiprimary; sum | max | min -----+-----+----- 34 | 20 | 2 (1 row) --Testcase 102: SELECT sum(b+5)+2 from multiprimary group by b/2 order by b/2; ?column? ---------- 16 17 27 (3 rows) --Testcase 103: SELECT sum(a) from multiprimary group by b having sum(a) > 0 order by sum(a); sum ----- 1 2 2 (3 rows) --Testcase 104: SELECT sum(a) A from multiprimary group by b having avg(abs(a)) > 0 AND sum(a) > 0 order by A; a --- 1 2 2 (3 rows) --Testcase 105: SELECT count(nullif(a, 1)) FROM multiprimary; count ------- 1 (1 row) --Testcase 106: SELECT a,a FROM multiprimary group by 1,2; a | a ---+--- 1 | 1 2 | 2 (2 rows) --Testcase 107: SELECT * from multiprimary, numbers WHERE multiprimary.a=numbers.a; a | b | c | a | b ---+----+----+---+----- 1 | 2 | 3 | 1 | One 1 | 2 | 4 | 1 | One 1 | 10 | 20 | 1 | One 2 | 20 | 40 | 2 | Two (4 rows) --Testcase 108: EXPLAIN (VERBOSE, COSTS OFF) SELECT sum(a) FROM multiprimary HAVING sum(a) > 0; QUERY PLAN ----------------------------------------------------------- Aggregate Output: sum(a) Filter: (sum(multiprimary.a) > 0) -> Foreign Scan on public.multiprimary Output: a, b, c SQLite query: SELECT `a` FROM main."multiprimary" (6 rows) --Testcase 109: SELECT sum(a) FROM multiprimary HAVING sum(a) > 0; sum ----- 5 (1 row) --Testcase 110: INSERT INTO numbers VALUES(4, 'Four'); -- All where clauses are pushed down --Testcase 111: SELECT * FROM numbers WHERE abs(a) = 4 AND upper(b) = 'FOUR' AND lower(b) = 'four'; a | b ---+------ 4 | Four (1 row) --Testcase 112: EXPLAIN (verbose, costs off) SELECT b, length(b) FROM numbers WHERE abs(a) = 4 AND upper(b) = 'FOUR' AND lower(b) = 'four'; QUERY PLAN ----------------------------------------------------------------------------------------------------- Foreign Scan on public.numbers Output: b, length((b)::text) Filter: ((upper((numbers.b)::text) = 'FOUR'::text) AND (lower((numbers.b)::text) = 'four'::text)) SQLite query: SELECT `b` FROM main."numbers" WHERE ((abs(`a`) = 4)) (4 rows) -- Only "length(b) = 4" are pushed down --Testcase 113: SELECT b, length(b) FROM numbers WHERE length(b) = 4 AND power(1, a) != 0 AND length(reverse(b)) = 4; b | length ------+-------- Four | 4 (1 row) --Testcase 114: EXPLAIN (verbose, costs off) SELECT b, length(b) FROM numbers WHERE length(b) = 4 AND power(1, a) != 0 AND length(reverse(b)) = 4; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.numbers Output: b, length((b)::text) Filter: ((power('1'::double precision, (numbers.a)::double precision) <> '0'::double precision) AND (length(reverse((numbers.b)::text)) = 4)) SQLite query: SELECT `a`, `b` FROM main."numbers" WHERE ((length(`b`) = 4)) (4 rows) --Testcase 115: INSERT INTO multiprimary (b,c) VALUES (99, 100); --Testcase 116: SELECT c FROM multiprimary WHERE COALESCE(a,b,c) = 99; c ----- 100 (1 row) --Testcase 139: CREATE FOREIGN TABLE multiprimary2(a int, b int, c int OPTIONS(column_name 'b')) SERVER sqlite_svr OPTIONS (table 'multiprimary'); --Testcase 117: SELECT * FROM multiprimary2; a | b | c ---+----+---- 1 | 2 | 2 1 | 2 | 2 1 | 10 | 10 2 | 20 | 20 | 99 | 99 (5 rows) --Testcase 214: ALTER FOREIGN TABLE multiprimary2 ALTER COLUMN a OPTIONS(ADD column_name 'b'); --Testcase 118: SELECT * FROM multiprimary2; a | b | c ----+----+---- 2 | 2 | 2 2 | 2 | 2 10 | 10 | 10 20 | 20 | 20 99 | 99 | 99 (5 rows) --Testcase 215: ALTER FOREIGN TABLE multiprimary2 ALTER COLUMN b OPTIONS (column_name 'nosuch column'); --Testcase 119: SELECT * FROM multiprimary2; ERROR: SQL error during prepare: no such column: nosuch column SELECT `b`, `nosuch column`, `b` FROM main."multiprimary" --Testcase 140: EXPLAIN (VERBOSE) SELECT * FROM multiprimary2; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.multiprimary2 (cost=10.00..2275.00 rows=2275 width=12) Output: a, b, c SQLite query: SELECT `b`, `nosuch column`, `b` FROM main."multiprimary" (3 rows) --Testcase 120: SELECT a FROM multiprimary2 WHERE b = 1; ERROR: SQL error during prepare: no such column: nosuch column SELECT `b` FROM main."multiprimary" WHERE ((`nosuch column` = 1)) --Testcase 141: CREATE FOREIGN TABLE columntest(a int OPTIONS(column_name 'a a', key 'true'), "b b" int OPTIONS(key 'true'), c int OPTIONS(column_name 'c c')) SERVER sqlite_svr; --Testcase 121: INSERT INTO columntest VALUES(1,2,3); --Testcase 122: UPDATE columntest SET c=10 WHERE a = 1; --Testcase 123: SELECT * FROM columntest; a | b b | c ---+-----+---- 1 | 2 | 10 (1 row) --Testcase 124: UPDATE columntest SET a=100 WHERE c = 10; --Testcase 125: SELECT * FROM columntest; a | b b | c -----+-----+---- 100 | 2 | 10 (1 row) --Testcase 126: INSERT INTO noprimary VALUES(1,'2'); --Testcase 127: INSERT INTO noprimary SELECT * FROM noprimary; --Testcase 128: SELECT * FROM noprimary; a | b ---+--- 1 | 2 1 | 2 (2 rows) --get version --Testcase 153: \df sqlite* List of functions Schema | Name | Result data type | Argument data types | Type --------+----------------------------+------------------+-----------------------------------------+------ public | sqlite_fdw_disconnect | boolean | text | func public | sqlite_fdw_disconnect_all | boolean | | func public | sqlite_fdw_get_connections | SETOF record | OUT server_name text, OUT valid boolean | func public | sqlite_fdw_handler | fdw_handler | | func public | sqlite_fdw_validator | void | text[], oid | func public | sqlite_fdw_version | integer | | func (6 rows) --Testcase 154: SELECT * FROM public.sqlite_fdw_version(); sqlite_fdw_version -------------------- 20101 (1 row) --Testcase 155: SELECT sqlite_fdw_version(); sqlite_fdw_version -------------------- 20101 (1 row) -- issue #44 github --Testcase 156: CREATE FOREIGN TABLE fts_table (name text, description text) SERVER sqlite_svr; --Testcase 157: INSERT INTO fts_table VALUES ('this is name', 'this is description'); --Testcase 158: SELECT * FROM fts_table; -- should work name | description --------------+--------------------- this is name | this is description (1 row) --Testcase 159: ALTER TABLE fts_table ALTER COLUMN name TYPE int; --Testcase 160: SELECT * FROM fts_table; -- should fail ERROR: invalid input syntax for type =1, column type =3 -- INSERT/UPDATE whole row with generated column --Testcase 216: CREATE FOREIGN TABLE grem1_1 ( a int generated always as (0) stored) SERVER sqlite_svr OPTIONS(table 'grem1_1'); --Testcase 217: INSERT INTO grem1_1 DEFAULT VALUES; --Testcase 218: SELECT * FROM grem1_1; a --- (1 row) --Testcase 219: CREATE FOREIGN TABLE grem1_2 ( a int generated always as (0) stored, b int generated always as (1) stored, c int generated always as (2) stored, d int generated always as (3) stored) SERVER sqlite_svr OPTIONS(table 'grem1_2'); --Testcase 220: INSERT INTO grem1_2 DEFAULT VALUES; --Testcase 221: SELECT * FROM grem1_2; a | b | c | d ---+---+---+--- | | | (1 row) --Testcase 142: DROP FUNCTION test_param_WHERE(); --Testcase 143: DROP FOREIGN TABLE numbers; --Testcase 144: DROP FOREIGN TABLE department; --Testcase 145: DROP FOREIGN TABLE employee; --Testcase 146: DROP FOREIGN TABLE empdata; --Testcase 147: DROP FOREIGN TABLE multiprimary; --Testcase 148: DROP FOREIGN TABLE multiprimary2; --Testcase 149: DROP FOREIGN TABLE columntest; --Testcase 150: DROP FOREIGN TABLE noprimary; --Testcase 161: DROP FOREIGN TABLE fts_table; --Testcase 222: DROP FOREIGN TABLE grem1_1; --Testcase 223: DROP FOREIGN TABLE grem1_2; --Testcase 151: DROP SERVER sqlite_svr; --Testcase 152: DROP EXTENSION sqlite_fdw CASCADE;