Hi Rob, I can see your point, but couldn't tell you if this should be considered a bug or not. Probably only DRH can tell you that.
RBS On Thu, Oct 27, 2016 at 8:21 AM, Rob Golsteijn <rob.golste...@mapscape.eu> wrote: > Hi RBS, > > Re-introducing mytable in the sub-select is a workaround (and to get the > same semantics in the general case I have to use the same row from the > inner mytable and outer mytable). As indicated in my original message I > already have a workaround for the issue. > > The intention of my post was to report that sqlite incorrectly reports > that mytable does not exists. Table mytable exists --- it is the target > table of the UPDATE statement and still in scope. I see no reason why it is > not allowed to use it in that EXISTS expression. > > So my question: is there a valid reason why sqlite does not know > mytable.myfield2 in my simplified query? Or is this a bug? > > > > Regards, > > Rob Golsteijn > > > > UPDATE mytable > SET myfield1 = (SELECT 1 from mytable > ORDER BY EXISTS (SELECT 1 > WHERE mytable.myfield2 = 1 > ) > ) > > > RBS > > On Tue, Oct 25, 2016 at 4:40 PM, Rob Golsteijn <rob.golste...@mapscape.eu> > wrote: > > > Hi List, > > > > I encountered a situation in which sqlite does not understand to which > > field I try to refer. I simplified the original query to show the > problem. > > The simplified query itself is now completely meaningless (and for my > > specific situation I could rewrite the query to work around the problem). > > > > In the example below I expected that mytable.myfield2 in the EXISTS > > expression would refer to myfield2 of table mytable from the top level > > UPDATE statement. It looks like the combination of an ORDER BY and an > > EXISTS that refers to the table of an UPDATE statement causes sqlite to > > report that myfield2 is unknown. Sqlite does not complain when I use > > mytable.myfield2 in other places in the query. > > > > CREATE TABLE mytable > > ( > > myfield1 INTEGER, > > myfield2 INTEGER > > ); > > > > UPDATE mytable > > SET myfield1 = (SELECT 1 > > ORDER BY EXISTS (SELECT 1 > > WHERE mytable.myfield2 =1 > > ) > > ); > > > > Error: no such column: mytable.myfield2 > > > > Tested with sqlite versions 3.8.4.3, 3.8.8.2, 3.11.1 and 3.15.0 > > (latest). > > > > Met Vriendelijke Groet, Kind Regards, > > > > Rob Golsteijn > > > _______________________________________________ > 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
