Crash using duplicate field names in Union Sql

[Erne]

Hello list,

I did report this a while ago:

On 20-ago-07, at 13:49, Erne wrote:

> Hello Ruslan,
>
> I just found a bug that makes Vale crash when executing a Union Sql.
>
> This one crashes:
>
> select a.recid, b.recid from myBO1 a, myBO2 b, myB_myB_Linker ab  
> where a.recid = 1
> union (select a.recid, b.recid from myBO1 a, myBO2 b,  
> myB_myB_Linker ab where a.recid = 2)
>
> This one works:
>
> select a.recid as aid, b.recid as bid from myBO1 a, myBO2 b,  
> myB_myB_Linker ab where a.recid = 1
> union (select a.recid as aid, b.recid as bid from myBO1 a, myBO2 b,  
> myB_myB_Linker ab where a.recid = 2)
>
>
> Difference is that 2nd query uses "as aid" and "as bid" fields  
> aliases for the Recid
>
> in fact this one also crashes:
>
> select a.Field1, c.Field1 from myBO1 a, myBO2 b, myBO3 c,  
> myB_myB_Linker ab, myB_myB_Linker1 ac where a.recid = 1
> union (select a.Field1, c.Field1 from myBO1 a, myBO2 b, myBO3 c,  
> myB_myB_Linker ab, myB_myB_Linker1 ac where a.recid = 2)
>
> because it uses 2 "Field1" fields with the same name from 2  
> different tables
>
>
> I think it should work since the difference is defined by the "a.",  
> "b." and "c." Table aliases,
> and anyway it shouldn't crash ;-)
>
>

it is claimed to be fixed in version 3.3 and indeed it doesn't crash  
anymore but throws this error:
Kernel error: 0x80501. Field name "RecID" is not unique.

still I don't see why the use of a field alias is required here

ok, the output cursor would have duplicate field names but why is it  
allowed for non UNION queries?

I guess it's because kernel evolves to more strict requirements as  
I've found this one also:

select a.recid as aid, b.recid as bid from myBO1 a, myBO2 b,  
myB_myB_Linker ab where a.recid = 1
union (select a.recid as aid, b.recid as bid from myBO1 a, myBO2 b,  
myB_myB_Linker ab where a.recid = 2)
Order by a.Field1

throws this message:
Kernel error: 0x70507. The column prefix "a" does not match with a  
table name or alias name used in the query.

which is incorrect, it should say "Can't Order by a field not present  
in the Select clause" or something like that

again this is allowed in non Union queries (very useful indeed!) but  
restricted in more elaborate ones

Well, I guess there's good reason for this

I'll report just the wrong error message to Mantis


Cool Runnings,
Erne.