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 NATURAL JOIN sectors WHERE
sites. config_site_name != sectors.sect_name ;", cursorOptions=256,
boundParams=0x0) at planner.c:405
