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
