CODEWITHSUNDEEP
hibernatehqljpql
prettyvoid
prettyvoid

Reputation: 3686

Query failing unless I reorder the values in SELECT statement

I'm trying to do HQL query but I don't understand why the query fails if I don't reorder the values in the SELECT statement

Following query does not work

Query 1

@Query("SELECT uf.flight.arrivalAirport.iataCode, uf.flight.flightStatus " +
        "FROM UserFlight uf WHERE uf.flight.id=?1 AND uf.user.id=?2")

Generated SQL

select airport2_.iata_code as col_0_0_, 
flight1_.flight_status_id as col_1_0_, 
flightstat4_.id as id1_6_, 
flightstat4_.code as code2_6_, 
flightstat4_.description as descript3_6_ 
from user_flight userflight0_, 
flight flight1_, 
airport airport2_ 
inner join flight_status flightstat4_ 
on flight1_.flight_status_id=flightstat4_.id 
where userflight0_.flight_id=flight1_.id 
and flight1_.arrival_airport_id=airport2_.id 
and userflight0_.flight_id=? 
and userflight0_.user_id=?

Exception

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'flight1_.flight_status_id' in 'on clause'

If I change the query to the following (just reordering SELECT values)

Query 2

@Query("SELECT uf.flight.flightStatus, uf.flight.arrivalAirport.iataCode " +
        "FROM UserFlight uf WHERE uf.flight.id=?1 AND uf.user.id=?2")

Generated SQL

select flight1_.flight_status_id as col_0_0_, 
airport4_.iata_code as col_1_0_, 
flightstat2_.id as id1_6_, 
flightstat2_.code as code2_6_, 
flightstat2_.description as descript3_6_ 
from user_flight userflight0_, 
flight flight1_ 
inner join flight_status flightstat2_ 
on flight1_.flight_status_id=flightstat2_.id, 
airport airport4_ 
where userflight0_.flight_id=flight1_.id 
and flight1_.arrival_airport_id=airport4_.id 
and userflight0_.flight_id=? 
and userflight0_.user_id=?

It works and I get the results from the database.

Can anybody tell me why is one of them working and the other is not?

Note: FlightStatus is an entity and not a string value.

Upvotes: 1

Views: 68

Answers (1)

Joe Taras
Joe Taras

Reputation: 15389

The error is here:

In the first query you have:

...
airport airport2_ 
inner join flight_status flightstat4_ 
on flight1_.flight_status_id=flightstat4_.id 
...

So the interpreter thinks you want to link airport2_ and flighstat4_, so the ON clause isn't correct

Instead, in the second query, you have:

...
flight flight1_ 
inner join flight_status flightstat2_ 
on flight1_.flight_status_id=flightstat2_.id, 
...

The ON clause is correct about two tables flight1_ and flighstat2_

EDIT

When you write:

SELECT uf.flight.arrivalAirport.iataCode, uf.flight.flightStatus

The interpreter decomposes objects in this order:

Processes the first field:

uf (user_flight) -> flight (flight) -> arrivalAirport (airport)

So processes the second field:

uf (user_flight) -> flight (flight)

These two objects already present in the tables, so it reuses them but, the order of JOIN is already defined so you have the first layout (with error).

When you change the order of field, the second field (of first query) has been processed by first and the old first as second, so you have the second lasyout without error.

I hope, I'm clean in my explanation :)

Upvotes: 1

Related Questions