From: Stefan Nobis <stefan-ml@snobis.de>
To: emacs-orgmode@gnu.org
Subject: Re: Finally figuring out some ob-sqlite stuff -- for worg?
Date: Sun, 10 Nov 2019 10:44:59 +0100 [thread overview]
Message-ID: <m1sgmwawv8.fsf@nobis-it.eu> (raw)
In-Reply-To: <87r22h53tp.fsf@ericabrahamsen.net> (Eric Abrahamsen's message of "Sat, 09 Nov 2019 09:58:58 -0800")
Eric Abrahamsen <eric@ericabrahamsen.net> 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:
#+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 <foo>" 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.
:)
--
Until the next mail...,
Stefan.
next prev parent reply other threads:[~2019-11-10 9:45 UTC|newest]
Thread overview: 12+ messages / expand[flat|nested] mbox.gz Atom feed top
2019-11-08 0:10 Finally figuring out some ob-sqlite stuff -- for worg? Eric Abrahamsen
2019-11-08 6:59 ` Fraga, Eric
2019-11-09 16:50 ` Eric Abrahamsen
2019-11-09 17:28 ` Stefan Nobis
2019-11-09 17:58 ` Eric Abrahamsen
2019-11-10 9:44 ` Stefan Nobis [this message]
2019-11-10 19:48 ` Eric Abrahamsen
2019-11-10 19:48 ` Eric Abrahamsen
2020-02-12 9:15 ` Bastien
-- strict thread matches above, loose matches on Subject: below --
2019-11-08 7:06 Thomas S. Dye
2019-11-08 16:20 ` Eric Abrahamsen
2019-11-09 17:38 Thomas S. Dye
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=m1sgmwawv8.fsf@nobis-it.eu \
--to=stefan-ml@snobis.de \
--cc=emacs-orgmode@gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).