emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Eric Abrahamsen <eric@ericabrahamsen.net>
To: emacs-orgmode@gnu.org
Subject: Re: Finally figuring out some ob-sqlite stuff -- for worg?
Date: Sun, 10 Nov 2019 11:48:48 -0800	[thread overview]
Message-ID: <877e475x7j.fsf@ericabrahamsen.net> (raw)
In-Reply-To: m1sgmwawv8.fsf@nobis-it.eu

Stefan Nobis <stefan-ml@snobis.de> writes:

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

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 <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.
> :)

Really interesting! Thanks again for the in-depth explanation

  reply	other threads:[~2019-11-10 19:49 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
2019-11-10 19:48           ` Eric Abrahamsen [this message]
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=877e475x7j.fsf@ericabrahamsen.net \
    --to=eric@ericabrahamsen.net \
    --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).