1 files changed, 19 insertions, 1 deletions
diff --git a/spec/lib/gitlab/sql/cte_spec.rb b/spec/lib/gitlab/sql/cte_spec.rb
index fdc150cd4b9..4cf94f4dcab 100644
--- a/spec/lib/gitlab/sql/cte_spec.rb
+++ b/spec/lib/gitlab/sql/cte_spec.rb
@@ -14,7 +14,14 @@ RSpec.describe Gitlab::SQL::CTE do
         relation.except(:order).to_sql
       end
 
-      expect(sql).to eq("#{name} AS (#{sql1})")
+      expected = [
+        "#{name} AS ",
+        Gitlab::Database::AsWithMaterialized.materialized_if_supported,
+        (' ' unless Gitlab::Database::AsWithMaterialized.materialized_if_supported.blank?),
+        "(#{sql1})"
+      ].join
+
+      expect(sql).to eq(expected)
     end
   end
 
@@ -41,4 +48,15 @@ RSpec.describe Gitlab::SQL::CTE do
       expect(relation.to_a).to eq(User.where(id: user.id).to_a)
     end
   end
+
+  it_behaves_like 'CTE with MATERIALIZED keyword examples' do
+    let(:expected_query_block_with_materialized) { 'WITH "some_cte" AS MATERIALIZED (' }
+    let(:expected_query_block_without_materialized) { 'WITH "some_cte" AS (' }
+
+    let(:query) do
+      cte = described_class.new(:some_cte, User.active, **options)
+
+      User.with(cte.to_arel).to_sql
+    end
+  end
 end