Re: [DISCUSS] Aliased table behaviour SQL standards compliance

[Istvan Toth <st...@apache.org>]

Very belated thanks for your reply, Daniel.

It's taken a long time, but the patch is finally ready for review:

https://issues.apache.org/jira/browse/PHOENIX-6365
https://github.com/apache/phoenix/pull/1133

Istvan

On Wed, Mar 10, 2021 at 10:43 PM Daniel Wong
<da...@salesforce.com.invalid> wrote:

> I'm of the opinion that we should be SQL compliant when possible and only
> if necessary add additional syntax etc for non-compliant behavior.  An
> example of this is we had several use cases that were relying on non-sql
> compliant behavior in our RVC comparisons.  Phoenix-3383 fixed *most*
> non-compliant behavior and I implemented RVC Offset syntax in PHOENIX-4845
> to support this use case.
> For this specific issue at my employer this is not a backwards compatible
> issue as far as I'm aware of.  Maybe we should include user@?
>
> On Wed, Mar 10, 2021 at 12:52 AM Istvan Toth <st...@apache.org> wrote:
>
> > Hi!
> >
> > While working on PHOENIX-6365, I realized that the way we handle aliased
> > tables (correlation names) does not comply with the SQL standard, which
> > states tables that have a correlation name defined, should not be
> > accessible by the original name.
> >
> > In a nutshell, *select table.x from table as alias; *should not work, but
> > it does now.
> >
> > Apart from being no standards-compliant, this is a particular problem for
> > self joins, where queries like
> > *select table.*, alias.* from table, table as alias where table.id
> > <
> >
> https://urldefense.com/v3/__http://table.id__;!!DCbAVzZNrAf4!Rz2ju-oQy_oewfte1yi5m4ZEnsml3tmTcB7mH-tR2IXEfowDtmoxpWC89KOUaDZojv_K$
> > > = 1 and alias.id <
> >
> https://urldefense.com/v3/__http://alias.id__;!!DCbAVzZNrAf4!Rz2ju-oQy_oewfte1yi5m4ZEnsml3tmTcB7mH-tR2IXEfowDtmoxpWC89KOUaAtDO3P2$
> > >=2;*
> > really don't play well with the current behaviour (this was likely one of
> > the reasons the standard is written like it is)
> >
> > IMO we should just fix Phoenix to be compliant, however, as I don't have
> > the context on why Phoenix works like this, I'd like to ask everyone:
> >
> > Is the current behaviour an important compatibility workaround for some
> > other
> > DB which is also non-compliant, and if it is, is this (still) important
> > enough not to fix this, or at least provide some way to preserve bug
> > compatibility, and take the associated complexity hit, while fixing it ?
> >
> > Istvan
> >
>
>
> --
> Daniel Wong
> Salesforce
> Mobile: 628.217.1808
>