DISTINCT does not work when sorting by additional columns

[nuno-faria]

Describe the bug
Performing a DISTINCT results in duplicate rows if we sort the table by additional columns than the ones that are projected. This is not critical as the additional columns can be removed from the sort, however it can cause problems with dynamically generated queries (which is how this bug was discovered).

To Reproduce

• Create a table and populate with some data:

  CREATE TABLE T (t1 int, t2 int);
  INSERT INTO t VALUES (1, 1), (1, 2);

• DISTINCT with single sort column works as expected:

  SELECT DISTINCT t1
  FROM T 
  ORDER BY t1;
  
  Returns:
  +------+
  | t1   |
  +======+
  |    1 |
  +------+

• DISTINCT when sorting by both columns returns repeated rows:

  SELECT DISTINCT t1
  FROM T
  ORDER BY t1, t2;
  
  Returns:
  +------+
  | t1   |
  +======+
  |    1 |
  |    1 |
  +------+

Expected behavior
Return just one row with value 1.

Software versions

• monetdb -v: MonetDB Database Server Toolkit v11.41.11 (Jul2021-SP1).
• OS: Ubuntu 20.04.3 LTS;
• Monetdb installed from release packages with apt (packages monetdb5-sql and monetdb-client);

[PedroTadim]

The order by clause should be computed after the distinct, so the second query should fail to compile. I have a fix in mind.

[PedroTadim]

Because this is a behavior change, I fixed on Jan2022 branch and it will be available on the next feature release.

[nuno-faria]

Thanks, can confirm that the second query does not compile anymore in the Jan22 branch.