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.
For these examples let's create a couple of tables we might need for examples
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.
It's possible to do the following to make this even cleaner:
Then you can write
There are no boolean literals in the language, but it's very easily to create one
CAST(0 AS BOOL) is a pretty verbose way to make a bool constant. Much easier would be
but even that leaves an expression in the generated code. We can do better still with
which forces the expression to be evaluated at compile time. This leads us to the usual definitions
which are preferred.
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
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:
But this is simply
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 is not needed at all so we could go to
NULL is already the default value for the
ELSE clause so you never need
Filtering out NULLs
There's no need to test for
NOT NULL here, the boolean will result in
foo.name is null
which is not true so the
WHERE test will fail.
Not null boolean expressions
In this statement we do not want to have a null result for the boolean expression
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:
Even without the
TRUE macro you could do
IS 1 above and still get a result of type
BOOL NOT NULL
IS when it makes sense to do so
This kind of boolean expression is also verbose for no reason
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.
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 IS NULL is true.
NULL IS [anything not null] is false.
Left joins that are not left joins
This is no longer a left join because the
WHERE clause demands a value for at least one column from