Can Skipping Rows be supported

[tb-mtg]

I tried to run a skip on and got error:

"Jet does not support skipping rows."

Could you implement skip functionality usings the method below taken from https://stackoverflow.com/a/10455965/12439476:

If you know how many records you want to skip, then you could do something like this:

SELECT *
FROM myTable x
WHERE x.ID NOT IN (SELECT Top 10 id FROM myTable ORDER BY ....)
ORDER BY ...

Then you could exclude the records that you don't want.

If you then know the total number of records that you want to return, then you could do the following:

SELECT Top 50 *
FROM myTable x
WHERE x.ID NOT IN (SELECT Top 10 id FROM myTable ORDER BY ....)
ORDER BY ...

[bubibubi]

I think it's already implemented (in a different way). There should be a flag on configuration to enable it.
It could also be that we removed it in some commits.

EDIT
It's still there.
The flag is
UseOuterSelectSkipEmulationViaDataReader

[ChrisJollyAU]

Yes, it is still there. However I'm not sure how well it still works and obviously it only works on the outer select. If I understand it correctly it sends the normal query to Jet, and only when it is pulling the results in through the datareader will it stop at the number of rows.

I think I can pick up the Expression and modify it. Probably going to need to push the current query down into a subquery and wrap in a new SelectExpression. Should be doable and would cover more scenarios than the current implementation. Though the SQL might get a bit interesting for some queries and have to see how the performance would be

In the JetQueryableMethodTranslatingExpressionVisitor I can override the functions for TranslateSkip and TranslateTake. Needed that for commit c8e109f when fixing cross joins followed by left join

Going to reopen this issue

[tb-mtg]

How do I set the UseOuterSelectSkipEmulationViaDataReader flag?

[bubibubi]

It works as you described and should convert take to top using also skip (top is skip + take).
I think the query suggested can't work in a common scenario (orderby + skip + take).

[ChrisJollyAU]

@tb-mtg You should be able to use it when configuring Jet. It is one of the parameters when you call the UseJet function. Just looked at the section from the test sources that I can play around with

public override DbContextOptionsBuilder AddProviderOptions(DbContextOptionsBuilder builder)
            => builder.UseJet(Connection, b => b.ApplyConfiguration().UseShortTextForSystemString().UseOuterSelectSkipEmulationViaDataReader()).EnableSensitiveDataLogging().EnableDetailedErrors();

Hopefully that gives you an area to look for

Was playing around wit the skip. Some progress but problem is finding the field to compare on with the NOT IN. Your example your subquery is simple and just query for id. Problem comes in to rewrite the normal query to find the field we need to compare on

• If the subquery is just a basic table then we might just look for the primary key
• It becomes more complex if the subquery is a complex query in itself or the table is keyless
• An example of the above would be if the subqquery returned only the fields of FirstName,LastName

I do have some ideas to play around with though

[ChrisJollyAU]

Well, there is progress. Skip...Take is now supported.

A sample query that is produced is

SELECT `t0`.`CustomerID`, `t0`.`Address`, `t0`.`City`, `t0`.`CompanyName`, `t0`.`ContactName`, `t0`.`ContactTitle`, `t0`.`Country`, `t0`.`Fax`, `t0`.`Phone`, `t0`.`PostalCode`, `t0`.`Region`
    FROM (
        SELECT TOP 10 `t`.`CustomerID`, `t`.`Address`, `t`.`City`, `t`.`CompanyName`, `t`.`ContactName`, `t`.`ContactTitle`, `t`.`Country`, `t`.`Fax`, `t`.`Phone`, `t`.`PostalCode`, `t`.`Region`
        FROM (
            SELECT TOP 15 `c`.`CustomerID`, `c`.`Address`, `c`.`City`, `c`.`CompanyName`, `c`.`ContactName`, `c`.`ContactTitle`, `c`.`Country`, `c`.`Fax`, `c`.`Phone`, `c`.`PostalCode`, `c`.`Region`
            FROM `Customers` AS `c`
            ORDER BY `c`.`ContactName`
        ) AS `t`
        ORDER BY `t`.`ContactName` DESC
    ) AS `t0`
    ORDER BY `t0`.`ContactName`

And this is generated by Skip(5).Take(10)

Take...Skip and Skip on its own currently do not work properly. By the looks of it I am actually getting the rows I want to skip (which is the opposite of what I want)

[ChrisJollyAU]

@tb-mtg Just checking in with how it is going? If you are after Skip...Take then you can try the daily builds and see how that goes