Vertica 40450 error from Tableau.

Kurt_Larson

Just back from the Vertica conferences and I have an internal customer getting this error from Tableau when trying to create a data extract from a Vertica instance.

[Vertica][Support] (40321) String value '0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' resulted in an invalid numeric.

I can't seem to find a SQL code or an error number that relates to this in any of the documentation or even a meaningful Google search result.

Has anyone seen this or know what it means?

[Deleted User]

Hi Kurt,

Hope you had a good time at the conference!

This particular error means pretty much what the error text says:  The current query is being asked to cast "0.000000...[lots of 0's]...000" to a numeric, and it couldn't produce a valid numeric from that string.

I'm not immediately sure why; it would depend on your schema and query.  But that's a suspiciously long number; I wonder if it has too much precision to fit into the size of the requested numeric field.

For more details, you'll probably have to look at the specific query that's being executed (in either the Vertica logs or our system tables).

Adam

Kurt_Larson

Write a reply   

Kurt_Larson

Hi Adam,

Thanks for providing some info on the error itself.  This particular error makes no sense in the context of the query.  i've since found the failed query in v_monitor.query_profiles and it has no string casts to numerics in it.   The table does have numeric columns and I'm wondering if the cast is the other direction, perhaps coming from the client side.    I'm thinking this because:

1. According to v_monitor.query_profiles the instance processes approx 12K of the 7.5M row result set.
2. The same query text executes and completed fine from another client, like DBeaver.
3. The same query ran fine in my customer environment as recently as a month ago, and other more data loaded there have been NO server side changes to the Vertica instance.

Could the error text I posted have originated from the Vertica client side instead of the server?

In the mean time we are working to further isolate and diagnose the issue, including reproducing the problem on another Tableau client installation, and looking futher into the v_monitor schema information.

Thanks,
Kurt

[Deleted User]

Hm...  I think it's most likely, given your above description that the error does come from the server but that it's triggered by client behavior.  You could verify this by checking to see if the error is listed in "vertica.log" on the query's initiator node.

ODBC/JDBC/etc clients can request casts that are not explicitly part of the SQL statement:  When you ask for a column, you ask for it as a number or string or whatnot (type specifics dependent on C/Java/etc); if you ask for something in a format that differs from that of the natural output of the specified SQL statement, that's effectively a type-cast.  But it wasn't in the SQL statement itself; you would have to review the code or logs of the app that's internally performing the cast (in this case, Tableau).


Most numerics can be correctly represented as a string; errors in that direction aren't too common.  The one failure mode that I'm aware of offhand is a numeric with too much precision being inserted into a varchar that's too small.  But in that case, I get the following error:

=> select '0.0'::numeric(20,2)::varchar(3);
ERROR 4208:  Numeric '0.00' is too long for type varchar(3)

It's always possible that there's a way to get that error that I'm not aware of, but this does look to me like a failed string->numeric cast of some sort.

Kurt_Larson

Hi Adam,

The problem turned to out to be exactly what you describe, an implicit cast for the client.  After looking at the data in the result of this particular query it turns out the numeric always returns a value with zero after the decimal point.  The usage of the column in the result set of the query supported adding an explicit ::int cast to the query, making the problem go away.  You've been a big help.  In hindsight, the only thing that would have been better is to be able to have the error code turn up in a doc. search.

Thanks!