This is an automated email from the ASF dual-hosted git repository.
ashishvijaywargiya pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/ofbiz-framework.git
The following commit(s) were added to refs/heads/trunk by this push:
new 8fc2801019 Fixed: I tried setting up ofbiz on hsql embedded db. And
fixed the below configuration setting.
8fc2801019 is described below
commit 8fc28010196d7258720891dde14ad4f7c65bb1a4
Author: Ashish Vijaywargiya <[email protected]>
AuthorDate: Tue May 26 10:44:26 2026 +0530
Fixed: I tried setting up ofbiz on hsql embedded db. And fixed the below
configuration setting.
I ran ./gradlew cleanAll loadAll but it failed due to a SQL syntax error
specific to HSQLDB (user lacks privilege or object not found:
CONTENT_ID_START). This happens because OFBiz was configured to use aliased
columns in the WHERE clause, which HSQLDB does not support.
I've fixed this by setting alias-view-columns="false" for the localhsql
datasource in entityengine.xml.
Additional details:
Here is exactly what happens and why it fails on HSQLDB:
1. What alias-view-columns="true" Does
When this is set to true, OFBiz will try to use the newly defined alias in
the WHERE clause.
For example, OFBiz generates a query that looks like this:
SELECT CA.CONTENT_ID AS CONTENT_ID_START, CO.DESCRIPTION
FROM CONTENT_ASSOC CA
LEFT OUTER JOIN CONTENT CO ON CA.CONTENT_ID_TO = CO.CONTENT_ID
WHERE CONTENT_ID_START = '12345'
Notice how it uses the alias CONTENT_ID_START inside the WHERE clause.
2. Why it Fails with HSQLDB
HSQLDB is strictly compliant with the ANSI SQL standard. According to the
SQL standard, a database evaluates the clauses of a query in a specific order:
FROM (and JOINs)
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Because the WHERE clause is evaluated before the SELECT clause, the SQL
engine doesn't know what CONTENT_ID_START is yet! It only knows about the
original columns in the tables.
When HSQLDB reaches the WHERE clause and sees CONTENT_ID_START, it throws
the error we saw: user lacks privilege or object not found: CONTENT_ID_START
(Note: Some older or looser database dialects allow aliases in the WHERE
clause as a non-standard convenience, but strict databases like HSQLDB,
PostgreSQL, and Oracle do not).
3. What alias-view-columns="false" Does
By setting this to false, we instruct OFBiz to fall back to using the
original, fully-qualified table aliases and column names in the WHERE clause.
The generated SQL changes to:
SELECT CA.CONTENT_ID AS CONTENT_ID_START, CO.DESCRIPTION
FROM CONTENT_ASSOC CA
LEFT OUTER JOIN CONTENT CO ON CA.CONTENT_ID_TO = CO.CONTENT_ID
WHERE CA.CONTENT_ID = '12345'
Because CA.CONTENT_ID actually exists in the FROM clause, HSQLDB accepts it
without any issues. If you look closely at the entityengine.xml file, OFBiz
actually sets alias-view-columns="false" by default for almost all other modern
databases (H2, PostgreSQL, MySQL, Oracle), but the localhsql configuration
block is quite old and hadn't been updated to reflect this stricter compliance.
---
framework/entity/config/entityengine.xml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/framework/entity/config/entityengine.xml
b/framework/entity/config/entityengine.xml
index 61aa972600..724fba47e6 100644
--- a/framework/entity/config/entityengine.xml
+++ b/framework/entity/config/entityengine.xml
@@ -135,7 +135,7 @@ access. For a detailed description see the
core/docs/entityconfig.html file.
use-foreign-key-indices="true"
use-fk-initially-deferred="false"
join-style="ansi-no-parenthesis"
- alias-view-columns="true">
+ alias-view-columns="false">
<read-data reader-name="tenant"/>
<read-data reader-name="seed"/>
<read-data reader-name="seed-initial"/>
