3 table join with group by

5 posts / 0 new
Last post
cbajomo
cbajomo's picture
Offline
Last seen: 2 weeks 3 days ago
Joined: Jan 25 2014
Junior Boarder

Posts: 3

Charles Bajomo , Data architect
3 table join with group by

Hello I am having a strange problem getting this query to run.

 

select 20140701 as end_date_id,
s.estate_id,
s.customer_id,
s.brand_id,
s.venue_id,
m.age_band_id,
s.venue_visit_id as visit_type_id,
count(distinct s.user_id) as users,
count(distinct s.device_id) as devices,
count(distinct s.venue_id) as venues,
s.registered_at_venue as registered_type,
s.session_type_id
from
DWH_current_prod.fact_radius_sessions s
inner join
DWH_current_prod.dim_age_band_marketing_list m on (s.age_id = m.age_id)
inner join
DWH_current_prod.dim_traffic_class t on (s.traffic_class_id = t.traffic_class_id)
where
s.end_date_id = 20140701
and
t.traffic_class_type = 1
group by
s.estate_id,
s.customer_id,
s.brand_id,
s.venue_id,
m.age_band_id,
s.session_type_id,
s.venue_visit_id,
s.registered_at_venue

 

Everytime I run it I get this error:

 

SQL Error (138): IDB-2035: An internal error occurred.  Check the error log file & contact support. */

Here is what I see in the error file:

err.log:

Jul 11 15:42:11 report-01.prod.man.o2wifi.co.uk Calpont[8378]: 11.877470 |0|0|0| E 00 CAL0000: rowgroup.cpp@1267: assertion 'cols <= columnCount' failed

debug.log

|540148|0|0| D 05 CAL0059: JobListFactory makeJoblist error: IDB-2035: An internal error occurred.  Check the error log file & contact support.
ExeMgr[8378]: 11.879231 |540148|0|0| D 16 CAL0042: End SQL statement
 
Any idea why this is happening.

davidhill
davidhill's picture
Offline
Last seen: 1 month 3 weeks ago
Joined: Oct 27 2009
Administrator

Posts: 595

david hill
3 table join

Can you provide the additional information:

 

1. show create table on the associated tables

2. repost the query in full, looks like previous posting was cut off.

 

Thanks, David

cbajomo
cbajomo's picture
Offline
Last seen: 2 weeks 3 days ago
Joined: Jan 25 2014
Junior Boarder

Posts: 3

Charles Bajomo , Data architect
Here are the create table
Here are the create table statements

CREATE TABLE `dim_age_band_marketing_list` ( `age_id` INT(11) NOT NULL, `age_band_id` INT(11) NOT NULL DEFAULT '0', `age_band` VARCHAR(25) NULL DEFAULT 'NONE' ) COLLATE='utf8_general_ci' ENGINE=InfiniDB;

CREATE TABLE `dim_traffic_class` ( `traffic_class_id` INT(11) NULL DEFAULT NULL COMMENT 'autoincrement', `traffic_class` VARCHAR(30) NULL DEFAULT 'LOW USAGE (UP TO 1MB)', `traffic_class_size` INT(11) NULL DEFAULT '0', `traffic_class_type` INT(11) NULL DEFAULT '1' ) COLLATE='utf8_general_ci' ENGINE=InfiniDB; [/quote]

[quote]CREATE TABLE `fact_radius_sessions` ( `start_date_id` INT(11) NOT NULL, `start_time_id` INT(11) NOT NULL, `end_date_id` INT(11) NOT NULL, `end_time_id` INT(11) NOT NULL, `estate_id` INT(11) NOT NULL DEFAULT '0', `customer_id` INT(11) NOT NULL, `brand_id` INT(11) NOT NULL DEFAULT '0', `venue_id` INT(11) NOT NULL DEFAULT '0', `equipment_id` INT(11) NOT NULL DEFAULT '0', `ip_range_id` INT(11) NOT NULL DEFAULT '0', `user_id` INT(11) NOT NULL, `gender_id` INT(11) NOT NULL DEFAULT '1', `age_id` INT(11) NOT NULL DEFAULT '0', `device_id` INT(11) NOT NULL, `device_type_id` INT(11) NOT NULL DEFAULT '0', `visit_type_id` INT(11) NOT NULL, `session_type_id` INT(11) NOT NULL, `traffic_class_id` INT(11) NOT NULL, `estate_visit_id` INT(11) NOT NULL DEFAULT '1', `customer_visit_id` INT(11) NOT NULL DEFAULT '1', `brand_visit_id` INT(11) NOT NULL DEFAULT '1', `venue_visit_id` INT(11) NOT NULL DEFAULT '1', `registered_at_customer` INT(11) NOT NULL DEFAULT '0', `registered_at_brand` INT(11) NOT NULL DEFAULT '0', `registered_at_venue` INT(11) NOT NULL DEFAULT '0', `session_duration` INT(11) NOT NULL, `session_unique_id` VARCHAR(50) NOT NULL DEFAULT '', `session_ip_address` INT(10) UNSIGNED NOT NULL DEFAULT '0', `bytes_downstream` BIGINT(20) NOT NULL DEFAULT '0', `bytes_upstream` BIGINT(20) NOT NULL DEFAULT '0', `inserted_by` VARCHAR(85) NOT NULL DEFAULT '', `inserted_id` INT(11) NOT NULL DEFAULT '0', `inserted_at` DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00', `rowid` INT(11) NOT NULL DEFAULT '0' ) COLLATE='utf8_general_ci' ENGINE=InfiniDB;


------------------------ QUERY ------------------

 

select 20140701 as end_date_id, s.estate_id, s.customer_id, s.brand_id, s.venue_id, m.age_band_id, s.venue_visit_id as visit_type_id, count(distinct s.user_id) as users, count(distinct s.device_id) as devices, count(distinct s.venue_id) as venues, s.registered_at_venue as registered_type, s.session_type_id from DWH_current_prod.fact_radius_sessions s inner join DWH_current_prod.dim_age_band_marketing_list m on (s.age_id = m.age_id) inner join DWH_current_prod.dim_traffic_class t on (s.traffic_class_id = t.traffic_class_id) where s.end_date_id = 20140701 and t.traffic_class_type = 1 group by s.estate_id, s.customer_id, s.brand_id, s.venue_id, m.age_band_id, s.session_type_id, s.venue_visit_id, s.registered_at_venue

-------------------------------------------
radams
radams's picture
Offline
Last seen: 4 days 6 hours ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
 Hi, We have reproduced the

 

Hi,

 

We have reproduced the error and have opened a bug. 

 

We will be addressing this issue with an upcoming release.

 

Thank you,

 

Robert

 

 

cbajomo
cbajomo's picture
Offline
Last seen: 2 weeks 3 days ago
Joined: Jan 25 2014
Junior Boarder

Posts: 3

Charles Bajomo , Data architect
okay great thanks. You don't

okay great thanks. You don't have any idea for a work around do you?