Skip to content

Commit 3a90ad9

Browse files
funbringerfunbringer
committed
more tests for stupid cases with arrays
1 parent 20fae01 commit 3a90ad9

File tree

2 files changed

+93
-102
lines changed

2 files changed

+93
-102
lines changed

‎expected/pathman_array_qual.out

Lines changed: 87 additions & 102 deletions
Original file line numberDiff line numberDiff line change
@@ -344,30 +344,18 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE b IN (NULL, NULL, NULL,
344344
*/
345345
/* a = ANY (...) - pruning should work */
346346
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (NULL);
347-
QUERY PLAN
348-
---------------------------------------------
349-
Append
350-
-> Seq Scan on test_1
351-
Filter: (a = ANY (NULL::integer[]))
352-
-> Seq Scan on test_2
353-
Filter: (a = ANY (NULL::integer[]))
354-
-> Seq Scan on test_3
355-
Filter: (a = ANY (NULL::integer[]))
356-
-> Seq Scan on test_4
357-
Filter: (a = ANY (NULL::integer[]))
358-
-> Seq Scan on test_5
359-
Filter: (a = ANY (NULL::integer[]))
360-
-> Seq Scan on test_6
361-
Filter: (a = ANY (NULL::integer[]))
362-
-> Seq Scan on test_7
363-
Filter: (a = ANY (NULL::integer[]))
364-
-> Seq Scan on test_8
365-
Filter: (a = ANY (NULL::integer[]))
366-
-> Seq Scan on test_9
367-
Filter: (a = ANY (NULL::integer[]))
368-
-> Seq Scan on test_10
369-
Filter: (a = ANY (NULL::integer[]))
370-
(21 rows)
347+
QUERY PLAN
348+
--------------------------
349+
Result
350+
One-Time Filter: false
351+
(2 rows)
352+
353+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[]::int4[]);
354+
QUERY PLAN
355+
--------------------------
356+
Result
357+
One-Time Filter: false
358+
(2 rows)
371359

372360
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[100, 100]);
373361
QUERY PLAN
@@ -436,29 +424,36 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100
436424
*/
437425
/* a = ALL (...) - pruning should work */
438426
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (NULL);
427+
QUERY PLAN
428+
--------------------------
429+
Result
430+
One-Time Filter: false
431+
(2 rows)
432+
433+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[]::int4[]);
439434
QUERY PLAN
440435
---------------------------------------------
441436
Append
442437
-> Seq Scan on test_1
443-
Filter: (a = ALL (NULL::integer[]))
438+
Filter: (a = ALL ('{}'::integer[]))
444439
-> Seq Scan on test_2
445-
Filter: (a = ALL (NULL::integer[]))
440+
Filter: (a = ALL ('{}'::integer[]))
446441
-> Seq Scan on test_3
447-
Filter: (a = ALL (NULL::integer[]))
442+
Filter: (a = ALL ('{}'::integer[]))
448443
-> Seq Scan on test_4
449-
Filter: (a = ALL (NULL::integer[]))
444+
Filter: (a = ALL ('{}'::integer[]))
450445
-> Seq Scan on test_5
451-
Filter: (a = ALL (NULL::integer[]))
446+
Filter: (a = ALL ('{}'::integer[]))
452447
-> Seq Scan on test_6
453-
Filter: (a = ALL (NULL::integer[]))
448+
Filter: (a = ALL ('{}'::integer[]))
454449
-> Seq Scan on test_7
455-
Filter: (a = ALL (NULL::integer[]))
450+
Filter: (a = ALL ('{}'::integer[]))
456451
-> Seq Scan on test_8
457-
Filter: (a = ALL (NULL::integer[]))
452+
Filter: (a = ALL ('{}'::integer[]))
458453
-> Seq Scan on test_9
459-
Filter: (a = ALL (NULL::integer[]))
454+
Filter: (a = ALL ('{}'::integer[]))
460455
-> Seq Scan on test_10
461-
Filter: (a = ALL (NULL::integer[]))
456+
Filter: (a = ALL ('{}'::integer[]))
462457
(21 rows)
463458

464459
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[100, 100]);
@@ -509,30 +504,18 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[NULL, NUL
509504
*/
510505
/* a < ANY (...) - pruning should work */
511506
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (NULL);
512-
QUERY PLAN
513-
---------------------------------------------
514-
Append
515-
-> Seq Scan on test_1
516-
Filter: (a < ANY (NULL::integer[]))
517-
-> Seq Scan on test_2
518-
Filter: (a < ANY (NULL::integer[]))
519-
-> Seq Scan on test_3
520-
Filter: (a < ANY (NULL::integer[]))
521-
-> Seq Scan on test_4
522-
Filter: (a < ANY (NULL::integer[]))
523-
-> Seq Scan on test_5
524-
Filter: (a < ANY (NULL::integer[]))
525-
-> Seq Scan on test_6
526-
Filter: (a < ANY (NULL::integer[]))
527-
-> Seq Scan on test_7
528-
Filter: (a < ANY (NULL::integer[]))
529-
-> Seq Scan on test_8
530-
Filter: (a < ANY (NULL::integer[]))
531-
-> Seq Scan on test_9
532-
Filter: (a < ANY (NULL::integer[]))
533-
-> Seq Scan on test_10
534-
Filter: (a < ANY (NULL::integer[]))
535-
(21 rows)
507+
QUERY PLAN
508+
--------------------------
509+
Result
510+
One-Time Filter: false
511+
(2 rows)
512+
513+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[]::int4[]);
514+
QUERY PLAN
515+
--------------------------
516+
Result
517+
One-Time Filter: false
518+
(2 rows)
536519

537520
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[100, 100]);
538521
QUERY PLAN
@@ -611,29 +594,36 @@ SELECT count(*) FROM array_qual.test WHERE a < ANY (array[NULL, 700]);
611594
*/
612595
/* a < ALL (...) - pruning should work */
613596
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (NULL);
597+
QUERY PLAN
598+
--------------------------
599+
Result
600+
One-Time Filter: false
601+
(2 rows)
602+
603+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[]::int4[]);
614604
QUERY PLAN
615605
---------------------------------------------
616606
Append
617607
-> Seq Scan on test_1
618-
Filter: (a < ALL (NULL::integer[]))
608+
Filter: (a < ALL ('{}'::integer[]))
619609
-> Seq Scan on test_2
620-
Filter: (a < ALL (NULL::integer[]))
610+
Filter: (a < ALL ('{}'::integer[]))
621611
-> Seq Scan on test_3
622-
Filter: (a < ALL (NULL::integer[]))
612+
Filter: (a < ALL ('{}'::integer[]))
623613
-> Seq Scan on test_4
624-
Filter: (a < ALL (NULL::integer[]))
614+
Filter: (a < ALL ('{}'::integer[]))
625615
-> Seq Scan on test_5
626-
Filter: (a < ALL (NULL::integer[]))
616+
Filter: (a < ALL ('{}'::integer[]))
627617
-> Seq Scan on test_6
628-
Filter: (a < ALL (NULL::integer[]))
618+
Filter: (a < ALL ('{}'::integer[]))
629619
-> Seq Scan on test_7
630-
Filter: (a < ALL (NULL::integer[]))
620+
Filter: (a < ALL ('{}'::integer[]))
631621
-> Seq Scan on test_8
632-
Filter: (a < ALL (NULL::integer[]))
622+
Filter: (a < ALL ('{}'::integer[]))
633623
-> Seq Scan on test_9
634-
Filter: (a < ALL (NULL::integer[]))
624+
Filter: (a < ALL ('{}'::integer[]))
635625
-> Seq Scan on test_10
636-
Filter: (a < ALL (NULL::integer[]))
626+
Filter: (a < ALL ('{}'::integer[]))
637627
(21 rows)
638628

639629
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[100, 100]);
@@ -700,30 +690,18 @@ SELECT count(*) FROM array_qual.test WHERE a < ALL (array[NULL, 700]);
700690
*/
701691
/* a > ANY (...) - pruning should work */
702692
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (NULL);
703-
QUERY PLAN
704-
---------------------------------------------
705-
Append
706-
-> Seq Scan on test_1
707-
Filter: (a > ANY (NULL::integer[]))
708-
-> Seq Scan on test_2
709-
Filter: (a > ANY (NULL::integer[]))
710-
-> Seq Scan on test_3
711-
Filter: (a > ANY (NULL::integer[]))
712-
-> Seq Scan on test_4
713-
Filter: (a > ANY (NULL::integer[]))
714-
-> Seq Scan on test_5
715-
Filter: (a > ANY (NULL::integer[]))
716-
-> Seq Scan on test_6
717-
Filter: (a > ANY (NULL::integer[]))
718-
-> Seq Scan on test_7
719-
Filter: (a > ANY (NULL::integer[]))
720-
-> Seq Scan on test_8
721-
Filter: (a > ANY (NULL::integer[]))
722-
-> Seq Scan on test_9
723-
Filter: (a > ANY (NULL::integer[]))
724-
-> Seq Scan on test_10
725-
Filter: (a > ANY (NULL::integer[]))
726-
(21 rows)
693+
QUERY PLAN
694+
--------------------------
695+
Result
696+
One-Time Filter: false
697+
(2 rows)
698+
699+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[]::int4[]);
700+
QUERY PLAN
701+
--------------------------
702+
Result
703+
One-Time Filter: false
704+
(2 rows)
727705

728706
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[100, 100]);
729707
QUERY PLAN
@@ -821,29 +799,36 @@ SELECT count(*) FROM array_qual.test WHERE a > ANY (array[NULL, 700]);
821799
*/
822800
/* a > ALL (...) - pruning should work */
823801
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (NULL);
802+
QUERY PLAN
803+
--------------------------
804+
Result
805+
One-Time Filter: false
806+
(2 rows)
807+
808+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[]::int4[]);
824809
QUERY PLAN
825810
---------------------------------------------
826811
Append
827812
-> Seq Scan on test_1
828-
Filter: (a > ALL (NULL::integer[]))
813+
Filter: (a > ALL ('{}'::integer[]))
829814
-> Seq Scan on test_2
830-
Filter: (a > ALL (NULL::integer[]))
815+
Filter: (a > ALL ('{}'::integer[]))
831816
-> Seq Scan on test_3
832-
Filter: (a > ALL (NULL::integer[]))
817+
Filter: (a > ALL ('{}'::integer[]))
833818
-> Seq Scan on test_4
834-
Filter: (a > ALL (NULL::integer[]))
819+
Filter: (a > ALL ('{}'::integer[]))
835820
-> Seq Scan on test_5
836-
Filter: (a > ALL (NULL::integer[]))
821+
Filter: (a > ALL ('{}'::integer[]))
837822
-> Seq Scan on test_6
838-
Filter: (a > ALL (NULL::integer[]))
823+
Filter: (a > ALL ('{}'::integer[]))
839824
-> Seq Scan on test_7
840-
Filter: (a > ALL (NULL::integer[]))
825+
Filter: (a > ALL ('{}'::integer[]))
841826
-> Seq Scan on test_8
842-
Filter: (a > ALL (NULL::integer[]))
827+
Filter: (a > ALL ('{}'::integer[]))
843828
-> Seq Scan on test_9
844-
Filter: (a > ALL (NULL::integer[]))
829+
Filter: (a > ALL ('{}'::integer[]))
845830
-> Seq Scan on test_10
846-
Filter: (a > ALL (NULL::integer[]))
831+
Filter: (a > ALL ('{}'::integer[]))
847832
(21 rows)
848833

849834
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[100, 100]);

‎sql/pathman_array_qual.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE b IN (NULL, NULL, NULL,
7777

7878
/* a = ANY (...) - pruning should work */
7979
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (NULL);
80+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[]::int4[]);
8081
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[100, 100]);
8182
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[100, 200, 300, 400]);
8283
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100, 200], array[300, 400]]);
@@ -90,6 +91,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100
9091

9192
/* a = ALL (...) - pruning should work */
9293
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (NULL);
94+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[]::int4[]);
9395
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[100, 100]);
9496
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[100, 200, 300, 400]);
9597
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[array[100, 200], array[300, 400]]);
@@ -104,6 +106,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[NULL, NUL
104106

105107
/* a < ANY (...) - pruning should work */
106108
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (NULL);
109+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[]::int4[]);
107110
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[100, 100]);
108111
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[99, 100, 101]);
109112
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[500, 550]);
@@ -122,6 +125,7 @@ SELECT count(*) FROM array_qual.test WHERE a < ANY (array[NULL, 700]);
122125

123126
/* a < ALL (...) - pruning should work */
124127
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (NULL);
128+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[]::int4[]);
125129
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[100, 100]);
126130
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[99, 100, 101]);
127131
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[500, 550]);
@@ -140,6 +144,7 @@ SELECT count(*) FROM array_qual.test WHERE a < ALL (array[NULL, 700]);
140144

141145
/* a > ANY (...) - pruning should work */
142146
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (NULL);
147+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[]::int4[]);
143148
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[100, 100]);
144149
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[99, 100, 101]);
145150
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[500, 550]);
@@ -158,6 +163,7 @@ SELECT count(*) FROM array_qual.test WHERE a > ANY (array[NULL, 700]);
158163

159164
/* a > ALL (...) - pruning should work */
160165
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (NULL);
166+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[]::int4[]);
161167
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[100, 100]);
162168
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[99, 100, 101]);
163169
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[500, 550]);

0 commit comments

Comments
 (0)