Error using XPLAIN

[Brett Wooldridge <br...@gmail.com>]

Devs,

I am encountering an error using the new explain functionality.  When I run
the following:

CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA('APP');

select *
from device d
where d.network='Default'
order by d.ip_high, d.ip_low
fetch first 100 rows only;

CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);

I get this error:

Error: A truncation error was encountered trying to shrink {0} '{1}' to
length {2}.
SQLState:  22001
ErrorCode: -1
Error occured in:
select *
from device this_
where this_.network='Default'
order by this_.ip_high, this_.ip_low
fetch first 100 rows only

And the following stack trace on my server:

Caused by: ERROR 22001: A truncation error was encountered trying to shrink
CHAR 'Thread[DRDAConnThread_27,5,derby.daemons]' to length 32.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.iapi.types.SQLChar.hasNonBlankChars(Unknown Source)
at org.apache.derby.iapi.types.SQLChar.normalize(Unknown Source)
at org.apache.derby.iapi.types.SQLChar.normalize(Unknown Source)
at org.apache.derby.iapi.types.DataTypeDescriptor.normalize(Unknown Source)
at
org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeColumn(Unknown
Source)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(Unknown
Source)
at
org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown
Source)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown
Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
Source)
... 15 more

Is the XPLAIN functionality only supposed to be use with an embedded driver?
 If not, the thread names of threads in the networks server are too long to
fit into the XPLAIN schema SYSXPLAIN_STATEMENTS(XPLAIN_THREAD_ID).  Along
that same vein, the TRANSACTION_ID column looks similarly too small to my
eye (CHAR 32).  I use Bitronix JTA and from my memory the xid are at least
64 characters.

-Brett

Re: Error using XPLAIN

[Bryan Pendleton <bp...@gmail.com>]

> driver?  If not, the thread names of threads in the networks server are
> too long to fit into the XPLAIN schema
> SYSXPLAIN_STATEMENTS(XPLAIN_THREAD_ID).  Along that same vein, the
> TRANSACTION_ID column looks similarly too small to my eye (CHAR 32).  I
> use Bitronix JTA and from my memory the xid are at least 64 characters.

Thanks Brett! I think this is currently logged as DERBY-4772

https://issues.apache.org/jira/browse/DERBY-4772

It would be helpful if you could attach your findings to that problem in Jira.

Perhaps you want to have a go at fixing it? As Kristian notes, we could either
make the column lengths longer, or we could truncate the values to fit when
capturing query plan data.

thanks,

bryan