New QueryUtils.hasOrderByClause doesn't find subselect/window order by clause

[jmax01]

New QueryUtils.hasOrderByClause doesn't find subselect/window order by clause preventing order by from being added to query.

spring-data-jpa 2.6.4
java 15

spring-data-jpa/spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/QueryUtils.java

Line 113 in a70fff4

private static final Pattern ORDER_BY_IN_WINDOW_OR_SUBSELECT = Pattern

SELECT
   foo_bar.*
 FROM
    foo foo
INNER JOIN
   foo_bar_dnrmv foo_bar ON
   foo_bar.foo_id = foo.foo_id
INNER JOIN
 (
  SELECT

       foo_bar_action.*,
       RANK() OVER (PARTITION BY "foo_bar_action".attributes->>'baz' ORDER BY "foo_bar_action".attributes->>'qux' DESC) AS ranking
  FROM
      foo_bar_action
  WHERE
       foo_bar_action.deleted_ts IS NULL)
    foo_bar_action ON
  foo_bar.foo_bar_id = foo_bar_action.foo_bar_id
  AND ranking = 1
INNER JOIN
  bar bar ON
  foo_bar.bar_id = bar.bar_id
INNER JOIN
  bar_metadata bar_metadata ON
  bar.bar_metadata_key = bar_metadata.bar_metadata_key
WHERE
  foo.tenant_id =:tenantId
 AND (foo.attributes ->> :serialNum IN (:serialNumValue))

[jmax01]

Added postgres JSON examples

[sparteupgrade]

This issue is also seen in spring-data-jpa 2.5.11. See commit that was added to 2.5.x branch - edf06a9.

[gregturn]

You may wish to investigate our hook for native queries using JSqlParser. See #2409 for more details.

[svolle]

The regex at

spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/QueryUtils.java

Line 113 in edf06a9

private static final Pattern ORDER_BY_IN_WINDOW_OR_SUBSELECT = Pattern.compile("(\\(\\s*[a-z0-9 ,.*]*order\\s+by\\s+[a-z0-9 ,.]*\\s*\\))", CASE_INSENSITIVE);

is much too restrictive and fails to match a lot of valid SQL characters.

Couldn't the regex be simplified to (\(.*order\s+by\s.*\)) or is this too naive?

[gregturn]

@svolle Interestingly enough, I plugged in your suggestion, and so far it passes all our existing unit and integrations tests.

I want to see if I can capture this scenario in a test and see how it fares there as well.

[gregturn]

Captured the opening comment as a test case. @svolle 's suggestion works on it as well!

@Test
void orderByShouldWorkWithSubSelectStatement() {

	Sort sort = Sort.by(Order.desc("age"));

	assertThat(QueryUtils.applySorting("SELECT\n" //
			+ "   foo_bar.*\n" //
			+ "FROM\n" //
			+ "    foo foo\n" //
			+ "INNER JOIN\n" //
			+ "   foo_bar_dnrmv foo_bar ON\n" //
			+ "   foo_bar.foo_id = foo.foo_id\n" //
			+ "INNER JOIN\n" //
			+ " (\n" //
			+ "  SELECT\n" //
			+ "       foo_bar_action.*,\n" //
			+ "       RANK() OVER (PARTITION BY \"foo_bar_action\".attributes->>'baz' ORDER BY \"foo_bar_action\".attributes->>'qux' DESC) AS ranking\n" //
			+ "  FROM\n" //
			+ "      foo_bar_action\n" //
			+ "  WHERE\n" //
			+ "       foo_bar_action.deleted_ts IS NULL)\n" //
			+ "    foo_bar_action ON\n" //
			+ "  foo_bar.foo_bar_id = foo_bar_action.foo_bar_id\n" //
			+ "  AND ranking = 1\n" //
			+ "INNER JOIN\n" //
			+ "  bar bar ON\n" //
			+ "  foo_bar.bar_id = bar.bar_id\n" //
			+ "INNER JOIN\n" //
			+ "  bar_metadata bar_metadata ON\n" //
			+ "  bar.bar_metadata_key = bar_metadata.bar_metadata_key\n" //
			+ "WHERE\n" //
			+ "  foo.tenant_id =:tenantId\n" //
			+ "AND (foo.attributes ->> :serialNum IN (:serialNumValue))", sort)).endsWith("order by foo.age desc");
}

[svolle]

@gregturn The regex I suggested will not match if the order by clause is not on the same line as the opening and/or closing brackets of the subselect/window.
Ex:

select 
 f.id,
 (
  select timestamp from bar
  where date(bar.timestamp) > '2022-05-21'
  and bar.foo_id = f.id 
  order by date(bar.timestamp) desc
  limit 1
) as timestamp
from foo f;

The following regex does not have this limitation: \([\s\S]*order\s+by\s[\s\S]*\)

[gregturn]

Backported to 2.7.x and 2.6.x.

Megathanks to @svolle!

[jmax01]

Thanks all!