Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12805

inconsistency in NULL handling, JSON type, CONNECT engine

    XMLWordPrintable

    Details

      Description

      Inconsistency in NULL handling

      JSON file:

      [
          {
              "i": 3,
              "k": 
                {
                  "WHAT": "milk",
                  "AMOUNT": null
                }
          },
          {
              "i": 4,
              "k": 
                {
                  "WHAT": null,
                  "AMOUNT": 5
                }
          },
            {
              "i": 5,
              "k": 
                [{
                  "WHAT": null,
                  "AMOUNT": 10
                },
                {
                  "WHAT": "sugar",
                  "AMOUNT": 20
                },
                 {
                  "WHAT": null,
                  "AMOUNT": 30
                }
              ]
       
          }
          
      ]
      

      Null value is substituted by string "???"

      CREATE TABLE `t1`  ENGINE=CONNECT `TABLE_TYPE`='JSON' `FILE_NAME`='exp.json' ;
      SELECT * FROM t1;
      +---+----------+
      | i | k        |
      +---+----------+
      | 3 | milk ??? |
      | 4 | ??? 5    |
      | 5 | ??? 10   |
      +---+----------+
      
      

      When adding 'level=1' in option_list:

      CREATE TABLE `t2` ENGINE=CONNECT `TABLE_TYPE`='JSON' `FILE_NAME`='exp.json' 
      OPTION_LIST='level=1' ;
      SELECT * FROM t2;
      +---+--------+----------+
      | i | k_WHAT | k_AMOUNT |
      +---+--------+----------+
      | 3 | milk   | NULL     |
      | 4 | NULL   | 5        |
      | 5 | NULL   | 10       |
      +---+--------+----------+
      
      

      When using [" and "] Null is substituted by "". If null is in last position, then it is not represented

      create table t3 (
      i int(2) field_format='i:[]:NUMBER',
      WHAT char(32) field_format='k:[" and "]:WHAT',
      Amm char(32) field_format='k:[", "]:AMOUNT'
      )engine=CONNECT table_type=JSON File_name='exp.json';
       
      select * from t3;
      +------+------------+------------+
      | i    | WHAT       | Amm        |
      +------+------------+------------+
      |    3 | milk       | NULL       |
      |    4 | NULL       | 5          |
      |    5 |  and sugar | 10, 20, 30 |
      +------+------------+------------+
      

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            alice Alice Sherepa
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: