Category: Technical

I have a table of maps:

CREATE TABLE map (
    mapName varchar(255),
    mapType ENUM('continent', 'region', 'country'),
    continent varchar(255) NULL,
    region varchar(255) NULL,
    country varchar(255) NULL
);
INSERT INTO map VALUES 
    ('Europe', 'continent', 'Europe', NULL, NULL),
    ('North America', 'continent', 'North America', NULL, NULL),
    ('Northern Hemisphere', 'region', NULL, 'Northern Hemisphere', NULL),
    ('Sweden', 'country', NULL, NULL, 'Sweden'),
    ('Mexico', 'country', NULL, NULL, 'Mexico');

And a table of locations:

CREATE TABLE locations (
    locationId int auto_increment primary key,
    continent varchar(255),
    region varchar(255),
    country varchar(255)
);

INSERT INTO locations VALUES 
    (NULL, 'Europe', 'Northern Hemisphere', 'Sweden'),
    (NULL, 'North America',  'Northern Hemisphere', 'Canada'),
    (NULL, 'North America',  'Pacific Northwest', 'USA');

Obviously, this example is denormalized – in the real world, I’d use a surrogate key and store continentId, regionId, and countryId. Even then, it’s not the ideal layout for this application. But that’s just part of the challenge.

Here’s the goal: find the most specific map for each location. By most specific, I mean use a country map if available. If not, use a region map. Use a continent map as a last resort.

So, the result set should be:

+------------+---------------------+
| locationId | mapName             |
+------------+---------------------+
|          1 | Sweden              |
|          2 | Northern Hemisphere |
|          3 | North America       |
+------------+---------------------+

Bonus points if it works pre-4.1

Note the definition of map.mapType. In particular, the order of the values.

Then take a gander at section 3.6 of the manual.

SELECT 
    locationId,
    m.mapName
FROM 
    locations l
    JOIN map m ON (
        l.continent = m.continent
        OR l.region = m.region
        OR l.country = m.country
    )
    LEFT JOIN map m2 ON (
        (l.continent = m2.continent
        OR l.region = m2.region
        OR l.country = m2.country)
        AND CAST(m.mapType AS unsigned) < CAST(m2.mapType AS unsigned)
    )
WHERE
    m2.mapType IS NULL
GROUP BY 
    locationId;

SELECT 1 LIMIT 0;
Empty set (0.00 sec)

SELECT 2 LIMIT 0;
Empty set (0.00 sec)

SELECT NULL LIMIT 0;
Empty set (0.00 sec)

Exactly what I’d expect. So here’s the question:

SELECT '1 = 1' AS equality, (SELECT 1 LIMIT 0) = (SELECT 1 LIMIT 0) AS result
UNION SELECT '1 = 2', (SELECT 1 LIMIT 0) = (SELECT 2 LIMIT 0)
UNION SELECT '1 = NULL', (SELECT 1 LIMIT 0) = (SELECT NULL LIMIT 0);

What do you think? Are all empty sets created identical?
MySQL says no.

+----------+--------+
| equality | result |
+----------+--------+
| 1 = 1    |      1 |
| 1 = 2    |      0 |
| 1 = NULL |   NULL |
+----------+--------+

From a question on Freenode.

SELECT * FROM theTable;

+----+----------+
| id | data     |
+----+----------+
|  1 | middle   |
|  2 | first    |
|  3 | showLast |
+----+----------+

Fair enough. Let’s order it by `data`.

SELECT * FROM theTable ORDER BY data;

+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

What??? Maybe the server didn’t quite understand.

SELECT * FROM theTable ORDER BY data ASC;

+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

How did that happen? I assure you I have not switched to some ‘reverse order’ collation.

SHOW CREATE TABLE theTable;

CREATE TABLE `thetable` (
  `id` int(11),
  `data` enum('showLast','middle','first')
)

Ah. `data` is an enum. ORDER BY on an enum orders by the enum index, not the string. You can order by CONCAT(data) or by CAST(data AS char). Are there better ways?

A bit of a quiz to see if you’re paying attention:

First, we’ll set up a couple tables with some auto increment fields:

CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id));
CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, INDEX(id));

Next, we’ll insert a row to one table:

INSERT INTO t1 VALUES (NULL);

Then, we’ll see what happens when we insert two rows using LAST_INSERT_ID():

INSERT INTO t2 VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 2);

What happens here? Did the insert go ok, or did we get an error? What happens if we now issue

SELECT * FROM t2;

No errors, because we defined the auto_increment field for t2 not as a primary or unique key, but just with index. Remember that LAST_INSERT_ID() only picks up the last value you inserted using NULL, and not explicitly defined values. So we get two copies of the value ‘1’ in our id field.

+----+-------+
| id | refT1 |
+----+-------+
|  1 |     1 | 
|  1 |     2 | 
+----+-------+