Appendix 7: CQL Anti-patterns

These are a few of the antipatterns I've seen while travelling through various CQL source files. They are in various categories.

Refer also to Appendix 8: CQL Best Practices.

Common Schema

For these examples let's create a couple of tables we might need for examples

CREATE TABLE foo (
    id integer primary key,
    name text
);

CREATE TABLE bar (
    id integer primary key,
    rate real
);

Declarations

DECLARE v LONG NOT NULL;
SET v := 1;

better

LET v := 1L;  -- long literals have the L suffix like in C

Similarly

DECLARE v REAL NOT NULL;
SET v := 1;

better

LET v := 1.0; -- use scientific notation or add .0 to make a real literal

Casts

Redundant casts fatten the code and don't really add anything to readability. Sometimems it's necessary to cast NULL to a particular type so that you can be sure that generated result set has the right data type, but most of the casts below are not necessary.

  SELECT
    CAST(foo.id as INTEGER) as id,
    CAST(foo.name as TEXT) as name,
    CAST(NULL as REAL) as rate
  FROM foo
UNION ALL
  SELECT
    CAST(bar.id as INTEGER) as id,
    CAST(NULL as TEXT) as name,
    CAST(bar.rate as REAL) as rate
  FROM bar

Better

  SELECT
    foo.id,
    foo.name,
    CAST(NULL as REAL) as rate
  FROM foo
UNION ALL
  SELECT
    bar.id,
    CAST(NULL as TEXT) as name,
    bar.rate
  FROM bar

It's possible to do the following to make this even cleaner:

-- somewhere central
#define NULL_TEXT CAST(NULL as TEXT)
#define NULL_REAL CAST(NULL as REAL)
#define NULL_INT CAST(NULL as INTEGER)
#define NULL_LONG CAST(NULL as LONG)

Then you can write

  SELECT
    foo.id,
    foo.name,
    NULL_REAL as rate
  FROM foo
UNION ALL
  SELECT
    bar.id,
    NULL_TEXT as name,
    bar.rate
  FROM bar

Booleans

TRUE and FALSE can be used as boolean literals.

SQLite doesn't care about the type but CQL will get the type information it needs to make the columns of type BOOL

  SELECT
    foo.id,
    foo.name,
    NULL_REAL as rate,
    TRUE as has_name,  -- this is a bit artificial but you get the idea
    FALSE as has_rate
  FROM foo
UNION ALL
  SELECT
    bar.id,
    NULL_TEXT as name,
    bar.rate,
    FALSE as has_name,
    TRUE as has_rate
  FROM bar

Boolean expressions and CASE/WHEN

It's easy to get carried away with the power of CASE expressions, I've seen this kind of thing:

CAST(CASE WHEN foo.name IS NULL THEN 0 ELSE 1 END AS BOOL)

But this is simply

foo.name IS NOT NULL

In general, if your case alternates are booleans a direct boolean expression would have served you better.

CASE and CAST and NULL

Somtimes there's clamping or filtering going on in a case statement

CAST(CASE WHEN foo.name > 'm' THEN foo.name ELSE NULL END AS TEXT)

Here the CAST is not needed at all so we could go to

CASE WHEN foo.name > 'm' THEN foo.name ELSE NULL END

NULL is already the default value for the ELSE clause so you never need ELSE NULL

So better:

CASE WHEN foo.name > 'm' THEN foo.name END

Filtering out NULLs

Consider

SELECT *
    FROM foo
    WHERE foo.name IS NOT NULL AND foo.name > 'm';

There's no need to test for NOT NULL here, the boolean will result in NULL if foo.name is null which is not true so the WHERE test will fail.

Better:

SELECT *
    FROM foo
    WHERE foo.name > 'm';

Not null boolean expressions

In this statement we do not want to have a null result for the boolean expression

SELECT
    id,
    name,
    CAST(IFNULL(name > 'm', 0) AS BOOL) AS name_bigger_than_m
    FROM FOO;

So now we've made several mistakes. We could have used the usual FALSE defintion to avoid the cast. But even that would have left us with an IFNULL that's harder to read. Here's a much simpler formulation:

SELECT
    id,
    name,
    name > 'm' IS TRUE AS name_bigger_than_m
    FROM FOO;

Even without the TRUE macro you could do IS 1 above and still get a result of type BOOL NOT NULL

Using IS when it makes sense to do so

This kind of boolean expression is also verbose for no reason

    rate IS NOT NULL AND rate = 20

In a WHERE clause probably rate = 20 suffices but even if you really need a NOT NULL BOOL result the expression above is exactly what the IS operator is for. e.g.

    rate IS 20

The IS operator is frequently avoided except for IS NULL and IS NOT NULL but it's a general equality operator with the added semantic that it never returns NULL. NULL IS NULL is true. NULL IS [anything not null] is false.

Left joins that are not left joins

Consider

  SELECT foo.id,
         foo.name,
         bar.rate
  FROM foo
  LEFT JOIN bar ON foo.id = bar.id
  WHERE bar.rate > 5;

This is no longer a left join because the WHERE clause demands a value for at least one column from bar.

Better:

  SELECT foo.id,
         foo.name,
         bar.rate
  FROM foo
  INNER JOIN bar ON foo.id = bar.id
  WHERE bar.rate > 5;