Add latest changes from gitlab-org/gitlab@14-10-stable-eev14.10.0-rc42

4 files changed, 140 insertions, 42 deletions

diff --git a/lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb b/lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb
index 065a3a0cf20..8c0f082f61c 100644
--- a/lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb
+++ b/lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb
@@ -120,7 +120,7 @@ module Gitlab
               .from(array_cte)
               .join(Arel.sql("LEFT JOIN LATERAL (#{initial_keyset_query.to_sql}) #{table_name} ON TRUE"))
 
-            order_by_columns.each { |column| q.where(column.column_expression.not_eq(nil)) }
+            order_by_columns.each { |c| q.where(c.column_expression.not_eq(nil)) unless c.column.nullable? }
 
             q.as('array_scope_lateral_query')
           end
@@ -200,7 +200,7 @@ module Gitlab
               .project([*order_by_columns.original_column_names_as_arel_string, Arel.sql('position')])
               .from("UNNEST(#{list(order_by_columns.array_aggregated_column_names)}) WITH ORDINALITY AS u(#{list(order_by_columns.original_column_names)}, position)")
 
-            order_by_columns.each { |column| q.where(Arel.sql(column.original_column_name).not_eq(nil)) } # ignore rows where all columns are NULL
+            order_by_columns.each { |c| q.where(Arel.sql(c.original_column_name).not_eq(nil)) unless c.column.nullable? } # ignore rows where all columns are NULL
 
             q.order(Arel.sql(order_by_without_table_references)).take(1)
           end
diff --git a/lib/gitlab/pagination/keyset/order.rb b/lib/gitlab/pagination/keyset/order.rb
index 1a00692bdbe..290e94401b8 100644
--- a/lib/gitlab/pagination/keyset/order.rb
+++ b/lib/gitlab/pagination/keyset/order.rb
@@ -99,6 +99,8 @@ module Gitlab
                                                        field_value.strftime('%Y-%m-%d %H:%M:%S.%N %Z')
                                                      elsif field_value.nil?
                                                        nil
+                                                     elsif lower_named_function?(column_definition)
+                                                       field_value.downcase
                                                      else
                                                        field_value.to_s
                                                      end
@@ -184,6 +186,10 @@ module Gitlab
 
         private
 
+        def lower_named_function?(column_definition)
+          column_definition.column_expression.is_a?(Arel::Nodes::NamedFunction) && column_definition.column_expression.name&.downcase == 'lower'
+        end
+
         def composite_row_comparison_possible?
           !column_definitions.one? &&
             column_definitions.all?(&:not_nullable?) &&
diff --git a/lib/gitlab/pagination/keyset/simple_order_builder.rb b/lib/gitlab/pagination/keyset/simple_order_builder.rb
index 5e79910a3e9..c36bd497aa3 100644
--- a/lib/gitlab/pagination/keyset/simple_order_builder.rb
+++ b/lib/gitlab/pagination/keyset/simple_order_builder.rb
@@ -11,13 +11,17 @@ module Gitlab
       # [transformed_scope, true] # true indicates that the new scope was successfully built
       # [orginal_scope, false] # false indicates that the order values are not supported in this class
       class SimpleOrderBuilder
+        NULLS_ORDER_REGEX = /(?<column_name>.*) (?<direction>\bASC\b|\bDESC\b) (?<nullable>\bNULLS LAST\b|\bNULLS FIRST\b)/.freeze
+
         def self.build(scope)
           new(scope: scope).build
         end
 
         def initialize(scope:)
           @scope = scope
-          @order_values = scope.order_values
+          # We need to run 'compact' because 'nil' is not removed from order_values
+          # in some cases due to the use of 'default_scope'.
+          @order_values = scope.order_values.compact
           @model_class = scope.model
           @arel_table = @model_class.arel_table
           @primary_key = @model_class.primary_key
@@ -28,10 +32,13 @@ module Gitlab
                     primary_key_descending_order
                   elsif Gitlab::Pagination::Keyset::Order.keyset_aware?(scope)
                     Gitlab::Pagination::Keyset::Order.extract_keyset_order_object(scope)
+                  # Ordered by a primary key. Ex. 'ORDER BY id'.
                   elsif ordered_by_primary_key?
                     primary_key_order
+                  # Ordered by one non-primary table column. Ex. 'ORDER BY created_at'.
                   elsif ordered_by_other_column?
                     column_with_tie_breaker_order
+                  # Ordered by two table columns with the last column as a tie breaker. Ex. 'ORDER BY created, id ASC'.
                   elsif ordered_by_other_column_with_tie_breaker?
                     tie_breaker_attribute = order_values.second
 
@@ -50,6 +57,77 @@ module Gitlab
 
         attr_reader :scope, :order_values, :model_class, :arel_table, :primary_key
 
+        def table_column?(name)
+          model_class.column_names.include?(name.to_s)
+        end
+
+        def primary_key?(attribute)
+          arel_table[primary_key].to_s == attribute.to_s
+        end
+
+        def lower_named_function?(attribute)
+          attribute.is_a?(Arel::Nodes::NamedFunction) && attribute.name&.downcase == 'lower'
+        end
+
+        def arel_nulls?(order_value)
+          return unless order_value.is_a?(Arel::Nodes::NullsLast) || order_value.is_a?(Arel::Nodes::NullsFirst)
+
+          column_name = order_value.try(:expr).try(:expr).try(:name)
+
+          table_column?(column_name)
+        end
+
+        def supported_column?(order_value)
+          return true if arel_nulls?(order_value)
+
+          attribute = order_value.try(:expr)
+          return unless attribute
+
+          if lower_named_function?(attribute)
+            attribute.expressions.one? && attribute.expressions.first.respond_to?(:name) && table_column?(attribute.expressions.first.name)
+          else
+            attribute.respond_to?(:name) && table_column?(attribute.name)
+          end
+        end
+
+        # This method converts the first order value to a corresponding arel expression
+        # if the order value uses either NULLS LAST or NULLS FIRST ordering in raw SQL.
+        #
+        # TODO: https://gitlab.com/gitlab-org/gitlab/-/issues/356644
+        # We should stop matching raw literals once we switch to using the Arel methods.
+        def convert_raw_nulls_order!
+          order_value = order_values.first
+
+          return unless order_value.is_a?(Arel::Nodes::SqlLiteral)
+
+          # Detect NULLS LAST or NULLS FIRST ordering by looking at the raw SQL string.
+          if matches = order_value.match(NULLS_ORDER_REGEX)
+            return unless table_column?(matches[:column_name])
+
+            column_attribute = arel_table[matches[:column_name]]
+            direction = matches[:direction].downcase.to_sym
+            nullable = matches[:nullable].downcase.parameterize(separator: '_').to_sym
+
+            # Build an arel order expression for NULLS ordering.
+            order = direction == :desc ? column_attribute.desc : column_attribute.asc
+            arel_order_expression = nullable == :nulls_first ? order.nulls_first : order.nulls_last
+
+            order_values[0] = arel_order_expression
+          end
+        end
+
+        def nullability(order_value, attribute_name)
+          nullable = model_class.columns.find { |column| column.name == attribute_name }.null
+
+          if nullable && order_value.is_a?(Arel::Nodes::Ascending)
+            :nulls_last
+          elsif nullable && order_value.is_a?(Arel::Nodes::Descending)
+            :nulls_first
+          else
+            :not_nullable
+          end
+        end
+
         def primary_key_descending_order
           Gitlab::Pagination::Keyset::Order.build([
             Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
@@ -69,63 +147,76 @@ module Gitlab
         end
 
         def column_with_tie_breaker_order(tie_breaker_column_order = default_tie_breaker_column_order)
-          order_expression = order_values.first
-          attribute_name = order_expression.expr.name
-
-          column_nullable = model_class.columns.find { |column| column.name == attribute_name }.null
-
-          nullable = if column_nullable && order_expression.is_a?(Arel::Nodes::Ascending)
-                       :nulls_last
-                     elsif column_nullable && order_expression.is_a?(Arel::Nodes::Descending)
-                       :nulls_first
-                     else
-                       :not_nullable
-                     end
-
           Gitlab::Pagination::Keyset::Order.build([
-            Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
-              attribute_name: attribute_name,
-              order_expression: order_expression,
-              nullable: nullable,
-              distinct: false
-            ),
+            column(order_values.first),
             tie_breaker_column_order
           ])
         end
 
-        def ordered_by_primary_key?
-          return unless order_values.one?
+        def column(order_value)
+          return nulls_order_column(order_value) if arel_nulls?(order_value)
+          return lower_named_function_column(order_value) if lower_named_function?(order_value.expr)
 
-          attribute = order_values.first.try(:expr)
+          attribute_name = order_value.expr.name
 
-          return unless attribute
+          Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
+            attribute_name: attribute_name,
+            order_expression: order_value,
+            nullable: nullability(order_value, attribute_name),
+            distinct: false
+          )
+        end
 
-          arel_table[primary_key].to_s == attribute.to_s
+        def nulls_order_column(order_value)
+          attribute = order_value.expr.expr
+
+          Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
+            attribute_name: attribute.name,
+            column_expression: attribute,
+            order_expression: order_value,
+            reversed_order_expression: order_value.reverse,
+            order_direction: order_value.expr.direction,
+            nullable: order_value.is_a?(Arel::Nodes::NullsLast) ? :nulls_last : :nulls_first,
+            distinct: false
+          )
         end
 
-        def ordered_by_other_column?
+        def lower_named_function_column(order_value)
+          attribute_name = order_value.expr.expressions.first.name
+
+          Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
+            attribute_name: attribute_name,
+            column_expression: Arel::Nodes::NamedFunction.new("LOWER", [model_class.arel_table[attribute_name]]),
+            order_expression: order_value,
+            nullable: nullability(order_value, attribute_name),
+            distinct: false
+          )
+        end
+
+        def ordered_by_primary_key?
           return unless order_values.one?
 
           attribute = order_values.first.try(:expr)
+          attribute && primary_key?(attribute)
+        end
 
-          return unless attribute
-          return unless attribute.try(:name)
+        def ordered_by_other_column?
+          return unless order_values.one?
 
-          model_class.column_names.include?(attribute.name.to_s)
+          convert_raw_nulls_order!
+
+          supported_column?(order_values.first)
         end
 
         def ordered_by_other_column_with_tie_breaker?
           return unless order_values.size == 2
 
-          attribute = order_values.first.try(:expr)
-          tie_breaker_attribute = order_values.second.try(:expr)
+          convert_raw_nulls_order!
 
-          return unless attribute
-          return unless tie_breaker_attribute
-          return unless attribute.respond_to?(:name)
+          return unless supported_column?(order_values.first)
 
-          model_class.column_names.include?(attribute.name.to_s) &&
-            arel_table[primary_key].to_s == tie_breaker_attribute.to_s
+          tie_breaker_attribute = order_values.second.try(:expr)
+          tie_breaker_attribute && primary_key?(tie_breaker_attribute)
         end
 
         def default_tie_breaker_column_order
diff --git a/lib/gitlab/pagination/offset_pagination.rb b/lib/gitlab/pagination/offset_pagination.rb
index fca75d1fe01..00304f48dc5 100644
--- a/lib/gitlab/pagination/offset_pagination.rb
+++ b/lib/gitlab/pagination/offset_pagination.rb
@@ -11,8 +11,8 @@ module Gitlab
         @request_context = request_context
       end
 
-      def paginate(relation, exclude_total_headers: false)
-        paginate_with_limit_optimization(add_default_order(relation)).tap do |data|
+      def paginate(relation, exclude_total_headers: false, skip_default_order: false)
+        paginate_with_limit_optimization(add_default_order(relation, skip_default_order: skip_default_order)).tap do |data|
           add_pagination_headers(data, exclude_total_headers)
         end
       end
@@ -27,7 +27,6 @@ module Gitlab
                           end
 
         return pagination_data unless pagination_data.is_a?(ActiveRecord::Relation)
-        return pagination_data unless Feature.enabled?(:api_kaminari_count_with_limit, type: :ops, default_enabled: :yaml)
 
         limited_total_count = pagination_data.total_count_with_limit
         if limited_total_count > Kaminari::ActiveRecordRelationMethods::MAX_COUNT_LIMIT
@@ -47,7 +46,9 @@ module Gitlab
         false
       end
 
-      def add_default_order(relation)
+      def add_default_order(relation, skip_default_order: false)
+        return relation if skip_default_order
+
         if relation.is_a?(ActiveRecord::Relation) && relation.order_values.empty?
           relation = relation.order(:id) # rubocop: disable CodeReuse/ActiveRecord
         end