I'm using JSON on a column to store language translations.
The provided livesql link has all the steps to reproduce, but here's the basics:
create table px_projects2 (
id number generated by default on null as identity (start with 1) primary key not null
, name_jtl varchar2(500) not null constraint px_projects2_name_tl_ck CHECK (name_jtl is json(strict))
, description_jtl varchar2(500) not null constraint px_projects2_desc_tl_ck CHECK (description_jtl is json(strict))
, alias varchar2(32)
, active_ind varchar2(1) not null
, created_by varchar2(60) default
coalesce(
sys_context('APEX$SESSION','app_user')
, regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
, sys_context('userenv','session_user')
)
not null
, created_on date default sysdate not null
, constraint px_projects2_ck_active check (active_ind in ('Y', 'N'))
)
enable primary key using index
/
-- Insert two rows
insert into PX_PROJECTS2 (NAME_JTL,DESCRIPTION_JTL,ALIAS,ACTIVE_IND) values (
'[{"l":"en","tl":"Name 1 - English"},
{"l":"fr","tl":"Nom 1 - Francais"},
{"l":"es","tl":"Nombre 1 - Espanol"}]'
,'[{"l":"en","tl":"Desc 1 - English"},
{"l":"fr","tl":"Desc 1 - Francais"},
{"l":"es","tl":"Desc 1 - Espanol"}]'
,'Nom 1','Y');
insert into PX_PROJECTS2 (NAME_JTL,DESCRIPTION_JTL,ALIAS,ACTIVE_IND) values ('[{"l":"en","tl":"Name 2- English"},{"l":"fr","tl":"Nom 2 - Francais"},{"l":"es","tl":"Nom 2 - Espaniol"}]','[{"l":"en","tl":"Desc 2 - English"},{"l":"fr","tl":"Desc 2 - Francais"},{"l":"es","tl":"Desc 2 - Espaniol"}]','Nom 2','Y');
-- This query correctly displays ALL the data
select t.id
, jdn.lang
, jdn.tl name
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdn
, json_table(t.description_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdd
where jdn.lang = jdd.lang
/
-- This one correctly shows the data in English (lang = 'en')
select t.id
, jdn.lang
, jdn.tl name
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdn
, json_table(t.description_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdd
where jdn.lang = jdd.lang
and jdn.lang = 'en'
/
-- But this one returns NOTHING!
select t.id
, jdn.lang
, jdn.tl name
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdn
, json_table(t.description_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdd
where jdn.lang = jdd.lang
and jdn.lang = 'fr'
/
I've tried rewriting as WITH statements. But I still cannot get the languages other than the first.
Am I doing something wrong? Is this a bug?
Thank you for your help!
Thanks for the test case.
Looks like a bug to me, because the MATERIALIZE hint creates a different result (which obviously should never be the case)
with t1 as (
select t.id
, jdn.lang
, jdn.tl name
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdn
),
t2 as (
select t.id
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.description_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdd
)
select t1.id, t1.lang, t1.name, t2.description
from t1, t2
where t1.id = t2.id
and t1.lang = t2.d_lang
and t1.lang = 'fr';
with t1 as (
select /*+ materialize */ t.id
, jdn.lang
, jdn.tl name
from px_projects2 t
, json_table(t.name_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdn
),
t2 as (
select /*+ materialize */ t.id
, jdd.lang d_lang
, jdd.tl description
from px_projects2 t
, json_table(t.description_jtl, '$[*]'
columns (
lang varchar2(10) path '$.l'
, tl varchar2(200) path '$.tl'
)) jdd
)
select t1.id, t1.lang, t1.name, t2.description
from t1, t2
where t1.id = t2.id
and t1.lang = t2.d_lang
and t1.lang = 'fr';
Please send this along to Support. (It also reproduces on 12.2)