Can new database connector handle "complex" SQL?

Help
1

I’m not seeing results for complex queries which do get results on “old.obser…”, for example something like:

const foo = display(await tsdb.sql`
WITH ram_lttb AS (
      SELECT
        panel_id,
        device_id,
        position,
        (unnest(lttb(time, ram, 60))).time  AS time,
        (unnest(lttb(time, ram, 60))).value AS ram
      FROM data_table
      WHERE time > NOW() - INTERVAL '${hours} hours'
        AND gateway_id = '${gateway}'
      GROUP BY panel_id, device_id, position
    )
    SELECT
      r.panel_id,
      r.position AS "position_number",
      r.time,
      r.ram,
      s.ram_min
    FROM ram_lttb r
    JOIN state_table s
      ON s.time = r.time
     AND s.panel_id = r.panel_id
     AND s.device_id = r.device_id
    WHERE s.time > NOW() - INTERVAL '${hours} hours'
      AND s.gateway_id = '${gateway}'
    ORDER BY
      r.panel_id ASC,
      r.position ASC,
      r.time DESC
`)
2

Tried using a plain sql cell, too. No joy.

3

Hm. At a glance I see no reason why it shouldn’t work in New. It’s possible that it’s just loading slowly…? One difference is that Old streamed results, and New doesn’t, so for especially big slow queries you might get it all at once later…?

4

It seems to “complete” but it’s empty. In “old” it gives me about 900 rows in about 1 second.

5

BTW, it’s not really clear from my example, but I’m using Postgres+TimescaleDB

UPDATE: Is seems as though timescale-specific things should be safely passed right through ADBC

6

OK, upon closer inspection, the way you’re interpolating variables there won’t work with the sql tagged template literal. The exact solution varies a bit because different database drivers allow different sorts of parameterization.

In Postgres, for the gateway_id string, you should do it without quotes around the parameterized value; for the interval, you have to multiply a string constant by the parameterized value:

DatabaseClient("postgres_db").sql`SELECT * FROM data_table
  WHERE time > NOW() - (INTERVAL '1 hour' * ${hours})
  AND gateway_id = ${gateway}`

In Snowflake, it’d be a little different, because it uses CURRENT_TIMESTAMP instead of NOW and doesn’t seem to allow that parameterized multiplication trick in INTERVALs. But it has another function, DATEADD, which works:

DatabaseClient("snowflake_db").sql`SELECT * FROM data_table 
  WHERE time > DATEADD(hour, ${-hours}, CURRENT_TIMESTAMP())
  AND gateway_id = ${gateway}`

Or, you can get sneaky and hacky and “opt out” of how we safely handle interpolation:

DatabaseClient("snowflake_db").sql`SELECT * FROM DIM_USERS WHERE create_time > CURRENT_TIMESTAMP - INTERVAL ${sql([`'${hours} hours'`])} LIMIT 10`

We also just shipped backwards compatibility for the query method (if you have the 2018 standard library selected in the :package: dependencies sidebar), which lets you do good old-fashioned injection-unsafe string-building, like you were doing before:

DatabaseClient("snowflake_db").query(`SELECT * FROM DIM_USERS WHERE create_time > CURRENT_TIMESTAMP - INTERVAL '${hours} hours' LIMIT 10`)

Does that fix it?

7

For further background, which you may or may not need, but which might help someone else…

Both Old and New have a db.sql method. As of this morning’s backwards compatibility update, both Old and New also have a db.query method. (In New, it’s only available if the notebook is set to use the 2018 standard library.) They behave similarly for the simplest queries, but the differences become important with complexity.

With query you query the database like this:

db.query("SELECT 1")

With sql you query the database like this:

db.sql`SELECT 1`

So far, those two queries will behave identically.

With query, you are passing a plain old string to the database as the query, and it would behave the same however you constructed the string, e.g. with backticks:

db.query(`SELECT 1`)

And it’d behave the same if you built the string by interpolating something into it, like this:

db.query(`SELECT ${number}`)

That would still just run the query SELECT 1 (assuming number is 1).

But with sql, if you do this:

db.sql`SELECT ${number}`

It is actually constructing a parameterized query string, SELECT ?, and then separately passing the parameter 1 directly to the database driver, which substitutes the 1 for the ? to produce SELECT 1, and you get the same results.

The new way is safer, because if the value of your variable number somehow became something like 1; DROP TABLE table_name;, the injection attack wouldn’t work.

And the new way can be more convenient, because you don’t have to put quotes around your strings or format your arrays to pass them in.

The problem is, you can’t put those question-mark parameters everywhere you might build up a string. They can only represent complete values.

With query, you could do something “crazy” like this:

db.query(`SELECT ${Math.random() > 0.5 ? "GREATEST" : "LEAST"}(1, 2)`)

Or even this:

db.query(`SEL${"ECT"} 1`)

But with sql, those are syntax errors. You can’t put ?s there! It ends up being like ?(1, 2) or SEL? 1, which indeed look like syntax errors. The question mark can only stand for a value.

There may be other database-specific limitations. For example, on Snowflake, it seems an INTERVAL cannot be dynamically constructed by a parameter. You certainly can’t do INTERVAL '${hours} hours', because that string would become '? hours', and that ? would not be replaced, it would just be nonsense, an invalid interval, a syntax error. But it looks like you can’t even do INTERVAL ${hoursString}, where hoursString is '6 hours', for whatever reason. Hence, the suggestion above to use DATEADD instead.

One “escape hatch” is that you can “cheat” and interpolate another sql fragment you’ve constructed by passing a raw string, like this:

db.sql`SELECT CURRENT_TIMESTAMP() - INTERVAL ${sql([`'${hours} hours'`])}`

That works because, behind the scenes, tagged template literals are just regular JavaScript functions with particular parameters, and nested sql fragments get flattened by the sql method. But that’s a hacky ugly confusing workaround that we wouldn’t really recommend. But maybe it helps elucidate what’s happening.

8

Great write-up @tophtucker!

You can shorten the last (unsafe interpolation) example even further if desired:

db.sql`SELECT CURRENT_TIMESTAMP() - INTERVAL '${sql([hours])} hours'`
9

Thanks @tophtucker and @mbostock… this is much closer to working.

Some data, though, comes in significantly differently:

  • UUID string is now a Uint8Array(16)
  • Portion of a different UUID string is now a Uint8Array(4)

There may be others… just the first I saw today which made my faceted plots become “infinitely” vertical. :slight_smile:

10

Glad things are getting better and sorry again for the continued trouble.

I don’t think we’ll be able to change the behavior of the database driver to implicitly convert UUID to TEXT. You’ll likely need to add a ::TEXT cast when you select a UUID in order to explicitly convert it to a string. For example:

SELECT gen_random_uuid()::text

Hope this helps.