[MDEV-12686] Valid values (zero and false) appear as NULL (CONNECT JSON) Created: 2017-05-04  Updated: 2017-05-11  Resolved: 2017-05-08

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.1.22
Fix Version/s: 10.1.24, 10.0.31

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 16.04


 Description   

When json file contains 0 or "false" values, CONNECT JSON type shows them as NULL, unless a column declared explicitly as not null. 

MariaDB [test]> \! cat /home/alice/Downloads/3.json
 
  [
 
    {
 
        "i1": 0,
 
        "i2": false,
 
        "i3": null
 
    }
 
]
 
MariaDB [test]>source /home/alice/t/c.sql
 
Show warnings enabled.
 
--------------
 
 
 
create table t1(
 
	i1 int,
 
        i2 int,
 
        i3 int
 
)engine=CONNECT table_type=JSON  File_name='/home/alice/Downloads/3.json'
 
--------------
 
Query OK, 0 rows affected (0.07 sec)
 
 
 
--------------
 
select * from t1
 
--------------
 
+------+------+------+
 
| i1   | i2   | i3   |
 
+------+------+------+
 
| NULL | NULL | NULL |
 
+------+------+------+
 
1 row in set (0.00 sec)
 
 
 
create table t2(
 
	i1 int not null,
 
        i2 int not null,
 
        i3 int
 
)engine=CONNECT table_type=JSON  File_name='/home/alice/Downloads/3.json'
 
--------------
 
Query OK, 0 rows affected (0.05 sec)
 
 
 
--------------
 
select * from t2
 
--------------
 
+----+----+------+
 
| i1 | i2 | i3   |
 
+----+----+------+
 
|  0 |  0 | NULL |
 
+----+----+------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Olivier Bertrand [ 2017-05-04 ]

NULL handling by connect is explained in the Data Types page of the documentation:
https://mariadb.com/kb/en/mariadb/connect-data-types/

Your example works as designed. Let us suppose the file is:

[
 
  {
 
    "i1": 0,
 
    "i2": false,
 
    "i3": null
 
  },
 
  {
 
    "i1": 1,
 
    "i2": true,
 
    "i3": "A"
 
  }
 
]

If the table is created leaving discovery find the column types, this is what is obtained:

create table t2 engine=CONNECT table_type=JSON  File_name='C:/Data/Json/t3.json';
 
show create table t2;
 
CREATE TABLE `t2` (
 
  `i1` bigint(1) NOT NULL,
 
  `i2` tinyint(1) NOT NULL,
 
  `i3` char(1) DEFAULT NULL
 
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' `FILE_NAME`='C:/Data/Json/t3.json';

and when using select:

MariaDB [json]> select * from t2;
 
+----+----+------+
 
| i1 | i2 | i3   |
 
+----+----+------+
 
|  0 |  0 | NULL |
 
|  1 |  1 | A    |
 
+----+----+------+
 
2 rows in set (0.00 sec)

The only problem is that CONNECT (nor MariaDB) does not have yet a true BOOL data type. This is why, TINYINT(1) is used instead. But is it clearly explained that the NULL handling is heavily depending on the null definition of the column.

Comment by Alice Sherepa [ 2017-05-05 ]

According to Codd's Rule 3 the engine should be able to handle differently null and zero at the same time and all other engines are capable to achieve this.
So what should I do if I want to import JSON file, that contains both 0 and null. (or true/false/null)

Comment by Olivier Bertrand [ 2017-05-05 ]

Wait for the fix I've just done but not pushed yet.
If the table is created as:

create table t3 (
 
  i1 int(5) DEFAULT NULL,
 
  i2 tinyint(1) DEFAULT NULL,
 
  i3 char(12) DEFAULT NULL
 
) engine=CONNECT table_type=JSON file_name='C:/Data/Json/t3.json';

Now for the file:

[
 
  {
 
    "i1": 0,
 
    "i2": false,
 
    "i3": null
 
  },
 
  {
 
    "i1": 1,
 
    "i2": true,
 
    "i3": "A"
 
  },
 
  {
 
    "i1": null,
 
    "i3": "25"
 
  }
 
]

the result will be:

MariaDB [test]> select * from t3;
 
+------+------+------+
 
| i1   | i2   | i3   |
 
+------+------+------+
 
|    0 |    0 | NULL |
 
|    1 |    1 | A    |
 
| NULL | NULL | 25   |
 
+------+------+------+
 
3 rows in set (30.29 sec)

Thanks for reporting all this.

Comment by Olivier Bertrand [ 2017-05-08 ]

It was a bug indeed.

Generated at Thu Feb 08 07:59:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.