[Maria-developers] Multiple default values for timestamps
Hey folks, On IRC I read somewhere that multiple timestamps cant have multiple default values using NOW() (error ER_TOO_MUCH_AUTO_TIMESTAMP_COLS). I tried to find out why this limitation is in place, but was not able to do so. The only thing I (or rather someone in #mysql and #maria) was able to find was this, in sql/field.cc (line 4729):
Actually SQL-99 says that we should allow niladic functions (like NOW()) as defaults for any field. Current limitations (only NOW() and only for one TIMESTAMP field) are because of restricted binary .frm format and should go away in the future.
I removed the check that spits out this error and updated the tests. Now I'll admit I'm not sure how to properly test this, but so far the entire testsuite passes. It may be that I'm missing any reasons for this limitation, or that tests may be missing/inaccurate. If you have any opinions on this, please let me know. You can find it in: lp:~freeaqingme/maria/improv-allowMultipleCurTimestampInDefaultOnTable Regards, Dolf Schimmel -- Freeaqingme p.s. Sorry for the long branchname, couldn't think of something shorter that was still descriptive :D
Hi, Dolf! On Jan 08, Dolf Schimmel wrote:
Hey folks,
On IRC I read somewhere that multiple timestamps cant have multiple default values using NOW() (error ER_TOO_MUCH_AUTO_TIMESTAMP_COLS). I tried to find out why this limitation is in place, but was not able to do so. The only thing I (or rather someone in #mysql and #maria) was able to find was this, in sql/field.cc (line 4729):
Actually SQL-99 says that we should allow niladic functions (like NOW()) as defaults for any field. Current limitations (only NOW() and only for one TIMESTAMP field) are because of restricted binary .frm format and should go away in the future.
I removed the check that spits out this error and updated the tests. Now I'll admit I'm not sure how to properly test this, but so far the entire testsuite passes. It may be that I'm missing any reasons for this limitation, or that tests may be missing/inaccurate. If you have any opinions on this, please let me know.
You can find it in: lp:~freeaqingme/maria/improv-allowMultipleCurTimestampInDefaultOnTable
Here's how to test it: create a table with few TIMESTAMP fields, say, four or five. Make some of them DEFAULT CURRENT_TIMESTAMP and others not - randomly, say second and fourth having DEFAULT CURRENT_TIMESTAMP and others not. Then insert few rows and see how it works. Regards, Sergei
Then insert few rows and see how it works. That was exactly the thing I was struggling with. How should I know in
Hey Sergei, Thanks for replying. the tests what the time was at the moment the query was executed? I did a few already, but I reckon they're probably insufficient. http://bazaar.launchpad.net/~freeaqingme/maria/improv-allowMultipleCurTimest... Tnx, Dolf -- Freeaqingme On Sun, Jan 9, 2011 at 9:59 PM, Sergei Golubchik <serg@askmonty.org> wrote:
Hi, Dolf!
On Jan 08, Dolf Schimmel wrote:
Hey folks,
On IRC I read somewhere that multiple timestamps cant have multiple default values using NOW() (error ER_TOO_MUCH_AUTO_TIMESTAMP_COLS). I tried to find out why this limitation is in place, but was not able to do so. The only thing I (or rather someone in #mysql and #maria) was able to find was this, in sql/field.cc (line 4729):
Actually SQL-99 says that we should allow niladic functions (like NOW()) as defaults for any field. Current limitations (only NOW() and only for one TIMESTAMP field) are because of restricted binary .frm format and should go away in the future.
I removed the check that spits out this error and updated the tests. Now I'll admit I'm not sure how to properly test this, but so far the entire testsuite passes. It may be that I'm missing any reasons for this limitation, or that tests may be missing/inaccurate. If you have any opinions on this, please let me know.
You can find it in: lp:~freeaqingme/maria/improv-allowMultipleCurTimestampInDefaultOnTable
Here's how to test it: create a table with few TIMESTAMP fields, say, four or five. Make some of them DEFAULT CURRENT_TIMESTAMP and others not - randomly, say second and fourth having DEFAULT CURRENT_TIMESTAMP and others not. Then insert few rows and see how it works.
Regards, Sergei
Hi, Dolf! On Jan 09, Dolf Schimmel wrote:
Hey Sergei,
Thanks for replying.
Then insert few rows and see how it works. That was exactly the thing I was struggling with. How should I know in the tests what the time was at the moment the query was executed? I did a few already, but I reckon they're probably insufficient. http://bazaar.launchpad.net/~freeaqingme/maria/improv-allowMultipleCurTimest...
I wouldn't worry about that, just try to verify that timestamps with the default get NOW() and timestamps without the default get all NULL. But anyway, you can know the moment the query was executed by creating another column of DATETIME or CHAR/VARCHAR type, and explicitly inserting NOW() into it: INSERT t1 (datetime_column) VALUES (NOW()); Regards, Sergei
participants (2)
-
Dolf Schimmel -
Sergei Golubchik