Blog

Bug or Not a Bug? – (part 1)

(EDIT: this article was published with a ‘part 1′ suffix in the title.  There will not be a’ part 2′. Read comments and you will understand why)

SELECT * FROM `first`;
/* returns
a
——
1
2
3
*/

CREATE TABLE IF NOT EXISTS `second`(`a` INT) SELECT * FROM `third`;

SELECT * FROM `first`;
/* returns
a
——
1
2
3
7
8
9
*/

Think about it!  Of course some special conditions will need to exist for this to be reproducible.

More details will appear here in a couple of days.

10 thoughts on “Bug or Not a Bug? – (part 1)

  1. `first` is a view of `second`, which already exists and is populated with the first three rows? The CREATE TABLE IF NOT EXISTS generates a warning, but the SELECT still runs to add the three rows from `third`?

    This behavior is described in the manual:

    “For CREATE TABLE … SELECT, if IF NOT EXISTS is given and the table already exists, MySQL handles the statement as follows:”

    http://dev.mysql.com/doc/refman/5.1/en/create-table.html

  2. Scott got it! That was much too fast! :-(

    Conditions are:
    1) `first` is a view of `second`, which already exists
    2) IF NOT EXISTS is used with CREATE table

    Refer to discussion:
    http://bugs.mysql.com/bug.php?id=47132

    Now with this discussion in mind: Bug or Not a Bug? What do people think about it? Is consistency/backwards compatibility more important than the risk of the unwanted INSERTS? Is there no solution that makes everybody happy?

    My personal opinion is that it should be realized that with the introduction of VIEWs also a very bad bug was introduced when using IF NOT EXISTS. But you will find my position in the discussion. You are also welcome to comment on my ‘style’ as well as the argumentation used by MySQL people. I know I was nasty, but I find the argumentation (“implemented and documened like that”, ‘intended behaviour”, “test cases assume this” etc.) almost irrelevant.

  3. correction rather:

    `second` already exists as a view defined on the table `first`

    - and that is the problem. Nobody would foresee INSERTS to the unreferenced table `first`.

  4. The full test case is this of course:

    CREATE TABLE `first`(a INT);
    CREATE TABLE `third`(a INT);
    INSERT INTO `first` VALUES (1),(2),(3);
    INSERT INTO `third` VALUES (7),(8),(9);
    CREATE VIEW `second` AS SELECT * FROM `first`;
    SELECT * FROM `first`;
    /* returns
    a
    ——
    1
    2
    3
    */
    CREATE TABLE IF NOT EXISTS `second`(a INT) SELECT * FROM `third`;
    SELECT * FROM `first`;
    /* returns
    a
    ——
    1
    2
    3
    7
    8
    9
    */

    I think I have made my position clear. CREATE TABLE IF NOT EXISTS operates differently *if table exists as base table* and *if table exists as view*. I think in both situations IF NOT EXISTS should ensure that no action is taken.

    Besides I will will not add more controversy about this. It was probably too much already. MySQL need to decide on this now. In this particular case it was also a complication that there is another related bug report that has been marked as private. I cannot and will not comment on that.

    Most important I believe the discussion revealed differences in the concept of what is considered most important. I believe that the most important thing with a database always is that you can rely on the data stored in it – that is so fundamental that everything else comes next in my opinion. Maybe I am just old-fashioned? I do not believe ‘availability of service’ is more important as the bugs.mysql.com guidelines seem to indicate. I get terrified by thinking of things like this happening in a banking system for instance. MySQL developers seem to have been focused so much on the crash with ‘view over multiple tables’ and related problems that data disappeared out of their mind.

    This discussion is not related to ‘view over multiple tables’ alone – but I was asked not to create another report so I didn’t.

    (and btw: there will not be a ‘part 2′ article as there is not more to say (damn Scott!))

  5. I thought it would be interesting to replace the VIEW with a TEMPORARY TABLE in the test case:

    CREATE TABLE `first`(a INT);
    CREATE TABLE `third`(a INT);
    INSERT INTO `first` VALUES (1),(2),(3);
    INSERT INTO `third` VALUES (7),(8),(9);
    CREATE TEMPORARY TABLE `second` AS SELECT * FROM `first`;
    SELECT * FROM `first`;
    /* returns
    a
    ——
    1
    2
    3
    */
    CREATE TABLE IF NOT EXISTS `second`(a INT) SELECT * FROM `third`;
    /*
    (3 row(s) affected, 1 warning(s))
    */
    SHOW WARNINGS;
    /*
    Level Code Message
    —— —— —————————–
    Note 1050 Table ‘second’ already exists

    .. what was actually the same warning as if `second` was a VIEW
    anyway still ..
    */
    SELECT * FROM `first`;
    /* returns
    a
    ——
    1
    2
    3
    .. so I do not understand how “CREATE … `second` …” affected 3 rows. Nothing was affected in my understanding.
    */

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>