Page 1 of 1
FLOAT lost sign in query
Posted: Tue 04 Aug 2015 13:21
by candychiu
Hi, I am querying an Oracle table which has a FLOAT field, as shown in Oracle SQL Developer.
Through the IQToolkit and Linqpad, this field shows up as FLOAT(22). Switching to Linq, the field is mapped to Decimal.
When I query this field, for some reason, the sign of the number is lost. For example, 2522 was returned for -2522 using both SQL and Linq.
Has anyone seen this before? Thank you.
Re: FLOAT lost sign in query
Posted: Tue 04 Aug 2015 14:13
by Shalex
candychiu wrote:Through the IQToolkit and Linqpad, this field shows up as FLOAT(22). Switching to Linq, the field is mapped to Decimal.
Please specify the ORM framework you are working with. For example, here is a default mapping for Entity Framework:
https://www.devart.com/dotconnect/oracl ... pping.html.
candychiu wrote:When I query this field, for some reason, the sign of the number is lost. For example, 2522 was returned for -2522 using both SQL and Linq.
Please try the newest (
8.5.464) build of dotConnect for Oracle which includes the fix for a similar issue.
Re: FLOAT lost sign in query
Posted: Tue 18 Aug 2015 17:19
by candychiu
The latest version did not resolve the issue.
I am running Debug/Any CPU. Mappings were generated by dotConnect for Oracle.
[Column(TypeName = "FLOAT")]
public decimal? NumberField { get; set; }
modelBuilder.Entity<SomeTable>()
.Property(e => e.NumberField)
.HasPrecision(29, 15);
The code is compiled with VS 2013 Update 5. I suspect it uses the Roslyn compiler. Debug/x86 didn't work neither.
Re: FLOAT lost sign in query
Posted: Thu 20 Aug 2015 10:42
by Shalex
We cannot reproduce the problem at the moment. Please
send us a small test project with the corresponding DDL/DML script for reproducing. Also specify the exact versions of your Oracle server and Oracle client.
Re: FLOAT lost sign in query
Posted: Thu 20 Aug 2015 16:21
by candychiu
I played with it a little more, and found that the new version of dotConnect to Oracle "fixed" the bug. I see that if the mapping of a NULL FLOAT is decimal instead of decimal?, things work out right. Also if I redirect the output, as in "select new { NegNum = i.SomeField }", it also works fine. Would you explain the behavior, and how are null values handled in this scenario?
For reference, here is what I see:
[Table("SomeTable")]
public partial class SomeTable
{
[Column(TypeName = "FLOAT")]
public decimal? SomeField { get; set; } // Option 1
//public decimal SomeField { get; set; } // Option 2
}
// scenario 1: Option 1, failed
var query = from i in SomeTable select i;
var count = query.Count(i => i.SomeField < 0);
// scenario 2: Option 2, succeeded
var query = from i in SomeTable select i;
var count = query.Count(i => i.SomeField < 0);
// scenario 3: Option 1, succeeded
var query = from i in SomeTable select new { SomeField = i.SomeField };
var count = query.Count(i => i.SomeField < 0);
Re: FLOAT lost sign in query
Posted: Thu 20 Aug 2015 17:39
by candychiu
I found another column which also negative nullable FLOAT in the database. This column works as expected. The column I am having issue with stores big numbers, hundreds of billions. Will it cause a problem?
The problem I am seeing also did not occur in SQL. Unfortunately, I don't have a way to recreate the scenario because I have read-only access to the database I am working with.
Re: FLOAT lost sign in query
Posted: Fri 21 Aug 2015 17:03
by Shalex
Try to narrow down the problem to the ADO.NET level. For example:
Code: Select all
using (var conn = new OracleConnection()) {
//conn.ConnectionString = "server=orcl1120;uid=***;pwd=***;";
conn.ConnectionString = "server=dboracle;direct=true;sid=orcl1120;uid=***;pwd=***;";
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select floatColumn from testTable";
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
var test = reader.GetDecimal(0);
} // set a break point and check the value of the test variable
}
}
If possible, localize the issue and
send us a small test project with the corresponding DDL/DML script for reproducing. Also specify the exact versions of your Oracle server and Oracle client.