[MDEV-12688] INSERT INTO tinyint of CONNECT.JSON brings NULL 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   

After inserting values into a column, that is defined as TINYINT (JSON table, CONNECT engine), result values are NULL

--------------
 
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)
 
 
 
--------------
 
insert into t1 values(1,1,1)
 
--------------
 
Query OK, 1 row affected (0.00 sec)
 
 
 
--------------
 
select * from t1
 
--------------
 
+------+------+------+
 
| i1   | i2   | i3   |
 
+------+------+------+
 
|    1 |    1 |    1 |
 
+------+------+------+
 
1 row in set (0.00 sec)
 
 
 
--------------
 
create table t2(
 
	    i1 tinyint,
 
        i2 tinyint,
 
        i3 tinyint
 
)engine=CONNECT table_type=JSON  File_name='/home/alice/Downloads/3.json'
 
--------------
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
--------------
 
select * from t2
 
--------------
 
+------+------+------+
 
| i1   | i2   | i3   |
 
+------+------+------+
 
|    1 |    1 |    1 |
 
+------+------+------+
 
1 row in set (0.00 sec)
 
 
 
--------------
 
insert into t2 values(1,1,1)
 
--------------
 
Query OK, 1 row affected (0.00 sec)
 
 
 
--------------
 
select * from t2
 
--------------
 
+------+------+------+
 
| i1   | i2   | i3   |
 
+------+------+------+
 
|    1 |    1 |    1 |
 
| NULL | NULL | NULL |
 
+------+------+------+
 
2 rows in set (0.00 sec)



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

Same as MDEV-686

Comment by Alice Sherepa [ 2017-05-05 ]

bug MDEV-686 is exclusively about NULL vs '0' and 'false' for any type;
this bug: any value becomes NULL only for tinyint type.

create table t2 ( i1 tinyint not null) engine=CONNECT table_type=JSON;
 
insert into t2 values (0), (5), (125);
 
select * from t2;
 
--------------
 
+----+
 
| i1 |
 
+----+
 
|  0 |
 
|  0 |
 
|  0 |
 
+----+
 
3 rows in set (0.00 sec)

Comment by Olivier Bertrand [ 2017-05-05 ]

Indeed a bug when writing the json file. Type tiny was forgotten and nulls were written (in fact no pair).
However, remember that CONNECT JSON is using TINYINT to represent Boolean values. After fixing the bug, the file is created as:

[
 
  {
 
    "i1": false
 
  },
 
  {
 
    "i1": true
 
  },
 
  {
 
    "i1": true
 
  }
 
]

and 

select * from t2;

will return:

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