Invalid count returned.

[shulcsm]

Hello, i'm using mssql server and freetds 7.0
This https://github.com/GeoffMontee/tds_fdw/blob/master/src/tds_fdw.c#L905 returns -1 and breaks costs. I found out https://gitorious.org/freetds/freetds/source/d78804636aef4562966e89a8d57aec7bea98cc3b:src/dblib/dblib.c#L2782 exists, but i'm clueless about the topic. Let me know if i can give you any more information.

[GeoffMontee]

Thanks for pointing out this issue and providing relevant links to the FreeTDS source code.

Based on the definition of dbcount(), it looks like -1 should only be returned if the dbproc is NULL, if the socket is NULL, or if the rows_affected variable is set to TDS_NO_COUNT (or -1). Do you know which of these cases you encountered?

For the last case, it looks like this can happen here, but I'm not entirely sure what it means in that context. Based on a short analysis, it looks like it means that the query hasn't been executed yet.

I don't know how that could be the case in tds_fdw though. dbcount() (or the DBCOUNT() alias) is only called if the erc result code for the query is set to SUCCEED.

Anyway, a quick fix would probably be to return rows_increment, rather than rows_report.

Did you receive any error messages before you experienced this behavior with dbcount()? Or was this the only error?

Also, I'm curious: what kind of behavior from PostgreSQL did you see when the -1 "broke the costs"? I don't really have any experience with the behavior of PostgreSQL's query planner, so I'm not really sure what would happen in that case.

[shulcsm]

Query, analyze, debug log: https://gist.github.com/shulcsm/96509da49c8dd935ced1
Looks, like i'm only getting -1, whatever i do. By "broken" i meant negative negative costs, so joining the table results in plans like this: https://gist.github.com/shulcsm/b1d32e058a0aefc5cdc4

[GeoffMontee]

Interesting, thanks.

I don't remember seeing dbcount() return -1 in any of my tests. What operating system and FreeTDS version are you using? (Note: Version 7 is likely the version of the TDS protocol you are using, not the version of FreeTDS. The FreeTDS version should look something like 0.91-2.)

A real "fix" for the root of this problem would probably be to come up with a better way to estimate costs. The current way that tds_fdw does this is incredibly inefficient. A good cost estimation would be to use MS SQL Server's and Sybase's analogs for EXPLAIN (assuming something like that exists) in order to find the cost of the query in a way that doesn't require the query to be executed at least twice. If MS SQL Server and Sybase use different cost estimation syntax, tds_fdw would also have to know which to use for a given foreign server.

Unfortunately, I don't have much time to work on tds_fdw at the moment. I don't work on this at my day job, so I have to find time during my personal time. Maybe I'll find some time in 2015. I apologize if this is a disappointing answer.

If any interested parties develop enhancements on their own, I'd be happy to merge pull requests.

[shulcsm]

No worries, i can work around this.
If this ever comes up I'm using ubuntu package 0.91-1 and SQLServer2005.

[GeoffMontee]

I found out today that there's a DB-Library function called dbsetopt. Some options are:

• DBESTIMATE
• DBNOEXEC

I wonder if these options can somehow be used to get an estimate of executing the query without actually executing the query.

I hope to look into this soon and ask about this on the FreeTDS mailing list if I can't figure it out on my own.

Also related to the bad dbcount() return value, I wonder if these options are related to that somehow?

• DBNOCOUNT
• DBROWCOUNT

I also wonder if the dbiscount() function is related to that problem.

[GeoffMontee]

I'm not sure why dbcount is returning -1, but I've fixed this problem by returning rows_increment rather than rows_report when dbicount() returns 0.

Commit: fb5b96d

I do want to improve how tds_fdw estimates its costs. However, that change will probably be more intrusive.

[GeoffMontee]

By the way, I've added the ability to set the option row_estimate_method to the value showplan_all on a foreign table in the latest commit. This will use MS SQL Server's SET SHOWPLAN_ALL to get row estimations. This means the query no longer has to get executed twice.

Commit: e4d830f

[GeoffMontee]

Fixed in version 1.0.2.

[shulcsm]

Finally got to trying it out. It works. Thanks!