'BETWEEN AND', inclusive or exclusive?
unknownzerx opened this issue · 2 comments
unknownzerx commented
Should 'BETWEEN AND' be inclusive or exclusive, or configurable by dialect?
araddon commented
Thanks for the issue, let me find some unit-tests and examples from other dialects and implement in unit tests here.
araddon commented
It appears that it is dialect specific but most dialects are inclusive https://english.stackexchange.com/questions/118402/when-is-between-inclusive-and-when-exclusive
Which means that currently this implementation is exclusive, but probably should be inclusive. Let me see if i can switch it without breaking too much.
- MySql appears to be Inclusive: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between
- Postgres appears to be inclusive as well. Although it appears to always return bools.
mysql> SELECT
-> 1 BETWEEN 1 AND 3 AS a,
-> 3 BETWEEN 1 AND 3 AS b,
-> 3 BETWEEN 1 AND '3' AS c,
-> 2 BETWEEN 3 and 1 AS d,
-> 3.0 BETWEEN 1 AND 3 as e,
-> 3 BETWEEN NULL AND 1 as f,
-> 3 BETWEEN NULL AND 4 AS g,
-> CAST("2017-03-03" AS DATE) BETWEEN CAST("2017-01-03" AS DATE) AND CAST("2017-12-03" AS DATE) AS d1,
-> CAST("2015-03-03" AS DATE) BETWEEN CAST("2017-01-03" AS DATE) AND CAST("2017-12-03" AS DATE) AS d2,
-> CAST("2017-03-03" AS DATE) BETWEEN CAST("2017-01-03" AS DATE) AND "2017-12-03" AS d3
-> ;
+---+---+---+---+---+------+------+------+------+------+
| a | b | c | d | e | f | g | d1 | d2 | d3 |
+---+---+---+---+---+------+------+------+------+------+
| 1 | 1 | 1 | 0 | 1 | 0 | NULL | 1 | 0 | 1 |
+---+---+---+---+---+------+------+------+------+------+
1 row in set (0.00 sec)
postgres=# SELECT
postgres-# 1 BETWEEN 1 AND 3 AS a,
postgres-# 3 BETWEEN 1 AND 3 AS b,
postgres-# 3 BETWEEN 1 AND '3' AS c,
postgres-# 2 BETWEEN 3 and 1 AS d,
postgres-# 3.0 BETWEEN 1 AND 3 as e,
postgres-# 3 BETWEEN NULL AND 1 as f,
postgres-# 3 BETWEEN NULL AND 4 AS g;
a | b | c | d | e | f | g
---+---+---+---+---+---+---
t | t | t | f | t | f |
(1 row)