Vertica数据库按日期时间ASC/DESC排序结果一致问题求助

问题描述 #

使用Vertica数据库时，尝试对日期时间类型的queue_entry_timestamp列分别按ASC和DESC排序，但两次返回结果完全一致。执行的SQL及结果如下：

DESC排序SQL及结果 #

testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY 'queue_entry_timestamp' desc limit 5;
     queue_entry_timestamp     |     acquisition_timestamp     |   queue wait
-------------------------------+-------------------------------+-----------------
 2023-01-27 03:40:02.259231+00 | 2023-01-27 03:40:02.259239+00 | 00:00:00.000008
 2023-01-27 03:40:02.258851+00 | 2023-01-27 03:40:02.25888+00  | 00:00:00.000029
 2023-01-27 03:40:45.958846+00 | 2023-01-27 03:40:45.958872+00 | 00:00:00.000026
 2023-01-27 03:41:41.958178+00 | 2023-01-27 03:41:41.958185+00 | 00:00:00.000007
 2023-01-27 03:42:42.966845+00 | 2023-01-27 03:42:42.966851+00 | 00:00:00.000006
(5 rows)

ASC排序SQL及结果 #

testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY 'queue_entry_timestamp' asc limit 5;
     queue_entry_timestamp     |     acquisition_timestamp     |   queue wait
-------------------------------+-------------------------------+-----------------
 2023-01-27 03:40:02.259231+00 | 2023-01-27 03:40:02.259239+00 | 00:00:00.000008
 2023-01-27 03:40:02.258851+00 | 2023-01-27 03:40:02.25888+00  | 00:00:00.000029
 2023-01-27 03:40:45.958846+00 | 2023-01-27 03:40:45.958872+00 | 00:00:00.000026
 2023-01-27 03:41:41.958178+00 | 2023-01-27 03:41:41.958185+00 | 00:00:00.000007
 2023-01-27 03:42:42.966845+00 | 2023-01-27 03:42:42.966851+00 | 00:00:00.000006
(5 rows)

问题原因 #

ORDER BY子句中用单引号包裹列名'queue_entry_timestamp'，会被Vertica识别为字符串常量而非列名。所有行的该常量值完全相同，因此无论指定ASC还是DESC，排序结果都不会变化——没有实际按列值进行排序。

正确解法 #

去掉ORDER BY中列名的单引号，直接使用列名即可：

DESC排序正确SQL #

testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS "queue wait" FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY queue_entry_timestamp DESC LIMIT 5;

ASC排序正确SQL #

testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS "queue wait" FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY queue_entry_timestamp ASC LIMIT 5;

注：如果列名或别名包含空格、特殊字符，建议用双引号（"）包裹，这是SQL标准的标识符引用方式，比单引号更规范。

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