Date: Tue, 21 Jul 2020 14:16:06 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Subject: v13 planner ERROR: could not determine which collation to use for
 string comparison
Message-ID: <20200721191606.GL5748@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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