TOPIC: Bug on UNION queries ?
#1846
Bug on UNION queries ? 7 Months ago
I was trying some queries and found a bug on UNION Queries:
With the table bellow and trying the UNION Query I discover that when we use the same field on the where clause on both sides of a union only one goes to the final optimized query, the last one.

Try this table and the union query if you change the order of the union you get different results because only the last where cluse is used on UNIONS.

dad_products_groups
(_update_count_, active, cdate, description,
group_id, mdate_TS, notes, parent)
key autoincrement(group_id)
key (description,parent)
index (parent) in dad_products_groups(group_id)

Foreign Keys
dad_products index (group_id) in dad_products_groups
dad_products_groups index (parent) in dad_products_groups(group_id)

dad_products_groups
where parent isnt ''
project group_id, parent, description
union
dad_products_groups
where parent is ''
project group_id, parent, description
 
 
#1847
Re:Bug on UNION queries ? 7 Months ago
I haven't tested this but sometimes you need to put brackets () around the individual queries that you are union'ing. This should prevent the query optimizer from moving the 'where' to after the union is done.
 
 
Jeff Ferguson
Suneido Software
 
#1848
Re:Bug on UNION queries ? 7 Months ago
That's true, with brackets it works as expected:

Code:


(dad_products_groups
where parent isnt ''
project group_id, parent, description)
union
(dad_products_groups 
where parent is ''
project group_id, parent, description)



And now that you've mentioned brackets there is an issue with the "if" statement with brackets:

Code:


a=98
b=87
if (a is b) or (a is 0)
Print(a)



The above code isn't accepted by suneido we need to surround both expressions passed to "if" to be accepted:

Code:


a=98
b=87
if ( (a is b) or (a is 0) )
Print(a)



And another issue is that suneido doesn't have a printf like function and the lack of it makes the translation module to be a lot inefficient because some strings passed to "Statusbar", "Alert", ..., are strings joined with parameters like line numbers, timestamps, function names, ..., this invalidate the translation cache very quickly.

In my opnion functions like "Alert" and others that shows content for users should have printf like parameters:

Alert('Error at line: %d', line_number)

This way the translation string "Error at line: %d" will be fixed and will not invalidate the translation cache so often.
 
 
Last Edit: 2010/01/21 18:23 By mingodad.
 
#1858
Re:Bug on UNION queries ? 7 Months ago
I will look at the query issue when I am back at work. I am not sure why it needs the parenthesis.

TranslateLanguage does support "arguments" (similar to printf). I can not remember how it works. It should be caching before filling in the arguments but I am not sure if it does. However, other functions like Alert probably do not handle arguments - I agree they should.

I am aware of the issue with if (...) or (...) - it is because the parser sees the opening parenthesis after the if and assumes the expression is parenthesized. I am not sure how to fix this (without breaking anything else!) I think it either requires backtracking (which the parser currently does not do anywhere) or complicated lookahead.

This should probably be three separate forum topics :-)
 
 
andrew