fix(bug-7): prevent NULL columns from collapsing CONCAT checksum

jasdeepkhalsa · jasdeepkhalsa · commit cc63c436432a · 2026-03-25T22:28:08.000Z
MySQL CONCAT() returns NULL if any argument is NULL, causing SHA2(CONCAT(...)) to produce NULL for any row with nullable columns. Two genuinely different rows both hash to NULL and compare as equal — silently dropping real data differences from the diff output. Fix: wrap each column in IFNULL(col, '\0') inside the CAST before concatenation, and add a NULL-presence bitmap (IF(col IS NULL, '1', '0')) as a secondary comparison column. The WHERE clause now checks both hash1 <> hash2 OR nullmap1 <> nullmap2, so: - NULL vs empty string are distinguished (different bitmaps) - NULL vs non-NULL values are caught (different hashes + bitmaps) - Regular value changes continue to work via hash comparison Refs: bugs.md Bug #7, PR #77, PR #63
diff --git a/src/DB/Data/LocalTableData.php b/src/DB/Data/LocalTableData.php
@@ -382,14 +382,23 @@ public function getChangeDiff($table, $key) {
 
         $wrapCast = function($arr, $p) {
             return array_map(function($el) use ($p) {
-                return "CAST(`{$p}`.`{$el}` AS CHAR CHARACTER SET utf8)";
+                return "CAST(IFNULL(`{$p}`.`{$el}`, '\\0') AS CHAR CHARACTER SET utf8)";
+            }, $arr);
+        };
+
+        // NULL-presence bitmap: distinguishes NULL from empty string
+        $wrapNullCheck = function($arr, $p) {
+            return array_map(function($el) use ($p) {
+                return "IF(`{$p}`.`{$el}` IS NULL, '1', '0')";
             }, $arr);
         };
 
         $columnsAas = implode(',', $wrapAs($columns1, 'a', 's_'));
         $columnsA   = implode(',', $wrapCast($columns1, 'a'));
+        $columnsA0  = implode(',', $wrapNullCheck($columns1, 'a'));
         $columnsBas = implode(',', $wrapAs($columns2, 'b', 't_'));
         $columnsB   = implode(',', $wrapCast($columns2, 'b'));
+        $columnsB0  = implode(',', $wrapNullCheck($columns2, 'b'));
         
         $keyCols = implode(self::SQL_AND, array_map(function($el) {
             return "`a`.`{$el}` = `b`.`{$el}`";
@@ -399,10 +408,12 @@ public function getChangeDiff($table, $key) {
         $result = $this->source->select(
            "SELECT * FROM (
                 SELECT $columnsAas, $columnsBas, SHA2(concat($columnsA), 256) AS hash1,
-                SHA2(concat($columnsB), 256) AS hash2 FROM `{$db1}`.`{$table}` as a 
+                SHA2(concat($columnsB), 256) AS hash2,
+                CONCAT($columnsA0) AS nullmap1, CONCAT($columnsB0) AS nullmap2
+                FROM `{$db1}`.`{$table}` as a 
                 INNER JOIN `{$db2}`.`{$table}` as b  
                 ON $keyCols
-            ) t WHERE hash1 <> hash2");
+            ) t WHERE hash1 <> hash2 OR nullmap1 <> nullmap2");
         $this->setFetchMode(\PDO::FETCH_ASSOC);
         
         foreach ($result as $row) {
