What the Heck is a Lateral Derived Table?

MySQL 8.0.14 came with Lateral Derived Tables as a new feature.  But what is that and how do you use them?

Let's start what a derived table is.  According to the manual (link below) A derived table is an expression that generates a table within the scope of a query FROM clause.   You are probably used to using a subquery or JSON_TABLE where that query feeds data into another query.

Derived Tables

Derived tables can return a scalar, column, row, or table. But there are limits to their use.
A derived table cannot be a correlated subquery and a derived table cannot contain references to other tables of the same SELECT. And previous to MySQL 8.0.14, a derived table cannot contain outer references.

Okay, so what is an 'outer reference'? The SQL-92 standard states that derived tables cannot depend on other tables in the same FROM clause.and cannot contain references to columns of other FROM clause tables.  But SQL-99 changed that with the keyword LATERAL (think sudo) so you can reference previously mention table.

Overly Simplified Example

The following is a very contrived query to illustrate the use of the LATERAL keyword.  The  query behind the LATERAL is returning the country.Code column to the query in front of the LATERAL. I am using the good old World database from the MySQL example data sets.

SELECT Name, Population, District, x.cc 
FROM city,
 LATERAL (SELECT Code AS cc FROM country 
         WHERE city.CountryCode = Code) AS x 
 WHERE District = 'Texas' ORDER BY name;

Now if you remove that the LATERAL from the above query, you will see the following error message. (Please do try the above query with and without LATERAL as a learning exercise)

Error Code: 1054. Unknown column 'city.CountryCode' in 'where clause'

So with the LATERAL keyword in place, the left query (the one before the LATERAL) provides that city.CountryCode column to the right query (the one after the lateral). In other words the query behind the LATERAL keyword is depending on the previous query.  Since we named the derived table as x we need to remember to reference it in the other table with x  too.

Quick Conclusion

So with lateral derived tables and Common Table Expressions (CTEs), it has become much easier to write sunqueries with MySQL.

The MySQL Track SCaLE 17x

The MySQL Track for the Southern California Linux Expo 17x has been published! On Friday march 9th you can learn about the MySQL Document Store, How to Deploy MySQL in Kubernetes, lean how Facebook achieves consistency with MySQL, MySQL Query Optimization, Analytics with MySQL, and MySQL Security. 

There are actually four days of the expo but the MySQL Track is on Friday, but you should stay around for the full weekend.  And MySQL is a sponsor and we will have a big booth with lots of swag, Be sure to stop by to say hello!

MySQL Shell 8.0.14 now with BSON

I have been asked the MySQL Shell handles BSON and now with today's release of version 8.0.,14, it does.  Version 8.0.13 added a very hand and blazing fast JSON bulk loader you get the ability to map and how the data is converted. The details are at the Conversions for representations of BSON data types page

From the 8.0.14 release notes:

 * The MySQL Shell JSON import utility can now process BSON
       (binary JSON) data types that are represented in JSON
       documents. The data types used in BSON documents are not
       all natively supported by JSON, but can be represented
       using extensions to the JSON format. The import utility
       can process documents that use JSON extensions to
       represent BSON data types, convert them to an identical
       or compatible MySQL representation, and import the data
       value using that representation. The resulting converted
       data values can be used in expressions and indexes, and
       manipulated by SQL statements and X DevAPI functions.
       To convert JSON extensions for BSON types into MySQL
       types in this way, you must specify the convertBsonTypes
       option when you run the import utility. Additional
       options are available to control the mapping and
       conversion for specific BSON data types. If you import
       documents with JSON extensions for BSON types and do not
       use this option, the documents are imported in the same
       way as they are represented in the input file.

One of the handier items is  the --decimalAsDouble switch to
convert the value of the BSON decimal type to a MySQL DOUBLE type instead of  a string.

One of the things to note is that the default option is that the documents are imported in the same way they were in the input file.  There ware ways to convert, decimals, dates, time stamps in the like but for those of you needed a quick upload of data from a MongoDB, this will be very useful.

Belgium! PHP Benelux, Pre-FOSDEM MySQL Day, and FOSDEM

I am getting ready to hit the road for the first time in 2019.  And the first stop is Belgium.

PHP Benelux is the first stop January 25 & 26, 2019 in Antwerp.  This is my firs time to this show but I have heard others rave about it for years and I am looking for to this show.

Next is an event on February 1st that is quickly filling up. The Pre-FOSDEM MySQL Day   Come hear session from the MySQL Engineering Teams, some other great technical speakers, and talk to them!  Great event but please reserve your space ASAP as this is a very popular event!

◾10:00–10:30 MySQL Shell – A devops-engineer day with MySQL’s development and administration tool by Miguel Araújo

◾10:35–11:05 MySQL Shell : The best DBA tool? by Frédéric Descamps

◾11:25–11:55 Tuning MySQL 8.0 InnoDB for high load by Dimitri Kravtchuk

◾13:30–14:00 New index features in MySQL 8.0 by Erik Frøseth

◾14:40–15:10 Regular expressions with full Unicode support by Martin Hansson

◾16:50–17:20 8 Group replication features that will make you smile by Tiago Vale

◾17:25–17:55 Document Store & PHP by David Stokes

And finally on Feb 2nd - 3rd is the chaotic FOSDEM.,  No pre-registration but we have a small room that gets packed quickly. And drop by the MySQL booth to talk MySQL and get some SWAG.