How to force selectrow_array return wrong value?

Question

I remember having problem with DBI method selectrow_array. When i wasn't tidy enough i got back from it not the value of the column i asked, but count of columns (or something unwanted, i can't recall exactly). Now i try to refactor some code and i want to make sure in every possible place, that i get back only expected value. So i try to avoid surprises and find out which the bad behaviour was. From DBI docs i read that this may be really be problematic situation:

If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an "undef" is returned if there are no more rows or if an error occurred. That "undef" can't be distinguished from an "undef" returned because the first field value was NULL. For these reasons you should exercise some caution if you use "selectrow_array" in a scalar context, or just don't do that.

Still i can't force selectrow_array to return anything but value of the col1 (that's it what i am expecting)

my $query = 'SELECT col1, col2, col3 FROM table WHERE id = 112233';

my ( $c ) = ( $dbh->selectrow_array( $query ) );
my $x = ask_from_db();
my $y = $dbh->selectrow_array( $query );
my $z = ( $dbh->selectrow_array( $query ) );
my @A = $dbh->selectrow_array( $query );

say "C: $c"; # C: col1
say "X: $x"; # X: col1
say "Y: $y"; # Y: col1
say "Z: $z"; # Z: col1
say "A: @A"; # A: col1 col2 col3

sub ask_from_db {
  return $dbh->selectrow_array( $query );
}

Every way i ask above, gives me fine result. How should i run the query to get wrong result?

wrong result != col1 value

I like that question. But I have no idea. I'd have tried the same snippets you have. One way might be the goatse-operator: my $foo =()= $dbh->selectrow_array($query). But who would code that unintentionally, really? Writing this by accident is like accitentally falling on your mp3-player to get it stuck somewhere dark and warm... — simbabque
– simbabque, Commented Nov 23, 2012 at 15:41
@simbabque: No, unfortunately it was not so poetic, it was simple and clear but took hours to debug to understand that i had problem with context. I was so completly lost :) — w.k
– w.k, Commented Nov 23, 2012 at 20:34
@simbabque, That will assign the number of columns to $foo, not the value for the first column. — ikegami
– ikegami, Commented Nov 24, 2012 at 0:38
@ikegami: yes, that was one of my recall's that i got back number of columns and he proposed a way how it could happened. But like he said, it is gonna hardly happen that someone uses goatse-operator accidentally — w.k
– w.k, Commented Nov 24, 2012 at 0:44

ikegami · Accepted Answer · 2012-11-24 02:42:24Z

3

The difference in outcome will be based on the implementation of the driver.

wantarray ? @row : $row[0]

vs

wantarray ? @row : $row[-1]

You'd use to use a different driver to get a different outcome. That said, I imagine you'll have a hard time finding a driver that doesn't return the first.

If you want to be sure to get the first, use:

( $dbh->selectrow_array( $query ) )[0]

edited Nov 24, 2012 at 2:42

answered Nov 23, 2012 at 17:08

ikegami

391k17 gold badges291 silver badges555 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

w.k Over a year ago

I thought on this, but i was not sure, maybe this one give also some other column on occasion. That's why i wanted to find a way, how to test false value. But now i see, it won't, because it will be already in list context, when slice is coming in.

ikegami Over a year ago

No, always the first. This calls selectrow_array in list context, which returns all the columns, then picks out the first one. It's called a list slice.

w.k Over a year ago

I'm sorry if my comment was vague, that was exactly mine point too.

ErikR · Accepted Answer · 2012-11-23 15:28:03Z

3

What the documentation means by "it is undefined whether the driver will return the value of the first column or the last" is that the column returned is defined by the database driver and not DBI.

So the Postgres driver may decide to always return the first column whereas the mysql driver may always return the last column, or the column returned might depend on the query.

So don't call selectrow_array is scalar context - always call it in list context:

my @row = $sth->selectrow_array($query)

and you'll avoid all of the issues that the documentation mentions.

answered Nov 23, 2012 at 15:28

ErikR

52.2k9 gold badges78 silver badges136 bronze badges

1 Comment

w.k Over a year ago

It is my fault, i forgot to define my goal, why i want to return it in scalar context! I wanted to avoid intermediate variables in simple subroutines, so i could directly return values from DB call. When i can't be sure about getting right value, i must use assigning to @row.

Collectives™ on Stack Overflow

How to force selectrow_array return wrong value?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related