Improve error message when required field is missing in query [DATAJDBC-613]

[spring-projects-issues]

benzen opened DATAJDBC-613 and commented

In a repository I have the following method

@Query("""
  select start, "end", timesheet.state from timesheet
  join time_entry on time_entry.timesheet = timesheet.id
  where
  time_entry.project_id in (:projectIds);
""")}}
public List<Timesheet> findByTimeEntryProjectIdIn(final List<Long> projectIds);

 

 

Here is the top part of the exception

An empty SqlIdentifier can't be used in to create column names
java.lang.UnsupportedOperationException: An empty SqlIdentifier can't be used in to create column names at org.springframework.data.relational.core.sql.SqlIdentifier$1.getReference(SqlIdentifier.java:68) at org.springframework.data.jdbc.core.convert.SqlGenerator.renderReference(SqlGenerator.java:705) at org.springframework.data.jdbc.core.convert.SqlGenerator.getBindMarker(SqlGenerator.java:152) at org.springframework.data.jdbc.core.convert.SqlGenerator.buildConditionForBackReference(SqlGenerator.java:234) at org.springframework.data.jdbc.core.convert.SqlGenerator.getFindAllByProperty(SqlGenerator.java:219) at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAllByPath(DefaultDataAccessStrategy.java:348)
 
It's unclear to me what is happening`` here.
I assume that it's related to the fact that one of my column use a reserved keyword.
Could not find anything related to this issue

---

Attachments:

• demo.zip (97.11 kB)

[spring-projects-issues]

benzen commented

I've tryed to changed my query to 

select timesheet.start, timesheet."end", timesheet.state from timesheet
 join time_entry on time_entry.timesheet = timesheet.id
 where
 time_entry.project_id in (:projectIds);

But this gives me the same result

[spring-projects-issues]

benzen commented

I tryed to change my field name from end, which is a reserved keyword in postgres, probably with other sql server too, and use endDate.

I'm still getting this exception

[spring-projects-issues]

benzen commented

It ended up beeing an error in my database.

Timesheet has a collection of timeEntry.

I had timeEntry with no timesheet id in them.

So it's only an issue with error reporting that is unclear to me at least

[spring-projects-issues]

benzen commented

There was a problem in my data, but it was not the source of my problem

[spring-projects-issues]

Jens Schauder commented

So the problem persists?

I'm currently not able to tell what is going wrong, it looks like it is ignoring the query and trying to derive the query form the method name.

Could you provide a complete reproducer, i.e. a project demonstrating the problem, that is ready to execute, including an (in memory) database setup?

[spring-projects-issues]

benzen commented

This might take quite some time for me to get enough  time to do this.

 

Is there a level of logging that i could turn on/up to see the actual query sent ?

 

I think something like show query on hibernate

[spring-projects-issues]

Jens Schauder commented

Since all queries are executed via a JdbcTemplate its logging features may be used.

This means setting the logging category org.springframework.jdbc.core.JdbcTemplate to DEBUG will yield the SQL statements executed.

[spring-projects-issues]

benzen commented

Here is the query logged

`2020-10-12 13:03:20.249 DEBUG 10349 --- [nio-9091-exec-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [ select`
` timesheet.start_date,`
` timesheet.end_date,`
` timesheet.state,`
` timesheet.author_id`
` from timesheet`
` join time_entry on time_entry.timesheet = timesheet.id`
` where`
` time_entry.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`
`]`

Which seams normal to me

[spring-projects-issues]

benzen commented

So I assume that my best luck is to provide a minimal exemple :)

[spring-projects-issues]

benzen commented

I just did, it seams to depend on the data.

If i request I make a search with a projectId that is not present in the db, i get an empty list as expected.

But when i find for timesheet with a project that i know is present, then i get the exception

[spring-projects-issues]

benzen commented

What happens is that my definition of timesheet has changed recently, I've added an id column.

But i've never added it into the my queries.

Know that I know what was my problem the error message is a little bit better.

Still i think it should be more clear.maybe something like 

Aggregate definition as fields that are not present in the query, ensure all fields of the aggreagate are defined in the query

[spring-projects-issues]

Jens Schauder commented

I agree, a better error message makes sense

[viktorardelean]

@spring-projects-issues I would like to improve the error messaging here.
From the last comment, I understand the issue was that a new "id" column was added to the "timesheet" DB table, and the code was using "GROUP BY" aggregation queries where the new column was missing.

My suggestion would be to change the error message from the SqlIdentifier from "An empty SqlIdentifier can't be used in to create column names" to "An empty SqlIdentifier can't be used in to create column names. Ensure that all selected fields are present in the aggregation clause"

Can I raise a PR with this?