Joinutility seperatorLogin utility separator Infobright.com
   
 
Why this query is not supported by Infobright?
 
arunsinghal01
Posted: 29 June 2010 08:16 AM   [ Ignore ]  
Newbie
Rank
Total Posts:  17
Joined  2010-04-19

Hi,

I am running following query on Infobright.

SELECT `pt`.`parent_id` AS `c0`, `dim_subscriptions`.`value` AS `c1`, `acc_g`.`type` AS `c2`, `fd`.`year_num` 
AS `c3`, `fd`.`month_num` AS `c4`, SUM(`fd`.`usage_count`) AS `m0` FROM `sprotocols_page_type_view` AS `pt`, 
`fact_details_degenerated` AS `fd`, `dim_subscriptions` AS `dim_subscriptions`, `sprotocols_account_parent_view` 
AS `acc_g` WHERE `fd`.`page_type` = `pt`.`child_id` AND `pt`.`parent_id` = 115 AND `fd`.`subscription` = `dim_subscriptions`.`value` 
AND `dim_subscriptions`.`value` = 'subscribed' AND `fd`.`account_id` = `acc_g`.`child_id` AND `acc_g`.`type` = 'Institution' 
GROUP BY c0, c1, c2, c3, c4 

This is giving following error.
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.

How can i improve this query?
Note: table names starting with dim are dimension tables.
table names starting ending with view are database views on dimension tables.
table name starting with fact is fact_table.

Profile
 
Janusz Borkowski
Posted: 29 June 2010 08:44 AM   [ Ignore ]   [ # 1 ]  
Super Duper Member
Avatar
RankRankRankRankRank
Total Posts:  915
Joined  2008-08-18

Hi !

Possibly the definition of sprotocols_page_type_view or sprotocols_account_parent_view (views?) is not supported by ICE…

Profile
 
arunsinghal01
Posted: 29 June 2010 08:59 AM   [ Ignore ]   [ # 2 ]  
Newbie
Rank
Total Posts:  17
Joined  2010-04-19

Hi,

Thanks for the prompt reply. Here are the table and view structures.

CREATE VIEW `sprotocols_page_type_view` AS 
SELECT
  `dapc`.`parent_id`   AS `parent_id`,
  `dapc`.`child_id`    AS `child_id`,
  `da`.`display_name`  AS `name`,
  `da2`.`display_name` AS `child_name`,
  `da`.`display_order` AS `display_order`
FROM ((`dim_pagetype_parent_child` `dapc`
    LEFT JOIN `dim_page_types` `da`
      ON (((`dapc`.`parent_id` = `da`.`id`)
           AND (`da`.`webmart_id` = 1101))))
   LEFT JOIN `dim_page_types` `da2`
     ON (((`dapc`.`child_id` = `da2`.`id`)
          AND (`da2`.`webmart_id` = 1101))))
WHERE (`dapc`.`webmart_id` = 1101); 
CREATE TABLE `dim_page_types` (
  `id` int(4) NOT NULL,
  `webmart_id` smallint(6) NOT NULL,
  `page_type` varchar(100) NOT NULL DEFAULT '',
  `display_name` varchar(100) NOT NULL,
  `display_order` tinyint(4) DEFAULT '0'
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

CREATE TABLE `dim_pagetype_parent_child` (
  `webmart_id` smallint(6) NOT NULL,
  `parent_id` smallint(6) NOT NULL,
  `child_id` smallint(6) NOT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8; 

 

CREATE VIEW `sprotocols_account_parent_view` AS 
SELECT
  `dapc`.`parent_id` AS `parent_id`,
  `dapc`.`child_id`  AS `child_id`,
  `da`.`name`        AS `name`,
  `da2`.`name`       AS `child_name`,
  `da`.`type`        AS `type`,
  `da2`.`city`       AS `city`,
  `da2`.`state`      AS `state`,
  `da2`.`country`    AS `country`,
  `da2`.`region`     AS `region`
FROM ((`dim_account_parent_child` `dapc`
    LEFT JOIN `dim_accounts` `da`
      ON (((`dapc`.`parent_id` = `da`.`code`)
           AND (`da`.`webmart_id` = 1101))))
   LEFT JOIN `dim_accounts` `da2`
     ON (((`dapc`.`child_id` = `da2`.`code`)
          AND (`da2`.`webmart_id` = 1101))))
WHERE (`dapc`.`webmart_id` = 1101) 
CREATE TABLE `dim_accounts` (
  `webmart_id` smallint(6) NOT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(250) NOT NULL,
  `type` varchar(15) DEFAULT 'Institution',
  `city` varchar(50) NOT NULL DEFAULT 'unknown',
  `state` varchar(50) NOT NULL DEFAULT 'unknown',
  `country` varchar(50) NOT NULL DEFAULT 'unknown',
  `region` varchar(50) NOT NULL DEFAULT 'unknown'
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

CREATE TABLE `dim_account_parent_child` (
  `webmart_id` smallint(6) NOT NULL,
  `parent_id` varchar(50) NOT NULL,
  `child_id` varchar(50) NOT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8; 

What is not supported by Infobright here?

Profile
 
Janusz Borkowski
Posted: 29 June 2010 09:26 AM   [ Ignore ]   [ # 3 ]  
Super Duper Member
Avatar
RankRankRankRankRank
Total Posts:  915
Joined  2008-08-18

Hi !

Try executing queries defining the joins. I guess the left joins are not supported by ICE. Even if they are, the join of views containing left joins apparently is not supported.

Profile
 
Jakub Wroblewski
Posted: 29 June 2010 10:11 AM   [ Ignore ]   [ # 4 ]  
Sr. Member
Avatar
RankRankRankRank
Total Posts:  736
Joined  2008-08-18

Hi,

Left joins in general are supported, except maybe some special cases. Anyway, please try:

1. to execute the queries defining views just as queries (to check whether there is the problem),

2. to execute the original query, but replacing the views by their definitions as explicit subqueries (btw. if any column in the views are not actually used in the outer query, it is better not to generate it).

Regards,

Signature 
Profile
 
   
 
 
‹‹ How can i make Infobright case insensitiive?      Mondrian giving wrong Results with infobright ››