Records with null enum values are omitted in `.not_` enum scopes

[aliismayilov]

Steps to reproduce

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  # If you want to test against edge Rails replace the previous line with this:
  # gem "rails", github: "rails/rails", branch: "main"

  gem "sqlite3"
end

require "active_record/railtie"
require "minitest/autorun"

# This connection will do for database-independent bug reports.
ENV["DATABASE_URL"] = "sqlite3::memory:"

class TestApp < Rails::Application
  config.load_defaults Rails::VERSION::STRING.to_f
  config.eager_load = false
  config.logger = Logger.new($stdout)
  config.secret_key_base = "secret_key_base"

  config.active_record.encryption.primary_key = "primary_key"
  config.active_record.encryption.deterministic_key = "deterministic_key"
  config.active_record.encryption.key_derivation_salt = "key_derivation_salt"
end
Rails.application.initialize!

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.string :state
  end
end

class Post < ActiveRecord::Base
  enum :state, %w[pending published]
end

class BugTest < ActiveSupport::TestCase
  def test_enum_null_values
    pending_post = Post.create!(state: "pending")
    published_post = Post.create!(state: "published")
    null_state_post = Post.create!(state: nil)

    assert_equal [published_post], Post.published.sort_by(&:id)
    assert_equal [pending_post, null_state_post], Post.not_published.sort_by(&:id)
  end
end

Expected behavior

I'd expect .not_published to include all posts that are not published, including the one with no state.

Actual behavior

Due to how SQL works, null value is treated separately and only records where the value is present and not equal to the given value are returned.

System configuration

Rails version: 8.0.3

Ruby version: 3.4.6