Well, it doesn't really matter what the documentation says. Fact is that it is currently broken. I can easily patch this on my end using an IDataReader implementation that forwards all calls except the GetSchemaTable method. It would be sad though if a high quality library like SQLite contained a flaw like this.
-----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe Mistachkin Sent: 04 January 2016 15:43 To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] System.Data.SQLite: Incorrect implementation of SQLiteDataReader::GetSchemaTable() The docs you quote from are specific to the "SqlClient" ADO.NET implementation, which is used to communicate only with MS SQL Server. Other providers are apparently free to have completely different semantics. Sent from my iPhone > On Jan 4, 2016, at 1:25 AM, Jann Roder <j.roder at wintoncapital.com> wrote: > > Looking at MSDN the docs are not that fuzzy: > https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldata > reader.getschematable(v=vs.110).aspx > > for example: > > AllowDBNull : Set if the consumer can set the column to a null value or if > the provider cannot determine whether the consumer can set the column to a > null value. Otherwise, not set. A column may contain null values, even if it > cannot be set to a null value. > > IsKey: true: The column is one of a set of columns in the rowset that, taken > together, uniquely identify the row. The set of columns with IsKey set to > true must uniquely identify a row in the rowset. There is no requirement that > this set of columns is a minimal set of columns. This set of columns may be > generated from a base table primary key, a unique constraint or a unique > index. > > Unfortunately this is written from the perspective of someone using the > dataset to insert data into the database, but still the current > implementation violates the above. > > While I understand your concern about backward compatibility this is still > clearly a bug in my opinion. Loading data into a datatable form SQLite is > only garunateed to work if the query is of the form SELECT * FROM Table. In > all other cases it depends. I think there should at least be an option that > will make it work in all cases. > > Jann > > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Joe > Mistachkin > Sent: 31 December 2015 17:45 > To: 'SQLite mailing list' <sqlite-users at mailinglists.sqlite.org> > Subject: Re: [sqlite] System.Data.SQLite: Incorrect implementation of > SQLiteDataReader::GetSchemaTable() > > > Jann Roder wrote: >> >> The implementation of GetSchemaTable() in SQLiteDataReader seems to >> be based on the properties of the columns used in the select. >> Obviously uniqueness, nullness and primary key properties do not >> transfer when only a subset of the columns is selected or combined >> with data from other tables. > > The official MSDN docs are a bit fuzzy on these points: > > > https://msdn.microsoft.com/en-us/library/system.data.idatareader.getsc > hemata > ble%28v=vs.110%29.aspx > >> >> Unfortunately the .Net Datatable::Load() command uses the information >> returned by SQLiteDataReader::GetSchemaTable(). This means that the >> result of many queries cannot be loaded into a Datatable using the >> Load() command because of non-nullable columns being null (due to a >> left join) or even wrong (the Datatable just drops "duplicate" rows >> when loading data). See the code below that illustrates the problem. > > Yes, I've seen this behavior while tracking down several past issues. > On a somewhat sunnier note, the built-in ADO.NET support classes seem > remarkably good at adapting to [some of] the of idiosyncrasies of the various > ADO.NET providers. However, as you have seen, there are some inconsistencies. > >> >> I'm not sure how other DB drivers solve this problem, but it seems to >> me the SQLite driver should stop trying to be clever and just always >> set the IsUnique and IsKey columns to false and also always allow >> null values. > > Unfortunately, changing this method in such a fundamental way would not be > backwards compatible. Also, in some cases, it can be quite difficult to > determine what the "correct" behavior should be. > > -- > Joe Mistachkin > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > Winton Capital Management Limited (?Winton?) is a limited company registered > in England and Wales with its registered offices at 16 Old Bailey, London, > EC4M 7EG (Registered Company No. 3311531). Winton is authorised and regulated > by the Financial Conduct Authority in the United Kingdom, registered as an > investment adviser with the US Securities and Exchange Commission, registered > with the US Commodity Futures Trading Commission and a member of the National > Futures Association in the United States. > > This communication, including any attachments, is confidential and may be > privileged. This email is for use by the intended recipient only. If you > receive it in error, please notify the sender and delete it. You should not > copy or disclose all or any part of this email. > > This email does not constitute an offer or solicitation and nothing contained > in this email constitutes, and should not be construed as, investment advice. > Prospective investors should request offering materials and consult their own > advisers with respect to investment decisions and inform themselves as to > applicable legal requirements, exchange control regulations and taxes in the > countries of their citizenship, residence or domicile. Past performance is > not indicative of future results. > > Winton takes reasonable steps to ensure the accuracy and integrity of its > communications, including emails. However Winton accepts no liability for any > materials transmitted. Emails are not secure and cannot be guaranteed to be > error free. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
