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

Commit231046d

Browse files
committed
fix ANY/ALL in handle_arrexpr()
1 parent84739e5 commit231046d

File tree

3 files changed

+189
-2
lines changed

3 files changed

+189
-2
lines changed

‎expected/pathman_basic.out‎

Lines changed: 158 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -564,6 +564,80 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id IN (-1, -1, -1, NU
564564
One-Time Filter: false
565565
(2 rows)
566566

567+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > ANY (ARRAY[1500, 2200]);
568+
QUERY PLAN
569+
-------------------------------------------------------
570+
Append
571+
-> Seq Scan on num_range_rel_2
572+
Filter: (id > ANY ('{1500,2200}'::integer[]))
573+
-> Seq Scan on num_range_rel_3
574+
-> Seq Scan on num_range_rel_4
575+
(5 rows)
576+
577+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > ANY (ARRAY[100, 1500]);
578+
QUERY PLAN
579+
------------------------------------------------------
580+
Append
581+
-> Seq Scan on num_range_rel_1
582+
Filter: (id > ANY ('{100,1500}'::integer[]))
583+
-> Seq Scan on num_range_rel_2
584+
-> Seq Scan on num_range_rel_3
585+
-> Seq Scan on num_range_rel_4
586+
(6 rows)
587+
588+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > ALL (ARRAY[1500, 2200]);
589+
QUERY PLAN
590+
-------------------------------------------------------
591+
Append
592+
-> Seq Scan on num_range_rel_3
593+
Filter: (id > ALL ('{1500,2200}'::integer[]))
594+
-> Seq Scan on num_range_rel_4
595+
(4 rows)
596+
597+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > ALL (ARRAY[100, 1500]);
598+
QUERY PLAN
599+
------------------------------------------------------
600+
Append
601+
-> Seq Scan on num_range_rel_2
602+
Filter: (id > ALL ('{100,1500}'::integer[]))
603+
-> Seq Scan on num_range_rel_3
604+
-> Seq Scan on num_range_rel_4
605+
(5 rows)
606+
607+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = ANY (ARRAY[1500, 2200]);
608+
QUERY PLAN
609+
-------------------------------------------------------
610+
Append
611+
-> Seq Scan on num_range_rel_2
612+
Filter: (id = ANY ('{1500,2200}'::integer[]))
613+
-> Seq Scan on num_range_rel_3
614+
Filter: (id = ANY ('{1500,2200}'::integer[]))
615+
(5 rows)
616+
617+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = ANY (ARRAY[100, 1500]);
618+
QUERY PLAN
619+
------------------------------------------------------
620+
Append
621+
-> Seq Scan on num_range_rel_1
622+
Filter: (id = ANY ('{100,1500}'::integer[]))
623+
-> Seq Scan on num_range_rel_2
624+
Filter: (id = ANY ('{100,1500}'::integer[]))
625+
(5 rows)
626+
627+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = ALL (ARRAY[1500, 2200]);
628+
QUERY PLAN
629+
--------------------------
630+
Result
631+
One-Time Filter: false
632+
(2 rows)
633+
634+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = ALL (ARRAY[100, 1500]);
635+
QUERY PLAN
636+
--------------------------
637+
Result
638+
One-Time Filter: false
639+
(2 rows)
640+
567641
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
568642
QUERY PLAN
569643
--------------------------------------------------------------------------------
@@ -701,6 +775,90 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value IN (NULL::int, NULL,
701775
One-Time Filter: false
702776
(2 rows)
703777

778+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value > ANY (ARRAY[1, 2]);
779+
QUERY PLAN
780+
----------------------------------------------------
781+
Append
782+
-> Seq Scan on hash_rel_0
783+
Filter: (value > ANY ('{1,2}'::integer[]))
784+
-> Seq Scan on hash_rel_1
785+
Filter: (value > ANY ('{1,2}'::integer[]))
786+
-> Seq Scan on hash_rel_2
787+
Filter: (value > ANY ('{1,2}'::integer[]))
788+
(7 rows)
789+
790+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value > ANY (ARRAY[1, 2, 3, 4, 5]);
791+
QUERY PLAN
792+
----------------------------------------------------------
793+
Append
794+
-> Seq Scan on hash_rel_0
795+
Filter: (value > ANY ('{1,2,3,4,5}'::integer[]))
796+
-> Seq Scan on hash_rel_1
797+
Filter: (value > ANY ('{1,2,3,4,5}'::integer[]))
798+
-> Seq Scan on hash_rel_2
799+
Filter: (value > ANY ('{1,2,3,4,5}'::integer[]))
800+
(7 rows)
801+
802+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value > ALL (ARRAY[1, 2]);
803+
QUERY PLAN
804+
----------------------------------------------------
805+
Append
806+
-> Seq Scan on hash_rel_0
807+
Filter: (value > ALL ('{1,2}'::integer[]))
808+
-> Seq Scan on hash_rel_1
809+
Filter: (value > ALL ('{1,2}'::integer[]))
810+
-> Seq Scan on hash_rel_2
811+
Filter: (value > ALL ('{1,2}'::integer[]))
812+
(7 rows)
813+
814+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value > ALL (ARRAY[1, 2, 3, 4, 5]);
815+
QUERY PLAN
816+
----------------------------------------------------------
817+
Append
818+
-> Seq Scan on hash_rel_0
819+
Filter: (value > ALL ('{1,2,3,4,5}'::integer[]))
820+
-> Seq Scan on hash_rel_1
821+
Filter: (value > ALL ('{1,2,3,4,5}'::integer[]))
822+
-> Seq Scan on hash_rel_2
823+
Filter: (value > ALL ('{1,2,3,4,5}'::integer[]))
824+
(7 rows)
825+
826+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = ANY (ARRAY[1, 2]);
827+
QUERY PLAN
828+
----------------------------------------------------
829+
Append
830+
-> Seq Scan on hash_rel_1
831+
Filter: (value = ANY ('{1,2}'::integer[]))
832+
-> Seq Scan on hash_rel_2
833+
Filter: (value = ANY ('{1,2}'::integer[]))
834+
(5 rows)
835+
836+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = ANY (ARRAY[1, 2, 3, 4, 5]);
837+
QUERY PLAN
838+
----------------------------------------------------------
839+
Append
840+
-> Seq Scan on hash_rel_0
841+
Filter: (value = ANY ('{1,2,3,4,5}'::integer[]))
842+
-> Seq Scan on hash_rel_1
843+
Filter: (value = ANY ('{1,2,3,4,5}'::integer[]))
844+
-> Seq Scan on hash_rel_2
845+
Filter: (value = ANY ('{1,2,3,4,5}'::integer[]))
846+
(7 rows)
847+
848+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = ALL (ARRAY[1, 2]);
849+
QUERY PLAN
850+
--------------------------
851+
Result
852+
One-Time Filter: false
853+
(2 rows)
854+
855+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = ALL (ARRAY[1, 2, 3, 4, 5]);
856+
QUERY PLAN
857+
--------------------------
858+
Result
859+
One-Time Filter: false
860+
(2 rows)
861+
704862
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
705863
QUERY PLAN
706864
----------------------------------------------------------------

‎sql/pathman_basic.sql‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -174,11 +174,22 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
174174
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1000AND id<3000;
175175
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1500AND id<2500;
176176
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE (id>=500AND id<1500)OR (id>2500);
177+
177178
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (2500);
178179
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (500,1500);
179180
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (-500,500,1500);
180181
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (-1,-1,-1);
181182
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE idIN (-1,-1,-1,NULL);
183+
184+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id> ANY (ARRAY[1500,2200]);
185+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id> ANY (ARRAY[100,1500]);
186+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id> ALL (ARRAY[1500,2200]);
187+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id> ALL (ARRAY[100,1500]);
188+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id= ANY (ARRAY[1500,2200]);
189+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id= ANY (ARRAY[100,1500]);
190+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id= ALL (ARRAY[1500,2200]);
191+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id= ALL (ARRAY[100,1500]);
192+
182193
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>'2015-02-15';
183194
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-01'AND dt<'2015-03-01';
184195
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-15'AND dt<'2015-03-15';
@@ -194,18 +205,30 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE false;
194205
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=NULL;
195206
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=2;
196207
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=2OR value=1;
208+
197209
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (2);
198210
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (2,1);
199211
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (1,2);
200212
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (1,2,-1);
201213
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (0,0,0);
202214
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE valueIN (NULL::int,NULL,NULL);
215+
216+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value> ANY (ARRAY[1,2]);
217+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value> ANY (ARRAY[1,2,3,4,5]);
218+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value> ALL (ARRAY[1,2]);
219+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value> ALL (ARRAY[1,2,3,4,5]);
220+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value= ANY (ARRAY[1,2]);
221+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value= ANY (ARRAY[1,2,3,4,5]);
222+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value= ALL (ARRAY[1,2]);
223+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value= ALL (ARRAY[1,2,3,4,5]);
224+
203225
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>2500;
204226
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1000AND id<3000;
205227
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1500AND id<2500;
206228
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE (id>=500AND id<1500)OR (id>2500);
207229
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relORDER BY id;
208230
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id<=2500ORDER BY id;
231+
209232
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>'2015-02-15';
210233
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-01'AND dt<'2015-03-01';
211234
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-15'AND dt<'2015-03-15';

‎src/pg_pathman.c‎

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -932,7 +932,7 @@ handle_arrexpr(const ScalarArrayOpExpr *expr,
932932
bool*elem_isnull;
933933

934934
WalkerContextnested_wcxt;
935-
List*ranges=NIL;
935+
List*ranges;
936936
inti;
937937

938938
/* Extract values from array */
@@ -951,6 +951,9 @@ handle_arrexpr(const ScalarArrayOpExpr *expr,
951951
(constvoid*)context,
952952
sizeof(WalkerContext));
953953

954+
/* Set default ranges for OR | AND */
955+
ranges=expr->useOr ?NIL :list_make1_irange_full(prel,IR_COMPLETE);
956+
954957
/* Select partitions using values */
955958
for (i=0;i<num_elems;i++)
956959
{
@@ -970,7 +973,10 @@ handle_arrexpr(const ScalarArrayOpExpr *expr,
970973
c.location=-1;
971974

972975
handle_const(&c,strategy,&nested_wcxt,&sub_result);
973-
ranges=irange_list_union(ranges,sub_result.rangeset);
976+
977+
ranges=expr->useOr ?
978+
irange_list_union(ranges,sub_result.rangeset) :
979+
irange_list_intersection(ranges,sub_result.rangeset);
974980

975981
result->paramsel=Max(result->paramsel,sub_result.paramsel);
976982
}

0 commit comments

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