search

cube-js / cube

📊 Cube — The Semantic Layer for Building Data Applications
https://cube.dev
Other
17.49k stars 1.74k forks source link

SQL API query errors when comparing two columns #8310

Open angideeksha opened 1 month ago

angideeksha commented 1 month ago

Failed SQL select * from InventoryItems where AvailableQuantity < TotalQuantity

Here is the failed SQL log message - Cube SQL Error {"protocol":"postgres","error":"Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.","apiType":"sql","query":"SELECT FROM InventoryItems WHERE AvailableQuantity < TotalQuantity","appName":"DataGrip 2024.1.3","sanitizedQuery":"SELECT FROM InventoryItems WHERE AvailableQuantity < TotalQuantity"}

Logical Plan [ERROR] {"id":1902,"name":"Cube SQL Error"} Cube SQL Error Exception: Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

Version: "@cubejs-backend/server-core": "0.34.59"

Additional context SQL fails when comparing 2 columns. If TotalQuantity field is replaced with a numeric value, it works. Query fails when comparing 2 string columns too. I tried typecasting but same behavior AvailableQuantity::NUMERIC < TotalQuantity::NUMERIC

paveltiunov commented 4 weeks ago

@angideeksha Those type of queries can be addressed by https://cube.dev/docs/product/apis-integrations/sql-api/query-format#query-pushdown