Archives

The List

« MySQL Pop Quiz | Main | MySQL Pop Quiz #3 »

MySQL Pop Quiz #2

By Carsten | February 14, 2008

Spanish flag Spanish translation by Marcos Besteiro

Yesterday’s Pop Quiz seemed quite popular, I got some personal responses and a couple of funny comments.

So here’s another one:

The table t is defined thusly on a MySQL server with no sql modes set:

  CREATE TABLE t (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
  );

You now do…

  INSERT INTO t (id)
  VALUES (NULL);

  SELECT *
  FROM t
  WHERE id IS NULL

What will be the result of the SELECT?


Click here to show answer

Topics: MySQL, Pop Quiz |

11 Responses to “MySQL Pop Quiz #2”

  1. gigiduru Says:
    February 14th, 2008 at 11:20 pm

    This is what will happen:

    mysql> show create table t1\G
    Table: t1
    Create Table: CREATE TABLE `t1` (
    `id` int(11) NOT NULL auto_increment,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

    mysql> insert into t1 (id) values (NULL);
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t1 where id IS NULL;
    +—-+
    | id |
    +—-+
    | 1 |
    +—-+
    1 row in set (0.00 sec)

    mysql> select * from t1 where id IS NULL;
    Empty set (0.00 sec)

    I swear that they are one after another, 2 seconds apart on the same connection. I love Oracle.

  2. vivo Says:
    February 15th, 2008 at 12:09 am

    re: gigiduru
    … the last generated auto_increment …
    so the select act as a call to mysql_insert_id() [1], so all it’s restrictions apply

    [1]http://dev.mysql.com/doc/refman/5.0/en/mysql-insert-id.html

  3. Diego Says:
    February 15th, 2008 at 12:38 am

    The published select actually gives you an error as it is not selecting any columns. But if you do select * …
    as gigiduru wrote on 5.0.45, the second time it returns an empty resultset.

  4. gigiduru Says:
    February 15th, 2008 at 12:42 am

    “Certain ODBC applications (including Delphi and Access) may have trouble obtaining the auto-increment value using the LAST_INSERT_ID() examples.”

    I’m not buying that bs. LAST_INSERT_ID() should suffice and it would be the equivalent of CUR_VAL() in a sequence. Let THOSE ODBC applications fix their s*&t, including Delphi and Acces. Changing server behaviour in this way is not quite acceptable.

  5. Morgan Tocker Says:
    February 15th, 2008 at 7:32 am

    mysql> SELECT FROM t WHERE id IS NULL;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM t WHERE id IS NULL’ at line 1

    You forgot a ‘*’ before the FROM.

  6. Carsten Says:
    February 15th, 2008 at 9:36 am

    Diego, Morgan: indeed I did. That’s what you get for writing when you should be sleeping. Now corrected. Thanks!

  7. fenixshadow Says:
    February 15th, 2008 at 9:43 am

    We can not satisfy everybody , just do the right thing.
    “Let THOSE ODBC applications fix their s*&t, including Delphi and Acces. Changing server behaviour in this way is not quite acceptable.”

  8. gica Says:
    February 15th, 2008 at 12:11 pm

    If you run the statements via JDBC the answer is correct (no rows selected).

  9. Scott Noyes Says:
    February 15th, 2008 at 3:57 pm

    gigiduru: The behavior you note (SELECT..WHERE id IS NULL returns different results) is by design, although poorly documented. See Evgeny Potemkin’s comment at http://bugs.mysql.com/bug.php?id=14553

  10. Propiedad Privada » Blog Archive » Test rápidos de MySQL - Test 2 Says:
    February 28th, 2008 at 6:36 pm

    [...] con permiso expreso de Mysql Test Quiz 2 Original, creado por Carsten H. [...]

  11. Guerrero de Terracota » MySQL Pop Quiz #2 Says:
    March 31st, 2008 at 9:00 am

    [...] ???????????? ? ?????????? Carsten Pedersen. ???????? [...]

Comments