Query unsupported: filtering on results of cross-shard subquery

[Yohan Bismuth]

Hello,

I have a java application using hibernate and connected to mysql with the native mysql jdbc driver.

I have a query that's not working. I'm querying a sharded keyspace. Here is my error:

unsupported: filtering on results of cross-shard subquery

And here is the query generated:

select

        resourcese0_.PID as PID1_0_,

        resourcese0_.RES_DELETED_AT as RES_DELE2_0_,

        resourcese0_.RES_ENCODING as RES_ENCO3_0_,

        resourcese0_.RES_VERSION as RES_VERS4_0_,

        resourcese0_.FORCED_PID as FORCED_P5_0_,

        resourcese0_.HAS_TAGS as HAS_TAGS6_0_,

        resourcese0_.PROV_REQUEST_ID as PROV_REQ7_0_,

        resourcese0_.PROV_SOURCE_URI as PROV_SOU8_0_,

        resourcese0_.RES_PUBLISHED as RES_PUBL9_0_,

        resourcese0_.RES_TEXT as RES_TEX10_0_,

        resourcese0_.RES_ID as RES_ID11_0_,

        resourcese0_.RES_TYPE as RES_TYP12_0_,

        resourcese0_.RES_VER as RES_VER13_0_,

        resourcese0_.RES_UPDATED as RES_UPD14_0_ 

    from

        ( SELECT

            h.pid               as pid,

            h.res_id            as res_id,

            h.res_type          as res_type,

            h.res_version       as res_version,

            h.res_ver           as res_ver,

            h.has_tags          as has_tags,

            h.res_deleted_at    as res_deleted_at,

            h.res_published     as res_published,

            h.res_updated       as res_updated,

            h.res_text          as res_text,

            h.res_encoding      as res_encoding,

            p.SOURCE_URI        as PROV_SOURCE_URI,

            p.REQUEST_ID        as PROV_REQUEST_ID,

            f.forced_id         as FORCED_PID      

        FROM

            HFJ_RES_VER h     

        LEFT OUTER JOIN

            HFJ_FORCED_ID f 

                ON f.resource_pid = h.res_id     

        LEFT OUTER JOIN

            HFJ_RES_VER_PROV p 

                ON p.res_ver_pid = h.pid     

        INNER JOIN

            HFJ_RESOURCE r       

                ON r.res_id = h.res_id 

                and r.res_ver = h.res_ver ) resourcese0_ 

    where

        resourcese0_.RES_ID in (

            ? , ? , ? , ? , ? , ? , ? , ? , ? , ?

        )

In mysql compatibility doc (https://vitess.io/docs/reference/mysql-compatibility/), I saw that:

LEFT JOIN is supported as long as there are not expressions that compare columns on the outer table to the inner table in sharded keyspaces.

Could it be my issue ? Do you know how I could workaround it, if that's possible ?

Thank you

[Rohit Nayak]

Are you also on Slack?: you will likely get much faster responses from the community at https://vitess.slack.com/messages

Otherwise let me know and I will forward this message on to the query processing folks.

[Harshit Gangal]

Also, it is better to create issues for these so that it can be tracked.

--
You received this message because you are subscribed to the Google Groups "vitess" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vitess+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/vitess/a3481e15-6a9b-41da-a50e-433c84a1d6edn%40googlegroups.com.

[Yohan Bismuth]

I'll give slack a try then, I saw there was a java channel.

Thank you