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

Commit032d81f

Browse files
committed
add tests for 'column = IN(...)' for both HASH & RANGE partitioned tables
1 parent9828aca commit032d81f

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*FROMtest.num_range_relWHERE id>=1000AND id<3000;
141141
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1500AND id<2500;
142142
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE (id>=500AND id<1500)OR (id>2500);
143+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (2500);
144+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (500,1500);
145+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (-500,500,1500);
146+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (-1,-1,-1);
147+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (-1,-1,-1,NULL);
143148
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>'2015-02-15';
144149
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-01'AND dt<'2015-03-01';
145150
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-15'AND dt<'2015-03-15';
@@ -153,6 +158,12 @@ SET enable_seqscan = OFF;
153158
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_rel;
154159
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=2;
155160
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=2OR value=1;
161+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (2);
162+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (2,1);
163+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (1,2);
164+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (1,2,-1);
165+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (0,0,0);
166+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (NULL::int,NULL,NULL);
156167
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>2500;
157168
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1000AND id<3000;
158169
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1500AND id<2500;

0 commit comments

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