Re: WL#946 and Changing time literal format
* Konstantin Osipov kos...@sun.com [09/02/05 03:35]: People are using MySQL because it's different and can satisfy their needs. Standards are useful, but not important for our current or future users. Getting the job done and not having downtime, even when upgrading, that is important! Hear, Monty, there has never been consensus about it even among you and David :-). - Forwarded message from David Axmark david.axm...@gmail.com - Date: Fri, 09 Jan 2009 11:01:26 +1000 From: David Axmark david.axm...@gmail.com To: Jay Pipes jay.pi...@sun.com Cc: drizzle-discuss Discuss drizzle-disc...@lists.launchpad.net Subject: Re: [Drizzle-discuss] Vote/Discuss: FROM_UNIXTIME() behaviour ERROR! I have always hated the way MySQL has taken strings like 1232STRING as a number. Leads to tons of stupid errors hidden deep in applications. /David On Thu, 2009-01-08 at 16:29 -0500, Jay Pipes wrote: All: mysql SELECT FROM_UNIXTIME(2008-01-08 03:14:07); +--+ | FROM_UNIXTIME(2008-01-08 03:14:07) | +--+ | 1969-12-31 19:33:28 | +--+ 1 row in set, 1 warning (0.00 sec) mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1292 | Truncated incorrect INTEGER value: '2008-01-08 03:14:07' | +-+--+--+ 1 row in set (0.00 sec) I was actually surprised to find this was the behaviour in MySQL and Drizzle. Would anyone have a problem if I converted the above to an error? FROM_UNIXTIME() only takes unsigned integers in the range of 0 to INT32_MAX. It doesn't take strings, or strings that look like numbers, or anything of the sort. IMHO, the above behaviour is dangerous, as it: * Implicitly tries to convert a string to an integer (based on the warning) * Produces an invalid TIMESTAMP value * Doesn't error, therefore giving the unassuming user the impression that FROM_UNIXTIME() indeed takes string parameters. Anyone object to me making the above an error? Cheers, Jay ___ Mailing list: https://launchpad.net/~drizzle-discuss Post to : drizzle-disc...@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~drizzle-discuss Post to : drizzle-disc...@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp - End forwarded message - -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
* Michael Widenius mo...@askmonty.org [09/02/03 19:24]: Konstantin Monty, I disagree with this statement. Our current users use the Konstantin current versions of the server. It's a separate question of what Konstantin support we're willing to give them and for how long. Konstantin In the new versions we should hold high the expectations of new Konstantin users, and they are about standard compliance, and also about ease Konstantin of migration. Sorry, but the above is not true. We have asked user over and over again what they think about the standard and they have said it's not critical or even important to them.; What is important that we don't break their old applications! Obviously, we talked with different people. When going forward, we must prioritize old user to new ones! The old ones are our current or customers in the near future. If we make them unhappy we don't have a business anymore. There is a way to introduce incompatible changes and retain your existing users. Trying to stay compatible is, sorry for a popular cliché :-p, like trying to hide one's head in the sand: we can't, anyway, we broke backward compatibility with 4.1, 5.0, and will break it with 6.0 and 6.1. Change management is what we need to be doing instead. The new users will mainly listen to old user if they should use MySQL or not. If we make the old ones angry, we don't get new users. I yet have to find an old user who would be particularly happy about our sloppiness with input. Users put aside, I am yet to meet an(other) engineer who would support it. And engineers matter no less than users: without being able to attract new engineers to the project, we're dead in the water. Just look around: no other database has this feature: postgres, the big 3, even our own drizzle killed it in the first 3 months after the fork. Any feature can be implemented. Nevertheless, clear and simple requirements is key to product simplicity and efficiency. MySQL doesn't even yet support SQL92 foundation, but is already a very convoluted piece of software. Konstantin MySQL server needs a vision. Sticking to expectations of existing Konstantin users is looking back into (not-so) glorious past. Our existing users is the second biggest user base for any database. We reached this level as MySQL has worked to their expectations. Trying to do things differently, like other companies have tried, will just lead to failures. Old expectations are met, let's get over it. If we don't try to do things differently (but better), we could just as well stop new development completely. People are using MySQL because it's different and can satisfy their needs. Standards are useful, but not important for our current or future users. Getting the job done and not having downtime, even when upgrading, that is important! It's possible to meet the standard and provide an upgrade path to people. It's hard, but it's a payback for telling users for 15 years that sloppy input is okay. I agree that we need to change things. I disagree that doing incompatible changes without planning and carefull thinking about how this will affect our user base is the right way to go. +1 on that. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! Konstantin == Konstantin Osipov kos...@sun.com writes: Konstantin * Michael Widenius mo...@mysql.com [09/01/30 14:53]: Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Konstantin Monty, I disagree with this statement. Our current users use the Konstantin current versions of the server. It's a separate question of what Konstantin support we're willing to give them and for how long. Konstantin In the new versions we should hold high the expectations of new Konstantin users, and they are about standard compliance, and also about ease Konstantin of migration. Sorry, but the above is not true. We have asked user over and over again what they think about the standard and they have said it's not critical or even important to them.; What is important that we don't break their old applications! When going forward, we must prioritize old user to new ones! The old ones are our current or customers in the near future. If we make them unhappy we don't have a business anymore. The new users will mainly listen to old user if they should use MySQL or not. If we make the old ones angry, we don't get new users. Konstantin sql_modes are not a solution since they make the server code a Konstantin mess, and won't let us make everyone happy anyway. I disagree that it makes the code messy. The code depends on how you implement them. sql_modes are there to help people easier switch to a newer server and gives them time to upgrade their old applications over time. When you have an application with million of code, it will take time to find and fix all issues. Seeing able to resolve things when things are found to break by simply using a sql_mode may save the day for them. It's important that you see the usage of MySQL from theu user point of view; Saying that something is complex and we will not do it, will not satisfy a user that needs it. Konstantin MySQL server needs a vision. Sticking to expectations of existing Konstantin users is looking back into (not-so) glorious past. Our existing users is the second biggest user base for any database. We reached this level as MySQL has worked to their expectations. Trying to do things differently, like other companies have tried, will just lead to failures. Konstanting Trying to make everybody happy is infeasible. Konstantin Our only option is to move forward Konstantin to meet expectations of our modern adopters, and they are largely Konstantin more intelligent, with past database experience, so the standard Konstantin compliance is high on their list. On what do you base your observation ? It's not what our users have been telling us on MySQL conferences. People are using MySQL because it's different and can satisfy their needs. Standards are useful, but not important for our current or future users. Getting the job done and not having downtime, even when upgrading, that is important! Konstantin What's worse, is that while we're fighting internally when to make Konstantin an incompatible change and when not, our change management process Konstantin is a mess. That's another issue, but it's not any reason to abound features that some of our users may depend on. Konstantin We introduce incompatible changes in every major release, so Konstantin people are forced to migrate their applications manually again and Konstantin again. And yet we can't plan our changes in a way that a bulk Konstantin incompatible changes in a certain area are done at once, forcing Konstantin people to look into the problem once only, rather than on every Konstantin upgrade. That is a problem with our development processes, has nothing to do with sql modes. Konstantin It's a pity we can't shift our focus and mental efforts from Konstantin developing a shared understanding what incompatible changes are Konstantin right and called for, to developing the best way of making Konstantin changes. Just focusing on one area doesn't solve any problems. What is needed is to have a good understanding of all aspect of the problem. I agree that we need to change things. I disagree that doing incompatible changes without planning and carefull thinking about how this will affect our user base is the right way to go. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! Roy == Roy Lyseng roy.lys...@sun.com writes: cut The question here is how PostgreSQL and ANSI does this and also what is the logical interpretation of the number. Roy ISO 9075 (ANSI SQL) is very strict about this. It only allows TIME Roy literals with 3 or 4 digit groups, and it only allows the ':' separator Roy (except after the seconds part). There is no possibility for ambiguity, Roy as the first number is always interpreted as an hour field. Roy This is a literal format that is seen only by the SQL programmer, so Roy there is no need for extensions. Date values provided by end users need Roy to go through localization features, so that could be a different story. What is more important than ANSI is how our users are using TIME now and how they want to use it in the future. There is nothing wrong in making things easier for the end user by using a relaxed way to read in time constants. We don't want to break working applications that are already used to use our relaxed time format to read data. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! Bernt == Bernt M Johnsen bernt.john...@sun.com writes: Michael Widenius wrote (2009-01-24 02:07:54): As Dmitri pointed out, we shouldn't deprecate '.' as substitute for dates. Another things is that we should stop making decisions about incompatible changes without listening to the MySQL users. They know more than we how MySQL is used and they are directly affected of any incompatible change we force upon them. Bernt Ok. And what is the users' verdict? Do they want a helpful best Bernt effort interpretation of time and dates or do they want a well-defined Bernt standardized portable scheme which reduces the possibilities of bugs? Normally they don't want their existing applications to break. Bernt And what does this helpfullness lead to? In Norway it is common to Bernt write dates as DD.MM.YY. So a buggy program that accepts 01.02.03 (for Bernt the date 2003-02-01) whould be able to insert it into MySQL without Bernt errors, but when retrieved the value is 2001-02-03. I don't think the Bernt user/programmer is happy with that. They will quickly notice this and fix their time order. In reality we haven't got many complains from Norway about this, so I assume they are smart enough in Norway to not run into this problem. Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Bernt We have a Norwgeian word for this helpfullness: bjørnetjeneste, but Bernt I'm not sure what the english idiom would be. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Michael Widenius wrote: Bernt We have a Norwgeian word for this helpfullness: bjørnetjeneste, but Bernt I'm not sure what the english idiom would be. A disservice. In German Bärendienst. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
* Michael Widenius mo...@mysql.com [09/01/30 14:53]: Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Monty, I disagree with this statement. Our current users use the current versions of the server. It's a separate question of what support we're willing to give them and for how long. In the new versions we should hold high the expectations of new users, and they are about standard compliance, and also about ease of migration. sql_modes are not a solution since they make the server code a mess, and won't let us make everyone happy anyway. MySQL server needs a vision. Sticking to expectations of existing users is looking back into (not-so) glorious past. Trying to make everybody happy is infeasible. Our only option is to move forward to meet expectations of our modern adopters, and they are largely more intelligent, with past database experience, so the standard compliance is high on their list. What's worse, is that while we're fighting internally when to make an incompatible change and when not, our change management process is a mess. We introduce incompatible changes in every major release, so people are forced to migrate their applications manually again and again. And yet we can't plan our changes in a way that a bulk incompatible changes in a certain area are done at once, forcing people to look into the problem once only, rather than on every upgrade. It's a pity we can't shift our focus and mental efforts from developing a shared understanding what incompatible changes are right and called for, to developing the best way of making changes. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! Konstantin Osipov wrote: * Michael Widenius mo...@mysql.com [09/01/30 14:53]: Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Monty, I disagree with this statement. Our current users use the current versions of the server. It's a separate question of what support we're willing to give them and for how long. In the new versions we should hold high the expectations of new users, and they are about standard compliance, and also about ease of migration. Full ack! IMO, offering a variety of input formats just creates one big mess. How often have you read some date notation and wondered which format was used - if all values are in the 1 to 12 range, you have to guess. [[...]] MySQL server needs a vision. Sticking to expectations of existing users is looking back into (not-so) glorious past. Trying to make everybody happy is infeasible. Our only option is to move forward to meet expectations of our modern adopters, and they are largely more intelligent, with past database experience, so the standard compliance is high on their list. Being stricter on input comes with small costs but huge benefits (not only to us but also to end users), and we should be able to get that message to our users and customers. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WL#946 and Changing time literal format
IMO, offering a variety of input formats just creates one big mess. How often have you read some date notation and wondered which format was used - if all values are in the 1 to 12 range, you have to guess. [JS] I agree 100%. I have to deal with dates from all over the world, and I often have to guess at the originator's format. Also, flexible input is a precursor to a demand for flexible output; before long you'd have as many input formats and output formats as you have collation options, and it would take serious research to figure out what was going on. [[...]] MySQL server needs a vision. Sticking to expectations of existing users is looking back into (not-so) glorious past. Trying to make everybody happy is infeasible. Our only option is to move forward to meet expectations of our modern adopters, and they are largely more intelligent, with past database experience, so the standard compliance is high on their list. Being stricter on input comes with small costs but huge benefits (not only to us but also to end users), and we should be able to get that message to our users and customers. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Michael Widenius wrote:
Hi!
Peter == Peter Gulutzan peter.gulut...@sun.com writes:
Peter Hi all,
Peter On 01/15/2009 03:11 PM Peter Gulutzan wrote:
For a TIME or DATETIME or TIMESTAMP literal, one can use
'.' instead of ':' and one can skip leading fields. For example:
INSERT INTO t (datetime_column) VALUES ('1001.01.01 11.22');
For default MySQL changes the value to '1001-01-01 11:22:00'.
The MySQL Reference Manual calls this relaxed form.
http://dev.mysql.com/doc/refman/5.1/en/using-date.html
For WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds,
'.' means something else: decimal point. The natural reading
of '11.22' is going to be '11.22 seconds' for many people.
The WL#946 HLS says That [relaxed form] will no longer be
possible, '.' must indicate that a fraction follows.
I think we should consider these alternatives now:
1. Deprecate use of '.' as a substitute for standard
punctuation characters. The other relaxed form stuff
can remain. The manual should say don't use '.' etc..
Peter Roy says it is not strictly necessary.
Peter Bernt says I would go for 1)
Peter Konstantin says This should be OK.
Peter Unless more comments appear before January 31, this is the
Peter winning option. Trudy wrote guidelines in 2006
Peter https://inside.mysql.com/wiki/DeprecatingServerFeatures
Peter I will try to follow them, except that instead of getting
Peter approval from architecture team I will ask ServerPT.
As Dmitri pointed out, we shouldn't deprecate '.' as substitute for
dates.
Another things is that we should stop making decisions about
incompatible changes without listening to the MySQL users. They know
more than we how MySQL is used and they are directly affected of any
incompatible change we force upon them.
2. Insist that '.' will continue to be a substitute for
standard punctuation characters if any field is missing,
but '.' will mean decimal if and only if all fields are
present and have no substitutions. Thus '11.22' means
11 hours 22 minutes but '00:00:11.22' means 11.22
seconds.
Peter Roy said option 2) will work quite well.
The question here is how PostgreSQL and ANSI does this and also what
is the logical interpretation of the number.
ISO 9075 (ANSI SQL) is very strict about this. It only allows TIME
literals with 3 or 4 digit groups, and it only allows the ':' separator
(except after the seconds part). There is no possibility for ambiguity,
as the first number is always interpreted as an hour field.
This is a literal format that is seen only by the SQL programmer, so
there is no need for extensions. Date values provided by end users need
to go through localization features, so that could be a different story.
Thanks,
Roy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi Monty,
Michael Widenius wrote:
Hi!
Peter == Peter Gulutzan peter.gulut...@sun.com writes:
Peter Hi all,
Peter On 01/15/2009 03:11 PM Peter Gulutzan wrote:
For a TIME or DATETIME or TIMESTAMP literal, one can use
'.' instead of ':' and one can skip leading fields. For example:
INSERT INTO t (datetime_column) VALUES ('1001.01.01 11.22');
For default MySQL changes the value to '1001-01-01 11:22:00'.
The MySQL Reference Manual calls this relaxed form.
http://dev.mysql.com/doc/refman/5.1/en/using-date.html
For WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds,
'.' means something else: decimal point. The natural reading
of '11.22' is going to be '11.22 seconds' for many people.
The WL#946 HLS says That [relaxed form] will no longer be
possible, '.' must indicate that a fraction follows.
I think we should consider these alternatives now:
1. Deprecate use of '.' as a substitute for standard
punctuation characters. The other relaxed form stuff
can remain. The manual should say don't use '.' etc..
Peter Roy says it is not strictly necessary.
Peter Bernt says I would go for 1)
Peter Konstantin says This should be OK.
Peter Unless more comments appear before January 31, this is the
Peter winning option. Trudy wrote guidelines in 2006
Peter https://inside.mysql.com/wiki/DeprecatingServerFeatures
Peter I will try to follow them, except that instead of getting
Peter approval from architecture team I will ask ServerPT.
As Dmitri pointed out, we shouldn't deprecate '.' as substitute for
dates.
Yes, '.' is fine in a date. But how do we know it's in a date?
Answer: because all the fields are there. So this applies for 2) not 1).
Another things is that we should stop making decisions about
incompatible changes without listening to the MySQL users. They know
more than we how MySQL is used and they are directly affected of any
incompatible change we force upon them.
If you refer to the deprecation guidelines that I cited, you'll see
that there is a requirement to consult interested parties (Support,
PS) before submitting to a committee, etc.
2. Insist that '.' will continue to be a substitute for
standard punctuation characters if any field is missing,
but '.' will mean decimal if and only if all fields are
present and have no substitutions. Thus '11.22' means
11 hours 22 minutes but '00:00:11.22' means 11.22
seconds.
Peter Roy said option 2) will work quite well.
The question here is how PostgreSQL and ANSI does this and also what
is the logical interpretation of the number.
PostgreSQL accepts various forms but '.' can only mean decimal point.
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html
Bernt has answered re ANSI.
Roy has also answered re ANSI.
I doubt that there would be agreement about logical interpretation.
I think that if we go with 1), 11.22 should mean 11.22 seconds for a
time field.
Yes, if we can't use '.' as a substitute for other punctuation
characters, then it's safe to have it in the place the
standard allows, as a decimal point. That's the point of 1).
And now for Dmitri's question, which was:
AFAIK usage of '.' as a field separator in dates is quite wide spread
in real world (although I am not not sure how often it is used in this
role in SQL statements executed by our users).
May be it makes sense to keep '.' as allowed separator in date part
and prohibit it only in time part of datetime value?
Yes, provided it's possible to distinguish the date part and the
time part. That could be done by looking for ' ', or by using
one set of rules for DATE and another set of rules for TIME.
But I believe the essential idea is: when you know which field
it is by noting whether it's first / second / third / fourth / etc.
within the string, then you don't need to worry about choice of
punctuation character.
So '1.1.1 1.1.1.1' could be correctly interpreted as
'0001-01-01 01:01:01.1'. In fact that's happening now, already:
mysql select cast('1-1-1 1.1.1.1' as datetime);
+---+
| cast('1-1-1 1.1.1.1' as datetime) |
+---+
| 0001-01-01 01:01:01.10|
+---+
1 row in set (0.01 sec)
cut
3. Add a new mode, @@sql_mode=monty's_revenge. If it's on
(which will never be the default), then relaxed mode
is still possible (you can skip fields and you can use
any punctuation other than '.'), but '.' means decimal
point, so '11.22' means '11.22 seconds'.
Peter Roy said it is not strictly necessary.
Peter Bernt said with the addition of 3) for backwards compatability.
Peter Konstantin said No new sql modes please.
I agree with Konstantin that we should avoid new sql modes as much as
possible. However, if we do break a lot of applications when we
deprecate the usage of . as a separator for TIME fields, then we
should add a temporary mode to help people move their applications
forward until they
