[teiid-issues] [JBoss JIRA] (TEIIDSB-213) We have strange behavior with aliases

[Steven Hawkins (Jira)]

    [ https://issues.redhat.com/browse/TEIIDSB-213?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14178525#comment-14178525 ] 

Steven Hawkins commented on TEIIDSB-213:
----------------------------------------

> It is BUG in Salesforce. Salesforce (because we have bug in it) do not return data for this request to Regular_Articles__kav with used aliases for this table.

I don't quite understand that conclusion yet.  There may be some confusion here between the Teiid and source queries.

In the no rows case you issued the Teiid query: "SELECT a.ArticleNumber,a.Id,a.KnowledgeArticleId,a.Language FROM Regular_Articles__kav a WHERE a.Language = 'it' LIMIT 5"

>From the log you see the source query (SOQL) issued is: "SELECT Regular_Articles__kav.ArticleNumber, Regular_Articles__kav.Id, Regular_Articles__kav.KnowledgeArticleId, Regular_Articles__kav.Language FROM Regular_Articles__kav WHERE Regular_Articles__kav.Language = 'it' LIMIT 5"

There are no aliases used in the source query.  In fact user level query aliasing is never used in the source SOQL by Teiid, it is only pushed to determine the proper form of self join queries and then discarded.

> 1. Teiid got an exception in request without aliases because "Language" is reserved word as I known.

That is not a problem, it is expected behavior.

> 2. Salesforce have a BUG with aliases for concrete SObject (in other objects all working fine).

Again, you should not be looking at the Teiid query, but the source SOQL query that is being generated.  

> Because Teiid converted this request to form with full qualified names as

That is not the SOQL, but the pushdown query from the query plan.  The user query is converted to a query plan that contains pushdown queries you can see those in the processor plan or in the log as DATA SOURCE COMMAND to the relevant sources.  Those pushdown queries are expressed in Teiid SQL.  At runtime when those pushdown queries are processed it's the job of the translator to covert that into source SQL, DSL, API calls of whatever the source supports - in the log as SOURCE SRC COMMAND.  In this case you can see logs similar to:

[Worker0_QueryProcessorQueue0]: 	SOURCE SRC COMMAND:	endTime=2020-06-26 17:42:07.505	requestID=Ecs7P/wl/YGs.0	sourceCommandID=0	executionID=0	txID=null	modelName=salesforce	translatorName=mysalesforce	sessionID=Ecs7P/wl/YGs	principal=anonymous	sourceCommand=[SELECT Regular_Articles__kav.ArticleNumber, Regular_Articles__kav.Id, Regular_Articles__kav.KnowledgeArticleId, Regular_Articles__kav.Language FROM Regular_Articles__kav WHERE Regular_Articles__kav.Language = 'it' LIMIT 5]

In other words it doesn't matter what quoting, aliases, etc. you have aliases in your user query the source SOQL will be the same.

If you are getting an unexpected row count, I would suggest not using the limit and/or where predicate to see if you do get expected results otherwise.

> We have strange behavior with aliases
> -------------------------------------
>
>                 Key: TEIIDSB-213
>                 URL: https://issues.redhat.com/browse/TEIIDSB-213
>             Project: Teiid Spring Boot
>          Issue Type: Bug
>          Components: datasource
>            Reporter: Renat Eskenin
>            Priority: Major
>         Attachments: Снимок экрана от 2020-06-26 12-37-40.png, Снимок экрана от 2020-06-26 12-38-04.png
>
>
> We have two SOQL
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
>   
>  SELECT a.ArticleNumber,a.Id,a.KnowledgeArticleId,a.Language FROM Regular_Articles__kav a WHERE a.Language = 'it' LIMIT 5 
> This SOQL requests get different responses from SF because in second request we have alias for table.
> It is mystics from SF (we made bug in sf)
> How we can call
> SELECT ArticleNumber,Id,KnowledgeArticleId,Language FROM Regular_Articles__kav WHERE Language = 'it' LIMIT 5
>   in Teiid without aliases?



--
This message was sent by Atlassian Jira
(v7.13.8#713008)