Unexplained case insensitive results

From: "Lloyd Mason" <LloydM(at)pbsinet(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Unexplained case insensitive results
Date: 2007-03-30 20:44:13
Message-ID: C34A4F8A078F5E4285EB8B10FF0DAF31182780@pbsi-sbsxch.PosBus.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

There appears to be a difference in the results from the following SQL
statement when run on an 8.1.5 version versus a 8.1.8 version:

SELECT T1.MRPtPro AS MRPtPro, T1.MRPtDOB, T1.MRPtSSN, T1.MRPtNbr,
T2.MRMDNam AS MRPtProN, T1.MRPtChtCmpFlg, T1.MRPtPhA, T1.MRPtPhP,
T1.MRPtPhS, T1.MRPtFNam, T1.MRPtLNam, T1.MRPtSts, T1.PrtNbr FROM (MR0011
T1 LEFT JOIN MR00051 T2 ON T2.PrtNbr = T1.PrtNbr AND T2.MRMDNbr =
T1.MRPtPro) WHERE (T1.PrtNbr = 1 and T1.MRPtSts = 'A' and T1.MRPtLNam >=
'person ') AND (T1.MRPtLNam < 'persoo
') ORDER BY T1.PrtNbr, T1.MRPtSts,

T1.MRPtLNam, T1.MRPtFNam

The column MRPtLAN has both upper and lower case data.

The 8.1.5 version shows all matches both upper and lower case. While
the 8.1.8 version shows only those entries that are lower case. Any
ideas as to why this is?

We would like it to show all matches (case-insensitive) that are
returned in 8.1.5 but would like to be on a newer version of the
database 8.1.8 and eventually 8.2.X.

Both databases are UTF-8 encoding and running on REDHAT EL 4.0. Both
databases were created from a pg_dump that came out of a 7.4.5 version
of Postgresql with SQL_ASCII encoding. I don't think the encoding
should matter but included it for your review.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message csaba 2007-03-30 21:21:53 Re: PANIC: unexpected hash relation size
Previous Message Tom Lane 2007-03-30 18:57:30 Re: unexpected data beyond EOF and character encoding