Thread: Function Error Response Is Not Clear
To whom it may concern,
Some functions if they are missing the correct arguments give vague error messages while others give specific ones.
Specific error message example query 1:
SELECT sum();
Specific error message example output 1:
ERROR: function sum() does not exist
LINE 1: SELECT sum();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
ERROR: function sum() does not exist
LINE 1: SELECT sum();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Specific error message example query 2:
SELECT sum();
Specific error message example output 2:
ERROR: function lower() does not exist
LINE 1: SELECT lower();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
LINE 1: SELECT lower();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Vague error message example query 1:
SELECT nullif();
Vague error message example output 1:
ERROR: syntax error at or near ")"
LINE 1: SELECT nullif();
^
LINE 1: SELECT nullif();
^
Vague error message example query 2:
SELECT nullif(1);
Vague error message example output 2:
ERROR: syntax error at or near ")"
LINE 1: SELECT nullif(1);
^
LINE 1: SELECT nullif(1);
^
Vague error message example query 1:
SELECT coalesce();
Vague error message example output 1:
ERROR: syntax error at or near ")"
LINE 1: SELECT coalesce();
^
LINE 1: SELECT coalesce();
^
These examples it is clear the function is the issue, but in my actual query there were many parentheses nearby so I thought I had mismatched parentheses for a while and it sent me hunting for the wrong thing wasting precious time on a production bugfix mid-crisis. I do not know if any other function besides NULLIF and COALESCE are affected by this. They are just the two I happened to test.
Best,
Luke
Luke Muther <lmutes8@gmail.com> writes:
> Some functions if they are missing the correct arguments give vague error
> messages while others give specific ones.
Yeah, it's difficult to move the goalposts here at reasonable cost.
The functions that you are complaining about do not go through the
normal parsing process for functions; instead they have hard-wired
grammar productions:
/*
* Special expressions that are considered to be functions.
*/
func_expr_common_subexpr:
...
| NULLIF '(' a_expr ',' a_expr ')'
...
| COALESCE '(' expr_list ')'
and Bison just doesn't do any better than "syntax error" if it
cannot find a match in the grammar. In these particular cases
we could make the grammar production more agnostic and complain
about wrong-number-of-arguments in parse analysis. However
that idea doesn't go very far, because most of the variants of
func_expr_common_subexpr have weird SQL-spec-mandated special
syntax, eg
| TRIM '(' trim_list ')'
...
trim_list: a_expr FROM expr_list
| FROM expr_list
| expr_list
;
So I don't see an easy way to make more than a very small
dent in the problem. If you're feeling motivated to
research this and look for a better answer, please do.
regards, tom lane