Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.23
-
None
-
ubuntu 16.04
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 | |
+------+------------+------------+ |