Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-427

IN behaviour mismatch for empty/NULL strings

    XMLWordPrintable

Details

    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)
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            LinuxJedi Andrew Hutchings (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.