Pomelo.entityframeworkcore.mysql: Problem with linq SUM

[msmolka]

Steps to reproduce

Simple model where field is type byte -> tinyint
public class Survey { public byte Rating {get;set;} }
Run following query:

var rating = context.Surveys.Sum(s => s.Rating);

The issue

Above query casing exception (see bellow). If cast rating to decimal

var rating = context.Surveys.Sum(s => (decimal)s.Rating);
there is no error, but application is stopping working with SQL Server, because it complains about casting opposite way.

Exception message:
Stack trace:

InvalidCastException: Unable to cast object of type 'System.Decimal' to type 'System.Int32'.

    MySql.Data.MySqlClient.Results.Row.GetInt32(int ordinal)
    lambda_method(Closure , DbDataReader )

[yukozh]

@bgrainger Should this be an upstream issue?

[bgrainger]

@msmolka can you paste the output from running SHOW COLUMNS FROM Surveys on your MySQL Server? (Assuming the table name is Surveys.)

[msmolka]

Hello
here is my output:

COLUMNS

| Field | Type | Null | Key | Default | Extra |
| - | - | - | - | - | - |
| Id | int(11) | NO | PRI | | auto_increment |
| FeedbackId | int(11) | NO | MUL | | |
| Rating | tinyint(3) unsigned | NO | | | |
| Type | tinyint(3) unsigned | NO | | | |
| CreatedDate | datetime(6) | NO | | CURRENT_TIMESTAMP(6) | |

At this moment to support both MySql and SqlServer I need to have conditional linq:

 var surveys = databaseProvider.Equals("MySql", StringComparison.OrdinalIgnoreCase)
                ? await context.Surveys.Where(s => (s.Feedback.CustomerId == this.CustomerId) && ((s.Type == 0) || (s.Type == 1)))
                    .SumAsync(f => (decimal)f.Rating, cancellationToken)
                : await context.Surveys.Where(s => (s.Feedback.CustomerId == this.CustomerId) && ((s.Type == 0) || (s.Type == 1)))
                    .SumAsync(f => f.Rating, cancellationToken);

It is not working without casting in MySql. Below is my model

    public class Survey
    {
        public DateTimeOffset CreatedDate { get; set; }
        public virtual Feedback Feedback { get; set; }
        public int FeedbackId { get; set; }
        public int Id { get; set; }
        public byte Rating { get; set; }
        public byte Type { get; set; }    
    }

and model builder

builder.ToTable("Survey");
builder.HasKey(u => u.Id);
builder.Property(u => u.Id).ValueGeneratedOnAdd();
builder.Property(u => u.CreatedDate).ValueGeneratedOnAdd();

[bgrainger]

I pushed a new test to try to reproduce this issue as reported: https://github.com/mysql-net/MySqlConnector/commit/89aed4a81f6c944f0bc2bc5eaf37e07f68daac9c

I created a table with a column Rating tinyint(3) unsigned not null then invoked MySqlDataReader.GetInt32(0) (which should call into Row.GetInt32, as given in your call stack. I was unable to reproduce an InvalidCastException from System.Decimal: AppVeyor, Travis.

I'm testing with MySQL 5.7 on Windows and Linux.

@msmolka What MySQL Server and version are you using?

I haven't repro'ed the problem with EF (since my knowledge of that is more limited). @caleblloyd would EF introduce any additional wrinkles here that could cause the connector to deserialize a tinyint(3) value as a decimal?

(Or am I reading the exception/call stack wrongly?)

[msmolka]

Hello
I've attached simple project together with table create script. Tested on MariaDB and MySQL. I don't think it is related to MySQL version.

ConsoleApp2.zip

As you can see it is crashing on line 36

I think it is related to #52

[bgrainger]

I can reproduce with the sample app and will dig in further.

[bgrainger]

The executed statement is SELECT SUM(s.Rating) FROMSurveyASs;. MySQL appears to promote the type of this result from tinyint(3) to 0xF6 (decimal). This is why MySqlConnector throws an exception casting a decimal to an int.

[caleblloyd]

Just checked the MySQL docs

For numeric arguments, the variance and standard deviation functions return a DOUBLE value. The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).

Our EF layer is designed to save a single byte as an unsigned tinyint. So summing the tinyints in the DB is correctly (by MySQL's design) returning a decimal.

By LINQ's definition, we probably should be returning a Byte here though. I'll look at some casting options in the EF layer tomorrow.

[bgrainger]

By LINQ's definition, we probably should be returning a Byte here though.

Seems like it could be better to promote it to Int32 (to avoid truncation); that may be what SQL Server is doing here. (Enumerable.Sum doesn't appear to have an overload for byte for LINQ to Objects.)

[caleblloyd]

I wasn't sure if LINQ supplied a generic for SUM that had the same return type as argument type.

If avoiding truncation is our primary concern, the result would be best left as a decimal for all integer sums.

If parity with SQL server is our primary concern, we'd need to see what they do for all of their different integer types.

@kagamine what's your thoughts?

[bgrainger]

Promoting all Sum calls to decimal would be unexpected, I think. LINQ to Objects returns the same type as the input (for overloads it defines) but can throw, e.g., new[] { Int32.MaxValue, Int32.MaxValue }.Sum() throws an OverflowException.

If we added implicit conversion from decimal to int (in MySqlConnector), we'd get roughly the same behaviour: return int by default but throw OverflowException for large values.

Existing issue at mysql-net/MySqlConnector#54.

[yukozh]

lgtm

[bgrainger]

Fixed the MySqlConnector issue in 0.11.2.

[yukozh]

Shipped in 1.1.1-prerelease-10012