MySQL Bugs: #46867: Support SQL 2003 SYMMETRIC/ASYMMETRIC options for BETWEEN clause

Bug #46867	Support SQL 2003 SYMMETRIC/ASYMMETRIC options for BETWEEN clause
Submitted:	23 Aug 2009 1:32	Modified:	23 Aug 2009 10:12
Reporter:	Justin Swanhart	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.4.1, 5.1.15,5.0.51a	OS:	Linux (CentOS 5.2)
Assigned to:		CPU Architecture:	Any

Description:
This query (between 1 and 2) returns results:
---------------------------
select d.i1, count(*) from f join d using(i1) where d.i2=1 and f.i1 between 1 and 2;

This query returns empty set and the EXPLAIN plan detects and "impossible where clause", which is clearly not impossible:
---------------------------
select d.i1, count(*) from f join d using(i1) where d.i2=1 and f.i1 between 2 and 1;

I encountered this bug on an older 5.1, then tested on newest 5.4 and found the exact same problem.  This may be a problem in many versions.

How to repeat:
mysql> create table f ( id bigint auto_increment primary key, i1 tinyint ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into f values (null, 1),(null,2),(null,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table d (i1 tinyint, i2 tinyint, primary key(i1,i2)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into d values (1,1),(1,2),(2,1),(2,2),(3,1),(3,2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> explain select d.i1, count(*) 
          from f join d
         using(i1) 
         where d.i2=1 
           and f.i1 between 1 and 2;
+----+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | f     | ALL    | NULL          | NULL    | NULL    | NULL               |    3 | Using where |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 2       | example.f.i1,const |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select d.i1, count(*) 
          from f join d 
         using(i1) 
          where d.i2=1 
           and f.i1 between 2 and 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

here is an easy way to duplicate w/out creating any tables:

mysql> select 1 from dual where 5 between 1 and 10;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 from dual where 5 between 10 and 1;
Empty set (0.00 sec)

duplicated on 5.0.51a as well:

[root@localhost mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.51a-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> (select 'Yes' from dual where 5 between 10 and 1 ) union (select 'No') limit 1;
+-----+
| Yes |
+-----+
| No  | 
+-----+
1 row in set (0.08 sec)

mysql>

Sorry, but this is NOT a bug. c BETWEEN a AND b is always interpreted as:

(c >= a) and (c <= b)

so if b < a there are no rows that can match this condition. Read http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_between also.

Then an error should be raised in min is greater than max.  The empty result is wrong.

Regardless, I am reopening as feature request for the SQL2003 standard BETWEEN clause:
From the SQL:2003 spec, foundation, section 8.3:

<between predicate part 2> ::= 
    [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] 
    <row value predicand> AND <row value predicand> 

1) If neither SYMMETRIC nor ASYMMETRIC is specified, then ASYMMETRIC is implicit. 
...
6) “X BETWEEN ASYMMETRIC Y AND Z” is equivalent to “X>=Y AND X<=Z”.

Changed synopsis.

I agree that it's not a bug.  As far as I can tell, the mysql behavior matches the SQL:2003 standard.