[teiid-issues] [JBoss JIRA] (TEIID-5522) Avoid pushing join to datasource if DS cannot handle 1600+ columns

[Steven Hawkins (Jira)]

     [ https://issues.jboss.org/browse/TEIID-5522?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-5522.
-----------------------------------
    Resolution: Done


Added a new method ExecutionFactory.getMaxProjectedColumns to limit how much can be pushed - this will not subdivide a single table, but will prevent joins.  It also can rely on an estimate on the number of output columns, so that we don't incur running rule assign output elements again.

> Avoid pushing join to datasource if DS cannot handle 1600+ columns 
> -------------------------------------------------------------------
>
>                 Key: TEIID-5522
>                 URL: https://issues.jboss.org/browse/TEIID-5522
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Misc. Connectors, Query Engine
>            Reporter: Norbert Funke
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 12.1
>
>
> Problem: I am trying to create a wide view (~5000 columns), which works across data sources fine JDV. However, when I try to create the view with a join on 2+ table from data source, the optimizer pushes down the join to the source. The current source cannot handle more then ~1600 columns.
> Example: When trying to join Member_DX1 and Member_DX2 at client, JDV pushes the enter code herecombined join to postgres as one getting the too max column error.
> /* TABLE 1 */
> CREATE VIEW Member_DX1 (
>     MEMB_BID Integer
>      , DX130402000000 Integer
>      , DX180608000000 Integer
>      , DX20401070000 Integer
> .... /* 1000 more */
> as
> SELECT dx.memb_bid
> , case dx.EPI_1_DX4 when 130402000000 then 1 else 0 END as  DX130402000000                                              
> , case dx.EPI_1_DX4 when 180608000000 then 1 else 0 END as  DX180608000000                                              
> , case dx.EPI_1_DX4 when 20401070000 then 1 else 0 END as  DX20401070000
> ...
> FROM BDR.ENH_EPI_DETAIL dx 
> /* TABLE 2 */
> CREATE VIEW Member_DX2 (
>     MEMB_BID Integer
>      , DX200102010000 Integer
>      , DX90125000000 Integer
>      , DX160603070000 Integer
> ... /* 1000 more ...
> SELECT dx.memb_bid  /* FOREIGN TABLE */
> , case dx.EPI_1_DX4 when 200102010000 then 1 else 0 END as  DX200102010000                                              
> , case dx.EPI_1_DX4 when 90125000000 then 1 else 0 END as  DX90125000000                                                
> , case dx.EPI_1_DX4 when 160603070000 then 1 else 0 END as  DX160603070000      
> ...`enter code here`
> FROM BDR.ENH_EPI_DETAIL dx;
> then my query in (e.g. dBeaver) looks like this:
> SELECT * from Member_DX1 dx1
> join Member_DX2 dx2 
> on dx1.MEMB_BID = dx2.MEMB_BID



--
This message was sent by Atlassian Jira
(v7.12.1#712002)