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

Sorry about that,   here is a script to demonstrate using a much simpler 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),   then successful query explain and run,
then create index and re-run the explain (now uses index) and query (now fails)

John

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

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

echo "WARNING   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="C"
export LC_ALL="C"
export LC_LOCALE="C"
psql -d postgres -c "DROP DATABASE IF EXISTS \"LIKE_42P22\"";
LANG="C" LC_LOCALE="C" createdb -T template0 -E UTF8 LIKE_42P22;
psql -d LIKE_42P22 -c "CREATE TABLE entity ( id bigint NOT NULL, discriminator character varying(255) NOT NULL )";
date;declare -i rc=0 id=1 count=0 index remdr;
declare -a ALPHABET=(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 {
     ((index=id/500)); ((remdr=id%20));discriminator="${ALPHABET[$index]}EPLOY${remdr}padding";
     psql -d LIKE_42P22 -c "INSERT INTO entity values ( $id , '${discriminator}' )" >/dev/null;rc=$?;
     [[ $rc -eq 0 ]] && ((count=count+1)); ((id=id+1));
}; done; echo "rc= $rc inserted $count entities $(date)";
psql -d LIKE_42P22 -c "ANALYZE entity";

#   display catalog metadata
psql -d LIKE_42P22 -c "select datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, dattablespace from pg_database where datname = '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_class C , pg_namespace N , pg_collation L where C.oid = A.attrelid and T.oid = A.atttypid and A.attcollation = L.oid and C.relnamespace = N.oid and N.nspname = 'public' and C.relname = 'entity' and A.attname = 'discriminator'";

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

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

----------------------------------------
> To: johnlumby(at)hotmail(dot)com
> CC: pgsql-bugs(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us
>
> John Lumby <johnlumby(at)hotmail(dot)com> writes:
> > psql -d proteusdb -c "select count(*) from entity as e1 where ( e1.association_id IN ( select id from entity as e2 where e2.parent_id=3043508) OR e1.association_id=3043508 ) 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs