Infobright.org Forums |

Posted: 31 August 2010 08:06 AM

[ Ignore ]

Newbie

Total Posts: 24

Joined 2010-03-29

Hi !

It seems that ICE does not support LEFT JOIN syntax with many conditions i.e. LEFT JOIN on (condition1 AND condition 2). ICE sends the following error message :

Error Code: 5
The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.

I searched the forums and indeed, this problem has been reported e.g. in this thread, which proposes too complex a solution in my opinion. There is also this more recent thread, but with no definitive answer.

So, may I ask :

1) why is left join with more than one condition not supported ?
2) will this be soon ?
3) meanwhile, is there a workaround, a simpler one than suggested in the aforementioned thread ?

Thanks,

Martin.

Posted: 31 August 2010 08:20 AM

[ Ignore ] [ # 1 ]

Sr. Member

Total Posts: 735

Joined 2008-08-18

Hi,

Please provide the whole query, if it is possible. In general, ICE does support multiple conditions in LEFT JOIN, but there are some special cases when it does not (e.g. for some combinations of multiple joins).

Regards,

Signature

Posted: 31 August 2010 12:13 PM

[ Ignore ] [ # 2 ]

Newbie

Total Posts: 24

Joined 2010-03-29

Hi !

Here is the query :

select count(*) from ib_assays a left join ib_all_bysample i on (a.name=i.assay and i.sample='NA18484') ; 

If need be, I can provide the table structures.

Posted: 08 September 2010 10:39 AM

[ Ignore ] [ # 3 ]

Newbie

Total Posts: 24

Joined 2010-03-29

Hi !

I rearranged my original query in the following way :

select count(*) from ib_assays a left join (select * from ib_all_bysample where sample='NA18484') as i on (a.name=i.assay) ; 

which seems to work fine. However, I think this rearrangement pattern is not applicable to all multi contidion left join patterns. Or is it ? Any other suggestions ?

Any plans on allowing many conditions in left join clauses soon ?

Cheers,

Martin.

Posted: 08 September 2010 11:45 AM

[ Ignore ] [ # 4 ]

Sr. Member

Total Posts: 735

Joined 2008-08-18

Hi,

First of all, the original query should work - at least I can’t reproduce any issues on similar queries on my data.
ICE is supporting multiple conditions in left join, especially for two-table joining (any problems may occur only for more than two tables). Maybe you’re using an old version of ICE? The problems described in the other thread are already solved.

Regards,

[ Edited: 08 September 2010 11:55 AM by Jakub Wroblewski]

Signature

Posted: 08 September 2010 12:03 PM

[ Ignore ] [ # 5 ]

Newbie

Total Posts: 24

Joined 2010-03-29

Hi !

As originally stated, I get an error message saying the syntax is not supported. I run version : Server version: 5.1.39 MySQL Community / InfiniDB Community Edition 1.0.3-3 Final (GPL).
Cheers,

Martin.

Posted: 08 September 2010 12:10 PM

[ Ignore ] [ # 6 ]

Sr. Member

Total Posts: 735

Joined 2008-08-18

Hi,

statgen - 08 September 2010 12:03 PM
Server version: 5.1.39 MySQL Community / InfiniDB Community Edition 1.0.3-3 Final

I think this version should not generate messages with “Infobright” inside.

Regards,

Signature

Posted: 08 September 2010 12:16 PM

[ Ignore ] [ # 7 ]

Newbie

Total Posts: 24

Joined 2010-03-29

Hi !

My bad. Here is the correct version, along with the error message :

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1065
Server version: 5.1.40-log build number (revision)=IB_3.3.1_r6997_7017(ice)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test_infobright;
Database changed
mysql>
mysql>
mysql> select count(*) from ib_assays a left join ib_all_bysample i on (a.name=i.assay and i.sample='NA18484') ;
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.
mysql> 

Cheers,

Martin

Posted: 08 September 2010 12:19 PM

[ Ignore ] [ # 8 ]

Sr. Member

Total Posts: 735

Joined 2008-08-18

Hi,

Not sure about 3.3.1, but 3.4.2 should work for this query - please try to upgrade.

Regards,

Signature

David Lutz

Posted: 08 September 2010 12:28 PM

[ Ignore ] [ # 9 ]

Sr. Member

Total Posts: 738

Joined 2008-08-18

statgen - 31 August 2010 12:13 PM

select count(*) from ib_assays a left join ib_all_bysample i on (a.name=i.assay and i.sample='NA18484') ;

Would it be possible to try the query with the filter in a WHERE clause, such as:

select count(*) from ib_assays a left join ib_all_bysample i on a.name=i.assay where i.sample='NA18484'; 

Signature

Posted: 08 September 2010 12:38 PM

[ Ignore ] [ # 10 ]

Sr. Member

Total Posts: 735

Joined 2008-08-18

Hi,

“Where” version will produce different result. Actually it will filter out all “outer” tuples, i.e. these where NULL is placed in the second table.

Regards,

Signature

Posted: 08 September 2010 12:41 PM

[ Ignore ] [ # 11 ]

Newbie

Total Posts: 24

Joined 2010-03-29

David Lutz - 08 September 2010 12:28 PM

select count(*) from ib_assays a left join ib_all_bysample i on a.name=i.assay where i.sample='NA18484';

Hi !

Unless I am badly mistaken, this query is not equivalent to the original one. It will not return rows for which there is null in the left joined table.

David Lutz

Posted: 08 September 2010 01:12 PM

[ Ignore ] [ # 12 ]

Sr. Member

Total Posts: 738

Joined 2008-08-18

statgen,

It was I who was badly mistaken. You are correct.

Apologies for the invalid suggestion.

Signature