search

google-code-export / dataobjectsdotnet

Automatically exported from code.google.com/p/dataobjectsdotnet
0 stars 0 forks source link

.NET standard function mapping issues #88

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 

1.

This test haven't failed earlier. And also some other TimeSpan related
test failes now.

Test:

  [Test]
  public void DateTime_opGreaterOrEqualTest()
  {
   var ts2 = new TimeSpan(1, 2, 3, 4, 5);
   var method = MethodInfo.GetCurrentMethod().Name;
   using(Domain.OpenSession())
   using(Transaction.Open())
   {
    var e = FetchTestEntity();
    e.FDateTime = new DateTime(2004, 1, 2, 3, 4, 5, 6);
    var q = from t in Query<AllTypes>.All
        select new
        {
         Value1 = t.FDateTime >= t.FDateTime - ts2,
         Value2 = t.FDateTime >= t.FDateTime,
         Value3 = t.FDateTime >= t.FDateTime + ts2,
         Method = method
        };
    foreach(var item in q)
    {
     Assert.AreEqual(e.FDateTime >= e.FDateTime - ts2, item.Value1);
     Assert.AreEqual(e.FDateTime >= e.FDateTime, item.Value2);
     Assert.AreEqual(e.FDateTime >= e.FDateTime + ts2, item.Value3);
    }
   }
  }

SQL:

SELECT "a"."column", "a"."column1", "a"."column2", "a"."column3" FROM
(SELECT "b"."ID", "b"."FDateTime", ("b"."FDateTime" >=
("b"."FDateTime" - '1 days 2:3:4.005')) AS "column", ("b"."FDateTime"
>= "b"."FDateTime") AS "column1", ("b"."FDateTime" >= ("b"."FDateTime"
+ '1 days 2:3:4.005')) AS "column2", 'DateTime_opGreaterOrEqualTest'
AS "column3" FROM (SELECT "c"."ID", "c"."FDateTime" FROM
"public"."AllTypes" "c") "b") "a" ORDER BY "a"."ID" ASC NULLS FIRST
6664|127.0.0.1(1972)|do4test|do4test|1319|2009-05-23 20:39:17.593 CEST
ERROR:  invalid input syntax for type timestamp: "1 days 2:3:4.005"

The solution is to replace '1 days 2:3:4.005' to '1 days 2:3:4.005'::interval
I don't know how you generate this, but TranslateLiteral() in SqlDom
appends this cast.

2. The return value of the operation is not casted (in SQL) to the
corresponding type of the return value of the corresponding .NET
operation

I enumerate the situations I discovered:

Math.BigMul. I reported this earlier.

TestCase 'Do4Tests.v8_3.LinqTestsPgSql83.Math_BigMulTest'
failed: System.InvalidCastException : A megadott típuskonverzió érvénytelen.
a következő helyen: Npgsql.NpgsqlDataReader.GetInt64(Int32 i)
...

BigMul operation compiler should cast the return value to bigint,
which is the corresponding return type of the .NET BigMul

I mean this:
Math.BigMul(int a, int b)  --->  CAST((a * b) AS bigint)

Other such scenarioes:

- TestCase 'Do4Tests.v8_3.LinqTestsPgSql83.Math_SignDecimalTest'
failed: System.InvalidCastException : A megadott típuskonverzió érvénytelen.
a következő helyen: Npgsql.NpgsqlDataReader.GetInt32(Int32 i)

- TestCase 'Do4Tests.v8_3.LinqTestsPgSql83.Math_SignDoubleTest'
failed: System.InvalidCastException : A megadott típuskonverzió érvénytelen.
a következő helyen: Npgsql.NpgsqlDataReader.GetInt32(Int32 i)

- TestCase 'Do4Tests.v8_3.LinqTestsPgSql83.Math_SignInt32Test'
failed: System.InvalidCastException : A megadott típuskonverzió érvénytelen.
a következő helyen: Npgsql.NpgsqlDataReader.GetInt32(Int32 i)

3. Math.Ceil(), Math.Floor(), Math.Truncate()

For Ceil 3.4 I get 3, but expect 4.
For Floor -3.4 I get -3, but expect -4.
For Truncate 3.7 I get 4m but expect 3.

  [Test]
  public void Math_CeilingDecimalTest()
  {
   var method = MethodInfo.GetCurrentMethod().Name;
   using(Domain.OpenSession())
   using(Transaction.Open())
   {
    var e = FetchTestEntity();
    e.Fdecimal = 3.4m;
    var q = from t in Query<AllTypes>.All
        select new
        {
         Value1 = Math.Ceiling(t.Fdecimal),
         Value2 = Math.Ceiling(-t.Fdecimal),
         Method = method
        };
    foreach(var item in q)
    {
     Assert.AreEqual(Math.Ceiling(e.Fdecimal), item.Value1);
     Assert.AreEqual(Math.Ceiling(-e.Fdecimal), item.Value2);
    }
   }
  }

SELECT "a"."column", "a"."column1", "a"."column2" FROM (SELECT
"b"."ID", "b"."Fdecimal", ceil("b"."Fdecimal") AS "column", ceil((-
"b"."Fdecimal")) AS "column1", 'Math_CeilingDecimalTest' AS "column2"
FROM (SELECT "c"."ID", "c"."Fdecimal" FROM "public"."AllTypes" "c")
"b") "a" ORDER BY "a"."ID" ASC NULLS FIRST
But select ceil(3.4)

  [Test]
  public void Math_FloorDecimalTest()
  {
   var method = MethodInfo.GetCurrentMethod().Name;
   using(Domain.OpenSession())
   using(Transaction.Open())
   {
    var e = FetchTestEntity();
    e.Fdecimal = 3.4m;
    var q = from t in Query<AllTypes>.All
        select new
        {
         Value1 = Math.Floor(t.Fdecimal),
         Value2 = Math.Floor(-t.Fdecimal),
         Method = method
        };
    foreach(var item in q)
    {
     Assert.AreEqual(Math.Floor(e.Fdecimal), item.Value1);
     Assert.AreEqual(Math.Floor(-e.Fdecimal), item.Value2);
    }
   }
  }

SELECT "a"."column", "a"."column1", "a"."column2" FROM (SELECT
"b"."ID", "b"."Fdecimal", floor("b"."Fdecimal") AS "column", floor((-
"b"."Fdecimal")) AS "column1", 'Math_FloorDecimalTest' AS "column2"
FROM (SELECT "c"."ID", "c"."Fdecimal" FROM "public"."AllTypes" "c")
"b") "a" ORDER BY "a"."ID" ASC NULLS FIRST

  [Test]
  public void Math_TruncateDecimalTest()
  {
   var method = MethodInfo.GetCurrentMethod().Name;
   using(Domain.OpenSession())
   using(Transaction.Open())
   {
    var e = FetchTestEntity();
    e.Fdecimal = 3.7m;
    var q = from t in Query<AllTypes>.All
        select new
        {
         Value1 = Math.Truncate(t.Fdecimal),
         Value2 = Math.Truncate(-t.Fdecimal),
         Method = method
        };
    foreach(var item in q)
    {
     Assert.AreEqual(Math.Truncate(e.Fdecimal), item.Value1);
     Assert.AreEqual(Math.Truncate(-e.Fdecimal), item.Value2);
    }
   }
  }

SELECT "a"."column", "a"."column1", "a"."column2" FROM (SELECT
"b"."ID", "b"."Fdecimal", (CASE WHEN ("b"."Fdecimal" > 0) THEN
floor("b"."Fdecimal") ELSE ceil("b"."Fdecimal") END) AS "column",
(CASE WHEN ((- "b"."Fdecimal") > 0) THEN floor((- "b"."Fdecimal"))
ELSE ceil((- "b"."Fdecimal")) END) AS "column1",
'Math_TruncateDecimalTest' AS "column2" FROM (SELECT "c"."ID",
"c"."Fdecimal" FROM "public"."AllTypes" "c") "b") "a" ORDER BY
"a"."ID" ASC NULLS FIRST

I can see that it is based on ceil and floor, that's why it's wrong also.

All these functions are tested with the double data type, and that
does not show this wrong behaviour. I don't understand...

4. Trim not yet implemented

5. TimeSpan parser for PgSql is not yet perfect, millisecond is problematic:

TestCase 'Do4Tests.v8_3.LinqTestsPgSql83.TimeSpan_ParseTest'
failed: TimeSpan_ParseTest: These values were parsed wrong:
expected: 00:00:00.2000000 actual: 00:00:00.0020000
expected: -00:00:00.2000000 actual: -00:00:00.0020000
expected: 00:00:00.0200000 actual: 00:00:00.0020000
expected: -00:00:00.0200000 actual: -00:00:00.0020000
expected: -6360.11:25:03.2100000 actual: -6360.11:25:03.0210000
expected: 6360.11:25:03.2100000 actual: 6360.11:25:03.0210000
expected: -6361.12:34:56.7900000 actual: -6361.12:34:56.0790000

6. Rounding

SQL rounding is different than the default .NET rounding. .NET rounds
to even by default, but SQL not.

Original issue reported on code.google.com by denis.kr...@gmail.com on 25 May 2009 at 12:57

GoogleCodeExporter commented 9 years ago

Original comment by alex.yakunin on 26 May 2009 at 6:51

GoogleCodeExporter commented 9 years ago

Original comment by alex.yakunin on 31 May 2009 at 2:36

GoogleCodeExporter commented 9 years ago

Original comment by alex.yakunin on 31 May 2009 at 5:33

GoogleCodeExporter commented 9 years ago

Original comment by denis.kr...@gmail.com on 1 Jun 2009 at 2:13

GoogleCodeExporter commented 9 years ago 
(1) is fixed via explicit cast from string to interval
(2) fixed

(3) still an issue, but decimal is correclty mapped now
(4) technical limitations, can not be fixed for now
(5) need exact input values to test correctness of parsing
(6) still an issue

Original comment by denis.kr...@gmail.com on 7 Jun 2009 at 2:48

GoogleCodeExporter commented 9 years ago

Original comment by alex.yakunin on 7 Jun 2009 at 4:09

GoogleCodeExporter commented 9 years ago 
(5) fixed

Original comment by denis.kr...@gmail.com on 8 Jun 2009 at 12:39

GoogleCodeExporter commented 9 years ago

Original comment by alex.yakunin on 12 Jun 2009 at 2:27

GoogleCodeExporter commented 9 years ago

Original comment by alex.yakunin on 12 Jun 2009 at 2:38

GoogleCodeExporter commented 9 years ago 
(3) ceil floor truncate implemented correctly
(4) trim implemented
(6) implemented. but there are issues with precision & round implementation on
different servers

Original comment by denis.kr...@gmail.com on 16 Jun 2009 at 8:27