[#MCOL-427] IN behaviour mismatch for empty/NULL strings

The following query will match when tranche_age is NULL:

SELECT * FROM ( SELECT id_unique  FROM DIFSN__odc__client_profil  WHERE ( (tranche_age IN ( '',1,2,3,4,5,6)) ) ) comptage ;

Whereas this query only includes the tranche_age NULL when the 'OR' condition is matched:

SELECT * FROM ( SELECT id_unique  FROM DIFSN__odc__client_profil  WHERE ( (tranche_age IN ( '',1,2,3,4,5,6)) ) OR (date_naiss>"1950-01-01") ) comptage ;

Test table and data:

CREATE TABLE `difsn__odc__client_profil` (

  `id_connect` int(11) DEFAULT '0',

  `id_unique` int(11) DEFAULT '0',

  `civilite` varchar(38) DEFAULT NULL,

  `date_naiss` varchar(10) DEFAULT '',

  `tranche_age` char(1) DEFAULT '',

  `statut_entreprise` varchar(31) DEFAULT ''

) ENGINE=Columnstore DEFAULT CHARSET=utf8;

INSERT INTO `difsn__odc__client_profil` VALUES (0,0,'test','1950-01-01','0','test'),(1,1,'test','1949-01-01','1','test'),(2,2,'test','1948-01-01','2','test'),(3,3,'test','1947-01-01','3','test'),(4,4,'test','1946-01-01','4','test'),(5,5,'test','1945-01-01','5','test'),(6,6,'test','1944-01-01','6','test'),(7,7,'test','1943-01-01','7','test'),(8,8,'test','1942-01-01','8','test'),(9,9,'test','1941-01-01','9','test'),(10,10,'test','1951-01-01','a','test'),(11,11,'test','1952-01-01',NULL,'test'),(12,12,'test','1943-01-01',NULL,'test');

Query results:

MariaDB [test]> SELECT * FROM ( SELECT id_unique  FROM DIFSN__odc__client_profil  WHERE ( (tranche_age IN ( '',1,2,3,4,5,6)) ) OR (date_naiss>"1950-01-01") ) comptage ;

+-----------+

| id_unique |

+-----------+

|         1 |

|         2 |

|         3 |

|         4 |

|         5 |

|         6 |

|        10 |

|        11 |

+-----------+

8 rows in set (0.06 sec)

MariaDB [test]> SELECT * FROM ( SELECT id_unique  FROM DIFSN__odc__client_profil  WHERE ( (tranche_age IN ( '',1,2,3,4,5,6)) ) ) comptage ;+-----------+

| id_unique |

+-----------+

|         1 |

|         2 |

|         3 |

|         4 |

|         5 |

|         6 |

|        11 |

|        12 |

+-----------+

8 rows in set (0.02 sec)

[MCOL-427] IN behaviour mismatch for empty/NULL strings Created: 2016-12-01 Updated: 2023-10-25 Resolved: 2023-10-25
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	PrimProc
Affects Version/s:	None
Fix Version/s:	23.10

[MCOL-427] IN behaviour mismatch for empty/NULL strings Created: 2016-12-01 Updated: 2023-10-25 Resolved: 2023-10-25