Quirks specific to some Database systems

In this page are described some Database system specific choices regarding the implementation of SQL standard that differ from all other supported systems.

When using one of these Database systems, this information should be taken into account to avoid possible problems.

OracleDB

• an empty string is equivalent to NULL; according to Oracle documentation on NULLs

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

• JDBC statements that include string literals with length > 2000 characters will fail with error "string literal too long"; as a workaround, prepared statements should be used instead (they don't fail with these long strings)
  Note: it is reported, though not experimented, that splitting the long string into smaller chunks might work
  INSERT INTO table (clob_column) VALUES ( to_clob( 'chunk 1' ) || to_clob( 'chunk 2' ) );

• LOBs >= 4000 bytes are stored off row. This is detrimental to the performance because of the accesses to that column because the scan of rows is no longer sequential (and databases are optimized for sequential accesses). See Oracle LOB Performance Guide for more details.

MySQL

• no support for IEEE 754-1985 non-numbers: positive and negative infinities and NaN (not-a-number)

• a communication packet (e.g. a query) has a relatively low default value of 4MB (this is increased to 64MB in MySQL >= 8.0.3);
  this value must be increased if large BLOB columns or long strings are used in the queries (should be at least as big as the largest BLOB/string that is expected to appear) — see MySQL documentation for "Packet too large" error and max_allowed_packet flag;
  if using InnoDB engine, the innodb-log-file-size flag must also be increased from the default value of 5MB (this is increased to 48MB in MySQL >= 5.6.8) — see also MySQL documentation for Redo Log File Configuration

• because MySQL’s UTF-8 implementation only supports up to 3 bytes (the standard allows up to 4), some Unicode characters may be wrongly encoded when using "utf8" encoding; if using real UTF-8 is needed, then the correct MySQL encoding option to use is "utf8mb4"

Microsoft SQL server

• during concurrent transactions, at least with isolation level "read committed", SQL server blocks a transaction trying to read a row that another transaction has already locked for writing (exclusive access);
  other supported Database systems will not block in this situation, returning instead the current committed value — this may seem less safe regarding the correctness of the read data, but the reads will be optimistic (won't block) and data verification can be done on the application level; to make this behavior consistent with other Database systems and to avoid application deadlocks, the database(s) being used should have enabled the flag READ_COMMITTED_SNAPSHOT — this can be done right after creating a database, by running
  ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON;