Joinutility seperatorLogin utility separator Infobright.com
Loading
   
 
‘Unsupported data type’ error on CREATE TABLE from SELECT with SUM()
 
Pavel Kolesnikov
Posted: 29 April 2009 10:32 AM   [ Ignore ]  
Newbie
Rank
Total Posts:  13
Joined  2009-04-29

Hi,

I have problems creating a table from a SELECT command involving the SUM() function. For example:

Server version: 5.1.14 build number (revision)=IB_3.1_r3027_3069(ice)

mysql> CREATE TABLE test (id int, value int);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TEMPORARY TABLE tmp SELECT id, SUM(value) FROM test GROUP BY id;
ERROR 8 (HY000): Unsupported data type. 

The same command works correctly with other aggregate functions (AVG, COUNT, MIN or MAX).

Does anybody have any idea about possible workarounds?

Thanks
Pavel

Profile
 
Geoffrey Falk
Posted: 29 April 2009 10:47 AM   [ Ignore ]   [ # 1 ]  
Sr. Member
Avatar
RankRankRankRank
Total Posts:  505
Joined  2008-08-18

Hi,

Can you post the DDL for your table, please?

ICE does not support the use of temporary tables. One workaround is to export your query result to a file using SELECT INTO OUTFILE. Then create the results table with an appropriate DDL, and load the results back using LOAD DATA INFILE.

thanks,
Geoffrey

[ Edited: 29 April 2009 10:49 AM by Geoffrey Falk]
Signature 
Profile
 
Pavel Kolesnikov
Posted: 29 April 2009 11:07 AM   [ Ignore ]   [ # 2 ]  
Newbie
Rank
Total Posts:  13
Joined  2009-04-29

Hi Geoffrey,

the DDL is as simple as

CREATE TABLE test (id int, value int); 

(see my initial post).

I don’t think this problem is about temporary tables.

The following query works:

CREATE TEMPORARY TABLE tmp SELECT id, COUNT(value) FROM test GROUP BY id; 

... but the following causes the ‘Unsupported data type’ error too:

CREATE TABLE tmp SELECT id, SUM(value) FROM test GROUP BY id; 

The problem really appears to be in the combination of CREATE TABLE and SUM.

Unfortunately, neither the idea of using SELECT INTO OUTFILE instead is applicable:

mysql> SELECT id, SUM(value) FROM test GROUP BY id INTO OUTFILE '/tmp/test.txt';
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. 
Profile
 
Geoffrey Falk
Posted: 29 April 2009 11:09 AM   [ Ignore ]   [ # 3 ]  
Sr. Member
Avatar
RankRankRankRank
Total Posts:  505
Joined  2008-08-18

Hi Pavel,

The problem is that it is trying to use DECIMAL(32) as the data type returned from SUM. But ICE only supports DECIMAL(18) or smaller.

As a workaround, you can use the export/import technique that I described in my previous post.

I will mention this as an issue with the product.

Thanks
Geoffrey

Signature 
Profile
 
Pavel Kolesnikov
Posted: 29 April 2009 11:27 AM   [ Ignore ]   [ # 4 ]  
Newbie
Rank
Total Posts:  13
Joined  2009-04-29

I was struggling a bit with the ‘syntax unsupported by optimizer problem’ error message but I’ve found the forum mentioning the “set @bh_dataformat = ‘txt_variable’” command. So the export/import workaround seems to work. Thanks.

Profile
 
Buddoda!
Posted: 01 September 2010 11:30 AM   [ Ignore ]   [ # 5 ]  
Newbie
Rank
Total Posts:  31
Joined  2010-07-31

CREATE TABLE tmp SELECT id, cast(SUM(value) as DECIMAL(18,10)) ‘VALUE’ FROM test GROUP BY id;

My so far experience(may be too little) dares to suggest this.

Profile
 
IBNoob
Posted: 14 June 2011 11:22 PM   [ Ignore ]   [ # 6 ]  
Newbie
Rank
Total Posts:  6
Joined  2010-12-06

The cast does not work for me, I still get the unsupported data type error (it works if you aren’t creating a table). The only way I found to satisfactorily work around it in my case was to provide column hints to the create statement.

mysql> CREATE TEMPORARY TABLE tmp (sum_value int) AS SELECT id, SUM(value) as sum_value FROM test GROUP BY id; 

I am able to dynamically generate summary tables of aggregates using this in the case where I hit that error.

Profile
 
   
 
 
‹‹ Out of resources message      CREATE TABLE ... SELECT extremely slow for slightly slower SELECT part ››