use Perl

• USING is bad anyway (Score:1)

  by Aristotle (5147)

  I prefer to name my primary key column `id` and use `foo_id` as the name for foreign key columns. Then I use `ON` explicitly.

  LEFT JOIN dead_things ON thing.id = dead_things.thing_id

  Overall the query becomes more verbose, but I prefer it that way. It makes my schemata much more easily readable when columns immediately reveal themselves for what they are by their name.
  • Re: (Score:2)

    by Ovid (2709)

    That still doesn't fix the bug :)
    • Re: (Score:2)

      by jdavidb (1361)

      I don't actually see the bug, yet, but I'm still a little groggy this AM. I do agree with your principle. But you may have to help me along to see this specific bug. :)

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • Re:USING is bad anyway (Score:2)

        by Ovid (2709) on 2006.10.03 8:20 (#50708) Homepage Journal

        You know, this might be a MySQL-specific bug. I don't have other databases handy on which to test this. I tried it in SQLite and the problem doesn't appear:

        ~ $ sqlite3 foo
        SQLite version 3.2.8
        Enter ".help" for instructions
        sqlite> create table one (server, name);
        sqlite> create table two (server, value);
        sqlite> insert into one values(1,'bob');
        sqlite> insert into one values(2,'ovid');
        sqlite> insert into two values(1,'one');
        sqlite> select * from one left join two using(server);
        1|bob|one
        2|ovid|

        With MySQL, that SELECT statement was returning data more like this:

        1|bob|1|one

        As a result, because it was a left join, the duplicated join value was sometimes null and when shoved into a hash, overwrote the original value. However, USING doesn't have this problem in SQLite, but it does with ON:

        sqlite> select * from one left join two on one.server = two.server;
        1|bob|1|one
        2|ovid||
        sqlite>

        Reply to This

        Parent

        • Re: (Score:2)

          by jdavidb (1361)

          I think it's definitely MySQL specific ... it sounds like MySQL doesn't actually know how to do that type of join properly?

          But I'm confused about something else ... I thought selectrow_hashref should give you a hash where the keys are the fieldnames and the values are the values in the record. You seem to be indicating a problem where one of the values is NULL and that gets used as a key in the hash? But I can't see how that would happen, unless I'm misunderstanding the expected results of selectrow_has

          --
          J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
          • Re: (Score:2)

            by jdavidb (1361)

            You seem to be indicating a problem where one of the values is NULL and that gets used as a key in the hash?

            No, I misunderstood. Never mind. :)

            --
            J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers