Issue in Advanced Sync Rules for JOIN Query

[aisyaharifin (Siti Nuraisyah)]

Hi Elastic,

I have a question to ask where I've encountered issue with the Advanced Sync Rules Query :

So I have an application lets call it myStaff, where I've been pulling multiple tables from the db using Microsoft SQL Connectors. As I need to JOIN multiple tables, I face blocker in doing the JOIN query using the Advanced rules.

I notice from the docker logs that it keep skipping my custom query :

As I construct any other rules than the JOIN rules, its working.
Even I tried the simplest JOIN couldn't works the query.
Below is one of the example query :

{
    "tables": [
      "Post",
      "Channel"
    ],
    "query": "SELECT TOP 100 P.id as PostID, C.id as ChannelID from Post P INNER JOIN Channel C ON C.Id = P.ChannelId where C.IsDeleted ='0' and C.isPrivate ='0' and P.IsDeleted ='0'"
  },

I've already checked with the technical team on the queries and everything works from their side. When they test the query. Is there any issue with the JOIN query?
I've been looking into this reference for the query and I'm already in 8.11.1 ver of Elastic :
https://www.elastic.co/guide/en/enterprise-search/8.11/connectors-ms-sql.html#connectors-ms-sql-sync-rules-example-one-join

Need help with this, thanks.