CQL查询在DbVisualizer正常运行但AdonisJS中报错求助

错误详情 #

Error executing Redis command: ResponseError: line 1:74 no viable alternative at input '(' (...train_number = '95233' ORDER BY [(]...)
    at FrameReader.readError (/home/dinesh/Desktop/Crowdsource/hello-world/node_modules/cassandra-driver/lib/readers.js:389:17)
    at Parser.parseBody (/home/dinesh/Desktop/Crowdsource/hello-world/node_modules/cassandra-driver/lib/streams.js:209:66)
    at Parser._transform (/home/dinesh/Desktop/Crowdsource/hello-world/node_modules/cassandra-driver/lib/streams.js:152:10)
    at Parser.Transform._write (node:internal/streams/transform:171:8)
    at writeOrBuffer (node:internal/streams/writable:564:12)
    at _write (node:internal/streams/writable:493:10)
    at Parser.Writable.write (node:internal/streams/writable:502:10)
    at Protocol.ondata (node:internal/streams/readable:1007:22)
    at Protocol.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12) {
  info: 'Represents an error message from the server',
  code: 8192,
  coordinator: '107.80.45.9:9042',
  query: `SELECT * FROM train_activities_dump WHERE train_number = '95233' ORDER BY ((CAST("latitude" as float) - 19.061322371609194) * (CAST("latitude" as float) - 19.061322371609194)) + ((CAST("longitude" as float) - 72.874012291431427) * (CAST("longitude" as float) - 72.874012291431427)) ASC LIMIT 1`
}

执行的CQL查询语句 #

SELECT * FROM train_activities_dump WHERE train_number = '95233' ORDER BY ((CAST("latitude" as float) - 19.061322371609194) * (CAST("latitude" as float) - 19.061322371609194)) + ((CAST("longitude" as float) - 72.874012291431427) * (CAST("longitude" as float) - 72.874012291431427)) ASC LIMIT 1

Cassandra表结构 #

CREATE TABLE 
    train_activities_dump 
    ( 
        train_number             TEXT, 
        time                     TIMESTAMP, 
        gps_status               TEXT, 
        latitude                 TEXT, 
        longitude                TEXT, 
        nearest_station_code     TEXT, 
        nearest_station_position TEXT, 
        only_time                TIME, 
        speed                    TEXT, 
        train_status             TEXT, 
        PRIMARY KEY (train_number, time) 
    )

AdonisJS控制器代码 #

import { cassandraClient } from 'Config/cassandra';

const cquery = `SELECT * FROM train_activities_dump WHERE train_number = '${train_number}' ORDER BY ((CAST("latitude" as float) - ${latitude}) * (CAST("latitude" as float) - ${latitude})) + ((CAST("longitude" as float) - ${longitude}) * (CAST("longitude" as float) - ${longitude})) ASC LIMIT 1`;

const history_data = await cassandraClient.execute(cquery);

console.log(history_data);

Cassandra配置文件代码 #

import { Client } from 'cassandra-driver';

export const cassandraConfig = {
  contactPoints: ['107.80.45.9','107.80.45.10'], // Cassandra node IPs
  localDataCenter: 'dc1', // Datacenter name
  keyspace: 'uat' // Keyspace name
};

export const cassandraClient = new Client(cassandraConfig);

核心问题 #

1. 相同CQL查询在DbVisualizer中能正常执行，但在AdonisJS中执行时触发语法错误，错误提示line 1:74 no viable alternative at input '('。
2. 错误信息中错误标注为Error executing Redis command，但实际操作的是Cassandra数据库。

解决方案 #

1. 修复CQL语法问题：ORDER BY不支持复杂表达式 #

Cassandra的ORDER BY子句仅允许对主键列（或聚类列）进行排序，不支持直接使用计算表达式作为排序依据。DbVisualizer可能因为客户端特性或版本兼容问题暂时允许执行，但这不符合Cassandra的语法规范，在官方驱动中会触发语法错误。

方案一：预计算距离并存储为列

在插入或更新数据时，预先计算好经纬度与目标点的距离值（即你当前查询中的表达式结果），将其存储为表中的一个FLOAT类型列（比如命名为distance_to_target），之后查询时直接按该列排序：

-- 先添加列
ALTER TABLE train_activities_dump ADD distance_to_target FLOAT;

-- 查询语句简化为
SELECT * FROM train_activities_dump WHERE train_number = '95233' ORDER BY distance_to_target ASC LIMIT 1;

这种方式是Cassandra中处理此类排序需求的标准做法，符合其数据模型设计，性能最优。

方案二：在客户端进行排序

如果无法修改表结构，可以先查询出所有符合train_number = '95233'的数据，然后在Node.js代码中计算距离并排序：

import { cassandraClient } from 'Config/cassandra';

// 先查询所有符合条件的数据
const cquery = `SELECT * FROM train_activities_dump WHERE train_number = '${train_number}'`;
const result = await cassandraClient.execute(cquery);

// 在客户端计算距离并排序
const targetLat = parseFloat(latitude);
const targetLng = parseFloat(longitude);
const sortedData = result.rows.sort((a, b) => {
  const calcDistance = (lat, lng) => {
    const latDiff = parseFloat(lat) - targetLat;
    const lngDiff = parseFloat(lng) - targetLng;
    return latDiff * latDiff + lngDiff * lngDiff;
  };
  return calcDistance(a.latitude, a.longitude) - calcDistance(b.latitude, b.longitude);
});

// 取第一条数据
const history_data = sortedData[0];
console.log(history_data);

注意：如果该train_number对应的数据量很大，此方法会导致客户端内存占用过高，不建议在数据量大的场景使用。

2. 关于错误提示中的Redis混淆问题 #

这个错误提示是cassandra-driver的一个误报，可能是驱动内部错误信息模板复用导致的，无需额外处理，解决CQL语法问题后该提示会自然消失。

3. 额外建议：使用参数化查询避免SQL注入风险 #

当前代码中直接将变量拼接到CQL语句中，存在SQL注入风险，建议改用驱动支持的参数化查询：

const cquery = `SELECT * FROM train_activities_dump WHERE train_number = ?`;
const result = await cassandraClient.execute(cquery, [train_number]);

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