SV: Bug in index scans with Locale support enabled
От | Jarmo Paavilainen |
---|---|
Тема | SV: Bug in index scans with Locale support enabled |
Дата | |
Msg-id | 00d701c061ca$c93d4de0$1501a8c0@theboss.comder.private обсуждение исходный текст |
Ответ на | Bug in index scans with Locale support enabled (Barry Lind <barry@xythos.com>) |
Список | pgsql-general |
Hi, ... > In researching a problem I have uncovered the following bug in index > scans when Locale support is enabled. ... > environment variable is set to en_US) to enable the US english locale ... > create table test (test_col text); > create index test_index on test (test_col); > insert into test values ('abc.xyz'); > insert into test values ('abcxyz'); > insert into test values ('abc/xyz'); > > If you run the query: > > select * from test where test_col >= 'abc.'; > > One would normally expect to only get one record returned, but instead > all records are returned. I would expect all to be returned (maybe not "abc/..."). Because noice should be sorted first. ie. '.' is less than '0' and 'x' (and maybe '/'). ... > The reason for this is that in the en_US locale all non-alphanumeric > characters are ignored when doing string comparisons. So the data above ...or... *I think* they are sorted first. If that is correct in your locale, I do not know. ... > Note that if you use a different locale for example en_UK, you will get Thats odd, I would expect en_UK and en_US to sort the same way (same charset). ... > select * from text where test_col like 'abc/%'; > > This query should return one row, the row for 'abc/xyz'. However if the > above query is executed via an index scan it will return the wrong > number of rows (0 in this case). ehh index scan? test_col >= 'abc/' or test_col >= 'abc/%' ???? The first one should return all rows but the one with '.', while the second should return 0 rows. If the first one returns zero rows, then its a bug. If you meant what the optimizer does with LIKE, well *I think* such optimazion is asking for trouble (compare strings with anything else than = and != are, well hard to predict). ... > "like '/aaa/bbb/%' don't work. From the above information I have > determined that such queries don't work if: > a) database is built with Locale support enabled (--enable-locale) Actually they should not work without '--enable-locale', or then Im wrong. > b) the database is running with locale en_US > c) the column the like is being performed on is indexed Dangerous LIKE optimation. ... > The current implementation for converting the like into an index scan > doesn't work with Locale support enabled and the en_US locale as shown Hmm. If memory serves its dropped in the later builds (no like optimation). // Jarmo
В списке pgsql-general по дате отправления: