trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suresh Subbiah <>
Subject Re: how to read debug info after set SUBQUERY_UNNESTING to 'debug'
Date Mon, 29 Jan 2018 01:38:15 GMT
Hi Ming,

The warning messages should show on the screen if we are using sqlci or
trafci. The methods used internally is the same as that used to raise any
other warning during compilation, say like a missing stats warning.
If no messages are seen on screen, then that means there are no correlated
subqueries that can be unnested. We do have an a form of subquery inlining
in the transformer. That is not covered by this cqd. This cqd covers
correlated subqueries that are unnested during the SQO (semantic query
optimization) phase. If no messages are seen, then it may be best to see
the transformation using DISPLAY, as Dave said. Prior to transform phase,
the subquery will be in ItemExpr format. We will not see it in the main
RelExpr tree shown by DISPLAY. If we go to the appropriate RelExpr and
click on ItemExpr, then we will see the subquery tree. After transformer
phase we can see that the ItemExpr tree has been replaced by a RelExpr tree.

For examples on use of this cqd please see compGeneral/TEST011. Its main
purpose is to give an indication as to why a correlated subquery was not
unnested (in case it is not). Every time we see a correlated subquery in
the normalizer we will print a warning saying that a correlated subquery
has been detected. Then if no further warnings are shown, then that means
the correlated subquery has been unnested successfully. If a warning is
seen, then it will contain the reason why we could not unnest.

It is not friendly to use. It was meant as a debugging aid, to give a clue
as to why something could not be unnested, with using the debugger.

Thank you

On Sun, Jan 28, 2018 at 12:49 PM, Dave Birdsall <>

> Hi Ming,
> I don't know the particulars of that CQD.
> However, have you used the DISPLAY utility? That shows the shape of the
> query tree after each pass of the compiler. So you will be able to see what
> pass transforms a subquery into a join.
> Dave
> ________________________________
> From: Liu, Ming (Ming) <>
> Sent: Sunday, January 28, 2018 12:58:22 AM
> To:
> Subject: how to read debug info after set SUBQUERY_UNNESTING to 'debug'
> hi, all,
> I am trying to understand better about subquery optimization in Trafodion.
> So I set the SUBQUERY_UNNESTING CQD to 'DEBUG', and try a few queries, but
> I don't know how to see the output of that DEBUG? I saw the code will
> generate some WARNs into diagArea, but not seeing them.
> If someone know how to use this debug skill, please help me.
> thanks,
> Ming

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message