联合所有在TDengine

Question

最近，我在TDengine上尝试了union，发现有一个问题：

taos> select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc;
         count         |              loc               |              loc               |
==========================================================================================
                    10 | nchar0                         | nchar0                         |
                    10 | nchar1                         | nchar1                         |
                    10 | nchar2                         | nchar2                         |
                    10 | nchar3                         | nchar3                         |
                    10 | nchar4                         | nchar4                         |
                    10 | nchar5                         | nchar5                         |
Query OK, 6 row(s) in set (0.003831s)

taos> select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc;
Query OK, 0 row(s) in set (0.002620s)

taos> select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc
   -> union all
   -> select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc;
         count         |              loc               |              loc               |
==========================================================================================
                    10 | nchar0                         | nchar0                         |
                    10 | nchar1                         | nchar1                         |
                    10 | nchar2                         | nchar2                         |
                    10 | nchar3                         | nchar3                         |
                    10 | nchar4                         | nchar4                         |
                    10 | nchar5                         | nchar5                         |
Query OK, 6 row(s) in set (0.004686s)

taos> select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc
   -> union all
   -> select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc;
         count         |              loc               |              loc               |
==========================================================================================

Query OK, 0 row(s) in set (0.004371s)

与上述查询相比，查询3和查询4的结果为何不同？这让我很困惑。

Answer 1

因为TDengine的SQL是一种类似SQL的语言，所以我猜它的SQL实现基本上与SQL相同。在mySQL中，UNIONALL的实现原则是根据前面的表结构而不是下表生成结果表。例如，表1的结构是：

CREATE TABLE `table_1` (
 `col1` int(255) DEFAULT NULL,
 `col2` int(255) DEFAULT NULL,
 `col3` int(255) DEFAULT NULL,
 `col4` int(255) DEFAULT NULL
) ENGINE = InnoDB CHARSET = latin1;

表2的结构如下：

CREATE TABLE `table_1` (
 `col4` int(255) DEFAULT NULL,
 `col3` int(255) DEFAULT NULL,
 `col2` int(255) DEFAULT NULL,
 `col1` int(255) DEFAULT NULL
) ENGINE = InnoDB CHARSET = latin1;

表1 Unionall和表2的结果与表1的结构相同，表2的Unionall的结果与表2的结构相同。

因此，对于这个问题，在第二种情况下，前一个表是空的，而TDengine可能无法识别它的表结构，因此结果表结构也是空的，从而导致结果不一致。