查询含/的topic数据时提示No field named错误的技术求助

问题场景 #

查询mqtt_consumer表前10条数据可正常返回结果：

public ❯ select * from mqtt_consumer limit 10;
+-------------------------------+--------------------+------------------------+--------------------+
| time                          | topic              | performance_activation | time_activation    |
+-------------------------------+--------------------+------------------------+--------------------+
| 2024-06-27T06:31:22.203227346 | sensors/activation | 19.328101094847515     | 26.856237052074817 |
| 2024-06-27T06:31:23.207314888 | sensors/activation | 61.598580777367005     | 53.612591775556886 |
| 2024-06-27T06:31:24.208854263 | sensors/activation | 80.02786075710074      | 57.167999081609786 |
| 2024-06-27T06:31:25.210799208 | sensors/activation | 98.98474977501623      | 22.953056836471283 |
| 2024-06-27T06:31:26.211302625 | sensors/activation | 49.916298791053215     | 8.198248465125758  |
| 2024-06-27T06:31:27.214480084 | sensors/activation | 18.184126975880464     | 0.4830122264985766 |
| 2024-06-27T06:31:28.218643626 | sensors/activation | 53.78084514606698      | 54.888674844135735 |
| 2024-06-27T06:31:29.224451376 | sensors/activation | 88.32009858311108      | 37.332068586185876 |
| 2024-06-27T06:31:30.225176127 | sensors/activation | 9.30493354696381       | 61.23087737886471  |
| 2024-06-27T06:31:31.227389294 | sensors/activation | 8.63619374306103       | 38.906050960079625 |
+-------------------------------+--------------------+------------------------+--------------------+

但使用WHERE条件匹配含/的字符串时，触发报错：

public ❯ select * from mqtt_consumer where topic = "sensors/activation" limit 10;
422 Unprocessable Entity, details: {"error_code":"010001","error_message":"Datafusion: Schema error: No field named \"sensors/activation\". Valid fields are mqtt_consumer.time, mqtt_consumer.topic, mqtt_consumer.performance_activation, mqtt_consumer.time_activation."}

问题原因 #

SQL语法中，双引号"用于引用标识符（如字段名、表名），单引号'才用于表示字符串常量。用双引号包裹sensors/activation会让Datafusion误将其识别为字段名，而表中不存在该字段，因此抛出"No field named"错误。

解决方案 #

将匹配字符串的双引号替换为单引号，修改后的SQL语句如下：

select * from mqtt_consumer where topic = 'sensors/activation' limit 10;

单引号会让Datafusion正确识别sensors/activation为字符串常量，从而匹配topic字段的对应值，执行查询即可返回预期结果。

内容的提问来源于stack exchange，提问作者Ivan