Fix does_not_support_reverse? to find sql functions with commas in nested brackets

[aquajach]

Summary

does_not_support_reverse? is supposed to check if a query statement is too complex to reverse its order. One of the scenarios is if there is a sql function with multiple arguments, by using /\([^()]*,[^()]*\)/ to match. However, it doesn't catch the case when a query statement has nested parentheses. For instance, concat(author_name, lower(title)) is regarded as supported to do reverse, and User.order(concat(author_name, lower(title))).lastwill raise a sql syntax or undefined column error that is quite confusing, because it does try to reverse the order

Other Information

The new implementation is a bit longer but is not necessarily slower. Here's the benchmark for order statements in different complexity levels:

def new_does_not_support_reverse?(order)
    (order.include?(',') && order.split(',').find { |section| section.count('(') != section.count(')')}) || /nulls (first|last)\Z/i.match?(order)
end

def does_not_support_reverse?(order)
    /\([^()]*,[^()]*\)/.match?(order) || /nulls (first|last)\Z/i.match?(order)
end

single_order = 'author_name DESC'
multiple_order = 'author_name ASC, lower(title)'
complex_order = 'concat(author_name, lower(title)) ASC'

Benchmark.bm do |x|
    x.report { 10_000.times do; does_not_support_reverse?(single_order); end }
    x.report { 10_000.times do; new_does_not_support_reverse?(single_order); end }
end
#       user     system      total        real
#   0.010000   0.000000   0.010000 (  0.014889)
#   0.010000   0.000000   0.010000 (  0.006995)

Benchmark.bm do |x|
    x.report { 10_000.times do; does_not_support_reverse?(multiple_order); end }
    x.report { 10_000.times do; new_does_not_support_reverse?(multiple_order); end }
end
#       user     system      total        real
#   0.010000   0.000000   0.010000 (  0.013635)
#   0.020000   0.000000   0.020000 (  0.022306)

Benchmark.bm do |x|
    x.report { 10_000.times do; does_not_support_reverse?(complex_order); end }
    x.report { 10_000.times do; new_does_not_support_reverse?(complex_order); end }
end
#       user     system      total        real
#   0.020000   0.000000   0.020000 (  0.021635)
#   0.010000   0.000000   0.010000 (  0.011312)

[rails-bot]

Thanks for the pull request, and welcome! The Rails team is excited to review your changes, and you should hear from @rafaelfranca (or someone else) soon.

If any changes to this PR are deemed necessary, please add them as extra commits. This ensures that the reviewer can see what has changed since they last reviewed the code. Due to the way GitHub handles out-of-date commits, this should also make it reasonably obvious what issues have or haven't been addressed. Large or tricky changes may require several passes of review and changes.

This repository is being automatically checked for code quality issues using Code Climate. You can see results for this analysis in the PR status below. Newly introduced issues should be fixed before a Pull Request is considered ready to review.

Please see the contribution instructions for more information.

[aquajach]

@rafaelfranca do you want to take a look? It is related to #25900

[rafaelfranca]

Backported in 08c9a6d