araddon/qlbridge

'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> 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)
  • 👍1