Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrectly generate CREATE SEQUENCE when using decimal #26562

Closed
bobbyangers opened this issue Nov 7, 2021 · 1 comment
Closed

Incorrectly generate CREATE SEQUENCE when using decimal #26562

bobbyangers opened this issue Nov 7, 2021 · 1 comment
Assignees
Labels
area-migrations area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Milestone

Comments

@bobbyangers
Copy link
Contributor

bobbyangers commented Nov 7, 2021

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10 Pro 21H1 19043.1320
IDE: Visual Studio 2019 16.11.5

when using DbContext.Datase.EnsureCreated();

if the dbContext contains a sequence using decimal, ie
for example >>

modelBuilder.HasSequence<decimal>("CountByDecimal", "dbo").StartsAt(593).IncrementsBy(82).IsCyclic(false).HasMin(5).HasMax(777777);

this generates:

CREATE SEQUENCE [dbo].[CountByDecimal] AS decimal(18,2) START WITH 593 INCREMENT BY 82 MINVALUE 5 MAXVALUE 777777 NO CYCLE;

But this fails to execute
Msg 11702, Level 16, State 2, Line 3
The sequence object 'dbo.CountByDecimal' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

This works

CREATE SEQUENCE [dbo].[CountByDecimal] AS decimal(18,0) START WITH 593 INCREMENT BY 82 MINVALUE 5 MAXVALUE 777777 NO CYCLE;

/*  or */

CREATE SEQUENCE [dbo].[CountByDecimal] AS decimal START WITH 593 INCREMENT BY 82 MINVALUE 5 MAXVALUE 777777 NO CYCLE;

SqlServerMigrationsSqlGenerator.cs && MigrationsSqlGenerator.cs

protected override void Generate(
            CreateSequenceOperation operation,
            IModel? model,
            MigrationCommandListBuilder builder)

        {
           /* ....  */
            if (operation.ClrType != typeof(long))
            {
                var typeMapping = Dependencies.TypeMappingSource.GetMapping(operation.ClrType);

                builder
                    .Append(" AS ")
        >>>            .Append(typeMapping.StoreType);
                 /*  clean any scale : decimal(18,2) should be decimal(18,0) or simple decimal */
                          /*  Maybe change to :  .Append(typeMapping.StoreTypeNameBase); */
            }

            builder
                .Append(" START WITH ")
                .Append(IntegerConstant(operation.StartValue));

            SequenceOptions(operation, model, builder);

            builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);

            EndStatement(builder);
        }

File a bug

Remember:

  • Please check that the documentation does not explain the behavior you are seeing.
  • Please search in both open and closed issues to check that your bug has not already been filed.
@bobbyangers
Copy link
Contributor Author

bobbyangers commented Nov 7, 2021

   public SqlServerTypeMappingSource(

 /* ... */

            _clrTypeMappings
                = new Dictionary<Type, RelationalTypeMapping>
                {
                    { typeof(int), _int },
                    { typeof(long), _long },
                    { typeof(DateTime), _datetime2 },
                    { typeof(Guid), _uniqueidentifier },
                    { typeof(bool), _bool },
                    { typeof(byte), _byte },
                    { typeof(double), _double },
                    { typeof(DateTimeOffset), _datetimeoffset },
                    { typeof(short), _short },
                    { typeof(float), _real },
      /*L163*/     { typeof(decimal), _decimal182 },    /* << this seems to have changed from _decimal to _decimal182, 
                                                         this was chnaged 2020-07-28
                                                        */
                    { typeof(TimeSpan), _time }
                };

Is there an easy way to override this when creating a sequence of type decimal ?

bobbyangers added a commit to bobbyangers/efcore that referenced this issue Nov 8, 2021
bobbyangers added a commit to bobbyangers/efcore that referenced this issue Nov 8, 2021
        - Generated SQL >> CREATE SEQUENCE wrong when using decimal
        was generating (18, 2) which SQL returned an error
        - Added tests for int, long, short, byte, decimal

Fixes dotnet#26562
bobbyangers added a commit to bobbyangers/efcore that referenced this issue Nov 8, 2021
        - Generated SQL >> CREATE SEQUENCE wrong when using decimal
        was generating (18, 2) which SQL returned an error
        - Added tests for int, long, short, byte, decimal

Fixes dotnet#26562
@roji roji self-assigned this Nov 8, 2021
bobbyangers added a commit to bobbyangers/efcore that referenced this issue Nov 8, 2021
        - Generated SQL >> CREATE SEQUENCE wrong when using decimal
        was generating (18, 2) which SQL returned an error
        - Added tests for int, long, short, byte, decimal

Fixes #26562Summary of the changes

        - Generated SQL >> CREATE SEQUENCE wrong when using decimal
        was generating (18, 2) which SQL returned an error
        - Added tests for int, long, short, byte, decimal

Fixes dotnet#26562
bobbyangers added a commit to bobbyangers/efcore that referenced this issue Nov 8, 2021
- Generated SQL >> CREATE SEQUENCE wrong when using decimal
   was generating (18, 2) which SQL returned an error
- Added tests for int, long, short, byte, decimal

Fixes dotnet#26562
bobbyangers added a commit to bobbyangers/efcore that referenced this issue Nov 8, 2021
- Generated SQL >> CREATE SEQUENCE wrong when using decimal
   was generating (18, 2) which SQL returned an error
- Added tests for int, long, short, byte, decimal

Fixes dotnet#26562
@roji roji closed this as completed in dbc86fd Nov 8, 2021
@roji roji changed the title incorrectly generate CREATE SEQUENCE when using decimal Incorrectly generate CREATE SEQUENCE when using decimal Nov 8, 2021
@roji roji added this to the 7.0.0 milestone Nov 8, 2021
@ajcvickers ajcvickers added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Nov 16, 2021
@ajcvickers ajcvickers modified the milestones: 7.0.0, 7.0.0-preview1 Feb 14, 2022
@ajcvickers ajcvickers modified the milestones: 7.0.0-preview1, 7.0.0 Nov 5, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-migrations area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Projects
None yet
Development

No branches or pull requests

3 participants