Redshift ODBC Query Folding Failure due to Data Types

[Anonymous]

Hi there,

At first, I didn't know what the question was. I was a bit stuck. But I believe that my question is the following:

When applying filters, what data types will (and will not) result in query folding through the Redshift ODBC Driver?

I have checked Simba docs, I cannot find an answer there...

Please help!

Read on below to follow my train of exploration to this Q.

I am using the built-in Redshift ODBC connector and am expecting my query to fold. I am loading in a table and applying a filter on a column called "city". However, the query is not folding when I use filters.

Here are the driver details

Name	Value
Driver Name	Microsoft Amazon Redshift ODBC Driver
Driver Version	1.4.41.1000
DBMS Name	Redshift
DBMS Version	8.0.2

I recieve the following OdbcQuery: Folding Warning messages:

VisitInvocation

VisitTextFrom

VisitColumnAccess

Data Type of column [col_name] with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo.

After these, I recieve a OdbcQueryDomain: ReportFoldingFailure, as such:

Exception:

ExceptionType: Microsoft.Mashup.Engine1.Runtime.FoldingFailureException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

Message: Exception of type 'Microsoft.Mashup.Engine1.Runtime.FoldingFailureException' was thrown.

StackTrace:

   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitColumnAccess(ColumnAccessQueryExpression expression)

   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitTextFrom(InvocationQueryExpression expression)

   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitInvocation(InvocationQueryExpression expression)

   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.AddColumns(IList`1 selectItems, IList`1 queryExpressions, Keys keys, IValueReference[] typeValues, List`1 newColumns, OdbcQuerySpecification newQuerySpecification, Boolean allowAggregates, Int32[] groupKey, IList`1 tableKeys, Boolean ignoreRange)

   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuer...

When searching for these errors, all support relates to custom-ODBC drivers. However, this Redshift driver is provided by Microsoft. I cannot find any other information around this. 

From what I can find, I think, but am not too sure, that the column type in Redshift is causing this issue. That's because the SQLGetInfo step cannot get any information, it's unsearchable. The column type that I am trying to filter in the datbase is "string"

To test this hypothesis, I attempted to apply a filter on an "int" column data type to see whether the query folders correctly, the query folds correctly.

So my final question is - what data types will be folded correctly? Do we have a list?

Thanks

Adam

[edhans]

They should all fold correctly. I've not used RedShift but I've filtered on decimals, integers, dates, and strings in Power Query to SQL and SnowFlake with no issues.

And even when folding breaks, it just continues to work in the Power Query mashup engine either on your desktop or gateway, it doesn't throw an error.

I'd consider opening a support ticket with MS. I have seen similar issues with Oracle not working correctly and there are double-bit character sets involved.

[Anonymous]

Thanks for the reply Ed!

Yeah, the PQ mash up evaluator contines but it's on millions of rows so it is incredibly slow. I'd rather it be folded.

Since my post I have tried to join on definite integers and it displays a 
The function (..) -> is not valid
The left query is not a valid ODBC query

Second one is strange, because it folds correctly up to that point...

Also, neither Table.Join or Table.nestedJoin functions fold on a smaller dataset I have put into Redshift., tried inner and leftOuter jouns. Both queries are folded up to the join for left and right queries before passing the results over to the mash up evaluator.