Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9e4b730

Browse files
committed
more tests (+ operator ~~)
1 parentab59cf0 commit9e4b730

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*FROMarray_qual.testWHERE val COLLATE"POSIX"= ANY (array['a','b']);
3737

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

3942

4043
DROPTABLEarray_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*FROMarray_qual.testWHERE a= ANY (NULL);
7680
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a= ANY (array[100,100]);
7781
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a= ANY (array[100,200,300,400]);
7882
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a= ALL (NULL);
8893
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a= ALL (array[100,100]);
8994
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a= ALL (array[100,200,300,400]);
9095
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a< ANY (NULL);
101107
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ANY (array[100,100]);
102108
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ANY (array[99,100,101]);
103109
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a< ALL (NULL);
118125
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ALL (array[100,100]);
119126
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ALL (array[99,100,101]);
120127
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a> ANY (NULL);
135143
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ANY (array[100,100]);
136144
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ANY (array[99,100,101]);
137145
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a> ALL (NULL);
152161
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ALL (array[100,100]);
153162
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ALL (array[99,100,101]);
154163
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ALL (array[500,550]);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp