-
Notifications
You must be signed in to change notification settings - Fork 101
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
DISTINCT doesn't work for 2.0.0-alpha1 #99
Comments
The
|
Same problem in I have foreign table:
And quite simple query:
As result I receive:
Here is lots of duplications. But, resulted amount of rows is less than simple count on same query. Moreover, for COUNT with DISTINCT all works as expected:
Total count is much more:
For branch Is this a "magic" or somehow related with locale? May you help me in this situation or problem can be somewhere else? I can provide logs and any other information about env if needed. In all described cased env is absolutely identical (same PG and MSSQL, same tables, same data, same initialize options and configs of the FDW and so on). Differences only in tds_fdw and FreeTDS versions. |
Hi @Loriowar, I'm not entirely sure what is causing this problem. WHERE and column pushdowns aren't even performed when using the Would you be willing to provide a data set and table definitions that can reproduce this problem? That would help a lot. |
Sorry for a huge delay. I can't provide a full data set, only a table definition:
|
Do you get a correct result if you rewrite the query in this way?
|
This query still return duplications
Your variant has incorrect syntax for PG. I don't know how can work |
Apologies, I forgot the
|
This query doesn't work. It return duplications too. |
Since pushdowns are not working, try this query:
|
Little changes due to postgres syntax:
And I see a duplications in the result again. |
An important question: must I update tds_fdw to the latest master commit? For now, all queries runs on tds_fdw compiled in the moment of commit 3a803c5. |
When wrapping the query in a from clause to perform the SELECT DISTINCT, the handling of DISTINCT is no longer on tds_fdw or MSSQL, it's pure PostgreSQL at this point. if there are duplicates, check to make sure there isn't something about them that you can't see that makes them distinct, like spaces of other invisible characters. A last stitch effort is to use the query to fill a temporary table and run SELECT DISTINCT against it. If there are duplicates it's not tds_fdw, it's definitely something else. Try creating the table locally, fill it using a query for the raw data from mssql and then query this new table with distinct. |
For now, I use cascade of materialized views. The first view is identical to the foreign table (wrc_ctrs) and it store data one to one without any modification. Second view store only distinct records from the first view. In this case all works perfect and there is no duplications. Other variant: I can install stable version of tds_fdw (version 1.0.7) and problem with distinct disappear. I can check your last variants of queries on tds_fdw with versions 1.0.8 and master. May this can help? In a first time I thought about whitespace or something else in the column, but there is nothing. Other question: may this appear due to different encoding between databases or due to russian characters in column value? If so, how I can check this? And finally, is this a problem of tds_fdw or this is a problem of my local installation? |
The russian characters might be the cause. @GeoffMontee would be able to explain better. I'm no good at encoding logic. |
I'm having this same issue with the current head. In my case, it seems related to case-sensitivity. I am using tds_version 7.4, freetds 1.00.26, SQL Server 2008 R2, and postgresql 9.3. The SQL Server database is using collation SQL_Latin1_General_CP1_CI_AS. The postgres database is using en_us.UTF-8 for LC_COLLATE and LC_CTYPE. The encoding is UTF8. I don't believe any of these settings are particularly exotic.
Please let me know if there's any other information I can provide. Unfortunately, I only have read access to the SQL Server, so there's a limited amount I can do on that end. |
Query
SELECT DISTINCT "column_name" FROM foreign_table;
doesn't work. This return duplicate entries in case of usage tds-fdw from branch2.0.0-alpha1
. Branch1.0.7
works as expected for the same query and the same environment.My env:
The text was updated successfully, but these errors were encountered: