Differences between MySQL and PostgreSQL with INSERT

So, recently when investigating an issue with a MySQL to PostgreSQL migration, I found a surprising difference in behaviour between the two databases.

The difference has to do with how each behave when explicitly passed NULL values in an INSERT statement to columns with defaults or auto-increments (called “serials” in Postgres)

What do I actually mean? To compare, in MySQL you can do this:

mysql> create table baz(whattimeisit TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table baz;
| Table | Create Table                                                                                                              |
| baz   | CREATE TABLE `baz` (
  `whattimeisit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
1 row in set (0.00 sec)

mysql> INSERT INTO baz (whattimeisit) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from baz;
| whattimeisit        |
| 2019-08-25 20:35:47 |
1 row in set (0.00 sec)

Where it decides to revert to the “DEFAULT” value when passed a NULL.

In Postgres, we get the following error:

postgres=# \d baz
                                 Table "public.baz"
    Column    |           Type           | Collation | Nullable |      Default      
 whatsthetime | timestamp with time zone |           | not null | CURRENT_TIMESTAMP

postgres=# INSERT INTO baz(whatsthetime) VALUES (NULL);
ERROR:  null value in column "whatsthetime" violates not-null constraint
DETAIL:  Failing row contains (null).

Funnily enough, when it comes to PostgreSQL if you don’t pass the column at all, the default is used and you don’t get the same error.

MySQL Bug #11918

In the software industry, every now and then you come accross some indescribably dense acts of stupidity that cost you hours of debugging time. When it’s not your own code that’s the problem, you get to rant about it on your blog 😉

Today I spent the last four hours trying to create a MySQL stored procedure to do something which I thought was quite basic. It just had to take an integer as input and return that number of rows from a specific table and at the end delete the rows. Kind of like a stack data structure (the rows ‘pop’ out), but with a database. So I sat down and wrote this code:


CREATE PROCEDURE `test`.`testGet` (IN numRecords INT)
SELECT * FROM test LIMIT numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

SELECT * FROM tmpTable;

DROP TABLE tmpTable;


So after saving this code and trying to run it I get this cryptic error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

‘ at line 4 (errno: 1064)

I spent the next two hours trying to figure out what MySQL was complaining about, pulling my hair out, trying to see which part of the code was screwing up…. finally after much googling, I came across this bug report on the MySQL bug tracker, the title of which simply reads “SP does not accept variables in LIMIT clause”. The bug’s been sitting in the bug tracker since the 13th of July 2005, FOUR YEARS this last July. The bug report is full of comments with detailed workarounds, most of which end up being versions of the ‘EXECUTE STMT USING’ statement. There’s a big discussion in the comments as to whether this is a bug fix or a feature request, meanwhile Oracle and SQL Server continue to dominate the marketplace. Nice going guys. I’m stupified that this bug has been hanging around for four years and is still not fixed. I’m tempted to download the source and have a look at how difficult it would be to patch.