[GH-ISSUE #9] Failed comparison if a field is NULL #142

New issue

Closed

opened 2026-03-07 20:41:28 +03:00 by kerem · 3 comments

kerem commented

2026-03-07 20:41:28 +03:00

Owner

Originally created by @webian on GitHub (May 23, 2016).
Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/9

If one of the fields of the analyzed record is NULL then MD5 returns NULL so even if 2 records are different they are considered equal: NULL = NULL
This happens in method getChangeDiff of class LocalTableData

Originally created by @webian on GitHub (May 23, 2016). Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/9 If one of the fields of the analyzed record is NULL then MD5 returns NULL so even if 2 records are different they are considered equal: NULL = NULL This happens in method getChangeDiff of class LocalTableData

kerem closed this issue

2026-03-07 20:41:29 +03:00

kerem commented

2026-03-07 20:41:30 +03:00

Author

Owner

@jasdeepkhalsa commented on GitHub (May 26, 2016):

Nice edge case!

I suppose we'll have to look at how to get MD5 to give a result for NULL (perhaps it could work as a string), or just to handle NULL records specially

@jasdeepkhalsa commented on GitHub (May 26, 2016): Nice edge case! I suppose we'll have to look at how to get MD5 to give a result for NULL (perhaps it could work as a string), or just to handle NULL records specially

kerem commented

2026-03-07 20:41:30 +03:00

Author

Owner

@jasdeepkhalsa commented on GitHub (Jul 8, 2016):

Fixed in https://github.com/DBDiff/DBDiff/pull/12

@jasdeepkhalsa commented on GitHub (Jul 8, 2016): Fixed in https://github.com/DBDiff/DBDiff/pull/12

kerem commented

2026-03-07 20:41:30 +03:00

Author

Owner

@djshen-ponddy commented on GitHub (Aug 15, 2016):

In getChangeDiff` of src/DB/Data/LocalTableData.php,

        $result = $this->source->select(
           "SELECT * FROM (
                SELECT $columnsAas, $columnsBas, MD5(concat($columnsA)) AS hash1,
                MD5(concat($columnsB)) AS hash2 FROM {$db1}.{$table} as a 
                INNER JOIN {$db2}.{$table} as b  
                ON $keyCols
            ) t WHERE hash1 <> hash2");

if one of the columns is NULL, then the result of concat is NULL. For example,

mysql> SELECT CONCAT("a", NULL);
+-------------------+
| CONCAT("a", NULL) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.03 sec)

Maybe it could be fixed by MySQL function IFNULL and replace NULL values with some characters.

@djshen-ponddy commented on GitHub (Aug 15, 2016): In getChangeDiff` of src/DB/Data/LocalTableData.php, ``` php $result = $this->source->select( "SELECT * FROM ( SELECT $columnsAas, $columnsBas, MD5(concat($columnsA)) AS hash1, MD5(concat($columnsB)) AS hash2 FROM {$db1}.{$table} as a INNER JOIN {$db2}.{$table} as b ON $keyCols ) t WHERE hash1 <> hash2"); ``` if one of the columns is NULL, then the result of concat is NULL. For example, ``` sql mysql> SELECT CONCAT("a", NULL); +-------------------+ | CONCAT("a", NULL) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.03 sec) ``` Maybe it could be fixed by MySQL function [IFNULL](http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull) and replace NULL values with some characters.

kerem referenced this issue

2026-03-07 20:42:37 +03:00

[PR #142] [MERGED] Expanded Migrations (Flyway, Liquibase XML/YAML, Laravel) & Adds Native DBDiff Migration CLI & Supabase Support #271