Re: Finally figuring out some ob-sqlite stuff -- for worg?
Hi Eric, Eric Abrahamsen writes: > This is about putting some more example stuff on Worg about using Org > and SQLite... Please go ahead and add this -- no need to ask for permission, Worg is not too sensible :) Thanks, -- Bastien
Re: Finally figuring out some ob-sqlite stuff -- for worg?
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.
Re: Finally figuring out some ob-sqlite stuff -- for worg?
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
Re: Finally figuring out some ob-sqlite stuff -- for worg?
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: #+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. :) -- Until the next mail..., Stefan.
Re: Fwd: Re: Finally figuring out some ob-sqlite stuff -- for worg?
"Thomas S. Dye" writes: > Cancel that, I was looking at a cached version of the page. I guessed that was what happened :)
Re: Finally figuring out some ob-sqlite stuff -- for worg?
Stefan Nobis writes: > Eric Abrahamsen writes: > >> Okay, it's up. If anyone wants to explain to me the point of the >> "where exists" clause in the SQL, I would be interested to hear. It >> works as expected this way, but is that clause necessary? > > Yes, very necessary. Without it, all ratings would be changed - the > two example rows without ratings (ids 5 and 12) would get the values > from the intermediary org table, every other row in table bookreview > would get its rating attribute set to null (because there is no > matching entry in the temporary updates table). > > Remember: update without a where clause always touches every single > row of the complete table. > > The "where exists" clause ensures that only those rows of bookreviews > are touched that are present in the intermediary org table. If you do > not like "where exists" you could say "where bookreview.id in (select > id from udpates)". Beautiful, this explains it perfectly. I had the sense that was the purpose, but my very straightforward programming brain was insisting that that job should be done with something like your final tip above: update only if the id is in the updates table. I was confused in part because the "where exists (select *..." looks like its main purpose is to return rows. Will the select subquery actually restrict the values that are available for updating/comparison in the update statement? 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? Ie, we could write "where exists (select " to the same effect? Hope that's clear! In essence, the "where exists" is acting as an "inner join"... Sorry for the off-topic SQL detour! And thanks very much for this cogent explanation. Eric
Fwd: Re: Finally figuring out some ob-sqlite stuff -- for worg?
Cancel that, I was looking at a cached version of the page. Thanks again for the documentation. All the best, Tom Thomas S. Dye writes: Okay, it's up. If anyone wants to explain to me the point of the "where exists" clause in the SQL, I would be interested to hear. It works as expected this way, but is that clause necessary? The 'if exists' clause protects against an SQLite error raised if you ask to delete a table that doesn't exist. The code will work without it if the table exists, but will exit without creating the table if not. Thanks for a useful addition to the Org babel SQLite documentation. I agree with you that Org mode tables are a convenient way to enter SQL data, keeping in mind that table columns can't be rearranged without changes to the SQL code. All the best, Tom -- Thomas S. Dye http://tsdye.online/tsdye
Re: Finally figuring out some ob-sqlite stuff -- for worg?
Okay, it's up. If anyone wants to explain to me the point of the "where exists" clause in the SQL, I would be interested to hear. It works as expected this way, but is that clause necessary? The 'if exists' clause protects against an SQLite error raised if you ask to delete a table that doesn't exist. The code will work without it if the table exists, but will exit without creating the table if not. Thanks for a useful addition to the Org babel SQLite documentation. I agree with you that Org mode tables are a convenient way to enter SQL data, keeping in mind that table columns can't be rearranged without changes to the SQL code. All the best, Tom -- Thomas S. Dye http://tsdye.online/tsdye
Re: Finally figuring out some ob-sqlite stuff -- for worg?
Eric Abrahamsen writes: > Okay, it's up. If anyone wants to explain to me the point of the > "where exists" clause in the SQL, I would be interested to hear. It > works as expected this way, but is that clause necessary? Yes, very necessary. Without it, all ratings would be changed - the two example rows without ratings (ids 5 and 12) would get the values from the intermediary org table, every other row in table bookreview would get its rating attribute set to null (because there is no matching entry in the temporary updates table). Remember: update without a where clause always touches every single row of the complete table. The "where exists" clause ensures that only those rows of bookreviews are touched that are present in the intermediary org table. If you do not like "where exists" you could say "where bookreview.id in (select id from udpates)". -- Until the next mail..., Stefan.
Re: Finally figuring out some ob-sqlite stuff -- for worg?
"Fraga, Eric" writes: > This looks quite useful and would be nice to have on Worg. Thanks. Okay, it's up. If anyone wants to explain to me the point of the "where exists" clause in the SQL, I would be interested to hear. It works as expected this way, but is that clause necessary?
Re: Finally figuring out some ob-sqlite stuff -- for worg?
"Thomas S. Dye" writes: > Aloha Eric, > > Good news. Yes, please feel free to update the Worg SQLite page. > > IIRC, you can get permission from Bastien to push changes and then you > can edit Worg at will. Cool! Bastien, can you help me get write access to Worg? Thanks, Eric
Re: Finally figuring out some ob-sqlite stuff -- for worg?
Aloha Eric, Good news. Yes, please feel free to update the Worg SQLite page. IIRC, you can get permission from Bastien to push changes and then you can edit Worg at will. All the best, Tom -- Thomas S. Dye http://tsdye.online/tsdye
Re: Finally figuring out some ob-sqlite stuff -- for worg?
This looks quite useful and would be nice to have on Worg. Thanks. -- Eric S Fraga via Emacs 27.0.50, Org release_9.2.6-552-g8c5a78
