Rails scope for null return incorrect result

Question

I want to find records where work_end_at IS NULL.

The type of work_end_at is datetime. And, I try this WorkingHistory.where("work_end_at IS NULL").

I got an empty result. However, record with id 532 is the one that I want to find.

ruby version: 2.1.5

rails version: 4.0.4

rails console result

WorkingHistory.where("work_end_at IS NULL")
SELECT `working_histories`.* FROM `working_histories` WHERE (work_end_at IS NULL)
[]

edit:

console result

# console result
WorkingHistory.where(work_end_at: [nil, ""])
SELECT `working_histories`.* FROM `working_histories` WHERE ((`working_histories`.`work_end_at` = '' OR `working_histories`.`work_end_at` IS NULL))
[]

WorkingHistory.where(work_end_at: nil)
SELECT `working_histories`.* FROM `working_histories` WHERE `working_histories`.`work_end_at` IS NULL
[]

#WorkingHistory.find(532)
SELECT `working_histories`.* FROM `working_histories` WHERE `working_histories`.`id` = 532 LIMIT 1
#<WorkingHistory:0x0055d8737a8838> {
                       :id => 532,
                  :user_id => 63,
              :contract_id => 220,
            :work_start_at => Thu, 05 Jul 2018 13:54:28 CST +08:00,
               :created_at => Thu, 05 Jul 2018 13:54:33 CST +08:00,
               :updated_at => Thu, 16 Aug 2018 11:13:21 CST +08:00,
              :work_end_at => nil,
            :work_time_sec => 3721,
    :contract_milestone_id => 822
}

# WorkingHistory model
class WorkingHistory < ActiveRecord::Base
  belongs_to :user
  belongs_to :contract
  belongs_to :contract_milestone

  has_one :memo
  has_many :snapshots
  has_many :details, class_name: 'WorkingHistoryDetail'

  scope :custom_region, -> (date) { where(work_start_at: date) }
  scope :custom_day, -> (date) { where(work_start_at: date.beginning_of_day..date.end_of_day) }
  scope :custom_week, -> (date) { where(work_start_at: date.beginning_of_week..date.end_of_week) }
  scope :custom_month, -> (date) { where(work_start_at: date.beginning_of_month..date.end_of_month) }
  scope :custom_year, -> (date) { where(work_start_at: date.beginning_of_year..date.end_of_year) }
  scope :relate, -> (datetime) { where('work_start_at < ? && work_end_at >= ?', datetime, datetime) }
  scope :current, -> { where('work_start_at < ? && work_end_at = ?', Time.zone.now, '0000-00-00 00:00:00') }
  scope :cross_cycle_working, -> { where('dayofweek(work_start_at) = ? AND dayofweek(work_end_at) = ?', 6, 1) }
  scope :not_end_or_end_at_today, -> { where('work_end_at >= ?', Time.zone.today.beginning_of_day) }
  scope :without_end_at, -> { where("work_end_at IS NULL or work_end_at = ''") }

  before_save :assign_to_contract_milestone

  def end_working?
    self.work_end_at.present?
  end

  def update_work_time_sec
    return unless self.end_working?

    limit_hours = contract.limit_hours * 3600
    total_working_time = contract_milestone.working_histories.where("working_histories.id != ?", self.id).sum(:work_time_sec)
    details_sum = self.details.summation
    if (details_sum + total_working_time) > limit_hours
      difference = (limit_hours - total_working_time) > 0 ? (limit_hours - total_working_time) : 0
      self.update!(work_time_sec: difference)
    else
      secs = details_sum > 0 ? details_sum : 0
      self.update!(work_time_sec: secs)
    end
  end

  private

  def work_end_and_yet_assign?
    work_time_sec.present? && contract_milestone_id.blank?
  end
end


# db schema

  create_table "working_histories", force: true do |t|
    t.integer  "user_id"
    t.integer  "contract_id"
    t.datetime "work_start_at"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.datetime "work_end_at"
    t.integer  "work_time_sec",         default: 0,     null: false
    t.integer  "contract_milestone_id"
    t.boolean  "finish",                default: false
  end

edit:

#The result of querying the data directly with MySQL

mysql> SELECT * FROM working_histories where `working_histories`.`id` = 532;
+-----+---------+-------------+---------------------+---------------------+---------------------+---------------------+---------------+-----------------------+--------+
| id  | user_id | contract_id | work_start_at       | created_at          | updated_at          | work_end_at         | work_time_sec | contract_milestone_id | finish |
+-----+---------+-------------+---------------------+---------------------+---------------------+---------------------+---------------+-----------------------+--------+
| 532 |      63 |         220 | 2018-07-05 05:54:28 | 2018-07-05 05:54:33 | 2018-08-16 03:13:21 | 0000-00-00 00:00:00 |          3721 |                   822 |      1 |
+-----+---------+-------------+---------------------+---------------------+---------------------+---------------------+---------------+-----------------------+--------+
1 row in set (0.01 sec)

Answer 1

The problem was not resolved after upgrading the MySQL version.

I found that the default value of work_end_at in MySQL schema is 0000-00-0000:00 00:00. It is different from db/schema.rb.

I think that's why I can't get the correct results.

mysql> describe working_histories;
+-----------------------+------------+------+-----+---------------------+----------------+
| Field                 | Type       | Null | Key | Default             | Extra          |
+-----------------------+------------+------+-----+---------------------+----------------+
| id                    | int(11)    | NO   | PRI | NULL                | auto_increment |
| user_id               | int(11)    | YES  | MUL | NULL                |                |
| contract_id           | int(11)    | YES  | MUL | NULL                |                |
| work_start_at         | datetime   | YES  |     | NULL                |                |
| created_at            | datetime   | YES  |     | NULL                |                |
| updated_at            | datetime   | YES  |     | NULL                |                |
| work_end_at           | datetime   | YES  |     | 0000-00-00 00:00:00 |                |
| work_time_sec         | int(11)    | NO   |     | 0                   |                |
| contract_milestone_id | int(11)    | YES  | MUL | NULL                |                |
| finish                | tinyint(1) | YES  |     | 0                   |                |
+-----------------------+------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)

Answer 2

Based on comments, you have a little issue with MySQL database. In this database, 'empty' DateTime field has a value of 0000-00-00 00:00:00 instead of null. However, Rails shows (or 'casts') it as nil, so that might be misleading.

First solution would be to query for that value treating it same as a nil or ''.

Second solution would be to update MySQL/Rails version, as @Subash tried to reproduce your problem and couldn't do it, so probably there are some differences between versions there.

Answer 3

Try this,

WorkingHistory.where(work_end_at: [nil, ""])

It'll find both that have null or empty value, if you only want null you can remove '' and use

WorkingHistory.where(work_end_at: nil)

EDIT 1

This is the output when I tried with your application, works fine for me