Обсуждение: v13 planner ERROR: could not determine which collation to use for string comparison

We hit this on v13b2 and verified it fails on today's HEAD (ac25e7b039).

explain SELECT 1 FROM sites NATURAL JOIN sectors WHERE sites.config_site_name != sectors.sect_name ;
ERROR:  could not determine which collation to use for string comparison 

I can workaround the issue by DELETEing stats for either column.

It's possible we're doing soemthing wrong and I need to revisit docs..but this
was working in v12.

ts=# SELECT * FROM pg_stats WHERE tablename='sites' AND attname='config_site_name'; 
-[ RECORD 1 ]----------+-----------------
schemaname             | public
tablename              | sites
attname                | config_site_name
inherited              | f
null_frac              | 0
avg_width              | 1
n_distinct             | 1
most_common_vals       | {""}
most_common_freqs      | {1}
histogram_bounds       | 
correlation            | 1
most_common_elems      | 
most_common_elem_freqs | 
elem_count_histogram   | 

#1  0x0000000000ab2993 in errfinish (filename=0xcaae40 "varlena.c", lineno=1476, funcname=0xcab7b0 <__func__.18296>
"check_collation_set")at elog.c:502
 
#2  0x0000000000a783ae in check_collation_set (collid=0) at varlena.c:1473
#3  0x0000000000a78857 in texteq (fcinfo=0x7fff1ecae590) at varlena.c:1740
#4  0x0000000000a4248c in eqjoinsel_inner (opfuncoid=67, collation=0, vardata1=0x7fff1ecae7a0, vardata2=0x7fff1ecae770,
nd1=1,nd2=1, isdefault1=false, isdefault2=false, sslot1=0x7fff1ecae720, 
 
    sslot2=0x7fff1ecae6e0, stats1=0x1a97c00, stats2=0x1a98230, have_mcvs1=true, have_mcvs2=true) at selfuncs.c:2466
#5  0x0000000000a41f66 in eqjoinsel (fcinfo=0x7fff1ecae8a0) at selfuncs.c:2298
#6  0x0000000000abb63c in DirectFunctionCall5Coll (func=0xa41caf <eqjoinsel>, collation=0, arg1=28313248, arg2=98,
arg3=28315832,arg4=0, arg5=140733710004032) at fmgr.c:908
 
#7  0x0000000000a43197 in neqjoinsel (fcinfo=0x7fff1ecaea40) at selfuncs.c:2824
#8  0x0000000000abc4a0 in FunctionCall5Coll (flinfo=0x7fff1ecaeb00, collation=100, arg1=28313248, arg2=531,
arg3=28315832,arg4=0, arg5=140733710004032) at fmgr.c:1245
 
#9  0x0000000000abcd1c in OidFunctionCall5Coll (functionId=106, collation=100, arg1=28313248, arg2=531, arg3=28315832,
arg4=0,arg5=140733710004032) at fmgr.c:1463
 
#10 0x000000000084b2c2 in join_selectivity (root=0x1b006a0, operatorid=531, args=0x1b010b8, inputcollid=100,
jointype=JOIN_INNER,sjinfo=0x7fff1ecaef40) at plancat.c:1822
 
#11 0x00000000007dba29 in clause_selectivity (root=0x1b006a0, clause=0x1b01168, varRelid=0, jointype=JOIN_INNER,
sjinfo=0x7fff1ecaef40)at clausesel.c:765
 
#12 0x00000000007dacf4 in clauselist_selectivity_simple (root=0x1b006a0, clauses=0x1b05fe8, varRelid=0,
jointype=JOIN_INNER,sjinfo=0x7fff1ecaef40, estimatedclauses=0x0) at clausesel.c:169
 
#13 0x00000000007dac33 in clauselist_selectivity (root=0x1b006a0, clauses=0x1b05fe8, varRelid=0, jointype=JOIN_INNER,
sjinfo=0x7fff1ecaef40)at clausesel.c:102
 
#14 0x00000000007e44e3 in calc_joinrel_size_estimate (root=0x1b006a0, joinrel=0x1b02ce0, outer_rel=0x1afd4f0,
inner_rel=0x1b01cf0,outer_rows=311, inner_rows=1047, sjinfo=0x7fff1ecaef40, restrictlist_in=0x1b05de0)
 
    at costsize.c:4857
#15 0x00000000007e41eb in set_joinrel_size_estimates (root=0x1b006a0, rel=0x1b02ce0, outer_rel=0x1afd4f0,
inner_rel=0x1b01cf0,sjinfo=0x7fff1ecaef40, restrictlist=0x1b05de0) at costsize.c:4712
 
#16 0x00000000008507a6 in build_join_rel (root=0x1b006a0, joinrelids=0x1b05c08, outer_rel=0x1afd4f0,
inner_rel=0x1b01cf0,sjinfo=0x7fff1ecaef40, restrictlist_ptr=0x7fff1ecaef38) at relnode.c:728
 
#17 0x00000000007f5ecb in make_join_rel (root=0x1b006a0, rel1=0x1afd4f0, rel2=0x1b01cf0) at joinrels.c:746
#18 0x00000000007f542e in make_rels_by_clause_joins (root=0x1b006a0, old_rel=0x1afd4f0, other_rels_list=0x1b05d08,
other_rels=0x1b05d28)at joinrels.c:312
 
#19 0x00000000007f4f04 in join_search_one_level (root=0x1b006a0, level=2) at joinrels.c:123
#20 0x00000000007d96a5 in standard_join_search (root=0x1b006a0, levels_needed=2, initial_rels=0x1b05d08) at
allpaths.c:3097
#21 0x00000000007d961e in make_rel_from_joinlist (root=0x1b006a0, joinlist=0x1b03b28) at allpaths.c:3028
#22 0x00000000007d4f82 in make_one_rel (root=0x1b006a0, joinlist=0x1b03b28) at allpaths.c:227
#23 0x000000000080f835 in query_planner (root=0x1b006a0, qp_callback=0x816525 <standard_qp_callback>,
qp_extra=0x7fff1ecaf320)at planmain.c:269
 
#24 0x0000000000813406 in grouping_planner (root=0x1b006a0, inheritance_update=false, tuple_fraction=0) at
planner.c:2058
#25 0x00000000008115b7 in subquery_planner (glob=0x1b00588, parse=0x1afdc48, parent_root=0x0, hasRecursion=false,
tuple_fraction=0)at planner.c:1015
 
#26 0x000000000080fe34 in standard_planner (parse=0x1afdc48, query_string=0x1938e90 "explain SELECT 1 FROM sites
NATURALJOIN sectors WHERE sites. config_site_name != sectors.sect_name ;", cursorOptions=256, 
 
    boundParams=0x0) at planner.c:405

We hit this on v13b2 and verified it fails on today's HEAD (ac25e7b039).

explain SELECT 1 FROM sites NATURAL JOIN sectors WHERE sites.config_site_name != sectors.sect_name ;
ERROR:  could not determine which collation to use for string comparison

I can workaround the issue by DELETEing stats for either column.

It's possible we're doing soemthing wrong and I need to revisit docs..but this
was working in v12.

Reproducer:

postgres=# CREATE TABLE t AS SELECT ''a FROM generate_series(1,99); CREATE TABLE u AS SELECT ''a FROM
generate_series(1,99); VACUUM ANALYZE t,u;
 
postgres=# explain SELECT * FROM t JOIN u ON t.a!=u.a;
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.

Justin Pryzby <pryzby@telsasoft.com> writes:
> We hit this on v13b2 and verified it fails on today's HEAD (ac25e7b039).
> explain SELECT 1 FROM sites NATURAL JOIN sectors WHERE sites.config_site_name != sectors.sect_name ;
> ERROR:  could not determine which collation to use for string comparison

> I can workaround the issue by DELETEing stats for either column.

Ugh.  It's clear from your stack trace that neqjoinsel() has forgotten to
pass through collation to eqjoinsel().  Will fix.

            regards, tom lane

On Tue, Jul 21, 2020 at 06:25:00PM -0400, Tom Lane wrote:
> Ugh.  It's clear from your stack trace that neqjoinsel() has forgotten to
> pass through collation to eqjoinsel().  Will fix.

Why didn't you include a regression test in bd0d893?
--
Michael

Michael Paquier <michael@paquier.xyz> writes:
> On Tue, Jul 21, 2020 at 06:25:00PM -0400, Tom Lane wrote:
>> Ugh.  It's clear from your stack trace that neqjoinsel() has forgotten to
>> pass through collation to eqjoinsel().  Will fix.

> Why didn't you include a regression test in bd0d893?

Didn't really see much point.  It's not like anybody's likely to
take out the collation handling now that it's there.

            regards, tom lane