Document Type | Technical Information
Category | Migration
Applicable Product Version | Tibero7.2.4
Document Number | TMITI021
Overview
This document provides guidance on the NULL handling methods for different RDBMS.
Caution
Since NULL handling varies by RDBMS, special attention is required when performing data migration between heterogeneous RDBMS.
Test Version Information
| RDBMS | VERSION | SQL |
| TIBERO | 7.2.4 | SELECT * FROM VT_VERSION; |
| ORACLE | Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production | SELECT * FROM V$VERSION; |
| MySQL | 8.0.29 | SELECT VERSION(); |
| MariaDB | 10.8.3-MariaDB-1:10.8.3+maria~jammy | SELECT VERSION(); |
| SQL Server (MS) | Microsoft SQL Server 2022 (CTP2.0) - 16.0.600.9 (X64) | SELECT @@VERSION; |
| PostgreSQL | PostgreSQL 14.3 | SELECT VERSION(); |
| CUBRID | 11.2.0.0658 | SELECT VERSION(); |
Method
NULL Definition
Except for TIBERO, ORACLE, other RDBMS treat '' (empty) as a string, not as NULL.
| INSERT VALUES | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 'NULL' | String | String | String | String | String | String | String |
| ' ' | String | String | String | String | String | String | String |
| '' | NULL | NULL | String | String | String | String | String |
Caution
These are precautions when performing data migration between heterogeneous RDBMS.
- If
''(empty) is treated as a string, migrating to TIBERO may cause constraint violations on NOT NULL tables.- Prior consultation is required before migration, and without it, performing a proper migration is difficult.
NULL Size
| RDBMS | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| NULL (byte) | 0 or 1 | 0 | 0 | 0 | 0 | 0 | 0 or declared data type size |
NULL Handling Methods
NULL Operations in SELECT Clause
| NULL Operation | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 + NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 โ NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 * NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 / NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
NULL String Concatenation in SELECT Clause
| NULL String Concatenation | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| 'A'||NULL||'Cโ | AC | AC | NULL (operation) | NULL (operation) | - | NULL | NULL |
| 'A'+NULL+'Cโ | NULL (operation) | ERROR | NULL (operation) | NULL (operation) | NULL | - | NULL |
| CONCAT('A',NULL,'C') | - | - | NULL | NULL | AC | AC | NULL |
| CONCAT('A',NULL) | A | A | NULL | NULL | A | A | NULL |
[*] NULL: cases where the result is treated as NULL
[*] NULL (operation): cases where the result is NULL but it's a character operation, not a character set
[*] -: syntax not supported
- Both TIBERO and ORACLE treat + as a string operation in the SELECT clause.
- The difference in results is because ORACLE checks if the left operand is a NUMBER when performing the SELECT clause operation.
- In TIBERO, swapping the left operand and the first right operand causes the same error as ORACLE.
Date/Time Operations in SELECT Clause
| NULL Date Operation | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| Date/Time function + NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
NULL Aggregate Functions
| TBL | |
| col1 | col2 |
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | 40 |
| 5 | 50 |
| Aggregate Function | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| COUNT(*) | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| COUNT(col1) | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| COUNT(col2) | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| SUM(col1) | 15 | 15 | 15 | 15 | 15 | 15 | 15 |
| SUM(col2) | 90 | 90 | 90 | 90 | 90 | 90 | 90 |
| SUM(col1+col2) | 99 | 99 | 99 | 99 | 99 | 99 | 99 |
| AVG(col1) | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| AVG(col2) | 45 | 45 | 45 | 45 | 45 | 45 | 45 |
| AVG(col1+col2) | 49.5 | 49.5 | 49 | 49.5 | 49 | 49.5 | 49.5 |
- COUNT: When performed on columns containing NULL, the result excludes NULL; COUNT(*) does not exclude NULL.
- SUM: When performed on columns containing NULL, the result excludes NULL; composite results also exclude NULL.
AVG: When performed on columns containing NULL, the result excludes NULL; composite results also exclude NULL.
Decimal truncation varies depending on the RDBMS characteristics and can be adjusted via parameters.
NULL Sorting
| ORDER BY | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| Numeric: ORDER BY ASC | High | High | Low | Low | Low | High | Low |
| String: ORDER BY ASC | High | High | Low | Low | Low | High | Low |
| Null Direction: ORDER BY ASC | High | High | Low | Low | Low | High | Low |
- NULL sorting differences among RDBMS are classified as high or low; there is no middle value (all RDBMS are the same).
- When performing MIN/MAX aggregation or comparisons, NULL is excluded. (All RDBMS behave the same)
NULL INDEX
| INDEX NULL Inclusion | TIBERO | ORACLE | MySQL | MariaDB | SQL Server | PostgreSQL | CUBRID |
| Single INDEX | X | X | O | O | O | O | X |
| Composite INDEX | O | O | O | O | O | O | O |
| Optional NULL Inclusion | X | X | O | O | O | O | X |
- TIBERO, ORACLE, and CUBRID do not include NULL in single indexes.
- For TIBERO and ORACLE, when creating composite indexes, if even one column has a NOT NULL constraint, NULL is included in the index. In CUBRID, NULL is included in the index but cannot be used for INDEX SCAN.
- Other RDBMS that include NULL in both single and composite indexes allow selective inclusion or exclusion of NULL. However, if there is a column with a NOT NULL constraint in a composite index, selective inclusion is not possible.