I still disagree. I have heard of the DBI "throwing way rows", nor have I seen this behavior in a statement such as this. On the contrary, the DB engine may decide to perform a sort operation (Oracle will always perform a sort when there is a UNION involved) on the data and return the distinct rows.
Here are examples (along with explain plans) from Oracle 10.1.0.2.0 running from SQL*Plus: Case #1: select 'x' from dual union select 'x' from dual; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=2) 1 0 SORT (UNIQUE) (Cost=6 Card=2) 2 1 UNION-ALL 3 2 FAST DUAL (Cost=2 Card=1) 4 2 FAST DUAL (Cost=2 Card=1) Only 1 row was returned because of the sort operation. Case #2: select 'x' from dual union select 'y' from dual; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=2) 1 0 SORT (UNIQUE) (Cost=6 Card=2) 2 1 UNION-ALL 3 2 FAST DUAL (Cost=2 Card=1) 4 2 FAST DUAL (Cost=2 Card=1) Two rows returned because the data in the result set in not unique. Case #3: select 5 as a, 6 as b from dual union select 5 as a, 7 as b from dual; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=2) 1 0 SORT (UNIQUE) (Cost=6 Card=2) 2 1 UNION-ALL 3 2 FAST DUAL (Cost=2 Card=1) 4 2 FAST DUAL (Cost=2 Card=1) Two rows returned. Again, because the data in the result set is not unique. In the case you cite, both rows will be returned because the combination of columns are not unique, even though you aliased the column names, exactly as I did in case #3 above.. In Oracle, this is the expected behavior and I expect the same behavior when using the DBI/DBD modules. I have not tested this in Perl, but I am confident this outcome will be exactly the same as when using SQL*Plus. In fact, if the outcome were different, there would be quite a lot of noise from the end user community (myself included) and eventually a patch would be submitted from someone in the user community or from the DBD developers themselves. ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Avis, Ed [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 13, 2005 8:17 AM To: Reidy, Ron; [email protected] Subject: RE: selectall_hashref() when key field not unique Ron Reidy wrote: >>$dbh->selectall_hashref( >> 'select 5 as a, 6 as b union select 5 as a, 7 as b', >> 'a' >>); >I do not think this should raise either an error nor a >warning. In Sybase, what does the SQL statement return when >run in the SQL tool (iSQL?)? Does it return 1 row, or both >rows? I should have made clear that this is a query returning two rows: a b 5 6 5 7 The current DBI behaviour is to throw away one of those rows, but it's not documented which. Rather than silently throwing away the row, it might be better to flag up that the programmer has tried to use 'a' as a unique key, but it turned out not to be unique. That said, there may be cases where you prefer for DBI to silently discard all but one row with a given value of 'a'. -- Ed Avis <[EMAIL PROTECTED]> This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
