File: select.out

package info (click to toggle)
postgresql-mysql-fdw 2.9.2-2
links: PTS, VCS
area: main
in suites: sid, trixie
size: 1,272 kB
sloc: ansic: 5,926; sql: 1,987; sh: 84; makefile: 42
file content (2223 lines) | stat: -rw-r--r-- 89,528 bytes
\set MYSQL_HOST			`echo \'"$MYSQL_HOST"\'`
\set MYSQL_PORT			`echo \'"$MYSQL_PORT"\'`
\set MYSQL_USER_NAME	`echo \'"$MYSQL_USER_NAME"\'`
\set MYSQL_PASS			`echo \'"$MYSQL_PWD"\'`
-- Before running this file User must create database mysql_fdw_regress on
-- MySQL with all permission for MYSQL_USER_NAME user with MYSQL_PWD password
-- and ran mysql_init.sh file to create tables.
\c contrib_regression
CREATE EXTENSION IF NOT EXISTS mysql_fdw;
CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr
  OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
-- Check version
SELECT mysql_fdw_version();
 mysql_fdw_version 
-------------------
             20902
(1 row)

-- Create foreign tables
CREATE FOREIGN TABLE f_mysql_test(a int, b int)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'mysql_test');
CREATE FOREIGN TABLE f_numbers(a int, b varchar(255))
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'numbers');
CREATE FOREIGN TABLE f_test_tbl1 (c1 INTEGER, c2 VARCHAR(10), c3 CHAR(9),c4 BIGINT, c5 pg_catalog.Date, c6 DECIMAL, c7 INTEGER, c8 SMALLINT)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test_tbl1');
CREATE FOREIGN TABLE f_test_tbl2 (c1 INTEGER, c2 VARCHAR(14), c3 VARCHAR(13))
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test_tbl2');
CREATE TYPE size_t AS enum('small','medium','large');
CREATE FOREIGN TABLE f_enum_t1(id int, size size_t)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'enum_t1');
CREATE FOREIGN TABLE test5_1(c1 INT, c2 CHAR, c3 VARCHAR, c4 BOOLEAN, c5 TEXT, c6 INTERVAL, c7 BYTEA, c8 pg_catalog.DATE, c9 NUMERIC, c10 NAME)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test5');
CREATE FOREIGN TABLE test5_2(c1 INT, c2 BYTEA, c3 BYTEA, c4 BYTEA, c5 BYTEA, c6 BYTEA, c7 BYTEA, c8 BYTEA, c9 BYTEA, c10 BYTEA)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test5');
-- Insert data in MySQL db using foreign tables
INSERT INTO f_test_tbl1 VALUES (100, 'EMP1', 'ADMIN', 1300, '1980-12-17', 800.23, NULL, 20);
INSERT INTO f_test_tbl1 VALUES (200, 'EMP2', 'SALESMAN', 600, '1981-02-20', 1600.00, 300, 30);
INSERT INTO f_test_tbl1 VALUES (300, 'EMP3', 'SALESMAN', 600, '1981-02-22', 1250, 500, 30);
INSERT INTO f_test_tbl1 VALUES (400, 'EMP4', 'MANAGER', 900, '1981-04-02', 2975.12, NULL, 20);
INSERT INTO f_test_tbl1 VALUES (500, 'EMP5', 'SALESMAN', 600, '1981-09-28', 1250, 1400, 30);
INSERT INTO f_test_tbl1 VALUES (600, 'EMP6', 'MANAGER', 900, '1981-05-01', 2850, NULL, 30);
INSERT INTO f_test_tbl1 VALUES (700, 'EMP7', 'MANAGER', 900, '1981-06-09', 2450.45, NULL, 10);
INSERT INTO f_test_tbl1 VALUES (800, 'EMP8', 'FINANCE', 400, '1987-04-19', 3000, NULL, 20);
INSERT INTO f_test_tbl1 VALUES (900, 'EMP9', 'HEAD', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO f_test_tbl1 VALUES (1000, 'EMP10', 'SALESMAN', 600, '1980-09-08', 1500, 0, 30);
INSERT INTO f_test_tbl1 VALUES (1100, 'EMP11', 'ADMIN', 800, '1987-05-23', 1100, NULL, 20);
INSERT INTO f_test_tbl1 VALUES (1200, 'EMP12', 'ADMIN', 600, '1981-12-03', 950, NULL, 30);
INSERT INTO f_test_tbl1 VALUES (1300, 'EMP13', 'FINANCE', 400, '1981-12-03', 3000, NULL, 20);
INSERT INTO f_test_tbl1 VALUES (1400, 'EMP14', 'ADMIN', 700, '1982-01-23', 1300, NULL, 10);
INSERT INTO f_test_tbl2 VALUES(10, 'DEVELOPMENT', 'PUNE');
INSERT INTO f_test_tbl2 VALUES(20, 'ADMINISTRATION', 'BANGLORE');
INSERT INTO f_test_tbl2 VALUES(30, 'SALES', 'MUMBAI');
INSERT INTO f_test_tbl2 VALUES(40, 'HR', 'NAGPUR');
SET datestyle TO ISO;
-- Retrieve Data from Foreign Table using SELECT Statement.
SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1
  ORDER BY c1 DESC, c8;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
(14 rows)

SELECT DISTINCT c8 FROM f_test_tbl1 ORDER BY 1;
 c8 
----
 10
 20
 30
(3 rows)

SELECT c2 AS "Employee Name" FROM f_test_tbl1 ORDER BY 1;
 Employee Name 
---------------
 EMP1
 EMP10
 EMP11
 EMP12
 EMP13
 EMP14
 EMP2
 EMP3
 EMP4
 EMP5
 EMP6
 EMP7
 EMP8
 EMP9
(14 rows)

SELECT c8, c6, c7 FROM f_test_tbl1 ORDER BY 1, 2, 3;
 c8 |     c6     |  c7  
----+------------+------
 10 | 1300.00000 |     
 10 | 2450.45000 |     
 10 | 5000.00000 |     
 20 |  800.23000 |     
 20 | 1100.00000 |     
 20 | 2975.12000 |     
 20 | 3000.00000 |     
 20 | 3000.00000 |     
 30 |  950.00000 |     
 30 | 1250.00000 |  500
 30 | 1250.00000 | 1400
 30 | 1500.00000 |    0
 30 | 1600.00000 |  300
 30 | 2850.00000 |     
(14 rows)

SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1
  WHERE c1 = 100 ORDER BY 1;
 c1  |  c2  |    c3     |  c4  |     c5     |    c6     | c7 | c8 
-----+------+-----------+------+------------+-----------+----+----
 100 | EMP1 | ADMIN     | 1300 | 1980-12-17 | 800.23000 |    | 20
(1 row)

SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1
  WHERE c1 = 100 OR c1 = 700 ORDER BY 1;
 c1  |  c2  |    c3     |  c4  |     c5     |     c6     | c7 | c8 
-----+------+-----------+------+------------+------------+----+----
 100 | EMP1 | ADMIN     | 1300 | 1980-12-17 |  800.23000 |    | 20
 700 | EMP7 | MANAGER   |  900 | 1981-06-09 | 2450.45000 |    | 10
(2 rows)

SELECT * FROM f_test_tbl1 WHERE c3 like 'SALESMAN' ORDER BY 1;
  c1  |  c2   |    c3     | c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+-----+------------+------------+------+----
  200 | EMP2  | SALESMAN  | 600 | 1981-02-20 | 1600.00000 |  300 | 30
  300 | EMP3  | SALESMAN  | 600 | 1981-02-22 | 1250.00000 |  500 | 30
  500 | EMP5  | SALESMAN  | 600 | 1981-09-28 | 1250.00000 | 1400 | 30
 1000 | EMP10 | SALESMAN  | 600 | 1980-09-08 | 1500.00000 |    0 | 30
(4 rows)

SELECT * FROM f_test_tbl1 WHERE c1 IN (100, 700) ORDER BY 1;
 c1  |  c2  |    c3     |  c4  |     c5     |     c6     | c7 | c8 
-----+------+-----------+------+------------+------------+----+----
 100 | EMP1 | ADMIN     | 1300 | 1980-12-17 |  800.23000 |    | 20
 700 | EMP7 | MANAGER   |  900 | 1981-06-09 | 2450.45000 |    | 10
(2 rows)

SELECT * FROM f_test_tbl1 WHERE c1 NOT IN (100, 700) ORDER BY 1 LIMIT 5;
 c1  |  c2  |    c3     | c4  |     c5     |     c6     |  c7  | c8 
-----+------+-----------+-----+------------+------------+------+----
 200 | EMP2 | SALESMAN  | 600 | 1981-02-20 | 1600.00000 |  300 | 30
 300 | EMP3 | SALESMAN  | 600 | 1981-02-22 | 1250.00000 |  500 | 30
 400 | EMP4 | MANAGER   | 900 | 1981-04-02 | 2975.12000 |      | 20
 500 | EMP5 | SALESMAN  | 600 | 1981-09-28 | 1250.00000 | 1400 | 30
 600 | EMP6 | MANAGER   | 900 | 1981-05-01 | 2850.00000 |      | 30
(5 rows)

SELECT * FROM f_test_tbl1 WHERE c8 BETWEEN 10 AND 20 ORDER BY 1;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     | c7 | c8 
------+-------+-----------+------+------------+------------+----+----
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |    | 20
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |    | 20
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |    | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |    | 20
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |    | 10
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |    | 20
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |    | 20
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |    | 10
(8 rows)

SELECT * FROM f_test_tbl1 ORDER BY 1 OFFSET 5;
  c1  |  c2   |    c3     | c4  |     c5     |     c6     | c7 | c8 
------+-------+-----------+-----+------------+------------+----+----
  600 | EMP6  | MANAGER   | 900 | 1981-05-01 | 2850.00000 |    | 30
  700 | EMP7  | MANAGER   | 900 | 1981-06-09 | 2450.45000 |    | 10
  800 | EMP8  | FINANCE   | 400 | 1987-04-19 | 3000.00000 |    | 20
  900 | EMP9  | HEAD      |     | 1981-11-17 | 5000.00000 |    | 10
 1000 | EMP10 | SALESMAN  | 600 | 1980-09-08 | 1500.00000 |  0 | 30
 1100 | EMP11 | ADMIN     | 800 | 1987-05-23 | 1100.00000 |    | 20
 1200 | EMP12 | ADMIN     | 600 | 1981-12-03 |  950.00000 |    | 30
 1300 | EMP13 | FINANCE   | 400 | 1981-12-03 | 3000.00000 |    | 20
 1400 | EMP14 | ADMIN     | 700 | 1982-01-23 | 1300.00000 |    | 10
(9 rows)

-- Retrieve Data from Foreign Table using Group By Clause.
SELECT c8 "Department", COUNT(c1) "Total Employees" FROM f_test_tbl1
  GROUP BY c8 ORDER BY c8;
 Department | Total Employees 
------------+-----------------
         10 |               3
         20 |               5
         30 |               6
(3 rows)

SELECT c8, SUM(c6) FROM f_test_tbl1
  GROUP BY c8 HAVING c8 IN (10, 30) ORDER BY c8;
 c8 |    sum     
----+------------
 10 | 8750.45000
 30 | 9400.00000
(2 rows)

SELECT c8, SUM(c6) FROM f_test_tbl1
  GROUP BY c8 HAVING SUM(c6) > 9400 ORDER BY c8;
 c8 |     sum     
----+-------------
 20 | 10875.35000
(1 row)

-- Row Level Functions
SELECT UPPER(c2), LOWER(c2) FROM f_test_tbl2 ORDER BY 1, 2;
     upper      |     lower      
----------------+----------------
 ADMINISTRATION | administration
 DEVELOPMENT    | development
 HR             | hr
 SALES          | sales
(4 rows)

-- Retrieve Data from Foreign Table using Sub Queries.
SELECT * FROM f_test_tbl1
  WHERE c8 <> ALL (SELECT c1 FROM f_test_tbl2 WHERE c1 IN (10, 30, 40))
  ORDER BY c1;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     | c7 | c8 
------+-------+-----------+------+------------+------------+----+----
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |    | 20
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |    | 20
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |    | 20
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |    | 20
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |    | 20
(5 rows)

SELECT c1, c2, c3 FROM f_test_tbl2
  WHERE EXISTS (SELECT 1 FROM f_test_tbl1 WHERE f_test_tbl2.c1 = f_test_tbl1.c8)
  ORDER BY 1, 2;
 c1 |       c2       |    c3    
----+----------------+----------
 10 | DEVELOPMENT    | PUNE
 20 | ADMINISTRATION | BANGLORE
 30 | SALES          | MUMBAI
(3 rows)

SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1
  WHERE c8 NOT IN (SELECT c1 FROM f_test_tbl2) ORDER BY c1;
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 
----+----+----+----+----+----+----+----
(0 rows)

-- Retrieve Data from Foreign Table using UNION Operator.
SELECT c1, c2 FROM f_test_tbl2 UNION
SELECT c1, c2 FROM f_test_tbl1 ORDER BY c1;
  c1  |       c2       
------+----------------
   10 | DEVELOPMENT
   20 | ADMINISTRATION
   30 | SALES
   40 | HR
  100 | EMP1
  200 | EMP2
  300 | EMP3
  400 | EMP4
  500 | EMP5
  600 | EMP6
  700 | EMP7
  800 | EMP8
  900 | EMP9
 1000 | EMP10
 1100 | EMP11
 1200 | EMP12
 1300 | EMP13
 1400 | EMP14
(18 rows)

SELECT c2 FROM f_test_tbl2  UNION ALL
SELECT c2 FROM f_test_tbl1 ORDER BY c2;
       c2       
----------------
 ADMINISTRATION
 DEVELOPMENT
 EMP1
 EMP10
 EMP11
 EMP12
 EMP13
 EMP14
 EMP2
 EMP3
 EMP4
 EMP5
 EMP6
 EMP7
 EMP8
 EMP9
 HR
 SALES
(18 rows)

-- Retrieve Data from Foreign Table using INTERSECT Operator.
SELECT c2 FROM f_test_tbl1 WHERE c1 >= 800 INTERSECT
SELECT c2 FROM f_test_tbl1 WHERE c1 >= 400 ORDER BY c2;
  c2   
-------
 EMP10
 EMP11
 EMP12
 EMP13
 EMP14
 EMP8
 EMP9
(7 rows)

SELECT c2 FROM f_test_tbl1 WHERE c1 >= 800 INTERSECT ALL
SELECT c2 FROM f_test_tbl1 WHERE c1 >= 400 ORDER BY c2;
  c2   
-------
 EMP10
 EMP11
 EMP12
 EMP13
 EMP14
 EMP8
 EMP9
(7 rows)

-- Retrieve Data from Foreign Table using EXCEPT.
SELECT c2 FROM f_test_tbl1 EXCEPT
SELECT c2 FROM f_test_tbl1 WHERE c1 > 900 ORDER BY c2;
  c2  
------
 EMP1
 EMP2
 EMP3
 EMP4
 EMP5
 EMP6
 EMP7
 EMP8
 EMP9
(9 rows)

SELECT c2 FROM f_test_tbl1 EXCEPT ALL
SELECT c2 FROM f_test_tbl1 WHERE c1 > 900 ORDER BY c2;
  c2  
------
 EMP1
 EMP2
 EMP3
 EMP4
 EMP5
 EMP6
 EMP7
 EMP8
 EMP9
(9 rows)

-- Retrieve Data from Foreign Table using CTE (With Clause).
WITH
  with_qry AS (SELECT c1, c2, c3 FROM f_test_tbl2)
SELECT e.c2, e.c6, w.c1, w.c2 FROM f_test_tbl1 e, with_qry w
  WHERE e.c8 = w.c1 ORDER BY e.c8, e.c2;
  c2   |     c6     | c1 |       c2       
-------+------------+----+----------------
 EMP14 | 1300.00000 | 10 | DEVELOPMENT
 EMP7  | 2450.45000 | 10 | DEVELOPMENT
 EMP9  | 5000.00000 | 10 | DEVELOPMENT
 EMP1  |  800.23000 | 20 | ADMINISTRATION
 EMP11 | 1100.00000 | 20 | ADMINISTRATION
 EMP13 | 3000.00000 | 20 | ADMINISTRATION
 EMP4  | 2975.12000 | 20 | ADMINISTRATION
 EMP8  | 3000.00000 | 20 | ADMINISTRATION
 EMP10 | 1500.00000 | 30 | SALES
 EMP12 |  950.00000 | 30 | SALES
 EMP2  | 1600.00000 | 30 | SALES
 EMP3  | 1250.00000 | 30 | SALES
 EMP5  | 1250.00000 | 30 | SALES
 EMP6  | 2850.00000 | 30 | SALES
(14 rows)

WITH
  test_tbl2_costs AS (SELECT d.c2, SUM(c6) test_tbl2_total FROM f_test_tbl1 e, f_test_tbl2 d
    WHERE e.c8 = d.c1 GROUP BY 1),
  avg_cost AS (SELECT SUM(test_tbl2_total)/COUNT(*) avg FROM test_tbl2_costs)
SELECT * FROM test_tbl2_costs
  WHERE test_tbl2_total > (SELECT avg FROM avg_cost) ORDER BY c2;
       c2       | test_tbl2_total 
----------------+-----------------
 ADMINISTRATION |     10875.35000
(1 row)

-- Retrieve Data from Foreign Table using Window Clause.
SELECT c8, c1, c6, AVG(c6) OVER (PARTITION BY c8) FROM f_test_tbl1
  ORDER BY c8, c1;
 c8 |  c1  |     c6     |          avg          
----+------+------------+-----------------------
 10 |  700 | 2450.45000 | 2916.8166666666666667
 10 |  900 | 5000.00000 | 2916.8166666666666667
 10 | 1400 | 1300.00000 | 2916.8166666666666667
 20 |  100 |  800.23000 | 2175.0700000000000000
 20 |  400 | 2975.12000 | 2175.0700000000000000
 20 |  800 | 3000.00000 | 2175.0700000000000000
 20 | 1100 | 1100.00000 | 2175.0700000000000000
 20 | 1300 | 3000.00000 | 2175.0700000000000000
 30 |  200 | 1600.00000 | 1566.6666666666666667
 30 |  300 | 1250.00000 | 1566.6666666666666667
 30 |  500 | 1250.00000 | 1566.6666666666666667
 30 |  600 | 2850.00000 | 1566.6666666666666667
 30 | 1000 | 1500.00000 | 1566.6666666666666667
 30 | 1200 |  950.00000 | 1566.6666666666666667
(14 rows)

SELECT c8, c1, c6, COUNT(c6) OVER (PARTITION BY c8) FROM f_test_tbl1
  WHERE c8 IN (10, 30, 40, 50, 60, 70) ORDER BY c8, c1;
 c8 |  c1  |     c6     | count 
----+------+------------+-------
 10 |  700 | 2450.45000 |     3
 10 |  900 | 5000.00000 |     3
 10 | 1400 | 1300.00000 |     3
 30 |  200 | 1600.00000 |     6
 30 |  300 | 1250.00000 |     6
 30 |  500 | 1250.00000 |     6
 30 |  600 | 2850.00000 |     6
 30 | 1000 | 1500.00000 |     6
 30 | 1200 |  950.00000 |     6
(9 rows)

SELECT c8, c1, c6, SUM(c6) OVER (PARTITION BY c8) FROM f_test_tbl1
  ORDER BY c8, c1;
 c8 |  c1  |     c6     |     sum     
----+------+------------+-------------
 10 |  700 | 2450.45000 |  8750.45000
 10 |  900 | 5000.00000 |  8750.45000
 10 | 1400 | 1300.00000 |  8750.45000
 20 |  100 |  800.23000 | 10875.35000
 20 |  400 | 2975.12000 | 10875.35000
 20 |  800 | 3000.00000 | 10875.35000
 20 | 1100 | 1100.00000 | 10875.35000
 20 | 1300 | 3000.00000 | 10875.35000
 30 |  200 | 1600.00000 |  9400.00000
 30 |  300 | 1250.00000 |  9400.00000
 30 |  500 | 1250.00000 |  9400.00000
 30 |  600 | 2850.00000 |  9400.00000
 30 | 1000 | 1500.00000 |  9400.00000
 30 | 1200 |  950.00000 |  9400.00000
(14 rows)

-- Views
CREATE VIEW smpl_vw AS
  SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1
    ORDER BY c1;
SELECT * FROM smpl_vw ORDER BY 1;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
(14 rows)

CREATE VIEW comp_vw (s1, s2, s3, s6, s7, s8, d2) AS
  SELECT s.c1, s.c2, s.c3, s.c6, s.c7, s.c8, d.c2
    FROM f_test_tbl2 d, f_test_tbl1 s WHERE d.c1 = s.c8 AND d.c1 = 10
    ORDER BY s.c1;
SELECT * FROM comp_vw ORDER BY 1;
  s1  |  s2   |    s3     |     s6     | s7 | s8 |     d2      
------+-------+-----------+------------+----+----+-------------
  700 | EMP7  | MANAGER   | 2450.45000 |    | 10 | DEVELOPMENT
  900 | EMP9  | HEAD      | 5000.00000 |    | 10 | DEVELOPMENT
 1400 | EMP14 | ADMIN     | 1300.00000 |    | 10 | DEVELOPMENT
(3 rows)

CREATE TEMPORARY VIEW ttest_tbl1_vw AS
  SELECT c1, c2, c3 FROM f_test_tbl2;
SELECT * FROM ttest_tbl1_vw ORDER BY 1, 2;
 c1 |       c2       |    c3    
----+----------------+----------
 10 | DEVELOPMENT    | PUNE
 20 | ADMINISTRATION | BANGLORE
 30 | SALES          | MUMBAI
 40 | HR             | NAGPUR
(4 rows)

CREATE VIEW mul_tbl_view AS
  SELECT d.c1 dc1, d.c2 dc2, e.c1 ec1, e.c2 ec2, e.c6 ec6
    FROM f_test_tbl2 d INNER JOIN f_test_tbl1 e ON d.c1 = e.c8 ORDER BY d.c1;
SELECT * FROM mul_tbl_view ORDER BY 1, 2,3;
 dc1 |      dc2       | ec1  |  ec2  |    ec6     
-----+----------------+------+-------+------------
  10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000
  10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000
  10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000
  20 | ADMINISTRATION |  100 | EMP1  |  800.23000
  20 | ADMINISTRATION |  400 | EMP4  | 2975.12000
  20 | ADMINISTRATION |  800 | EMP8  | 3000.00000
  20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000
  20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000
  30 | SALES          |  200 | EMP2  | 1600.00000
  30 | SALES          |  300 | EMP3  | 1250.00000
  30 | SALES          |  500 | EMP5  | 1250.00000
  30 | SALES          |  600 | EMP6  | 2850.00000
  30 | SALES          | 1000 | EMP10 | 1500.00000
  30 | SALES          | 1200 | EMP12 |  950.00000
(14 rows)

-- Insert Some records in numbers table.
INSERT INTO f_numbers VALUES (1, 'One');
INSERT INTO f_numbers VALUES (2, 'Two');
INSERT INTO f_numbers VALUES (3, 'Three');
INSERT INTO f_numbers VALUES (4, 'Four');
INSERT INTO f_numbers VALUES (5, 'Five');
INSERT INTO f_numbers VALUES (6, 'Six');
INSERT INTO f_numbers VALUES (7, 'Seven');
INSERT INTO f_numbers VALUES (8, 'Eight');
INSERT INTO f_numbers VALUES (9, 'Nine');
-- Retrieve Data From foreign tables in functions.
CREATE OR REPLACE FUNCTION test_param_where() RETURNS void AS $$
DECLARE
  n varchar;
BEGIN
  FOR x IN 1..9 LOOP
    SELECT b INTO n FROM f_numbers WHERE a = x;
    RAISE NOTICE 'Found number %', n;
  END LOOP;
  return;
END
$$ LANGUAGE plpgsql;
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)

CREATE OR REPLACE FUNCTION test_param_where2(int, text) RETURNS integer AS '
  SELECT a FROM f_numbers WHERE a = $1 AND b = $2;
' LANGUAGE sql;
SELECT test_param_where2(1, 'One');
 test_param_where2 
-------------------
                 1
(1 row)

-- Foreign-Foreign table joins
-- CROSS JOIN.
SELECT f_test_tbl2.c2, f_test_tbl1.c2 FROM f_test_tbl2 CROSS JOIN f_test_tbl1 ORDER BY 1, 2;
       c2       |  c2   
----------------+-------
 ADMINISTRATION | EMP1
 ADMINISTRATION | EMP10
 ADMINISTRATION | EMP11
 ADMINISTRATION | EMP12
 ADMINISTRATION | EMP13
 ADMINISTRATION | EMP14
 ADMINISTRATION | EMP2
 ADMINISTRATION | EMP3
 ADMINISTRATION | EMP4
 ADMINISTRATION | EMP5
 ADMINISTRATION | EMP6
 ADMINISTRATION | EMP7
 ADMINISTRATION | EMP8
 ADMINISTRATION | EMP9
 DEVELOPMENT    | EMP1
 DEVELOPMENT    | EMP10
 DEVELOPMENT    | EMP11
 DEVELOPMENT    | EMP12
 DEVELOPMENT    | EMP13
 DEVELOPMENT    | EMP14
 DEVELOPMENT    | EMP2
 DEVELOPMENT    | EMP3
 DEVELOPMENT    | EMP4
 DEVELOPMENT    | EMP5
 DEVELOPMENT    | EMP6
 DEVELOPMENT    | EMP7
 DEVELOPMENT    | EMP8
 DEVELOPMENT    | EMP9
 HR             | EMP1
 HR             | EMP10
 HR             | EMP11
 HR             | EMP12
 HR             | EMP13
 HR             | EMP14
 HR             | EMP2
 HR             | EMP3
 HR             | EMP4
 HR             | EMP5
 HR             | EMP6
 HR             | EMP7
 HR             | EMP8
 HR             | EMP9
 SALES          | EMP1
 SALES          | EMP10
 SALES          | EMP11
 SALES          | EMP12
 SALES          | EMP13
 SALES          | EMP14
 SALES          | EMP2
 SALES          | EMP3
 SALES          | EMP4
 SALES          | EMP5
 SALES          | EMP6
 SALES          | EMP7
 SALES          | EMP8
 SALES          | EMP9
(56 rows)

-- INNER JOIN.
SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d, f_test_tbl1 e WHERE d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
(14 rows)

SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d INNER JOIN f_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
(14 rows)

-- OUTER JOINS.
SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d LEFT OUTER JOIN f_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
 40 | HR             |      |       |            |   
(15 rows)

SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d RIGHT OUTER JOIN f_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
(14 rows)

SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d FULL OUTER JOIN f_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
 40 | HR             |      |       |            |   
(15 rows)

-- Local-Foreign table joins.
CREATE TABLE l_test_tbl1 AS
  SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1;
CREATE TABLE l_test_tbl2 AS
  SELECT c1, c2, c3 FROM f_test_tbl2;
-- CROSS JOIN.
SELECT f_test_tbl2.c2, l_test_tbl1.c2 FROM f_test_tbl2 CROSS JOIN l_test_tbl1 ORDER BY 1, 2;
       c2       |  c2   
----------------+-------
 ADMINISTRATION | EMP1
 ADMINISTRATION | EMP10
 ADMINISTRATION | EMP11
 ADMINISTRATION | EMP12
 ADMINISTRATION | EMP13
 ADMINISTRATION | EMP14
 ADMINISTRATION | EMP2
 ADMINISTRATION | EMP3
 ADMINISTRATION | EMP4
 ADMINISTRATION | EMP5
 ADMINISTRATION | EMP6
 ADMINISTRATION | EMP7
 ADMINISTRATION | EMP8
 ADMINISTRATION | EMP9
 DEVELOPMENT    | EMP1
 DEVELOPMENT    | EMP10
 DEVELOPMENT    | EMP11
 DEVELOPMENT    | EMP12
 DEVELOPMENT    | EMP13
 DEVELOPMENT    | EMP14
 DEVELOPMENT    | EMP2
 DEVELOPMENT    | EMP3
 DEVELOPMENT    | EMP4
 DEVELOPMENT    | EMP5
 DEVELOPMENT    | EMP6
 DEVELOPMENT    | EMP7
 DEVELOPMENT    | EMP8
 DEVELOPMENT    | EMP9
 HR             | EMP1
 HR             | EMP10
 HR             | EMP11
 HR             | EMP12
 HR             | EMP13
 HR             | EMP14
 HR             | EMP2
 HR             | EMP3
 HR             | EMP4
 HR             | EMP5
 HR             | EMP6
 HR             | EMP7
 HR             | EMP8
 HR             | EMP9
 SALES          | EMP1
 SALES          | EMP10
 SALES          | EMP11
 SALES          | EMP12
 SALES          | EMP13
 SALES          | EMP14
 SALES          | EMP2
 SALES          | EMP3
 SALES          | EMP4
 SALES          | EMP5
 SALES          | EMP6
 SALES          | EMP7
 SALES          | EMP8
 SALES          | EMP9
(56 rows)

-- INNER JOIN.
SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM l_test_tbl2 d, f_test_tbl1 e WHERE d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
(14 rows)

SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d INNER JOIN l_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
(14 rows)

-- OUTER JOINS.
SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d LEFT OUTER JOIN l_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
 40 | HR             |      |       |            |   
(15 rows)

SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d RIGHT OUTER JOIN l_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
(14 rows)

SELECT d.c1, d.c2, e.c1, e.c2, e.c6, e.c8
  FROM f_test_tbl2 d FULL OUTER JOIN l_test_tbl1 e ON d.c1 = e.c8 ORDER BY 1, 3;
 c1 |       c2       |  c1  |  c2   |     c6     | c8 
----+----------------+------+-------+------------+----
 10 | DEVELOPMENT    |  700 | EMP7  | 2450.45000 | 10
 10 | DEVELOPMENT    |  900 | EMP9  | 5000.00000 | 10
 10 | DEVELOPMENT    | 1400 | EMP14 | 1300.00000 | 10
 20 | ADMINISTRATION |  100 | EMP1  |  800.23000 | 20
 20 | ADMINISTRATION |  400 | EMP4  | 2975.12000 | 20
 20 | ADMINISTRATION |  800 | EMP8  | 3000.00000 | 20
 20 | ADMINISTRATION | 1100 | EMP11 | 1100.00000 | 20
 20 | ADMINISTRATION | 1300 | EMP13 | 3000.00000 | 20
 30 | SALES          |  200 | EMP2  | 1600.00000 | 30
 30 | SALES          |  300 | EMP3  | 1250.00000 | 30
 30 | SALES          |  500 | EMP5  | 1250.00000 | 30
 30 | SALES          |  600 | EMP6  | 2850.00000 | 30
 30 | SALES          | 1000 | EMP10 | 1500.00000 | 30
 30 | SALES          | 1200 | EMP12 |  950.00000 | 30
 40 | HR             |      |       |            |   
(15 rows)

-- FDW-206: LEFT JOIN LATERAL case should not crash
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_mysql_test t1 LEFT JOIN LATERAL (
  SELECT t2.a, t1.a AS t1_a FROM f_mysql_test t2) t3 ON t1.a = t3.a ORDER BY 1;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Nested Loop Left Join
   Output: t1.a, t1.b, t2.a, (t1.a)
   ->  Foreign Scan on public.f_mysql_test t1
         Output: t1.a, t1.b
         Remote query: SELECT `a`, `b` FROM `mysql_fdw_regress`.`mysql_test` ORDER BY `a` IS NULL, `a` ASC
   ->  Foreign Scan on public.f_mysql_test t2
         Output: t2.a, t1.a
         Remote query: SELECT `a` FROM `mysql_fdw_regress`.`mysql_test` WHERE ((? = `a`))
(8 rows)

SELECT * FROM f_mysql_test t1 LEFT JOIN LATERAL (
  SELECT t2.a, t1.a AS t1_a FROM f_mysql_test t2) t3 ON t1.a = t3.a ORDER BY 1;
 a | b | a | t1_a 
---+---+---+------
 1 | 1 | 1 |    1
(1 row)

SELECT t1.c1, t3.c1, t3.t1_c8 FROM f_test_tbl1 t1 INNER JOIN LATERAL (
  SELECT t2.c1, t1.c8 AS t1_c8 FROM f_test_tbl2 t2) t3 ON t3.c1 = t3.t1_c8
  ORDER BY 1, 2, 3;
  c1  | c1 | t1_c8 
------+----+-------
  100 | 20 |    20
  200 | 30 |    30
  300 | 30 |    30
  400 | 20 |    20
  500 | 30 |    30
  600 | 30 |    30
  700 | 10 |    10
  800 | 20 |    20
  900 | 10 |    10
 1000 | 30 |    30
 1100 | 20 |    20
 1200 | 30 |    30
 1300 | 20 |    20
 1400 | 10 |    10
(14 rows)

SELECT t1.c1, t3.c1, t3.t1_c8 FROM l_test_tbl1 t1 LEFT JOIN LATERAL (
  SELECT t2.c1, t1.c8 AS t1_c8 FROM f_test_tbl2 t2) t3 ON t3.c1 = t3.t1_c8
  ORDER BY 1, 2, 3;
  c1  | c1 | t1_c8 
------+----+-------
  100 | 20 |    20
  200 | 30 |    30
  300 | 30 |    30
  400 | 20 |    20
  500 | 30 |    30
  600 | 30 |    30
  700 | 10 |    10
  800 | 20 |    20
  900 | 10 |    10
 1000 | 30 |    30
 1100 | 20 |    20
 1200 | 30 |    30
 1300 | 20 |    20
 1400 | 10 |    10
(14 rows)

SELECT *, (SELECT r FROM (SELECT c1 AS c1) x, LATERAL (SELECT c1 AS r) y)
  FROM f_test_tbl1 ORDER BY 1, 2, 3;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 |  r   
------+-------+-----------+------+------------+------------+------+----+------
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20 |  100
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30 |  200
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30 |  300
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20 |  400
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30 |  500
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30 |  600
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10 |  700
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20 |  800
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10 |  900
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30 | 1000
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20 | 1100
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30 | 1200
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20 | 1300
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10 | 1400
(14 rows)

-- LATERAL JOIN with RIGHT should throw error
SELECT t1.c1, t3.c1, t3.t1_c8 FROM f_test_tbl1 t1 RIGHT JOIN LATERAL (
  SELECT t2.c1, t1.c8 AS t1_c8 FROM f_test_tbl2 t2) t3 ON t3.c1 = t3.t1_c8
  ORDER BY 1, 2, 3;
ERROR:  invalid reference to FROM-clause entry for table "t1"
LINE 2:   SELECT t2.c1, t1.c8 AS t1_c8 FROM f_test_tbl2 t2) t3 ON t3...
                        ^
DETAIL:  The combining JOIN type must be INNER or LEFT for a LATERAL reference.
-- FDW-207: NATURAL JOIN should give correct output
SELECT t1.c1, t2.c1, t3.c1
  FROM f_test_tbl1 t1 NATURAL JOIN f_test_tbl1 t2 NATURAL JOIN f_test_tbl1 t3
  ORDER BY 1, 2, 3;
  c1  |  c1  |  c1  
------+------+------
  200 |  200 |  200
  300 |  300 |  300
  500 |  500 |  500
 1000 | 1000 | 1000
(4 rows)

-- FDW-208: IS NULL and LIKE should give the correct output with
-- use_remote_estimate set to true.
INSERT INTO f_test_tbl2 VALUES (50, 'TEMP1', NULL);
INSERT INTO f_test_tbl2 VALUES (60, 'TEMP2', NULL);
ALTER SERVER mysql_svr OPTIONS (use_remote_estimate 'true');
SELECT t1.c1, t2.c1
  FROM f_test_tbl2 t1 INNER JOIN f_test_tbl2 t2 ON t1.c1 = t2.c1
  WHERE t1.c3 IS NULL ORDER BY 1, 2;
 c1 | c1 
----+----
 50 | 50
 60 | 60
(2 rows)

SELECT t1.c1, t2.c1
  FROM f_test_tbl2 t1 INNER JOIN f_test_tbl2 t2 ON t1.c1 = t2.c1 AND t1.c2 LIKE 'TEMP%'
  ORDER BY 1, 2;
 c1 | c1 
----+----
 50 | 50
 60 | 60
(2 rows)

DELETE FROM f_test_tbl2 WHERE c1 IN (50, 60);
ALTER SERVER mysql_svr OPTIONS (SET use_remote_estimate 'false');
-- FDW-169: Insert/Update/Delete on enum column.
INSERT INTO f_enum_t1
  VALUES (1, 'small'), (2, 'medium'), (3, 'medium'), (4, 'small');
SELECT * FROM f_enum_t1 WHERE id = 4;
 id | size  
----+-------
  4 | small
(1 row)

UPDATE f_enum_t1 SET size = 'large' WHERE id = 4;
SELECT * FROM f_enum_t1 WHERE id = 4;
 id | size  
----+-------
  4 | large
(1 row)

DELETE FROM f_enum_t1 WHERE size = 'large';
SELECT * FROM f_enum_t1 WHERE id = 4;
 id | size 
----+------
(0 rows)

-- Negative scenarios for ENUM handling.
-- Test that if we insert the ENUM value which is not present on MySQL side,
-- but present on Postgres side.
DROP FOREIGN TABLE f_enum_t1;
DROP TYPE size_t;
-- Create the type with extra enum values.
CREATE TYPE size_t AS enum('small', 'medium', 'large', 'largest', '');
CREATE FOREIGN TABLE f_enum_t1(id int, size size_t)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'enum_t1');
-- If we insert the enum value which is not present on MySQL side then it
-- inserts empty string in ANSI_QUOTES sql_mode, so verify that.
INSERT INTO f_enum_t1 VALUES (4, 'largest');
SELECT * from f_enum_t1;
 id |  size  
----+--------
  1 | small
  2 | medium
  3 | medium
  4 | 
(4 rows)

DELETE FROM f_enum_t1 WHERE size = '';
-- Postgres should throw an error as the value which we are inserting for enum
-- column is not present in enum on Postgres side, no matter whether it is
-- present on MySQL side or not. PG's sanity check itself throws an error.
INSERT INTO f_enum_t1 VALUES (4, 'big');
ERROR:  invalid input value for enum size_t: "big"
LINE 1: INSERT INTO f_enum_t1 VALUES (4, 'big');
                                         ^
-- FDW-155: Enum data type can be handled correctly in select statements on
-- foreign table.
SELECT * FROM f_enum_t1 WHERE size = 'medium' ORDER BY id;
 id |  size  
----+--------
  2 | medium
  3 | medium
(2 rows)

-- Remote aggregate in combination with a local Param (for the output
-- of an initplan)
SELECT EXISTS(SELECT 1 FROM pg_enum), sum(id) from f_enum_t1;
 exists | sum 
--------+-----
 t      |   6
(1 row)

SELECT EXISTS(SELECT 1 FROM pg_enum), sum(id) from f_enum_t1 GROUP BY 1;
 exists | sum 
--------+-----
 t      |   6
(1 row)

-- Check with the IMPORT FOREIGN SCHEMA command.  Also, check ENUM types with
-- the IMPORT FOREIGN SCHEMA command. If the enum name is the same for multiple
-- tables, then it should handle correctly by prefixing the table name.
CREATE TYPE enum_t1_size_t AS enum('small', 'medium', 'large');
CREATE TYPE enum_t2_size_t AS enum('S', 'M', 'L');
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (enum_t1, enum_t2)
  FROM SERVER mysql_svr INTO public;
NOTICE:  error while generating the table definition
HINT:  If you encounter an error, you may need to execute the following first:
DO $$BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type WHERE typname = 'enum_t1_size_t') THEN CREATE TYPE enum_t1_size_t AS enum('small','medium','large'); END IF; END$$;

NOTICE:  error while generating the table definition
HINT:  If you encounter an error, you may need to execute the following first:
DO $$BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type WHERE typname = 'enum_t2_size_t') THEN CREATE TYPE enum_t2_size_t AS enum('S','M','L'); END IF; END$$;

SELECT attrelid::regclass, atttypid::regtype FROM pg_attribute
  WHERE (attrelid = 'enum_t1'::regclass OR attrelid = 'enum_t2'::regclass) AND
    attnum > 1 ORDER BY 1;
 attrelid |    atttypid    
----------+----------------
 enum_t1  | enum_t1_size_t
 enum_t2  | enum_t2_size_t
(2 rows)

SELECT * FROM enum_t1 ORDER BY id;
 id |  size  
----+--------
  1 | small
  2 | medium
  3 | medium
(3 rows)

SELECT * FROM enum_t2 ORDER BY id;
 id | size 
----+------
 10 | S
 20 | M
 30 | M
(3 rows)

DROP FOREIGN TABLE enum_t1;
DROP FOREIGN TABLE enum_t2;
-- FDW-217: IMPORT FOREIGN SCHEMA command with 'import_enum_as_text' option as
-- true should map MySQL ENUM type to TEXT type in PG.
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (enum_t1, enum_t2)
  FROM SERVER mysql_svr INTO public OPTIONS (import_enum_as_text 'true');
SELECT attrelid::regclass, atttypid::regtype FROM pg_attribute
  WHERE (attrelid = 'enum_t1'::regclass OR attrelid = 'enum_t2'::regclass) AND
    attnum > 1 ORDER BY 1;
 attrelid | atttypid 
----------+----------
 enum_t1  | text
 enum_t2  | text
(2 rows)

SELECT * FROM enum_t1 ORDER BY id;
 id |  size  
----+--------
  1 | small
  2 | medium
  3 | medium
(3 rows)

SELECT * FROM enum_t2 ORDER BY id;
 id | size 
----+------
 10 | S
 20 | M
 30 | M
(3 rows)

DROP FOREIGN TABLE enum_t1;
DROP FOREIGN TABLE enum_t2;
-- FDW-248: IMPORT FOREIGN SCHEMA command should work correctly if called
-- multiple times. Earlier we wrongly used PG_TRY/CATCH block to get the server
-- options without clearing the error state and that exceeded
-- ERRORDATA_STACK_SIZE hard coded to 5.
DO
$DO$
DECLARE
  i int;
BEGIN
  FOR i IN 1..5
  LOOP
    IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (mysql_test)
    FROM SERVER mysql_svr INTO public;

    DROP FOREIGN TABLE mysql_test;
  END LOOP;
END;
$DO$;
-- Parameterized queries should work correctly.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1, c2 FROM f_test_tbl1
  WHERE c8 = (SELECT c1 FROM f_test_tbl2 WHERE c1 = (SELECT 20))
  ORDER BY c1;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: f_test_tbl1.c1, f_test_tbl1.c2
   Remote query: SELECT `c1`, `c2` FROM `mysql_fdw_regress`.`test_tbl1` WHERE ((`c8` = ?)) ORDER BY `c1` IS NULL, `c1` ASC
   InitPlan 2 (returns $1)
     ->  Foreign Scan on public.f_test_tbl2
           Output: f_test_tbl2.c1
           Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test_tbl2` WHERE ((`c1` = ?))
           InitPlan 1 (returns $0)
             ->  Result
                   Output: 20
(10 rows)

SELECT c1, c2 FROM f_test_tbl1
  WHERE c8 = (SELECT c1 FROM f_test_tbl2 WHERE c1 = (SELECT 20))
  ORDER BY c1;
  c1  |  c2   
------+-------
  100 | EMP1
  400 | EMP4
  800 | EMP8
 1100 | EMP11
 1300 | EMP13
(5 rows)

SELECT * FROM f_test_tbl1
  WHERE c8 NOT IN (SELECT c1 FROM f_test_tbl2 WHERE c1 = (SELECT 20))
  ORDER BY c1;
  c1  |  c2   |    c3     | c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+-----+------------+------------+------+----
  200 | EMP2  | SALESMAN  | 600 | 1981-02-20 | 1600.00000 |  300 | 30
  300 | EMP3  | SALESMAN  | 600 | 1981-02-22 | 1250.00000 |  500 | 30
  500 | EMP5  | SALESMAN  | 600 | 1981-09-28 | 1250.00000 | 1400 | 30
  600 | EMP6  | MANAGER   | 900 | 1981-05-01 | 2850.00000 |      | 30
  700 | EMP7  | MANAGER   | 900 | 1981-06-09 | 2450.45000 |      | 10
  900 | EMP9  | HEAD      |     | 1981-11-17 | 5000.00000 |      | 10
 1000 | EMP10 | SALESMAN  | 600 | 1980-09-08 | 1500.00000 |    0 | 30
 1200 | EMP12 | ADMIN     | 600 | 1981-12-03 |  950.00000 |      | 30
 1400 | EMP14 | ADMIN     | 700 | 1982-01-23 | 1300.00000 |      | 10
(9 rows)

-- Check parameterized queries with text/varchar column, should not crash.
CREATE FOREIGN TABLE f_test_tbl3 (c1 INTEGER, c2 text, c3 text)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test_tbl2');
CREATE TABLE local_t1 (c1 INTEGER, c2 text);
INSERT INTO local_t1 VALUES (1, 'SALES');
SELECT c1, c2 FROM f_test_tbl3 WHERE c3 = (SELECT 'PUNE'::text) ORDER BY c1;
 c1 |     c2      
----+-------------
 10 | DEVELOPMENT
(1 row)

SELECT c1, c2 FROM f_test_tbl2 WHERE c3 = (SELECT 'PUNE'::varchar) ORDER BY c1;
 c1 |     c2      
----+-------------
 10 | DEVELOPMENT
(1 row)

SELECT * FROM local_t1 lt1 WHERE lt1.c1 =
  (SELECT count(*) FROM f_test_tbl3 ft1 WHERE ft1.c2 = lt1.c2) ORDER BY lt1.c1;
 c1 |  c2   
----+-------
  1 | SALES
(1 row)

SELECT c1, c2 FROM f_test_tbl1 WHERE c8 = (
  SELECT c1 FROM f_test_tbl2 WHERE c1 = (
    SELECT min(c1) + 1 FROM f_test_tbl2)) ORDER BY c1;
 c1 | c2 
----+----
(0 rows)

SELECT * FROM f_test_tbl1 WHERE c1 = (SELECT 500) AND c2 = (
  SELECT max(c2) FROM f_test_tbl1 WHERE c4 = (SELECT 600))
  ORDER BY 1, 2;
 c1  |  c2  |    c3     | c4  |     c5     |     c6     |  c7  | c8 
-----+------+-----------+-----+------------+------------+------+----
 500 | EMP5 | SALESMAN  | 600 | 1981-09-28 | 1250.00000 | 1400 | 30
(1 row)

SELECT t1.c1, (SELECT c2 FROM f_test_tbl1 WHERE c1 =(SELECT 500))
  FROM f_test_tbl2 t1, (
    SELECT c1, c2 FROM f_test_tbl2 WHERE c1 > ANY (SELECT 20)) t2
  ORDER BY 1, 2;
 c1 |  c2  
----+------
 10 | EMP5
 10 | EMP5
 20 | EMP5
 20 | EMP5
 30 | EMP5
 30 | EMP5
 40 | EMP5
 40 | EMP5
(8 rows)

-- FDW-255: Should throw an error when we select system attribute.
SELECT xmin FROM f_test_tbl1;
ERROR:  system attribute "xmin" can't be fetched from remote relation
SELECT ctid, xmax, tableoid FROM f_test_tbl1;
ERROR:  system attribute "ctid" can't be fetched from remote relation
SELECT xmax, c1 FROM f_test_tbl1;
ERROR:  system attribute "xmax" can't be fetched from remote relation
SELECT count(tableoid) FROM f_test_tbl1;
ERROR:  system attribute "tableoid" can't be fetched from remote relation
-- FDW-333: MySQL BINARY and VARBINARY data type should map to BYTEA in
-- Postgres while importing the schema.
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO ("test5")
  FROM SERVER mysql_svr INTO public;
SELECT attrelid::regclass, atttypid::regtype FROM pg_attribute
  WHERE attrelid = 'test5'::regclass AND attnum > 1 ORDER BY 1;
 attrelid | atttypid 
----------+----------
 test5    | bytea
 test5    | bytea
 test5    | bytea
 test5    | bytea
 test5    | bytea
 test5    | bytea
 test5    | bytea
 test5    | bytea
 test5    | bytea
(9 rows)

SELECT * FROM test5 ORDER BY 1;
 c1 |  c2  |    c3    |  c4  |           c5           |   c6   |  c7  |           c8           | c9 | c10 
----+------+----------+------+------------------------+--------+------+------------------------+----+-----
  1 | \x63 | \x633363 | \x74 | \x63356335633500000000 | \x3034 | \x31 | \x30312d31302d32303231 |    | \x
(1 row)

-- Test Mapping of MySQL BINARY and VARBINARY data type with various
-- Postgres data types.
SELECT * FROM test5_1 ORDER BY 1;
 c1 | c2 | c3  | c4 |   c5   |    c6    |  c7  |     c8     | c9 | c10 
----+----+-----+----+--------+----------+------+------------+----+-----
  1 | c  | c3c | t  | c5c5c5 | @ 4 secs | \x31 | 2021-01-10 |    | 
(1 row)

SELECT * FROM test5_1 WHERE c9 IS NULL ORDER BY 1;
 c1 | c2 | c3  | c4 |   c5   |    c6    |  c7  |     c8     | c9 | c10 
----+----+-----+----+--------+----------+------+------------+----+-----
  1 | c  | c3c | t  | c5c5c5 | @ 4 secs | \x31 | 2021-01-10 |    | 
(1 row)

SELECT * FROM test5_1 WHERE c10 IS NULL ORDER BY 1;
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 
----+----+----+----+----+----+----+----+----+-----
(0 rows)

-- Test MYSQL BINARY(n) and VARBINARY(n) variants mapping to Postgres BYTEA.
SELECT * FROM test5_2 ORDER BY 1;
 c1 |  c2  |    c3    |  c4  |           c5           |   c6   |  c7  |           c8           | c9 | c10 
----+------+----------+------+------------------------+--------+------+------------------------+----+-----
  1 | \x63 | \x633363 | \x74 | \x63356335633500000000 | \x3034 | \x31 | \x30312d31302d32303231 |    | \x
(1 row)

SELECT * FROM test5_2 WHERE c9 IS NULL ORDER BY 1;
 c1 |  c2  |    c3    |  c4  |           c5           |   c6   |  c7  |           c8           | c9 | c10 
----+------+----------+------+------------------------+--------+------+------------------------+----+-----
  1 | \x63 | \x633363 | \x74 | \x63356335633500000000 | \x3034 | \x31 | \x30312d31302d32303231 |    | \x
(1 row)

SELECT * FROM test5_2 WHERE c10 IS NULL ORDER BY 1;
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 
----+----+----+----+----+----+----+----+----+-----
(0 rows)

-- FDW-400: Test the parameterized query by enabling use_remote_estimate
-- option.
ALTER SERVER mysql_svr options (SET use_remote_estimate 'true');
SELECT c1, sum(c7) FROM f_test_tbl1 t1
  GROUP BY c1 HAVING EXISTS
    (SELECT 1 FROM f_test_tbl1 t2 WHERE (t1.c1 = t2.c1))
  ORDER BY 1,2;
  c1  | sum  
------+------
  100 |     
  200 |  300
  300 |  500
  400 |     
  500 | 1400
  600 |     
  700 |     
  800 |     
  900 |     
 1000 |    0
 1100 |     
 1200 |     
 1300 |     
 1400 |     
(14 rows)

ALTER SERVER mysql_svr options (SET use_remote_estimate 'false');
-- FDW-411: Volatile/immutable functions should not get pushed down to remote
-- MySQL server.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1, c2, c3 FROM f_test_tbl1 WHERE pg_catalog.timeofday() IS NOT NULL
  ORDER BY 1 limit 5;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Limit
   Output: c1, c2, c3
   ->  Foreign Scan on public.f_test_tbl1
         Output: c1, c2, c3
         Filter: (timeofday() IS NOT NULL)
         Remote query: SELECT `c1`, `c2`, `c3` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c1` IS NULL, `c1` ASC
(6 rows)

SELECT c1, c2, c3 FROM f_test_tbl1 WHERE pg_catalog.timeofday() IS NOT NULL
  ORDER BY 1 limit 5;
 c1  |  c2  |    c3     
-----+------+-----------
 100 | EMP1 | ADMIN    
 200 | EMP2 | SALESMAN 
 300 | EMP3 | SALESMAN 
 400 | EMP4 | MANAGER  
 500 | EMP5 | SALESMAN 
(5 rows)

-- FDW-447: Fix function implicit/explicit coercion.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 WHERE c1 = 12.2;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE ((`c1` = 12.2))
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 WHERE c1::numeric = 12.2;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE ((`c1` = 12.2))
(3 rows)

-- FDW-408: Skip importing relations that have SET type because Postgres
-- doesn't have equivalent datatype which can be mapped to MySQL SET.
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (test_set, mysql_test, test_tbl1)
  FROM SERVER mysql_svr INTO public;
WARNING:  skipping import for relation "test_set"
DETAIL:  MySQL SET columns are not supported.
SELECT relname FROM pg_class
  WHERE relname IN ('test_set', 'mysql_test', 'test_tbl1') AND relnamespace = 'public'::regnamespace;
  relname   
------------
 mysql_test
 test_tbl1
(2 rows)

-- FDW-417: Updating a NOT NULL column with NULL should throw an error
-- if we set sql_mode to STRICT_ALL_TABLES.
ALTER SERVER mysql_svr OPTIONS (sql_mode 'ANSI_QUOTES,STRICT_ALL_TABLES');
UPDATE f_mysql_test SET b = NULL WHERE a = 1;
ERROR:  failed to execute the MySQL query: 
Column 'b' cannot be null
SELECT * FROM f_mysql_test ORDER BY 1;
 a | b 
---+---
 1 | 1
(1 row)

ALTER SERVER mysql_svr OPTIONS (DROP sql_mode);
UPDATE f_mysql_test SET b = NULL WHERE a = 1;
SELECT * FROM f_mysql_test ORDER BY 1;
 a | b 
---+---
 1 | 0
(1 row)

UPDATE f_mysql_test SET b = 1 WHERE a = 1;
-- We should get a run-time error when sql_mode is set to invalid value.
ALTER SERVER mysql_svr OPTIONS (sql_mode 'ABCD');
SELECT * FROM f_mysql_test ORDER BY 1;
ERROR:  failed to execute the MySQL query: 
Variable 'sql_mode' can't be set to the value of 'ABCD'
ALTER SERVER mysql_svr OPTIONS (DROP sql_mode);
-- FDW-426: The numeric value should display correctly per precision and scale
-- defined.
CREATE FOREIGN TABLE f_test6(c1 numeric(6,2))
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test6');
SELECT * FROM f_test6 ORDER BY 1;
  c1   
-------
 25.25
(1 row)

-- Number with the required precision.
DROP FOREIGN TABLE f_test6;
CREATE FOREIGN TABLE f_test6(c1 numeric(6,4))
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test6');
SELECT * FROM f_test6 ORDER BY 1;
   c1    
---------
 25.2525
(1 row)

-- Number only with precision.
DROP FOREIGN TABLE f_test6;
CREATE FOREIGN TABLE f_test6(c1 numeric(6))
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test6');
SELECT * FROM f_test6 ORDER BY 1;
 c1 
----
 25
(1 row)

-- Number with improper precision and scale, should throw an error.
DROP FOREIGN TABLE f_test6;
CREATE FOREIGN TABLE f_test6(c1 numeric(3,2))
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test6');
SELECT * FROM f_test6 ORDER BY 1;
ERROR:  numeric field overflow
DETAIL:  A field with precision 3, scale 2 must round to an absolute value less than 10^1.
-- FDW-156: Long string data in column greater than MAXDATALEN length should
-- be fetched correctly.
CREATE FOREIGN TABLE f_test7(c1 int, c2 text)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test7');
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1, length(c2) FROM f_test7 ORDER BY 1;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test7
   Output: c1, length(c2)
   Remote query: SELECT `c1`, `c2` FROM `mysql_fdw_regress`.`test7` ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, length(c2) FROM f_test7 ORDER BY 1;
 c1 | length 
----+--------
  1 |  67500
(1 row)

-- FDW-130: ORDER BY with collation clause, should not get pushdown.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c2 COLLATE "en_US";
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Sort
   Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c2)::character varying(10))
   Sort Key: f_test_tbl1.c2 COLLATE "en_US"
   ->  Foreign Scan on public.f_test_tbl1
         Output: c1, c2, c3, c4, c5, c6, c7, c8, c2
         Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1`
(6 rows)

SELECT * FROM f_test_tbl1 ORDER BY c2 COLLATE "en_US";
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
(14 rows)

-- Order by desc
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c1 DESC;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c1` IS NOT NULL, `c1` DESC
(3 rows)

SELECT * FROM f_test_tbl1 ORDER BY c1 DESC;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
(14 rows)

-- Order by is not null
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;
                                                                                           QUERY PLAN                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: c1, c2, c3, c4, c5, c6, c7, c8, (c7 IS NOT NULL)
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY (`c7` IS NOT NULL) IS NULL, (`c7` IS NOT NULL) ASC, `c1` IS NULL, `c1` ASC
(3 rows)

SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
(14 rows)

-- Order by is not null desc
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;
                                                                                             QUERY PLAN                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: c1, c2, c3, c4, c5, c6, c7, c8, (c7 IS NOT NULL)
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY (`c7` IS NOT NULL) IS NOT NULL, (`c7` IS NOT NULL) DESC, `c1` IS NULL, `c1` ASC
(3 rows)

SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
(14 rows)

-- Order by desc nulls first
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;
                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c7` IS NOT NULL, `c7` DESC, `c1` IS NULL, `c1` ASC
(3 rows)

SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
(14 rows)

-- Order by asc nulls last
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c7` IS NULL, `c7` DESC, `c1` IS NULL, `c1` ASC
(3 rows)

SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;
  c1  |  c2   |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
------+-------+-----------+------+------------+------------+------+----
  500 | EMP5  | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
  300 | EMP3  | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
  200 | EMP2  | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
 1000 | EMP10 | SALESMAN  |  600 | 1980-09-08 | 1500.00000 |    0 | 30
  100 | EMP1  | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
  400 | EMP4  | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
  600 | EMP6  | MANAGER   |  900 | 1981-05-01 | 2850.00000 |      | 30
  700 | EMP7  | MANAGER   |  900 | 1981-06-09 | 2450.45000 |      | 10
  800 | EMP8  | FINANCE   |  400 | 1987-04-19 | 3000.00000 |      | 20
  900 | EMP9  | HEAD      |      | 1981-11-17 | 5000.00000 |      | 10
 1100 | EMP11 | ADMIN     |  800 | 1987-05-23 | 1100.00000 |      | 20
 1200 | EMP12 | ADMIN     |  600 | 1981-12-03 |  950.00000 |      | 30
 1300 | EMP13 | FINANCE   |  400 | 1981-12-03 | 3000.00000 |      | 20
 1400 | EMP14 | ADMIN     |  700 | 1982-01-23 | 1300.00000 |      | 10
(14 rows)

-- Test LIMIT where ORDER BY is not pushed down due to unsafe pathkeys.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 t1 ORDER BY t1 LIMIT 5;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: c1, c2, c3, c4, c5, c6, c7, c8, t1.*
   ->  Sort
         Output: c1, c2, c3, c4, c5, c6, c7, c8, t1.*
         Sort Key: t1.*
         ->  Foreign Scan on public.f_test_tbl1 t1
               Output: c1, c2, c3, c4, c5, c6, c7, c8, t1.*
               Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1`
(8 rows)

SELECT * FROM f_test_tbl1 t1 ORDER BY t1 LIMIT 5;
 c1  |  c2  |    c3     |  c4  |     c5     |     c6     |  c7  | c8 
-----+------+-----------+------+------------+------------+------+----
 100 | EMP1 | ADMIN     | 1300 | 1980-12-17 |  800.23000 |      | 20
 200 | EMP2 | SALESMAN  |  600 | 1981-02-20 | 1600.00000 |  300 | 30
 300 | EMP3 | SALESMAN  |  600 | 1981-02-22 | 1250.00000 |  500 | 30
 400 | EMP4 | MANAGER   |  900 | 1981-04-02 | 2975.12000 |      | 20
 500 | EMP5 | SALESMAN  |  600 | 1981-09-28 | 1250.00000 | 1400 | 30
(5 rows)

-- FDW-322: Test mysql_fdw_display_pushdown_list() function.
SELECT count(*) FROM mysql_fdw_display_pushdown_list();
 count 
-------
   250
(1 row)

-- FDW:554 - Array expression should not get pushdown to remote MySQL server as
-- syntax is not supported.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 t1 WHERE c1 = ANY(ARRAY[100, 200, c1 + 10000]);
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c1 = ANY (ARRAY[100, 200, (t1.c1 + 10000)]))
   Remote query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_regress`.`test_tbl1`
(4 rows)

SELECT * FROM f_test_tbl1 t1 WHERE c1 = ANY(ARRAY[100, 200, c1 + 10000]);
 c1  |  c2  |    c3     |  c4  |     c5     |     c6     | c7  | c8 
-----+------+-----------+------+------------+------------+-----+----
 100 | EMP1 | ADMIN     | 1300 | 1980-12-17 |  800.23000 |     | 20
 200 | EMP2 | SALESMAN  |  600 | 1981-02-20 | 1600.00000 | 300 | 30
(2 rows)

-- FDW:668 - MySQL's timestamp datatype should be mapped to PG's timestamptz.
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (timestamp_test)
  FROM SERVER mysql_svr INTO public;
SELECT attname, atttypid::regtype FROM pg_attribute
  WHERE attrelid = 'timestamp_test'::regclass AND attnum >= 1 ORDER BY 1;
 attname |         atttypid         
---------+--------------------------
 a       | integer
 b       | timestamp with time zone
(2 rows)

SET timezone = 'Asia/Kolkata';
INSERT INTO timestamp_test VALUES (1, '2020-01-01 00:00:00');
INSERT INTO timestamp_test VALUES (2, '2020-01-01 00:00:00 +0:00');
SET timezone = 'UTC';
INSERT INTO timestamp_test VALUES (3, '2020-01-01 00:00:00');
INSERT INTO timestamp_test VALUES (4, '2020-01-01 00:00:00 +5:30');
SELECT * FROM timestamp_test ORDER BY 1;
 a |           b            
---+------------------------
 1 | 2019-12-31 18:30:00+00
 2 | 2020-01-01 00:00:00+00
 3 | 2020-01-01 00:00:00+00
 4 | 2019-12-31 18:30:00+00
(4 rows)

SET timezone = 'Asia/Kolkata';
SELECT * FROM timestamp_test ORDER BY 1;
 a |             b             
---+---------------------------
 1 | 2019-12-31 18:30:00+05:30
 2 | 2020-01-01 00:00:00+05:30
 3 | 2020-01-01 00:00:00+05:30
 4 | 2019-12-31 18:30:00+05:30
(4 rows)

-- FDW:692 - Scalar array operation with operator = ANY/<> ALL should pushdown.
EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ANY ('{400, 4000}'::INT[])
  ORDER BY c1;
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c1` IN (400, 4000)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ANY ('{400, 4000}'::INT[])
  ORDER BY c1;
 c1  |  c2  |     c6     | c8 
-----+------+------------+----
 400 | EMP4 | 2975.12000 | 20
(1 row)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, NULL, 800}'::INT[])
  ORDER BY c1;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c4
   Remote query: SELECT `c1`, `c2`, `c4` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c4` IN (400, NULL, 800)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, NULL, 800}'::INT[])
  ORDER BY c1;
  c1  |  c2   | c4  
------+-------+-----
  800 | EMP8  | 400
 1100 | EMP11 | 800
 1300 | EMP13 | 400
(3 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, 800}'::INT[])
  ORDER BY c1;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c4
   Remote query: SELECT `c1`, `c2`, `c4` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c4` IN (400, 800)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, 800}'::INT[])
  ORDER BY c1;
  c1  |  c2   | c4  
------+-------+-----
  800 | EMP8  | 400
 1100 | EMP11 | 800
 1300 | EMP13 | 400
(3 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 != ALL ('{400, 4000}'::INT[])
  ORDER BY c1;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c1` NOT IN (400, 4000)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 != ALL ('{400, 4000}'::INT[])
  ORDER BY c1;
  c1  |  c2   |     c6     | c8 
------+-------+------------+----
  100 | EMP1  |  800.23000 | 20
  200 | EMP2  | 1600.00000 | 30
  300 | EMP3  | 1250.00000 | 30
  500 | EMP5  | 1250.00000 | 30
  600 | EMP6  | 2850.00000 | 30
  700 | EMP7  | 2450.45000 | 10
  800 | EMP8  | 3000.00000 | 20
  900 | EMP9  | 5000.00000 | 10
 1000 | EMP10 | 1500.00000 | 30
 1100 | EMP11 | 1100.00000 | 20
 1200 | EMP12 |  950.00000 | 30
 1300 | EMP13 | 3000.00000 | 20
 1400 | EMP14 | 1300.00000 | 10
(13 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, NULL, 800}'::INT[])
  ORDER BY c1;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c4
   Remote query: SELECT `c1`, `c2`, `c4` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c4` NOT IN (400, NULL, 800)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, NULL, 800}'::INT[])
  ORDER BY c1;
 c1 | c2 | c4 
----+----+----
(0 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, 800}'::INT[])
  ORDER BY c1;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c4
   Remote query: SELECT `c1`, `c2`, `c4` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c4` NOT IN (400, 800)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, 800}'::INT[])
  ORDER BY c1;
  c1  |  c2   |  c4  
------+-------+------
  100 | EMP1  | 1300
  200 | EMP2  |  600
  300 | EMP3  |  600
  400 | EMP4  |  900
  500 | EMP5  |  600
  600 | EMP6  |  900
  700 | EMP7  |  900
 1000 | EMP10 |  600
 1200 | EMP12 |  600
 1400 | EMP14 |  700
(10 rows)

-- Scalar array operation with other than operator =/<> should not pushdown.
EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 > ANY ('{400, 4000}'::INT[])
  ORDER BY c1;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Filter: (e.c1 > ANY ('{400,4000}'::integer[]))
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c1` IS NULL, `c1` ASC
(4 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 > ANY ('{400, 4000}'::INT[])
  ORDER BY c1;
  c1  |  c2   |     c6     | c8 
------+-------+------------+----
  500 | EMP5  | 1250.00000 | 30
  600 | EMP6  | 2850.00000 | 30
  700 | EMP7  | 2450.45000 | 10
  800 | EMP8  | 3000.00000 | 20
  900 | EMP9  | 5000.00000 | 10
 1000 | EMP10 | 1500.00000 | 30
 1100 | EMP11 | 1100.00000 | 20
 1200 | EMP12 |  950.00000 | 30
 1300 | EMP13 | 3000.00000 | 20
 1400 | EMP14 | 1300.00000 | 10
(10 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 < ANY (ARRAY[100, 200, 500])
  ORDER BY c1;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Filter: (e.c1 < ANY ('{100,200,500}'::integer[]))
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c1` IS NULL, `c1` ASC
(4 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 < ANY (ARRAY[100, 200, 500])
  ORDER BY c1;
 c1  |  c2  |     c6     | c8 
-----+------+------------+----
 100 | EMP1 |  800.23000 | 20
 200 | EMP2 | 1600.00000 | 30
 300 | EMP3 | 1250.00000 | 30
 400 | EMP4 | 2975.12000 | 20
(4 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 > ALL (ARRAY[100, 200, 500])
  ORDER BY c1;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Filter: (e.c1 > ALL ('{100,200,500}'::integer[]))
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c1` IS NULL, `c1` ASC
(4 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 > ALL (ARRAY[100, 200, 500])
  ORDER BY c1;
  c1  |  c2   |     c6     | c8 
------+-------+------------+----
  600 | EMP6  | 2850.00000 | 30
  700 | EMP7  | 2450.45000 | 10
  800 | EMP8  | 3000.00000 | 20
  900 | EMP9  | 5000.00000 | 10
 1000 | EMP10 | 1500.00000 | 30
 1100 | EMP11 | 1100.00000 | 20
 1200 | EMP12 |  950.00000 | 30
 1300 | EMP13 | 3000.00000 | 20
 1400 | EMP14 | 1300.00000 | 10
(9 rows)

-- Scalar array operation with = ALL/<> ANY should not pushdown.
EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 != ANY (ARRAY[100, 200, 500])
  ORDER BY c1;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Filter: (e.c1 <> ANY ('{100,200,500}'::integer[]))
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c1` IS NULL, `c1` ASC
(4 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 != ANY (ARRAY[100, 200, 500])
  ORDER BY c1;
  c1  |  c2   |     c6     | c8 
------+-------+------------+----
  100 | EMP1  |  800.23000 | 20
  200 | EMP2  | 1600.00000 | 30
  300 | EMP3  | 1250.00000 | 30
  400 | EMP4  | 2975.12000 | 20
  500 | EMP5  | 1250.00000 | 30
  600 | EMP6  | 2850.00000 | 30
  700 | EMP7  | 2450.45000 | 10
  800 | EMP8  | 3000.00000 | 20
  900 | EMP9  | 5000.00000 | 10
 1000 | EMP10 | 1500.00000 | 30
 1100 | EMP11 | 1100.00000 | 20
 1200 | EMP12 |  950.00000 | 30
 1300 | EMP13 | 3000.00000 | 20
 1400 | EMP14 | 1300.00000 | 10
(14 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ALL ('{400, 500}'::INT[])
  ORDER BY c1;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Filter: (e.c1 = ALL ('{400,500}'::integer[]))
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` ORDER BY `c1` IS NULL, `c1` ASC
(4 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ALL ('{400, 500}'::INT[])
  ORDER BY c1;
 c1 | c2 | c6 | c8 
----+----+----+----
(0 rows)

-- FDW-693: ScalarArrayOpExpr with multidimensional array should pushdown.
EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c2 = ANY('{{EMP2, EMP4}, {EMP3, EMP7}, {NULL, EMP8}}'::TEXT[])
  ORDER BY c1;
                                                                                    QUERY PLAN                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c2` IN ('EMP2', 'EMP4', 'EMP3', 'EMP7', NULL, 'EMP8')) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c2 = ANY('{{EMP2, EMP4}, {EMP3, EMP7}, {NULL, EMP8}}'::TEXT[])
  ORDER BY c1;
 c1  |  c2  |     c6     | c8 
-----+------+------------+----
 200 | EMP2 | 1600.00000 | 30
 300 | EMP3 | 1250.00000 | 30
 400 | EMP4 | 2975.12000 | 20
 700 | EMP7 | 2450.45000 | 10
 800 | EMP8 | 3000.00000 | 20
(5 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ANY('{{100, 400}, {300, 500}, {NULL, 4000}}'::INT[])
  ORDER BY c1;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c1` IN (100, 400, 300, 500, NULL, 4000)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ANY('{{100, 400}, {300, 500}, {NULL, 4000}}'::INT[])
  ORDER BY c1;
 c1  |  c2  |     c6     | c8 
-----+------+------------+----
 100 | EMP1 |  800.23000 | 20
 300 | EMP3 | 1250.00000 | 30
 400 | EMP4 | 2975.12000 | 20
 500 | EMP5 | 1250.00000 | 30
(4 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4, c6, c8 FROM f_test_tbl1 e
  WHERE c4 = ANY('{{100, 400}, {300, 500}, {NULL, 4000}}'::INT8[])
  ORDER BY c1;
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c4, c6, c8
   Remote query: SELECT `c1`, `c2`, `c4`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c4` IN (100, 400, 300, 500, NULL, 4000)) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c4, c6, c8 FROM f_test_tbl1 e
  WHERE c4 = ANY('{{100, 400}, {300, 600}, {NULL, 4000}}'::INT8[])
  ORDER BY c1;
  c1  |  c2   | c4  |     c6     | c8 
------+-------+-----+------------+----
  200 | EMP2  | 600 | 1600.00000 | 30
  300 | EMP3  | 600 | 1250.00000 | 30
  500 | EMP5  | 600 | 1250.00000 | 30
  800 | EMP8  | 400 | 3000.00000 | 20
 1000 | EMP10 | 600 | 1500.00000 | 30
 1200 | EMP12 | 600 |  950.00000 | 30
 1300 | EMP13 | 400 | 3000.00000 | 20
(7 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4, c6, c8 FROM f_test_tbl1 e
  WHERE c6 = ANY('{{100, 400}, {300, 2850.00000}, {NULL, 1250.00000}}'::NUMERIC[])
  ORDER BY c1;
                                                                                            QUERY PLAN                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c4, c6, c8
   Remote query: SELECT `c1`, `c2`, `c4`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c6` IN ('100', '400', '300', '2850.00000', NULL, '1250.00000')) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c4, c6, c8 FROM f_test_tbl1 e
  WHERE c6 = ANY('{{100, 400}, {300, 2850.00000}, {NULL, 1250.00000}}'::NUMERIC[])
  ORDER BY c1;
 c1  |  c2  | c4  |     c6     | c8 
-----+------+-----+------------+----
 300 | EMP3 | 600 | 1250.00000 | 30
 500 | EMP5 | 600 | 1250.00000 | 30
 600 | EMP6 | 900 | 2850.00000 | 30
(3 rows)

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c6 = ANY (ARRAY[400, 2850.00000])
  ORDER BY c1;
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl1 e
   Output: c1, c2, c6, c8
   Remote query: SELECT `c1`, `c2`, `c6`, `c8` FROM `mysql_fdw_regress`.`test_tbl1` WHERE (`c6` IN ('400', '2850.00000')) ORDER BY `c1` IS NULL, `c1` ASC
(3 rows)

SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c6 = ANY (ARRAY[400, 2850.00000])
  ORDER BY c1;
 c1  |  c2  |     c6     | c8 
-----+------+------------+----
 600 | EMP6 | 2850.00000 | 30
(1 row)

-- Cleanup
DROP TABLE l_test_tbl1;
DROP TABLE l_test_tbl2;
DROP TABLE local_t1;
DROP VIEW smpl_vw;
DROP VIEW comp_vw;
DROP VIEW ttest_tbl1_vw;
DROP VIEW mul_tbl_view;
DELETE FROM f_test_tbl1;
DELETE FROM f_test_tbl2;
DELETE FROM f_numbers;
DELETE FROM f_enum_t1;
DELETE FROM timestamp_test;
DROP FOREIGN TABLE f_test_tbl1;
DROP FOREIGN TABLE f_test_tbl2;
DROP FOREIGN TABLE f_numbers;
DROP FOREIGN TABLE f_mysql_test;
DROP FOREIGN TABLE f_enum_t1;
DROP FOREIGN TABLE f_test_tbl3;
DROP FOREIGN TABLE test5;
DROP FOREIGN TABLE test5_1;
DROP FOREIGN TABLE test5_2;
DROP FOREIGN TABLE mysql_test;
DROP FOREIGN TABLE test_tbl1;
DROP FOREIGN TABLE f_test6;
DROP FOREIGN TABLE f_test7;
DROP FOREIGN TABLE timestamp_test;
DROP TYPE size_t;
DROP TYPE enum_t1_size_t;
DROP TYPE enum_t2_size_t;
DROP FUNCTION test_param_where();
DROP FUNCTION test_param_where2(int, text);
DROP USER MAPPING FOR public SERVER mysql_svr;
DROP SERVER mysql_svr;
DROP EXTENSION mysql_fdw;