Public bug reported:
/* This shows 1800+ records. Note that foo_tbl values are all title
case (except one record), while all values in bar_tbl are all uppercase.
This is why "collate nocase" is important */
select foo_tbl.name,trim(bar_tbl.name),foo_tbl.host,bar_tbl.host from
foo_tbl join bar_tbl on bar_tbl.uid = foo_tbl.uid where
trim(foo_tbl.name) = trim(bar_tbl.name) collate nocase;
/* Adding 'and foo_tbl.host like "%"' should have no effect, but in fact
only 1 record is shown. foo_tbl has 1 record where the capitalization
matches. This indicates that the new "like" condition is breaking the
"collate nocase" */
select foo_tbl.name,trim(bar_tbl.name),foo_tbl.host,bar_tbl.host from
foo_tbl join bar_tbl on bar_tbl.uid = foo_tbl.uid where
trim(foo_tbl.name) = trim(bar_tbl.name) and foo_tbl.host like '%'
collate nocase;
/* Workaround: This hacks around the above problem shows 1800+ records.
*/
select * from (select foo_tbl.name,trim(bar_tbl.name),foo_tbl.host,host
from foo_tbl join bar_tbl on bar_tbl.uid = foo_tbl.id where
trim(foo_tbl.name) = trim(bar_tbl.name) collate nocase) where
foo_tbl.host like '%';
** Affects: sqlite (Ubuntu)
Importance: Undecided
Status: New
--
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
https://bugs.launchpad.net/bugs/1935724
Title:
adding a "like" clause breaks "collate nocase"
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/sqlite/+bug/1935724/+subscriptions
--
ubuntu-bugs mailing list
ubuntu-bugs@lists.ubuntu.com
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
