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: [email protected]
> CC: [email protected]
> 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: [email protected]
>
> John Lumby <[email protected]> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
