search

taosdata / TDengine

High-performance, scalable time-series database designed for Industrial IoT (IIoT) scenarios
https://tdengine.com
GNU Affero General Public License v3.0
23.41k stars 4.87k forks source link

Aliases 即 AS 存在兼容性问题：不支持单双引号；无法与 count 函数共同使用 #28766

Open ipaddicting opened 1 week ago

ipaddicting commented 1 week ago

Bug Description Aliases 即 AS 存在兼容性问题：

首先，AS 不支持单双引号，即 " 和 ' 

taos> SELECT TIMETRUNCATE(ts, 1d, 0) AS ts, max(engine_load) AS "max(engine_load)" FROM abacus.log_combikat GR
OUP BY TIMETRUNCATE(ts, 1d, 0) ORDER BY engine_load DESC;
DB error: syntax error near ""max(engine_load)" from abacus.log_combikat group by timetruncate(ts, 1d, 0) order by engine_load desc;" (0.000060s)

去除 ' 或 " 后仅支持简单字符，但存在歧义：

taos> SELECT TIMETRUNCATE(ts, 1d, 0) AS ts, max(engine_load) AS engine_load FROM abacus.log_combikat GROUP BY
TIMETRUNCATE(ts, 1d, 0) ORDER BY engine_load DESC;
           ts            |     engine_load      |
=================================================
 2016-02-08 00:00:00.000 |          -24.3999996 |
 2016-02-04 00:00:00.000 |          -24.3999996 |
 2016-02-17 00:00:00.000 |          -24.3999996 |
 2016-02-18 00:00:00.000 |          -24.3999996 |
 2016-02-13 00:00:00.000 |          -24.3999996 |
 2016-02-02 00:00:00.000 |          -24.3999996 |
 2016-02-07 00:00:00.000 |          -24.3999996 |
 2016-02-14 00:00:00.000 |          -24.3999996 |
 2016-02-19 00:00:00.000 |          -24.3999996 |
 2016-02-06 00:00:00.000 |          -24.3999996 |
 2016-02-12 00:00:00.000 |          -24.3999996 |
 2016-02-03 00:00:00.000 |          -24.3999996 |
 2016-02-15 00:00:00.000 |          -24.3999996 |
 2016-02-16 00:00:00.000 |          -24.3999996 |
 2016-02-29 00:00:00.000 |          -24.5000000 |
 2016-02-01 00:00:00.000 |          -24.5000000 |
 2016-02-24 00:00:00.000 |          -24.5000000 |
 2016-02-27 00:00:00.000 |          -24.5000000 |
 2016-02-22 00:00:00.000 |          -24.5000000 |
 2016-02-11 00:00:00.000 |          -24.5000000 |
 2016-02-10 00:00:00.000 |          -24.5000000 |
 2016-02-21 00:00:00.000 |          -24.5000000 |
 2016-02-20 00:00:00.000 |          -24.5000000 |
 2016-02-28 00:00:00.000 |          -24.5000000 |
 2016-02-25 00:00:00.000 |          -24.5000000 |
 2016-02-09 00:00:00.000 |          -24.5000000 |
 2016-02-05 00:00:00.000 |          -24.5000000 |
 2016-02-26 00:00:00.000 |          -24.5000000 |
 2016-02-23 00:00:00.000 |          -24.5000000 |
Query OK, 29 row(s) in set (0.011580s)

不支持单双引号导致与其他软件如 Superset 存在兼容问题：

Error: [0x2600]: syntax error near ""MAX(engine_load)" 
from abacus.log_combikat group by timetruncate(ts, 1d, 0) order by "MAX(engine_load)" desc
 limit 10000"

其次，COUNT 函数无法使用 AS，报错如下：

taos> SELECT TIMETRUNCATE(ts, 1d, 0) AS ts, count(*) AS count FROM abacus.log_combikat GROUP BY TIMETRUNCATE(t
s, 1d, 0);
DB error: syntax error near "count from abacus.log_combikat group by timetruncate(ts, 1d, 0);" (0.000144s)

移除 AS 可正常查询：

taos> SELECT TIMETRUNCATE(ts, 1d, 0) AS ts, count(*) FROM abacus.log_combikat GROUP BY TIMETRUNCATE(ts, 1d, 0)
           ts            |       count(*)        |
==================================================
 2016-02-05 00:00:00.000 |                   286 |
 2016-02-11 00:00:00.000 |                   282 |
 2016-02-16 00:00:00.000 |                   286 |
 2016-02-06 00:00:00.000 |                   286 |
 2016-02-15 00:00:00.000 |                   286 |
 2016-02-10 00:00:00.000 |                   284 |
 2016-02-21 00:00:00.000 |                   288 |
 2016-02-20 00:00:00.000 |                   288 |
 2016-02-28 00:00:00.000 |                   288 |
 2016-02-14 00:00:00.000 |                   286 |
 2016-02-25 00:00:00.000 |                   288 |
 2016-02-17 00:00:00.000 |                   286 |
 2016-02-18 00:00:00.000 |                   286 |
 2016-02-09 00:00:00.000 |                   286 |
 2016-02-23 00:00:00.000 |                   287 |
 2016-02-02 00:00:00.000 |                   286 |
 2016-02-26 00:00:00.000 |                   288 |
 2016-02-22 00:00:00.000 |                   287 |
 2016-02-27 00:00:00.000 |                   287 |
 2016-02-07 00:00:00.000 |                   286 |
 2016-02-29 00:00:00.000 |                   288 |
 2016-02-13 00:00:00.000 |                   286 |
 2016-02-04 00:00:00.000 |                   286 |
 2016-02-19 00:00:00.000 |                   288 |
 2016-02-01 00:00:00.000 |                   286 |
 2016-02-24 00:00:00.000 |                   288 |
 2016-02-03 00:00:00.000 |                   286 |
 2016-02-12 00:00:00.000 |                   285 |
 2016-02-08 00:00:00.000 |                   286 |
Query OK, 29 row(s) in set (0.009566s)

To Reproduce 参考以上 SQL 语句。

Expected Behavior

  1. AS 需支持单双引号；
  2. AS 可以与 COUNT 函数共同使用。

Environment (please complete the following information):

Additional Context 目前我正在尝试将 taospy 集成进 Superset 的过程中，相关代码会在集成验证完成后提交 taospy 以及 Superset 合并，谢谢。

ipaddicting commented 4 days ago

另外，3.3.3.0 版本测试结果如下：

taos> SELECT COUNT(*) AS total FROM taosd_cluster_info;
         total         |
========================
                     8 |
Query OK, 1 row(s) in set (0.002362s)

taos> SELECT COUNT(*) AS "total_sth" FROM taosd_cluster_info;

DB error: syntax error near ""total_sth" from taosd_cluster_info;" (0.000087s)

COUNT 函数已支持 Alias，但函数别名 AS 依旧不支持单双引号。

根本原因在于 AS 不支持单双引号，与函数使用无关：

taos> SELECT _ts AS time FROM taosd_cluster_info LIMIT 10;
          time           |
==========================
 2024-11-18 02:40:51.591 |
 2024-11-18 02:41:21.595 |
 2024-11-18 02:41:51.782 |
 2024-11-18 02:42:21.983 |
 2024-11-18 02:42:51.983 |
 2024-11-18 02:43:22.018 |
 2024-11-18 02:43:52.126 |
 2024-11-18 02:44:22.275 |
 2024-11-18 02:44:52.399 |
 2024-11-18 02:45:22.480 |
Query OK, 10 row(s) in set (0.004828s)

taos> SELECT _ts AS 'time' FROM taosd_cluster_info LIMIT 10;

DB error: syntax error near "'time' from taosd_cluster_info limit 10;" (0.000082s)
yu285 commented 4 days ago

taos> SELECT COUNT(*) as das FROM meters; das |

              1001 |

Query OK, 1 row(s) in set (0.003325s)

taos> SELECT COUNT(*) AS total FROM meters; total |

              1001 |

Query OK, 1 row(s) in set (0.006529s)

不用引号，用的话使用反引号。

ipaddicting commented 4 days ago

单纯使用 SQL 来讲的话都好解决，但是通过 SQLAlchemy 与其他第三方应用如 Superset 集成的时候就存在该兼容性问题，因为大部分 SQL 数据库都支持 AS 使用单双引号。

当然我也可以在 taospy 中做兼容处理，比如将单双引号统一替换成反引号，但总觉得还是符合主流操作更为合理一些。

yu285 commented 4 days ago

mysql 可以这样写对吗。

yu285 commented 4 days ago

或者其它关系型库

ipaddicting commented 4 days ago

是的，没找到官方文档，但是应该是默认支持：

目前我是通过在 Superset 中自定义的 TDengineEngineSpec 来处理兼容性问题：

class TDengineEngineSpec(BaseEngineSpec):
    engine = 'taosrest'
    engine_name = 'TDengine'

    _time_grain_expressions = {
        None: "{col}",
        "PT1S": "TIMETRUNCATE({col}, 1s, 0)",
        "PT1M": "TIMETRUNCATE({col}, 1m, 0)",
        "PT1H": "TIMETRUNCATE({col}, 1h, 0)",
        "P1D": "TIMETRUNCATE({col}, 1d, 0)",
        "P1W": "TIMETRUNCATE({col}, 1w, 0)",
    }

    @classmethod
    def execute(
        cls,
        cursor: Any,
        query: str,
        database: Database,
        **kwargs: Any,
    ) -> None:
        query = re.sub(r'\s*(?i:AS)\s+[\'"]([^\'"]+)[\'"]', r' AS `\1`', query)
        super().execute(cursor, query, database, **kwargs)