Category: MySQL Gotchas

CREATE TABLE myTable (
 id INT,
 string varchar(100) DEFAULT NULL
);
INSERT INTO myTable (id, string) VALUES (1, "First"), (2, NULL);
UPDATE myTable SET string = CONCAT(id, " has the value ", string);
SELECT * FROM myTable;

What is the output?

+------+-----------------------+
| id   | string                |
+------+-----------------------+
|    1 | 1 has the value First |
|    2 | NULL                  |
+------+-----------------------+

What would result from the following?

CREATE TABLE my_table (
   id INT
);
INSERT INTO my_table (id) VALUES (2.9), (3), (3.4), (3.9), (4);
SELECT * FROM my_table AS m WHERE my_table.id > 3;

Answer:

ERROR 1109 (42S02): Unknown table 'my_table' in where clause.


(because it uses an alias for my_table, we should have said “WHERE m.id > 3"

Assume the following has completed correctly:

CREATE TABLE test (
   charField varchar(5)
);

What is the end difference between the following two statements?

INSERT INTO test (charField) VALUES ("123456");
INSERT INTO test (charField) VALUES ("12345 ");

Answer: In both cases, the inserted value is trimmed to ‘12345’. For the first, a warning is issued. For the second, only whitespace is trimmed, so no warning.

What would result from the following?

CREATE TABLE my_table (
id INT
);
INSERT INTO my_table (id) VALUES (2.9), (3), (3.4), (3.9), (4);
SELECT * FROM my_table AS m WHERE my_table.id > 3;

Answer:
ERROR 1109 (42S02): Unknown table 'my_table' in where clause.
(because it uses an alias for my_table, we should have said WHERE m.id > 3

Assume the following have completed:

GRANT ALL ON test1.* TO 'johnQ'@'localhost' IDENTIFIED BY 'pass1';
GRANT ALL ON test2.* TO 'johnQ'@'localhost' IDENTIFIED BY 'pass2';

User johnQ has a query that joins tables from test1 to tables from test2. How should he log in?

a) Using ‘pass1’.
b) Using ‘pass2’.
c) Using ‘pass1’ and ‘pass2’, separated by a colon.
d) It can’t be done; he must change the two passwords to match.
e) Other (explain)

Answer: b) Using ‘pass2’. Passwords are specific to the user/host combination, not to the table.

SELECT COUNT(DISTINCT someField) FROM someTable;
SELECT DISTINCT someField FROM someTable;

T/F: The value returned by the first will always equal the number of rows returned by the second.

Answer: False. COUNT() doesn’t count NULL, but SELECT DISTINCT will return a NULL. So the query using COUNT() could return one less row than the number of rows found with DISTINCT.

If I run the following, what will happen?

CREATE TABLE myTable (someField int, UNIQUE (someField));
INSERT INTO myTable VALUES (null);
INSERT INTO myTable VALUES (NULL);

Answer: Two rows containing NULL will be inserted. NULL’s might be unique, so the unique index allows multiple.

Assume the following has completed correctly:

CREATE TABLE test (
charField varchar(5)
);


What is the end difference between the following two statements?

INSERT INTO test (charField) VALUES ("123456");
INSERT INTO test (charField) VALUES ("12345 ");
Answer: In both cases, the inserted value is trimmed to ‘12345’. For the first, a warning is issued. For the second, only whitespace is trimmed, so no warning.