Out of range error while accessing a boolean field of a JSON object stored in a column

Help JSON
1

Following the documentation:

We’re implementing a stored procedure as part of the Kafka Memsql pipeline. We have a mix of string, boolean, and integer values.

For integer/numerical values, we used:
JSON_EXTRACT_DOUBLE

For string values:
JSON_EXTRACT_STRING

For boolean values, we used
JSON_EXTRACT_JSON

This worked for us in the first go, but failed at a later point, and now it is persistently failing with the below mentioned exception:

Unhandled exception
        Type: ER_WARN_DATA_OUT_OF_RANGE (1264)
        Message: Leaf Error : Out of range value for column 'booleancolumn'
        Callstack:
        #0 Line 3 in db_name.prc_name called from
        #1 Line 1 in helper

        Leaf Error: Out of range value for column 'booleancolumn'

Following are the questions:

  1. What could be the possible reason for this exception?
  2. We also observed that JSON_EXTRACT_DOUBLE is also working for boolean values. Is that understanding right?
  3. Is there an operator for boolean values just like %(for numerical value), $(for string)?

Here is the sample JSON:
{a:"1",b:2,c:true}

Sample procedure:

    CREATE OR REPLACE PROCEDURE prc_name(SAMPLE_BATCH query(SAMPLE_JSON json))
    AS
    BEGIN
    INSERT INTO table_name(a,b,c)
    SELECT json_extract_string(SAMPLE_JSON, 'a'), json_extract_double(SAMPLE_JSON,'b'),json_extract_json(SAMPLE_JSON,'c')
    FROM SAMPLE_BATCH;
    END;

Corresponding pipeline creation:

CREATE PIPELINE `pipeline_name`
AS LOAD DATA KAFKA 'kafka_url/topic'
FORMAT JSON
(
    `SAMPLE_JSON` <- %
)
2

The issue is one which we inherited in the name of MySQL compatibility: BOOL is just an alias for TINYINT(1), and the SQL TEXT or JSON value "true" doesn’t automatically convert to 1. It’s unfortunate.

Does the stored procedure do more than directly inserting into a table? If not, you can write the pipeline to insert directly into the table e.g. create pipeline ... into table t ( a <- a, b <- b, c <- c) format json. As a pipelines-specific feature, that extraction method will automatically convert “true”/“false” to 1/0 and will also be more efficient.

Otherwise, yes, ::% has the semantics which you’d want:

MySQL [db]> select a, a::%a from t;
+---------------+-------+
| a             | a::%a |
+---------------+-------+
| {"a":true}    |     1 |
| {"a":false}   |     0 |
| {"a":"hello"} |     0 |
+---------------+-------+

There is no explicit extract variant for JSON boolean values. I’ve filed an internal note to revisit this and BOOL semantics, or at least document them more loudly.

1 Like
3

I am facing this same issue with ERROR code 1264 but when a pipeline is loading parquet from S3 Bucket into a table.

This arrangement was working well in 7.9.5, but the moment we upgraded to 8.1.x this issue came up.

Any help?