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 Suresh On Sun, Jan 28, 2018 at 12:49 PM, Dave Birdsall <[email protected]> wrote: > 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) <[email protected]> > Sent: Sunday, January 28, 2018 12:58:22 AM > To: [email protected] > 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 >
