FLOAT lost sign in query

FLOAT lost sign in query

Postby 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.
candychiu
 
Posts: 4
Joined: Tue 04 Aug 2015 13:04

Re: FLOAT lost sign in query

Postby 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/oracle/docs/?DataTypeMapping.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.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: FLOAT lost sign in query

Postby 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.
candychiu
 
Posts: 4
Joined: Tue 04 Aug 2015 13:04

Re: FLOAT lost sign in query

Postby 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.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: FLOAT lost sign in query

Postby 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

Postby 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.
candychiu
 
Posts: 4
Joined: Tue 04 Aug 2015 13:04

Re: FLOAT lost sign in query

Postby 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.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle