Re: LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...

Sorry about that,=A0=A0 here is a script to demonstrate using a much simple=
r example.
It presumes linux and that there is a bash shell at /bin/bash.

You should see table created and loaded (takes a few minutes),=A0=A0 then s=
uccessful query explain and run,
then create index and re-run the explain (now uses index) and query (now fa=
ils)

John

############################################### snip

#!/bin/bash
#=A0=A0 demonstrate bug :=20
#=A0=A0 LIKE predicate and ERROR:=A0 42P22: could not determine which colla=
tion to use for string comparison -
#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 HINT:=A0 Use the COLLATE clause ...132
#=A0=A0 WARNING=A0=A0 this script will (try to) drop and recreate a databas=
e named LIKE_42P22

echo "WARNING=A0=A0 this script will (try to) drop and recreate a database =
named LIKE_42P22
press enter to continue or Ctl-C to cancel"
read nothing

export LANG=3D"C"
export LC_ALL=3D"C"
export LC_LOCALE=3D"C"
psql -d postgres -c "DROP DATABASE IF EXISTS \"LIKE_42P22\"";
LANG=3D"C" LC_LOCALE=3D"C" createdb -T template0 -E UTF8 LIKE_42P22;
psql -d LIKE_42P22 -c "CREATE TABLE entity ( id bigint NOT NULL, discrimina=
tor character varying(255) NOT NULL )";
date;declare -i rc=3D0 id=3D1 count=3D0 index remdr;
declare -a ALPHABET=3D(A B C D E F G H I J K L M N O P Q R S T U V W X Y Z);
while [[ $rc -eq 0 ]] && [[ $id -le 10000 ]]; do {
=A0=A0=A0=A0 ((index=3Did/500)); ((remdr=3Did%20));discriminator=3D"${ALPHA=
BET[$index]}EPLOY${remdr}padding";
=A0=A0=A0=A0 psql -d LIKE_42P22 -c "INSERT INTO entity values ( $id , '${di=
scriminator}' )" >/dev/null;rc=3D$?;
=A0=A0=A0=A0 [[ $rc -eq 0 ]] && ((count=3Dcount+1)); ((id=3Did+1));
}; done; echo "rc=3D $rc inserted $count entities $(date)";
psql -d LIKE_42P22 -c "ANALYZE entity";

#=A0=A0 display catalog metadata
psql -d LIKE_42P22 -c "select datname, datdba, encoding, datcollate, datcty=
pe, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid,=
 dattablespace from pg_database where datname =3D 'LIKE_42P22'";
psql -d LIKE_42P22 -c "select N.nspname , C.relname, A.attrelid , A.attname=
 , A.atttypid , T.typname , L.collname from pg_attribute A, pg_type T, pg_c=
lass C , pg_namespace N , pg_collation L where C.oid =3D A.attrelid and T.o=
id =3D A.atttypid and A.attcollation =3D L.oid and C.relnamespace =3D N.oid=
 and N.nspname =3D 'public' and C.relname =3D 'entity' and A.attname =3D 'd=
iscriminator'";

#=A0 run the problem query before any index on discriminator=A0 -=A0 it sho=
uld work
psql -d LIKE_42P22 -c "EXPLAIN select count(*) from entity as e1 where e1.d=
iscriminator LIKE 'DEPLOY%'";
psql -d LIKE_42P22 -c "select Count(*) from entity as e1 where e1.discrimin=
ator like 'DEPLOY%'"; # should be 500 matches

#=A0=A0 now create index and the bug will strike
psql -d LIKE_42P22 -c "CREATE INDEX entity_discriminator ON entity USING bt=
ree (discriminator)";
psql -d LIKE_42P22 -c "ANALYZE entity";
psql -d LIKE_42P22 -c "EXPLAIN select count(*) from entity as e1 where e1.d=
iscriminator LIKE 'DEPLOY%'";
psql -d LIKE_42P22 -c "select count(*) from entity as e1 where e1.discrimin=
ator LIKE 'DEPLOY%'";=A0 #=A0 should fail
############################################### snip


----------------------------------------
> To: johnlumby@hotmail.com
> CC: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] LIKE predicate and ERROR: 42P22: could not determine =
which collation to use for string comparison - HINT: Use the COLLATE clause=
 ...
> Date: Sat, 10 Dec 2011 03:26:13 -0500
> From: tgl@sss.pgh.pa.us
>
> John Lumby <johnlumby@hotmail.com> writes:
> > psql -d proteusdb -c "select count(*) from entity  as e1 where ( e1.ass=
ociation_id IN ( select id from entity as e2 where e2.parent_id=3D3043508) =
  OR e1.association_id=3D3043508 ) and e1.discriminator LIKE 'DEPLOY%'";
> > ERROR:  could not determine which collation to use for string comparison
> > HINT:  Use the COLLATE clause to set the collation explicitly.
>
> FWIW, I tried to replicate this on the basis of the limited information
> you gave, and could not. Can you provide a self-contained test case?
>
> regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
=20=09=09=20=09=20=20=20=09=09=20=20