Hi, Am 06.04.2016 um 15:00 schrieb Cezary H. Noweta: > Hello, > > On 2016-04-06 09:43, Darren Duncan wrote: >> On 2016-04-05 10:19 AM, Richard Hipp wrote: > >>> It seems to me that the most consistent answer is that the "type" of >>> columns in a VIEW should always be an empty string. > >> That's only consistent if you do the same thing with base tables. > > Non--consistency: [snip] > > Column's affinity is still propagated: > > sqlite> CREATE TABLE b2 AS SELECT * FROM bv; > sqlite> PRAGMA table_info(b2); > 0|a|NUM|0||0 > > so the point is that ``PRAGMA table_info'' stopped displaying column's > affinity in case of views. > > On the other side, views are not tables so a consistency does not > require to preserve that info. In 3.11.x ``PRAGMA table_info'' was not > displaying an affinity in case of expressions, too: > > === SHELL 3.11.x === > sqlite> CREATE VIEW av2 AS SELECT CAST(+a AS NUMERIC) FROM a; > sqlite> PRAGMA table_info(av2); > 0|CAST(+a AS NUMERIC)||0||0 > > so the decision was to remove that view's info at all.
we have been bitten by this case, e.g. aggregate and expression not giving any meaningful info for views. But this change to PRAGMA table_info() throws out the child with the bathwater in a way. E.g. we currently try to find the types for a view in a multistage process: 1. Inspect PRAGMA table_info() 2. Look in a special hint table that states the type explicitly via configuration (e.g. for views with aggregate functions) 3. Select a row with LIMIT 1 and look at the result (fails if the view is empty). With the old behaviour, the case 1 would find a reliable result 99% of the time (for our schema). Now we either have to add additional configuration just for SQLite (because similar code works fine for Oracle and MS SQL Server), or hope that case 3 works. btw. would be nice to have an INFORMATION SCHEMA style view for this info instead of a pragma. Michael -- Michael Schlenker Senior Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Stra?e 1-3 Fax: +49 (421) 20153-41 28359 Bremen E-Mail: michael.schlenker at contact-software.com http://www.contact-software.com/ Registered office: Bremen, Germany Managing directors: Karl Heinz Zachries, Ralf Holtgrefe Court of register: Amtsgericht Bremen HRB 1321 -- Michael Schlenker Senior Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Stra?e 1-3 Fax: +49 (421) 20153-41 28359 Bremen E-Mail: michael.schlenker at contact-software.com http://www.contact-software.com/ Registered office: Bremen, Germany Managing directors: Karl Heinz Zachries, Ralf Holtgrefe Court of register: Amtsgericht Bremen HRB 1321
