Unclear error location

aryehgielchinsky

I'm having trouble with Vertica's description of errors. It doesn't tell me what line it is on, so the errors can often be very hard to find. We often get something like this
`Query execution failed

Reason:
SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near ","`

My code is full of commas! Is there any way to get the row number of the error?

We access our Vertica DB through Dbeaver and Redash.
Thanks

Jim_Knicely

Check the DC_ERRORS system table.

Example:

dbadmin=> SELECT 1,,2;
ERROR 4856:  Syntax error at or near "," at character 10
LINE 1: SELECT 1,,2;
                 ^
dbadmin=> SELECT time, log_message, cursor_position FROM dc_errors ORDER BY time DESC LIMIT 1;
             time              |         log_message         | cursor_position
-------------------------------+-----------------------------+-----------------
 2019-02-13 09:26:20.089451-05 | Syntax error at or near "," |              10
(1 row)

I can see that the error is in position 10 of the SQL I ran!

Vertica Quick Tip:
https://forum.vertica.com/discussion/239461/viewing-query-error-information

aryehgielchinsky

I saw that Vertica Quick Tip before, but it is very inconvenient to have to find my error in another table. Is there any way to get that information into a SQL client like Dbeaver or Redeash? I've attached a picture of the error popup I get. The details button is already expanded.

Jim_Knicely

Note that in my example using vsql, the error message included the text "at character 10". Unfortunately that info is being omitted in DBeaver, but I am not sure why. This seems to be the case with any SQL client that uses the Vertica JDBC driver.

I will check around to see if I can figure out why that's the case and will create a feature request to modify that behavior if the issue is on our side.

In the meantime, I think the checking the Vertica system tables is your only option to find the error position.

It's not to difficult if you run the query against the DC_ERRORS table immediately after you get the error:

Maybe create a view to show the last error in you current session?

dbadmin=> CREATE OR REPLACE VIEW my_last_error AS
dbadmin->   SELECT time, log_message, cursor_position
dbadmin->     FROM dc_errors
dbadmin->    WHERE session_id = current_session()
dbadmin->    ORDER BY time DESC
dbadmin->    LIMIT 1;
CREATE VIEW

dbadmin=> SELECT 1,,2;
ERROR 4856:  Syntax error at or near "," at character 10
LINE 1: SELECT 1,,2;
                 ^

dbadmin=> SELECT * FROM my_last_error;
             time              |         log_message         | cursor_position
-------------------------------+-----------------------------+-----------------
 2019-02-13 09:55:25.592443-05 | Syntax error at or near "," |              10
(1 row)

ftobin

It seems this is a bug in the JDBC driver Vertica in that it doesn't deliver the complete error message . Using 3 separate SQL editors, the error messages truncate after the first line, stopping the user from seeing the line #/context.

SergeB

It is possible to get the position using the Vertica JDBC driver but the application you use needs to leverage it.

Here's how to get all information about latest Vertica error on your current connection:

                VerticaDatabaseErrorInfo errorinfo = ((VerticaConnection) conn).getLastDatabaseError();
                System.out.println("getDetail: " + errorinfo.getDetail());
                System.out.println("getErrorCode: " + errorinfo.getErrorCode());
                System.out.println("getErrorLevel: " + errorinfo.getErrorLevel());
                System.out.println("getHint: " + errorinfo.getHint());
                System.out.println("getPosition: " + errorinfo.getPosition());
                System.out.println("getSQLState: " + errorinfo.getSQLState()); 
                System.out.println("getMessage: " + errorinfo.getMessage()); 

Using Jim's sample query (SELECT 1,,2) would show the following:

getDetail: null
getErrorCode: 4856
getErrorLevel: ERROR
getHint: null
getPosition: 10
getSQLState: 42601
getMessage: Syntax error at or near ","

ftobin

It's unfortunate for the driver to expect the client do the linenumber lift, because DBeaver, DBVisualizer, and SQLDbx all exhibit the same behavior. The driver seems the odd man out here. Requires having a workaround of having a separate terminal with vsql open just for the purposes of copying/pasting SQL and showing me the line number.

mosheg

1) Jim's example to find the exact place for the syntax error, works nice in DBeaver as follows:
select 1,,1;
SELECT time, log_message, cursor_position FROM dc_errors WHERE session_id = current_session() ORDER BY time DESC LIMIT 1;
2) CTRL+ALT+SHIFT+X Execute queries in DBeaver simultaneously, showing results in separate tabs.

3) Click on the Skip button and get the 2nd query result in a separate DBeaver tab:
Syntax error at or near "," 10

ftobin

Hi mosheg, I'm not seeing how the example you gave gives the user any useful information about where the error is. In vsql, at least the line number is given, along with a caret pointing to the position in the line:

PROD=> select 1,,1;
ERROR 4856:  Syntax error at or near "," at character 10
LINE 1: select 1,,1;
                 ^

The key information is in the second and third lines lines. The character position of an error is not useful in a in a 200-line SQL query. Even simply the the line # would be extremely helpful.

Imagine compiling a C++ program and being told "syntax error at character 4000". Simply not useful.

Jim_Knicely

You can always test the sql in VSQL first