Unsupported SQL feature

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Unsupported SQL feature

Alexander J. Kozlovsky
Hi!

I just notice, SQLite don't support this type of queries:


  select * from t1 where a >= ALL (select b from t2)

 
Of course, queries with ALL, ANY and SOME clauses may be reformulated,
but this is part of SQL standard.

I use SQLite for teach SQL to students, and will be very happy
if such feature appears in next version of SQLite



Best regards,
 Alexander                          mailto:[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Unsupported SQL feature

D. Richard Hipp
Alexander Kozlovsky <[hidden email]> wrote:

> Hi!
>
> I just notice, SQLite don't support this type of queries:
>
>
>   select * from t1 where a >= ALL (select b from t2)
>
>  
> Of course, queries with ALL, ANY and SOME clauses may be reformulated,
> but this is part of SQL standard.
>
> I use SQLite for teach SQL to students, and will be very happy
> if such feature appears in next version of SQLite
>

What are these constructs suppose to do?
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Unsupported SQL feature

Klint Gore
On Sun, 06 Nov 2005 15:09:34 -0500, [hidden email] wrote:

> Alexander Kozlovsky <[hidden email]> wrote:
> > I just notice, SQLite don't support this type of queries:
> >
> >
> >   select * from t1 where a >= ALL (select b from t2)
> >
> >  
> > Of course, queries with ALL, ANY and SOME clauses may be reformulated,
> > but this is part of SQL standard.
>
> What are these constructs suppose to do?

[from the sql 1992 standard]

         8.7  <quantified comparison predicate>

         Function

         Specify a quantified comparison.

         Format

         <quantified comparison predicate> ::=
              <row value constructor> <comp op> <quantifier> <table subquery>


         <quantifier> ::= <all> | <some>

         <all> ::= ALL

         <some> ::= SOME | ANY


         Syntax Rules

         1) The <row value constructor> shall be of the same degree as the
            result of the <table subquery>.

         2) The data types of the values of the <row value constructor>
            shall be respectively comparable to those of the columns of the
            <table subquery>.

         3) The collating sequence for each pair of respective values in
            the <quantified comparison predicate> is determined in the same
            manner as described in Subclause 8.2, "<comparison predicate>".

         Access Rules

            None.

         General Rules

         1) Let R be the result of the <row value constructor> and let T be
            the result of the <table subquery>.

         2) The result of "R <comp op> <quantifier> T" is derived by the
            application of the implied <comparison predicate> "R <comp op>
            RT" to every row RT in T:

            Case:

            a) If T is empty or if the implied <comparison predicate> is
              true for every row RT in T, then "R <comp op> <all> T" is
              true.

            b) If the implied <comparison predicate> is false for at least
              one row RT in T, then "R <comp op> <all> T" is false.

            c) If the implied <comparison predicate> is true for at least
              one row RT in T, then "R <comp op> <some> T" is true.

            d) If T is empty or if the implied <comparison predicate> is
              false for every row RT in T, then "R <comp op> <some> T" is
              false.

            e) If "R <comp op> <quantifier> T" is neither true nor false,
              then it is unknown.





+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : [hidden email]           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+
Reply | Threaded
Open this post in threaded view
|

Re: Unsupported SQL feature

Kurt Welgehausen
In reply to this post by D. Richard Hipp
> What are these constructs suppose to do?


  x <op> all (select y from t where ...)

is equivalent to

  not exists (select y from t where not (x <op> y) and ...)

Any and some are synonyms.

  x <op> any (select y from t where ...)

is equivalent to

  exists (select y from t where x <op> y and ...)

Any can be confusing because of its ambiguity in the English
language. If I say 'Is x greater than any y in t?', almost
everyone will say the answer is yes if x is greater than one
or more y-values in t -- but if I say 'x is greater than any
y in t', you may interpret that to mean that x is greater
than every y-value in t. This has lead any and all to be
deprecated for new code in some circles.

Regards
Reply | Threaded
Open this post in threaded view
|

Re[2]: Unsupported SQL feature

Alexander J. Kozlovsky
In reply to this post by D. Richard Hipp

> > select * from t1 where a >= ALL (select b from t2)
>
> What are these constructs suppose to do?

"""
  QUANTIFIED SUBQUERIES

  A quantified subquery allows several types of tests and
  can use the full set of comparison operators. It has
  the following general format:

  value-1  {=|>|<|>=|<=|<>}  {ALL|ANY|SOME}  (query-1)

  The comparison operator specifies how to compare value-1
  to the single query column value from each subquery
  result row. The ANY, ALL, SOME specifiers give the type
  of match expected. ANY and SOME must match at least one row
  in the subquery. ALL must match all rows in the subquery,
  or the subquery must be empty (produce no rows).
"""

The next two WHERE condition are equivalent:

  total_price > ALL (SELECT total_price FROM items
                     WHERE order_num = 1023)

  total_price > (SELECT MAX(total_price) FROM items
                 WHERE order_num = 1023)

The next two WHERE condition are equivalent also:

  total_price > ANY (SELECT total_price FROM items
                     WHERE order_num = 1023)

  total_price > (SELECT MIN(total_price) FROM items
                 WHERE order_num = 1023)

ANY and SOME are synonyms.


The next example show real use-case.

Table: Assemblies
+----------------+--------------+--------------+
|  AssemblyName  |  PartNumber  |  PartWeight  |
+----------------+--------------+--------------+
|  Assembly1     |  01          |  100         |
|  Assembly1     |  02          |  150         |
|  Assembly2     |  01          |  120         |
|  ...           |  ...         |  ...         |

Query: Assemblies with max total weight:

Because SQL doesn't allow nested aggregated functions
MAX(SUM(...)), it is easy for some people
write this query as:

    select AssemblyName from Assemblies
    group by AssemblyName
    having SUM(PartWeight) >= ALL (
         select SUM(PartWeight)
         from T1
         group by AssemblyName
    )

Of course, this query may be reformulated as:

    select TOP 1 AssemblyName from Assemblies
    group by AssemblyName
    order by SUM(PartWeight) DESC


>   x <op> all (select y from t where ...)
>
> is equivalent to
>
>   not exists (select y from t where not (x <op> y) and ...)
>
> Any and some are synonyms.
>
>   x <op> any (select y from t where ...)
>
> is equivalent to
>
>   exists (select y from t where x <op> y and ...)

Nice summary


Best regards,
 Alexander                            mailto:[hidden email]