mysql BETWEEN is exclusive on right for characters?

Question

Consider this table named easy_drinks,

+------------------+
| drink_name       |
+------------------+
| Kiss on the Lips |
| Hot Gold         |
| Lone Tree        |
| Greyhound        |
| Indian Summer    |
| Bull Frog        |
| Soda and It      |
| Blackthorn       |
| Blue Moon        |
| Oh My Gosh       |
| Lime Fizz        |
+------------------+

A query as such,

 select drink_name from easy_drinks where drink_name BETWEEN 'G' and 'O';

results in

+------------------+
| drink_name       |
+------------------+
| Kiss on the Lips |
| Hot Gold         |
| Lone Tree        |
| Greyhound        |
| Indian Summer    |
| Lime Fizz        |
+------------------+

drink names starting with O are not included in the result. But as per the manual page

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN >returns 1, otherwise it returns 0.

Why is the query giving such results?

I have been through questions that explain the behaviour for Timestamp and Date. What is the reason in this case?

Answer 1

tl;dr

Don't use BETWEEN for strings

where drink_name >= 'G' and drink_name < 'P';

Why?

The O is effectivly expanded with trailing spaces to match the column. So

'O                  '

is before

'Oh My Gosh'

So you'd need

where drink_name BETWEEN 'G' and 'OZ';

If you have a drink called Ozymandias then this won't work. So:

where drink_name BETWEEN 'G' and 'OZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ';

But, can we can safely assume that there is no drink called P and many spaces?
This is not understandable.

select drink_name from easy_drinks
where drink_name BETWEEN 'G' and 'P';

The obvious choice might be to compare only first letters using LEFT

select drink_name from easy_drinks
where LEFT(drink_name, 1) BETWEEN 'G' and 'O';

But this will prevent used of any index on drink_name.

Answer 2

Here's an easier way to spot what's going on:

select drink_name, drink_name >='G', drink_name <='O'
from easy_drinks
order by 1;

Key results are:

• Greyhound is >= 'G'
• Oh My Gosh is not <= 'O'

It makes sense once you think about it. In a dictionary, a single O entry is always the first item in letter O.

You probably want something like this:

select drink_name
from easy_drinks
where SUBSTRING(drink_name, 1, 1) BETWEEN 'G' and 'O';

Edit:

I completely forgot about LEFT(drink_name, 1), which is a handy shorcut for SUBSTRING(drink_name, 1, 1).