[OFBIZ-1121] EntityOperator.BETWEEN generates incorrect SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: Release Branch 4.0
• Component/s: framework

Environment

Any

• Assignee: Unassigned
• Reporter: Cameron Smith
• Created: 03/Jul/07 09:53
• Updated: 17/Sep/08 04:14
• Resolved: 15/Sep/07 06:47

Description

EntityOperator.BETWEEN generates incorrect SQL. For
instance, if I use something like (Java 1.5):

List criteria = new LinkedList();
criteria.add(1.0); criteria.add(10.0);
new EntityExpr("amount", EntityOperator.BETWEEN,
criteria);

The EntityExpr, if used in a delegator query, will
generate the following SQL:
... BETWEEN (1.0, 10.0)

However this syntax is incorrect in the following
RDBMS:
MySQL 5.0:
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
PostgreSQL 8.2:
http://www.postgresql.org/docs/8.2/static/functions-comparison.html
Oracle 9i: (couldn't be bothered to fight my way
through Oracle's online docs but I did check my ref.
book here and my own recollection of 8 years
programming with Oracle).

I originally put this problem to the ML and no-one had actually used EntityOperator.BETWEEN successfully, they had all used workarounds.

Issue Links

• duplicates: OFBIZ-1045 EntityOperator does not correctly construct SQL for BETWEEN clause