Re: SqlAdvisor column suggestions in GROUP BY — expected behavior?

[Bosiang Huang]

Hi Julian,

Thanks for responding. I will create a bug ticket with more details.

Thanks,
Bosiang

On Wed, Nov 19, 2025 at 8:42 AM Julian Hyde <jhyde.apa...@gmail.com> wrote:

> It sounds like you’re hitting a bug in SqlAdvisor. It seems reasonable to
> me that the same columns are suggested in GROUP BY as are suggested in
> WHERE. You should log it.
>
> Julian
>
> > On Nov 19, 2025, at 12:49 AM, Bosiang Huang <bosian...@gmail.com> wrote:
> >
> > Hi Calcite developers,
> >
> > I’m experimenting with SqlAdvisor to provide SQL autocompletion hints and
> > noticed something I’m not sure about.
> >
> > With a simple in-memory table:
> >
> > SchemaPlus rootSchema = calciteConnection.getRootSchema();
> >
> > rootSchema.add("PEOPLE", new PeopleTable());
> >
> >
> >   - Column suggestions *work* in WHERE clauses:
> >
> >
> >
> > SELECT * FROM PEOPLE WHERE NA
> >
> > -- Suggests: NAME
> >
> >
> >   -  Column suggestions *work* in ORDER BY clauses:
> >
> >
> >
> >   SELECT * FROM PEOPLE ORDER BY NA
> >
> >   -- Suggests: NAME
> >
> >
> >   -  Column suggestions *do NOT appear* in GROUP BY clauses:
> >
> >
> >
> >   SELECT count(AGE) FROM PEOPLE GROUP BY N
> >
> >   -- Only suggests SQL keywords, not column names
> >
> > I’ve tried:
> >
> >   - Using SqlValidator.Config with withIdentifierExpansion(true)
> >   - Constructing CalciteCatalogReader with the root schema
> >
> > but SqlAdvisor still doesn’t suggest columns in GROUP BY positions.
> >
> > *Questions:*
> >
> >   1. Is this behavior *by design*, i.e., SqlAdvisor does not provide
> >   column suggestions in GROUP BY clauses?
> >   2. If it’s expected, is there a recommended way to get column
> >   suggestions in GROUP BY?
> >
> > I can provide a minimal Java example if needed for reference.
> >
> > Thanks for any guidance!
> >
> > Best regards,
> > Bosiang Huang
>