npgsql: Numeric stopped working on Amazon Redshift

Steps to reproduce

CREATE TABLE performance.testtable (
    id varchar(36) encode zstd distkey,
    "created"  timestamp DEFAULT ('now'::text)::timestamp without time zone encode raw,
    clientid int4 DEFAULT 0 encode raw,
    openingbalance numeric(18, 6) DEFAULT 0
)

INSERT INTO performance.testtable
(id, clientid, openingbalance)
VALUES('b505877f-29dd-44af-b1f8-4f05f91b2a62', 644, 450.66);

INSERT INTO performance.testtable
(id, clientid, openingbalance)
VALUES('761dc502-f35b-4b21-8f50-0bae6416a8cc', 644, 129.88);

INSERT INTO performance.testtable
(id, clientid, openingbalance)
VALUES('a49f7a83-43d6-4ff5-a33d-ba266871808e', 644, -40.6);

    [Table("testtable")]
    public class TestTable
    {
        [Key]
        public string Id { get; set; }
        public int ClientId { get; set; }
        public decimal OpeningBalance { get; set; }
    }   

 var aggregatedSecurities = await _context.TestTable
        .Where(p => p.ClientId == request.ClientId)
        .GroupBy(p => new {p.ClientId})
        .Select(p => new PortfolioSecurityPerformanceItem
        {
            ClientId = p.Key.ClientId,
            OpeningBalance = p.Sum(i => i.OpeningBalance)
        }).ToListAsync();

    public class PortfolioSecurityPerformanceItem
    {
        public int ClientId { get; set; }
        public decimal OpeningBalance { get; set; }
    }

Execution of this causes Numeric value does not fit in a System.Decimal.

The issue

I don’t expect this to happen. I would expect the decimal to be returned, I have seen some post saying if you define the numeric as having more than 29 digits you can get this error but I am seeing this when the precision is (18,6). There is something going on under the covers I cannot see. Probably some return value from redshift. This has only started happening after AWS upgrade the new build to Build 1.0.28965, 1.0.29551. Any help would be appreciated.

SQL produced
"SELECT t. ClientId , COALESCE(SUM(t. OpeningBalance ), 0.0) AS  OpeningBalance  FROM performance.testtable AS t WHERE t. ClientId  = @__request_ClientId_0 GROUP BY t. ClientId "

Exception message: Numeric value does not fit in a System.Decimal
Stack trace:
   at Npgsql.Internal.TypeHandlers.NumericHandlers.DecimalRaw.Multiply(DecimalRaw& value, UInt32 multiplier)
   at Npgsql.Internal.TypeHandlers.NumericHandlers.NumericHandler.<Read>d__10.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler.Read[TAny](NpgsqlReadBuffer buf, Int32 len, FieldDescription fieldDescription)
   at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
   at Npgsql.NpgsqlDataReader.GetDecimal(Int32 ordinal)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.ReadDecimal(DbDataReader reader, Int32 ordinal, ReaderColumn column)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.ReadRow()
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.<InitializeAsync>d__90.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.<InitializeAsync>d__27.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.<InitializeAsync>d__27.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__19.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__19.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.<InitializeReaderAsync>d__19.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass30_0`2.<<ExecuteAsync>b__0>d.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteImplementationAsync>d__31`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteImplementationAsync>d__31`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteAsync>d__30`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.<MoveNextAsync>d__18.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()

Numeric value does not fit in a System.Decimal

Further technical details

Npgsql version: 6.0.0-preview5 PostgreSQL version: Redshift Build 1.0.29551 (https://forums.aws.amazon.com/ann.jspa?annID=8817) Operating system: Local windows

This all works on Build 1.0.29306 https://forums.aws.amazon.com/ann.jspa?annID=8775

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 18 (9 by maintainers)

Most upvoted comments

Redshift has fixed this issue in the latest release (1.0.30409). I am trying to get more details and will post if/when I get more.

mattdone01 on Sep 4, 2021

Just made a redshift cluster (version 1.0.29551) and run the example above - doesn’t look like it fails. Tested with npgsql 5.0.7 and npgsql 6.0.0 preview5.

var csb = new NpgsqlConnectionStringBuilder()
{
	Host = "redshift",
	Port = 5439,
	ServerCompatibilityMode = ServerCompatibilityMode.Redshift,
	Username = "postgres",
	Database = "dev",
	Password = "Master1234"
};

await using var conn = new NpgsqlConnection(csb.ConnectionString);
await conn.OpenAsync();

await using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT t.\"ClientId\", COALESCE(SUM(t.\"OpeningBalance\"), 0.0) AS OpeningBalance FROM \"testtable\" AS t GROUP BY t.\"ClientID\"";
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
	var id = reader.GetInt32(0);
	var sum = reader.GetDecimal(1);
}

The returned sum is 539.940000.

vonzshik on Aug 26, 2021

Thanks. One thing to note for anyone reading this is to make sure you trail the maintenance packs in production. Our prod env was on the current track and our app stopped working. It was a head scratcher to figure out what was going on.

mattdone01 on Aug 16, 2021

Sounds very similar to #3843 - with older Redshift versions. I’m not sure what to say - I’d try flagging this AWS support to see if the change is intentional and is expected to persist (or maybe they’ll fix it again as they did in #3843). This seems to make Redshift incompatible with the standard PostgreSQL decimal representation, which isn’t a good thing.

roji on Aug 16, 2021