The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,953 other followers

SQL comma bullet point formatting: because AS is optional

Posted by jpluimers on 2022/11/03

Do you see the error below?

(note: OCR via [Wayback/Archive.is] Best Free OCR API, Online OCR, Searchable PDF – Fresh 2021 OCR Software)

SELECT
  license date
  expiration_date,
  renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'
expiration_date renewal_ due date
1 2016-04-08 09:50:00 [NULL]
2 2013-11-14 11:15:00 [NULL]
3 2014-11-20 14:51:00 [NULL]
4 2017-07-21 16:00:00 [NULL]
5 2018-12-17 14:37:46 2020-12-17 14:37:46

All of the expiration_date columns have values, which is contrary to the WHERE clause. This is because the table itself contains an expiration_date column, and the SELECT part aliases license_date into expiration_date.

The result is that you see rows that have expiration_date being NULL, but license_date having a value.

So I totally agree with [Archive.is] Mathias Magnusson on Twitter: “That is one reason I’m a form believer in comma bullet point in my SQL. Problems like that has bit me far too often due to som issue with commas.… “

This is how the SQL should have looked:

SELECT
  license date
, expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

Yes indeed: an alias of a column without the AS keyword is allowed in quite a few SQL dialects (they differ even more widely in SQL extensions like SQL/PSM, T-SQL, PL/SQL, SQL_PL, or ABAB).

Aliases are for output, cannot be used in WHERE (but can in ORDER BY).

You can see what happens (and how hard this can become on one line) with these two dbfiddle queries running on Microsoft SQL Server 2019 dialect (though it works similar in other dialects):

  1. select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    b c
    7 6
    8 4
    9 2

    It shows only the column names a and b, but note the table itself is aliased to t above as well.

  2. select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    a b c
    9 1 2
    8 3 4
    7 5 6

    It shows only the column names a, b and c, but note the table itself is aliased to t above as well.

I really wish various SQL dialects would force the SQL syntax to be (together with a hint that the alias would overwrite an existing field):

SELECT
  license date AS expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

That is not going to happen, so the second best is to wish for tooling to hint/warn about it, and provide better syntax highlighting for it. That seems work in progress by now:

–jeroen


 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: