** Description changed: /* 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. - */ + /* Workaround: This hacks around the above problem shows 1800+ records + without having to give up the "like" clause. But it's ugly. */ 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 '%';
-- 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 [email protected] https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
