{"action":"created","issue":{"url":"https://api.github.com/repos/rails/rails/issues/25670","repository_url":"https://api.github.com/repos/rails/rails","labels_url":"https://api.github.com/repos/rails/rails/issues/25670/labels{/name}","comments_url":"https://api.github.com/repos/rails/rails/issues/25670/comments","events_url":"https://api.github.com/repos/rails/rails/issues/25670/events","html_url":"https://github.com/rails/rails/pull/25670","id":163577803,"number":25670,"title":"Serialize JSON attribute value nil as SQL NULL, not JSON 'null'","user":{"login":"tdtran","id":122872,"avatar_url":"https://avatars.githubusercontent.com/u/122872?v=3","gravatar_id":"","url":"https://api.github.com/users/tdtran","html_url":"https://github.com/tdtran","followers_url":"https://api.github.com/users/tdtran/followers","following_url":"https://api.github.com/users/tdtran/following{/other_user}","gists_url":"https://api.github.com/users/tdtran/gists{/gist_id}","starred_url":"https://api.github.com/users/tdtran/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tdtran/subscriptions","organizations_url":"https://api.github.com/users/tdtran/orgs","repos_url":"https://api.github.com/users/tdtran/repos","events_url":"https://api.github.com/users/tdtran/events{/privacy}","received_events_url":"https://api.github.com/users/tdtran/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/rails/rails/labels/activerecord","name":"activerecord","color":"0b02e1"}],"state":"open","locked":false,"assignee":{"login":"senny","id":5402,"avatar_url":"https://avatars.githubusercontent.com/u/5402?v=3","gravatar_id":"","url":"https://api.github.com/users/senny","html_url":"https://github.com/senny","followers_url":"https://api.github.com/users/senny/followers","following_url":"https://api.github.com/users/senny/following{/other_user}","gists_url":"https://api.github.com/users/senny/gists{/gist_id}","starred_url":"https://api.github.com/users/senny/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/senny/subscriptions","organizations_url":"https://api.github.com/users/senny/orgs","repos_url":"https://api.github.com/users/senny/repos","events_url":"https://api.github.com/users/senny/events{/privacy}","received_events_url":"https://api.github.com/users/senny/received_events","type":"User","site_admin":false},"assignees":[{"login":"senny","id":5402,"avatar_url":"https://avatars.githubusercontent.com/u/5402?v=3","gravatar_id":"","url":"https://api.github.com/users/senny","html_url":"https://github.com/senny","followers_url":"https://api.github.com/users/senny/followers","following_url":"https://api.github.com/users/senny/following{/other_user}","gists_url":"https://api.github.com/users/senny/gists{/gist_id}","starred_url":"https://api.github.com/users/senny/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/senny/subscriptions","organizations_url":"https://api.github.com/users/senny/orgs","repos_url":"https://api.github.com/users/senny/repos","events_url":"https://api.github.com/users/senny/events{/privacy}","received_events_url":"https://api.github.com/users/senny/received_events","type":"User","site_admin":false}],"milestone":null,"comments":4,"created_at":"2016-07-03T20:09:12Z","updated_at":"2016-08-05T09:58:14Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/rails/rails/pulls/25670","html_url":"https://github.com/rails/rails/pull/25670","diff_url":"https://github.com/rails/rails/pull/25670.diff","patch_url":"https://github.com/rails/rails/pull/25670.patch"},"body":"Assume `payload` is a table column of JSON type. A Ruby `nil` value of `payload` can be stored in the database in one of two ways\r\n\r\n* SQL `NULL`\r\n* JSON value `'null'`\r\n\r\nWhen loading data from the database both `NULL` and `'null'` are deserialized correctly into Ruby `nil`. This part is fine.\r\n\r\nWhen data are persisted into the DB `nil` is translated to `'null'`, not SQL `NULL`. This leads to several problems:\r\n\r\nAlthough `x.update_attributes(payload: nil`) translates to SQL `UPDATE products SET payload = 'null'`, a query `Product.where(payload: nil)` translates to `SELECT * from products WHERE payload IS NULL`. Such a SQL query returns nothing because `'null' IS NOT NULL`. This is demonstrated by the failed test I added in this PR.\r\n\r\nOne way to fix this bug is to translate `where(payload: nil)` to `WHERE payload IS NULL OR payload = 'null'`. We'd have to check if `payload` type is JSON first and such a SQL statement is slower than the simpler `payload IS NOT NULL`.\r\n\r\nSupport for JSON types in Postgresql in Rails 4.x translates `nil` to SQL `NULL`, not JSON `'null'`. This change of behavior was introduced when AR added JSON support for MySQL (https://github.com/rails/rails/pull/21110). In our code base we have a few complicated SQL statements which rely on the old behavior. They are broken when we upgrade to Rails 5.0. Queries for `nil`, not `nil` are pretty common, I suspect we are not the only ones affected.\r\n\r\nI think the preferred fix is to map `nil` to SQL `NULL`, exactly as how it used to work in Rails 4.x.\r\n"},"comment":{"url":"https://api.github.com/repos/rails/rails/issues/comments/237811706","html_url":"https://github.com/rails/rails/pull/25670#issuecomment-237811706","issue_url":"https://api.github.com/repos/rails/rails/issues/25670","id":237811706,"user":{"login":"tdtran","id":122872,"avatar_url":"https://avatars.githubusercontent.com/u/122872?v=3","gravatar_id":"","url":"https://api.github.com/users/tdtran","html_url":"https://github.com/tdtran","followers_url":"https://api.github.com/users/tdtran/followers","following_url":"https://api.github.com/users/tdtran/following{/other_user}","gists_url":"https://api.github.com/users/tdtran/gists{/gist_id}","starred_url":"https://api.github.com/users/tdtran/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tdtran/subscriptions","organizations_url":"https://api.github.com/users/tdtran/orgs","repos_url":"https://api.github.com/users/tdtran/repos","events_url":"https://api.github.com/users/tdtran/events{/privacy}","received_events_url":"https://api.github.com/users/tdtran/received_events","type":"User","site_admin":false},"created_at":"2016-08-05T09:58:14Z","updated_at":"2016-08-05T09:58:14Z","body":"@senny changelog entry added, commits squashed, branch rebased against latest `rails[master]`"}}
{"actor":{"display_login":"tdtran"}}