Hi,

I am newbie. I am using ICE with jasper server.
In my OLAP schema, i have an inline table.
Following query is being generated by the jasperserver which is running on Infobright database

select `pt`.`id` as `c0`, `subs`.`value` as `c1`, `acc_g`.`type` as `c2`, date_format(request_date,’%Y’) as `c3`, date_format(request_date,’%m’) as `c4`, sum(`fd`.`usage_count`) as `m0` from `npg_page_type_view` as `pt`, `fact_details` as `fd`, (select 1 as `id`, ‘subscribed’ as `value` union all select 2 as `id`, ‘unsubscribed’ as `value` union all select 3 as `id`, ‘unknown’ as `value`) as `subs`, `npg_account_parent_view` as `acc_g` where `fd`.`page_type` = `pt`.`id` and `pt`.`id` in (‘19’, ‘20’, ‘1506’) and `fd`.`subscription` = `subs`.`value` and `subs`.`value` = ‘subscribed’ and `fd`.`account_id` = `acc_g`.`child_id` and `acc_g`.`type` = ‘Group’ and date_format(request_date,’%Y’) = ‘2010’ and date_format(request_date,’%m’) = ‘01’ group by c0, c1, c2, c3, c4

The result of this query is

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.

If i remove inline table from OLAP schema or replaces inline table with database table then it works fine.

Does it mean Infobright does not support inline tables in jasperserver?

Selecting from a select that is not a table is currently not supported with our optimizer. For example;

select * from (select ‘apples’) a;  < not supported

select * from (select ‘apples’ from my_table limit 1) a; < supported

If you can change your schema to not allow this construct, that would be the easiest thing to do.

As Carl stated, the problem is that the subselect is not from a table, so it is handled entirely within the MySQL optimizer. Then when it tries to join to an Infobright (Brighthouse) table, you get this conflict. (For the same reason we can’t join a MyISAM table to a BRIGHTHOUSE table).

You can create a small lookup table (brighthouse engine) and reference that instead.

Geoffrey

Thanks CarlGelbart and Falk for your valuable time.

Hi,

I have got one more query which is also not supported by Infobright Optimizer. Following is the query:

SELECT `pt`.`id` AS `c0`, `dim_subscriptions`.`value` AS `c1`, `acc_g`.`type` AS `c2`, DATE_FORMAT(request_date,’%Y’) AS `c3`, DATE_FORMAT(request_date,’%m’) AS `c4`, SUM(`fd`.`usage_count`) AS `m0` FROM `npg_page_type_view` AS `pt`, `fact_details` AS `fd`, `dim_subscriptions` AS `dim_subscriptions`, `npg_account_parent_view` AS `acc_g` WHERE `fd`.`page_type` = `pt`.`id` AND `pt`.`id` IN (‘19’, ‘20’, ‘1506’) AND `fd`.`subscription` = `dim_subscriptions`.`value` AND `dim_subscriptions`.`value` = ‘subscribed’ AND `fd`.`account_id` = `acc_g`.`child_id` AND `acc_g`.`type` = ‘Group’ AND DATE_FORMAT(request_date,’%Y’) = 2010 AND DATE_FORMAT(request_date,’%m’) = 01 GROUP BY c0, c1, c2, c3, c4;

This query does not have select inside select. Then why it is showing the following error? I have already replaced inline table with a new database table to avoid select inside select.

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.

Hi,

Are all the involved tables created as engine=‘brighthouse’?
Please try also changing 2010 and 01 into ‘2010’ and ‘01’ in WHERE part.

Regards,

Thanks Jakub for your prompt reply.
Replacing 2010 and 01 with ‘2010’ and ‘01’ worked for me. Thanks again.