Skip to content

Commit 032d81f

Browse files
funbringerfunbringer
committed
add tests for 'column = IN(...)' for both HASH & RANGE partitioned tables
1 parent 9828aca commit 032d81f

File tree

2 files changed

+106
-0
lines changed

2 files changed

+106
-0
lines changed

‎expected/pathman_basic.out

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -517,6 +517,48 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1
517517
-> Seq Scan on num_range_rel_4
518518
(8 rows)
519519

520+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (2500);
521+
QUERY PLAN
522+
-----------------------------------
523+
Append
524+
-> Seq Scan on num_range_rel_3
525+
Filter: (id = 2500)
526+
(3 rows)
527+
528+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (500, 1500);
529+
QUERY PLAN
530+
------------------------------------------------------
531+
Append
532+
-> Seq Scan on num_range_rel_1
533+
Filter: (id = ANY ('{500,1500}'::integer[]))
534+
-> Seq Scan on num_range_rel_2
535+
Filter: (id = ANY ('{500,1500}'::integer[]))
536+
(5 rows)
537+
538+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (-500, 500, 1500);
539+
QUERY PLAN
540+
-----------------------------------------------------------
541+
Append
542+
-> Seq Scan on num_range_rel_1
543+
Filter: (id = ANY ('{-500,500,1500}'::integer[]))
544+
-> Seq Scan on num_range_rel_2
545+
Filter: (id = ANY ('{-500,500,1500}'::integer[]))
546+
(5 rows)
547+
548+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (-1, -1, -1);
549+
QUERY PLAN
550+
--------------------------
551+
Result
552+
One-Time Filter: false
553+
(2 rows)
554+
555+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (-1, -1, -1, NULL);
556+
QUERY PLAN
557+
--------------------------
558+
Result
559+
One-Time Filter: false
560+
(2 rows)
561+
520562
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
521563
QUERY PLAN
522564
--------------------------------------------------------------------------------
@@ -587,6 +629,59 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
587629
Filter: (value = 1)
588630
(5 rows)
589631

632+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (2);
633+
QUERY PLAN
634+
------------------------------
635+
Append
636+
-> Seq Scan on hash_rel_1
637+
Filter: (value = 2)
638+
(3 rows)
639+
640+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (2, 1);
641+
QUERY PLAN
642+
----------------------------------------------------
643+
Append
644+
-> Seq Scan on hash_rel_1
645+
Filter: (value = ANY ('{2,1}'::integer[]))
646+
-> Seq Scan on hash_rel_2
647+
Filter: (value = ANY ('{2,1}'::integer[]))
648+
(5 rows)
649+
650+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (1, 2);
651+
QUERY PLAN
652+
----------------------------------------------------
653+
Append
654+
-> Seq Scan on hash_rel_1
655+
Filter: (value = ANY ('{1,2}'::integer[]))
656+
-> Seq Scan on hash_rel_2
657+
Filter: (value = ANY ('{1,2}'::integer[]))
658+
(5 rows)
659+
660+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (1, 2, -1);
661+
QUERY PLAN
662+
-------------------------------------------------------
663+
Append
664+
-> Seq Scan on hash_rel_1
665+
Filter: (value = ANY ('{1,2,-1}'::integer[]))
666+
-> Seq Scan on hash_rel_2
667+
Filter: (value = ANY ('{1,2,-1}'::integer[]))
668+
(5 rows)
669+
670+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (0, 0, 0);
671+
QUERY PLAN
672+
------------------------------------------------------
673+
Append
674+
-> Seq Scan on hash_rel_1
675+
Filter: (value = ANY ('{0,0,0}'::integer[]))
676+
(3 rows)
677+
678+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (NULL::int, NULL, NULL);
679+
QUERY PLAN
680+
--------------------------
681+
Result
682+
One-Time Filter: false
683+
(2 rows)
684+
590685
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
591686
QUERY PLAN
592687
----------------------------------------------------------------

‎sql/pathman_basic.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,11 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
140140
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
141141
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
142142
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
143+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (2500);
144+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (500, 1500);
145+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (-500, 500, 1500);
146+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (-1, -1, -1);
147+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (-1, -1, -1, NULL);
143148
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
144149
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-01';
145150
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-15' AND dt < '2015-03-15';
@@ -153,6 +158,12 @@ SET enable_seqscan = OFF;
153158
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
154159
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
155160
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
161+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (2);
162+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (2, 1);
163+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (1, 2);
164+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (1, 2, -1);
165+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (0, 0, 0);
166+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (NULL::int, NULL, NULL);
156167
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
157168
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
158169
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;

0 commit comments

Comments
 (0)