In cases where a SQL query does not contain an explicit FROM clause, table name appears as 'unknown'

[JesseLaughlin]

Is your feature request related to a problem? Please describe.

The issue seems to be rooted in the NodeJs agent's parsing of the SQL statement.

This line:
https://github.com/newrelic/node-newrelic/blob/main/lib/db/query-parsers/sql.js#L15
shows that the query parsing for SELECT requires a FROM clause to be included in the query.

However, it is possible to run a SELECT query without a FROM clause. For example, when using the pattern SELECT schema.FunctionName(arg1, arg2) to return a value (integer, varchar, jsonb, etc-- not a "table"), as shown here:

https://www.postgresql.org/docs/current/sql-syntax-calling-funcs.html

In this use case, the query appears in APM as "Postgres unknown select", even though the table name is visible in the query details.

This appears to have been missed in our integration tests, which all include an explicit FROM clause:

node-newrelic/test/lib/cross_agent_tests/sql_parsing.json

Line 13 in 683b254

{"input":"SELECT * FROM `database`.foobar WHERE x > y", "operation":"select", "table":"foobar"},

Feature Description

We would like to see the agent's SQL regex parsing updated to handle SELECT queries without a FROM clause. We would like the table name to be displayed in the Datastore metric name, rather than unknown.

[workato-integration]

https://issues.newrelic.com/browse/NR-165290

[mrickard]

Thank you for the feature request, @JesseLaughlin!

[workato-integration]

https://new-relic.atlassian.net/browse/NR-165290

[bizob2828]

Internal: Sync up with .net agent. They had a community PR that allowed customers to replace the parsed collection with a sql comment for cases like this