1 2 Previous Next 19 Replies Latest reply on Oct 8, 2014 12:21 PM by Steven Hawkins

    Not all source columns support search type and unexpected join results

    Bas Piepers Novice

      I am trying to investigate an INNER JOIN problem where a query unexpectedly doesn't return any results. I come across a warning from the query planner:

       

      Relational Planner, LOW,not all source columns support search type pwent_x_src,pwent_badge_src.TABLE_X.ID = pwent_x_src.TABLE_Y.ID was not pushed>

       

      What does this statement actually mean? I suspect that JDV doesn't "push" this statement down to the database, comparing the results it gets back from query parts itself (so essentially does the above comparison himself) rather than letting the database deal with that. Is that correct? How can I correct this? It seems very inefficient if JDV goes and fetches a lot of results from TABLE_X and TABLE_Y and then tries to compare the ID fields to get the matching records rather than having the database join the target tables itself.

       

      Thanks in advance.

        • 1. Re: Not all source columns support search type
          Steven Hawkins Master

          > What does this statement actually mean?

           

          There is searchable metadata on columns.  It can be searchable, all except like, like only, and unsearchable.  If one of these columns is not marked as searchable, then we won't push the comparison down.  Can you check/change the metadata if search is actually supported?

          1 of 1 people found this helpful
          • 2. Re: Not all source columns support search type
            Bas Piepers Novice

            I can actually set the fields to being searchable manually (in the Teiid designer) and then the query is pushed down to the SQL server instance correctly. The join itself still doesn't return results. Is there a way to see what JDV does with the query results? For example I have a query plan in which two separate queries are executed and I see the following log statements indicating two results that came back with records (removed some irrelevant log statements):

             

            END SRC COMMAND:  sourceCommandID=7   executionID=19  txID=null   modelName=pwent_evlog_src translatorName=sqlserver   finalRowCount=521
            END SRC COMMAND:  sourceCommandID=6   executionID=18  txID=null   modelName=pwent_badge_src translatorName=sqlserver   finalRowCount=169984

             

            Then thereafter I only see the end command statement indicating no results were finally returned:

             

            END USER COMMAND: finalRowCount=0

             

            Is there a way I can find out why it comes back without any result?

            • 3. Re: Not all source columns support search type
              Steven Hawkins Master

              The full query plan would be helpful to see what processing is happening above the source queries.

              • 4. Re: Not all source columns support search type and unexpected join results
                Bas Piepers Novice

                I had to replace some table information with non-customer specific names but the queries should be exactly the same. Underneath is the query plan with some of the irrelevant parts of the log omitted. Please also note that the ID fields of TABLE_C and TABLE_V are varbinary fields with a uuid value in it. Perhaps this may be the source of the problem. If I let the LOG table reside in the same schema as the rest of the tables so that only one source model is required in JDV the query does work. In that case, the entire query is pushed down to the database and so no join in JDV itself is executed.

                 

                START USER COMMAND:  sql=SELECT FORMATTIMESTAMP(MAX(e.EVNT_DAT), 'yy/MM/dd') AS CDATE, bc.CARDNO, e.FNAME,

                e.LNAME, bv.GGDUSERID, MIN(e.EVNT_DAT) AS Entry_Time, MAX(e.EVNT_DAT) AS Exit_Time,

                DAYOFMONTH(e.EVNT_DAT) FROM view_edl.TABLE_C bc

                INNER JOIN view_edl.TABLE_V bv ON bc.ID=bv.ID

                INNER JOIN view_edl.LOG e ON bc.CARDNO = e.CARDNO

                WHERE e.EVNT_DAT BETWEEN PARSETIMESTAMP('2013-10-15 00:00:00', 'yyyy-MM-dd HH:mm:ss') AND PARSETIMESTAMP('2013-11-07 23:59:59', 'yyyy-MM-dd HH:mm:ss') AND e.COMP_NAME LIKE 'ABC%'

                GROUP BY bc.CARDNO, e.FNAME, e.LNAME, bv.GGDUSERID, DAYOFMONTH(e.EVNT_DAT) ORDER BY DAYOFMONTH(e.EVNT_DAT)

                 

                START DATA SRC COMMAND: sourceCommandID=6       sql=SELECT g_0.CARDNO AS c_0, g_1.GGDUSERID AS c_1 FROM source_src.TABLE_C AS g_0, source_src.TABLE_V AS g_1 WHERE g_0.ID = g_1.ID GROUP BY g_0.CARDNO, g_1.GGDUSERID ORDER BY c_0

                START DATA SRC COMMAND: sourceCommandID=7       sql=SELECT g_0.CARDNO AS c_0, g_0.FNAME AS c_1, g_0.LNAME AS c_2, DAYOFMONTH(g_0.EVNT_DAT) AS c_3, MAX(g_0.EVNT_DAT) AS c_4, MIN(g_0.EVNT_DAT) AS c_5 FROM other_source_src.LOG AS

                g_0 WHERE (g_0.EVNT_DAT >= {ts'2013-10-15 00:00:00.0'}) AND (g_0.EVNT_DAT <= {ts'2013-11-07 23:59:59.0'}) AND (g_0.COMP_NAME LIKE 'ABC%') GROUP BY g_0.CARDNO, g_0.FNAME, g_0.LNAME, DAYOFMONTH(g_0.EVNT_DAT) ORDER BY c_0

                END SRC COMMAND:        sourceCommandID=7       finalRowCount=521

                END SRC COMMAND:        sourceCommandID=6       finalRowCount=169984

                END USER COMMAND:       finalRowCount=0

                • 5. Re: Not all source columns support search type and unexpected join results
                  Steven Hawkins Master

                  The query plan will show a tree of processing nodes: Query Plans - Teiid 8.9 (draft) - Project Documentation Editor

                   

                  > Perhaps this may be the source of the problem.

                   

                  It could be.  If you issue the queries separate and look at the contents of the varbinary values, do they seem to match or is do they vary - for example padding to a fixed length?

                  • 6. Re: Not all source columns support search type and unexpected join results
                    Bas Piepers Novice

                    I know the query plan in the tools but that doesn't help me here because I am only exposing some tables from the database. I am executing the query in this topic from an sql client like DBVisualizer. How can I see the query plan then? Do I execute the command SHOWPLAN ON/SHOWPLAN DEBUG from somewhere (doing it from the sql client doesn't work).

                    • 7. Re: Not all source columns support search type and unexpected join results
                      Steven Hawkins Master

                      You can either turn the server logging to the debug level or you can issue the statement:

                       

                      SET SHOWPLAN ON

                       

                      prior to running your query, then issue:

                       

                      SHOW PLAN

                       

                      immediately after.

                      • 8. Re: Re: Not all source columns support search type and unexpected join results
                        Bas Piepers Novice

                        It's quite large, the query plan, but attached it to this reply. Does that give you an idea why the last join of the query doesn't return any results?

                        • 9. Re: Re: Not all source columns support search type and unexpected join results
                          Steven Hawkins Master

                          So you would expect the issue to be with the join as there is no filtering occurring above the join - just aggregation.

                           

                          If you issue the source queries:

                           

                          SELECT g_0.CARDNO AS c_0, g_1.GGDUSERID AS c_1 FROM source_src.TABLE_C AS g_0, source_src.TABLE_V AS g_1 WHERE g_0.ID = g_1.ID GROUP BY g_0.CARDNO, g_1.GGDUSERID ORDER BY c_0

                          SELECT g_0.CARDNO AS c_0, g_0.FNAME AS c_1, g_0.LNAME AS c_2, DAYOFMONTH(g_0.EVNT_DAT) AS c_3, MAX(g_0.EVNT_DAT) AS c_4, MIN(g_0.EVNT_DAT) AS c_5 FROM other_source_src.LOG AS

                          g_0 WHERE (g_0.EVNT_DAT >= {ts'2013-10-15 00:00:00.0'}) AND (g_0.EVNT_DAT <= {ts'2013-11-07 23:59:59.0'}) AND (g_0.COMP_NAME LIKE 'ABC%') GROUP BY g_0.CARDNO, g_0.FNAME, g_0.LNAME, DAYOFMONTH(g_0.EVNT_DAT) ORDER BY c_0

                           

                          individually, can you confirm the expected ordering of CARDNO and that values aren't padded to different lengths?

                          • 10. Re: Not all source columns support search type and unexpected join results
                            Bas Piepers Novice

                            That is correct. They return results that are to be expected and there shouldn't be a reason why the join on cardno shouldn't work. The values are not padded to different lengths and the ordering seems to be correct. I can't be 100% sure, though, that no strange characters are in the contents of the strings because they may not show up in the SQL client I'm using...

                            • 11. Re: Not all source columns support search type and unexpected join results
                              Steven Hawkins Master

                              They are byte arrays correct? Can you provide the details on their contents in hex/base64 to confirm the sort order?  And/or can you try the translator property supportsOrderBy=false to disble sorting on each of the translators involved and see if Teiid produces the results then?

                              • 12. Re: Not all source columns support search type and unexpected join results
                                Bas Piepers Novice

                                Sorry, not sure what you mean. CARDNO is a nvarchar on the database and gets translated to a String in Teiid. In the end, Strings are byte arrays ofcourse but the native type is a nvarchar.

                                 

                                Tried to change the translator property. According to the documentation (Translators - Teiid 8.9 (draft) - Project Documentation Editor) I should be able to change it in the vdb.xml but only if it is a dynamic VDB. The VDB created in Teiid doesn't allow me to add the translator properties mentioned in the documentation. I get a parse error when doing so.

                                • 13. Re: Not all source columns support search type and unexpected join results
                                  Steven Hawkins Master

                                  > Sorry, not sure what you mean. CARDNO is a nvarchar on the database and gets translated to a String in Teiid. In the end, Strings are byte arrays ofcourse but the native type is a nvarchar.

                                   

                                  I'm just referring to your early posts in which you state "Please also note that the ID fields of TABLE_C and TABLE_V are varbinary fields with a uuid value in it."

                                   

                                  > I should be able to change it in the vdb.xml but only if it is a dynamic VDB

                                   

                                  You can change translator override properties regardless of the type of vdb.

                                   

                                  > I get a parse error when doing so.

                                   

                                  Can you show what you are trying to do?

                                  • 14. Re: Not all source columns support search type and unexpected join results
                                    Bas Piepers Novice

                                    As far as I'm aware, I can't override translator properties in Teiid designer so I tried changing the vdb.xml file that is generated by Teiid Designer. To do so, I unpack it, edit vdb.xml and repack the vdb. Then when I copy that vdb back to the workspace and override the Teiid Designer's VDB file and refresh that file in the designer, it comes back with a parse error (also when I deploy the file). Because the documentation doesn't seem to mention where I should put the translator override in the vdb.xml file, I just put it in as a child of the vdb tag:

                                     

                                    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

                                    <vdb name="vdbname" version="1">

                                        <property name="preview" value="false"/>

                                        <property name="validationDateTime" value="Wed Oct 08 13:31:26 CEST 2014"/>

                                        <property name="validationVersion" value="8.4.2-redhat-1"/>

                                       <translator name="sqlserver-override" type="sqlserver">

                                              <property name="SupportsOrderBy" value="false"/>

                                        </translator>

                                         <model name="source_src" type="PHYSICAL" visible="true" path="/vdbname_prj/data-sources/source_src.xmi">

                                            <property name="checksum" value="4194366796"/>

                                            <property name="indexName" value="3719768633.INDEX"/>

                                            <property name="modelUuid" value="mmuuid:07c51878-f9c3-4e9d-a8c4-a7bcbd255fdf"/>

                                            <property name="modelClass" value="Relational"/>

                                            <property name="builtIn" value="false"/>

                                            <source name="source_src" connection-jndi-name="source_src" translator-name="sqlserver-override"/>

                                        </model>

                                         <model (...)>

                                              (...)

                                         </model>

                                        

                                         (more model definitions)

                                    </vdb>

                                    1 2 Previous Next