Ruby2.6のInfiniteRangeをActiveRecordで利用した時の挙動を各RDBMSに対して調査した

TL;DR

User.where(id: 1..).to_sql
#=> "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" BETWEEN 1 AND NULL"

— . (@y_yagi) 2018年12月26日

NULLが全ての有理数より大きく振舞うか小さく振舞うかがRDBMS依存なので不定な振舞いのSQLとも言える

— Ryuta Kamizono (@kamipo) 2018年12月26日

こちらのtweetで言及されているNULLの挙動について軽く調べました。
調査対象はMySQL, PostgreSQL, SQLite3, Oracleの4つです。
今回調査したRDBMSでは結果は同一で、BETWEENにおいてどちらかにNULLを利用すると、結果はNULLとなるようです。

詳細

ruby2.6で追加されたInfinite RangeをActiveRecordで利用した時の挙動について調べました。

Infinite RangeをActiveRecordのin句で下記のように利用すると

User.where(id: (0..)).to_sql

このようなSQLが作成されます。

"SELECT `users`.* FROM `users` WHERE `users`.`id` BETWEEN 0 AND NULL"

RDBMSによって細かい箇所が微妙に異なりますがそこは各自補完してください。
このSQLの結果がどの方になるかを各RDBMSに対して調査しました。

今回調べたのはMySQL, PostgreSQL, SQLite3, Oracleの4種類です。

結論から言うと、この4種類のRDBMSでの上記SQLの結果は同じものとなりました。

empty setが返ってきます。

実行例

PostreSQL

postgresql

posgre_development=# SELECT * FROM users WHERE users.id BETWEEN 0 AND NULL;
 id | created_at | updated_at 
----+------------+------------
(0 rows)

MySQL

mysql

mysql> SELECT `users`.* FROM `users` WHERE `users`.`id` BETWEEN 0 AND NULL;
Empty set (0.00 sec)

SQLite

sqlite

sqlite> SELECT * FROM users WHERE users.id BETWEEN 0 AND 1;
1|2019-01-09 16:19:13.215559|2019-01-09 16:19:13.215559
sqlite> SELECT * FROM users WHERE users.id BETWEEN 0 AND NULL;
sqlite> 

(sqliteは結果がemptyの場合結果を何も表示しないようです。)

Oracle

SELECT * FROM CUSTOMERS WHERE CUSTOMERS.ID BETWEEN 1 AND NULL;

no rows selected

クエリを逆にした場合

ちなみに、BETWEEN句を逆にした下記のようなクエリについても調べました。

"SELECT `users`.* FROM `users` WHERE `users`.`id` BETWEEN NULL AND 10"

これは、記事冒頭のtweetで言及されているNULLの振る舞いについて調べるためです。

結論から書くと、こちらも同様の結果となり、結果はempty setでした。

PostreSQL

posgre_development=# SELECT * FROM users WHERE users.id BETWEEN NULL AND 129;
 id | created_at | updated_at 
----+------------+------------
(0 rows)

MySQL

mysql

mysql> SELECT `users`.* FROM `users` WHERE `users`.`id` BETWEEN NULL AND 10;
Empty set (0.00 sec)

SQLite

sqlite

sqlite> SELECT * FROM users WHERE users.id BETWEEN NULL AND 10;
sqlite> 

Oracle

SQL> SELECT * FROM CUSTOMERS WHERE (CUSTOMERS.ID NOT IN (SELECT CUSTOMER_WITHDRAWALS.CUSTOMER_ID FROM CUSTOMER_WITHDRAWALS)) AND (CUSTOMERS.ID BETWEEN NULL AND 10);

no rows selected

まとめ

NULLと絡んだSQLクエリの結果は不定となり、NULLとなるようです。
他のRDBMSについてはまた余裕があれば調べようかと思います。

調査に際して、下記のリポジトリを利用しました。
https://github.com/colorbox/2.6-rails-range-research