Re: [sqlite] Error in docs

[Dominique Devienne]

On Tue, Apr 9, 2019 at 9:41 AM Richard Hipp  wrote:

> On 4/9/19, Dominique Devienne  wrote:
> >>
> >> It defines the table and view:
> >> CREATE TABLE t1(a INT, b TEXT, c REAL);
> >> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
> >>
> >> It then states "The affinity of the v1.x column will be the same as the
> >> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
> >>
> >> However column t1.b is TEXT, not INTEGER.
> >>
> >
> > How to do determine that exactly? Via SQL? Debugging?
>
> If you compile with -DSQLITE_DEBUG, then there is a new undocumented
> function affinity().  After inserting a row into table t1:
>

Cool! Thanks for the heads up. But why debug only? It's as useful as
typeof() IMHO.
Obviously affinity-related-code is compiled-in in non-debug build, so I
don't see any
down side of unconditionally adding a function that if not explicitly used
as zero runtime
overhead, no? While SQLITE_DEBUG itself likely adds overhead.

Would be really useful for troubleshooting and teaching to always have it.
My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error in docs

[Richard Hipp]

On 4/9/19, Dominique Devienne  wrote:
>>
>> It defines the table and view:
>> CREATE TABLE t1(a INT, b TEXT, c REAL);
>> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
>>
>> It then states "The affinity of the v1.x column will be the same as the
>> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
>>
>> However column t1.b is TEXT, not INTEGER.
>>
>
> How to do determine that exactly? Via SQL? Debugging?

If you compile with -DSQLITE_DEBUG, then there is a new undocumented
function affinity().  After inserting a row into table t1:

INSERT INTO t1 values(1,2,3);

You can do:

SELECT affinity(x), affinity(y), affinity(z) FROM v1;

And get the answer:

'text','none','none'

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error in docs

[Dominique Devienne]

On Mon, Apr 8, 2019 at 7:58 PM Jim Dossey  wrote:

> I think I found an error in the documentation here:
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> <
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> >
>
> It defines the table and view:
> CREATE TABLE t1(a INT, b TEXT, c REAL);
> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
>
> It then states "The affinity of the v1.x column will be the same as the
> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
>
> However column t1.b is TEXT, not INTEGER.
>

How to do determine that exactly? Via SQL? Debugging?

There's no way to get "directly" the affinity of a column, that I know of,
you can only infer it from side-effects I believe. Thus my curiosity. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error in docs for WHERE clause in CREATE INDEX

[Richard Hipp]

Fixed in the draft documentation.

On 10/4/17, Jens Alfke  wrote:
> From https://www.sqlite.org/partialindex.html
>  :
>
>> The WHERE clause may not contain subqueries, references to other tables,
>> non-deterministic functions, or bound parameters. The LIKE, GLOB, MATCH,
>> and REGEXP operators in SQLite are implemented as functions by the same
>> name. Since functions are prohibited in the WHERE clause of a CREATE INDEX
>> statement, so too are the LIKE, GLOB, MATCH, and REGEXP operators.
>
> I believe the last sentence is false. The first sentence includes the
> qualifier “non-deterministic”, which was probably inserted when
> deterministic functions were introduced. But that invalidates the last
> sentence, since all the functions listed are deterministic.
>
> (If the last sentence is deleted, then of course the second sentence becomes
> pointless and should be deleted too.)
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users