Issue with datetime(6) column using timestamp(6) range

[dtminnaar]

When storing a record with the datetime value of 1000-01-01 00:00:00 I get the following error:
MySqlConnector.MySqlException (0x80004005): value "1000-01-01 00:00:00" is outside of timestamp(6) range

The column in the database is datetime(6), not timestamp(6).
Manually changing the value in the database works.

You can recreate this error by adding the following lines to Program.cs in this sample:

app.MapRazorPages();

// Test adding a movie with date 1000-1-1 00:00:00
using var scope = app.Services.CreateScope();
using var doltContext = scope.ServiceProvider.GetRequiredService<DoltContext>();
if (doltContext.Database.GetPendingMigrations().Any())
{
	doltContext.Database.Migrate();
	doltContext.Database.ExecuteSql($"call dolt_commit('-Am', Applied database migration);");
}

doltContext.Add(new Movie
{
	Genre ="test",
	Price = 1,
	ReleaseDate = new DateTime(1000, 1, 1, 0, 0, 0),
	Title = "test",
});

try
{
	doltContext.SaveChangesAndCommitAsync("added test movie").Wait();
}
catch (Exception ex)
{
	// Why is the type timestamp(6) used?
	Console.WriteLine(ex.ToString());
}

app.Run();

Updated sample to .net core 8.0.7
Updated Pomelo.EntityFrameworkCore.MySql to 8.0.2
Using docker image dolt-sql-server version 1.42.7

Best regards.

[fulghum]

Thanks for taking the time to report this issue @dtminnaar 🙏 Sorry we didn't notice it for a few days. We monitor the dolthub/dolt issue queue daily, so feel free to report issues there and we'll see them faster.

Thank you for the easy repro instructions. Thanks to those, I was able to quickly identify the query that Entity Framework is running against Dolt:

INSERT INTO `Movie` (`Id`, `Genre`, `Price`, `ReleaseDate`, `Title`) VALUES ('3f1a1895-06d8-4d29-a8aa-dab630d81ec0', 'test', 1, timestamp('1000-01-01 00:00:00.000000'), 'test');

Note the use of the timestamp function around the date. It looks like's Dolt's timestamp function is converting values to timestamp types (like the name seems to imply), while MySQL's timestamp function actually returns datetime values.

Here's a quick test I did with MySQL 8.3.0 to confirm that:

mysql -uroot --protocol TCP -e "select timestamp('1000-01-01 00:00:00');" --column-type-info
Field   1:  `timestamp('1000-01-01 00:00:00')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY 


+----------------------------------+
| timestamp('1000-01-01 00:00:00') |
+----------------------------------+
| 1000-01-01 00:00:00              |
+----------------------------------+

Once we fix Dolt's timestamp function to convert values to datetime instead of timestamp, that should resolve this issue. I'll keep digging in and update you as we make progress.

[timsehn]

I'm going to move this to the Dolt queue for visibility.

[fulghum]

I fixed the issue in the go-mysql-server library and it's building into dolt right now. I took a dev build of dolt with that change and confirmed that your repro code is now able to correctly insert a movie with a "1000-01-01 00:00:00" date. 🙌

I'll kick off a release once that build is finished so you can pick up the fix at your convenience.

Thanks again for helping us find this one! This was a good one to fix!

[fulghum]

@dtminnaar – Dolt 1.42.10 contains the fix for this one. Let us know if we can do anything else to help you build with Dolt!