Icon View Incident Report

Serious Serious
Reported By: Roy Lambert
Reported On: 10/1/2013
For: Version 2.14 Build 1
# 3904 Comparing an INTEGER Value to a FLOAT/DECIMAL Value Results in Automatic Truncation of FLOAT/DECIMAL

I wanted to reduce the amount of data in one of the tabkes I'm preparing for the demo of my query generator. I decided a sufficiently "random" method would be to divide the id column by 11 and wherever it was divisible exactly by 11 keep that. _ID is an autoinc (or more formally "_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,)

My first attempt is below.

The first two columns are there to see what's going on because I was astounded that the entire table was selected. To give an idea the first row is

90909, 90909.1818181818

to me that says NOT EQUAL but there they are bold as brass! At this point I thought it looks as though when comparing a float to an integer the float is truncated.

select floor(_ID / 11) , (_ID/11), * from companies where floor(_ID / 11) = (_ID/11)

Comments Comments and Workarounds
The workaround is:

select floor(_ID / 11) , (_ID/11), * from companies where CAST(floor(_ID / 11) AS FLOAT) = (_ID/11)

Resolution Resolution
Fixed Problem on 10/9/2013 in version 2.14 build 2

Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial