[
https://issues.apache.org/jira/browse/CALCITE-2439?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16573830#comment-16573830
]
Vladimir Sitnikov commented on CALCITE-2439:
--------------------------------------------
{quote}getMatchRelevance as you have described it is not a pure function. It
somehow needs to know the SQL text that was passed to getHintsToFilter
{quote}
Why does it need to know the SQL?
The idea was:
{{getHintsNoFilter}} produces tuples like {{(id, names, type, match)}}.
Currently it produces all of that but {{match}}
{code:java}
public class SqlAdvisorHint {
/** Fully qualified object name as string. */
public final String id;
/** Fully qualified object name as array of names. */
public final String[] names;
/** One of {@link org.apache.calcite.sql.validate.SqlMonikerType}. */
public final String type;
{code}
{{match}} should be something that describes the part of original SQL that
Advisor suggests to replace.
Apparently simple String in not enough, and something more complicated is
required to describe that "advisor suggest to replace 'e' with
'catalog.sales.emp' table name, however 'catalog.sales.' part is already
present in original SQL". That is {{match}} could be {{(String, int)}} or
something more complicated. As you say, it might be json-like object and it
might even include original SQL, however I just think full SQL is not required
there.
That is why {{getMatchRelevance}} would be a pure function, and it could assess
how well given match suits for given replacement. I'm sure it is in line with
SQL.
{quote}I think when offering a completion for "select * from sales.e^" it
should suggest "emp" as a completion and "e" as the thing that is being
replaced by the completion. That is, every completion is a pair (replaced,
replacement).
{quote}
1) I'm sure "fully qualified identifier" + "type" is important to display the
proposal in the UI (draw proper icon and so on)
2) Replacement text might depend on user preferences. For instance: {{select
na^ from sales.emp e}}. Suppose the completion is {{name}}. Should it be
quoted? Should it be converted to upper case? Should it be prefixed with alias
like {{e.name}}?
Do you think Calcite should provide answers for all those?
I can imagine "sane" defaults like:
2.1) Quote the identifier if original string started with quote or if the
replacement needs quotes to be parsed properly
2.2) Use when source string is "all lowercase", then try use "all lowercase"
for replacement. When source string was "all uppercase", then use upper. E.g.
{{sele^ ==> select}}, but {{SELE^ ==> SELECT}}
2.3) Always prefix identifiers with alias when possible
> Smart complete for SqlAdvisor
> -----------------------------
>
> Key: CALCITE-2439
> URL: https://issues.apache.org/jira/browse/CALCITE-2439
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.17.0
> Reporter: Vladimir Sitnikov
> Assignee: Julian Hyde
> Priority: Major
>
> Current implementation of SqlAdvisor provides no way to perform smart
> complete.
> For example,
> 1) A valid completion for {{select cd^ from clients}} might be {{select
> client_id from clients}}.
> That is completion is valid if all input characters are represented in final
> word in a proper sequence.
> 2) Completion might be case-insensitive if all input characters have the same
> upper/lower case.
> 3) "Contains" might be valid completion option as well. That is {{select id^
> from ...}} might be completed to \{{select client_id from...} as well.
> Of course, exact match should be sorted the first, then partial matches and
> so on.
> It is not clear if smart complete logic belongs to {{SqlAdvisor}} or not.
> Current client-facing API provides no way to skip default "case-sensitive
> prefix filtering", so there's no way to implement smart complete at the
> client side only.
> It is not clear where this logic belongs:
> a) Ultimate solution would be "skip filtering the identifiers at SqlAdvisor
> side". Then client can filter and sort the way it wants. The downside of the
> approach is it would force client to pipe large amount of items across JDBC
> bridge
> b) It might be helpful if Calcite had pre-defined implementations that would
> filter and sort the results. The good part is it simplifies client
> development, however various clients might have various filters applied.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
