Home » Databases » WORKAROUND: SAP Sybase IQ 15.4 ESD 3 and IQ 16.0 SP1 – stacktrace from subquery with a left, right, outer or inner join and an exists clause

WORKAROUND: SAP Sybase IQ 15.4 ESD 3 and IQ 16.0 SP1 – stacktrace from subquery with a left, right, outer or inner join and an exists clause

SybaseIn SAP’s Sybase IQ, if you execute a query similar to the one below involving a subquery with a left, right, outer, or inner join AND an EXISTS clause, IQ will produce a stacktrace but will not close your connection. This is SAP bug number 750376, tentatively scheduled to be fixed in 15.4 SP08 in Q1 2014.

The problem query:

select T1.DATADATE,T1.hyper_space_id from MY_USER.JF_TEST_TABLE as T1
where T1.DATADATE = '2013-05-01' and T1.STARDATEUNITS = 0
and exists
(select 1
from MY_USER.JF_TEST_TABLE as T2
left outer join MY_USER.JF_TEST_TABLE as T3 on
T3.TSTPLANET = T1.TSTPLANET
and T3.MOON = T1.MOON
and T3.STARDATEUNITS <> 0
and T3.DATADATE > T2.DATADATE
where
T2.TSTPLANET = T1.TSTPLANET
and T2.MOON = T1.MOON
and T2.STARDATEUNITS = 0
and T2.DATADATE < = '2013-04-30'
and T3.QT is null)

The problem query will cause IQ to produce the following stacktrace in the IQ msg.log file (error log):

I. 10/30 09:30:57. 0000220759 Cmt 2207200
I. 10/30 09:30:57. 0000220759 PostCmt 0
I. 10/30 09:30:57. 0000220759 Txn 2207201 0 2207201
I. 10/30 09:30:57. 0000220759 Cmt 2207202
I. 10/30 09:30:57. 0000220759 PostCmt 0
I. 10/30 09:30:57. 0000220759 Txn 2207203 0 2207203
I. 10/30 09:30:57. 0000220759
I. 10/30 09:30:57. 0000220759 ********************************************************
I. 10/30 09:30:57. 0000220759 **
I. 10/30 09:30:57. 0000220759 ** Non-Fatal IQ Internal Error Detected
I. 10/30 09:30:57. 0000220759 ** at dfjo.cxx:227 on thread 3260823296 (TID 6)
I. 10/30 09:30:57. 0000220759 **
I. 10/30 09:30:57. 0000220759 ** Please report this to Sybase IQ support
I. 10/30 09:30:57. 0000220759 ** with the following diagnostic information,
I. 10/30 09:30:57. 0000220759 ** and, if possible, with the user's command.
I. 10/30 09:30:57. 0000220759 **
I. 10/30 09:30:57. 0000220759 ** Error from IQ connection: SA connHandle:11182 SA connID: 33 IQ connID: 0000220759 User: sa
I. 10/30 09:30:57. 0000220759 ** Time of error: 2013-10-30 09:30:57
I. 10/30 09:30:57. 0000220759 ** IQ Version: Sybase IQ/15.4.0/130221/P/ESD 3
I. 10/30 09:30:57. 0000220759 ** OS info: IQ built on: Enterprise Linux64- x86_64 - 2.6.18-194.el5, Executed on: Linux/lip76a1/2.6.32-358.18.1.el6.x86_64/#1 SMP Fri Aug 2 17:04:38 EDT 2013/x86_64
I. 10/30 09:30:57. 0000220759 ** Command status when error occured: CURSOR ACTIVE
I. 10/30 09:30:57. 0000220759 ** Command text:
I. 10/30 09:30:57. 0000220759 2.MOON = T1.MOON and T2.STARDATEUNITS = 0 and T2.DATADATE &lt;= '2013-04-30
I. 10/30 09:30:57. 0000220759
I. 10/30 09:30:57. 0000220759
===== Thread Number 3260823296 (IQ connID: 0000220759) =====
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf40d926 pcstkwalk(stk_trace*, int, db_log*, hos_fd*)+0x36
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf40db61 ucstkgentrace(int, int)+0x111
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf40f0f3 StackTraceForThisThread(char const*, int, char const*, char const*, char const*)+0x313
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf045272 df_Exception::df_Exception(char const*, int, df_Exception::df_ErrorCode, char const*, char const*, dfo const*,df_Evaluable const*)+0x442
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf046ca9 df_Exception::ThrowException_0(char const*, int, df_Exception::df_ErrorCode)+0x39
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf0ab826 apply_FindInternalDFEs(void*, df_Evaluable*)+0x276
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf037e6c df_Evaluable::InternalDepthLast
ConditionalApply(unsigned int, void*, int (*)(void*, df_Evaluable*))+0x3c
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf037ea2 df_Evaluable::InternalDepthLast
ConditionalApply(unsigned int, void*, int (*)(void*, df_Evaluable*))+0x72
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf0aaafc dfjo::FillInternalProductionVector()+0x3c
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf116c55 dfo::WalkForOutput(hos_vector_noerror&amp;)+0x3d5
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf1173ae dfo::WalkForOutput(hos_vector_noerror&amp;)+0xb2e
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf1175bc dfo::WalkForOutput(hos_vector_noerror&amp;)+0xd3c
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf1173ae dfo::WalkForOutput(hos_vector_noerror&amp;)+0xb2e
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf127715 dfo_Root::GenerateOutputVectors()+0x65
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf4cb479 opt_Driver::VectorAssignment(dfo_Root*, df_Heap*)+0x439
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf4cc2d8 opt_Driver::ApplyDFOTreeTransforms(dfo_Root*, df_Heap*, int)+0x428
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf4cda3c opt_Driver::GenerateTree(QueryNode*, st_cursor*)+0x85c
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbfbd0e52 st_cursorIQtoSA::Parse()+0xd2
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbef42883 df_OpenCursor::Parse()+0x83
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbfbbc503 st_command::DoCmdThroughResourceGate()+0x43
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbfc53bf5 st_iqdml::OpenCursor(a_statement*, UIConnection*, UICursor*, a_cursor_spec*, a_cursor_orientation)+0x25
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf4723a5 UIQDML_OpenCursor+0x25
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbfc7e526 st_SAIQInterfaceInfo::callFunction()+0x26
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbfc7dc09 st_SAIQInterface::RunIQFunc(st_SAIQInterfaceInfo*)+0x189
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbfc7eecd st_SAIQInterface::Execute(int,void*, unsigned int (*)(void*, void*), void*, unsigned int)+0x20d
I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf55ba91 saint_iqthresholddml::OpenCursor(a_statement*, IConnection*, ICursor*, a_cursor_spec*, a_cursor_orientation)+0x91
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d680c9 df_OmniRowScan::Restart(a_cursor_orientation)+0x1c9
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d47812 dfo_Scan::DoFetch(a_cursor_orientation)+0xc2
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d46ac2 dfo_Scan::DoFirstFetch(a_cursor_orientation)+0xd2
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d316e2 dfo_Base::Fetch(a_cursor_orientation)+0x102
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d3696b dfo_Root::DoFetch(a_cursor_orientation)+0x4b
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d332fa dfo_Root::DoFirstFetch(a_cursor_orientation)+0x1a
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d316e2 dfo_Base::Fetch(a_cursor_orientation)+0x102
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d33023 dfo_Root::DoFetchRelative(int,int*)+0xd3
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026d34b27 dfo_Root::FetchRelative(int, int*)+0xd7
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026a6e51d DB_Search_fetch(a_db_cursor*, unsigned int, int, int*)+0x7d
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026b72052 do_move_cursor(a_db_cursor*, int, unsigned int, unsigned int, unsigned int)+0x92
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026b749c0 dbi_fetch(a_db_cursor*, int, unsigned short, unsigned int, unsigned int, unsigned int, unsigned int, unsigned int)+0x330
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c4700d db__fetch(Connection*, an_sqlpres_receive*)+0x1bd
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c5cded RequestProcedure::call()+0x9ed
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026afd82e Context::call(Procedure*, Context**)+0x4e
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026afd916 Worker::call_on_stack(Procedure*)+0x56
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c5acad TopProcedure::call()+0x3d
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026b00da9 Worker::spawn(Procedure*)+0x49
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c5aeee EngStream::handle_ind(unsigned char, unsigned int)+0x13e
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c5f235 EngStream::execute()+0xc95
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c59de5 RQBaseItem::do_work(Worker*)+0x25
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c86a26 RequestQueue::worker_body()+0x66
I. 10/30 09:30:57. 0000220759 pc: 0x7f4026c5a516 request_task(void*)+0x46
I. 10/30 09:30:57. 0000220759 pc: 0x7f402709be7d run_task_body+0x2d
I. 10/30 09:30:57. 0000220759 ******************* End of STACKTRACE ******************
I. 10/30 09:30:57. 0000220759 **
I. 10/30 09:30:57. 0000220759 ** End of stack trace from Non-Fatal IQ Internal Error dfjo.cxx:227
I. 10/30 09:30:57. 0000220759 **
I. 10/30 09:30:57. 0000220759 ********************************************************
I. 10/30 09:30:57. 0000220759
I. 10/30 09:30:57. 0000220759 Exception Thrown from dfjo.cxx:227, Err# 0, tid 6 origtid 6
I. 10/30 09:30:57. 0000220759 O/S Err#: 0, ErrID: 9216 (df_Exception); SQLCode: -1006000, SQLState: 'QBA00', Severity: 23
I. 10/30 09:30:57. 0000220759 [20238]: IQ Internal error. Please report this to Sybase IQ support.
I. 10/30 09:31:01. 0000220759 Cmt 2207210
I. 10/30 09:31:01. 0000220759 PostCmt 0
I. 10/30 09:31:01. 0000220759 Txn 2207211 0 2207211
I. 10/30 09:31:01. 0000220759 Cmt 2207214
I. 10/30 09:31:01. 0000220759 PostCmt 0
I. 10/30 09:31:01. 0000220759 Txn 2207215 0 2207215
I. 10/30 09:31:02. 0000220759

The application will report the following error:

IQ Internal error. Please report this to Sybase IQ support.
--
(dfjo.cxx 227)
SQLCODE=-1006000, ODBC 3 State="HY000"

WORKAROUND
All we need to do is go back to the days when DBMSs didn’t support subqueries with exists clauses. Simply put, if the subquery returns any data at all, it will be a result set consisting of ones. Since one is equal to one, it satisfies the outer query:

select T1.DATADATE,T1.hyper_space_id from MY_USER.JF_TEST_TABLE as T1
where T1.DATADATE = '2013-05-01' and T1.STARDATEUNITS = 0
and
(select distinct 1
from MY_USER.JF_TEST_TABLE as T2
left outer join MY_USER.JF_TEST_TABLE as T3 on
T3.TSTPLANET = T1.TSTPLANET
and T3.MOON = T1.MOON
and T3.STARDATEUNITS <> 0
and T3.DATADATE > T2.DATADATE
where
T2.TSTPLANET = T1.TSTPLANET
and T2.MOON = T1.MOON
and T2.STARDATEUNITS = 0
and T2.DATADATE < = '2013-04-30'
and T3.QT is null) = 1
Share Button

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect