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 >
