The query below runs in a loop and never completes.
SELECT VALUE
FROM TABLE(
SYSTOOLS.JSON_TABLE(SYSTOOLS.JSON2BSON(''),
'items',
's:200'
)
);
Repro steps below:
create database jsondb automatic storage yes using codeset utf-8
territory us pagesize 32 K;
connect to jsondb;
CREATE BUFFERPOOL NOSQLSYSTOOLSBP ALL DBPARTITIONNUMS SIZE
AUTOMATIC
PAGESIZE 32K;
CREATE TEMPORARY TABLESPACE NOSQLSYSTOOLSTEMP PAGESIZE 32K
MANAGED BY
AUTOMATIC STORAGE BUFFERPOOL NOSQLSYSTOOLSBP;
update db cfg for jsondb using dft_table_org row;
-- Must use minimum INLINE LENGTH as per docs for 10K blob, 6
-- fails with SQL0604
-- CREATE TABLE T1 (C1 BLOB(10K) INLINE LENGTH 6 );
CREATE TABLE T1 (C1 BLOB(10K) INLINE LENGTH 116 );
INSERT INTO T1 VALUES (BLOB('')) ;
SELECT VALUE FROM T1, TABLE(SYSTOOLS.JSON_TABLE(C1,
'PO.items.item.productName', 's:20')) X;
/* This query just returns empty rows forever */
SELECT
JSON_VAL(
SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))),
'aspectRangeId', 'i')
as RANGE_ID,
JSON_VAL(
SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))), 'aspectName',
's:50')
as ASPECT_NAME,
JSON_VAL(
SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))),
'aspectLabel', 's:50')
as ASPECT_LABEL,
JSON_VAL(
SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))), 'fromValue',
'i')
as FROM_VAL,
JSON_VAL(
SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))), 'toValue',
'i')
as TO_VAL
FROM TABLE(
SYSTOOLS.JSON_TABLE(SYSTOOLS.JSON2BSON(''),
'items',
's:200'
)
);
RANGE_ID ASPECT_NAME ASPECT_LABEL FROM_VAL TO_VAL
----------- ------------ ------------- ----------- -----------
- - - - -
- - - - -
(returns empty rows forever) |
