Обсуждение: Bug #821: bug when using between or <= on varchar fields
Thomas Piszczek (t.piszczek@tallence.com) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description bug when using between or <= on varchar fields Long Description I just recognized that when using between or <= and >= on varchar fields postgresql behaves quite strange. If i search forusers in my user table which does contain last_name entries beginning with o or p and have an sql that searches for "lower(last_name)>='k'and lower(last_name)<='o'" i get all results between k and n, but no "o-entries". If i do a "lower(last_name)>='k'and lower(last_name)<=p it works. I then get all results between k and o(no p's). Using "between 'k' and 'o'" behaves just the same. I think this is a bug(i'm using postgresql 7.2.3 on suse linux 8.1 with php 4.2.3) Sample Code SELECT user_id,....... FROM cma_user WHERE LOWER(last_name)>='k' AND LOWER(last_name)<='o' SELECT user_id,....... FROM cma_user WHERE LOWER(last_name) BETWEEN 'k' AND 'o' No file was uploaded with this report
On Tue, 19 Nov 2002 pgsql-bugs@postgresql.org wrote: > Thomas Piszczek (t.piszczek@tallence.com) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > bug when using between or <= on varchar fields > > Long Description > I just recognized that when using between or <= and >= on varchar > fields postgresql behaves quite strange. If i search for users in my > user table which does contain last_name entries beginning with o or p > and have an sql that searches for "lower(last_name)>='k' and > lower(last_name)<='o'" i get all results between k and n, but no > "o-entries". If i do a "lower(last_name)>='k' and lower(last_name)<=p > it works. I then get all results between k and o(no p's). > Using "between 'k' and 'o'" behaves just the same. > I think this is a bug(i'm using postgresql 7.2.3 on suse linux 8.1 with php 4.2.3) The only entry that should give you is one that was equal to 'o', not ones starting with 'o'. If you want that then you want to get a substring of one letter for the comparison.