Return-Path: 
 <dev-return-20882-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org>
X-Original-To: apmail-openjpa-dev-archive@www.apache.org
Delivered-To: apmail-openjpa-dev-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 8B1FFDD8F
	for <apmail-openjpa-dev-archive@www.apache.org>;
 Fri, 24 Aug 2012 02:35:44 +0000 (UTC)
Received: (qmail 23426 invoked by uid 500); 24 Aug 2012 02:35:44 -0000
Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org
Received: (qmail 22779 invoked by uid 500); 24 Aug 2012 02:35:43 -0000
Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:dev-help@openjpa.apache.org>
List-Unsubscribe: <mailto:dev-unsubscribe@openjpa.apache.org>
List-Post: <mailto:dev@openjpa.apache.org>
List-Id: <dev.openjpa.apache.org>
Reply-To: dev@openjpa.apache.org
Delivered-To: mailing list dev@openjpa.apache.org
Received: (qmail 22562 invoked by uid 99); 24 Aug 2012 02:35:43 -0000
Received: from arcas.apache.org (HELO arcas) (140.211.11.28)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 24 Aug 2012 02:35:43 +0000
Received: from arcas.apache.org (localhost [127.0.0.1])
	by arcas (Postfix) with ESMTP id B2C452C0A67
	for <dev@openjpa.apache.org>; Fri, 24 Aug 2012 02:35:42 +0000 (UTC)
Date: Fri, 24 Aug 2012 13:35:42 +1100 (NCT)
From: "Jody Grassel (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Message-ID: <1584699551.9026.1345775742732.JavaMail.jiratomcat@arcas>
In-Reply-To: 
 <906589528.21637.1330933864299.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Closed] (OPENJPA-2149) Criteria.function adds wrong casts
 to parameters making it unsuable
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


     [ https://issues.apache.org/jira/browse/OPENJPA-2149?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jody Grassel closed OPENJPA-2149.
---------------------------------

    
> Criteria.function adds wrong casts to parameters making it unsuable
> -------------------------------------------------------------------
>
>                 Key: OPENJPA-2149
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2149
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: criteria, kernel
>    Affects Versions: 2.0.1, 2.0.2, 2.1.1
>         Environment: Standalone 2.0.0 and 2.0.1 downloaded from openjpa website, and on WebSphere 7 JPA2 feature pack 1.0.0.5 (contains OpenJPA 2.0.2)
>            Reporter: Aviram Segal
>            Assignee: Albert Lee
>            Priority: Critical
>             Fix For: 2.0.3, 2.1.2, 2.3.0, 2.2.1
>
>         Attachments: OPENJPA-2149-2.0.x.patch
>
>
> Criteria.function will generate an SQL with only the last parameter casted and to the wrong type.
> 		Expression<String> stPointFunc = cb.function(
> 				"db2gse.st_point", 
> 				String.class,
> 				cb.literal(0.0),
> 				cb.literal(0.0),
> 				cb.literal(1003));
> 		
> 		Expression<Double> distanceFunc = cb.function(
> 				"db2gse.st_distance", 
> 				Double.class, 
> 				stPointFunc, 
> 				usersLocations.get("location"));
> 		
> 		criteriaQuery.select(usersLocations).where(cb.lessThan(distanceFunc, cb.literal(50.0)));
> Will generate the following SQL:
> (db2gse.st_distance(db2gse.st_point(?, ?, CAST(? AS DOUBLE)), t0.LOCATION) < ?)
> Notice the 3rd parameter is an Integer and its being cast as Double.
> The problem is in org.apache.openjpa.jdbc.kernel.exps.DatastoreFunction#appendTo
> Line 54:  args.appendTo(sel, ctx, state, sql, 0);
> Will append 3 ? to the sql buffer: "(db2gse.st_distance(db2gse.st_point(?, ?, ?"
> Then the loop in line 56-58
>             for (int i = 1; i < vals.length; i++) {
>                 sql.addCastForParam(getOperator(), vals[i]);
>             }
> It becomes: "(db2gse.st_distance(db2gse.st_point(?, ?, CAST(? AS DOUBLE)"
> Starts with 1 (second parameter and not the first one), whil sql.addCastForParam only works for the last ? in the sql buffer, meaning the cast for the param at index 1 is added to the last ? and the method will not do anything else.
> This issue leaves Criteria.function useless to me, I tried extending my DBDictionary to remove all the cast as a work around but the function became ambiguous.
> Thanks in advance.
> Found a temporary (working but ugly) workaround:
> 		Expression<String> stPointFunc = cb.function(
> 				"db2gse.st_point", 
> 				String.class,
> 				cb.coalesce(cb.literal(0.0), cb.literal(0.0)),
> 				cb.coalesce(cb.literal(1.0), cb.literal(1.0)),
> 				cb.coalesce(cb.literal(1003), cb.literal(1003)));
> coalesce uses raw value instead of parameters and makes it work (the same value twice becuase if I put cb.nullLiteral I get a NullPointerException, might be another bug)

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira