Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Marek Wieckowski]

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 
wrote:

> On Tue, 14 Feb 2017 15:06:16 +0100
> Marek Wieckowski  wrote:
>
> > UPDATE test
> > SET value = value + 100 *(
> > SELECT i.value
> > FROM test i
> > WHEREi.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
>   WHEREi.whereField = test.whereField
>   );
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 14 Feb 2017, at 4:42pm, David Raymond  wrote:

> Correct.

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

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 14 Feb 2017, at 3:55pm, David Raymond  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org] 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  wrote:

>   UPDATE test
>   SET value = value + 100 * (
>  SELECT min(i.value) -- or max, or something
>  FROM test i
>  WHEREi.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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 14 Feb 2017, at 3:36pm, James K. Lowden  wrote:

>   UPDATE test
>   SET value = value + 100 * (
>  SELECT min(i.value) -- or max, or something
>  FROM test i
>  WHEREi.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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> UPDATE test
> SET value = value + 100 *(
> SELECT i.value
> FROM test i
> WHEREi.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
  WHEREi.whereField = test.whereField
  );

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
WHEREi.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
WHEREi.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-317.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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users