Joining Models creates Error about nvarchar type to numeric, but same SQL works fine

Get Help
1

I am trying to join two models based on a partnumber string (which I am now casting to nvarchar, but that doesnt help), but get the following behaviour:

  • Error message: An error occurred during the current command (Done status 0). Fehler beim Konvertieren des nvarchar-Datentyps in numeric.
    • (which means Error converting data type nvarchar to numeric )
  • If I display and copy the resulting SQL and run it on the same DB, the query runs without any problem and returns results.
  • If I switch join type to ‘full outer join‘: no error message and the query also runs fine in the metabase UI.
    • (but that is not the required join type)

This is on:

  • Metabase v0.57.6.3
  • MS SQL 2016 Database

I will try to get all the columns in the query hardcoded to nvarchar, but have the feeling that this might not resolve the problem…

Is there any known workaround about this?

2

The issues seems to be related to the data itself “Error converting data type nvarchar to numeric” … Try joining the table but with a limit 5 for example probably it will work cause you’re not converting the value that is failing here

3

still trying:

  • limiting to 5 or less also results in the error
  • deselecting all fields from the dropdown (which should then only display the first column at all it seems) also leads to this error.

So just from those tries, one could think that the problem arises based on the column that is used for joining. But that is also cast to nvar in the sql which defines the models. :man_shrugging:

4

here is the column that the SQL creates which is used to join:

  cast((
      CASE
        WHEN (var = '')
          THEN num
        ELSE (
            CONCAT (
              num,
              '-',
              var
              )
            )
        END
      ) AS NVARCHAR) AS 'partnumber',

maybe that helps for understanding, or I might have included some other mistakes there…

5

Check the DDL for all of the “partnumber” columns, one of them is a number and not text.

6

The funny thing is that in the underlying database tables, those columns are all of type nvarchar:

2025-12-18 15_02_29-Clipboard

Maybe it has to do something with being connected to MS SQL server? I tried to reproduce the problem using postgres sql on the sample db, but there were no problems using joins then :man_shrugging:

edit…

So it makes sense that when running the displayed SQL no errors happen, but it seems that somewhere Metabase does something there?

Well, I found out that it is due to some other column, but hunting it down now stripping both models stepwise…

7

Where does num come from in that CASE statement? Could that be a numeric or decimal?

8

numis the same as varbut just has another name. Its the generic part of the partnumber string. So its also nvarchar as type. :person_shrugging:

I found out that when I strip the columns down from the bottom, I get the error also when displaying a column which also contains text but which comes using two join statements. I will try to get it this anonymized. The join statement uses some columns that are numeric and nun-numeric. but the columns that is in the select statement is non-numeric.

Still I don’t understand why running the query displayed by Metabase as SQL does not result in the error. And as there are no more precise log messages to be found, it’s not so easy to debug since it’s quite a long query for both models.

9

The query that’s shown in the notebook editor isn’t the one that is actually sent to the database. In the one sent to the database, constants like filters are sent as bind variables. Depending on the database system – I know PostgreSQL does this but Sybase-oids like SQL Server may do it too – the application sends a data type along with the variable data. Bind variables are not subject to implicit type cohersion, so if there is a type conflict, it throws an error.

Conversely, in the monitor, there are no bind variables and string literals start untyped. Those untyped literals (in PG they are type ‘unknown’) are subject to implicit cohersion to the most reasonable destination type depending on the context and built-in rules.

As an example, for your snippet here, what is actually sent to the database is more like this:

cast((
      CASE
        WHEN ($1 = '')
          THEN $2
        ELSE (
            CONCAT (
              $2,
              '-',
              $1
              )
            )
        END
      ) AS NVARCHAR) AS 'partnumber',

$1 and $2 are then bound to the values of val and num, and marked as type nvarchar. If var gets sent as numeric, SQL Server throws a type error as it won’t convert numeric to nvarcharto pass to concat, which only accepts string types.

I hope this clarifies the difference you are seeing. It’s a little confusing if you’ve never developed database applications and run into this yourself. Usually Metabase does a good job covering up the details, but native queries expose the underpinnings enough that you can trip over them if you try to do unusual things.

1 Like