Virtual/generated column support for MySQL 5.7.5+ and MariaDB 5.2.0+

MySQL generated columns: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
MariaDB virtual columns: https://mariadb.com/kb/en/mariadb/virtual-computed-columns/

Declare virtual columns with `t.virtual name, type: …, as: "expression"`.
Pass `stored: true` to persist the generated value (false by default).

Example:

    create_table :generated_columns do |t|
      t.string  :name
      t.virtual :upper_name, type: :string,  as: "UPPER(name)"
      t.virtual :name_length, type: :integer, as: "LENGTH(name)", stored: true
      t.index :name_length  # May be indexed, too!
    end

Closes #22589

13 files changed, 170 insertions, 28 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index acdf7d40f8..12a037465b 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,22 @@
+*   Virtual/generated column support for MySQL 5.7.5+ and MariaDB 5.2.0+.
+
+    MySQL generated columns: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
+    MariaDB virtual columns: https://mariadb.com/kb/en/mariadb/virtual-computed-columns/
+
+    Declare virtual columns with `t.virtual name, type: …, as: "expression"`.
+    Pass `stored: true` to persist the generated value (false by default).
+
+    Example:
+
+        create_table :generated_columns do |t|
+          t.string  :name
+          t.virtual :upper_name,  type: :string,  as: "UPPER(name)"
+          t.virtual :name_length, type: :integer, as: "LENGTH(name)", stored: true
+          t.index :name_length  # May be indexed, too!
+        end
+
+    *Ryuta Kamizono*
+
 *   Deprecate `initialize_schema_migrations_table` and `initialize_internal_metadata_table`.
 
     *Ryuta Kamizono*
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
index 807df2184a..81dec97bf7 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb
@@ -106,6 +106,7 @@ module ActiveRecord
             column_options[:primary_key] = o.primary_key
             column_options[:collation] = o.collation
             column_options[:comment] = o.comment
+            column_options[:as] = o.as
             column_options
           end
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
index b518ef760b..ecc6caa8f2 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
@@ -9,7 +9,7 @@ module ActiveRecord
     # are typically created by methods in TableDefinition, and added to the
     # +columns+ attribute of said TableDefinition object, in order to be used
     # for generating a number of table creation or table changing SQL statements.
-    ColumnDefinition = Struct.new(:name, :type, :limit, :precision, :scale, :default, :null, :first, :after, :auto_increment, :primary_key, :collation, :sql_type, :comment) do #:nodoc:
+    ColumnDefinition = Struct.new(:name, :type, :limit, :precision, :scale, :default, :null, :first, :after, :auto_increment, :primary_key, :collation, :sql_type, :comment, :as) do # :nodoc:
       def primary_key?
         primary_key || type.to_sym == :primary_key
       end
@@ -173,6 +173,7 @@ module ActiveRecord
         :text,
         :time,
         :timestamp,
+        :virtual,
       ].each do |column_type|
         module_eval <<-CODE, __FILE__, __LINE__ + 1
           def #{column_type}(*args, **options)
@@ -374,6 +375,7 @@ module ActiveRecord
         column.primary_key = type == :primary_key || options[:primary_key]
         column.collation   = options[:collation]
         column.comment     = options[:comment]
+        column.as          = options[:as]
         column
       end
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
index b912d24626..d6c912a69e 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb
@@ -7,7 +7,7 @@ module ActiveRecord
     # Adapter level by over-writing this code inside the database specific adapters
     module ColumnDumper
       def column_spec(column)
-        [schema_type(column), prepare_column_options(column)]
+        [schema_type_with_virtual(column), prepare_column_options(column)]
       end
 
       def column_spec_for_primary_key(column)
@@ -59,6 +59,14 @@ module ActiveRecord
           schema_type(column) == :bigint
         end
 
+        def schema_type_with_virtual(column)
+          if supports_virtual_columns? && column.virtual?
+            :virtual
+          else
+            schema_type(column)
+          end
+        end
+
         def schema_type(column)
           if column.bigint?
             :bigint
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
index 348396de72..fd11cab5c0 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
@@ -346,6 +346,11 @@ module ActiveRecord
         true
       end
 
+      # Does this adapter support virtual columns?
+      def supports_virtual_columns?
+        false
+      end
+
       # This is meant to be implemented by the adapters that support extensions
       def disable_extension(name)
       end
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index aedf4581f5..89b4ec9473 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -141,6 +141,14 @@ module ActiveRecord
         end
       end
 
+      def supports_virtual_columns?
+        if mariadb?
+          version >= "5.2.0"
+        else
+          version >= "5.7.5"
+        end
+      end
+
       def supports_advisory_locks?
         true
       end
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/column.rb b/activerecord/lib/active_record/connection_adapters/mysql/column.rb
index 1499c1681f..c9ad47c035 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/column.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/column.rb
@@ -15,6 +15,10 @@ module ActiveRecord
         def auto_increment?
           extra == "auto_increment"
         end
+
+        def virtual?
+          /\b(?:VIRTUAL|STORED|PERSISTENT)\b/.match?(extra)
+        end
       end
     end
   end
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb
index d808b50332..39c2acbca9 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_creation.rb
@@ -2,8 +2,8 @@ module ActiveRecord
   module ConnectionAdapters
     module MySQL
       class SchemaCreation < AbstractAdapter::SchemaCreation
-        delegate :add_sql_comment!, to: :@conn
-        private :add_sql_comment!
+        delegate :add_sql_comment!, :mariadb?, to: :@conn
+        private :add_sql_comment!, :mariadb?
 
         private
 
@@ -32,6 +32,7 @@ module ActiveRecord
           def column_options(o)
             column_options = super
             column_options[:charset] = o.charset
+            column_options[:stored] = o.stored
             column_options
           end
 
@@ -44,6 +45,13 @@ module ActiveRecord
               sql << " COLLATE #{collation}"
             end
 
+            if as = options[:as]
+              sql << " AS (#{as})"
+              if options[:stored]
+                sql << (mariadb? ? " PERSISTENT" : " STORED")
+              end
+            end
+
             add_sql_comment!(super, options[:comment])
           end
 
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb
index f1ba0cb708..76ebd0bf6c 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb
@@ -3,8 +3,7 @@ module ActiveRecord
     module MySQL
       module ColumnMethods
         def primary_key(name, type = :primary_key, **options)
-          options[:auto_increment] = true if [:primary_key, :integer, :bigint].include?(type) && !options.key?(:default)
-          options[:limit] = 8 if [:primary_key].include?(type)
+          options[:auto_increment] = true if [:integer, :bigint].include?(type) && !options.key?(:default)
           super
         end
 
@@ -58,24 +57,29 @@ module ActiveRecord
       end
 
       class ColumnDefinition < ActiveRecord::ConnectionAdapters::ColumnDefinition
-        attr_accessor :charset, :unsigned
+        attr_accessor :charset, :unsigned, :stored
       end
 
       class TableDefinition < ActiveRecord::ConnectionAdapters::TableDefinition
         include ColumnMethods
 
         def new_column_definition(name, type, options) # :nodoc:
-          column = super
-          case column.type
+          case type
+          when :virtual
+            type = options[:type]
           when :primary_key
-            column.type = :integer
-            column.auto_increment = true
+            type = :integer
+            options[:limit] ||= 8
+            options[:auto_increment] = true
+            options[:primary_key] = true
           when /\Aunsigned_(?<type>.+)\z/
-            column.type = $~[:type].to_sym
-            column.unsigned = true
+            type = $~[:type].to_sym
+            options[:unsigned] = true
           end
-          column.unsigned ||= options[:unsigned]
+          column = super
+          column.unsigned = options[:unsigned]
           column.charset = options[:charset]
+          column.stored = options[:stored]
           column
         end
 
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb
index d44c35714f..7a277b8cfd 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb
@@ -14,6 +14,13 @@ module ActiveRecord
         def prepare_column_options(column)
           spec = super
           spec[:unsigned] = "true" if column.unsigned?
+
+          if supports_virtual_columns? && column.virtual?
+            spec[:as] = extract_expression_for_virtual_column(column)
+            spec[:stored] = "true" if /\b(?:STORED|PERSISTENT)\b/.match?(column.extra)
+            spec = { type: schema_type(column).inspect }.merge!(spec)
+          end
+
           spec
         end
 
@@ -46,6 +53,21 @@ module ActiveRecord
               column.collation.inspect if column.collation != @table_collation_cache[table_name]
             end
           end
+
+          def extract_expression_for_virtual_column(column)
+            if mariadb?
+              create_table_info = create_table_info(column.table_name)
+              if %r/#{quote_column_name(column.name)} #{Regexp.quote(column.sql_type)} AS \((?<expression>.+?)\) #{column.extra}/m =~ create_table_info
+                $~[:expression].inspect
+              end
+            else
+              sql = "SELECT generation_expression FROM information_schema.columns" \
+                    " WHERE table_schema = #{quote(@config[:database])}" \
+                    "   AND table_name = #{quote(column.table_name)}" \
+                    "   AND column_name = #{quote(column.name)}"
+              select_value(sql, "SCHEMA").inspect
+            end
+          end
       end
     end
   end
diff --git a/activerecord/test/cases/adapters/mysql2/datetime_precision_quoting_test.rb b/activerecord/test/cases/adapters/mysql2/datetime_precision_quoting_test.rb
index 135789a57d..c131a5169c 100644
--- a/activerecord/test/cases/adapters/mysql2/datetime_precision_quoting_test.rb
+++ b/activerecord/test/cases/adapters/mysql2/datetime_precision_quoting_test.rb
@@ -6,23 +6,27 @@ class Mysql2DatetimePrecisionQuotingTest < ActiveRecord::Mysql2TestCase
   end
 
   test "microsecond precision for MySQL gte 5.6.4" do
-    stub_version "5.6.4"
-    assert_microsecond_precision
+    stub_version "5.6.4" do
+      assert_microsecond_precision
+    end
   end
 
   test "no microsecond precision for MySQL lt 5.6.4" do
-    stub_version "5.6.3"
-    assert_no_microsecond_precision
+    stub_version "5.6.3" do
+      assert_no_microsecond_precision
+    end
   end
 
   test "microsecond precision for MariaDB gte 5.3.0" do
-    stub_version "5.5.5-10.1.8-MariaDB-log"
-    assert_microsecond_precision
+    stub_version "5.5.5-10.1.8-MariaDB-log" do
+      assert_microsecond_precision
+    end
   end
 
   test "no microsecond precision for MariaDB lt 5.3.0" do
-    stub_version "5.2.9-MariaDB"
-    assert_no_microsecond_precision
+    stub_version "5.2.9-MariaDB" do
+      assert_no_microsecond_precision
+    end
   end
 
   private
@@ -41,5 +45,8 @@ class Mysql2DatetimePrecisionQuotingTest < ActiveRecord::Mysql2TestCase
     def stub_version(full_version_string)
       @connection.stubs(:full_version).returns(full_version_string)
       @connection.remove_instance_variable(:@version) if @connection.instance_variable_defined?(:@version)
+      yield
+    ensure
+      @connection.remove_instance_variable(:@version) if @connection.instance_variable_defined?(:@version)
     end
 end
diff --git a/activerecord/test/cases/adapters/mysql2/virtual_column_test.rb b/activerecord/test/cases/adapters/mysql2/virtual_column_test.rb
new file mode 100644
index 0000000000..442a4fb7b5
--- /dev/null
+++ b/activerecord/test/cases/adapters/mysql2/virtual_column_test.rb
@@ -0,0 +1,59 @@
+require "cases/helper"
+require "support/schema_dumping_helper"
+
+if ActiveRecord::Base.connection.supports_virtual_columns?
+  class Mysql2VirtualColumnTest < ActiveRecord::Mysql2TestCase
+    include SchemaDumpingHelper
+
+    self.use_transactional_tests = false
+
+    class VirtualColumn < ActiveRecord::Base
+    end
+
+    def setup
+      @connection = ActiveRecord::Base.connection
+      @connection.create_table :virtual_columns, force: true do |t|
+        t.string  :name
+        t.virtual :upper_name,  type: :string,  as: "UPPER(`name`)"
+        t.virtual :name_length, type: :integer, as: "LENGTH(`name`)", stored: true
+      end
+      VirtualColumn.create(name: "Rails")
+    end
+
+    def teardown
+      @connection.drop_table :virtual_columns, if_exists: true
+      VirtualColumn.reset_column_information
+    end
+
+    def test_virtual_column
+      column = VirtualColumn.columns_hash["upper_name"]
+      assert_predicate column, :virtual?
+      assert_match %r{\bVIRTUAL\b}, column.extra
+      assert_equal "RAILS", VirtualColumn.take.upper_name
+    end
+
+    def test_stored_column
+      column = VirtualColumn.columns_hash["name_length"]
+      assert_predicate column, :virtual?
+      assert_match %r{\b(?:STORED|PERSISTENT)\b}, column.extra
+      assert_equal 5, VirtualColumn.take.name_length
+    end
+
+    def test_change_table
+      @connection.change_table :virtual_columns do |t|
+        t.virtual :lower_name, type: :string, as: "LOWER(name)"
+      end
+      VirtualColumn.reset_column_information
+      column = VirtualColumn.columns_hash["lower_name"]
+      assert_predicate column, :virtual?
+      assert_match %r{\bVIRTUAL\b}, column.extra
+      assert_equal "rails", VirtualColumn.take.lower_name
+    end
+
+    def test_schema_dumping
+      output = dump_table_schema("virtual_columns")
+      assert_match(/t\.virtual\s+"upper_name",\s+type: :string,\s+as: "UPPER\(`name`\)"$/i, output)
+      assert_match(/t\.virtual\s+"name_length",\s+type: :integer,\s+as: "LENGTH\(`name`\)",\s+stored: true$/i, output)
+    end
+  end
+end
diff --git a/activerecord/test/cases/migration/change_table_test.rb b/activerecord/test/cases/migration/change_table_test.rb
index 8a4242cf1d..ec817a579b 100644
--- a/activerecord/test/cases/migration/change_table_test.rb
+++ b/activerecord/test/cases/migration/change_table_test.rb
@@ -101,12 +101,7 @@ module ActiveRecord
 
       def test_primary_key_creates_primary_key_column
         with_change_table do |t|
-          if current_adapter?(:Mysql2Adapter)
-            @connection.expect :add_column, nil, [:delete_me, :id, :primary_key, { first: true, auto_increment: true, limit: 8, primary_key: true }]
-          else
-            @connection.expect :add_column, nil, [:delete_me, :id, :primary_key, primary_key: true, first: true]
-          end
-
+          @connection.expect :add_column, nil, [:delete_me, :id, :primary_key, primary_key: true, first: true]
           t.primary_key :id, first: true
         end
       end