UNION 查询失败，出现"double query has incompatible types: Union, varchar"

Question

我正在尝试将 2 个数据集与 Amazon Athena 中的不同列联合起来。查询失败，出现以下错误

第 78：1 行：双查询中的第 8 列具有不兼容的类型：联合、瓦尔查尔

据我所知，所有列共享相同的数据类型(主要是双精度，除了字符串的时间)。我也对这种类型错误感到困惑。听起来它说一列具有类型联合，另一列具有 varchar 类型，但这没有任何意义。

您会注意到我用"将一些列名括起来，这只是为了考虑SQL没有解析的"-"字符

我还在这里更改了所有列名称，所以如果有什么不对劲，这就是原因

CREATE
OR REPLACE VIEW combined_dataset AS
SELECT time,
RRLTTR001,
RRLTTR002,
XXTRL001m,
XXTRL002m,
XXTRL003m,
XXTRL004m,
TYLLO001,
XXTRL005m,
ERTLT035,
ERTLT036,
ERTLT034,
ERTLT001,
ERTLT002,
ERTLT003,
ERTLT004,
XXTRL006m,
XXTRL007m,
ERTLT005,
ERTLT006,
ERTLT007,
ERTLT008,
ERTLT009,
XXTRL008m,
XXTRL009m,
ERTLT010,
ERTLT011,
ERTLT012,
ERTLT013,
ERTLT014,
XXTRL010m,
XXTRL011m,
ERTLT015,
ERTLT016,
ERTLT017,
ERTLT018,
ORTEX001,
ORTEX002,
ORTEX003,
XXTRL012m,
XXTRL013m,
ERTLT019,
ERTLT020,
ERTLT021,
ERTLT022,
ERTLT023,
ERTLT024,
ERTLT025,
ERTLT026,
XXTRL014m,
TYLLO002,
XXTRL015m,
XXTRL016m,
ERTLT027,
ERTLT028,
XXTRL017m,
XXTRL018m,
XXTRL019m,
ERTLT029,
ERTLT030,
ERTLT031,
ERTLT032,
ERTLT033,
TYLLO003,
ORTEX004,
ORTEX005,
ORTEX006,
ORTEX007,
ORTEX008,
location,
facility,
NULL AS "TRTL0-p",
NULL AS "CCYLY-p",
NULL AS "FFoooliPLP-p"
FROM dataset_1
UNION
SELECT Time,
RRLTTR001,
RRLTTR002,
XXTRL001m,
XXTRL002m,
XXTRL003m,
XXTRL004m,
"FFoooliPLP-p",
XXTRL005m,
ERTLT001,
ERTLT002,
ERTLT003,
ERTLT004,
XXTRL006m,
XXTRL007m,
ERTLT005,
ERTLT006,
ERTLT007,
ERTLT008,
ERTLT009,
XXTRL008m,
XXTRL009m,
ERTLT010,
ERTLT011,
ERTLT012,
ERTLT013,
ERTLT014,
XXTRL010m,
XXTRL011m,
ERTLT015,
ERTLT016,
ERTLT017,
ERTLT018,
ORTEX001,
ORTEX002,
ORTEX003,
XXTRL012m,
XXTRL013m,
ERTLT019,
ERTLT020,
ERTLT021,
ERTLT022,
ERTLT023,
ERTLT024,
ERTLT025,
ERTLT026,
XXTRL014m,
"CCYLY-p",
XXTRL015m,
XXTRL016m,
ERTLT027,
ERTLT028,
XXTRL017m,
XXTRL018m,
XXTRL019m,
ERTLT029,
ERTLT030,
ERTLT031,
ERTLT032,
ERTLT033,
"TRTL0-p",
ORTEX007,
ORTEX008,
ERTLT034,
ERTLT035,
ERTLT036,
location,
facility,
NULL AS ORTEX006,
NULL AS ORTEX005,
NULL AS ORTEX004,
NULL AS TYLLO001,
NULL AS TYLLO002,
NULL AS TYLLO003
FROM dataset_2

我已经检查了数据类型，一切看起来都很好。也用谷歌搜索了一下，什么也没找到。这必须是一个简单的问题，我只是不知道要寻找什么

编辑：这是表格定义，伙计们，如果您发现我错过了什么，请告诉我。我不得不重做替换，所以如果你看到任何看起来搞砸了的名字，这可能是我在修改这篇文章的架构时犯的错误

数据集 1

1   time    string
2   RRLTTR001   double
3   RRLTTR002   double
4   XXTRL001m   double
5   XXTRL002m   double
6   XXTRL003m   double
7   XXTRL004m   double
8   TYLLO001    double
9   XXTRL005m   double
10  ERTLT035    double
11  ERTLT036    double
12  ERTLT034    double
13  ERTLT001    double
14  ERTLT002    double
15  ERTLT003    double
16  ERTLT004    double
17  XXTRL006m   double
18  XXTRL007m   double
19  ERTLT005    double
20  ERTLT006    double
21  ERTLT007    double
22  ERTLT008    double
23  ERTLT009    double
24  XXTRL008m   double
25  XXTRL009m   double
26  ERTLT010    double
27  ERTLT011    double
28  ERTLT012    double
29  ERTLT013    double
30  ERTLT014    double
31  XXTRL010m   double
32  XXTRL011m   double
33  ERTLT015    double
34  ERTLT016    double
35  ERTLT017    double
36  ERTLT018    double
37  ORTEX001    double
38  ORTEX002    double
39  ORTEX003    double
40  XXTRL012m   double
41  XXTRL013m   double
42  ERTLT019    double
43  ERTLT020    double
44  ERTLT021    double
45  ERTLT022    double
46  ERTLT023    double
47  ERTLT024    double
48  ERTLT025    double
49  ERTLT026    double
50  XXTRL014m   double
51  TYLLO002    double
52  XXTRL015m   double
53  XXTRL016m   double
54  ERTLT027    double
55  ERTLT028    double
56  XXTRL017m   double
57  XXTRL018m   double
58  XXTRL019m   double
59  ERTLT029    double
60  ERTLT030    double
61  ERTLT031    double
62  ERTLT032    double
63  ERTLT033    double
64  TYLLO003    double
65  ORTEX004    double
66  ORTEX005    double
67  ORTEX006    double
68  ORTEX007    double
69  ORTEX008    double
70  location    string
71  facility    string

数据集 2

1   time    string
2   RRLTTR001   double
3   RRLTTR002   double
4   XXTRL001m   double
5   XXTRL002m   double
6   XXTRL003m   double
7   XXTRL004m   double
8   FFoooliPLP-p    double
9   XXTRL005m   string
10  ERTLT001    double
11  ERTLT002    double
12  ERTLT003    double
13  ERTLT004    double
14  XXTRL006m   double
15  XXTRL007m   double
16  ERTLT005    double
17  ERTLT006    double
18  ERTLT007    double
19  ERTLT008    double
20  ERTLT009    double
21  XXTRL008m   double
22  XXTRL009m   double
23  ERTLT010    double
24  ERTLT011    double
25  ERTLT012    double
26  ERTLT013    double
27  ERTLT014    double
28  XXTRL010m   double
29  XXTRL011m   double
30  ERTLT015    double
31  ERTLT016    double
32  ERTLT017    double
33  ERTLT018    double
34  ORTEX001    double
35  ORTEX002    double
36  ORTEX003    double
37  XXTRL012m   double
38  XXTRL013m   double
39  ERTLT019    double
40  ERTLT020    double
41  ERTLT021    double
42  ERTLT022    double
43  ERTLT023    double
44  ERTLT024    double
45  ERTLT025    double
46  ERTLT026    double
47  XXTRL014m   double
48  CCYLY-p double
49  XXTRL015m   double
50  XXTRL016m   double
51  ERTLT027    double
52  ERTLT028    double
53  XXTRL017m   double
54  XXTRL018m   double
55  XXTRL019m   double
56  ERTLT029    double
57  ERTLT030    double
58  ERTLT031    double
59  ERTLT032    double
60  ERTLT033    double
61  TRTL0-p     double
62  ORTEX007    double
63  ORTEX008    double
64  ERTLT034    double
65  ERTLT035    double
66  ERTLT036    double
67  location    string
68  facility    string

Answer 1

正如您提到的，大多数列都是双列，并且两个表具有相同的数据类型，我只能看到很少的字段，例如"FFoooliPLP-p"，"CCYLY-p"，"TRTL0-p"dataset_2表中硬编码，这可能是罪魁祸首或ORTEX008，位置，dataset_1表中的设施字段可以是不同的数据类型。

如果无法解决，请共享表结构。