File: select.sql

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 (715 lines) | stat: -rw-r--r-- 28,633 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
\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();

-- 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;
SELECT DISTINCT c8 FROM f_test_tbl1 ORDER BY 1;
SELECT c2 AS "Employee Name" FROM f_test_tbl1 ORDER BY 1;
SELECT c8, c6, c7 FROM f_test_tbl1 ORDER BY 1, 2, 3;
SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1
  WHERE c1 = 100 ORDER BY 1;
SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM f_test_tbl1
  WHERE c1 = 100 OR c1 = 700 ORDER BY 1;
SELECT * FROM f_test_tbl1 WHERE c3 like 'SALESMAN' ORDER BY 1;
SELECT * FROM f_test_tbl1 WHERE c1 IN (100, 700) ORDER BY 1;
SELECT * FROM f_test_tbl1 WHERE c1 NOT IN (100, 700) ORDER BY 1 LIMIT 5;
SELECT * FROM f_test_tbl1 WHERE c8 BETWEEN 10 AND 20 ORDER BY 1;
SELECT * FROM f_test_tbl1 ORDER BY 1 OFFSET 5;

-- 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;
SELECT c8, SUM(c6) FROM f_test_tbl1
  GROUP BY c8 HAVING c8 IN (10, 30) ORDER BY c8;
SELECT c8, SUM(c6) FROM f_test_tbl1
  GROUP BY c8 HAVING SUM(c6) > 9400 ORDER BY c8;

-- Row Level Functions
SELECT UPPER(c2), LOWER(c2) FROM f_test_tbl2 ORDER BY 1, 2;

-- 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;
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;
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;

-- 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;

SELECT c2 FROM f_test_tbl2  UNION ALL
SELECT c2 FROM f_test_tbl1 ORDER BY c2;

-- 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;

SELECT c2 FROM f_test_tbl1 WHERE c1 >= 800 INTERSECT ALL
SELECT c2 FROM f_test_tbl1 WHERE c1 >= 400 ORDER BY c2;

-- 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;

SELECT c2 FROM f_test_tbl1 EXCEPT ALL
SELECT c2 FROM f_test_tbl1 WHERE c1 > 900 ORDER BY c2;

-- 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;

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;

-- 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;
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;
SELECT c8, c1, c6, SUM(c6) OVER (PARTITION BY c8) FROM f_test_tbl1
  ORDER BY c8, c1;

-- 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;

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;

CREATE TEMPORARY VIEW ttest_tbl1_vw AS
  SELECT c1, c2, c3 FROM f_test_tbl2;
SELECT * FROM ttest_tbl1_vw ORDER BY 1, 2;

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;

-- 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();

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');

-- 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;
-- 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;
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;
-- 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;
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;
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;

-- 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;
-- 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;
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;
-- 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;
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;
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;

-- 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;
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;
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;
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;
SELECT *, (SELECT r FROM (SELECT c1 AS c1) x, LATERAL (SELECT c1 AS r) y)
  FROM f_test_tbl1 ORDER BY 1, 2, 3;
-- 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;

-- 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;

-- 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;
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;
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;
UPDATE f_enum_t1 SET size = 'large' WHERE id = 4;
SELECT * FROM f_enum_t1 WHERE id = 4;
DELETE FROM f_enum_t1 WHERE size = 'large';
SELECT * FROM f_enum_t1 WHERE id = 4;

-- 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;
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');

-- 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;

-- 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;
SELECT EXISTS(SELECT 1 FROM pg_enum), sum(id) from f_enum_t1 GROUP BY 1;

-- 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;
SELECT attrelid::regclass, atttypid::regtype FROM pg_attribute
  WHERE (attrelid = 'enum_t1'::regclass OR attrelid = 'enum_t2'::regclass) AND
    attnum > 1 ORDER BY 1;
SELECT * FROM enum_t1 ORDER BY id;
SELECT * FROM enum_t2 ORDER BY id;
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;
SELECT * FROM enum_t1 ORDER BY id;
SELECT * FROM enum_t2 ORDER BY id;
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;
SELECT c1, c2 FROM f_test_tbl1
  WHERE c8 = (SELECT c1 FROM f_test_tbl2 WHERE c1 = (SELECT 20))
  ORDER BY c1;

SELECT * FROM f_test_tbl1
  WHERE c8 NOT IN (SELECT c1 FROM f_test_tbl2 WHERE c1 = (SELECT 20))
  ORDER BY c1;

-- 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;
SELECT c1, c2 FROM f_test_tbl2 WHERE c3 = (SELECT 'PUNE'::varchar) ORDER BY c1;

SELECT * FROM local_t1 lt1 WHERE lt1.c1 =
  (SELECT count(*) FROM f_test_tbl3 ft1 WHERE ft1.c2 = lt1.c2) ORDER BY lt1.c1;

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;

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;
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;

-- FDW-255: Should throw an error when we select system attribute.
SELECT xmin FROM f_test_tbl1;
SELECT ctid, xmax, tableoid FROM f_test_tbl1;
SELECT xmax, c1 FROM f_test_tbl1;
SELECT count(tableoid) FROM f_test_tbl1;

-- 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;
SELECT * FROM test5 ORDER BY 1;
-- Test Mapping of MySQL BINARY and VARBINARY data type with various
-- Postgres data types.
SELECT * FROM test5_1 ORDER BY 1;
SELECT * FROM test5_1 WHERE c9 IS NULL ORDER BY 1;
SELECT * FROM test5_1 WHERE c10 IS NULL ORDER BY 1;
-- Test MYSQL BINARY(n) and VARBINARY(n) variants mapping to Postgres BYTEA.
SELECT * FROM test5_2 ORDER BY 1;
SELECT * FROM test5_2 WHERE c9 IS NULL ORDER BY 1;
SELECT * FROM test5_2 WHERE c10 IS NULL ORDER BY 1;

-- 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;
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;
SELECT c1, c2, c3 FROM f_test_tbl1 WHERE pg_catalog.timeofday() IS NOT NULL
  ORDER BY 1 limit 5;

-- FDW-447: Fix function implicit/explicit coercion.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 WHERE c1 = 12.2;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 WHERE c1::numeric = 12.2;

-- 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;
SELECT relname FROM pg_class
  WHERE relname IN ('test_set', 'mysql_test', 'test_tbl1') AND relnamespace = 'public'::regnamespace;

-- 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;
SELECT * FROM f_mysql_test ORDER BY 1;
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;
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;
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;

-- 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;

-- 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;

-- 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;

-- 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;
SELECT c1, length(c2) FROM f_test7 ORDER BY 1;

-- 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";
SELECT * FROM f_test_tbl1 ORDER BY c2 COLLATE "en_US";

-- Order by desc
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c1 DESC;
SELECT * FROM f_test_tbl1 ORDER BY c1 DESC;

-- Order by is not null
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;
SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL, c1;

-- Order by is not null desc
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;
SELECT * FROM f_test_tbl1 ORDER BY c7 IS NOT NULL DESC, c1;

-- Order by desc nulls first
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;
SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS FIRST, c1;

-- Order by asc nulls last
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;
SELECT * FROM f_test_tbl1 ORDER BY c7 DESC NULLS LAST, c1;

-- 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;
SELECT * FROM f_test_tbl1 t1 ORDER BY t1 LIMIT 5;

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

-- 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]);
SELECT * FROM f_test_tbl1 t1 WHERE c1 = ANY(ARRAY[100, 200, c1 + 10000]);

-- 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;

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;
SET timezone = 'Asia/Kolkata';
SELECT * FROM timestamp_test ORDER BY 1;


-- 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;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ANY ('{400, 4000}'::INT[])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, NULL, 800}'::INT[])
  ORDER BY c1;
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, NULL, 800}'::INT[])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, 800}'::INT[])
  ORDER BY c1;
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 = ANY ('{400, 800}'::INT[])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 != ALL ('{400, 4000}'::INT[])
  ORDER BY c1;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 != ALL ('{400, 4000}'::INT[])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, NULL, 800}'::INT[])
  ORDER BY c1;
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, NULL, 800}'::INT[])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, 800}'::INT[])
  ORDER BY c1;
SELECT c1, c2, c4 FROM f_test_tbl1 e
  WHERE c4 != ALL ('{400, 800}'::INT[])
  ORDER BY c1;

-- 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;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 > ANY ('{400, 4000}'::INT[])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 < ANY (ARRAY[100, 200, 500])
  ORDER BY c1;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 < ANY (ARRAY[100, 200, 500])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 > ALL (ARRAY[100, 200, 500])
  ORDER BY c1;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 > ALL (ARRAY[100, 200, 500])
  ORDER BY c1;

-- 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;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 != ANY (ARRAY[100, 200, 500])
  ORDER BY c1;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ALL ('{400, 500}'::INT[])
  ORDER BY c1;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ALL ('{400, 500}'::INT[])
  ORDER BY c1;

-- 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;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c2 = ANY('{{EMP2, EMP4}, {EMP3, EMP7}, {NULL, EMP8}}'::TEXT[])
  ORDER BY c1;

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;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c1 = ANY('{{100, 400}, {300, 500}, {NULL, 4000}}'::INT[])
  ORDER BY c1;

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;
SELECT c1, c2, c4, c6, c8 FROM f_test_tbl1 e
  WHERE c4 = ANY('{{100, 400}, {300, 600}, {NULL, 4000}}'::INT8[])
  ORDER BY c1;

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;
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;

EXPLAIN (VERBOSE, COSTS FALSE)
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c6 = ANY (ARRAY[400, 2850.00000])
  ORDER BY c1;
SELECT c1, c2, c6, c8 FROM f_test_tbl1 e
  WHERE c6 = ANY (ARRAY[400, 2850.00000])
  ORDER BY c1;

-- 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;