Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: MySQL: NO DATE constrain !!!!

    If in the control center, I open a test table, and in a DATE field I enter

    2003-11-31
    2003-02-30 (!!)
    2003-02-31 (!!!)

    the server (mysql-max) accept it like it would be a valid date !!

    Even a silly DB like Access will not accept such a data !!!

    The only check is done, is if th month is littler of 12, and il the day is littler of 31 !!

    Disappointing !
    Is should be a three lines of code to check relations between day<-> month (i.e. siwtch { case: case: default: }) and a ten lines of code to implement a check boudary on february/bisestile years !!


    I wonder how it hasn't be discovered untill now !!
    Is there any fix planned for it ???

    H2O
    P.S.
    I was using an InnoDB Table
    Last edited by H2O; 11-01-03 at 04:45.

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: MySQL: NO DATE constrain !!!!

    Originally posted by H2O
    If in the control center, I open a test table, and in a DATE field I enter

    2003-11-31
    2003-02-30 (!!)
    2003-02-31 (!!!)

    the server (mysql-max) accept it like it would be a valid date !!

    Even a silly DB like Access will not accept such a data !!!

    The only check is done, is if th month is littler of 12, and il the day is littler of 31 !!

    Disappointing !
    Is should be a three lines of code to check relations between day<-> month (i.e. siwtch { case: case: default: }) and a ten lines of code to implement a check boudary on february/bisestile years !!


    I wonder how it hasn't be discovered untill now !!
    Is there any fix planned for it ???

    H2O
    P.S.
    I was using an InnoDB Table
    THIS IS DOCUMENTED and well known in MySQL. MySQL being mostly used in Web apps for it speed, the developers thought that checking every date would probably cause some perf degradation so they decided they would not validate the dates and leave it to the application to do the validation.

    Ain't so bad when you know it.

    You<ll be able to do that kind of validation once they implement triggers and/or functions in the near future. Until then, you'll have to handle date validation in your app...

  3. #3
    Join Date
    Oct 2003
    Posts
    24

    Re: MySQL: NO DATE constrain !!!!

    Originally posted by bstjean
    THIS IS DOCUMENTED and well known in MySQL.
    Well know.......... bug ??
    To quote the mysql point of view:
    http://www.mysql.com/doc/en/Using_DATE.html
    "We think it is up to the application to check the dates, and not the server."

    MySQL being mostly used in Web apps for it speed, the developers thought that checking every date would probably cause some perf degradation so they decided they would not validate the dates and leave it to the application to do the validation.
    Sorry bstjean , but I don't get to the same conclusion you do.. the facts are that:

    1- MySQL DO a kind of bound check for the DATE type, as it checks if the moth is > 12, and the day is < 31

    2- This means that mySql DO use a parser to find out from the string you send, which is the month and which is the day. It check them, and it also performs a kind of integrity constrain in the data field, setting the date to 000-00-00 if the date is wrong.
    ALL THIS WORK is, computational speacking, the heavvy load that may cause the "perf degradation" you talk.

    2- After all this work the strong check, once you got the month and the day in the "integer" form, is nothing, really nothing to be performed (five line of a switch clause...). But unfortunately it doesn't happen!!

    So:
    MySQL has a procedure/function that parse the date string, get the integer value of the month and day, provide a integrity constrain if the data field is incorrect, (read: do a lot of work, with possibly perf. degradation) but the check it does leaves disappointed....


    H2O

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    What would you use to check the date? Assume that you have the month and day in integer form.

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: MySQL: NO DATE constrain !!!!

    Originally posted by H2O
    Well know.......... bug ??
    To quote the mysql point of view:
    http://www.mysql.com/doc/en/Using_DATE.html
    "We think it is up to the application to check the dates, and not the server."



    Sorry bstjean , but I don't get to the same conclusion you do.. the facts are that:

    1- MySQL DO a kind of bound check for the DATE type, as it checks if the moth is > 12, and the day is < 31

    2- This means that mySql DO use a parser to find out from the string you send, which is the month and which is the day. It check them, and it also performs a kind of integrity constrain in the data field, setting the date to 000-00-00 if the date is wrong.
    ALL THIS WORK is, computational speacking, the heavvy load that may cause the "perf degradation" you talk.

    2- After all this work the strong check, once you got the month and the day in the "integer" form, is nothing, really nothing to be performed (five line of a switch clause...). But unfortunately it doesn't happen!!

    So:
    MySQL has a procedure/function that parse the date string, get the integer value of the month and day, provide a integrity constrain if the data field is incorrect, (read: do a lot of work, with possibly perf. degradation) but the check it does leaves disappointed....


    H2O
    Well, if you consider the fact that you would have to check if it is a leap year for every date in February and perform a lookup in a hash table to validate the maximum days in a month, that can be very expensive.

    The basic leap year algorithm need 3 modulos, 4 equality checks, one lookup and one addition. You might say that while they're parsing the date for basic range check (month 1-12 and date 1-31), why not do a real validation while we're there? I guess the MySQL developers considered it for a while and did some test and decided it was not worth it...

    And add to the fact that your can default a date to 00-00-0000, the validation code would have to take into account that value, adding more overhead...

    I'm not saying the actual situation is good... Just like you I think if you do validate, validate everything or just don't do it... But guess what, some might as well say that since they validate the dates at the entry level (to save a failed insert because of a bad date), they don't care since they already do the validation themselves anyway!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    I guess the MySQL developers considered it for a while and did some test and decided it was not worth it...
    actually, it may have been that they were seduced by the temptation to allow dates like 1969-11-00 and 1949-00-00 for situations where you don't know the day, or the month, useful in a bigraphical application where you know the year or year and month of someone's birth date, and want to record as much as you can

    the relevant statement in the mysql docs is:
    To make the date checking 'fast', MySQL only checks that the month is in the range of 0-12 and the day is in the range of 0-31. The above ranges are defined this way because MySQL allows you to store, in a DATE or DATETIME column, dates where the day or month-day is zero. This is extremely useful for applications that need to store a birth-date for which you don't know the exact date. In this case you simply store the date like 1999-00-00 or 1999-01-00. (You cannot expect to get a correct value from functions like DATE_SUB() or DATE_ADD for dates like these.)
    http://www.mysql.com/doc/en/Date_and_time_types.html
    it means you can store a "partial" date and not have to store year and month in separate numeric fields as you would in other databases where your date or datetime values must be complete and valid

    i'm not saying i condone this, just that it does have one advantage


    rudy
    http://r937.com/

    p.s. by the way, bstjean, it is not necessary to quote someone's entire post when replying to it

  7. #7
    Join Date
    Oct 2003
    Posts
    24
    Originally posted by aus
    What would you use to check the date? Assume that you have the month and day in integer form.
    Just to dump down some -silly- few lines of C++.... (not checked grammar errors ). I tried to use the style "more lines, more readable..".

    It could be very otpimezed by the compiler and not be very heavy load..
    Consider there are only logical operation and three modulus operation... should be a non time consuming function..

    ----------------------------------------------
    bool bIsGoodDate ( int uiMonth, int iDay, int iYear)
    {

    /*return true if the date is correct; return false if it is not */
    /* do we have to accept a date prior to 0 ??? boh ..*/

    if (iDay < 1) or (iMonth <1) return false;
    if (iMonth > 12) return false;

    switch (iMonth)
    {
    case 11,4,6,9: /* let me be quickly... the 30 days months */
    if (iDay < 32)
    return true
    else
    return false;
    break;

    case 2 :
    /* is it a bisestile year ?? */
    bool bBisestile = false; /*3 years every 4, it's not bisestile.... */
    if ((iYear % 4) == 0) bBisestile = true;
    if ((iYear % 100)) == 0) bBisestile = false;
    if ((iYear % 1000)) == 0) bBisestile = true;

    if bBisestile /* a bisestile year */
    {
    if (iDay<30) return true;
    else return false;}
    else /* not a bisestile */
    { if (iDay<29) return true;
    else return false;} ;
    break;

    default: if (iDay<32) return true; /*a "normal" 31 days month*/
    else return false;
    }

    }

  8. #8
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    That is pretty concise. Except that it would not allow partial date entries like the documentation hints at. You're right, the code is simple and would not take up that much as far as resources go, but I would have to agree with MySQL AB on this one, it is probably more important to allow the flexibility.

    Originally posted by H2O
    Just to dump down some -silly- few lines of C++.... (not checked grammar errors ). I tried to use the style "more lines, more readable..".

    It could be very otpimezed by the compiler and not be very heavy load..
    Consider there are only logical operation and three modulus operation... should be a non time consuming function..

  9. #9
    Join Date
    Oct 2003
    Posts
    24
    would not take up that much as far as resources go, but I would have to agree with MySQL AB on this one, it is probably more important to allow the flexibility. [/SIZE]
    Thanks !... but I think the point is anyway ... data constrain.
    A SQL server should never accept a value that is incompatible with the data type.

    AB put the goal in the direction of "flexibility", but doing this it breaks the nature of the "relational database"; as Dr. Code (SQL "daddy") stated about what a relationale database should do: data constrain is a matter of the server, not the application !

    I just wonder if MySQL will ever accept a standard SQL statement like:

    CREATE DOMAIN Voto
    AS DATE
    CHECK ( value >="2000-01-01" AND value <=2000-31-12" )

    This is a standard statament, but following the direction of "flexibility", it will never reached, no matter which is the version number ; simply it will never be reached because of the database design !!

    That is, we have a relational database, that claims to be said that, but clearly the project do not want to be that !!

    I could clearly understand the fact of the lack of date constrain if it would be a bug and/or a goal of future realeses.
    But with this design in mind, simply AB should stop calling MySql a "relational" database....
    And of course it is not because Mr. Code, when defined the relational database, stated what a relational databse had to be....


    ... sorry... I'm now reading this post before submitting..... I didn't want to open a "flame".....
    I just remeber that MySQL is sold also as commercial software for 500, and if we buy a "relational database", we must have such a database, not a network database or a gerarchical database...

    I like mySql, - i wouldn't be in this forum- .... it was only to undeline a weack point ....

    H2O

  10. #10
    Join Date
    Oct 2003
    Posts
    24
    Just to report AB answer:

    ---------------------------------------------
    ID: 1730
    Updated by: Dean Ellis <dean@mysql.com>
    Reported by:
    User Type: User
    -Originating List:
    +Originating List: General
    Status: Open
    -Severity: Non-critical
    +Severity: Feature request
    Priority: Medium
    Category: Server
    Operating System: Windows 2000
    Version: 4.0.16
    New Comment:

    I am changing this to a Feature Request, as MySQL's limited validity
    testing on date values is documented behaviour. You may read more
    about it here:

    http://www.mysql.com/doc/en/Using_DATE.html

    Thank you

  11. #11
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by H2O
    AB put the goal in the direction of "flexibility", but doing this it breaks the nature of the "relational database"; as Dr. Code (SQL "daddy") stated about what a relationale database should do: data constrain is a matter of the server, not the application !
    H2O
    Just for the purpose of documenting... It's Codd, not Code! Like in Boyce-Codd, yep, just like the fifth normal form...

    And that data constrain matter is debatable... How come I can't have a date like 02-15-12735 ? Who said 12735 wasn't a valid year? And who decided that miliseconds would be the smallest unit of time for timestamp precision? What if I need nanoseconds? No matter how precise and exhaustive you want to be, every database has built-in limits that you have to accept. DB/2 limits table and column identifiers to 18 chars, Oracle does something else, etc. and MySQL, well, doesn't validate dates... As long as developers are aware of these limitations, we can always have workarounds and use the RDBMS to the best of *it's* limits...

  12. #12
    Join Date
    Oct 2003
    Posts
    24
    Originally posted by bstjean
    Just for the purpose of documenting... It's Codd, not Code! Like in Boyce-Codd, yep, just like the fifth normal form...
    Sorry... hope mr Codd will forgive me .... RIP

    I would like to know your opinion about theese SQL queries, I entered in ***control center***.
    I report the history and message tabs content:

    ____HISTORY-
    UPDATE `tblfornitori`
    SET `Data`='2003-12-01'
    WHERE `ID`=1
    ____MESAGE
    Query OK, 1 row affected (0.00) sec

    ____HISTORY-
    UPDATE `tblfornitori`
    SET `Data`='2003-12-32'
    WHERE `ID`=1
    ____MESAGE
    Query OK, 1 row affected (0.02) sec

    After the second query the 'Data' field now contains 0000-00-00.
    But I get no warning and no error message. The message stated that the query was OK.

    I waited something like:
    ER_WARN_DATA_OUT_OF_RANGE
    but it didn't happened !

    Do you think is the fault of Control Center or MySQL ?


    H2O

  13. #13
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by H2O
    Sorry... hope mr Codd will forgive me .... RIP

    I would like to know your opinion about theese SQL queries, I entered in ***control center***.
    I report the history and message tabs content:

    ____HISTORY-
    UPDATE `tblfornitori`
    SET `Data`='2003-12-01'
    WHERE `ID`=1
    ____MESAGE
    Query OK, 1 row affected (0.00) sec

    ____HISTORY-
    UPDATE `tblfornitori`
    SET `Data`='2003-12-32'
    WHERE `ID`=1
    ____MESAGE
    Query OK, 1 row affected (0.02) sec

    After the second query the 'Data' field now contains 0000-00-00.
    But I get no warning and no error message. The message stated that the query was OK.

    I waited something like:
    ER_WARN_DATA_OUT_OF_RANGE
    but it didn't happened !

    Do you think is the fault of Control Center or MySQL ?


    H2O
    No idea! I never use the control center. Waht if you issue the same query from the MySQL client or an app using an ODBC connection? Do you get an SQL error (or warning) ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •