Обсуждение: subselects

OK, I now have rewrites working with subselects.  At least, I think it
will work.  No way to test it yet.

It basically rewrites all the subqueries first, then the main query.  It
does the lowest queries first.  I do this so if a rewrite adds a
subquery as part of the rewrite, the new subquery does not get
processed.

For each query rewritten, I have code to go into each SubLink and recode
any correlated variables that reference the outer query level I am
rewriting.  Pretty slick.

--
Bruce Momjian
maillist@candle.pha.pa.us

Bruce wrote:
> I have completed adding Var.varlevelsup, and have added code to the
> parser to properly set the field.  It will allow correlated references
> in the WHERE clause, but not in the target list.

select i2.ip1, i1.ip4 from nameip i1 where ip1 = (select ip1 from nameip
i2);
   522: Table (i2) not selected in query.
select i1.ip4 from nameip i1 where ip1 = (select i1.ip1 from nameip i2);
   284: A subquery has returned not exactly one row.
select i1.ip4 from nameip i1 where ip1 = (select i1.ip1 from nameip i2
where name='zeus');
 2 row(s) retrieved.

Informix allows correlated references in the target list. It also allows
subselects in the target list as in:
select i1.ip4, (select i1.ip1 from nameip i2) from nameip i1;
   284: A subquery has returned not exactly one row.
select i1.ip4, (select i1.ip1 from nameip i2 where name='zeus') from
nameip i1;
 2 row(s) retrieved.

Is this what you were looking for ?

Andreas

>
> Bruce wrote:
> > I have completed adding Var.varlevelsup, and have added code to the
> > parser to properly set the field.  It will allow correlated references
> > in the WHERE clause, but not in the target list.
>
> select i2.ip1, i1.ip4 from nameip i1 where ip1 = (select ip1 from nameip
> i2);
>    522: Table (i2) not selected in query.
> select i1.ip4 from nameip i1 where ip1 = (select i1.ip1 from nameip i2);
>    284: A subquery has returned not exactly one row.
> select i1.ip4 from nameip i1 where ip1 = (select i1.ip1 from nameip i2
> where name='zeus');
>  2 row(s) retrieved.
>
> Informix allows correlated references in the target list. It also allows
> subselects in the target list as in:
> select i1.ip4, (select i1.ip1 from nameip i2) from nameip i1;
>    284: A subquery has returned not exactly one row.
> select i1.ip4, (select i1.ip1 from nameip i2 where name='zeus') from
> nameip i1;
>  2 row(s) retrieved.
>
> Is this what you were looking for ?
>
> Andreas
>
>

Yes, I know other engines support subqueries and references in the
target list.  I want to know if we are going to do that for 6.3.
Personally, I have never seen much use for it.

--
Bruce Momjian
maillist@candle.pha.pa.us

On Wed, 21 Jan 1998, Bruce Momjian wrote:

> >
> > Bruce wrote:
> > > I have completed adding Var.varlevelsup, and have added code to the
> > > parser to properly set the field.  It will allow correlated references
> > > in the WHERE clause, but not in the target list.
> >
> > select i2.ip1, i1.ip4 from nameip i1 where ip1 = (select ip1 from nameip
> > i2);
> >    522: Table (i2) not selected in query.
> > select i1.ip4 from nameip i1 where ip1 = (select i1.ip1 from nameip i2);
> >    284: A subquery has returned not exactly one row.
> > select i1.ip4 from nameip i1 where ip1 = (select i1.ip1 from nameip i2
> > where name='zeus');
> >  2 row(s) retrieved.
> >
> > Informix allows correlated references in the target list. It also allows
> > subselects in the target list as in:
> > select i1.ip4, (select i1.ip1 from nameip i2) from nameip i1;
> >    284: A subquery has returned not exactly one row.
> > select i1.ip4, (select i1.ip1 from nameip i2 where name='zeus') from
> > nameip i1;
> >  2 row(s) retrieved.
> >
> > Is this what you were looking for ?
> >
> > Andreas
> >
> >
>
> Yes, I know other engines support subqueries and references in the
> target list.  I want to know if we are going to do that for 6.3.
> Personally, I have never seen much use for it.

    If its easy to add in the next couple of days, sure, go for
it...but can someone explain to me *why* you would use a subselect in the
target list?  I've actually never seen that before :9

> > Yes, I know other engines support subqueries and references in the
> > target list.  I want to know if we are going to do that for 6.3.
> > Personally, I have never seen much use for it.
>
>     If its easy to add in the next couple of days, sure, go for
> it...but can someone explain to me *why* you would use a subselect in the
> target list?  I've actually never seen that before :9

I have no idea why someone would want to do that.  I have enough trouble
figuring out how the engine is going to execute normal queries, let
alone strange ones like that.

--
Bruce Momjian
maillist@candle.pha.pa.us