 OK, welcome. So, we just recently heard from Susanna on CTEs and window functions. This is a new functionality that we added to MySQL 8014, which was released almost two weeks ago. So, it's about teaching all Dolphin's new tricks, and this is one of the new SQL features that we have added. And when the engineers came and said, we're doing lateral dry tables, I said, yay, what is it? And that's basically this talk, what is it, how do you use it, and how does it work? So, it's a very simple agenda. It's basically what is it or how does it work? First half is roughly, well, mostly theory. And then I'll go into examples. So, lateral dry tables. Let's start with derived tables. Some of you know, I guess, some may not have this clearly in front of them, that derived tables are subqueries in front classes. You have subqueries in the select list or in the where clause or whatever, but those are not derived tables. It's in front class, it's a derived table, and you give it a name. And MySQL has two ways of executing these derived tables. We can materialize, which means we just create a temporary table, run that subquery, and fill it into the temporary table, and then execute the main query using those tables. Or we can merge this into the main query itself and do it in one go. So, these are derived tables. And then we have lateral derived tables. And I'm not a native English speaker. I guess most of you aren't either. So, I thought I knew what lateral meant, but to be sure, I went through the dictionary. This is not readable in the resolution of this projector, but it says that it refers to kind of sideways relations. So, relating to the side. And what does that mean for derived tables? To the side of what? It means that we are referring to other tables in the same front class. So, if we had nested queries with kind of one main query with a subquery, with a subquery, with a subquery. If you refer to one of these other queries, that would be a reference to an outer query, but a lateral reference is a reference to something in the same front class, on the same level. So, a lateral derived table is introduced by the keyword lateral. This is what allows you to make those references. Without that keyword, it's a derived table that is not allowed to make those references. So, a non-lateral derived table is self-contained. It doesn't have any references. Before 8.14 is self-contained. In 8.14, we have another restriction that's lifted. You can have outer references in derived tables. That is, no matter whether you have lateral keyword or not, outer references are always allowed in 8.14. Before that, it wasn't allowed at all. So, with lateral, you can refer to other tables, but we have t1 here, and we have the derived table, and we have t2. And the lateral statement can't refer to t2 because t2 comes after it in the front class. So, the smart people in the SQL standards community they decided that you should only be allowed to refer to things to the left of your lateral derived table because that way you avoid loops in your declarations. So, I guess it would be really hard to solve all this if you could refer to any table in the front class. You can also refer to other derived tables. It doesn't have to be a proper table. So, a lateral derived table can refer to another derived table, even lateral derived tables. And again, we can execute this using materialization or merging. Depending on the query, we may not be able to merge this into the outer query. So, materialization is a bit more common, I guess, in this case than with non-lateral derived tables. If you like specifications, the SQL standard calls this feature t491, lateral derived table. I recommend watching this presentation before trying to read this in standard because this explains it, the standards define it, and it's, I tried reading it, it's not an introduction to the topic. There are other lateral tables or table functions. Table functions are by default or always lateral in an SQL. MySQL has one table function, JSON table, and it is lateral. You're not allowed to say lateral in front of it, but it is always implicitly lateral. So, if you have used JSON table or seen one of our examples, you will see that you always select from a table containing the JSON column and then we say JSON table and refer to that column. So, we have lateral reference always in these examples because basically that's the only way it's really useful. You can also do lateral derived tables, you could say lateral, and then not actually have a lateral reference. Like this one, we select one, that's not, there's no reference at all in this query. SQL is smart, it optimizes this as if lateral is not present. There's no performance penalty, this is fine. But think about the next guy reading your query. So, don't go add lateral to all your derived tables. If you come to a query and see this, oh, there's lateral in front of this derived table. There must be a lateral reference, but no, there's not. Who the heck was this guy who wrote this and you discovered it was yourself? So, don't add lateral. If you don't use lateral, you will get an error if you try to use lateral reference. And if you don't expect to use one, you probably want that error and at least a warning. So, use this carefully. Then, we are ready to do examples. So, did anyone, everyone get the theory part of this? Cool, good, cool. I'll try to run this in. So, I'm sorry, Fredrik and Miguel, we don't use MySQL shell here, we use workbench. I hope you can see it in the back. I'm not able to change the size of the status messages, but you can see the queries, at least. So, just to clean up if I don't, I haven't done it already. So, my example is real simple. I have a table cities. Each city has a name, it has population, and it is in a specific country. We have excluded cities that are not in just one country. If it's shared between countries, I don't want to know about it. This is SQL, not politics. And we have data sets, some example cities. One thing, we have several cities in the same country, for some of these countries. So, there's not only one city in each country. And that is the data sets. So, what I want to know is, what is the largest city in each country? And I sat down, I was typing lateral stuff, and I said, yeah, okay, let's use lateral for this. So, I am selecting the population, the name of the city, and the name of the country from a derived table, non-lateral derived table, where I select distinct country names from cities. So, I basically select a list of countries, all the countries. And I join that with a lateral derived table, which selects the name of the city and population, where the country name matches the name of the country from the country table. So, this is the lateral reference I have. And I order it by population from the largest to the smallest and limit one. So, this is an EU first approach to solving this query. The logic is pretty clear. I select a list of countries, and for each country, I select the largest city. That is why people call this feature the for each of SQL. So, lateral derived tables are the for each loop of SQL. Let this to the engineer who implemented lateral derived tables in my scroll. So, Glenn said, yeah, that works. I have another option. Let's look at the result first, just to verify that this is correct. So, I get a number of cities. I have more queries doing the same thing. So, Glenn said to me, yeah, I have a different approach. This is what I would do, also using lateral. So, instead of selecting countries and then the largest city of each country, he suggests selecting the country and the size of the largest city. And then joining that with a lateral table containing the city name where the country and population size matches what we found in the other one. So, those are the two lateral references in this screen. The reason is that this doesn't do order by and limit, which is kind of okay, it's a better query. It's cheaper to execute. So, I said, yeah, cool. And we ran it and got the same result. Good catch. There's a mistake in there. If you have two cities with the same population, you will get two rows in this case. But in my query, you would get one, a random one, one of these. And that is surely a mistake, but it's not a mistake in lateral. It's a mistake in my request for this query. So, that is the difference between these. So, there is a semantic difference, but they are, if you exclude the chance of having two equally large cities being the largest cities in one country, you will get the same result. So, it's a matter of definition, which is best and which you want. But the fact is that this is a more efficient execution. I will show the explains later on. What he also told me is that this is another way of doing it without lateral. I'll just mark this for you. This is the same thing as in the previous query. So, the only new part is this join. So, we joined the country and max population with the city table and just saying that these two should match. So, it's not in the lateral derived table. And this is actually what the MySQL optimizer will produce if you type the first query. You can look at the explain for this. I think we can do virtual explain. It's probably a bit small to read in the back, but let's see if we can make it bigger. There's a derived table that is materialized. That is the one with the grouping. So, this is the list of countries and populations. And it's joined with the city's table. So, there's no second derived table here. If you look at the tabular view, there's one derived table and that is the list of countries with populations. If we look at the one that is supposed to be equivalent in the eyes of the optimizer, we get the same thing. There's one derived table here, and indeed the visual explain is also the same. So, we can see the optimizer is actually removing our lateral query. It's just merging our lateral derived table into the main query itself. Let's look at my suboptimal first attempt. That is different. Oh, sorry, that was execution, not explain. Explain. Can do graphically first. You see that there are two materializations. There's one for the list of countries and one for the largest cities. So, I materialized both of these. And one doing distinct country names and one doing ordering and limits. If you look at the textura explain, I hope you can read this, there is the primary derived tube. That is the derived table with the country names. This is the derived table, normal derived table. And then we have derived three, which is the optimizer's names for the second derived table, the lateral one. And we see that here in the first one, it says rematerialize derived three, which means that for each new value, each new country, we will rematerialize the lateral derived table. So, you get a hint in the explain what is happening, which thing is causing what to rematerialize. And we have the base tables for this. For the list of countries, we have the cities table. And for the list of, or for the largest city, we have another one. We remove the marking to make it more readable, I guess. It says dependent derived, which means that this is the derived table, but it's dependent on the rematerialization. So, again, you have a clue that this one is read every time. So, that is kind of three variants of the same query, or slight variants of the task list. So, you can say, is this materialized thing worse? Well, one thing that this can solve, that the other one can solve, is if I ask for the three largest cities of each country. It's not sorted, but there should be three of each. That is not that easy to solve with the other options. So, you might need this. I'm not aware of any theoretical proof that you can or can't replace lateral with other constructs always. So, I'm not sure there's a proof either way for that, but I am sure that if you try to replace all laterals with other stuff, it will be a very hard to read query, because this makes it at least easier to read. But be aware that sometimes we're not able to optimize it fully. If we go back to the presentation and skip the slides in case things failed. So, on Thursday, there was a new blog post on MySQL Server team.com from Gillem, the guy who wrote this code, about how it works. Also for cities and window functions that we heard about in the last presentation, there's blog posts here explaining the features. So, if there's something you wonder about about MySQL features, go to MySQL Server team.com and read it. That was all I had. Thank you.