Overview of the Issue

When doing a LEFT JOIN with USING, we rewrite the USING clause to a WHERE clause leading to the error invalid syntax near where.

Below is an example of the query used to reproduce this.

Reproduction Steps

Unit test

In onecase.json use the following query:

SELECT * FROM unsharded_authoritative as A LEFT JOIN unsharded_authoritative as B USING(col1)

The plan output is:

{
  "QueryType": "SELECT",
  "Original": "SELECT * FROM unsharded_authoritative as A LEFT JOIN unsharded_authoritative as B USING(col1)",
  "Instructions": {
    "OperatorType": "Route",
    "Variant": "Unsharded",
    "Keyspace": {
     "Name": "main",
      "Sharded": false
    },
    "FieldQuery": "select A.col1 as col1, A.col2 as col2, B.col2 as col2 from unsharded_authoritative as A left join unsharded_authoritative as B where 1 != 1",
    "Query": "select A.col1 as col1, A.col2 as col2, B.col2 as col2 from unsharded_authoritative as A left join unsharded_authoritative as B where A.col1 = B.col1",
    "Table": "unsharded_authoritative"
  },
  "TablesUsed": [
    "main.unsharded_authoritative"
  ]
}

Here we can see the aforementioned issue where we have twice the same alias (col2) and we have a left join with a where clause.

End-to-End Test

Using a similar query as mentioned in the previous section but in an end-to-end test shows the error MySQL produces.

func TestLeftJoinUsingUnsharded(t *testing.T) {
	mcmp, closer := start(t)
	defer closer()

	utils.Exec(t, mcmp.VtConn, "insert into uks.unsharded(id1) values (1),(2),(3),(4),(5)")
	utils.Exec(t, mcmp.VtConn, "select * from uks.unsharded as A left join uks.unsharded as B using(id1)")
}

Running the test above in the go/test/endtoend/vtgate/queries/misc/misc_test.go file leads to the following error:

target: uks.0.primary: vttablet: rpc error: code = InvalidArgument desc = syntax error at position 99 near 'where' (CallerID: userData1) (errno 1105) (sqlstate HY000) during query: select * from uks.unsharded as A left join uks.unsharded as B using(id1)

Binary Version

all supported branches

Operating System and Environment details

n/a

Log Fragments

No response