DISTINCT(X) is considered a function by vtgate

[aquarapid]

Schema and vSchema:

$ cat schema.sql 
create table foo( c1 bigint not null, PRIMARY KEY (c1)) engine=innodb;

$ cat vschema.json 
{
    "ks1": {
        "sharded": true,
        "vindexes": {
            "hash": {
                "type": "hash"
            }
        },
        "tables": {
            "foo": {
                "columnVindexes": [
                    {
                        "column": "c1",
                        "name": "hash"
                    }
                ]
            }
        }
    }
}

Now:

$ vtexplain -schema-file schema.sql -vschema-file vschema.json -shards 2 -sql 'select distinct(c1) from foo;'
ERROR: vtexplain execute error in 'select distinct(c1) from foo': generating order by clause: cannot reference a complex expression

Note the distinct with parentheses; which is valid synonym for distinct without parentheses. Without them, the query works as expected:

$ vtexplain -schema-file schema.sql -vschema-file vschema.json -shards 2 -sql 'select distinct c1 from foo;'
----------------------------------------------------------------------
select distinct c1 from foo

1 ks1/-80: select distinct c1 from foo limit 10001
1 ks1/80-: select distinct c1 from foo limit 10001

----------------------------------------------------------------------

The same happens when using DISTINCTROW instead of DISTINCT too, of course.

The parser doesn't recognize that they are equivalent, and thinks DISTINCT with parentheses is a SQL function to be pushed down, bypassing the special planbuilder handling for DISTINCT.

[sougou]

This is unfortunate:

mysql> select * from a;
+----+------+
| id | val  |
+----+------+
|  1 |    1 |
|  2 |    1 |
+----+------+
2 rows in set (0.00 sec)

mysql> select distinct(val) from a;
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select distinct(val), id from a;
+------+----+
| val  | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
+------+----+
2 rows in set (0.00 sec)

mysql> select distinct val, id from a;
+------+----+
| val  | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
+------+----+
2 rows in set (0.00 sec)

mysql> select distinct (val, id) from a;
ERROR 1241 (21000): Operand should contain 1 column(s)

My interpretation for distinct as a function is:

• It takes only one argument.
• If there are other expressions in the select, then it's a no-op.
• If it's the only argument in the select, and it's the distinct function, then it's equivalent to a regular distinct.

@systay: we should amend the parser to convert these to the normalized form.

[systay]

My understanding is that something different is happening.

SELECT (c1) from foo

is a valid query, right? It's just the value from column c1 with parens around it - a perfectly normal expression (albeit complex). Adding DISTINCT before makes it look like a function call, but it's not. You can double check this by trying something like:

SELECT c2, distinct(c1) from foo

This is not accepted - DISTINCT must come before the first expression in the SELECT clause.

We could (and maybe will) do more expression rewriting and remove unnecessary parens like this one, but we don't at the moment, and that why this doesn't work.

[systay]

This should be solved by #5906