1

We use calcite to execute a mysql query, the SQL is:

select a.title, c.score from phx_ep.phx_gql_product a left join phx_ep.phx_gql_product_tag c on a.product_id = c.product_id limit 3

It's simple and clear, but calcite optimized it to:

SELECT `t2`.`title`, `t2`.`score`
FROM (SELECT *
FROM (SELECT `product_id`, `title`
FROM (SELECT *
FROM `phx_ep`.`phx_gql_product`
LIMIT 3) AS `t`) AS `t0`
LEFT JOIN (SELECT `product_id`, `score`
FROM `phx_ep`.`phx_gql_product_tag`) AS `t1` ON `t0`.`product_id` = `t1`.`product_id`
LIMIT 3) AS `t2`

Which caused a Duplicate column name error

If remove 'limit 3', it works well.

Is it a bug?

Jakub Kriz
  • 1,501
  • 2
  • 21
  • 29
Linyy
  • 23
  • 6
  • It's hard to say without seeing all the steps you took to optimize the query. Your best bet would be to open an issue on [Calcite's issue tracker](https://issues.apache.org/jira/projects/CALCITE/issues) with a reproducible test case. – Michael Mior Jul 22 '20 at 15:25

0 Answers0