cockroachdb / cockroachdb/cockroach

sql: jsonpath parse errors are obscured for routine arguments

Open
#144,909 1 comment 0 reactions 0 assignees View on GitHub
A-sql-builtins branch-release-25.2 C-bug O-qa T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

In Postgres, calling `jsonb_path_query` with invalid syntax in the JsonPath argument results in the syntax error being printed:
```
postgres=# SELECT jsonb_path_query('{}', 'foobarinvalid');
ERROR: syntax error at end of jsonpath input
LINE 1: SELECT jsonb_path_query('{}', 'foobarinvalid');
^
```
In CRDB, this instead results in a failure to resolve the function:
```
root@localhost:26257/defaultdb> SELECT jsonb_path_query('{}', 'foobarinvalid');
ERROR: unknown signature: jsonb_path_query(string, string)
SQLSTATE: 42883
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
```

This behavior seems to mask any parse-time error in the JsonPath input, including `unimplemented` and `invalid regexp` errors.

Postgres:
```
postgres=# SELECT jsonb_path_query('{}', '$ ? (@ like_regex "(invalid pattern")');
ERROR: invalid regular expression: parentheses () not balanced
LINE 1: SELECT jsonb_path_query('{}', '$ ? (@ like_regex "(invalid p...
^
postgres=# SELECT jsonb_path_query('{}', '$.datetime()');
ERROR: jsonpath item method .datetime() can only be applied to a string
```
CRDB:
```
root@localhost:26257/defaultdb> SELECT jsonb_path_query('{}', '$ ? (@ like_regex "(invalid pattern")');
ERROR: unknown signature: jsonb_path_query(string, string)
SQLSTATE: 42883
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
root@localhost:26257/defaultdb> SELECT jsonb_path_query('{}', '$.datetime()');
ERROR: unknown signature: jsonb_path_query(string, string)
SQLSTATE: 42883
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
```

In fact, this issue applies to user-defined routines as well:
```
root@localhost:26257/defaultdb> CREATE OR REPLACE PROCEDURE p(foo JSONPATH) LANGUAGE PLpgSQL AS $$ BEGIN RAISE NOTICE
-> 'hello'; END $$;
-> CALL p('$');
-> CALL p('invalid');
CREATE PROCEDURE

Time: 43ms total (execution 22ms / network 21ms)

NOTICE: hello
CALL

Time: 1ms total (execution 0ms / network 0ms)

ERROR: procedure p(string) does not exist
SQLSTATE: 42883
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
```

Jira issue: CRDB-49601

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.