Why DISTINCT is not pushed down when the translator doesn't support select expressions?
Hi-
We are dealing with a data source which possibly does not support select expressions, hence the translator is implemented to return false for select expressions support method. While we figure out for the support for select expression at the data source for sure, we wanted to understand why Teiid does not push down DISTINCT to the data source (it supports SELECT DISTINCT btw). Here is a sample-
Without the support of select expression, Teiid produces the following query plan-
SELECT DISTINCT ID, '1' as a FROM viewModel.testView
-------------------------------------------------------------------------------
Plan: SortNode
+ Output Columns:
0: ID (integer)
1: a (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:
0: ID (integer)
1: a (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Output Columns:ID (integer)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.ID FROM h2ContinuousTranslator.TEST.TESTSCHEMA.DUMMY AS g_0
+ Model Name:h2ContinuousTranslator
+ Select Columns:
0: h2ContinuousTranslator.TEST.TESTSCHEMA.DUMMY.ID
1: '1' AS a
+ Sort Mode:DUP_REMOVE
With the support of select expression, Teiid produces the following query plan-
SELECT DISTINCT ID, '1' as a FROM viewModel.testView
-------------------------------------------------------------------------------
Plan: AccessNode
+ Output Columns:
0: ID (integer)
1: a (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT DISTINCT g_0.ID FROM h2ContinuousTranslator.TEST.TESTSCHEMA.DUMMY AS g_0
+ Model Name:h2ContinuousTranslator
Our own understanding why it might happen is this-
If the data source does not support select expressions, that means that Teiid itself will evaluate those. So if we combine it with DISTINCT, it means that even if Teiid pushes it down to the data source, the uniqueness of rows cannot be determined as the data source cannot evaluate the select expression so it doesn’t know how to compare those. So if the following query is executed against a data source not supporting select expressions-
SELECT DISTINCT ID, '1' as a FROM viewModel.testView
‘1’ as a is something the data source doesn’t know how to evaluate and hence cannot figure out that the following two rows are distinct-
ID a
1 1
2 1
And Teiid decides not to push down DISTINCT to the data source.
Let us know if there something obvious that we are missing here.
Thank you.
Sanjeev.
