Fetching structs containing datetimes fails silently

[jnj16180340]

In this step,

databricks-sql-nodejs/lib/result/JsonResult.ts

Line 121 in c99dfd1

private toJSON(value: any, defaultValue: any): any {

databricks-sql-nodejs/lib/result/JsonResult.ts

Line 89 in c99dfd1

case TTypeId.STRUCT_TYPE:

If the data being parsed contains datetimes, it fails and returns defaultValue. Example error and data are below:

SyntaxError: Unexpected number in JSON at position 39
    at JSON.parse (<anonymous>)
    at JsonResult.toJSON (./databricks-sql-nodejs/dist/result/JsonResult.js:93:25)
    at JsonResult.convertData (./databricks-sql-nodejs/dist/result/JsonResult.js:64:29)
    at ./databricks-sql-nodejs/dist/result/JsonResult.js:47:25
    at Array.map (<anonymous>)
    at JsonResult.getSchemaValues (./databricks-sql-nodejs/dist/result/JsonResult.js:43:35)
    at ./databricks-sql-nodejs/dist/result/JsonResult.js:27:62
    at Array.reduce (<anonymous>)
    at JsonResult.getRows (./databricks-sql-nodejs/dist/result/JsonResult.js:27:28)
    at ./databricks-sql-nodejs/dist/result/JsonResult.js:16:31

The string that's failing to parse is below (partially scrubbed)
'{"id":414247,"created_at":2021-12-21 21:33:59.339,"updated_at":2021-12-21 21:33:59.339,"deleted_at":null,"s3_bucket":"thebucket","s3_key":"c411f24d-1b4a-4eb0-b25b-d2287c7ba3c0"}'

Also, would it make sense to at least log a warning if parsing fails and returns the default value?

[kravets-levko]

Hi @jnj16180340! First of all, thank you for reporting this issue, and also I really appreciate all the additional info you provided. I can see that json is definitely malformed, and it probably is some bug. I need to investigate it, but I'm pretty sure that bug is on backend. And I totally agree that library should emit some warning in such cases, that's what we'll definitely implement. I'll keep you posted if any news on this issue. Sorry for the inconvenience

[jnj16180340]

Thanks @kravets-levko - Still working on understanding everything going on under the hood, however if "backend" means the remote SQL Warehouse it seems unlikely since the JDBC interface and python databricks-sql both handle structs+arrays without issue

[kravets-levko]

Thank you @jnj16180340, that's very important update!

[kravets-levko]

@jnj16180340 sorry for being silent for so long. A very quick update re. your issue. It looks that there is some bug with serializing complex structures, we're trying to figure it out. JDBC and python drivers use Apache Arrow to represent query results by default and very likely that's why they work (complex structures are handled by Arrow and not represented as JSON). Node driver uses JSON-based results format and that's why it is affected by this bug. Arrow support for Node driver is currently in progress (/#94) - I checked your case on that branch and can confirm that with Arrow support enabled it works fine. I'll keep you posted once I have more news. Sorry for the inconvenience