SQLの三値論理の挙動をRuby + sqlite + activerecord + bundle/inlineで確認する

下記の記事を読んで、なんとなく知ってたような気もするが普段意識してないし実際ハマりそうだなと思って挙動を確認した。

火の用心！NULL値1個、バグの元！

最近 bundle/inlineを知って、いいなと思ったので使っている。 pythonならuvで同様のことができるが、RubyはActiveRecordがORMとして定番なのが良い。

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'sqlite3'
  gem 'activerecord', require: 'active_record'
end

require 'active_record'

# SQLite3データベースへの接続設定
ActiveRecord::Base.establish_connection(
  adapter: 'sqlite3',
  database: ':memory:'
)

# usersテーブルの作成
ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
    t.string :name
    t.integer :age
  end
end

# Userモデルの定義
class User < ActiveRecord::Base
end

# データの挿入
User.create(name: '田中', age: 30)
User.create(name: '田中', age: nil)
User.create(name: '鈴木', age: nil)
User.create(name: nil, age: 20)
User.create(name: nil, age: nil)

# データの確認 マークダウンのテーブル形式で出力
puts 'Initial Data:'
puts '| name  | age |'
puts '|-------|-----|'
User.all.each do |user|
  puts "| #{user.name.nil? ? 'NULL' : user.name} | #{user.age.nil? ? 'NULL' : user.age} |"
end

puts '---'

# 条件1: NOT(name = '田中' AND age IS NULL)
puts 'Condition 1: NOT(name = "田中" AND age IS NULL)'
users = User.where.not(name: '田中', age: nil)
users.each { |user| p user.attributes }
puts

# 条件2: NOT(name = '田中' AND age IS NULL) OR (name IS NULL AND age IS NULL)
puts 'Condition 2: NOT(name = "田中" AND age IS NULL) OR (name IS NULL AND age IS NULL)'
users = User.where.not(name: '田中', age: nil).or(User.where(name: nil, age: nil))
users.each { |user| p user.attributes }
puts

# 条件3: NOT(name IS NOT DISTINCT FROM '田中' AND age IS NULL)
puts 'Condition 3: NOT(name IS NOT DISTINCT FROM "田中" AND age IS NULL)'
users = User.where.not("name IS NOT DISTINCT FROM '田中' AND age IS NULL")
users.each { |user| p user.attributes }

# UNKNOWNの挙動確認
puts "---"
puts "Checking '田中' evaluation behavior: evaluation of '田中' is UNKNOWN when name is NULL"
users = User.select("users.*, (name = '田中') AS name_is_tanaka")
puts '| name  | name = 田中 |'
puts '|-------|------------|'
users.each do |user|
  puts "| #{user.name.nil? ? 'NULL' : user.name} | #{user.name_is_tanaka} |"
end
puts

出力

condition1が意図しない結果、2と3があるべき実装。

➜  ruby sql_null.rb
-- create_table(:users, {force: true})
   -> 0.0062s
Initial Data:
| name  | age |
|-------|-----|
| 田中 | 30 |
| 田中 | NULL |
| 鈴木 | NULL |
| NULL | 20 |
| NULL | NULL |
---
Condition 1: NOT(name = "田中" AND age IS NULL)
{"id" => 1, "name" => "田中", "age" => 30}
{"id" => 3, "name" => "鈴木", "age" => nil}
{"id" => 4, "name" => nil, "age" => 20}

Condition 2: NOT(name = "田中" AND age IS NULL) OR (name IS NULL AND age IS NULL)
{"id" => 1, "name" => "田中", "age" => 30}
{"id" => 3, "name" => "鈴木", "age" => nil}
{"id" => 4, "name" => nil, "age" => 20}
{"id" => 5, "name" => nil, "age" => nil}

Condition 3: NOT(name IS NOT DISTINCT FROM "田中" AND age IS NULL)
{"id" => 1, "name" => "田中", "age" => 30}
{"id" => 3, "name" => "鈴木", "age" => nil}
{"id" => 4, "name" => nil, "age" => 20}
{"id" => 5, "name" => nil, "age" => nil}
---
Checking '田中' evaluation behavior: evaluation of '田中' is UNKNOWN when name is NULL
| name  | name = 田中 |
|-------|------------|
| 田中 | 1 |
| 田中 | 1 |
| 鈴木 | 0 |
| NULL |  |
| NULL |  |

うーん確かに。

最後のクエリ例でわかるとおり、nameがNULLのときに name='田中' の評価結果はUNKNOWNになるので、unknown and true -> unknownになるという理解。

意識しておかないといつかハマりそうなのでメモ。