String comparison problem in select - too many results

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: String comparison problem in select - too many results
Date: 2018-01-10 12:21:15
Message-ID: CAEcMXhmFVUmAJB1D_BQahfWjQENNWqeuhz6HcD4_crm=XqLQNQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Members!

Today one of hour clients reported an error.

She made a report filtered with string range, and she got wrong documents.

I checked it.
She filtered by a document nr (varchar field -> character varying(15)).

The range was: '18/0113', and '18/0212'.

Q.Close;
Q.SQL.Text := 'select * from idoc where nr >=:pfrom and nr <=:pto';
Q.paramByName('pfrom').Asstring := PFrom;
Q.paramByName('pto').Asstring := PTo;
Q.Open;

This devart TPGQuery uses parameters, the query prepared before call.

She said that she got nr = '180/2010' document too (and much more).

Firstly I tried to check this in LibreOffice calc, because it seems to be
impossible.

Lesser Greater
'18/0113 '180/2010 True False
'18/0212 '180/2010 True False
It's ok.

Then I tried to use the select in the database.
I also got 180/2010 in the list!!!

Then I tried a simple check with SQL:

select
'18/0113' > '180/2010',
'18/0113' < '180/2010',
'18/0212' > '180/2010',
'18/0212' < '180/2010'
---
f;t;t;f

Whaaaaat????

It's impossible.
First I assumed the "/" is special char like escape "\". But in this case
the pretag is "18/0" in every input string!!!

Next I supposed that PGSQL converts this expression and the numeric value
could be different.
Without calculation I changed the select to:

select
cast('18/0113' as text) > cast('180/2010' as text),
cast('18/0113' as text) < cast('180/2010' as text),
cast('18/0212' as text) > cast('180/2010' as text),
cast('18/0212' as text) < cast('180/2010' as text),
replace(cast('18/0113' as text), '/', '_') > replace(cast('180/2010' as
text), '/', '_'),
replace(cast('18/0212' as text), '/', '_') > replace(cast('180/2010' as
text), '/', '_')

To be sure the strings must be strings. I use replace at the end to filter
"/" sign.
The result is also wrong:
f;t;t;f;f;t

Please help me in this theme!

What can cause differences between similar strings?
How can I force the good range?
What is the base of the problem?

The PG is 9.4 on Linux, the DataBase encoding is:
ENCODING = 'UTF8'
LC_COLLATE = 'hu_HU.UTF-8'
LC_CTYPE = 'hu_HU.UTF-8'

Thank you for your help!

Best regards
dd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2018-01-10 14:19:07 Re: help with generation_series in pg10
Previous Message Glauco Torres 2018-01-10 11:49:01 Segmentation fault with core dump