Nulls LAST / FIRST ignored in Version 1.4.0

[ShaneLee]

Hi,

I'm currently looking at using Criteria queries to query my database. I think I've found a bug where ordering of nulls is ignored. I have to do this programmatically, so can't get around it using @query.

I've written some basic unit tests for the R2DBCEntityTemplate which I think expose the issue (but I'm not sure if this is where the problem is or if I'm just wrong)

	@Test
	void shouldSelectByCriteriaWhereNullsLast() {

		recorder.addStubbing(s -> s.startsWith("SELECT"), Collections.emptyList());

		entityTemplate.select(Query.query(Criteria.where("name")
						.is("Walter"))
						.sort(Sort.by(new Sort.Order(Sort.Direction.ASC, "name", Sort.NullHandling.NULLS_LAST))), Person.class) //
				.as(StepVerifier::create) //
				.verifyComplete();

		StatementRecorder.RecordedStatement statement = recorder.getCreatedStatement(s -> s.startsWith("SELECT"));

		assertThat(statement.getSql())
				.isEqualTo("SELECT person.* FROM person WHERE person.THE_NAME = $1 ORDER BY person.THE_NAME ASC NULLS LAST");
		assertThat(statement.getBindings()).hasSize(1).containsEntry(0, Parameter.from("Walter"));
	}

	@Test
	void shouldSelectByCriteriaWhereNullsFirst() {

		recorder.addStubbing(s -> s.startsWith("SELECT"), Collections.emptyList());

		entityTemplate.select(Query.query(Criteria.where("name")
								.is("Walter"))
						.sort(Sort.by(new Sort.Order(Sort.Direction.ASC, "name", Sort.NullHandling.NULLS_FIRST))), Person.class) //
				.as(StepVerifier::create) //
				.verifyComplete();

		StatementRecorder.RecordedStatement statement = recorder.getCreatedStatement(s -> s.startsWith("SELECT"));

		assertThat(statement.getSql())
				.isEqualTo("SELECT person.* FROM person WHERE person.THE_NAME = $1 ORDER BY person.THE_NAME ASC NULLS FIRST");
		assertThat(statement.getBindings()).hasSize(1).containsEntry(0, Parameter.from("Walter"));
	}

Any help with this is appreciated.

[mp911de]

Null sort precedence isn't supported yet.

[ShaneLee]

Happy to look at adding a PR if I can get some pointers about how this currently works

[mp911de]

Null sorting precedence is subject to dialect specifics as some database are able to handle this on their own. Databases that do not support this require some kind of workaround (CASE/WHEN/THEN expressions along with sorting indicators). It depends on some functionality within Spring Data Relational (spring-projects/spring-data-relational#755).