postgresql bug, query can't find the row with exact same string

Question

I have a table invoice_lines with line_description column (text, utf8). The rows are inserted by PHP code (utf8). Recently i've found the following problem:

When I run

SELECT * FROM invoice_lines
WHERE line_description ilike '%1 X Hired 1100 (LTR). Derby Road, Burton _________%'

or

SELECT * FROM invoice_lines
WHERE line_description ilike '%1 X Hired 1100 (LTR). Derby Road, Burton %'

postgres returns 81 rows with line_description like "1 X Hired 1100 (LTR). Derby Road, Burton On Trent, Staffordshire - 4 days @ £2.95 per week (05/08/2015 - 08/08/2015). Grade: Mixed Municipal Waste" (dates from 2015 to 2016)

But when I run this:

SELECT * FROM invoice_lines
WHERE line_description ilike '%1 X Hired 1100 (LTR). Derby Road, Burton On Trent%'

or

SELECT * FROM invoice_lines
WHERE line_description ilike '%1 X Hired 1100 (LTR). Derby Road, Burton O%'

postgres returns 0 rows.

When I try to get only 1 row with exact value, it also returns 0:

SELECT * FROM invoice_lines
WHERE line_description = '1 X Hired 1100 (LTR). Derby Road, Burton On Trent, Staffordshire - 4 days @ £2.95 per week (05/08/2015 - 08/08/2015). Grade: Mixed Municipal Waste'

It looks like characters starting from "On Trent, Staffordshire" are different, but the code that inserted these lines was used for millions of other records that can be found by similar queries.

Please can you tell me why this is happening?

If you have something to try, ask me in a comment section and i'll tell you the results

Nimantha · Accepted Answer · 2022-01-03 11:50:02Z

4

My guess is that ' On Trent' is not what it looks like. It is hard to figure out the actual problem. The most likely culprit is the pace between "Burton" and "Trent":

So, see if these work:

WHERE line_description ilike '%1 X Hired 1100 (LTR). Derby Road, Burton_O%'

WHERE line_description ilike '%1 X Hired 1100 (LTR). Derby Road, Burton%O%'

Otherwise it might be the "O".

edited Jan 3, 2022 at 11:50

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Oct 17, 2016 at 18:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

alex23 Over a year ago

Burton_O% returned 0 rows, Burton%O% returned 81 rows, then i tried: WHERE line_description ilike '%1 X Hired 1100 (LTR). Derby Road, Burton%On Trent, Staffordshire%' and it returned 81 rows, so you was right about the space, but what's wrong with it??

Gordon Linoff Over a year ago

@alex23 . . . Perhaps there are two spaces. Try __ and ___ for the separator, to see how many characters are there.

alex23 Over a year ago

yes, you are right,

WHERE line_description = '1 X Hired 1100 (LTR). Derby Road, Burton  On Trent, Staffordshire - 4 days @ £2.95 per week (05/08/2015 - 08/08/2015). Grade: Mixed Municipal Waste'

with 2 spaces after Burton found the row. Thank you Gordon!

Collectives™ on Stack Overflow

postgresql bug, query can't find the row with exact same string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related