on 2020 Sep 30 6:03 AM
As a follow-up to this FAQ, it seems that the sp_parse_json system procedure only supports numbers as double or integer:
begin declare MyJson long varchar; select '{"MyDouble": -12345678901234.567890, "MyEValue": -1e6, "MyInt": -2147483648, "MyBigInt1": 2147483648, "MyBigInt2": -1234567890123456789, "MyBit": 1}' into MyJson; drop variable if exists sql_row; call sp_parse_json ('sql_row', MyJson); select '1 MyDouble' as "Variable", exprtype('select sql_row.MyDouble', 1) as Data_Type, sql_row.MyDouble as Value union all select '2 MyEValue', exprtype('select sql_row.MyEValue', 1), sql_row.MyEValue union all select '3 MyInt', exprtype('select sql_row.MyInt', 1), sql_row.MyInt union all select '4 MyBigInt1', exprtype('select sql_row.MyBigInt1', 1), sql_row.MyBigInt1 union all select '5 MyBigInt2', exprtype('select sql_row.MyBigInt2', 1), sql_row.MyBigInt2 union all select '6 MyBit', exprtype('select sql_row.MyBit', 1), sql_row.MyBit order by 1; end;
returns
1 MyDouble;double;-12345678901234,568 2 MyEValue;double;-1000000 3 MyInt;integer;-2147483648 4 MyBigInt1;integer;2147483647 <-- wrong 5 MyBigInt2;integer;-2147483648 <-- wrong 6 MyBit;integer;1
This was tested with 17.0.10.6175.
Resume: MyBigInt1 and MyBigInt2 are wrongly limited to the maximum positive resp. negative integer values, so it seems that JSON numbers in the format without fraction and exponent are wrongly casted to integer instead of bigint or other "huger" types.
Request clarification before answering.
FWIW, this has been partly fixed with SQL Anywhere 17.0.11.6933 and above, according to the docs the parsing of integral values now allows for signed bigint numbers.
However, large bigint values seem to get rounded to thousands, see values 5 and 6 with the minimum and maximum bigint values (tested with 17.0.11.7236):
begin declare MyJson long varchar; select '{"MyDouble": -12345678901234.567890, "MyEValue": -1e6, "MyInt": -2147483648, "MyBigInt1": 2147483648, "MyBigInt2": -9223372036854775808, "MyBigInt3": 9223372036854775807, "MyBit": 1}' into MyJson; drop variable if exists sql_row; call sp_parse_json ('sql_row', MyJson); select '1 MyDouble' as "Variable", exprtype('select sql_row.MyDouble', 1) as Data_Type, sql_row.MyDouble as Value union all select '2 MyEValue', exprtype('select sql_row.MyEValue', 1), sql_row.MyEValue union all select '3 MyInt', exprtype('select sql_row.MyInt', 1), sql_row.MyInt union all select '4 MyBigInt1', exprtype('select sql_row.MyBigInt1', 1), sql_row.MyBigInt1 union all select '5 MyBigInt2', exprtype('select sql_row.MyBigInt2', 1), sql_row.MyBigInt2 union all select '6 MyBigInt3', exprtype('select sql_row.MyBigInt3', 1), sql_row.MyBigInt3 union all select '7 MyBit', exprtype('select sql_row.MyBit', 1), sql_row.MyBit order by 1; end;
returns
1 MyDouble;double;-12345678901234,568 2 MyEValue;double;-1000000 3 MyInt;bigint;-2147483648 4 MyBigInt1;bigint;2147483648 5 MyBigInt2;bigint;-9223372036854776000 --< note the difference to -9223372036854775808 6 MyBigInt3;bigint;9223372036854776000 --< note the difference to 9223372036854775807 7 MyBit;bigint;1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This behavior appears to only occur when one or both of MyDouble and MyValue are selected in the query. For example, the following will work
select '5 MyBigInt2', exprtype('select sql_row.MyBigInt2', 1), sql_row.MyBigInt2 union all select '6 MyBigInt3', exprtype('select sql_row.MyBigInt3', 1), sql_row.MyBigInt3 -- union all -- select '1 MyDouble' as "Variable", exprtype('select ----sql_row.MyDouble', 1) as Data_Type, sql_row.MyDouble as Value -- union all -- select '2 MyEValue', exprtype('select sql_row.MyEValue', 1), sql_row.MyEValue
If you uncomment the select for MyDouble and/or MyEValue, the behaviour reported will be seen.
User | Count |
---|---|
54 | |
6 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.