Query comparisons for big decimals fail on Sqlite

[ajcvickers]

With this model:

public class Rider
{
    public int Id { get; set; }

    public decimal Mount { get; set; }
}

public class XContext : DbContext
{
    public DbSet<Rider> Riders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlite("Filename=etetest.db");
}

This works:

[Fact]
public void Main()
{
    using (var context = new XContext())
    {
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.Add(
            new Rider
            {
                Mount = 222211110000m
            });

        context.SaveChanges();
    }

    using (var context = new XContext())
    {
        Assert.Equal(1, context.Set<Rider>().Count(e => e.Mount == 222211110000m));
    }
}

But making the value bigger fails:

[Fact]
public void Main()
{
    using (var context = new XContext())
    {
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.Add(
            new Rider
            {
                Mount = 3333222211110000m
            });

        context.SaveChanges();
    }

    using (var context = new XContext())
    {
        // Now fails to match
        Assert.Equal(1, context.Set<Rider>().Count(e => e.Mount == 3333222211110000m));
    }
}

[smitpatel]

Probably type affinity thing mentioned here:

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class.

We store data as TEXT but we generate literal of REAL.
SQL in above case

      SELECT COUNT(*)
      FROM "Riders" AS "e"
      WHERE "e"."Mount" = 3333222211110000.0

So for smaller no equality works fine. For bigger no. type affinity fails and equality fails (probably due to being different types). If we compare with TEXT literal then it works.

[bricelam]

We can make this work by using TEXT literals for decimal values, but we might lose the ability to perform other comparisons and arithmetic operations in SQL.

[bricelam]

Related to #10534

[bricelam]

Digging into this, I think we should switch to TEXT literals. I can't foresee any regressions. 🤞 The operators appear do the right(?) thing.