Skip to content

Commit 9e4b730

Browse files
funbringerfunbringer
committed
more tests (+ operator ~~)
1 parent ab59cf0 commit 9e4b730

File tree

2 files changed

+180
-0
lines changed

2 files changed

+180
-0
lines changed

‎expected/pathman_array_qual.out

Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "POSIX" = AN
101101
Filter: ((val)::text = ANY ('{a,b}'::text[]))
102102
(5 rows)
103103

104+
/* non-btree operator */
105+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val ~~ ANY (array['a', 'b']);
106+
QUERY PLAN
107+
------------------------------------------------
108+
Append
109+
-> Seq Scan on test_1
110+
Filter: (val ~~ ANY ('{a,b}'::text[]))
111+
-> Seq Scan on test_2
112+
Filter: (val ~~ ANY ('{a,b}'::text[]))
113+
-> Seq Scan on test_3
114+
Filter: (val ~~ ANY ('{a,b}'::text[]))
115+
-> Seq Scan on test_4
116+
Filter: (val ~~ ANY ('{a,b}'::text[]))
117+
(9 rows)
118+
104119
DROP TABLE array_qual.test CASCADE;
105120
NOTICE: drop cascades to 5 other objects
106121
CREATE TABLE array_qual.test(a INT4 NOT NULL, b INT4);
@@ -328,6 +343,32 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE b IN (NULL, NULL, NULL,
328343
* Test expr = ANY (...)
329344
*/
330345
/* a = ANY (...) - pruning should work */
346+
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)
371+
331372
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[100, 100]);
332373
QUERY PLAN
333374
----------------------------------------------------
@@ -394,6 +435,32 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100
394435
* Test expr = ALL (...)
395436
*/
396437
/* a = ALL (...) - pruning should work */
438+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (NULL);
439+
QUERY PLAN
440+
---------------------------------------------
441+
Append
442+
-> Seq Scan on test_1
443+
Filter: (a = ALL (NULL::integer[]))
444+
-> Seq Scan on test_2
445+
Filter: (a = ALL (NULL::integer[]))
446+
-> Seq Scan on test_3
447+
Filter: (a = ALL (NULL::integer[]))
448+
-> Seq Scan on test_4
449+
Filter: (a = ALL (NULL::integer[]))
450+
-> Seq Scan on test_5
451+
Filter: (a = ALL (NULL::integer[]))
452+
-> Seq Scan on test_6
453+
Filter: (a = ALL (NULL::integer[]))
454+
-> Seq Scan on test_7
455+
Filter: (a = ALL (NULL::integer[]))
456+
-> Seq Scan on test_8
457+
Filter: (a = ALL (NULL::integer[]))
458+
-> Seq Scan on test_9
459+
Filter: (a = ALL (NULL::integer[]))
460+
-> Seq Scan on test_10
461+
Filter: (a = ALL (NULL::integer[]))
462+
(21 rows)
463+
397464
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[100, 100]);
398465
QUERY PLAN
399466
----------------------------------------------------
@@ -441,6 +508,32 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[NULL, NUL
441508
* Test expr < ANY (...)
442509
*/
443510
/* a < ANY (...) - pruning should work */
511+
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)
536+
444537
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[100, 100]);
445538
QUERY PLAN
446539
----------------------------------------------------
@@ -517,6 +610,32 @@ SELECT count(*) FROM array_qual.test WHERE a < ANY (array[NULL, 700]);
517610
* Test expr < ALL (...)
518611
*/
519612
/* a < ALL (...) - pruning should work */
613+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (NULL);
614+
QUERY PLAN
615+
---------------------------------------------
616+
Append
617+
-> Seq Scan on test_1
618+
Filter: (a < ALL (NULL::integer[]))
619+
-> Seq Scan on test_2
620+
Filter: (a < ALL (NULL::integer[]))
621+
-> Seq Scan on test_3
622+
Filter: (a < ALL (NULL::integer[]))
623+
-> Seq Scan on test_4
624+
Filter: (a < ALL (NULL::integer[]))
625+
-> Seq Scan on test_5
626+
Filter: (a < ALL (NULL::integer[]))
627+
-> Seq Scan on test_6
628+
Filter: (a < ALL (NULL::integer[]))
629+
-> Seq Scan on test_7
630+
Filter: (a < ALL (NULL::integer[]))
631+
-> Seq Scan on test_8
632+
Filter: (a < ALL (NULL::integer[]))
633+
-> Seq Scan on test_9
634+
Filter: (a < ALL (NULL::integer[]))
635+
-> Seq Scan on test_10
636+
Filter: (a < ALL (NULL::integer[]))
637+
(21 rows)
638+
520639
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[100, 100]);
521640
QUERY PLAN
522641
----------------------------------------------------
@@ -580,6 +699,32 @@ SELECT count(*) FROM array_qual.test WHERE a < ALL (array[NULL, 700]);
580699
* Test expr > ANY (...)
581700
*/
582701
/* a > ANY (...) - pruning should work */
702+
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)
727+
583728
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[100, 100]);
584729
QUERY PLAN
585730
----------------------------------------------------
@@ -675,6 +820,32 @@ SELECT count(*) FROM array_qual.test WHERE a > ANY (array[NULL, 700]);
675820
* Test expr > ALL (...)
676821
*/
677822
/* a > ALL (...) - pruning should work */
823+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (NULL);
824+
QUERY PLAN
825+
---------------------------------------------
826+
Append
827+
-> Seq Scan on test_1
828+
Filter: (a > ALL (NULL::integer[]))
829+
-> Seq Scan on test_2
830+
Filter: (a > ALL (NULL::integer[]))
831+
-> Seq Scan on test_3
832+
Filter: (a > ALL (NULL::integer[]))
833+
-> Seq Scan on test_4
834+
Filter: (a > ALL (NULL::integer[]))
835+
-> Seq Scan on test_5
836+
Filter: (a > ALL (NULL::integer[]))
837+
-> Seq Scan on test_6
838+
Filter: (a > ALL (NULL::integer[]))
839+
-> Seq Scan on test_7
840+
Filter: (a > ALL (NULL::integer[]))
841+
-> Seq Scan on test_8
842+
Filter: (a > ALL (NULL::integer[]))
843+
-> Seq Scan on test_9
844+
Filter: (a > ALL (NULL::integer[]))
845+
-> Seq Scan on test_10
846+
Filter: (a > ALL (NULL::integer[]))
847+
(21 rows)
848+
678849
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[100, 100]);
679850
QUERY PLAN
680851
----------------------------------------------------

‎sql/pathman_array_qual.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "C" < ANY (a
3535
/* different collations (pruning should work) */
3636
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "POSIX" = ANY (array['a', 'b']);
3737

38+
/* non-btree operator */
39+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val ~~ ANY (array['a', 'b']);
40+
3841

3942

4043
DROP TABLE array_qual.test CASCADE;
@@ -73,6 +76,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE b IN (NULL, NULL, NULL,
7376
*/
7477

7578
/* a = ANY (...) - pruning should work */
79+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (NULL);
7680
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[100, 100]);
7781
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[100, 200, 300, 400]);
7882
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100, 200], array[300, 400]]);
@@ -85,6 +89,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100
8589
*/
8690

8791
/* a = ALL (...) - pruning should work */
92+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (NULL);
8893
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[100, 100]);
8994
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[100, 200, 300, 400]);
9095
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[array[100, 200], array[300, 400]]);
@@ -98,6 +103,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ALL (array[NULL, NUL
98103
*/
99104

100105
/* a < ANY (...) - pruning should work */
106+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (NULL);
101107
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[100, 100]);
102108
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[99, 100, 101]);
103109
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[500, 550]);
@@ -115,6 +121,7 @@ SELECT count(*) FROM array_qual.test WHERE a < ANY (array[NULL, 700]);
115121
*/
116122

117123
/* a < ALL (...) - pruning should work */
124+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (NULL);
118125
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[100, 100]);
119126
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[99, 100, 101]);
120127
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[500, 550]);
@@ -132,6 +139,7 @@ SELECT count(*) FROM array_qual.test WHERE a < ALL (array[NULL, 700]);
132139
*/
133140

134141
/* a > ANY (...) - pruning should work */
142+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (NULL);
135143
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[100, 100]);
136144
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[99, 100, 101]);
137145
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[500, 550]);
@@ -149,6 +157,7 @@ SELECT count(*) FROM array_qual.test WHERE a > ANY (array[NULL, 700]);
149157
*/
150158

151159
/* a > ALL (...) - pruning should work */
160+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (NULL);
152161
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[100, 100]);
153162
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[99, 100, 101]);
154163
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[500, 550]);

0 commit comments

Comments
 (0)