select * and bind_cols considered harmful

[blssu]

We've had terrible problems with bugs caused by developers using select * with bind_cols. Frequently the problem can not be reproduced on our development system. Table changes affect the order and/or number of columns, so the hard-coded variable list in bind_cols is a bug waiting to happen.

Selecting explicit columns fixes the problem, but then the column list is redundant with the bound variable list -- bad (short, weird abbreviations, etc.) variable names can be used because it's "obvious" what the variable holds.

I like fetchrow_hashref because it's safe to use with select *, self-documents use of database columns in the code, and does not have annoying redundancy between variable names and column names. Hakkr was probably thinking of these advantages.

[perrin]

Sorry, but I'm flabbergasted that anyone would ever use "select *" in a real program! That is the bug waiting to happen, not bind_cols.

I think typing out the list of lexical variables is worth the extra speed. There is some data on the speed difference in Tim Bunce's talk.

Of course, if speed makes no difference in your application then use whatever you like.

[blssu]

I'd categorize them as different kinds of bugs. The select * with fetchrow_hashref can lead to performance problems, but the bind_cols can completely break things.

During code reviews I tell people to select explicit columns and use fetchrow_hashref. I know people are sometimes going to break standards and ignore my advice. There are three options I know of: (1) give them standards that will probably keep working even if not perfectly followed, (2) audit all code for compliance, (3) fire people who write bad code.

Option (1) is my pragmatic choice. What's yours?

[perrin]

Easy, it's nothing personal. Select * with fetchrow_hashref will break in confusing ways if a column name is changed, but will survive columns being added. Of course performance will get progressively worse as columns get added even if they aren't being used. Regardless, select * is obviously a bad practice and fetchrow_hashref is just a band-aid that hides some of the badness. The DBAs at most jobs I've had would have hung me from the rafters if I used select * in production code.

[Abigail-II]

I've worked at a company who produced a large banking application (about 700 binaries and 200 dynamic libraries). We didn't have much coding standards, but among the few we had was "you shall never use 'select *'". Beside the problem mentioned that out of order applying of patches can produce different order of columns on tables, the other disadvantage of using 'select *' is that it's a performance hit. It means that for each time the query is compiled, the database has to do lookups and figure out what the columns will be.

'select *' is just there for interactive usuage - applications should never use them.

Abigail

[tommyw]

Well, select * is harmful anyway.

If you've got a perl program, and I add an extra column, or many columns, to the table you're using, then, even though these new columns are completely unnecessary in your program, it still retrieves the values, taking database, network and memory to do so (especially if, for example, I've added a graphic LOB).

You've got to name the keys of the hash to do anything useful with them, so you might as well name them in the select statement. And that way, if somebody adds extra columns, there are no side-effects. If the columns are renamed, you've got to bring your select statement into sync, but you've got to change your code anyway, to update the keys.

(This is unless you're writing something which merely dumps the entire table out for a human to look at, without really caring what the column values are.)

--
Tommy
Too stupid to live.
Too stubborn to die.

[Aristotle]

Redundant variable names are no excuse not to bind. Nor does SELECT * self-document at all, you have to go look at the following code to see which columns are really being asked for. And this being Perl, which has hashes and excellent text munging, there's no reason you need to be redundant either.

I shall paraphrase some code from chromatic's DBI is ok article on Perl.com:

sub bind_hash {
    my $table = shift;
    my %results;
    my $sql = do {
        local $" = ", ";
        "SELECT @_ FROM $table";
    };

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->bind_columns(map { \$results{$_} } @_);

    return (\%results, sub { $sth->fetch() });
}

# ... later:

my ($res, $fetch) = bind_hash users => qw(name email);
while ($fetch->()) {
    print "$res->{name} >$res->{email}>\n";
}

It's not optimal yet, but as you see, a little inventiveness goes a long way.

Makeshifts last the longest.