possible bug with cfsqltype="cf_sql_float", wrong value inserted into database

[whostheJBoss]

I'll try to explain as best I can. I am seeing this issue only on
Railo, CF8 and CF9 both insert the value correctly. I'm not sure if
this is a bug on the Railo side or what, but below is the behavior I'm
seeing (this is migrated from a discussion in the Transfer group where
Mark suggested that this was a bug in Railo).

Anyway, I am using Transfer and have run into an issue where the value
I'm trying to insert into the database is being changed and inserted
incorrectly.

The column I am trying to insert into is a BIGINT column in an innoDB
table on MySQL 5.1

I am doing the following:

user = instance.Transfer.new("users.user");
user.setCustomID(rc.customID);
user.setNote(rc.customID);

The value of rc.customID is 1474075992.

When I check my database, the note field, VARCHAR(45), is shown
correctly as: 1474075992

But the customID field which is BIGINT(20) is shown as: 1474076030

Both values are populated at the same time using the same value.

When doing the insert via Transfer the debug shows:

Source C:\Program Files (x86)\Apache Software Foundation\Tomcat
6.0\sites\foo\ROOT\transfer\com\sql\QueryExecution.cfc
Execution Time 0
Recordcount 0
Query INSERT INTO users(email,password,accountType,customID,hash)
VALUES ( '' , '' , 0 , 1474075992 , '' )

But the value in the database shows up as: 1474076030

If I copy and paste the query exactly and run it without Transfer
like
this:

<cfquery datasource="foo" name="add">
INSERT INTO users(email,password,accountType,customID,hash) VALUES
( '' , '' , 0 , 1474075992 , '' )
</cfquery>

The value is inserted correctly and the debug shows:

Source C:\Program Files (x86)\Apache Software Foundation\Tomcat
6.0\sites\foo\ROOT\playground\insert.cfm
Execution Time 0
Recordcount 0
Query INSERT INTO users(email,password,accountType,customID,hash)
VALUES ( '' , '' , 0 , 1474075992 , '' )

So when inserting with Transfer the value gets inserted incorrectly,
but running the EXACT same query without Transfer inserts it fine.

I posted this on the Transfer group and had a long discussion where I
was assuming Transfer was the problem. Turns out I was wrong... sort
of.

In:

transfer.com.sql.QueryExecution

This line:

<cfelseif block.mapparam.type eq "numeric">
<cfqueryparam value="#value#" cfsqltype="cf_sql_float"
list="#param.list#" null="#param.isNull#">

If I replace it with:

<cfelseif block.mapparam.type eq "numeric">
<cfqueryparam value="#value#" cfsqltype="CF_SQL_BIGINT"
list="#param.list#" null="#param.isNull#">

It works and inserts the correct value. Obviously I can't leave it
this way since I have other fields that are not BIGINT and will cause
the same problem for those fields, but this is definitely where the
problem is.

I didn't notice it at first because the debug doesn't show the
cfsqltype so I didn't think about that.

So, all numeric values are being set to float by Transfer. Mark has
confirmed this:

"Sounds like a bug in Railo to me.
Mark"

I asked in return:

"You happen to know what part of Transfer the bug is affecting? I'd
be
happy to take it up with the guys at Railo, just need to know what I
should be asking.

Is Transfer checking the column type of the objects and then using
that type to wrap them with a cfsqltype? Does that mean Railo is not
providing this information to Transfer, so then it is falling back on
the default cfsqltype for numeric, which in this case happens to be
float?

Basically, is Transfer unable to get the type from Railo so it runs
this:
<cfelseif block.mapparam.type eq "numeric">
<cfqueryparam value="#value#" cfsqltype="cf_sql_float"
list="#param.list#" null="#param.isNull#">

And sets it to float?

Thanks!"

Mark replied:

"numeric maps to using cf_sql_float.
cf_sql_float is a parameter that will correctly handle floating
points
across databases.
It seems that Railo has an issue with how it is mapping cf_sql_float,
so it
cannot handle BigInt values in the database you are using, whereas on
CF it
has no such trouble.
Mark"

I can confirm that CF8 and CF9 both handle this correctly according to
Mark's explanation.

Should I create a JIRA ticket? Or is this expected behavior?

[Barney Boisvert]

What happens if you change the line from "cf_sql_float"to
"cf_sql_double" or "cf_sql_decimal"? I bet it's a truncation issue,
where ColdFusion considers cf_sql_float and cf_sql_double to be
equivalent, but Railo is correctly using reduced precision for
cf_sql_float (causing your erroneous value). To put that another way,
I suspect a bug in ColdFusion (not reducing precision on cf_sql_float)
is masking a bug in Transfer (using an imprecise type for numerics)
that Railo's correct implementation (reducing cf_sql_float precision)
unmasks. :)

cheers,
barneyb

--
Barney Boisvert
bboi...@gmail.com
http://www.barneyb.com/

[whostheJBoss]

That was my assumption as well, since by changing the cfsqltype to
cf_sql_bigint worked fine! I suggested this but wasn't met with any
acceptance.

I didn't think that all numerics should be treated as float. Anyone
else have something to chime in with here?

> bboisv...@gmail.comhttp://www.barneyb.com/

[whostheJBoss]

Can someone confirm if this is something I need to take back up on the
original Transfer post? If so, I'd like to have a good case so he
doesn't send me back here again :)

> > > VALUES ( '' , '' , 0 ,1474075992, '' )

>
> > > But the value in the database shows up as:1474076030
>
> > > If I copy and paste the query exactly and run it without Transfer
> > > like
> > > this:
>
> > > <cfquery datasource="foo" name="add">
> > > INSERT INTO users(email,password,accountType,customID,hash) VALUES

> > > ( '' , '' , 0 ,1474075992, '' )

> > > </cfquery>
>
> > > The value is inserted correctly and the debug shows:
>
> > > Source  C:\Program Files (x86)\Apache Software Foundation\Tomcat
> > > 6.0\sites\foo\ROOT\playground\insert.cfm
> > > Execution Time  0
> > > Recordcount     0
> > > Query   INSERT INTO users(email,password,accountType,customID,hash)

> > > VALUES ( '' , '' , 0 ,1474075992, '' )

[Todd Rafferty]

If you need confirmation from Railo, please use JIRA.

~Todd Rafferty
Railo Community Manager -- Volunteer
http://getRailo.org/

[Barney Boisvert]

I'm pretty sure all that's needed for confirmation is to change the
type from cf_sql_float to cf_sql_decimal or cf_sql_double and confirm
that that fixes the issue (just like cf_sql_bigint) does.

[whostheJBoss]

Will do! Can't now because I'm mobile, but when I land I'll try it.

On Aug 6, 12:39 pm, Barney Boisvert <bboisv...@gmail.com> wrote:
> I'm pretty sure all that's needed for confirmation is to change the
> type from cf_sql_float to cf_sql_decimal or cf_sql_double and confirm
> that that fixes the issue (just like cf_sql_bigint) does.
>

> On Thu, Aug 6, 2009 at 12:37 PM, Todd Rafferty<t...@getrailo.org> wrote:
> > If you need confirmation from Railo, please use JIRA.
>
> > ~Todd Rafferty
> > Railo Community Manager -- Volunteer
> >http://getRailo.org/
>
> --
> Barney Boisvert

> bboisv...@gmail.comhttp://www.barneyb.com/

[Michael Offner-Streit]

Hi whostheJBoss

Railo translate CF_SQL_FLOAT to java.sql.Types.FLOAT
then java.sql.PreparedStatement.setFloat(int index,float value) is used
in this case,
i think adobeCF use java.sql.PreparedStatement.setDouble(int
index,double value) instead of setFloat.

but this is definitly the wrong way, like barney has written before
"cf_sql_double" or "cf_sql_decimal is the way to go.
from my perspective it is wrong to change this to "setDouble".

what do you thnk?

greetings micha


whostheJBoss schrieb:

--
Michael Offner-Streit
CTO
Railo Technologies GmbH
michael...@railo.ch
www.getrailo.com

Mailing List (english): http://groups.yahoo.com/group/railo_talk/
Mailing List (german): http://de.groups.yahoo.com/group/railo/
Linked in: http://www.linkedin.com/e/gis/71368/0CF7D323BBC1
Issue Tracker: http://jira.jboss.org/jira/browse/RAILO
Blog: http://www.railo-technologies.com/blog