bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

Marek Wieckowski
Hi,

Basically: in a subselect in an update statement all  fields from the row
which is being updated CAN be used in SELECT and WHERE clauses (see the
first update below using test.whereField in the WHERE clause), but using
them in ORDER BY leads to an error.


Example:

create table test (
            whereField integer,
            orderField integer,
            value integer);

insert into test (whereField, orderField, value)
values
    (0, 0, 10),
    (1, 2, 30),
    (0, 1, 15),
    (1, 0,  7);

UPDATE test
SET value = value + 100 *(
                    SELECT i.value
                    FROM test i
                    WHERE    i.whereField = test.whereField
                    LIMIT 1
                    );

SELECT * FROM test;
-- whereField  orderField  value
-- ----------  ----------  ----------
-- 0           0           1010
-- 1           2           3030
-- 0           1           101015
-- 1           0           303007

UPDATE test
SET value = value + 100 *(
                    SELECT i.value
                    FROM test i
                    WHERE    i.whereField = test.whereField
                    ORDER BY i.orderField = test.orderField
                    LIMIT 1
                    );

Error: no such column: test.orderField


Reproducible with the newest vanilla sqlite:
      sqlite-autoconf-3170000.tar.gz
      SQLite version 3.17.0 2017-02-13 16:02:40
but the same bug exists in older versions: we noticed it back in September,
see e.g.
http://stackoverflow.com/questions/39350537/sqlite-update-select-query-referencing-table-being-updated-in-order-by-clause/


Regards,
Marek
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

James K. Lowden
On Tue, 14 Feb 2017 15:06:16 +0100
Marek Wieckowski <[hidden email]> wrote:

> UPDATE test
> SET value = value + 100 *(
>                     SELECT i.value
>                     FROM test i
>                     WHERE    i.whereField = test.whereField
>                     ORDER BY i.orderField = test.orderField
>                     LIMIT 1
>                     );
>
> Error: no such column: test.orderField

There are other errors, too.  The update is nondeterministic and
nonstandard.  Does this not serve the purpose better?  

        UPDATE test
        SET value = value + 100 * (
                      SELECT min(i.value) -- or max, or something
                      FROM test i
                      WHERE    i.whereField = test.whereField
                      );

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

Simon Slavin-3

On 14 Feb 2017, at 3:36pm, James K. Lowden <[hidden email]> wrote:

> UPDATE test
> SET value = value + 100 * (
>                      SELECT min(i.value) -- or max, or something
>                      FROM test i
>                      WHERE    i.whereField = test.whereField
>                      );

Someone please explain something to me ?  One of my assumptions is wrong.

The construction "FROM test i" is a short form of "FROM test AS i".  This sets up an alias to the table "test" so you can call it "i" if you want to.

Later on in that command I see "WHERE i.whereField = test.whereField".  Under the circumstances is that not the same as "WHERE test.whereField = test.whereField" ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

David Raymond
It's not. It sets up an alias to a new view of test. In the where clause the i.whereField is referring to that new view of the whole table, whereas the test.whereField is referring to the field in the current record of test that's being updated.

So if the table is things, and whereField is the type of thing, what's happening here is for each thing to increment its value by 100 times the least valuable thing of the same type.

So what's being commented is that the "least valuable thing" is potentially changing after every updated row, rather than being a constant of "the least valuable thing as it stood at the start of the update"


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Tuesday, February 14, 2017 10:41 AM
To: SQLite mailing list
Subject: Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect


On 14 Feb 2017, at 3:36pm, James K. Lowden <[hidden email]> wrote:

> UPDATE test
> SET value = value + 100 * (
>                      SELECT min(i.value) -- or max, or something
>                      FROM test i
>                      WHERE    i.whereField = test.whereField
>                      );

Someone please explain something to me ?  One of my assumptions is wrong.

The construction "FROM test i" is a short form of "FROM test AS i".  This sets up an alias to the table "test" so you can call it "i" if you want to.

Later on in that command I see "WHERE i.whereField = test.whereField".  Under the circumstances is that not the same as "WHERE test.whereField = test.whereField" ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

Simon Slavin-3

On 14 Feb 2017, at 3:55pm, David Raymond <[hidden email]> wrote:

> It's not. It sets up an alias to a new view of test. In the where clause the i.whereField is referring to that new view of the whole table, whereas the test.whereField is referring to the field in the current record of test that's being updated.

Is it a self-JOIN then ?  A select where you do something like

SELECT products.id,betterProduct.id FROM products
        JOIN products AS betterProduct ON betterProduct.purpose = product.purpose
        WHERE betterProduct.score > product.score

?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

David Raymond
Correct.



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Tuesday, February 14, 2017 11:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect


On 14 Feb 2017, at 3:55pm, David Raymond <[hidden email]> wrote:

> It's not. It sets up an alias to a new view of test. In the where clause the i.whereField is referring to that new view of the whole table, whereas the test.whereField is referring to the field in the current record of test that's being updated.

Is it a self-JOIN then ?  A select where you do something like

SELECT products.id,betterProduct.id FROM products
        JOIN products AS betterProduct ON betterProduct.purpose = product.purpose
        WHERE betterProduct.score > product.score

?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

Simon Slavin-3

On 14 Feb 2017, at 4:42pm, David Raymond <[hidden email]> wrote:

> Correct.

Thanks, David.  I'd completely missed what this syntax allowed you to do.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

Marek Wieckowski
In reply to this post by James K. Lowden
Hi James,

Thanks for your answer.

Yes, there are lots of different queries that you might want to write
_instead_ - in your example you have changed the logic of the update...

But you could also rewrite the original query to keep the original logic in
such a way that it would work in sqlite (see e.g. the stackoverflow page
http://stackoverflow.com/questions/39350537/sqlite-update-select-query-referencing-table-being-updated-in-order-by-clause/
- the simplest is to ... add an extra level of sub-select, select the value
you want to order by (so you use an "external" field in SELECT) and then
order by the value from sub-subselect... Super ugly and harder to
understand.).


The thing is that in principle there is nothing wrong with using test.xxx
fields in the subselect: there really should be no difference whether you
use them in "where" or "order by"... The fact that sqlite does not allow
them to be used in ORDER BY (while allowing in SELECT and WHERE) imho is
simply a bug. You don't want to force users to write ugly workarounds.

Oh, and btw: the same syntax (with using an external field in ORDER BY of a
subselect) simply works e.g. in update statements postgres.

Best,
Marek


On Tue, Feb 14, 2017 at 4:36 PM, James K. Lowden <[hidden email]>
wrote:

> On Tue, 14 Feb 2017 15:06:16 +0100
> Marek Wieckowski <[hidden email]> wrote:
>
> > UPDATE test
> > SET value = value + 100 *(
> >                     SELECT i.value
> >                     FROM test i
> >                     WHERE    i.whereField = test.whereField
> >                     ORDER BY i.orderField = test.orderField
> >                     LIMIT 1
> >                     );
> >
> > Error: no such column: test.orderField
>
> There are other errors, too.  The update is nondeterministic and
> nonstandard.  Does this not serve the purpose better?
>
>         UPDATE test
>         SET value = value + 100 * (
>                       SELECT min(i.value) -- or max, or something
>                       FROM test i
>                       WHERE    i.whereField = test.whereField
>                       );
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

James K. Lowden
On Wed, 15 Feb 2017 12:06:58 +0100
Marek Wieckowski <[hidden email]> wrote:

> The thing is that in principle there is nothing wrong with using
> test.xxx fields in the subselect: there really should be no
> difference whether you use them in "where" or "order by"... The fact
> that sqlite does not allow them to be used in ORDER BY (while
> allowing in SELECT and WHERE) imho is simply a bug.

The SQL standard does not allow ORDER BY anywhere except as the last
clause of the main query.  ORDER BY does not enter into the logic of
the query; it's a convenience to the host program receiving the rows.  

The idiom

        select ...
        order by ...
        limit 1

can always be replaced with a logical

        select min(...) -- or max(...)

The idiom

        select ...
        limit N

is illogical because nondeterministic.  Since we're trading
opinions ;-)  mine is that SQL should allow only logical constructs and
should refuse every illogical construct as a syntax error.  

There was kind of hole in standard SQL in that there was no convenient
way to express the idea of TOP N rows or the Nth row with a particular
ranking.  That's addressed these days with window functions, although
it's debatable how "convenient" they are, and in any event SQLite
doesn't support them.  

In SQLite the hole is filled with ORDER BY ... LIMIT.  The problem is
that construct is frequently misused, as in  your example, and it is
unnecessarily complex.  

How so?  Observe that *order* is implicit in magnitude functions: max()
implies order without requiring the user to say so.  

Much more powerful would be functions that return N values instead of
just one.  The most convenient form would require support in the SQL
interpreter.  For example:

        select date, maxn(3, score) from scores group by by date

would produce (up to) three scores for each date.  

Not only is the logic for that query awkward to express in SQL (any
version) but, because of its roundabout expression, it presents a
challenge to the query planner.  With the notion of "top N" buried in
a function, the interpreter would be free to keep track of the top N
values without necessarily sorting them.  

--jkl



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users