TO_JSON conversion of TINYINT columns

{question}

How to convert TINYINT columns to TO_JSON?

{question}

{answer}

The TINYINT datatype is a very small integer with the range (-128) to 127. BOOL and BOOLEAN are synonymous with TINYINT. A value of 0 is considered FALSE, non-zero values are considered TRUE.

In SingleStore versions 7.5 and earlier, the TO_JSON conversion of TINYINT is not supported for values other than 0 or 1. Therefore, the below errors would be thrown on trying for conversion.

ERROR 2459 UNKNOWN_ERR_CODE: Leaf Error (<>): Converting to JSON requires 0 or 1 for TINYINT input. Please cast to a numeric datatype using ':>' to trigger conversion to a JSON number, rather than a JSON boolean. 

ERROR 2459 UNKNOWN_ERR_CODE: Leaf Error (<>): TO_JSON() requires 0 or 1 for TINYINT input. Please cast to a numeric datatype using ':>' to trigger conversion to a JSON number, rather than a JSON boolean.

Example:

CREATE TABLE inv_detail (
inv_id bigint(20) DEFAULT NULL,
lineno tinyint(4) DEFAULT NULL,
partno int(11) DEFAULT NULL,
disc_pct tinyint(4) DEFAULT NULL,
KEY inv_id (inv_id)
);

insert into inv_detail values (1,2,3,4);

select TO_JSON(lineno :> TINYINT) from inv_detail;
 ERROR 2459 UNKNOWN_ERR_CODE: Leaf Error (<>): Converting to JSON requires 0 or 1 for TINYINT input. Please cast to a numeric datatype using ':>' to trigger conversion to a JSON number, rather than a JSON boolean. 

As a workaround, typecast the value to a numeric datatype(for example, INT) specified in the error message to get the desired result, as shown below.

select TO_JSON(lineno :> INT) from inv_detail;

This behavior is planned to be changed in later versions, which would allow us to convert the TINYINT values other than 0/1 into JSON without the need to convert them into numeric datatypes.

{answer}