Comment by da_chicken

11 days ago

Yeah, this seemed like a very long way to say, "Our RDBMS has system catalogs," as if it's 1987.

But then, they're also doing JOINs with the USING clause, which seems like one of those things that everybody tries... until they hit one of the several reasons not to use them, and then they go back to the ON clause which is explicit and concrete and works great in all cases.

Personally, I'd like to hear more about the claims made about Snowflake IDs.

6 comments

da_chicken

Reply
zabzonk  10 days ago

> doing JOINs with the USING clause

I'm ashamed to say that despite using SQL from the late 1980s, and as someone that likes reading manuals and text books, I'd never come across USING. Probably a bit late for me now to use it (or not) :-(

  • tkejser  10 days ago

    I didn't really write USING in anger until around 10 years ago, and I have been around a long time too.

    Not all databases support it. But once you start using it (pun) - a lot of naming conventions snap into place.

    It has some funky semantics you should be aware of. Consider this:

      CREATE TABLE foo (x INT);

  CREATE TABLE bar (x INT);

  SELECT \* FROM foo JOIN bar USING (x);

    There is only one `x` in the above `SELECT *` - the automatically disambiguated one. Which is typically want you want.

  • n4r9  10 days ago

    I've used SQL for around a decade and also never came across it. I'm maintaining SQL code with hundreds if not thousands of basic primary key joins and this could make those queries way more concise. Now I want to know the reasons for not using USING!

    • tkejser  10 days ago

      There are reasons for not USING.

      First, you need to be aware of the implicit disambiguration. When you join with USING, you are introducing a hidden column that represents both sides. This is typically what you want - but it can bite you.

      Consider this PostgreSQL example:

        CREATE TABLE foo (x INT);
  INSERT INTO foo VALUeS (1);

  CREATE TABLE bar (x FLOAT);
  INSERT INTO bar VALUES (1);

  SELECT pg_typeof(x) FROM foo JOIN bar USING (x);

      The type of x is is double, - because x was implicitly upcast as we can see with EXPLAIN:

        Merge Join  (cost=338.29..931.54 rows=28815 width=4)
    Merge Cond: (bar.x = ((foo.x)::double precision))

      Arguably, you should never be joining on keys of different types. It just bad design. But you don't always get that choice if someone else made the data model for you.

      It also means that this actually works:

        CREATE TABLE foo (x INT);
  INSERT INTO foo VALUeS (1);

  CREATE TABLE bar (x INT);
  INSERT INTO bar VALUES (1);

  CREATE TABLE baz (x INT);
  INSERT INTO baz VALUES (1);

  SELECT \*
  FROM foo
  JOIN bar USING (x)
  JOIN baz USING (x);

      Which might not be what you expected :-)

      If you are both the data modeller and the query writer - I have not been able to come up with a reason for not USING.

      1 reply →

tkejser  10 days ago

@da_chicken: You can read more about Snowflake ID in the Wiki page linked in the article.

The short story:

They are bit like UUID in that you can generate them across a system in a distributed way without coordination. Unlike UUID they are only 64-bit.

The first bits of the snowflake ID are structured in such a way that the values end up roughly sequentially ordered on disk. That makes them great for large tables where you need to locate specific values (such a those that store query information).