Looking at MSDN the docs are not that fuzzy: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.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-boun...@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.getschemata 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.
