SQLite Forum

Wrong column type from sub-query
Login

Wrong column type from sub-query

(1) By Simon (simon_o) on 2020-08-11 14:35:56 [source]

Hello everyone,

I'm seeing a curious issue involving SQLite and the JDBC driver, and I'm wondering whether there is any information on it.

Consider this query:

SELECT DETAIL.DC_ALIAS_0 AS DC_ALIAS_0,
       DETAIL.DC_ALIAS_1 AS DC_ALIAS_1,
       DETAIL.DC_ALIAS_2 AS DC_ALIAS_2,
       DETAIL.DC_ALIAS_3 AS DC_ALIAS_3
FROM
  (SELECT DISTINCT t17.PARAMETER AS DC_ALIAS_0,
                   DATE(t18.DATE, 'start of year') AS DC_ALIAS_1,
                   SUM(t18.VALUE) AS DC_ALIAS_2,
                   '{t:d}' AS DC_ALIAS_3
   FROM (MST_VALUE t18
         INNER JOIN MST_PARAMETER t17 ON (t18.PARAMETER_NO = t17.PARAMETER_NO))
   GROUP BY t17.PARAMETER,
            DATE(t18.DATE, 'start of year')
   UNION ALL SELECT DISTINCT t17.PARAMETER AS DC_ALIAS_0,
                             NULL AS DC_ALIAS_1,
                             SUM(t18.VALUE) AS DC_ALIAS_2,
                             '{t:st}' AS DC_ALIAS_3
   FROM (MST_VALUE t18
         INNER JOIN MST_PARAMETER t17 ON (t18.PARAMETER_NO = t17.PARAMETER_NO))
   GROUP BY t17.PARAMETER) DETAIL
ORDER BY DETAIL.DC_ALIAS_0,
         DETAIL.DC_ALIAS_1

From the POV of the JDBC driver (JDBC3ResultSet#getColumnType(int)) the column type of DC_ALIAS_1 is NUMERIC, which appears to be some catch-all type, despite the DATE(...) part of that query (and e. g. "2004-05-06" not qualifying for any kind of numeric type at all).

I first looked at the JDBC driver as the culprit, but the function that retrieves the type appears to be written in C.

Any ideas?

Thanks!

Simon

(2) By Richard Hipp (drh) on 2020-08-11 15:16:12 in reply to 1 [link] [source]

The sqlite3_column_decltype() that your JDBC driver is presumably calling (aside: there is no "the" JDBC driver - multiple JDBC drivers are available for SQLite) only works if the result column is coming directly from a table. SQLite has no concept of a datatype for a function.

(3) By Simon (simon_o) on 2020-08-11 17:07:12 in reply to 2 [link] [source]

Thank you Richard, this explains things!

A note to benefit future readers:

SQLite seems to report the missing column type by returning SQLITE_NULL, which is subsequently turned into NUMERIC by the JDBC driver (for reasons unknown to me).

Thanks again,

Simon