Perl DBI fetchall_arrayref - puzzling behaviour

kiat has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks,

I'm puzzled by the behaviour of the following snippet of code:

my $query = 'apple';
my $sql = qq{ SELECT COUNT(*)
  FROM table1
  WHERE name=?
  UNION SELECT COUNT(*) FROM table2
  WHERE name=?
};

my $sth->execute($sql, $query, $query)
my $rows = $sth->fetchall_arrayref();

#table1
apple
apple
orange
pear

#table2
pear
apple
orange
orange
[download]

Given a query to count the number of rows containing 'apple' from the above SQL, $rows returns:

$VAR1 = [ [ '2' ], [ '1' ] ];
[download]

That's correct. However, when the contents of the tables change as follows (1 more 'apple' added to table2):

#table1
apple
apple
orange
pear

#table2
pear
apple
orange
orange
apple
[download]

That is, two instances of 'apple' in table1 and two in table2. In this case, $rows returns:

$VAR1 = [ [ '2' ] ];
[download]

That is, a single annonymous array in an AoA.

I was expecting $VAR1 to contain:

$VAR1 = [ [ '2' ], [ '2' ] ];
[download]

Could someone enlighten me on what's going there?

Thanks in anticipation :)

Comment on Perl DBI fetchall_arrayref - puzzling behaviour Select or Download Code

Replies are listed 'Best First'.
Re: Perl DBI fetchall_arrayref - puzzling behaviour by gellyfish (Monsignor) on Jul 16, 2004 at 13:16 UTC
I think you will find it is the behaviour of your database server rather than the DBI - if you change the SQL to have `UNION ALL` rather than simply `UNION` I'm fairly certain you will see what you expect. /J\	[reply] [d/l] [select]
Re^2: Perl DBI fetchall_arrayref - puzzling behaviour by kiat (Vicar) on Jul 16, 2004 at 13:21 UTC
Thank you so much, gellyfish! You're abolutely right :) I added the ALL and now I get both numbers when the are the same. But, if you don't mind, what's the difference between UNION and UNION ALL? Thanks once again and cheers, Update: From MySQL FAQ If you don't use the keyword ALL for the UNION, all returned rows will be unique, as if you had done a DISTINCT for the total result set. If you specify ALL, you will get all matching rows from all the used SELECT statements.	[reply]
Re^3: Perl DBI fetchall_arrayref - puzzling behaviour by dragonchild (Archbishop) on Jul 16, 2004 at 13:27 UTC
Think in terms of sets. A set is, mathematically, a bunch of unique items. So, when you union two sets, you take all the unique items that appear in either set. '2' already appeared, so it won't be added again the second time its seen. (It's not exactly like that, but that's the basic idea.) ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply]
Re^4: Perl DBI fetchall_arrayref - puzzling behaviour by kiat (Vicar) on Jul 16, 2004 at 13:33 UTC
Re^3: Perl DBI fetchall_arrayref - puzzling behaviour by gellyfish (Monsignor) on Jul 16, 2004 at 13:33 UTC
I think it would be easier to quote the documentation: `UNION Specifies that multiple result sets are to be combined and returned as + a single result set. ALL Incorporates all rows into the results, including duplicates. If not s +pecified, duplicate rows are removed.` [download] /J\ _{janitored by ybiC: Replace <pre> tags with <code> to avoid lateral scrolling}	[reply] [d/l]