0037112: incorrect HQL to retrieve preferences - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0037112

Openbravo ERP

A. Platform

public

2017-10-19 10:21

2018-01-03 12:38

Reporter

alostale

Assigned To

alostale

Priority

normal

Severity

minor

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

3.0PR18Q1

Merge Request Status

Review Assigned To

caristu

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0037112: incorrect HQL to retrieve preferences

Description

When retrieving preferences without organization, the HQL that is generated has unbalanced parentheses.

Even it's an incorrect HQL, Hibernate manages to somehow generate a valid SQL (although it removes some parts).

Note that most of the times organization parameter is used, this issue appears only when it is not. Main user is login page that has not organization yet.

This same HQL fails in newer (5.2) Hibernate versions.

Steps To Reproduce

Open login page and check hql generated for ForcedLoginURL preference [1] and the SQL it's finally executed.

HQL:

 as p  
 where (coalesce(p.visibleAtClient, '0')='0')  
 and (p.visibleAtRole is null)      
 and (coalesce(p.visibleAtOrganization, '0')='0')) 
 and (p.userContact is null)  
 and (p.window is null)  
 and p.propertyList = 'Y' 
 and p.property = ?  
 order by p.AD_Window_ID

SQL:

  ...
  from AD_Preference adpreferen0_ 
  where coalesce(adpreferen0_.VisibleAt_Client_ID, '0')='0' 
  and (adpreferen0_.VisibleAt_Role_ID is null) 
  and coalesce(adpreferen0_.VisibleAt_Org_ID, '0')='0' 
  and (adpreferen0_.AD_User_ID is null) 
  and (adpreferen0_.AD_Window_ID is null) 
  and adpreferen0_.IsPropertyList='Y' 
  and adpreferen0_.Property=?

Removing the extra parenthesis in the HQL, the correct SQL is:

  from AD_Preference adpreferen0_ 
  where coalesce(adpreferen0_.VisibleAt_Client_ID, '0')='0' 
  and (adpreferen0_.VisibleAt_Role_ID is null) 
  and coalesce(adpreferen0_.VisibleAt_Org_ID, '0')='0' 
  and (adpreferen0_.AD_User_ID is null) 
  and (adpreferen0_.AD_Window_ID is null) 
  and adpreferen0_.IsPropertyList='Y' 
  and adpreferen0_.Property=? 
  and (adpreferen0_.AD_Org_ID in ('0'))
  and (adpreferen0_.AD_Client_ID in ('0')) 
  and adpreferen0_.IsActive='Y' 
  order by adpreferen0_.AD_Preference_ID

---
[1] https://code.openbravo.com/erp/devel/pi/file/c77412df1f7e/src/org/openbravo/erpCommon/businessUtility/Preferences.java#l455 [^]

Proposed Solution

Additional Information

Tags

No tags attached.

Relationships

related to	defect	0037130	closed	marvintm	Retail Modules	Terminal authentication is being activated even if the preference is set to 'N'
related to	defect	0038655	closed	alostale	Openbravo ERP	incorrect query in UserLock
blocks	feature request	0037064	closed	caristu	Openbravo ERP	upgrade hibernate to 5.3.2
blocks	defect	0037324	closed	alostale	Openbravo ERP	slow login having many preferences