[ic] sql query with UNION broken

Jon Jensen jon at endpoint.com
Fri Aug 22 14:45:42 EDT 2003


On Fri, 22 Aug 2003, Bill Carr wrote:

> I am trying to do a SQL query using UNION. When I add parentheses around
> each of my SELECT statements I do not get the full results. I only get
> the values from the first column of the first row. I need the
> parentheses because I would like to sort the results of the UNION.
> 
> This gives the desired results:
> [query
>         list=1
>         sql=|
>                 SELECT 1,2
>                 UNION
>                 SELECT 3,4
>                 |
> ]
>         [list]
>                 [sql-pos 0], [sql-pos 1]<br>
>         [/list]
> [/query]
> Yields:
> 	1,2
> 	3,4
> 
> Adding parentheses breaks it:
> [query
>         list=1
>         sql=|
>                 (SELECT 1,2)
>                 UNION
>                 (SELECT 3,4)
>                 |
> ]
>         [list]
>                 [sql-pos 0], [sql-pos 1]<br>
>         [/list]
> [/query]
> Yields:
> 	2,
> 
> Is this bug? Any ideas?

I think you could call it a bug. It's caused by Vend::Table::DBI::query's 
logic determining what kind of statement it's been passed:

$update = 1 if $query !~ /^\s*select\s+/i;

Since your query starts with '(', it doesn't match that regex, and it's
being tagged as an UPDATE statement, and Interchange then returns the
number of rows affected, which for your query is the number of rows in the
result set = 2.

Perhaps we should switch the logic of the test, since UPDATE statements 
will be more homogeneous than SELECTs and also much rarer:

$update = 1 if $query =~ /^\s*update\s+/i;

But that would mess up DELETE, DROP INDEX/TABLE, CREATE INDEX/TABLE, etc.  
So maybe just a minor correction would be better:

$update = 1 if $query !~ /^\s*\W*\s*select\s+/i;

Comments?

Jon


More information about the interchange-users mailing list