Sorry for the swedish form of 'yyyy-mm-dd' dates, but anyway...
date. With some databases, I am not allowed to store zero for day, month
that are valid.
don't know the birth year. Then I can enter birthday '0000-11-10'.
date with the missing information.
Post by Reid ThompsonPost by lordSauronPost by Reid Thompsonhttp://sql-info.de/mysql/gotchas.html vs
http://sql-info.de/postgresql/postgres-gotchas.html
So basically you're moaning about how MySQL handles some undefined behaviour?
Post by Reid Thompsonhttp://sql-info.de/postgresql/
Also, PostgreSQL is completely free for ANY use -- BSD license, versus
MySQL which is only free for certain uses.
That could be an influence...
Perhaps.....Perhaps not. See if you can find the defined behavior for
the gotchas( i seem to recall a table somewhere on the web that google
should be able to come up with). If you do, see which DB's ( MySQL,
PostgreSQL, ORACLE, DB2, SYBASE, MS SQL, INFORMIX, etc) most accurately
reflect the defined behaviors. For those with no defined behavior,
compare MySQL's behavior to the other DB's. In most cases I believe you
will find that the 'other' DB's more accurately reflect the defined
behavior, and that for undefined behaviors the 'other' DB's are more
alike in their behavior than MySQL (i.e. MySQL is the odd ball of the
bunch). That may or may not make any difference to the OP, or to you,
or even to other list members. It does to me, and should to anyone who
might be writing code that may have to be fronted onto different DB
backends.
Even if it's not defined, the gotchas listed on
http://sql-info.de/mysql/gotchas.html require extra work/code to account
for ( try applying the concept of 'least surprise' to the listing )....
What goes in - isn't (always) what comes out
So - your app messed up. It tried to insert an invalid value into a
column. There are two options here: 1) the database throws an error and
your app either deals with it gracefully or fails. 2) The database
truncates the value, or takes a guess at what might be an alternative
value and silently inserts it without giving you the teensiest hint that
what you put in is different to what you'll get out.
mysql> CREATE TABLE bounds_test (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
price NUMERIC(4,2),
code VARCHAR(8),
numbers_only INT
);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO bounds_test VALUES (
99999999999999,
21474.83,
'ABCDEFGHIJK',
'A quick brown dolphin...'
);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM bounds_test;
+------------+--------+----------+--------------+
| id | price | code | numbers_only |
+------------+--------+----------+--------------+
| 2147483647 | 999.99 | ABCDEFGH | 0 |
+------------+--------+----------+--------------+
1 row in set (0.01 sec)
(Note: in MySQL 4.1.x, the presence of warnings is notified on the query
status line; executing SHOW WARNINGS after the INSERT displays Data
truncated messages for each column).
As a nice extra touch note that although the price was defined with a
precision of 4 digits, MySQL inserted a number containing 5. Possibly
this is because MySQL internally adds an extra "digit" to store a minus
sign, and because the storage space is there uses it with positive
http://dev.mysql.com/doc/mysql/en/Numeric_types.html is anything to go by.
Other databases (tested: Firebird 1.5rc4, Oracle 8.1.7 and PostgreSQL
7.4) raised errors with the same data. On a column defined as
NUMERIC(4,2) the highest value accepted in all databases was the
expected 99.99.
For another reason why this is seriously bad database mojo see section
3.5 <http://sql-info.de/mysql/referential-integrity.html#3_5>
Division by zero
mysql> SELECT 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.02 sec)
Other databases (tested: DB2 8.1, Firebird 1.5rc4, Oracle 8.1.7,
PostgreSQL 7.3.4) all raise a "division by zero" error when performing
the same calculation.
See: http://dev.mysql.com/doc/mysql/en/Arithmetic_functions.html
February 31st
Throughout history many different calendar systems have been developed
around the world. Although the way of counting years still varies, most
countries and regions have adopted the Roman-Nordic system of months and
weekdays - except, ironically enough, a small corner of Scandinavia with
a high dolphin population ;-).
mysql> CREATE TABLE datetest (id INT, a_date DATE);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO datetest VALUES(1, '2003-02-31');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM datetest;
+------+------------+
| id | a_date |
+------+------------+
| 1 | 2003-02-31 |
+------+------------+
1 row in set (0.00 sec)
So, what's the day before February 31st?
mysql> SELECT DATE_SUB('2003-02-31', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_SUB('2003-02-31', INTERVAL 1 DAY) |
+----------------------------------------+
| 2003-03-02 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('2003-02-31', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2003-02-31', INTERVAL 1 DAY) |
+----------------------------------------+
| 2003-03-04 |
+----------------------------------------+
1 row in set (0.00 sec)
If you use really malformed dates, the result is NULL.
--http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
mysql> SELECT DATE_ADD('2003-02-!!!!!!31!!!!!', INTERVAL 1 DAY);
+---------------------------------------------------+
| DATE_ADD('2003-02-!!!!!!31!!!!!', INTERVAL 1 DAY) |
+---------------------------------------------------+
| 2003-03-04 |
+---------------------------------------------------+
1 row in set (0.00 sec)
Nope.
mysql> SELECT DATE_ADD('2003-02-99', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2003-02-99', INTERVAL 1 DAY) |
+----------------------------------------+
| NULL |
+----------------------------------------+
1 row in set (0.00 sec
Hmmm. A pattern emerges...
days 00-31, months 00-12, years 1000-9999. Any date not within this
range will revert to 0000-00-00. Please note that this still allows you
to store invalid dates such as 2002-04-31. It allows web applications to
store data from a form without further checking. To ensure a date is
valid, perform a check in your application.
--http://dev.mysql.com/doc/mysql/en/DATETIME.html
Makes you wonder why they bother...
Addendum: future MySQL versions are scheduled to contain server options
which enable sane data checks, although the need to retain
backwards-compatibility means these will probably not be enabled by
default.
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Dev-cpp-users mailing list
TO UNSUBSCRIBE: http://www23.brinkster.com/noicys/devcpp/ub.htm
https://lists.sourceforge.net/lists/listinfo/dev-cpp-users