MariaDB using ‘distinct’ keyword changes expected behavior of other column

Why does this work:

SET @cnt = 0;
SELECT (@cnt:=@cnt+1) AS 'foo', title FROM employee;

But when I add ‘distinct’ keyword, it does not, while distinct works on its own (not shown).

SET @cnt = 0;
SELECT (@cnt:=@cnt+1) AS 'foo', distinct title FROM employee; // "error in syntax"

In other words, you can have a custom column or a distinct keyword but not both.

I think it’s because each part is trying to return a different number of records for each and that throws an error, but I want it to return the same number of records, the distinct column being the limiter. Please share the keywords and technical terms of what I’m trying to do so I can learn and grow. Also let me know if this should’ve/could’ve gone in the database admin stack exchange.

Answer

This syntax is nonsensical SQL:

SELECT (@cnt:=@cnt+1) AS 'foo', distinct title
FROM employee; // "error in syntax"

Why? Because DISTINCT is used in two places in SQL (that I can readily think of):

• As a modifier to aggregation functions, notably COUNT(DISTINCT).
• As part of the SQL keyword SELECT DISTINCT.

(There are other uses such as IS DISTINCT FROM but MySQL does not support that.)

In the latter case, the DISTINCT applies to all columns being selected. I would recommend putting this in a subquery:

SELECT (@cnt := @cnt + 1) AS foo, title
FROM (SELECT DISTINCT title FROM employee) e; // "error in syntax"