union duplicate key

[yaozhu]

mysql table baseall and table test has the same tale struct and recored

show create table baseall;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| baseall | CREATE TABLE baseall (
k0 tinyint(1) DEFAULT NULL,
k1 tinyint DEFAULT NULL,
k2 smallint DEFAULT NULL,
k3 int DEFAULT NULL,
k4 bigint DEFAULT NULL,
k5 decimal(9,3) DEFAULT NULL,
k6 char(5) DEFAULT NULL,
k10 date DEFAULT NULL,
k11 datetime DEFAULT NULL,
k7 varchar(20) DEFAULT NULL,
k8 double DEFAULT NULL,
k9 float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE test (
k0 tinyint(1) DEFAULT NULL,
k1 tinyint DEFAULT NULL,
k2 smallint DEFAULT NULL,
k3 int DEFAULT NULL,
k4 bigint DEFAULT NULL,
k5 decimal(9,3) DEFAULT NULL,
k6 char(5) DEFAULT NULL,
k10 date DEFAULT NULL,
k11 datetime DEFAULT NULL,
k7 varchar(20) DEFAULT NULL,
k8 double DEFAULT NULL,
k9 float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

execute sql as follow:
SELECT *
FROM
(SELECT *
FROM
(SELECT k1, k2, k3, k4, k5, k6, k10, k11, k7, k8, k9
FROM test.baseall) AS t
LEFT JOIN
(SELECT k1, k2, k3, k4, k5, k6, k10, k11, k7, k8, k9
FROM test.test) AS t0
ON
t.k1 = t0.k1
UNION
SELECT *
FROM
(SELECT k1, k2, k3, k4, k5, k6, k10, k11, k7, k8, k9
FROM test.baseall) AS t1
RIGHT JOIN
(SELECT k1, k2, k3, k4, k5, k6, k10, k11, k7, k8, k9
FROM test.test) AS t2
ON
t1.k1 = t2.k1) AS t
LIMIT 1000

report error Duplicated inline view column alias k1 in inline view t

[cgivre]

Can you please provide a screenshot or some explanation that is more clear? This is really difficult to follow.

[yaozhu]

Can you please provide a screenshot or some explanation that is more clear? This is really difficult to follow.

[yaozhu]

@cgivre are u clear?

[cgivre]

I'm not yet convinced there is a bug in Drill here. I think the issue may actually be in your query. My hunch here is that there may be an issue in the ORDER BY statement. Your inner tables each have columns called k1. Then you are calling a sort on that without specifying which one it is. You might first try adding table names to the ORDER BY clause.

Secondly, I'm not trying to be rude but what are you trying to achieve with this query? It looks like you are performing a union of a left and right join of the same table? Why not combine these? Then you have nested SELECT * statements that don't really seem to serve any purpose.

[yaozhu]

This is a test case written by testers, without actual usage scenarios. What do you mean by combining them?