A bug has been identified in Power BI Desktop Mashup Engine.

Reported to and assistance requested from the DataConnectors GitHub community lead:
https://github.com/CurtHagenlocher

The issue might become a showstopper when using the custom Power BI connector in some common use cases like selecting top few rows from a table, e.g. Table.TakeN() [a.k.a. SELECT X FROM Y LIMIT Z]

The detailed bug report with trace info and root analysis attached in PBIDesktop-MashupEngine-bug-report-20200626-with-MEZ-file.zip

Response from Curt Hagenlocher:

From: Curt Hagenlocher <curth@microsoft.com>
Sent: 29 June 2020 01:50
To: Ilya Belomutov <ilyabelomutov@hotmail.com>
Cc: todd.stoffel@mariadb.com; Colin Popell <George.Popell@microsoft.com>; Anantha Ganjam <anganjam@microsoft.com>
Subject: RE: [EXTERNAL] Fwd: Power BI Desktop Mashup Engine bug report

Questions related to certified connectors should generally go to Colin Popell (George.Popell@microsoft.com).

This behavior is effectively "by design" – at least for now. The fundamental issue is that ODBC is defined in terms of SQL-92, which lacks any kind of standardization around TOP or LIMIT. Because of this, there are no capability flags in the ODBC specification which would say whether or not e.g. the TOP clause is supported inside a subquery. Now of course the same is true for TOP as a whole, but we couldn’t avoid implementing support for basic uses of it for obvious reasons – and we did it by adding the LimitClauseKind – but we haven’t yet done the same for more complicated scenarios.

Frankly, a naked TOP is a bit of a dodgy operation when it comes to relational databases anyway, as there’s no well-defined order for the rows being returned without an explicit ORDER BY. So in the sample you provided, which takes “the first ten rows in the flights table”, the actual rows returned are entirely database-dependent. This can be surprising to a user who expects that it will be the first ten rows that appear in the previous step of the query editor. I’m curious whether this example comes from a specific user-driven scenario or a more mechanical testing of the functionality available in the UI.

In any event, this is only the second time in four years that someone has raised this issue so we’re not likely to assign a very high priority to it. If can be done cheaply and with low risk then we’ll consider making a change.