cockroachdb / cockroachdb/cockroach
sql: jsonpath parse errors are obscured for routine arguments
- 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
Assessment
This issue has not been assessed yet.