FLOAT lost sign in query

[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.

[Shalex]

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.

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]

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]

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]

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]

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]

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.