SQL: selecting a literal from grouped by query generates error

[astefan]

sql> select 1 from test_emp group by gender;
Bad request [Found 1 problem(s)
line 1:33: Unknown column [gender], did you mean [gender]?]

[elasticmachine]

Pinging @elastic/es-search

[codebird]

I was thinking of a solution like this:

https://github.com/elastic/elasticsearch/compare/master...codebird:issue%2341413?expand=1

Logic behind it, is that if all we're selecting are literals, then resolved should become all acceptable fields in our field list. Now for some reason, this is considering the group by field as a literal too. so this:

select 1 from testing group by name

is being considered like this:

select 1 from testing group by 'name'

So I wanted to get some input on my approach before going forward.

[matriv]

@astefan this is similar to #34583, Isn't it? Should we close the old one as a duplicate?

[astefan]

@codebird thank you for looking at this. We haven't yet started looking at the issue or the proposed PR.

Not sure @matriv, but I'd be cautious and keep both. There are other issues around literals in different scenarios, as well.

[matriv]

The status of such queries has been changed. No error is thrown but instead only one row is returned, independently of the buckets that the group by would return, since they are wrongly translated to LocalExec plans.

[codebird]

my pull request solves this issue, but for some reason it has been forgotten.

[matriv]

Hi @codebirg,

Apologies for the long delay, but we didn't forget your PR. We wanted to wait for the refactoring merged with: #49570 which simplifies the code in many aspects, so that we could address such issues in a less "hacky" way.

[codebird]

I'll merge into my branch and check it out @matriv will try to check it out this week

[matriv]

Thank you @codebird !

[codebird]

@matriv all issue were already fixed, except #41951, and aliasing a literal

Ex:
SELECT COUNT(*) as c FROM test WHERE ABS(int) > 0 GROUP BY int
SELECT 1 as t FROM test GROUP BY int

I updated my pull request accordingly...

[matriv]

@codebird
SELECT 1 as t FROM test GROUP BY int
is not fixed. It doesn't throw an Exception but it returns only one row, where it should return one row per group (created by values of int in this case)

[codebird]

thank you for looking into it @matriv, this slipped through I had it fixed this time, seems I forgot to move some of the code. I'll fix it today and start working on tests.

[codebird]

@matriv this issue SELECT 1 as t FROM test GROUP BY int is also on this query SELECT 1 FROM test GROUP BY int, I am still trying to figure out, but notice that both queries return the same result. SELECT 1 FROM test GROUP BY int <- this is currently on master.

[codebird]

the issue with these 2 queries was fixed. If you can check out my solution. I'll go on with writing the tests.

[matriv]

master : 45b8580
7.x : 5b32d11
7.6 : 967f702