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

There are no boolean literals in the language, but it's very easily to create one

Poor:

SELECT
foo.id,
foo.name,
NULL_REAL as rate,
CAST(1 AS BOOL) as has_name, -- this is a bit artificial but you get the idea
CAST(0 AS BOOL) as has_rate
FROM foo
UNION ALL
SELECT
bar.id,
NULL_TEXT as name,
bar.rate,
CAST(0 AS BOOL) as has_name,
CAST(1 AS BOOL) as has_rate
FROM bar

Actually CAST(0 AS BOOL) is a pretty verbose way to make a bool constant. Much easier would be 0==1 but even that leaves an expression in the generated code. We can do better still with const(0==1) which forces the expression to be evaluated at compile time. This leads us to the usual definitions which are preferred.

-- somewhere common
#define TRUE const(1==1)
#define FALSE const(1==0)

This gives you the most economy, the code gen will be just 0/1. 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;
Last updated on by Winnie Quinn