-
1. Re: Not all source columns support search type
shawkins Oct 7, 2014 7:31 AM (in response to bpiepers)1 of 1 people found this helpful> 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?
-
2. Re: Not all source columns support search type
bpiepers Oct 7, 2014 7:45 AM (in response to shawkins)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
shawkins Oct 7, 2014 8:26 AM (in response to bpiepers)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
bpiepers Oct 7, 2014 8:47 AM (in response to shawkins)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
shawkins Oct 7, 2014 9:26 AM (in response to bpiepers)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
bpiepers Oct 7, 2014 9:59 AM (in response to shawkins)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
shawkins Oct 7, 2014 10:04 AM (in response to bpiepers)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
bpiepers Oct 14, 2014 2:14 AM (in response to shawkins)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
shawkins Oct 7, 2014 12:48 PM (in response to bpiepers)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
bpiepers Oct 7, 2014 3:41 PM (in response to shawkins)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
shawkins Oct 7, 2014 3:51 PM (in response to bpiepers)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
bpiepers Oct 8, 2014 7:08 AM (in response to shawkins)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
shawkins Oct 8, 2014 9:01 AM (in response to bpiepers)> 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
bpiepers Oct 8, 2014 9:33 AM (in response to shawkins)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>