Hi Maksim, Interesting, thanks for your reply. Okay, I misunderstood (I also thought being on a single node that it didn't matter).
Is it the case that as long as the affinity key is in the join predicate that it would be a colocated JOIN (I'm concerned about the impact of setDistributedJoins(true))? Or is it the case that if you're joining on partitioned tables, you must do so with ONLY the affinity key in the join predicate? SELECT tbl.releaseId, tbl.name FROM T0 tbl INNER JOIN T1 col ON tbl.releaseId = <What goes here?> In the previous tables, T1 does not have the releaseId as a column so does that mean it is impossible to do a co-located JOIN with this setup? If we modify T1 so that it also has releaseId and we make releaseId the affinity key of T1 will both of these work? SELECT tbl.releaseId, tbl.name FROM T0 tbl INNER JOIN T1 col ON tbl.releaseId = col.releaseId AND SELECT tbl.releaseId, tbl.name FROM T0 tbl INNER JOIN T1 col ON tbl.releaseId = col.releaseId AND col.tableId = tbl.id > AND col.x = y In other words, if both tables share the same affinity key is it still a collocated join if there are other filters in the join predicate? If the answer to this yes, does it matter if the filters in the join predicate are all = i.e. does it have to be an equi-join? or could the predicate be > ON tbl.releaseId = col.releaseId AND* col.tableId > tbl.id > <http://tbl.id/>* AND *col.x >= y* > Thanks On Fri, Feb 11, 2022 at 6:42 PM Maksim Timonin <timoninma...@apache.org> wrote: > Hi Courtney, > > > I don't expect collocation issues to be in play here > > Do you check this doc: > https://ignite.apache.org/docs/latest/SQL/distributed-joins ? > > It says: "A distributed join is a SQL statement with a join clause that > combines two or more partitioned tables. If the tables are joined on the > partitioning column (affinity key), the join is called a colocated join. > Otherwise, it is called a non-colocated join" > > You definitely have a collocation issue due to non-collocated join: T0 > partitioned by "releaseId", T1 by "t0Id", and you make a join by columns > that aren't affinity columns (id = tableId). > > You should specify the flag "SqlFieldsQuery.setDistributedJoins(true)" to > make your join return correct results. > > Maksim > > > On Fri, Feb 11, 2022 at 8:09 PM Courtney Robinson < > courtney.robin...@crlog.info> wrote: > >> >> I have a query like this: >> >> SELECT >>> tbl.id AS tbl_id, tbl.releaseId AS tbl_releaseId, col.type AS col_type >>> FROM T0 tbl >>> INNER JOIN T1 col ON tbl.id = col.tableId >>> *WHERE tbl.releaseId = ? AND tbl.name <http://tbl.name> = ?* >>> LIMIT 100 >>> >> >> This returns no results so after investigating, I ended up changing it to >> the below >> >> SELECT >>> tbl.id AS tbl_id, tbl.releaseId AS tbl_releaseId, col.type AS col_type >>> FROM *(SELECT * FROM T0 t WHERE t.releaseId = ? AND t.name >>> <http://t.name> = ?) *tbl >>> INNER JOIN T1 col ON tbl.id = col.tableId >>> LIMIT 100 >>> >> >> This returns the results expected. >> Can anyone offer any insight into what is going wrong here? >> >> The tables here look like this (I removed some columns from the tables >> and the query to help make it easier on the eyes to parse): >> >> CREATE TABLE IF NOT EXISTS T0 >>> ( >>> id LONG, >>> releaseId VARCHAR, >>> name VARCHAR, >>> PRIMARY KEY (releaseId, id) >>> ) WITH "template=hypi_tpl,affinity_key=releaseId"; >> >> CREATE INDEX IF NOT EXISTS VirtualTable_idx0 ON VirtualTable (releaseId, >>> name); >>> >>> CREATE TABLE IF NOT EXISTS T1 >>> ( >>> id LONG, >>> t0Id LONG, >>> name VARCHAR, >>> type VARCHAR, >>> PRIMARY KEY (t0Id, id) >>> ) WITH "template=hypi_tpl,affinity_key=t0Id"; >>> >> >> Note here it is a single node locally (so I don't expect collocation >> issues to be in play here) - in development so not in a production cluster >> yet. >> Running Ignite 2.8.0 >> >> This is not the first time we've had something like this but it's the >> first time I've been able to reproduce it myself and consistently. >> >> Best, >> Courtney >> >