v13 planner ERROR: could not determine which collation to use for string comparison

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: v13 planner ERROR: could not determine which collation to use for string comparison
Date: 2020-07-21 19:16:06
Message-ID: 20200721191606.GL5748@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David G. Johnston 2020-07-21 19:34:01 Re: v13 planner ERROR: could not determine which collation to use for string comparison
Previous Message Tom Lane 2020-07-21 18:33:23 Re: xl_heap_header alignment?