Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Querydsl Creates toLowerCase Expression for all Types [DATAJPA-1198] #1533

Closed
spring-projects-issues opened this issue Oct 4, 2017 · 4 comments
Closed
Assignees

Comments

@spring-projects-issues
Copy link

@spring-projects-issues spring-projects-issues commented Oct 4, 2017

Akim Bassa opened DATAJPA-1198 and commented

org.springframework.data.jpa.repository.support.Querydsl#buildOrderPropertyPathFrom always adds lower(...) to the sortPropertyExpression if order.isIgnoreCase is true, regardless of the type of the property which will get ordered.

When sorting e.g. a Timestamp, this leads to an exception in Postgresql 9.4.1212: o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: function lower(timestamp without time zone) does not exist.

org.springframework.data.jpa.repository.query.QueryUtils#toJpaOrder in comparison does the same job but only adds lower(...) if the property is a String.

	private Expression<?> buildOrderPropertyPathFrom(Order order) {

		Assert.notNull(order, "Order must not be null!");

		PropertyPath path = PropertyPath.from(order.getProperty(), builder.getType());
		Expression<?> sortPropertyExpression = builder;

		while (path != null) {
                       // FIXME also check type here
			if (!path.hasNext() && order.isIgnoreCase()) {
				// if order is ignore-case we have to treat the last path segment as a String.
				sortPropertyExpression = Expressions.stringPath((Path<?>) sortPropertyExpression, path.getSegment()).lower();
			} else {
				sortPropertyExpression = Expressions.path(path.getType(), (Path<?>) sortPropertyExpression, path.getSegment());
			}

			path = path.next();
		}

		return sortPropertyExpression;
	}
	private static javax.persistence.criteria.Order toJpaOrder(Order order, Root<?> root, CriteriaBuilder cb) {

		PropertyPath property = PropertyPath.from(order.getProperty(), root.getJavaType());
		Expression<?> expression = toExpressionRecursively(root, property);

		if (order.isIgnoreCase() && String.class.equals(expression.getJavaType())) {
			Expression<String> lower = cb.lower((Expression<String>) expression);
			return order.isAscending() ? cb.asc(lower) : cb.desc(lower);
		} else {
			return order.isAscending() ? cb.asc(expression) : cb.desc(expression);
		}
	}
Expected solution:

Only add lower to sortPropertyExpression if sortPropertyExpression has type String


Affects: 1.11.8 (Ingalls SR8)

Attachments:

Issue Links:

  • DATAJPA-1779 Querydsl buildOrderPropertyPathFrom generate SQL inconsistent for column not String
    ("is duplicated by")
  • DATAJPA-1780 Querydsl buildOrderPropertyPathFrom generate SQL inconsistent for column not String
    ("is duplicated by")

Referenced from: pull request #428

Backported to: 2.3.5 (Neumann SR5)

1 votes, 3 watchers

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Sep 20, 2020

marcusvoltolim commented

I have the same problem, reported in 2017 and 3 years later was not fixed ?!

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Sep 22, 2020

Mark Paluch commented

We should apply the same behavior to the Querydsl utility for a consistent behavior. I'm wondering what's the use-case for setting ignore case on non-String properties in the first place? Since Spring Data JPA backoffs for a wrong input, I'm reclassifying this ticket to enhancement

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Sep 22, 2020

marcusvoltolim commented

Hello, how are you?

Sorry for opening the duplicate tickets, I didn't find this old one, and one of the duplications was because a message appears on the screen to reload and create again, but I had already created it!

 

In the ticket https://jira.spring.io/browse/DATAJPA-1779, I created an example: https://github.com/marcusvoltolim/spring-jpa-example-order-bug

Sometimes you want to ignore the case of all sorting by default:

!image-2020-09-22-15-44-36-126.png!

 

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Sep 23, 2020

Mark Paluch commented

No worries, I took care of the duplicates. Thanks for the additional details. It makes sense to be consistent how sort translation works.

One more thing: Please refrain from attaching source code as image. Pasting code as text allows for indexing and finding it

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.