Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
Description
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)
|