Showing entries 1 to 2
Displaying posts with tag: NULL VALUES (reset)
10 Things in SQL Server Which Don’t Work as Expected

So far, I have been blogging about curious RDBMS caveats mostly related to Oracle and MySQL databases. Some examples:

But there are also other databases, which have 1-2 things that do not work as expected. For example, SQL Server. Here’s an interesting blog post, …

[Read more]
ON DUPLICATE KEY With NULL Validation

I am not sure if this is a bug or how MySQL works on validating constraints in association with ON DUPLICATE KEY (late or early checking). For example, consider the following use case (this is irrepective of storage engine and MySQL version):

mysql> create table t1(id int not null primary key, val int not null) Engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
 
mysql> insert into t1 values(10,20);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t1 values(20,10);
Query OK, 1 row affected (0.00 sec)
 
mysql> create table t2(id1 int not null primary key, val1 int) Engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)
 
mysql> insert into t2 values(10,NULL);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1(id, val) select id1, val1 from t2 ON DUPLICATE KEY UPDATE val=IF (VALUES(val) IS NULL, val, VALUES(val));
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 1 …
[Read more]
Showing entries 1 to 2