Archives

The List

« More Info on MySQL Certification | Main | MySQL Conference ‘07 Certification Exam Pass Rates »

MySQL Pop Quiz #5

By Carsten | February 20, 2008

Spanish flag Spanish translation by Marcos Besteiro

This thing is catching on … today’s question comes courtesy of Lasse Christiansen who is apparently residing in Japan (Lasse, correct me if I’m wrong).

Suppose you issue the following list of commands:

CREATE TABLE test (
  id varchar(1) NOT NULL,
  PRIMARY KEY  (id)
);
INSERT INTO test VALUES ('0'), ('1'), ('2'), ('a'), ('b');

The INSERT succeeds — nothing surprising here, the table now contains the values ‘0′, ‘1′, ‘2′, ‘a’ and ‘b’

Now you do:

DELETE FROM test WHERE id=0;

…and your client informs you that 3 rows were deleted.

Explain why.

Stuck? Click here for a hint.

Click here for the answer

If you have come across something that you think would make a good pop quiz question, send it to carsten (at) bitbybit (dot) dk. It’s OK if you don’t know the answer to your own question — that only makes things more interesting.More Pop Quiz entries.

Topics: MySQL, Pop Quiz |

11 Responses to “MySQL Pop Quiz #5”

  1. Hubert Roksor Says:
    February 20th, 2008 at 10:43 am

    “Explain why.”

    Because your sql_mode is too lax? ^^

    PS: keep up the good work with those pop quiz, they’re interesting and entertaining at the same time.

  2. Hubert Roksor Says:
    February 20th, 2008 at 10:47 am

    Oops, followin up to my previous comment it seems like I spoke too fast: no sql-mode will save you from this one unfortunately. :/

  3. Pieter Says:
    February 20th, 2008 at 4:35 pm

    Nice quiz :-)

  4. Shlomo Priymak Says:
    February 20th, 2008 at 6:20 pm

    This is just not right… Implicit conversion in this case is not a quirk one should be aware of, it’s broken behavior. The database should warn you when it does this, or just disallow it.

    I’ve tried the same in SQL Server 2005:

    CREATE TABLE test (
    id varchar(1) NOT NULL,
    PRIMARY KEY (id)
    );

    INSERT INTO test VALUES (’0′)
    INSERT INTO test VALUES (’1′)
    INSERT INTO test VALUES (’2′)
    INSERT INTO test VALUES (’a')
    INSERT INTO test VALUES (’b')

    DELETE FROM test WHERE id=0;

    And the result is
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value ‘a’ to data type int.

    Which makes a lot more sense to me…

    Good question though, keep them coming :)

  5. anon Says:
    February 20th, 2008 at 7:23 pm

    Because MySQL actually permits an implicit conversion to a number, resulting in the zero and all characters being equal to zero.

  6. Log Buffer #85: a Carnival of the Vanities for DBAs Says:
    February 22nd, 2008 at 8:03 pm

    [...] MySQL Pop Quiz #5 on Carsten’s Random Ramblings. Pencils poised . . .you may be [...]

  7. fenixshadow Says:
    February 25th, 2008 at 5:50 pm

    Cool quiz. Poor MySQL.

  8. MySQL Pop Quiz #8 | Carsten’s Random Ramblings Says:
    February 28th, 2008 at 8:31 am

    [...] Quiz #5, we looked at the following CREATE TABLE and [...]

  9. Propiedad Privada » Blog Archive » Test rápidos de MySQL - Test 5 Says:
    March 18th, 2008 at 12:57 pm

    [...] Traducido con permiso expreso de Mysql Test Quiz 5 Original [...]

  10. Guerrero de Terracota » MySQL Pop Quiz #5 Says:
    March 31st, 2008 at 9:08 am

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

  11. Log Buffer #85: A Carnival of the Vanities for DBAs Says:
    February 11th, 2013 at 10:31 pm

    [...] MySQL Pop Quiz #5 on Carsten’s Random Ramblings. Pencils poised . . .you may be [...]

Comments