INNER JOIN ... USING performs cross join

[bgrainger]

a INNER JOIN b ON a.x = b.x performs an inner join, as expected, but a INNER JOIN b USING (x) appears to perform a cross join (i.e., Cartesian product).

I was able to reproduce this with the built-in information_schema.collations and information_schema.character_sets tables; I didn't yet find a repro with a user-created table.

-- 272 rows returned
select coll.CHARACTER_SET_NAME, cs.CHARACTER_SET_NAME
from information_schema.collations coll
inner join information_schema.character_sets cs
on coll.CHARACTER_SET_NAME = cs.CHARACTER_SET_NAME;

-- 11,152 rows (= 272 * 41) returned
select coll.CHARACTER_SET_NAME, cs.CHARACTER_SET_NAME
from information_schema.collations coll
inner join information_schema.character_sets cs
using (CHARACTER_SET_NAME);

Expectation is that the second query will also return 272 rows. This works with MySQL Community Server 8.0.31.

[orware]

@bgrainger,

I'll report this internally as well to our Vitess team because I see this as being related to some other INFORMATION_SCHEMA fixes that have been corrected related to the newer Gen4 query planner in recent months on our end.

I was able to run a test using the older V3 query planner and the second query returned the expected 272 results.

I did run a simplified test beforehand though with a user created table and also didn't experience the same sort of issue so it may be limited to areas like the INFORMATION_SCHEMA only.

For reference the user created table was pretty simple and I pulled it mostly from a section of the MySQL JOIN documentation page and tweaked the example queries to more closely match the example you had shared above.

CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);

Example queries:

select t1.j, t2.j
from t1
inner join t2
on t1.j = t2.j;

-- Would potentially return more rows than the first query above:
select t1.j, t2.j
from t1
inner join t2
USING (j);

[bgrainger]

Your example looks very much like what I tried. I also tried joining on a VARCHAR(64) column (like CHARACTER_SET_NAME) in case that triggered the bug, but that still worked as expected for user-defined tables.

[orware]

@bgrainger,

A fix for the issue you've brought up here should be making its way into PlanetScale more generally within the next 1-2 weeks. It is already part of the latest updates but depending on the type of database branch it may be on a different schedule for receiving the latest updates.

It should be more immediate though if testing things from a new development branch.