Error on query table with default column as a SQL default

[andretf]

I get this error after upgrade to 4.2.4 from 4.1.0.
I cannot query tables with column defaulted by a SQL default constraint - which has a getdate() function.
Error is occurring at lib/active_record/connection_adapters/sqlserver/schema_statements.rb : line 319 - (at code below - it gets 320 due to line added for byebug).
I think that I showing the byebug output will explain better:

[311, 320] in /home/vagrant/.rvm/gems/ruby-2.2.2/gems/activerecord-sqlserver-adapter-4.2.4/lib/active_record/connection_adapters/sqlserver/schema_statements.rb
   311:                 [nil, default_function]
   312:               when /\A\(N'(.*)'\)\Z/m
   313:                 string_literal = SQLServer::Utils.unquote_string(Regexp.last_match[1])
   314:                 [string_literal, nil]
   315:               else
=> 316:                 type = case ci[:type]
   317:                        when /smallint|int|bigint/ then ci[:_type]
   318:                        else ci[:type]
   319:                        end
   320:                 value = default.match(/\A\((.*)\)\Z/m)[1]
(byebug)  default.match(/\A\((.*)\)\Z/m)[1]
*** NoMethodError Exception: undefined method `[]' for nil:NilClass

nil
(byebug) ci
{:table_name=>"ED_Snapshot", :name=>"esna_create_date", :type=>"datetime", :default_value=>"\r\nCREATE DEFAULT dbo.RS_d_datetime_stamp AS getdate()\r\n", :numeric_scale=>nil, :numeric_precision=>nil, :datetime_precision=>3, :ordinal_position=>2, :length=>8, :is_nullable=>nil, :is_primary=>nil, :is_identity=>false, :_type=>"datetime"}
(byebug)  ci[:default_value]
"\r\nCREATE DEFAULT dbo.RS_d_datetime_stamp AS getdate()\r\n"

I think that code should has one more case option to match SQL defaults.

[metaskills]

Is this table a view?

[andretf]

No.
Below, the print of the table and problematic column definitions and the problematic Default.

[metaskills]

Wow, very interesting. Right now our column_definitions method only handles a regexp for a function. I'm not sure how to handle this. Obviously if you just replaced that default object with a simple getdate() function for the default it would work. Would you be interested in help the adapter setup a test for your use case tho?

Maybe a fall thru in this case statement? https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver/schema_statements.rb#L305

[andretf]

Yes.. The second 'when' statement would handle. Right now, I'm doing a monkey patch adding a clause something like /\A(\\r\\n)*(create)[\s\\r\\n]+(default)[\s\\r\\n]+[\w.]*[\s\\r\\n]+(as)[\s\\r\\n].*\Z/i that I'm going to test. I think it will work in my case. But I'm not sure how to do it safely. Even because, default can be set also by sp_bindefault - obsolete, but still alive.

[metaskills]

OK, I think I have a fix for this that has a much simpler regular expression. A few forward thoughts tho. First, regarding this feature.

CREATE DEFAULT (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms173565.aspx

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use default definitions created using the DEFAULT keyword of ALTER TABLE or CREATE TABLE.

If and when this becomes true, I will remove the specific SQL that sets up the test, but I will leave the case in the column_definitions method.

[andretf]

Thanks!!
I created a patch that I have been used for a while with no incidents:
master...andretf:master (starting on l.305) - but didn't create some tests.
Unfortunately, I'm stuck in a legacy database.

[metaskills]

@andretf Can you try out master with 75e9f01 and let me know how that works for you?

[andretf]

Works perfectly. Thank you so much for your help!