You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Bosiang Huang <bo...@gmail.com> on 2025/11/19 08:44:39 UTC

SqlAdvisor column suggestions in GROUP BY — expected behavior?

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

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

Posted by Bosiang Huang <bo...@gmail.com>.
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 <jh...@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 <bo...@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
>

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

Posted by Julian Hyde <jh...@gmail.com>.
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 <bo...@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