Category: MySQL Gotchas

CREATE TABLE secretData (
    secretValue int COMMENT 'If this goes over 5, WWIII will start'
);

CREATE SQL SECURITY DEFINER VIEW censoredData AS 
SELECT * FROM secretData WHERE secretValue < 5 WITH CHECK OPTION;

GRANT SELECT, INSERT ON test.censoredData TO 'evilFiend'@'%';

<telephone> ring ring

<sysadmin> "Hello?"

<evilFiend> "I'd like to create an insertable view on some tables I already have rights to. I don't know just yet what I'll use for my select statement."

<sysadmin> "Ok. I'll set it up so you can do what you'd like."

CREATE SQL SECURITY INVOKER VIEW evilFiendsView AS SELECT 1;
GRANT SELECT, INSERT, ALTER ON test.evilFiendsView TO 'evilFiend'@'%';

evilFiend connects to the server, while twiddling the end of his handlebar mustache.

ALTER VIEW evilFiendsView AS 
SELECT * FROM censoredData WITH LOCAL CHECK OPTION;

INSERT INTO evilFiendsView VALUES (42);

Muhahaha!

Time for another MySQL Community Quiz:

Everybody knows that LAST_INSERT_ID() returns the value most recently created in an auto_increment column. Fewer of us know that you can pass a value to LAST_INSERT_ID(), and that value will be returned for the next call to LAST_INSERT_ID(). For example,

INSERT INTO table (someNonAutoIncrementField) VALUES (LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

The INSERT inserts ’42’ into the table, and the SELECT returns 42.

So, here’s the question: What if we pass an expression to LAST_INSERT_ID and also use an auto_increment field in the same statement?

CREATE TABLE test (id int auto_increment primary key, field int);
INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

You get the auto_increment field, at least in this case:

mysql> INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

And for a follow up, does it matter if you swap the insertion around?

INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
SELECT LAST_INSERT_ID();

Nope.

mysql> INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.

First, the setup:

CREATE TABLE `user` (
  `user` varchar(255) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL
);

INSERT INTO `user` 
    (`user`, `host`, `sort`) 
VALUES 
    ('','%',8),
    ('testUser','%',7),
    ('','%localhost',9),
    ('testUser','%localhost',5),
    ('','%localhost%',10),
    ('testUser','%localhost%',6),
    ('','localhost',2),
    ('testUser','localhost',1),
    ('','localhost%',4),
    ('testUser','localhost%',3);

Now, the quiz:

SELECT * FROM user ORDER BY ___

Fill in the blank to get the following output. Difficulty: the `sort` field may not appear anywhere in the query.

+----------+-------------+------+
| user     | host        | sort |
+----------+-------------+------+
| testUser | localhost   |    1 |
|          | localhost   |    2 |
| testUser | localhost%  |    3 |
|          | localhost%  |    4 |
| testUser | %localhost  |    5 |
| testUser | %localhost% |    6 |
| testUser | %           |    7 |
|          | %           |    8 |
|          | %localhost  |    9 |
|          | %localhost% |   10 |
+----------+-------------+------+

Culled from the Certification Study Guide:

mysql> SELECT * FROM test;
+----------------+
| data           |
+----------------+
| This is a test |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(data) FROM test;
+----------------+
| UPPER(data)    |
+----------------+
| This is a test |
+----------------+
1 row in set (0.03 sec)

How’s that work?

 CREATE TABLE `test` (
   `data` varbinary(255) default NULL
 );

Binary strings are just a list of bytes. They aren’t characters anymore, so there is no upper or lower case. Remember that when choosing between char/varchar/text and binary/varbinary/blob.

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 | 
+----+-------+

Straight quotes can be used like backticks around aliases IN SOME CASES (as of 5.0.15, at least). Observe:

CREATE TABLE myTable (id int, someData int);
INSERT INTO myTable VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2);

These three queries produce identical output:

SELECT * FROM myTable GROUP BY id;
SELECT * FROM myTable GROUP BY `id`;
SELECT * FROM myTable GROUP BY 'id';
(more…)

Here are a couple table definitions:

CREATE TABLE `users1` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);

CREATE TABLE `users2` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);

After inserting some data, we get the following row counts:

SELECT COUNT(*) FROM users1;
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+

SELECT COUNT(*) FROM users2;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+


Now, for this query:

SELECT * FROM users1 UNION SELECT * FROM users2;

How many rows will be in the result set? (Choose the best answer)

a) Exactly 27
b) At least 12
c) At least 15
d) Between 1 and 27
e) Zero or more

d) Between 1 and 27. Default behavior for UNION is DISTINCT. If all 27 rows in the two tables are identical, there will be only 1 row in the result. If all are unique, there will be 27 rows.