E589 fix collations, more tests · postgrespro/pg_pathman@d83e980 · GitHub
[go: up one dir, main page]
Skip to content

Commit d83e980

Browse files
funbringerfunbringer
committed
fix collations, more tests
1 parent 23125b7 commit d83e980

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+
CREATE TABLE array_qual.test(val TEXT NOT NULL);
10+
CREATE SEQUENCE array_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 INTO array_qual.test VALUES ('aaaa');
17+
INSERT INTO array_qual.test VALUES ('bbbb');
18+
INSERT INTO array_qual.test VALUES ('cccc');
19+
20+
ANALYZE;
21+
22+
/*
23+
* Test expr op ANY (...)
24+
*/
25+
26+
/* matching collations */
27+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val < ANY (array['a', 'b']);
28+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val < ANY (array['a', 'z']);
29+
30+
/* different collations */
31+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "POSIX" < ANY (array['a', 'b']);
32+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val < ANY (array['a', 'b' COLLATE "POSIX"]);
33+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "C" < ANY (array['a', 'b' COLLATE "POSIX"]);
34+
35+
/* different collations (pruning should work) */
36+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE val COLLATE "POSIX" = ANY (array['a', 'b']);
37+
38+
39+
40+
DROP TABLE array_qual.test CASCADE;
41+
42+
43+
944
CREATE TABLE array_qual.test(a INT4 NOT NULL, b INT4);
1045
SELECT create_range_partitions('array_qual.test', 'a', 1, 100, 10);
1146
INSERT INTO array_qual.test SELECT i, i FROM 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
static Node *wrapper_make_expression(WrapperNode *wrap, int index, bool *alwaysTrue);
5555

5656
static void handle_const(const Const *c,
57+
const Oid collid,
5758
const int strategy,
5859
const WalkerContext *context,
5960
WrapperNode *result);
6061

62+
static void handle_array(ArrayType *array,
63+
const Oid collid,
64+
const int strategy,
65+
const bool use_or,
66+
const WalkerContext *context,
67+
WrapperNode *result);
68+
6169
static void handle_boolexpr(const BoolExpr *expr,
6270
const WalkerContext *context,
6371
WrapperNode *result);
@@ -406,9 +414,9 @@ append_child_relation(PlannerInfo *root, Relation parent_relation,
406414

407415

408416
/*
409-
* --------------------------
410-
* RANGE partition prunning
411-
* --------------------------
417+
* -------------------------
418+
* RANGE partition pruning
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
case T_Const:
616-
handle_const((Const *) expr, BTEqualStrategyNumber, context, result);
624+
handle_const((Const *) expr, ((Const *) expr)->constcollid,
625+
BTEqualStrategyNumber, context, result);
617626
return result;
618627

619628
/* AND, OR, NOT expressions */
@@ -718,6 +727,7 @@ wrapper_make_expression(WrapperNode *wrap, int index, bool *alwaysTrue)
718727
/* Const handler */
719728
static void
720729
handle_const(const Const *c,
730+
const Oid collid,
721731
const int strategy,
722732
const WalkerContext *context,
723733
WrapperNode *result) /* ret value #1 */
@@ -806,8 +816,7 @@ handle_const(const Const *c,
806816
FmgrInfo cmp_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
goto handle_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
static void
843852
handle_array(ArrayType *array,
853+
const Oid collid,
844854
const int strategy,
845855
const bool use_or,
846856
const WalkerContext *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 = (const Node *) 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-
else handle_const(c, strategy, context, &wrap);
1081+
else handle_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)
0