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: (Score:2)

        by Ovid (2709)

        Major Hint: what are we joining on and what data structure is Perl using?
      • Re: (Score:2)

        by Ovid (2709)

        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|ov
        • 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
    • Re: (Score:1)

      by Aristotle (5147)

      I’m saying that you got bitten this easily because you combined two bad ideas.

      Although now that I think of it, the naming scheme I use means that any join would lead to duplicate id columns.

      Good thing I avoid SELECT * like the pest, then. Hmm, looking at some of my own source code, I find I do use the star quite a lot in the older parts. However, it’s always qualified with a table name, which explains why I never ran into your bug.
• Bug depends on the DB (I think) (Score:2)

  by Matts (1087)

  Some DBs would return that as "Table.Column", unless it's DBI always turning it into just "Column".
  
  I agree on the principle though - SELECT * should probably only be used in the SQL-Shell for testing stuff out.
  • Re: (Score:2)

    by jdavidb (1361)

    Ah, I can see how that would be a problem, if you wound up with two fields in different tables with the same name. But I'm used to the syntax of NATURAL JOIN and didn't look up the syntax of LEFT JOIN to see if it removes the duplicated column from the output or not.

    --
    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
• "SELECT *" encouraged in some environments (Score:1)

  by runrig (3385)

  In the 4gl I used, when you had to explicitly declare variables, you could declare a record 'like' a table, then 'select *' into the record. This was extremely convenient on tables with many columns. Unfortunately, that meant that you had to recompile the program if the table changed.

  And Matt is correct in that some databases return 'table.column' as the column name (as the hash key or in $sth->{NAME}), which is annoying when switching between databases.