Allow positional arguments for group by, order by, limit by#27530
Allow positional arguments for group by, order by, limit by#27530kssenii merged 13 commits intoClickHouse:masterfrom
Conversation
robot-clickhouse
added
doc-alert
pr-feature
…ckHouse into col-identifier-as-col-number
…o col-identifier-as-col-number
|
I think there are some small problems. avogar-dev.sas.yp-c.yandex.net :) create table test(x1 Int, x2 Int, x3 Int) engine=Memory()
CREATE TABLE test
(
`x1` Int,
`x2` Int,
`x3` Int
)
ENGINE = Memory
Query id: 21f966b1-65fa-4e29-bb10-cb66a40bb100
Ok.
0 rows in set. Elapsed: 0.023 sec.
avogar-dev.sas.yp-c.yandex.net :) insert into test values (1, 10, 100), (10, 1, 10), (100, 100, 1)
INSERT INTO test VALUES
Query id: af086b77-1dd0-4050-9d03-0d27eff98f98
Ok.
2 rows in set. Elapsed: 0.001 sec.avogar-dev.sas.yp-c.yandex.net :) select max(x1) from test order by x1
SELECT max(x1)
FROM test
ORDER BY x1 ASC
Query id: 07b7d229-6ac3-4dbf-9b82-3b3647aca506
0 rows in set. Elapsed: 0.002 sec.
Received exception from server (version 21.9.1):
Code: 215. DB::Exception: Received from localhost:9000. DB::Exception: Column `x1` is not under aggregate function and not in GROUP BY: While processing x1 ASC. (NOT_AN_AGGREGATE)
avogar-dev.sas.yp-c.yandex.net :) select max(x1) from test order by 1
SELECT max(x1)
FROM test
ORDER BY 1 ASC
Query id: fcaf420e-8c39-4e92-abc9-3350d218ccfc
0 rows in set. Elapsed: 0.002 sec.
Received exception from server (version 21.9.1):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown column: x1, there are only columns max(x1), 1. (UNKNOWN_IDENTIFIER)Exception messages are differ, but maybe it's not important. avogar-dev.sas.yp-c.yandex.net :) select max(x1), max(x2), max(x3) from test group by x1
SELECT
max(x1),
max(x2),
max(x3)
FROM test
GROUP BY x1
Query id: fa5cee5d-91fe-4187-bbe1-ce44a2ac85d3
┌──x1─┬─max(x2)─┬─max(x3)─┐
│ 1 │ 10 │ 100 │
│ 100 │ 100 │ 1 │
│ 10 │ 1 │ 10 │
└─────┴─────────┴─────────┘
3 rows in set. Elapsed: 0.002 sec.
avogar-dev.sas.yp-c.yandex.net :) select max(x1), max(x2), max(x3) from test group by 1
SELECT
max(x1),
max(x2),
max(x3)
FROM test
GROUP BY 1
Query id: 857830c3-7b0b-4608-83bd-7c3699df54ab
┌─max(x1)─┬─max(x2)─┬─max(x3)─┐
│ 1 │ 10 │ 100 │
│ 100 │ 100 │ 1 │
│ 10 │ 1 │ 10 │
└─────────┴─────────┴─────────┘
3 rows in set. Elapsed: 0.002 sec.The outputs look a bit differ. avogar-dev.sas.yp-c.yandex.net :) select x3, x2, x1 from test order by 1
SELECT
x3,
x2,
x1
FROM test
ORDER BY 1 ASC
Query id: 1721f158-2935-4b3f-bf99-7c52c2d4c133
┌──x3─┬──x2─┬──x1─┐
│ 100 │ 10 │ 1 │
│ 10 │ 1 │ 10 │
│ 1 │ 100 │ 100 │
└─────┴─────┴─────┘
3 rows in set. Elapsed: 0.002 sec.
avogar-dev.sas.yp-c.yandex.net :) select x3, x2, x1 from test order by x3
SELECT
x3,
x2,
x1
FROM test
ORDER BY x3 ASC
Query id: 8916cb21-b43e-4605-a916-92e95eb64f6e
┌──x3─┬──x2─┬──x1─┐
│ 1 │ 100 │ 100 │
│ 10 │ 1 │ 10 │
│ 100 │ 10 │ 1 │
└─────┴─────┴─────┘
3 rows in set. Elapsed: 0.002 sec. If I understand correctly, these queries should be the same, because we should use relative index of column from select list (https://riptutorial.com/sql/example/3080/sorting-by-column-number--instead-of-name-). avogar-dev.sas.yp-c.yandex.net :) select x3, x2 from test order by 1
SELECT
x3,
x2
FROM test
ORDER BY 1 ASC
Query id: dab55d95-6172-48f7-9d45-6cc8328d9b2f
0 rows in set. Elapsed: 0.001 sec.
Received exception from server (version 21.9.1):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown column: x1, there are only columns x2, x3, 1. (UNKNOWN_IDENTIFIER)I would expect that this query will be the same as |
|
As I can see, in SQL we shoud use relative index except case when we use CREATE TABLE test (x1 INTEGER, x2 INTEGER);
INSERT INTO test VALUES (1, 2), (2, 1);
select x2, x1 from test order by 1; |
|
psql> select max(x1), max(x2), max(x3) from test group by x1;
max | max | max
-----+-----+-----
1 | 10 | 100
10 | 1 | 10
100 | 100 | 1
psql> select max(x1), max(x2), max(x3) from test group by 1;
ERROR: aggregate functions are not allowed in GROUP BY
LINE 1: select max(x1), max(x2), max(x3) from test group by 1
^
:) select max(x1), max(x2), max(x3) from test group by max(x1);
Code: 46. DB::Exception: Received from localhost:9000. DB::Exception: Unknown function max. There is an aggregate function with the same name, but ordinary function is expected here. Maybe you meant: ['map']. (UNKNOWN_FUNCTION) |
…o col-identifier-as-col-number
…o col-identifier-as-col-number
kssenii
force-pushed
the
col-identifier-as-col-number
branch
from
0e95b47 to
ec9323f
Compare
|
@kssenii Let's fix build check and merge it |
kssenii
force-pushed
the
col-identifier-as-col-number
branch
from
bad9273 to
c6f456b
Compare
|
Internal documentation ticket: DOCSUP-13583 |
|
I've updated Clickhouse to the latest version : 21.10.2.15
and i got this error:
It's seem like if i only use the raw column, this'll be fine. But if i need to use some transform for the raw column, i get an error like above. |
|
@dinhthengoc2021998 see comment #27530 (comment). |
|
@kssenii |
|
Can you check our clickhouse tableau connector then? |
|
Someone did attached this connector to server. -> Altinity/tableau-connector-for-clickhouse#2 (comment) |
|
@kssenii I expect that it should work for expressions. Example:
@den-crane only noted that it cannot work if you do GROUP BY by aggregate function. |
|
I see no reason why it should not work... and it's the most relevant use case: avoid typing expression twice. |
|
example, psql (11.12 (Debian 11.12-0+deb10u1), server 9.6.22)
create table test(x1 Int, x2 Int, x3 Int);
insert into test values (1, 10, 100), (10, 1, 10), (100, 100, 1);
select x1, x1 * 2, max(x2), max(x3) from test group by 2, 1, x1 order by 1, 2, 4 desc, 3 asc;
x1 | ?column? | max | max
-----+----------+-----+-----
1 | 2 | 10 | 100
10 | 20 | 1 | 10
100 | 200 | 100 | 1 |
8 participants
I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en
Changelog category (leave one):
Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):
Allow positional arguments under setting
enable_positional_arguments. Closes #2592.