Support expression indexes for MySQL

MySQL 8.0.13 and higher supports functional key parts that index
expression values rather than column or column prefix values.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

2 files changed, 6 insertions, 3 deletions

diff --git a/activerecord/test/cases/schema_dumper_test.rb b/activerecord/test/cases/schema_dumper_test.rb
index db13f20a39..dda3efa47c 100644
--- a/activerecord/test/cases/schema_dumper_test.rb
+++ b/activerecord/test/cases/schema_dumper_test.rb
@@ -229,11 +229,14 @@ class SchemaDumperTest < ActiveRecord::TestCase
   if ActiveRecord::Base.connection.supports_expression_index?
     def test_schema_dump_expression_indices
       index_definition = dump_table_schema("companies").split(/\n/).grep(/t\.index.*company_expression_index/).first.strip
+      index_definition.sub!(/, name: "company_expression_index"\z/, "")
 
       if current_adapter?(:PostgreSQLAdapter)
-        assert_match %r{CASE.+lower\(\(name\)::text\)}i, index_definition
+        assert_match %r{CASE.+lower\(\(name\)::text\).+END\) DESC"\z}i, index_definition
+      elsif current_adapter?(:Mysql2Adapter)
+        assert_match %r{CASE.+lower\(`name`\).+END\) DESC"\z}i, index_definition
       elsif current_adapter?(:SQLite3Adapter)
-        assert_match %r{CASE.+lower\(name\)}i, index_definition
+        assert_match %r{CASE.+lower\(name\).+END\) DESC"\z}i, index_definition
       else
         assert false
       end
diff --git a/activerecord/test/schema/schema.rb b/activerecord/test/schema/schema.rb
index 4ef463fdad..56ff81af29 100644
--- a/activerecord/test/schema/schema.rb
+++ b/activerecord/test/schema/schema.rb
@@ -216,7 +216,7 @@ ActiveRecord::Schema.define do
     t.index [:firm_id, :type, :rating], name: "company_index", length: { type: 10 }, order: { rating: :desc }
     t.index [:firm_id, :type], name: "company_partial_index", where: "(rating > 10)"
     t.index :name, name: "company_name_index", using: :btree
-    t.index "(CASE WHEN rating > 0 THEN lower(name) END)", name: "company_expression_index" if supports_expression_index?
+    t.index "(CASE WHEN rating > 0 THEN lower(name) END) DESC", name: "company_expression_index" if supports_expression_index?
   end
 
   create_table :content, force: true do |t|