Query Folding Bug - Incorrect native query to MSAccess when filtering on logical data type field

[Scott_Parker]

Only seen using the Access.Database connector. The bug causes Query Folding to erroneously add the condition "where 0 = 1" to the SQL query so no rows are returned.

To reproduce the issue:

1. Create an .accdb file
2. Create a table (Table1) with an ID column and one other column. Populate with some test data (anything you want)
3. Connect to the table from Power Query
4. Add a custom column of boolean values, say (each if Number.Mod([ID],2)=0 then true else false​)
5. Filter the table (each [Custom] = -1)
6. Power Query will make a Query Folding mistake and return an empty table.

The incorrectly folded query looks like the below. The error is on the last line, "where 0 = 1"

select [_].[ID],
    [_].[Title],
    [_].[Custom]
from 
(
    select [_].[ID] as [ID],
        [_].[Title] as [Title],
        switch(
            [_].[ID] MOD 2 = 0 and [_].[ID] MOD 2 is not null, true,
            1, false
        ) as [Custom]
    from [Table1] as [_]
) as [_]
where 0 = 1

If you use any values other than 0 for false and -1 for true, the query will fold correctly, e.g:

select [_].[ID],
    [_].[Title],
    [_].[Custom]
from 
(
    select [_].[ID] as [ID],
        [_].[Title] as [Title],
        switch(
            [_].[ID] MOD 2 = 0 and [_].[ID] MOD 2 is not null, -2,
            1, -1
        ) as [Custom]
    from [Table1] as [_]
) as [_]
where [_].[Custom] = -1

[Anonymous]

Hi @Scott_Parker 

It only happened in Access.Database connector ? If you connect to SQL and use the same query , can the query run normally ?

Best Regards,
Community Support Team _ Ailsa Tao

[sivakumar_sadha]

Facing a similar issue with Snowflake database as well when calling a stored proc. Query folding is adding a filter to the query sent to backend "where 0 = 1" which means return nothing The query will return result without this filter. but this filter is added by Power BI before sending it to backend DB.

select * from (

SELECT column1, column2 FROM TABLE( GLOBAL_DB.DATA_PROD.PROC_DATA( 'test1', 'test2', 'test3', 'test4', 'test5' ) )

) as "_" where 0 = 1