时间戳

首页 > 解决方案 > 为什么这个双重否定查询与肯定查询不同？

sql - 为什么这个双重否定查询与肯定查询不同？

问题描述

当我进行两个应该选择同一组人的查询时，它们会得到不同的结果。逻辑是一样的，只是数字不同。

这是我的模型： 具有年龄的人员类：整数名称：字符串宠物：字符串

class Person < ApplicationRecord
  PET_TYPES = ['dog', 'cat', 'bird', 'fish']
  validates :pet, inclusion: { in: PET_TYPES }, allow_nil: true
  validates :age, numericality: { greater_than: 0 }
end

在我的种子文件中，我已经在数据库中填满了拥有 21 岁以下或 21 岁以上的每种宠物（包括零）的人：

Person.all.destroy_all
Person::PET_TYPES.each do |pet|
    10.times do |n|
        Person.create!(name: "Person-young-#{pet}-#{n}", pet: pet, age: (1..20).to_a.sample)
    end
    10.times do |n|
        Person.create!(name: "Person-old-#{pet}-#{n}", pet: pet, age: (21..80).to_a.sample)
    end
end
10.times do |n|
    Person.create!(name: "Person-young-no-pet-#{n}", pet: nil, age: (1..20).to_a.sample)
end
10.times do |n|
    Person.create!(name: "Person-old-no-pet-#{n}", pet: nil, age: (21..80).to_a.sample)
end

我执行了以下两个查询，它们应该选择同一组人。但我得到不同的数字。

Person.count
#>>   (1.0ms) SELECT COUNT(*) FROM "people"
#=> 100
Person.where.not(id: Person.where.not('age > ? AND pet = ?', 21, 'dog')).count
#>>   (1.7ms) SELECT COUNT(*) FROM "people" WHERE "people"."id" NOT IN (SELECT "people"."id" FROM "people" WHERE NOT (age > 21 AND pet = 'dog'))
#=> 20
Person.where('age > ? AND pet = ?', 21, 'dog').count
#>>   (1.0ms) SELECT COUNT(*) FROM "people" WHERE (age > 21 AND pet = 'dog')
#=> 10

这两个语句不应该返回相同的数字吗？

标签： sqlruby-on-railspostgresql

解决方案

在 SQL 中，null不等于 ( =) 到任何东西——甚至不等于另一个null. 而且，这是三值逻辑的一个常见例子。当您null与其他任何事物进行比较时，它既不是 true 也不是 false；它是UNKNOWN。

基本的“问题”是（如在此处更深入地讨论）where.not(x: true)即WHERE x != true；它不是的否定形式WHERE x == true。

让我们将您的问题分解为一个最小、完整、可验证的示例：

Person.count
   (1.2ms)  SELECT COUNT(*) FROM "people"
=> 100
Person.where(pet: 'dog').count
   (1.0ms)  SELECT COUNT(*) FROM "people" WHERE "people"."pet" = 'dog'
=> 20
Person.where.not(pet: 'dog').count
   (1.4ms)  SELECT COUNT(*) FROM "people" WHERE "people"."pet" != 'dog'
=> 60
Person.where.not(pet: 'dog').pluck(:pet).uniq
=> ["cat", "bird", "fish"]

如您所见，null两个结果都缺少有宠物的人。

总而言之，由于三值逻辑，您尝试双重否定查询实际上会导致不同的结果集。

Person.where.not('age > ? AND pet = ?', 21, 'dog')包括除了有狗的 老人和没有宠物的老人以外的所有人。

推荐阅读