Oddities about MySQL

MySQL silently crops data if it’s larger than the specified fieldlength:

mysql> create table t2 (col1 varchar(5));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values ('12345');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values ('123456789');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+-------+
| col1  |
+-------+
| 12345 |
| 12345 |
+-------+
2 rows in set (0.00 sec)

mysql>

Let’s try this on an Oracle-Database:

SQL>create table t2 (col1 varchar2(5));

Table created.

SQL>insert into t2 values ('12345');

1 row created.

SQL>insert into t2 values ('123456789');
insert into t2 values ('123456789')
                       *
ERROR at line 1:
ORA-01401: inserted value too large for column

SQL>select * from t2;

COL1
-----
12345

SQL>

On MySQL you can’t UPDATE a table when that table is referenced in a subquery in the WHERE-condition of the same statement:

mysql> create table t1 (c1 varchar(10),c2 varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('abc','abcs@ssdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('abc','abcs@ssdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('abc','abcsssdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('abc','abcs@ssdef');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set c2='xyz' where c2 in (select c2 from t1 where instr(c2,'@')>0);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
mysql> select * from t1;
+------+------------+
| c1   | c2         |
+------+------------+
| abc  | abcs@ssdef |
| abc  | abcs@ssdef |
| abc  | abcsssdef  |
| abc  | abcs@ssdef |
+------+------------+
4 rows in set (0.00 sec)

mysql>

This behaviour is officially documented here:
http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html
And it is independent of the used storage engine (tested with MyISAM and InnoDB).

What about Oracle? Let’s see:

SQL>create table t1 (c1 varchar2(10),c2 varchar2(10));

Table created.

SQL>insert into t1 values ('abc','abcs@ssdef');

1 row created.

SQL>insert into t1 values ('abc','abcs@ssdef');

1 row created.

SQL>insert into t1 values ('abc','abcsssqw');

1 row created.

SQL>insert into t1 values ('abc','abcs@ssdef');

1 row created.

SQL>select * from t1;

C1         C2
---------- ----------
abc        abcs@ssdef
abc        abcs@ssdef
abc        abcsssqw
abc        abcs@ssdef

SQL>update t1 set c2='xyz' where c2 in (select c2 from t1 where instr(c2,'@')>0);

3 rows updated.

SQL>select * from t1;

C1         C2
---------- ----------
abc        xyz
abc        xyz
abc        abcsssqw
abc        xyz

SQL>

More astonishing oddities about MySQL can be found here:
http://www.databasejournal.com/features/mysql/article.php/3519116/MySQL-Oddities.htm. It’s really worth reading!

Most of this oddities depend on the given “SQL Server Mode” and appear with the default settings of MySQL. By changing the “SQL Server Mode”, one can make MySQL to behave like a “traditional” RDBMS. More info on that here: http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: