using mysql view causes NoMethodError in activerecord-7.0.3

[hoshi-sano]

Calling count, where (, ...and so on) for a mysql view causes an error.

NoMethodError: undefined method `match' for nil:NilClass

            match = create_table_info(table_name).match(/`#{field_name}` (.+) DEFAULT ('|\d+|[A-z]+)/)
                                                 ^^^^^^

Steps to reproduce

I hope you could reproduce with this.

backtrace:

NoMethodError: undefined method `match' for nil:NilClass

            match = create_table_info(table_name).match(/`#{field_name}` (.+) DEFAULT ('|\d+|[A-z]+)/)
                                                 ^^^^^^
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/mysql/schema_statements.rb:162:in `default_type'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/mysql/schema_statements.rb:190:in `new_column_from_field'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:117:in `block in columns'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/mysql2_adapter.rb:94:in `each'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/mysql2_adapter.rb:94:in `each_hash'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:116:in `each'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:116:in `map'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:116:in `columns'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/schema_cache.rb:117:in `block in columns'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/schema_cache.rb:116:in `fetch'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/schema_cache.rb:116:in `columns'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/schema_cache.rb:125:in `block in columns_hash'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/schema_cache.rb:124:in `fetch'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/connection_adapters/schema_cache.rb:124:in `columns_hash'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/model_schema.rb:568:in `load_schema!'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/attributes.rb:264:in `load_schema!'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/encryption/encryptable_record.rb:122:in `load_schema!'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/model_schema.rb:554:in `block in load_schema'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/model_schema.rb:551:in `synchronize'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/model_schema.rb:551:in `load_schema'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/model_schema.rb:417:in `attribute_types'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/model_schema.rb:443:in `type_for_attribute'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/type_caster/map.rb:16:in `type_for_attribute'
/usr/local/bundle/gems/activerecord-7.0.3/lib/arel/table.rb:107:in `type_for_attribute'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/table_metadata.rb:18:in `type'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/predicate_builder.rb:59:in `build'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/predicate_builder.rb:54:in `[]'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/predicate_builder.rb:126:in `block in expand_from_hash'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/predicate_builder.rb:79:in `each'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/predicate_builder.rb:79:in `flat_map'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/predicate_builder.rb:79:in `expand_from_hash'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/predicate_builder.rb:25:in `build_from_hash'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/query_methods.rb:1299:in `build_where_clause'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/query_methods.rb:742:in `where!'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/relation/query_methods.rb:737:in `where'
/usr/local/bundle/gems/activerecord-7.0.3/lib/active_record/querying.rb:22:in `where'
/usr/local/bundle/gems/railties-7.0.3/lib/rails/commands/runner/runner_command.rb:46:in `<main>'
/usr/local/bundle/gems/railties-7.0.3/lib/rails/commands/runner/runner_command.rb:46:in `eval'
/usr/local/bundle/gems/railties-7.0.3/lib/rails/commands/runner/runner_command.rb:46:in `perform'

Expected behavior

occured here:

https://github.com/rails/rails/blob/v7.0.3/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb#L162

          def default_type(table_name, field_name)
            match = create_table_info(table_name).match(/`#{field_name}` (.+) DEFAULT ('|\d+|[A-z]+)/)

https://github.com/rails/rails/blob/v7.0.3/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L826-L828

        def create_table_info(table_name) # :nodoc:
          exec_query("SHOW CREATE TABLE #{quote_table_name(table_name)}", "SCHEMA").first["Create Table"]
        end

Should work without error even if table_name is for a view.

Actual behavior

When using the SHOW CREATE TABLE syntax, a "Create Table" column could be get for a normal table,

$ bin/rails console
Loading development environment (Rails 7.0.3)
irb(main):001:0> con = ActiveRecord::Base.connection
=>
#<ActiveRecord::ConnectionAdapters::Mysql2Adapter:0x00007f31cdbcb5a0
...
irb(main):002:0> con.exec_query("SHOW CREATE TABLE `articles`", "SCHEMA").first
=>
{"Table"=>"articles",
 "Create Table"=>"CREATE TABLE `articles` (\n  `id` varchar(36) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"}

but for a view, a "Create View" column is generated, so the expected value could not be obtained.

irb(main):003:0> con.exec_query("SHOW CREATE TABLE `test_views`", "SCHEMA").first
=>
{"View"=>"test_views",
 "Create View"=>"CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test_views` AS select `a`.`id` AS `id` from `articles` `a` union select `c`.`id` AS `id` from `comments` `c`",
 "character_set_client"=>"utf8mb4",
 "collation_connection"=>"utf8mb4_0900_ai_ci"}

System configuration

Rails version: 7.0.3

Ruby version: 3.1.2

MySQL: 8.0.29

Probable cause

• Fixed MariaDB default function #44654
• Get rid of MySQL::SchemaStatements#create_table_sql #44712

[claasz]

Same problem here. We have an app which is mostly based on MySQL views. After upgrading from 7.0.2.4 to 7.0.3, it gets broken because you would get NoMethodError: undefined method 'match' for nil:NilClass for every SELECT.

[eileencodes]

I've merged @fatkodima's PR (thank you!) and backported this to 7-0-stable. The fix will be in the next release, which will not be this week or next week as I'm prepping for my RailsConf keynote.

In the meantime you can link to 7-0-stable on GitHub in your Gemfile.

[hoshi-sano]

@eileencodes @fatkodima
Thanks for the quick response and fixing.