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

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.

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

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

Hi,

MySQL-8.0 is end-of-life. So we will not be backporting it to that release.

Thanks,
Chaithra

Yes, but what about MySQL 8.4? That is not yet EOL, right?

This bug is now back ported to 8.4. It will be available in MySQL-8.4.14 version.