All Forums Database
m.knauss 4 posts Joined 07/09
19 May 2014
Error creating volatile table in version 14.10

Hello, 
we just upgraded to version 14.10 and are experiencing a new issue in the following situation.  We have a query that is trying to create a volatile table with a nested select like this: 
CREATE VOLATILE MULTISET TABLE foo AS (
SELECT a.bar, b.baz FROM aTable a WHERE a.bar = 'Y'
INNER JOIN bTable b ON a.baz = b.baz)
 
Table "aTable" actually does have data populating some rows. 
Volatile table "bTable" does NOT. 
We are getting the following error: 

STATEMENT 1:  Create Table failed.  Failed [5325 : HY000] Length 0 is not allowed for a CHAR, VARCHAR, BYTE, VARBYTE column.

 

This seems to be due to trying to join to the empty vtable.  I've Google searched for this error and it basically just says to create the table with columns of non-zero length.  

Raja_KT 1246 posts Joined 07/09
19 May 2014

Is it because of upgradation? 
 
My suggestion is first write the sql part and see the result (even if you dont have data).
I hope you have baz field  too in aTable, because in your selct part you have a.bar
The query can be:
SELECT a.bar(z), b.baz FROM aTable a 
INNER JOIN bTable b ON a.baz = b.baz WHERE a.bar = 'Y'
Once you have a query fine, then you put in create statment
CREATE VOLATILE MULTISET TABLE foo AS (
SELECT a.bar(z), b.baz FROM aTable a 
INNER JOIN bTable b ON a.baz = b.baz WHERE a.bar = 'Y')
With data
on commit preserve rows;
In your case you don't have data.
You can do
select * from foo;
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

m.knauss 4 posts Joined 07/09
20 May 2014

Raja, 
thanks for the reply.  We were actually able to find an odd difference in how 14.10 is handling '' versus ' '.  In the actual SQL being used we had something like this: 
 
SELECT a.bar(z), b.baz,
'' as aNewCol,
'' as anotherNewCol
FROM aTable a 
INNER JOIN bTable b ON a.baz = b.baz WHERE a.bar = 'Y'
Once you have a query fine, then you put in create statment
CREATE VOLATILE MULTISET TABLE foo AS (
SELECT a.bar(z), b.baz FROM aTable a 
INNER JOIN bTable b ON a.baz = b.baz WHERE a.bar = 'Y')
With data
on commit preserve rows;
 
In 13.10 the empty single quotes were handled without an error.  It seems that in 14.10 the parsing engine is a bit more strict and requires that at least a space be between the single quotes. 
 
Martie
 

You must sign in to leave a comment.