JSON_COMPOSE returns a string instead of a number for a decimal value

Database
Teradata Employee

JSON_COMPOSE returns a string instead of a number for a decimal value

Hi ,

I have upgraded my system from 14.10 to  15.10.05.06 and i am facing an issue.
Please find the scenario below:

create table T1 (V BIGINT NOT NULL);

 * Table has been created.
 * Total elapsed time was 2 seconds.


insert into T1 values (12);

 * Insert completed. One row added.
 * Total elapsed time was 1 second.


insert into T1 values (123);

 * Insert completed. One row added.
 * Total elapsed time was 1 second.
 
 select V*0.01 from T1
)
select V,
V (format '--Z(I)9.99', named F),
cast(json_compose(V as v1, V (format '--Z(I)9.99') as v2) as VARCHAR(50)) as Z
from T;

* Query completed. 2 rows found. 3 columns returned.
 * Total elapsed time was 1 second.

                   V                      F Z
-------------------- ---------------------- -------------------------------
                1.23                   1.23 {"v1":1.23,"v2":1.23}
                 .12                   0.12 {"v1":".12","v2":0.12}  <<<<<".12" comes like string instead of number or decimal
    
The output is showing V1 as string instead of number e.g :"v1":".12"
I have checked when i am inserting three digit number it is showing number but wjhen i am using two digit number it is showing output as string.
"v1":".12"<<<<<<<<String as an output
"v2":1.23 <<<<<<<<Number as an output

Thanks in Advance.

Best Regards,
Sunder

3 REPLIES
Junior Apprentice

Re: JSON_COMPOSE returns a string instead of a number for a decimal value

Hi Sunder,

 

Have you got your example correct?

In your post below the last SELECT shows as:

 select V*0.01 from T1
)
select V,
V (format '--Z(I)9.99', named F),
cast(json_compose(V as v1, V (format '--Z(I)9.99') as v2) as VARCHAR(50)) as Z
from T;

Where is table 'T'? 

In your sample output you show

I think the start of this SQL may be missing and it is meant to show a derived table (called 'T'). Is that correct?

 

Assuming that is what it is meant to show I coded the following:

 WITH t (v)
 AS
 (
 SELECT V*0.01 AS v FROM T1
)
SELECT V,
V (FORMAT '--Z(I)9.99', NAMED F),
CAST(JSON_COMPOSE(V AS v1, V (FORMAT '--Z(I)9.99') AS v2 ) AS VARCHAR(50)) AS Z
FROM T;

With that I get the same results as you.

Note that the 'switch point' - i.e. at what value does the result display as a number instead of string - is when the value is 1.0 or higher.

I added another row to the table with a value of 100 and got the following results:

v	F	Z
1.23	1.23	{"v1":1.23,"v2":1.23}
1.00	1.00	{"v1":1.00,"v2":1.00}
0.12	0.12	{"v1":".12","v2":0.12}

I'm running on TD 15.10.1.1. I've tested on 16.0 and 16.10 (beta) and the results are the same.

 

I couldn't see anything obvious from JSON specs or boards that refer to values less than 1.0 being returned as strings, so i don't know if this is 'what is expected' or a bug in the TD json_compose function. It might be worth checking with TD support on this.

 

Cheers,

Dave

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: JSON_COMPOSE returns a string instead of a number for a decimal value

Thanks Dave ,

 

I will check and update.

 

Best Regards,

Sunder

Teradata Employee

Re: JSON_COMPOSE returns a string instead of a number for a decimal value

In the JSON specification, a number must always have at least one digit to the left of the decimal point (and must not have leading zeros unless a single zero is the only digit to the left of the decimal).

So 0.12 is a number, as is -0.12 but .12  and -.12 are not valid numbers (nor is 012 or 00.12).

 

The default Teradata format for DECIMAL fields does not include a leading zero for values with magnitude less than 1. (You can SELECT V, FORMAT(V) to see the default format that is being used.)