From mboxrd@z Thu Jan 1 00:00:00 1970 From: Eric Abrahamsen Subject: Re: Finally figuring out some ob-sqlite stuff -- for worg? Date: Sun, 10 Nov 2019 11:48:50 -0800 Message-ID: <875zjr5x7h.fsf@ericabrahamsen.net> References: <87bltn5it6.fsf@ericabrahamsen.net> <87d0e23lca.fsf@ucl.ac.uk> <87woc956zj.fsf@ericabrahamsen.net> <87r22h53tp.fsf@ericabrahamsen.net> Mime-Version: 1.0 Content-Type: text/plain Return-path: Received: from eggs.gnu.org ([2001:470:142:3::10]:39069) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1iTtDp-0003eR-2p for emacs-orgmode@gnu.org; Sun, 10 Nov 2019 14:50:07 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1iTtDn-0000IU-Rj for emacs-orgmode@gnu.org; Sun, 10 Nov 2019 14:50:04 -0500 Received: from 195-159-176-226.customer.powertech.no ([195.159.176.226]:37530 helo=blaine.gmane.org) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1iTtDn-0000I8-Gr for emacs-orgmode@gnu.org; Sun, 10 Nov 2019 14:50:03 -0500 Received: from list by blaine.gmane.org with local (Exim 4.89) (envelope-from ) id 1iTtDm-000X2e-Aj for emacs-orgmode@gnu.org; Sun, 10 Nov 2019 20:50:02 +0100 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: emacs-orgmode@gnu.org Stefan Nobis writes: > Eric Abrahamsen writes: > >> I was confused in part because the "where exists (select *..." looks >> like its main purpose is to return rows. > > Indeed that's the purpose: Restrict the set of rows upon which update > acts on. Here I tried to reformat the statement a bit in order to > emphasize its structure: Right -- I should have phrased that as "looks like its main purpose is to return data from rows", which as you clarify below, isn't its main purpose. > #+begin_src sql > UPDATE bookreview > SET rating = (select rating from updates > where bookreview.id = updates.id) > WHERE EXISTS (select * from updates > where updates.id = bookreview.id); > #+end_src > > The subselect of the "SET rating" part is a correlated subquery. So if > you imagine UPDATE as a kind of loop over the table, the subquery of > the SET part is executed once for every row UPDATE acts on (maybe the > SQL execution engine optimizes this in some kind, but the mental model > here is: run the subquery for every row we visit on our journey > throught the table). > > Only the WHERE EXISTS clause belonging directly to the UPDATE > statement will reduce the set of rows to act on. > >> Will the select subquery actually restrict the values that are >> available for updating/comparison in the update statement? > > No. > >> Or does the "exists" mean the subquery is treated as a plain yes/no >> boolean, and the update still has access to anything it likes? We >> could write "where exists (select " to the same effect? > > Yes. The SELECT clause of an EXISTS subquery (as in the above example) > is rather meaningless. So somethimes you see constructs like "where > exists (select 1 from ...)". Some SQL engines are not very clever and > execute the subquery of such an EXISTS clause unchanged - meaning that > way too much data is fetched for the intermediate result (unnecessary > IO and maybe polluting caches). Thus the "select 1" as a workaround > for those unclever engines. But current engines should have no > problems with optimizing these EXISTS subqueries and in that case it > does not matter how the select clause looks like - it will be ignored. > >> In essence, the "where exists" is acting as an "inner join"... > > Yes, effectively we are simulating an inner join at this point. Sadly, > many SQL engines are not able to update rows of join constructs (or at > least have quite severe constraints in these cases). Thus we need to > build these kinds of workarounds to change data in more complex cases. > > SQL is quite a capable language, but it has also has some rough edges. > :) Really interesting! Thanks again for the in-depth explanation.