FLOAT lost sign in query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
candychiu
Posts: 4
Joined: Tue 04 Aug 2015 13:04

FLOAT lost sign in query

Post by candychiu » Tue 04 Aug 2015 13:21

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.

Shalex
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: FLOAT lost sign in query

Post by Shalex » Tue 04 Aug 2015 14:13

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.

candychiu
Posts: 4
Joined: Tue 04 Aug 2015 13:04

Re: FLOAT lost sign in query

Post by candychiu » Tue 18 Aug 2015 17:19

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.

Shalex
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: FLOAT lost sign in query

Post by Shalex » Thu 20 Aug 2015 10:42

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.

candychiu
Posts: 4
Joined: Tue 04 Aug 2015 13:04

Re: FLOAT lost sign in query

Post by candychiu » Thu 20 Aug 2015 16:21

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);

candychiu
Posts: 4
Joined: Tue 04 Aug 2015 13:04

Re: FLOAT lost sign in query

Post by candychiu » Thu 20 Aug 2015 17:39

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.

Shalex
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: FLOAT lost sign in query

Post by Shalex » Fri 21 Aug 2015 17:03

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.

Post Reply