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

Commitd83e980

Browse files
committed
fix collations, more tests
1 parent23125b7 commitd83e980

File tree

3 files changed

+161
-11
lines changed

3 files changed

+161
-11
lines changed

‎expected/pathman_array_qual.out‎

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,107 @@
22
SET search_path = 'public';
33
CREATE EXTENSION pg_pathman;
44
CREATE SCHEMA array_qual;
5+
CREATE TABLE array_qual.test(val TEXT NOT NULL);
6+
CREATE SEQUENCE array_qual.test_seq;
7+
SELECT add_to_pathman_config('array_qual.test', 'val', NULL);
8+
add_to_pathman_config
9+
-----------------------
10+
t
11+
(1 row)
12+
13+
SELECT add_range_partition('array_qual.test', 'a'::TEXT, 'b');
14+
add_range_partition
15+
---------------------
16+
array_qual.test_1
17+
(1 row)
18+
19+
SELECT add_range_partition('array_qual.test', 'b'::TEXT, 'c');
20+
add_range_partition
21+
---------------------
22+
array_qual.test_2
23+
(1 row)
24+
25+
SELECT add_range_partition('array_qual.test', 'c'::TEXT, 'd');
26+
add_range_partition
27+
---------------------
28+
array_qual.test_3
29+
(1 row)
30+
31+
SELECT add_range_partition('array_qual.test', 'd'::TEXT, 'e');
32+
add_range_partition
33+
---------------------
34+
array_qual.test_4
35+
(1 row)
36+
37+
INSERT INTO array_qual.test VALUES ('aaaa');
38+
INSERT INTO array_qual.test VALUES ('bbbb');
39+
INSERT INTO array_qual.test VALUES ('cccc');
40+
ANALYZE;
41+
/*
42+
* Test expr op ANY (...)
43+
*/
44+
/* matching collations */
45+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val < ANY (array['a', 'b']);
46+
QUERY PLAN
47+
--------------------------
48+
Append
49+
-> Seq Scan on test_1
50+
(2 rows)
51+
52+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val < ANY (array['a', 'z']);
53+
QUERY PLAN
54+
--------------------------
55+
Append
56+
-> Seq Scan on test_1
57+
-> Seq Scan on test_2
58+
-> Seq Scan on test_3
59+
-> Seq Scan on test_4
60+
(5 rows)
61+
62+
/* different collations */
63+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "POSIX" < ANY (array['a', 'b']);
64+
QUERY PLAN
65+
-------------------------------------------------------
66+
Append
67+
-> Seq Scan on test_1
68+
Filter: ((val)::text < ANY ('{a,b}'::text[]))
69+
-> Seq Scan on test_2
70+
Filter: ((val)::text < ANY ('{a,b}'::text[]))
71+
-> Seq Scan on test_3
72+
Filter: ((val)::text < ANY ('{a,b}'::text[]))
73+
-> Seq Scan on test_4
74+
Filter: ((val)::text < ANY ('{a,b}'::text[]))
75+
(9 rows)
76+
77+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val < ANY (array['a', 'b' COLLATE "POSIX"]);
78+
QUERY PLAN
79+
---------------------------------------------------------------
80+
Append
81+
-> Seq Scan on test_1
82+
Filter: (val < ANY ('{a,b}'::text[] COLLATE "POSIX"))
83+
-> Seq Scan on test_2
84+
Filter: (val < ANY ('{a,b}'::text[] COLLATE "POSIX"))
85+
-> Seq Scan on test_3
86+
Filter: (val < ANY ('{a,b}'::text[] COLLATE "POSIX"))
87+
-> Seq Scan on test_4
88+
Filter: (val < ANY ('{a,b}'::text[] COLLATE "POSIX"))
89+
(9 rows)
90+
91+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "C" < ANY (array['a', 'b' COLLATE "POSIX"]);
92+
ERROR: collation mismatch between explicit collations "C" and "POSIX" at character 95
93+
/* different collations (pruning should work) */
94+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "POSIX" = ANY (array['a', 'b']);
95+
QUERY PLAN
96+
-------------------------------------------------------
97+
Append
98+
-> Seq Scan on test_1
99+
Filter: ((val)::text = ANY ('{a,b}'::text[]))
100+
-> Seq Scan on test_2
101+
Filter: ((val)::text = ANY ('{a,b}'::text[]))
102+
(5 rows)
103+
104+
DROP TABLE array_qual.test CASCADE;
105+
NOTICE: drop cascades to 5 other objects
5106
CREATE TABLE array_qual.test(a INT4 NOT NULL, b INT4);
6107
SELECT create_range_partitions('array_qual.test', 'a', 1, 100, 10);
7108
create_range_partitions

‎sql/pathman_array_qual.sql‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,41 @@ CREATE SCHEMA array_qual;
66

77

88

9+
CREATETABLEarray_qual.test(valTEXTNOT NULL);
10+
CREATESEQUENCEarray_qual.test_seq;
11+
SELECT add_to_pathman_config('array_qual.test','val',NULL);
12+
SELECT add_range_partition('array_qual.test','a'::TEXT,'b');
13+
SELECT add_range_partition('array_qual.test','b'::TEXT,'c');
14+
SELECT add_range_partition('array_qual.test','c'::TEXT,'d');
15+
SELECT add_range_partition('array_qual.test','d'::TEXT,'e');
16+
INSERT INTOarray_qual.testVALUES ('aaaa');
17+
INSERT INTOarray_qual.testVALUES ('bbbb');
18+
INSERT INTOarray_qual.testVALUES ('cccc');
19+
20+
ANALYZE;
21+
22+
/*
23+
* Test expr op ANY (...)
24+
*/
25+
26+
/* matching collations*/
27+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE val< ANY (array['a','b']);
28+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE val< ANY (array['a','z']);
29+
30+
/* different collations*/
31+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE val COLLATE"POSIX"< ANY (array['a','b']);
32+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE val< ANY (array['a','b' COLLATE"POSIX"]);
33+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE val COLLATE"C"< ANY (array['a','b' COLLATE"POSIX"]);
34+
35+
/* different collations (pruning should work)*/
36+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE val COLLATE"POSIX"= ANY (array['a','b']);
37+
38+
39+
40+
DROPTABLEarray_qual.test CASCADE;
41+
42+
43+
944
CREATETABLEarray_qual.test(a INT4NOT NULL, b INT4);
1045
SELECT create_range_partitions('array_qual.test','a',1,100,10);
1146
INSERT INTOarray_qual.testSELECT i, iFROM generate_series(1,1000) g(i);

‎src/pg_pathman.c‎

Lines changed: 25 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -54,10 +54,18 @@ void _PG_init(void);
5454
staticNode*wrapper_make_expression(WrapperNode*wrap,intindex,bool*alwaysTrue);
5555

5656
staticvoidhandle_const(constConst*c,
57+
constOidcollid,
5758
constintstrategy,
5859
constWalkerContext*context,
5960
WrapperNode*result);
6061

62+
staticvoidhandle_array(ArrayType*array,
63+
constOidcollid,
64+
constintstrategy,
65+
constbooluse_or,
66+
constWalkerContext*context,
67+
WrapperNode*result);
68+
6169
staticvoidhandle_boolexpr(constBoolExpr*expr,
6270
constWalkerContext*context,
6371
WrapperNode*result);
@@ -406,9 +414,9 @@ append_child_relation(PlannerInfo *root, Relation parent_relation,
406414

407415

408416
/*
409-
* --------------------------
410-
* RANGE partitionprunning
411-
* --------------------------
417+
* -------------------------
418+
* RANGE partitionpruning
419+
* -------------------------
412420
*/
413421

414422
/* Given 'value' and 'ranges', return selected partitions list */
@@ -613,7 +621,8 @@ walk_expr_tree(Expr *expr, const WalkerContext *context)
613621
{
614622
/* Useful for INSERT optimization */
615623
caseT_Const:
616-
handle_const((Const*)expr,BTEqualStrategyNumber,context,result);
624+
handle_const((Const*)expr, ((Const*)expr)->constcollid,
625+
BTEqualStrategyNumber,context,result);
617626
returnresult;
618627

619628
/* AND, OR, NOT expressions */
@@ -718,6 +727,7 @@ wrapper_make_expression(WrapperNode *wrap, int index, bool *alwaysTrue)
718727
/* Const handler */
719728
staticvoid
720729
handle_const(constConst*c,
730+
constOidcollid,
721731
constintstrategy,
722732
constWalkerContext*context,
723733
WrapperNode*result)/* ret value #1 */
@@ -806,8 +816,7 @@ handle_const(const Const *c,
806816
FmgrInfocmp_finfo;
807817

808818
/* Cannot do much about non-equal strategies + diff. collations */
809-
if (strategy!=BTEqualStrategyNumber&&
810-
c->constcollid!=prel->ev_collid)
819+
if (strategy!=BTEqualStrategyNumber&&collid!=prel->ev_collid)
811820
{
812821
gotohandle_const_return;
813822
}
@@ -817,7 +826,7 @@ handle_const(const Const *c,
817826
getBaseType(prel->ev_type));
818827

819828
select_range_partitions(c->constvalue,
820-
c->constcollid,
829+
collid,
821830
&cmp_finfo,
822831
PrelGetRangesArray(context->prel),
823832
PrelChildrenCount(context->prel),
@@ -841,6 +850,7 @@ handle_const(const Const *c,
841850
/* Array handler */
842851
staticvoid
843852
handle_array(ArrayType*array,
853+
constOidcollid,
844854
constintstrategy,
845855
constbooluse_or,
846856
constWalkerContext*context,
@@ -898,7 +908,7 @@ handle_array(ArrayType *array,
898908
c.constbyval=elem_byval;
899909
c.location=-1;
900910

901-
handle_const(&c,strategy,context,&wrap);
911+
handle_const(&c,collid,strategy,context,&wrap);
902912

903913
/* Should we use OR | AND? */
904914
ranges=use_or ?
@@ -1020,7 +1030,8 @@ handle_arrexpr(const ScalarArrayOpExpr *expr,
10201030

10211031
/* Examine array */
10221032
handle_array(DatumGetArrayTypeP(c->constvalue),
1023-
strategy,expr->useOr,context,result);
1033+
expr->inputcollid,strategy,
1034+
expr->useOr,context,result);
10241035

10251036
/* Save expression */
10261037
result->orig= (constNode*)expr;
@@ -1063,10 +1074,12 @@ handle_arrexpr(const ScalarArrayOpExpr *expr,
10631074

10641075
/* Examine array */
10651076
handle_array(DatumGetArrayTypeP(c->constvalue),
1066-
strategy,expr->useOr,context,&wrap);
1077+
expr->inputcollid,strategy,
1078+
expr->useOr,context,&wrap);
10671079
}
10681080
/* ... or a single element? */
1069-
elsehandle_const(c,strategy,context,&wrap);
1081+
elsehandle_const(c,expr->inputcollid,
1082+
strategy,context,&wrap);
10701083

10711084
/* Should we use OR | AND? */
10721085
ranges=expr->useOr ?
@@ -1131,6 +1144,7 @@ handle_opexpr(const OpExpr *expr,
11311144
if (IsConstValue(param,context))
11321145
{
11331146
handle_const(ExtractConst(param,context),
1147+
expr->inputcollid,
11341148
strategy,context,result);
11351149

11361150
/* Save expression */

0 commit comments

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