This issue has been created
 
 
Newsletter Application / cid:jira-generated-image-avatar-9352c369-1123-40b2-a013-c223e87bbd0a XNEWSLET-10 Open

Several queries compare integer and date properties with strings

 
View issue   ยท   Add comment
 

Issue created

 
cid:jira-generated-image-avatar-da000096-6dfc-4a42-a87b-623795f4c818 Thomas Mortagne created this issue on 17/Nov/26 15:11
 
Summary: Several queries compare integer and date properties with strings
Issue Type: cid:jira-generated-image-avatar-9352c369-1123-40b2-a013-c223e87bbd0a Bug
Affects Versions: 1.1
Assignee: Unassigned
Created: 17/Nov/26 15:11
Priority: cid:jira-generated-image-static-major-9f7bffea-9f8a-4d61-b882-064320782dbc Major
Reporter: Thomas Mortagne
Description:

Several queries compare integer properties with a string (Code.InvoicesResults, Code.NewsletterSheet, Code.SendNewsletterInstances) and date properties with an empty string (Code.NewsletterLiveTableResultsMacros, Code.NewsletterSheet):

published.value='1'
active.value='1'
invoiceDate.value<>''
unsubDate.value<>''

Since Hibernate 6, HQL checks the types of both sides of a comparison when the query is parsed, and rejects incompatible ones (SemanticException: Cannot compare left expression of type ... with right expression of type ...). XWiki is moving to Hibernate 7 (XWIKI-19708), where these queries will fail on every database. With Hibernate 5 the literal is copied as is in the SQL, so the queries rely on the database converting it implicitly, which not all databases do.

Comparing a date with an empty string already fails on PostgreSQL, and never matches on Oracle (where an empty string is NULL).

Compare the integer properties with numbers (published.value = 1) and check that the date properties are set with is not null. This works with all the databases and all the Hibernate versions.