Error creating volatile table in version 14.10

Database
Enthusiast

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.  

2 REPLIES
Enthusiast

Re: Error creating volatile table in version 14.10

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;

Enthusiast

Re: Error creating volatile table in version 14.10

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