When use * calculation in multiple table join sql will cause mysql duplicate column issue

1

Env info:

Dremio dremio-community-15.5.0

DB type: mysql5.7

Table struct:

create table t1(id int, c1 double);
create table t2(id int, c1 double);
create table t3(id int, c1 double);

Used sql below to query the mysql table:

select 
    a.c1 * 1.1
from 
    t1 a,
    t2 b,
    t3 c
where 
    a.id=b.id
    and a.id=c.id

Error:

Source ‘mysql71’ returned error ‘(conn=96728) Duplicate column name ‘id’’.

Check the sql in Physical plan, the * is used in alias, and the sql is not supported in Mysql :

SELECT `t`.`*` AS `EXPR$0`
FROM (SELECT `t1`.`id`, `t1`.`*`
FROM (SELECT `t1`.`id`, (`t1`.`c1` * 1.1) AS `*`
FROM `test`.`t1`) AS `t1`
INNER JOIN (SELECT `t2`.`id`
FROM `test`.`t2`) AS `t2` ON `t1`.`id` = `t2`.`id`) AS `t`
INNER JOIN (SELECT `t3`.`id`
FROM `test`.`t3`) AS `t3` ON `t`.`id` = `t3`.`id`
2

@wujx Can you try using External Query instead?

3

Thank you for your reply, it works with external query.
And can you give us more advices about when should we use exerntal query?

4

@wujx External query can be used when you have a certain function that Dremio’s SQL library cannot parse