empty string for a nullable column are not converted to null

[andreacfm]

I have a noted a different behaviour between the sqlserver and pg adapter.
When passing an empty string to the pg adapter that refers to a column of type string nullable this is passed to the query as NULL. The sqlserver adapter pass the empty string to the query and this fails in my case due to a constraints that I have on that particular column.

[metaskills]

You will have to do some research for me and point me to what is the expected behavior. For instance I do not see anything in the quoting.rb of abstract adapter that enforces what you are saying and if this was expected ActiveRecord behavior I would be almost certain a test would have failed, which non has. Perhaps the ActiveRecord test is wrapped in a pg connection condition? If so, you will need to find where this is explicit in the code or documented in a test so I can do a test too, fix, then make sure no regressions happen.

[andreacfm]

I will try to debug it more.
What I see is that if send a parameter with an empty string to update_atrributes pg will attempt to insert null into db while sqlserver adapter will try to insert an empty string.
I will try to set up a small test isolated test case.

[metaskills]

Thanks, but a test to show the case is not what I am looking for. I am looking for something that says that this is expected behavior and is tested for and established somewhere higher up the chain in ActiveRecord or the PG adapter. Please look for things at that layer and see if you can point me to them. Questions that come to mind is there a way that PG adapter is reflecting on the column and using some schema attribute that may have a SQL Server counterpart. So looking in quoting.rb, etc should yield some answers. I am also curious if other DBs do this, MySQL, etc.

I do not want to simply act on something like this just because PG does it without knowing the higher order bit and details of why and if needed, in what special circumstances.

[andreacfm]

I understand the point but think about this.
Is very common to use include_blank in a select command. This will add an option that if submitted will pass an empty string.
Now is also very common that a select command set an id that has a constraint in db (fk). If I want to be able to save the form without making a selection the sql server adapter forces me to sanitize the value and manually convert it into NIL. But the intention is simply to submit a form without making a selection and so without changing the db value that is actually NULL.
This sounds very logical to me.

[metaskills]

I am not disagreeing with you, but I must emphasis my points from above for the needed research. We are passing 100% of the tests in ActiveRecord and I can not believe that this situation is not tested for and if it is not, perhaps there is a reason.

BTW, when you say "sql server adapter forces me", you may actually be talking about validate differences in the database, not adapters. But since you did not do the research and leg work, I have nothing to go on but an outside in parity test and opinion.

[metaskills]

Have you done any of this research to help? Have you tried searching Google with "activerecord postgresql empty string null", looked at some code and tests in ActiveRecord, etc? All I am asking for is some help with understanding the background before telling everyone that uses the adapter that we will not let them insert blank strings into their database.

[metaskills]

I did a quick code dive and Google search and found no evidence of the proper way to proceed. Perhaps someone should solicit opinion on the rails-core mailing list.

Re the code dive, I looked for any test with "def test_.*null" regexp and only found a few that tested MySQL does widely different things. I also did a search thru the entire active record code to see if there was a .null method called on a column, presuming that maybe the Abstract or PG adapters may be looking for when a string is .blank? and the column.null and doing what you say is happening, again I found nothing. It may be possible that this is happening at the DB layer by PG for you automatically, via some index or other DB specific behavior with PG. If so, the adapter will not shim SQL Server to act like PG when it is possible to write a few line hack to make ActiveRecord::Write do this for you in a few lines of code, or perhaps research showing there might be a different ANSI setting you want to toggle per your SQL Server connections.

So without knowledge and background info it seems more and more this is a something that the adapter should not be concerned with. But willing to be wrong if you show me where and why.

[andreacfm]

Thanks for answering.
I will try to find time for some research or asking about that in mailing lists or irc.

[metaskills]

Bug also related to this. http://github.com/rails-sqlserver/tiny_tds/issues/88

[metaskills]

Honest bug, working on a fix now. Sadly this is what PostgreSQL does but it is not tested behavior in ActiveRecord.

assert_nil Post.where(id:'').first
assert_nil Post.where(id:nil).first
assert_raise(ActiveRecord::RecordNotFound) { Post.find('') }
assert_raise(ActiveRecord::RecordNotFound) { Post.find(nil) }