Re: [h2] Re: how are subqueries executed in an IN Select
Noel, Thanks for the suggestion. I opted for a JOIN for the time-being. I am a little hesitant to upgrade h2 versions, because we are using it in production. When I have time, I hope to locally test the newest stable h2 to see if the IN Select is now optimized to not repeat a sub-query. Best, -Adam On Monday, September 19, 2016 at 2:59:17 AM UTC-4, Noel Grandin wrote: > > Convert it to a join. > > Or pick a newer version, but stick with the PAGE_STORE engine, I'm pretty > much we've made lots of plannerimprovements since 1.3.176 > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: how are subqueries executed in an IN Select
Convert it to a join. Or pick a newer version, but stick with the PAGE_STORE engine, I'm pretty much we've made lots of plannerimprovements since 1.3.176 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: how are subqueries executed in an IN Select
Hi, I put in a logger via a user defined function, and it seems to in fact re-execute the IN sub-query for every matching outer row. Am I interpreting this correctly? Any work around? -Adam On Sunday, September 18, 2016 at 8:25:20 PM UTC-4, Adam McMahon wrote: > > Hi, > > When a SELECT is used as a sub-query of an IN, is the sub-query only > executed once? Consider the following made up query that finds all staff > members who are in the state of California who are also a student majoring > in Computer Science. > > select * from students where major='CSC' and id IN (select id from staff > where state='CA') > > Does H2 first execute the sub-query, and the the outer one? Or does it > execute the sub-query at every row match from the outer query? I am using > version 1.3.176 with page store (if that matters). > > Thanks, > > -Adam > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
