Bug #121143 Correlated usage of json_table function does not seem to produce values

• Submitted: 20 Aug 9:40
• Modified: 20 Aug 10:51
• Reporter: Christian Beikov
• Status: Duplicate
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.46
• OS: Any
• CPU Architecture: Any

[20 Aug 9:40] Christian Beikov

Description:
Correlated usage of json_table function does not seem to produce values.
The following query works fine:

    select
        ewa1_0.id 
    from
        EntityWithArrays ewa1_0 
    join
        json_table(ewa1_0.the_array, '$[*]' columns(v varchar(255) path '$')) t1_0 
            on true 
    where
        t1_0.v='abc'

but when using a correlation style, it fails.

How to repeat:
Consider the following model:

    create table EntityWithArrays (
        id bigint not null,
        the_array json,
        primary key (id)
    ) engine=InnoDB;

    insert 
    into
        EntityWithArrays
        (the_array, id) 
    values
        (cast('[]' as json), 1);
    insert 
    into
        EntityWithArrays
        (the_array, id) 
    values
        (cast('["abc",null,"def"]' as json), 2);
    insert 
    into
        EntityWithArrays
        (the_array, id) 
    values
        (cast(null as json), 3);

Run the query:

    select
        ewa1_0.id 
    from
        EntityWithArrays ewa1_0 
    where
        'abc' in (
          select t1_0.v
          from json_table(ewa1_0.the_array, '$[*]' columns(
            v varchar(255) path '$'
          )) t1_0
        )

or

    select
        ewa1_0.id 
    from
        EntityWithArrays ewa1_0 
    where
        exists (
          select 1 
          from json_table(ewa1_0.the_array, '$[*]' columns(
            v varchar(255) path '$'
          )) t1_0 
          where t1_0.v='abc'
        )

It should produce a result, but doesn't.

[20 Aug 10:48] Chaithra Marsur Gopala Reddy

Hi Christian Beikov,

Thank you for the test case. We see that this problem has been fixed in 9.3+ with the patch for Bug#114897. So we are marking this as duplicate.

Thanks,
Chaithra

[20 Aug 10:51] Christian Beikov

Are you going to backport the fix to MySQL 8 also?