MySQL Bugs: #44759: Add an extra example to documentation on BETWEEN

Bug #44759	Add an extra example to documentation on BETWEEN
Submitted:	8 May 2009 23:20	Modified:	18 May 2009 16:41
Reporter:	David Keech	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.0.38	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	between

Description:
The results returned differ depending on the order of the two values being compared in a BETWEEN statement.

How to repeat:
The simplest test case:

mysql> SELECT 2 BETWEEN 1 AND 3;
+-------------------+
| 2 BETWEEN 1 AND 3 |
+-------------------+
|                 1 | 
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 2 BETWEEN 3 AND 1;
+-------------------+
| 2 BETWEEN 3 AND 1 |
+-------------------+
|                 0 | 
+-------------------+
1 row in set (0.00 sec)

This behaviour is the same (but much more confusing) if any of the fields are columns in a table:

mysql> CREATE TABLE test (a INT, b INT, c INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (a, b, c) VALUES (1, 2, 3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test WHERE b BETWEEN a AND c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
+------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE b BETWEEN c AND a;
Empty set (0.00 sec)

To add more confusion, the query returns any row in which the order of the data in the columns matches the order of the query.  These results cannot be reproduced without using a table as it was in the first example:

mysql> INSERT INTO test (a, b, c) VALUES (3, 2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
|    3 |    2 |    1 | 
+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE b BETWEEN a AND c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
+------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE b BETWEEN c AND a;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    3 |    2 |    1 | 
+------+------+------+
1 row in set (0.00 sec)

This behaviour can be reproduced by using < and > but it makes sense in this context because it is explicitly stated that only rows where a is less than b should be returned.

mysql> SELECT * FROM test WHERE a < b AND b < c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
+------+------+------+
1 row in set (0.00 sec)

... or rows where b is less than a.

mysql> SELECT * FROM test WHERE c < b AND b < a;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    3 |    2 |    1 | 
+------+------+------+
1 row in set (0.00 sec)

Suggested fix:
Since b is between a and c and is also between c and a, it should return both rows when using the BETWEEN keyword.

The desired result can be obtained by using this query:

mysql> SELECT * FROM test WHERE b BETWEEN a AND c UNION SELECT * FROM test WHERE b BETWEEN c AND a;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
|    3 |    2 |    1 | 
+------+------+------+
2 rows in set (0.03 sec)

Also, SELECT 2 BETWEEN 3 and 1; should return 1 instead of 0.

If changing the behaviour of MySQL is not possible, adding an extra example or two to the documentation ( http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between ) to illustrate these differences would help avoid the confusion.

Thank you for the bug report. The BETWEEN predicate is (quoting the book SQL-99 Complete by Peter Gulutzan & Trudy Pelzer) 'just a shorthand for a combination of >= and <= comparisons, so the same rules that apply for comparison of specific <data type>s apply to BETWEEN as well.'

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.82-Win X64 revno 2780-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > SELECT 2 BETWEEN 1 AND 3;
+-------------------+
| 2 BETWEEN 1 AND 3 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT 2 >= 1 AND 2 <= 3;
+-------------------+
| 2 >= 1 AND 2 <= 3 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT 2 BETWEEN 3 AND 1;
+-------------------+
| 2 BETWEEN 3 AND 1 |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT 2 >= 3 and 2 <= 1;
+-------------------+
| 2 >= 3 and 2 <= 1 |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.0 > use test
Database changed
mysql 5.0 > SELECT * FROM test WHERE b BETWEEN a AND c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT * FROM test WHERE b >= a AND b <= c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT * FROM test WHERE b BETWEEN c AND a;
Empty set (0.00 sec)

mysql 5.0 > SELECT * FROM test WHERE b >= c AND b <= a;
Empty set (0.00 sec)

So this neither is a server bug nor documentation bug since is compliant with SQL Standard.

After checking I agree that MySQL is compliant with the spec and it's the spec that's confusing, so the main purpose of this bug report is resolved.

The secondary purpose was to help avoid other MySQL users from falling into the same trap I did (which was assuming that the shortcut acted the way you would expect it to in English) and, as such, becomes a feature request rather than a bug report for this example to be added to the documentation page mentioned earlier:

mysql> SELECT 2 BETWEEN 3 AND 1;
        -> 0

Thank you for the feedback. Category and Status changed.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I've added an example. However, note that the documentation already states: "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."

So if min > max, the conditions fail and BETWEEN returns 0.