search

demetrixbio / FSharp.Data.Npgsql

F# type providers to support statically typed access to input parameters and result set of sql statement in idiomatic F# way. Data modifications via statically typed tables.
Apache License 2.0
128 stars 15 forks source link

TimeSpan updates incorrectly #76

Closed sandeepc24 closed 4 years ago

sandeepc24 commented 4 years ago

I am updating a row using DataTable and the TimeSpan column is not updated correctly.

The value set in the code is 1 day 9 hours but it is persisted as "1 day 02:09:36" in db.

sandeepc24 commented 4 years ago

I have tested this using Npgsql and that work correctly.

sandeepc24 commented 4 years ago

Can someone tell me how to debug the code so that I can see what is going wrong?

sandeepc24 commented 4 years ago

Can someone please help us with this? Our project is stuck because of this issue, we are getting incorrect numbers because of this issue.

kerams commented 4 years ago

Rebuild the FSharp.Data.Npgsql.sln, then open the Tests.sln (close Tests.sln and repeat this step if you make any changes in the type provider)

Comment out NpgsqlCmdTests.fs and NpgsqlConnectionTests.fs if you just want to diagnose the issue against your database.

In Program.fs paste the code that's causing you problems.

From the FSharp.Data.Npgsql.sln VS instance attach the debugger to devenv.exe of the other instance.

If you now modify the connection string static parameter, you'll be able to hit breakpoints in InformationSchema.fs

If you modify any command, you'll be able to hit breakpoints in NpgsqlCommandProvider and NpgsqConnectionProvider.

=== All of the above is debugging the design-time part of the provider.

To debug the runtime bit (ISqlCommand.fs in particular), change Tests.fsproj to an executable and type Console.ReadKey () just above the line where you execute the problematic command.

Keeping in mind the fact that you need to rebuild and close solutions with any modification, launch the Tests program without pressing anything.

From the FSharp.Data.Npgsql.sln VS instance attach to Tests.exe, place breakpoints in Runtime.fsproj and only then press a key in the Tests progam's window to continue execution, hopefully hitting your breakpoints.

sandeepc24 commented 4 years ago

It's this change causing the issue.

If the issue occurs then IMO it needs to be fixed further up - in DbCommandBuilder, not at this level. IMHO this change needs to be reverted.

@melanore could you please have a look and share your thoughts?

daz10000 commented 4 years ago

Apologies for delta replying. Managing our local response to virus outbreak :(. Would really help me to see a piece of test code that reproduces. Most of the issues we have seen with timezones are related to subtle misunderstanding of the Postgres semantics. To make sure it is a bug I love to reproduce it exactly as a failing test ideally. I can take a look tomorrow. Sorry this is holding you up

Darren

Am 21.03.2020 um 7:26 PM schrieb Sandeep Chandra notifications@github.com:

 It's https://github.com/demetrixbio/FSharp.Data.Npgsql/blob/master/src/Runtime/CommandBuilder.fs#L37 causing the issue.

If the issue occurs then IMO it needs to be fixed further up - in DbCommandBuilder, not at this level. IMHO this change needs t0 be reverted.

@melanore could you please have a look and share your thoughts?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

sandeepc24 commented 4 years ago

Attached is a test app. It has DDL and sql to insert test data. TestApp.zip

Take your time, response to coronavirus has higher priority than this.

daz10000 commented 4 years ago

Thanks for the app - just to confirm I can reproduce the bug which is a good start. Debugging to try and understand what is happening. Your theory might be right about the resetdbtype. It looks like it's getting cast badly at some point

daz10000 commented 4 years ago

Hey just an update on this - have been wading through it slowly. I haven't set up testing for a long time on this so had some cleanup to do but have ported your example into a failing unit test, updated and documented the test setup and cleaned a bunch of failing unit tests. There's a branch https://github.com/demetrixbio/FSharp.Data.Npgsql/tree/20/03/BUG-interval_updates_corrupting you can follow if you want to help with the actual debugging but I kind of understand now how it's coming up with that strange time interval (converting 33 hours into 1 day 2 hours 9 minutes 36 seconds). That happens to be 1.09 (1.089999 actually) days (decimal days), so something is doing a horrible cast of 1,09 from 1 day 09 hours to 1.09 days.

image

@melanore suggested we might need to also set DbType in here - am going to try that. Have had a bit of trouble getting debugger to step into this code since it's an override - rider seems to only let me breakpoint the underlying method but will resort to print statements if necessary till I kill this. Thanks for your patience.

image

melanore commented 4 years ago

Fixed in https://www.nuget.org/packages/FSharp.Data.Npgsql/0.2.4-beta Thanks @daz10000 for figuring out the root cause and @sandeepc24 for reporting an issue.

daz10000 commented 4 years ago

Quick postscript on this one - thanks @sandeepc24 as well, this flushed out some awkwardness in enum handling we had been seeing so fixed two problems for price of one bug fix. Always nice.

sandeepc24 commented 4 years ago

Thanks guys for fixing this issue.

sandeepc24 commented 4 years ago

Just updated to latest version and I get following error

FSC : error FS3031: The type provider 
'\.nuget\packages\fsharp.data.npgsql\0.2.4-beta\lib\netstandard2.0\FSharp.Data.Npgsql.dll' 
reported an error: Assembly attribute 'TypeProviderAssemblyAttribute' refers to a designer 
assembly 'FSharp.Data.Npgsql.DesignTime' which cannot be loaded or doesn't exist. 
Could not load file or assembly 
'\.nuget\packages\fsharp.data.npgsql\0.2.4-beta\lib\netstandard2.0\FSharp.Data.Npgsql.DesignTime.dll'. 
The system cannot find the file specified.
melanore commented 4 years ago

That's the known design time issue with the .net core type provider. Could you try to switch FSAC to net/mono in Ionide (or any other IDE you are using)?

sandeepc24 commented 4 years ago

I installed version 16.5 of Visual Studio and that is causing this issue.

kerams commented 4 years ago

Same problem now with .NET 5 Preview. Both 0.2.3 and 0.2.4 :(

melanore commented 4 years ago

Error seems to be caused by this issue - https://github.com/demetrixbio/FSharp.Data.Npgsql/issues/78. Hopefully fix suggested by @cartermp will work.