Projections with native queries don't work as expected [DATAJPA-980]

[spring-projects-issues]

Robert Hunt opened DATAJPA-980 and commented

The documentation doesn't mention anything about native queries in relation to the projection functionality so I don't know if it's even a supported use case. It does seem to work to some extent but there are issues.

I have put together a demonstration project: https://github.com/roberthunt/spring-data-native-query-projection

There is a test for each of these methods:

    List<PersonSummary> findAllProjectedBy();

    @Query(value = "SELECT name AS name, age AS age FROM Person")
    List<PersonSummary> findAllProjectedJpaQuery();

    @Query(value = "SELECT name AS name, age AS age FROM Person", nativeQuery = true)
    List<PersonSummary> findAllProjectedNativeQuery();

    PersonSummary findProjectedById(long id);

    @Query(value = "SELECT name AS name, age AS age FROM Person WHERE id = ?1")
    PersonSummary findProjectedByIdJpaQuery(long id);

    @Query(value = "SELECT name AS name, age AS age FROM Person WHERE id = ?1", nativeQuery = true)
    PersonSummary findProjectedByIdNativeQuery(long id);

In the case of the native query which returns a list, it seems to work but the properties are in the wrong order - the name property contains the age value and the age property contains the name value. I think re-ordering the properties in the projection or query might resolve this but it seems rather fragile.

In the case of the native query which returns a single projection a org.springframework.core.convert.ConversionFailedException exception is thrown.

I know that a native query isn't necessary in this case but it's a demonstration of an issue I encountered whilst trying it on another project.

Thanks!

---

Affects: 1.10.4 (Hopper SR4)

Reference URL: https://github.com/roberthunt/spring-data-native-query-projection

Issue Links:

• DATACMNS-927 Projection Column Mapping Issue
  ("is duplicated by")
• DATAJPA-1209 Compatibility with Hibernate < 5.2.11 broken for projections on native queries

Backported to: 1.11.8 (Ingalls SR8), 1.10.12 (Hopper SR12)

0 votes, 7 watchers

[spring-projects-issues]

Oliver Drotbohm commented

The root cause of all this is that native queries currently don't get the extra treatment that's necessary to produce a proper result the downstream ResultProcessor can handle. I've tried to adapt that logic, but it looks like Hibernate currently doesn't support handing a Tuple (i tried Map, too) to the creation of a native query, although that works for non-native queries.

I guess we need to get that fixed in Hibernate first before we can proceed here. I can't file any tickets in the Hibernate bug tracker

[spring-projects-issues]

Michael Gmeiner commented

Another possible solution would be to rely on the order of the getters in the projection-interface

[spring-projects-issues]

Oliver Drotbohm commented

We can't as the reflection APIs of Java don't guarantee them being returned in any particular order. Besides the fact, that that'd be quite a brittle solution.

One workaround I could imagine to parse the query for used aliases, and the use their order to deduce the keys for the Map entries by mapping their position to the index of the collection of returned values. That might cause issues with queries with more complex select clauses but it would at least produce a reliable error

[spring-projects-issues]

Oliver Drotbohm commented

Would anyone of you mind going ahead and filing a ticket that Tuple cannot be handed to EntityManager.createNativeQuery(…) but only to EntityManager.createQuery(…)?

[spring-projects-issues]

Oliver Drotbohm commented

For reference, Vlad from the Hibernate team was so kind to go ahead. See the link above

[spring-projects-issues]

Arnold Galovics commented

I fixed that ticket. Is there anything else which must be done before proceeding with this?

[spring-projects-issues]

Oliver Drotbohm commented

Looks good to me. I'll have a look early next week

[spring-projects-issues]

Oliver Drotbohm commented

That's in place now in master. Note that Kay will be based on Hibernate 5.2 (5.2.11 at the time of writing). Ingalls and Hopper will need to be run on 5.2.11 or better as well to see this working but stay generally compatible with Hibernate 3.x even

[spring-projects-issues]

Arnold Galovics commented

Great, thank you Oliver

[spring-projects-issues]

Thomas Lang commented

Hello together, i have been watching this issue, because it affected my source as well. I have updated to spring boot 1.5.8 and hibernate 5.2.12 as of today. The projection interface is beeing built up in the right way, but i have a strange naming issue.

1. The repository:

/**
     * Does a native query to get the invoices assigned for the given users and groups coded in the similarToPattern
     *
     * @param similarToPattern the similar to pattern (postgres specific as "%tlang%|%Seminare%"
     * @param state            a given invoice state
     * @return Iterable of InvoiceProjection
     */
    @Query(value = "select i.invoice_id as invoiceId, i.booking_tag as bookingTag, i.invoice_number as invoiceNumber, " +
            "i.created_by as createdBy, i.invoice_api_groups as groups, i.invoice_date as invoiceDate, r.recipient as recipient " +
            "from invoice i inner join recipient r on (i.recipient_recipient_id = r.recipient_id) where i.invoice_state = ?2 " +
            "and i.invoice_api_groups similar to ?1 order by i.invoice_date desc", nativeQuery = true)
    Iterable<InvoiceProjection> findAllProjectedByInvoiceStateAndUser(String similarToPattern, int state);

Please mind the naming: "as invoiceId" and especially the capital I in Id

2. The projection Interface:

/**
 * Projection interface for spring data jpa.
 * takes the resultset of a spring data jpa query.
 *
 * @author tlang
 */
public interface InvoiceProjection {

    /**
     * The invoice id.
     *
     * @return long
     */
    long getInvoiceId();

    /**
     * The booking tag.
     *
     * @return String
     */
    String getBookingTag();

    /**
     * The invoice number.
     *
     * @return String
     */
    String getInvoiceNumber();

    /**
     * The invoice date.
     *
     * @return LocalDate
     */
    LocalDate getInvoiceDate();

    /**
     * The invoice recipient.
     *
     * @return String
     */
    String getRecipient();

    /**
     * The invoice state
     *
     * @return InvoiceState
     */
    InvoiceState getInvoiceState();

    /**
     * The created by property.
     *
     * @return String
     */
    String getCreatedBy();

    /**
     * The modified by property.
     *
     * @return String
     */
    String getModifiedBy();
}

3. The error:

allProjectedBlaBlaBlaByInvoiceStateAndUser.forEach((s) -> {
            System.out.println(s.getRecipient());
            System.out.println(s.getInvoiceId());
        });

When i iterate the result set (Iterable<InvoiceProjection>) and print the s.getInvoiceId() property then the value is NULL.
When i change the property name to long getInvoiceid(); the value is right, please mind the lowercase i in id.

[spring-projects-issues]

Oliver Drotbohm commented

Hm, that's weird, as were actually using the JDK's PropertyDescriptor to lookup the values. Would you mind debugging into ResultProcessor.processResult(…) to see whether the source map actually contains entries with the keys reflecting the camel case results that you expect (the source keys delivered by you JPA provider that is). Also it might worth checking AbstractJpaQuery.TupleConverter.convert(…) whether the Tuple instances returned expose the aliases as expected

[spring-projects-issues]

Thomas Lang commented

Dear Oliver, thank you for your reply.

I have debugged into your suggested method. And indeed, when the native Projection query was on its turn, the naming was wrong.
This means the property was named invoiceid instead of invoiceId.

Please see these additional hibernate logs:
The first is a spring data jpa projection query (query by name, aka, findByFirstNameAndLastName, but with the same interface). The hibernate logs show the right naming.
The second query is a spring data jpa projection native query (for postgres with the same interface). The hibernate logs show the wrong naming.

final Iterable<InvoiceProjection> userInvoices = invoiceRepository.findAllProjectedByInvoiceStateAndUser(createdBy, invoiceState);
final Iterable<InvoiceProjection> userInvoicesTakeTwo = invoiceRepository.findAllProjectedBlaBlaBlaByInvoiceStateAndUser("%tlang%|%Seminare%", invoiceState.ordinal());

2017-11-03 09:21:11.228 DEBUG 10436 --- [lTaskExecutor-1] org.hibernate.SQL                        : select invoice0_.invoice_id as col_0_0_, invoice0_.booking_tag as col_1_0_, invoice0_.invoice_number as col_2_0_, invoice0_.invoice_date as col_3_0_, recipient1_.recipient as col_4_0_ from public.invoice invoice0_ inner join public.recipient recipient1_ on invoice0_.recipient_recipient_id=recipient1_.recipient_id where invoice0_.invoice_state=? and invoice0_.created_by=? order by invoice0_.invoice_date desc

2017-11-03 09:21:16.011 DEBUG 10436 --- [lTaskExecutor-1] org.hibernate.SQL                        : select i.invoice_id as invoiceId, i.booking_tag as bookingTag, i.invoice_number as invoiceNumber, i.created_by as createdBy, i.invoice_api_groups as groups, i.invoice_date as invoiceDate, r.recipient as recipient from invoice i inner join recipient r on (i.recipient_recipient_id = r.recipient_id) where i.invoice_state = ? and i.invoice_api_groups similar to ? order by i.invoice_date desc

I don´t know how to upload images here to show you the debug output from the ResultProcessor?
Please don´t mind giving you a link to our public gitlab page, where the image is saved within a wiki:
DATAJPA-980 tlang

I hope this is helpful for you?
Please let me know if you need further information.

By the way:
Very nice and very handy job with spring data jpa. This is an outstanding work. I have a lot of projets going on with the .net core world and in detail with their entity framework core or mapper. And with that i have to code all queries by hand. You guys do a very great job. Thank you for that! It really makes the developers` daily business more comfortable!

Kind regards
Thomas Lang

[spring-projects-issues]

Jens Schauder commented

This is probably coming from the database itself.

At least Oracle seems to be case agnostic in column and table names. But what is really happening is, it is converting everything to upper case. So by default, there is no difference between as invoiceId and as invoiceid or as INVOICEID. You can though quote column and table names (including aliases I think) with double quotes, so using as "invoiceId" should fix the problem for an Oracle database and something similar probably exists for the database of your choice.

Side note: I recommend opening a separate issue. Comments on closed issues get easily lost if we can't react imediatly

[spring-projects-issues]

Thomas Lang commented

Hey there Jens,
thank you for your help and the tip. I have created a new issue here: https://jira.spring.io/browse/DATAJPA-1225 ...
I have another question on quoting the column names, which i asked on the new issue.

Thank you!
Kind regards!
Thomas