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

[Former Member]

In 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 # 7503760

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 <= '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&)+0x3d5

I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf1173ae dfo::WalkForOutput(hos_vector_noerror&)+0xb2e

I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf1175bc dfo::WalkForOutput(hos_vector_noerror&)+0xd3c

I. 10/30 09:30:57. 0000220759 pc: 0x7f3bbf1173ae dfo::WalkForOutput(hos_vector_noerror&)+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

http://froebe.net/blog/2013/11/04/workaround-sap-sybase-iq-15-4-esd-3-and-iq-16-0-sp1-stacktrace-fro...