Re: [sqlite] Possible bug in storing text values in numeric columns

[Richard Hipp]

On 10/13/19, Shawn Wagner  wrote:
> The documentation for a column with NUMERIC affinity says
>
>> When text data is inserted into a NUMERIC column, the storage class of
> the text is converted to INTEGER or REAL (in order of preference) if such
> conversion is lossless and reversible.
>

Thank you for the bug report.

The term "lossless" in the documentation is certainly very confusing
as it was used. Therefore the documentation has been revised to avoid
using the word "lossless" and to be more precise about when automatic
type conversions occur and when they do not.

I consider this to be a documentation bug, not an SQLite bug.

-- 
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] Possible bug in storing text values in numeric columns

[Igor Tandetnik]

On 10/13/2019 8:04 PM, Shawn Wagner wrote:

That's what I told the guy having the original issue to do, yes. That's not
important.

My concern is why a conversion that's only supposed to happen if it's
lossless is in fact happening and causing data loss.


You define the term "lossless" differently than that article does. The 
conversion is lossless under the article's definition, even while it's not lossless under 
the definition you insist upon (but which makes no sense for a column of NUMERIC 
affinity).
--
Igor Tandetnik


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

Re: [sqlite] Possible bug in storing text values in numeric columns

[Shawn Wagner]

That's what I told the guy having the original issue to do, yes. That's not
important.

My concern is why a conversion that's only supposed to happen if it's
lossless is in fact happening and causing data loss.

On Sun, Oct 13, 2019, 4:48 PM Igor Tandetnik  wrote:

> On 10/13/2019 7:25 PM, Shawn Wagner wrote:
> > I wouldn't call that conversion, or any other, lossless unless it can be
> > turned back into a string that's character for character identical with
> the
> > one that was originally inserted.
>
> If you want the text preserved character for character, store it in a
> column with TEXT affinity. NUMERIC makes little sense for this requirement.
> --
> Igor  Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in storing text values in numeric columns

[Igor Tandetnik]

On 10/13/2019 7:25 PM, Shawn Wagner wrote:

I wouldn't call that conversion, or any other, lossless unless it can be
turned back into a string that's character for character identical with the
one that was originally inserted.


If you want the text preserved character for character, store it in a column 
with TEXT affinity. NUMERIC makes little sense for this requirement.
--
Igor  Tandetnik


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

Re: [sqlite] Possible bug in storing text values in numeric columns

[Keith Medcalf]

sqlite> create table x(x numeric);
sqlite> insert into x values ('0012');
sqlite> select typeof(x), x from x;
integer|12
sqlite> select printf('%04d', x) from x;
0012

Presentation is a user/application problem.  Not a database data problem.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Shawn Wagner
>Sent: Sunday, 13 October, 2019 17:26
>To: SQLite mailing list 
>Subject: Re: [sqlite] Possible bug in storing text values in numeric
>columns
>
>I wouldn't call that conversion, or any other, lossless unless it can be
>turned back into a string that's character for character identical with
>the
>one that was originally inserted.
>
>On Sun, Oct 13, 2019, 4:10 PM Igor Tandetnik  wrote:
>
>> On 10/13/2019 5:11 PM, Shawn Wagner wrote:
>> > The documentation for a column with NUMERIC affinity says
>> >
>> >> When text data is inserted into a NUMERIC column, the storage class
>of
>> > the text is converted to INTEGER or REAL (in order of preference) if
>such
>> > conversion is lossless and reversible.
>>
>> "Lossless and reversible" here clearly means that the numerical value
>is
>> preserved, not that the exact text representation is. Thus, a couple
>> paragraphs down in the same article, you'll find
>>
>> "A string might look like a floating-point literal with a decimal point
>> and/or exponent notation but as long as the value can be expressed as
>an
>> integer, the NUMERIC affinity will convert it into an integer. Hence,
>the
>> string '3.0e+5' is stored in a column with NUMERIC affinity as the
>integer
>> 30, not as the floating point value 30.0."
>>
>> It is clear in this example that '3.0e+5' will in fact get coerced to a
>> number, and that number will *not* in fact be rendered as '3.0e+5' when
>> converted back to text.
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Possible bug in storing text values in numeric columns

[J. King]

On October 13, 2019 7:25:50 p.m. EDT, Shawn Wagner  
wrote:
>I wouldn't call that conversion, or any other, lossless unless it can
>be
>turned back into a string that's character for character identical with
>the
>one that was originally inserted.

It's lossless if you consider the input as the number itself, not as a specific 
representation of that number.
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in storing text values in numeric columns

[Shawn Wagner]

Yes, I know. (This all comes from someone on stack overflow who used
"string" as a column type and thus ran into this issue because that of
course results in numeric affinity)

On Sun, Oct 13, 2019, 4:27 PM Simon Slavin  wrote:

> On 13 Oct 2019, at 10:11pm, Shawn Wagner  wrote:
>
> > As you can see, the leading zeros in the original string are gone and
> it's been converted to an integer. This seems to violate the "lossless and
> reversible" constraint. Shouldn't it be kept as text?
>
> You defined the column as 'numeric'.  Had you defined it as 'text' you
> would get different behaviour.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in storing text values in numeric columns

[Simon Slavin]

On 13 Oct 2019, at 10:11pm, Shawn Wagner  wrote:

> As you can see, the leading zeros in the original string are gone and it's 
> been converted to an integer. This seems to violate the "lossless and 
> reversible" constraint. Shouldn't it be kept as text?

You defined the column as 'numeric'.  Had you defined it as 'text' you would 
get different behaviour.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in storing text values in numeric columns

[Shawn Wagner]

I wouldn't call that conversion, or any other, lossless unless it can be
turned back into a string that's character for character identical with the
one that was originally inserted.

On Sun, Oct 13, 2019, 4:10 PM Igor Tandetnik  wrote:

> On 10/13/2019 5:11 PM, Shawn Wagner wrote:
> > The documentation for a column with NUMERIC affinity says
> >
> >> When text data is inserted into a NUMERIC column, the storage class of
> > the text is converted to INTEGER or REAL (in order of preference) if such
> > conversion is lossless and reversible.
>
> "Lossless and reversible" here clearly means that the numerical value is
> preserved, not that the exact text representation is. Thus, a couple
> paragraphs down in the same article, you'll find
>
> "A string might look like a floating-point literal with a decimal point
> and/or exponent notation but as long as the value can be expressed as an
> integer, the NUMERIC affinity will convert it into an integer. Hence, the
> string '3.0e+5' is stored in a column with NUMERIC affinity as the integer
> 30, not as the floating point value 30.0."
>
> It is clear in this example that '3.0e+5' will in fact get coerced to a
> number, and that number will *not* in fact be rendered as '3.0e+5' when
> converted back to text.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in storing text values in numeric columns

[Igor Tandetnik]

On 10/13/2019 5:11 PM, Shawn Wagner wrote:

The documentation for a column with NUMERIC affinity says


When text data is inserted into a NUMERIC column, the storage class of

the text is converted to INTEGER or REAL (in order of preference) if such
conversion is lossless and reversible.


"Lossless and reversible" here clearly means that the numerical value is 
preserved, not that the exact text representation is. Thus, a couple paragraphs down in 
the same article, you'll find

"A string might look like a floating-point literal with a decimal point and/or 
exponent notation but as long as the value can be expressed as an integer, the NUMERIC 
affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a 
column with NUMERIC affinity as the integer 30, not as the floating point value 
30.0."

It is clear in this example that '3.0e+5' will in fact get coerced to a number, 
and that number will *not* in fact be rendered as '3.0e+5' when converted back 
to text.
--
Igor Tandetnik

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

Re: [sqlite] Possible bug in storing text values in numeric columns

[Shawn Wagner]

3.30, though it goes back to at least 3.8.7 (the oldest version I have
available to test with)

On Sun, Oct 13, 2019, 3:03 PM Igor Korot  wrote:

> Hi,
>
> On Sun, Oct 13, 2019 at 4:12 PM Shawn Wagner 
> wrote:
> >
> > The documentation for a column with NUMERIC affinity says
> >
> > > When text data is inserted into a NUMERIC column, the storage class of
> > the text is converted to INTEGER or REAL (in order of preference) if such
> > conversion is lossless and reversible.
> >
> > But consider:
> >
> > sqlite> create table foo(bar numeric);
> > sqlite> insert into foo values ('0012');
> > sqlite> select bar, typeof(bar) from foo;bar typeof(bar)
> > --  ---12  integer
> >
> >
> > As you can see, the leading zeros in the original string are gone and
> it's
> > been converted to an integer. This seems to violate the "lossless and
> > reversible" constraint. Shouldn't it be kept as text?
>
> What version of SQLite do you use?
>
> Thank you.
>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in storing text values in numeric columns

[Igor Korot]

Hi,

On Sun, Oct 13, 2019 at 4:12 PM Shawn Wagner  wrote:
>
> The documentation for a column with NUMERIC affinity says
>
> > When text data is inserted into a NUMERIC column, the storage class of
> the text is converted to INTEGER or REAL (in order of preference) if such
> conversion is lossless and reversible.
>
> But consider:
>
> sqlite> create table foo(bar numeric);
> sqlite> insert into foo values ('0012');
> sqlite> select bar, typeof(bar) from foo;bar typeof(bar)
> --  ---12  integer
>
>
> As you can see, the leading zeros in the original string are gone and it's
> been converted to an integer. This seems to violate the "lossless and
> reversible" constraint. Shouldn't it be kept as text?

What version of SQLite do you use?

Thank you.

> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug in storing text values in numeric columns

[Shawn Wagner]

The documentation for a column with NUMERIC affinity says

> When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if such
conversion is lossless and reversible.

But consider:

sqlite> create table foo(bar numeric);
sqlite> insert into foo values ('0012');
sqlite> select bar, typeof(bar) from foo;bar typeof(bar)
--  ---12  integer


As you can see, the leading zeros in the original string are gone and it's
been converted to an integer. This seems to violate the "lossless and
reversible" constraint. Shouldn't it be kept as text?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in Alter Table

[Balaji Ramanathan]

Thank you, Richard.  I can understand how not naming the columns of a view
can lead to ambiguities and other problems down the line.  Hopefully the
documentation will be updated so that users are aware that the alter table
command can't really deal with cascading effects such as in the case of
views referring to other views, etc.

Is there a way to unravel a complex schema so that you can identify which
views are based directly on tables and which views refer to other views,
and what those other views are?  At this point, the simple solution seems
to be to just use .dump to dump out the contents of the database into a
text file, use search and replace to do the column rename, and then use
.read to read it back into a SQLite database.  Anything I have to watch out
for if I do the above?

Thank you.

Balaji Ramanathan

From: Richard Hipp 
To: SQLite mailing list 
Cc:
Bcc:
Date: Mon, 26 Nov 2018 14:11:54 -0500
Subject: Re: [sqlite] Possible bug in Alter Table
On 11/25/18, Balaji Ramanathan  wrote:
> I expected the Alter Table command to find and replace all occurrences of
> that column name in my schema with the new name.

Here is simplified SQL that illustrates the problem:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x FROM t1;
CREATE VIEW v2 AS SELECT x FROM v1;
ALTER TABLE t1 RENAME x TO y;

The ALTER TABLE fails because after changing the name of t1.x to t1.y,
the definition of the view v2 is no longer valid.

This is not something we intend to "fix" in SQLite.  The root of the
problem is that the column names for the v1 view are not specified.
And since they are not specified, that means SQLite is free to pick
whatever arbitrary names it wants for those columns.  The definition
of view v2 depends on one particular algorithm for picking the column
names of view v1, but there are no guarantees that every version of
SQLite will use that particular algorithm.  Hence, the definition of
view v2 is under-specified and prone to failure, such as in this case.

If you are careful to defined the names of all columns within your
views, then the problem goes away.  For example:

CREATE TABLE t1(x);
CREATE VIEW v1(y) AS SELECT x FROM t1;
CREATE VIEW v2(z) AS SELECT y FROM v1;
ALTER TABLE t1 RENAME x TO y;

Or:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x AS y FROM t1;
CREATE VIEW v2 AS SELECT y AS z FROM v1;
ALTER TABLE t1 RENAME x TO y;

If you do not specify the names of columns in views, then SQLite is
free to choose whatever names it wants for those columns, and the
choices might shift after an ALTER TABLE, which could then break
queries and/or downstream views.  So it is best not to do that.

Admittedly, this is not well-documented.  I will strive to improve the
documentation for the next release.  Perhaps I will also add a
"warning" mechanism to alert programmers to gotchas like this in some
subsequent release, though there probably is not time to get warnings
in to the forthcoming 3.26.0 release.

-- 
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] Possible bug in Alter Table

[Petite Abeille]

> On Nov 27, 2018, at 06:16, Wout Mertens  wrote:
> 
> If it's on a mac, this terrible misfeature can be turned off in system
> preferences - keyboard - text - smart quotes.

Oh my... right you are :|

Grrr indeed.

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

Re: [sqlite] Possible bug in Alter Table

[Wout Mertens]

>
>
> You have something mucking about and "helping you" to be cutie-pie.  If
> you turn that crap off, your problems will go away...
>

If it's on a mac, this terrible misfeature can be turned off in system
preferences - keyboard - text - smart quotes.

I lost a couple hours this way too, I paired with a colleague on something
and then sent the result to me. Sometime later I notice that CSS is broken
for our app and I finally figure out it's a cute quote disabling all the
rules from where it is. Grr.

Wout.

>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in Alter Table

[Keith Medcalf]

On Monday, 26 November, 2018 12:19, Petite Abeille  
wrote:

...

> Talking of which, the CLI doesn’t seem to handle the following
> statement very gracefully:
>
> sqlite> select DATE '1998-12-25’;
>   ...>
>   ...>
>   …>

>Note how the CLI doesn’t recognize the semicolon marking the end-of-
>statement and expects more input.

>sqlite3 -version
>
>3.25.3 2018-11-05 20:37:38
>89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2

That is because you are using a "goofy quote" and not an ASCII Quotation mark.  
You can tell cuz it is all curvy and cutie-pie -- it does *not* close the 
quoted string because it is just-another-unicode-character with no special 
meaning ... you need to enter a closing quote-mark in order to terminate the 
quoted string ...

’ is not a closing quote.  A closing quote looks like this ' and matches the 
opening quote that you used.  I also doubt that a text mode program used the … 
character in its prompt for more input (especially since the two lines above 
that use three ascii periods (...) and not the single-character-unicode … .  

You have something mucking about and "helping you" to be cutie-pie.  If you 
turn that crap off, your problems will go away...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





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

Re: [sqlite] Possible bug in Alter Table

[Petite Abeille]

> On Nov 26, 2018, at 21:16, Shawn Wagner  wrote:
> 
> It's waiting on a plain single quote to end the string. You have a Unicode
> smart quote character U+2019 (’) instead of a ' at the end before the
> semicolon, which doesn't count.

D’oh. Facepalm. Right you are. Long live Unicode! :)

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

Re: [sqlite] Possible bug in Alter Table

[Shawn Wagner]

It's waiting on a plain single quote to end the string. You have a Unicode
smart quote character U+2019 (’) instead of a ' at the end before the
semicolon, which doesn't count.

On Mon, Nov 26, 2018, 11:19 AM Petite Abeille 
>
> > On Nov 26, 2018, at 20:11, Richard Hipp  wrote:
> >
> > though there probably is not time to get warnings in to the forthcoming
> 3.26.0 release.
>
> Talking of which, the CLI doesn’t seem to handle the following statement
> very gracefully:
>
> sqlite> select DATE '1998-12-25’;
>   ...>
>   ...>
>   …>
>
> Note how the CLI doesn’t recognize the semicolon marking the
> end-of-statement and expects more input.
>
> sqlite3 -version
>
> 3.25.3 2018-11-05 20:37:38
> 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in Alter Table

[Petite Abeille]

> On Nov 26, 2018, at 20:25, Richard Hipp  wrote:
> 
> Unable to repro:

Hmmm… ok… then… local problem of some type…

$ uname -a
Darwin 18.2.0 Darwin Kernel Version 18.2.0: Fri Oct  5 19:41:49 PDT 2018; 
root:xnu-4903.221.2~2/RELEASE_X86_64 x86_64

$ brew info sqlite3
sqlite: stable 3.25.3 (bottled) [keg-only]

==> Dependencies
Required: readline ✔
==> Options
--with-fts
Enable the FTS3 module
--with-fts5
Enable the FTS5 module (experimental)
--with-functions
Enable more math and string functions for SQL queries
--with-json1
Enable the JSON1 extension


No clue what could interfere with the CLI.



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

Re: [sqlite] Possible bug in Alter Table

[Richard Hipp]

On 11/26/18, Petite Abeille  wrote:
> the CLI doesn’t seem to handle the following statement
> very gracefully:
>
> sqlite> select DATE '1998-12-25’;
>   ...>
>   ...>
>   …>

Unable to repro:

SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select DATE'1998-12-25';
Error: no such column: DATE
sqlite> select DATE '1998-12-25';
Error: no such column: DATE
sqlite>



-- 
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] Possible bug in Alter Table

[Petite Abeille]

> On Nov 26, 2018, at 20:11, Richard Hipp  wrote:
> 
> though there probably is not time to get warnings in to the forthcoming 
> 3.26.0 release.

Talking of which, the CLI doesn’t seem to handle the following statement very 
gracefully:

sqlite> select DATE '1998-12-25’;
  ...>
  ...>
  …>

Note how the CLI doesn’t recognize the semicolon marking the end-of-statement 
and expects more input.  

sqlite3 -version

3.25.3 2018-11-05 20:37:38 
89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in Alter Table

[Richard Hipp]

On 11/25/18, Balaji Ramanathan  wrote:
> I expected the Alter Table command to find and replace all occurrences of
> that column name in my schema with the new name.

Here is simplified SQL that illustrates the problem:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x FROM t1;
CREATE VIEW v2 AS SELECT x FROM v1;
ALTER TABLE t1 RENAME x TO y;

The ALTER TABLE fails because after changing the name of t1.x to t1.y,
the definition of the view v2 is no longer valid.

This is not something we intend to "fix" in SQLite.  The root of the
problem is that the column names for the v1 view are not specified.
And since they are not specified, that means SQLite is free to pick
whatever arbitrary names it wants for those columns.  The definition
of view v2 depends on one particular algorithm for picking the column
names of view v1, but there are no guarantees that every version of
SQLite will use that particular algorithm.  Hence, the definition of
view v2 is under-specified and prone to failure, such as in this case.

If you are careful to defined the names of all columns within your
views, then the problem goes away.  For example:

CREATE TABLE t1(x);
CREATE VIEW v1(y) AS SELECT x FROM t1;
CREATE VIEW v2(z) AS SELECT y FROM v1;
ALTER TABLE t1 RENAME x TO y;

Or:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x AS y FROM t1;
CREATE VIEW v2 AS SELECT y AS z FROM v1;
ALTER TABLE t1 RENAME x TO y;

If you do not specify the names of columns in views, then SQLite is
free to choose whatever names it wants for those columns, and the
choices might shift after an ALTER TABLE, which could then break
queries and/or downstream views.  So it is best not to do that.

Admittedly, this is not well-documented.  I will strive to improve the
documentation for the next release.  Perhaps I will also add a
"warning" mechanism to alert programmers to gotchas like this in some
subsequent release, though there probably is not time to get warnings
in to the forthcoming 3.26.0 release.

-- 
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] Possible bug in Alter Table

[Digital Dog]

On Mon, Nov 26, 2018 at 2:52 AM Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> SQLite> Alter Table Trip rename column StartGMTOffset to StartUTCOffset;
> Error: error in view CumulativeStatisticsByPlaceName after rename: no such
> column: StartGMTOffset
>
> I was able to reproduce this behaviour:

C:\temp>sqlite3 bug.sqlite
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
sqlite> create table t1 (c1 int);
sqlite> create view v1 as select c1 from t1;
sqlite> create view v11 as select null from t1 left join v1 on v1.c1=t1.c1;
sqlite> alter table t1 rename column c1 to c2;
Error: error in view v11 after rename: no such column: v1.c1
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug in Alter Table

[Balaji Ramanathan]

Hi,

I am writing to report a possible bug in the Alter Table command.  It
seems to fail on a somewhat complex schema with multiple tables and views,
and views that reference other views.  I was not able to come up with a
simple one table, one view schema where this error actually shows up.  That
is why I am not able to post the schema here for you to view directly.  So,
please refer to the database test.db located on Google Drive at the
following address:
https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg

In this database, I have a table Trip, that has a column
"StartGMTOffset".  I have multiple views referencing this column, but no
indexes or triggers on this column.  I am running SQLite version 3.25.3.  I
now issue the following command at the SQLite command prompt and I get the
response below:

SQLite> Alter Table Trip rename column StartGMTOffset to StartUTCOffset;
Error: error in view CumulativeStatisticsByPlaceName after rename: no such
column: StartGMTOffset

Well, duh!  There is no such column because I just renamed it.  And I
expected the Alter Table command to find and replace all occurrences of
that column name in my schema with the new name.  But maybe, it only works
for a table and views that reference it directly, but not views that
reference views that reference the table?  I am not sure.  But I thought I
would report it anyway.

I apologize if I misunderstood the documentation regarding the alter
table command, or mangled the syntax in some way.  Thank you.

Balaji Ramanathan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug in window function queries.

[Shawn Wagner]

Been playing around with the new window functions in 3.25, and ran into
something that looks like an infinite loop.

Working with the t1 table many of the examples in the documentation use,
I've come up with a minimal test case:

sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id >
1 order by b;
id  b   x
--  --  --
2   B   two
3   C   three
4   D   one
5   E
6   F   two
7   G   three

is all well and good. However, after adding a LIMIT to the query:

sqlite> select id, b, lead(c, 1) over (order by c) as x from t1 where id >
1 order by b limit 1;
(time goes by)
^CError: interrupted

A LIMIT of just a few rows causes the query to hang and never produce any
output while sqlite3 uses 100% CPU.

LIMIT 4 and up works, but change the comparison to >= and LIMIT 4 also
freezes, but LIMIT 5 works. Take out the ORDER BY b and no freeze.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug affecting 3.22 and 3.23 - load_extension()

[Richard Hipp]

I have checked in a change to address this on trunk.

Meanwhile, your work-around is to include the ".so" suffix on the
library name.  xample:

SELECT load_extension('mod_spatiallite.so');

On 5/6/18, a.furi...@lqt.it  wrote:
> it seems that some unexpected regression is affecting load_extension()
> on Linux when using the most recent versions of SQLite (3.22 and 3.23).
>
> my test configuration:
> - sqlite3 installed on the current directory
> - extension module installed on /usr/local/lib
>(/usr/local/lib/mod_spatialite.so)
>
> $ export "LD_LIBRARY_PATH=/usr/local/lib"
> $ ./sqlite3
> sqlite> SELECT load_extension('mod_spatialite');
>
> - the extension module is successfully loaded when using 3.20.1
> - but it fails when using 3.22.0 or 3.23.1
>
> digging into the code I was finally able to reestablish the expected
> behavior of load_extension() after applying the following patch:
>
> sqlite3.c (v.3.23.1) - line 115917 - sqlite3LoadExtension()
> ---
> handle = sqlite3OsDlOpen(pVfs, zFile);
> #if SQLITE_OS_UNIX || SQLITE_OS_WIN
>for(ii=0; ii  char *zAltFile = sqlite3_mprintf("%s.%s", zFile, azEndings[ii]);
>  int bExists = 0;
>  if( zAltFile==0 ) return SQLITE_NOMEM_BKPT;
> /*
>  sqlite3OsAccess(pVfs, zAltFile, SQLITE_ACCESS_EXISTS, );
>  if( bExists )
> */
>  handle = sqlite3OsDlOpen(pVfs, zAltFile);
>  sqlite3_free(zAltFile);
>}
> #endif
> ---
>
> it seems that sqlite3OsAccess() fails to correctly locate the
> module to be loaded because it checks just the current working
> directory whilst sqlite3OsDlOpen() correctly searches more
> directories, as the additional ones defined by LD_LIBRARY_PATH.
>
> bye
> Sandro Furieri (developer and maintainer of SpatiaLite)
> ___
> 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

[sqlite] possible bug affecting 3.22 and 3.23 - load_extension()

[a . furieri]

it seems that some unexpected regression is affecting load_extension()
on Linux when using the most recent versions of SQLite (3.22 and 3.23).

my test configuration:
- sqlite3 installed on the current directory
- extension module installed on /usr/local/lib
  (/usr/local/lib/mod_spatialite.so)

$ export "LD_LIBRARY_PATH=/usr/local/lib"
$ ./sqlite3
sqlite> SELECT load_extension('mod_spatialite');

- the extension module is successfully loaded when using 3.20.1
- but it fails when using 3.22.0 or 3.23.1

digging into the code I was finally able to reestablish the expected
behavior of load_extension() after applying the following patch:

sqlite3.c (v.3.23.1) - line 115917 - sqlite3LoadExtension()
---
handle = sqlite3OsDlOpen(pVfs, zFile);
#if SQLITE_OS_UNIX || SQLITE_OS_WIN
  for(ii=0; ii

Re: [sqlite] possible bug: select clause column alias shadowing

[Clemens Ladisch]

Jean-Christophe Deschamps wrote:
> At 23:36 18/03/2018, you wrote:
>> In other words, aliases in the SELECT clause are evaluated _after_ the
>> FROM and WHERE clauses are done.
>
> I must be misinterpreting:

I was talking about the SQL standard.  (I might have mentioned that somewhere 
...)

> select a int, printf('<%5i>', a) fmt from t where fmt like '%>';
>
> Here WHERE understands what fmt refers to.

SQLite tries to be helpful.  But when in doubt (i.e., when an alias tries to
shadow a real column), it chooses the standard-conforming interpretation.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug: select clause column alias shadowing

[petern]

Compared to PostgreSQL, SQLite does a better job here when there is no
input column collision.

The column collision case below returns no rows in both SQLite and
PostgreSQL:

WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS a FROM t WHERE a='foo!';

But the following edit with intermediating alias column b produces 'ERROR:
column "b" does not exist' in PostgreSQL:

sqlite> WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS b FROM t WHERE
b='foo!';
b
foo!

A safer coding style would be to use an intermediating query/view/cte when
any input column's meaning is being modified:

sqlite> WITH t(a) AS (VALUES ('foo')), u AS (SELECT a||'!' AS a FROM t)
SELECT a FROM u WHERE a='foo!';
a
foo!

Peter



On Sun, Mar 18, 2018 at 2:31 AM, Moritz Bruder 
wrote:

> Hi,
>
> I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22
> 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d).
> Consider the following test case:
>
> CREATE TABLE test (name varchar);
> INSERT INTO test VALUES ("foo"),("bar");
>
> -- Returns a single row with a single column: 'foo!'
> SELECT (test.name || '!') AS tname
> FROM test
> WHERE tname = 'foo!'
>
> --Returns an empty result.
> SELECT (test.name || '!') AS name
> FROM test
> WHERE name = 'foo!';
>
> What happens is that the identifier "name", defined in the SELECT-clause,
> gets shadowed by the table's column "name". I'm not exactly sure what the
> SQL standard says but it is wrong in my opinion. I expect it to be the
> other way round.Let me know whether you consider it a bug.
>
>
> Best wishes,
>
> Moritz
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug: select clause column alias shadowing

[Igor Tandetnik]

On 3/18/2018 5:31 AM, Moritz Bruder wrote:

I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d). Consider the 
following test case:

     CREATE TABLE test (name varchar);
     INSERT INTO test VALUES ("foo"),("bar");

-- Returns a single row with a single column: 'foo!'
SELECT (test.name || '!') AS tname
FROM test
WHERE tname = 'foo!'

     --Returns an empty result.
     SELECT (test.name || '!') AS name
     FROM test
     WHERE name = 'foo!';

What happens is that the identifier "name", defined in the SELECT-clause, gets shadowed 
by the table's column "name".


If I recall correctly, SQL standard doesn't allow aliases from SELECT to be 
used in WHERE clause, only in ORDER BY and, possibly, HAVING (I'm not sure of 
the latter). SQLite allows aliases in WHERE as an extension, but prefers the 
real column name in case of conflict, so as to match the behavior of other DBMS.
--
Igor Tandetnik

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

Re: [sqlite] possible bug: select clause column alias shadowing

[Jean-Christophe Deschamps]

At 23:36 18/03/2018, you wrote:

In other words, aliases in the SELECT clause are evaluated _after_ the
FROM and WHERE clauses are done.

The order of the SELECT/WHERE clauses in the SQL syntax is misleading;
the actual behaviour would be better represented by something like this:

( FROM test
  WHERE name = 'foo!' )
SELECT test.name || '!' AS name;


I must be misinterpreting:

create temp table t (a int);
insert into t values (1), (2), (6);
select a int, printf('<%5i>', a) fmt from t where fmt like '%>';

int fmt
1   <1>
2   <2>
6   <6>

Here WHERE understands what fmt refers to.


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

Re: [sqlite] possible bug: select clause column alias shadowing

[Clemens Ladisch]

Simon Slavin wrote:
> As best I can find, SQL92 does not specify what happens when you choose
> an AS clause giving a value name the same as a column.

| 7.3  
|
|  Function
|
|Specify a table or a grouped table.
|
|  Format
|
| ::=
| 
| [  ]
| [  ]
| [  ]
|
|  [...]
|  General Rules
|
|1) If all optional clauses are omitted, then the result of the  is the same as the result of the .
|   Otherwise, each specified clause is applied to the result of
|   the previously specified clause and the result of the  is the result of the application of the last specified
|   clause.
|
| [...]
|
| 7.9 
|
|  Function
|
|Specify a table derived from the result of a .
|
|  Format
|
| ::=
| SELECT [  ]  
|
| ::=
|   
| |  [ {   }... ]
|
| ::=
|   
| |   
|
| ::=  [  ]
|
|  Syntax Rules
|
|1) Let T be the result of the .
|[...]
|
|6) Each  directly contained in each  ... shall unambiguously reference a column of T.
|
|  [...]
|  General Rules
|
|  1) a) ii) [...] each  is applied to each row of T
|yielding a table of M rows, where M is the cardinality of T.
|The i-th column of the table contains the values derived by
|the evaluation of the i-th .

In other words, aliases in the SELECT clause are evaluated _after_ the
FROM and WHERE clauses are done.

The order of the SELECT/WHERE clauses in the SQL syntax is misleading;
the actual behaviour would be better represented by something like this:

( FROM test
  WHERE name = 'foo!' )
SELECT test.name || '!' AS name;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug: select clause column alias shadowing

[Simon Slavin]

On 18 Mar 2018, at 9:31am, Moritz Bruder  wrote:

> I'm not exactly sure what the SQL standard says 

As best I can find, SQL92 does not specify what happens when you choose an AS 
clause giving a value name the same as a column.  It doesn't go into much 
detail at all about applying "AS" to a value.

It does go into more detail about applying "AS" to a table name.  Section 5.4 
paragraph 12 it says

An  that is a  is associated with
a table within a particular scope. The scope of a  is either a , , or
 (see Subclause 6.3, "").
Scopes may be nested. In different scopes, the same  may be associated with different tables or with the same
table.

We can apply this to a 'correlation name' for a value rather than a table.  If 
I read this right, it suggests you're right: the use of 'name' should equate to 
your definition '(test.name || '!')', not to 'test.name'.

However if I see code where the programmer picked an alias the same as a real 
column name I'd question the quality of that programmer's thinking.  I get a 
"Do not do this." feeling.

Cue comment from the development team about backward compatibility.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible bug: select clause column alias shadowing

[Moritz Bruder]

Hi,

I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22 
18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d). 
Consider the following test case:


    CREATE TABLE test (name varchar);
    INSERT INTO test VALUES ("foo"),("bar");

-- Returns a single row with a single column: 'foo!'
SELECT (test.name || '!') AS tname
FROM test
WHERE tname = 'foo!'

    --Returns an empty result.
    SELECT (test.name || '!') AS name
    FROM test
    WHERE name = 'foo!';

What happens is that the identifier "name", defined in the 
SELECT-clause, gets shadowed by the table's column "name". I'm not 
exactly sure what the SQL standard says but it is wrong in my opinion. I 
expect it to be the other way round.Let me know whether you consider it 
a bug.



Best wishes,

Moritz
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug: separator string and quote mode

[Clemens Ladisch]

p dev wrote:
> https://www.sqlite.org/cli.html  5. Changing Output Formats
>
> All columns are separated from each other by a comma (or whatever alternative 
> character is selected using ".separator").

This is indeed a bug in the documentation; quote mode ignores the .separator 
setting.

> I have been using SQLite for a small data-processing application.
> I am hoping to get quote mode output with a field separator that is not a 
> comma.

Why?  What is the actual problem you're trying to solve?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug: separator string and quote mode

[petern]

Pamela.  Shell .mode quote uses hardwired separator, presumably to rule out
errors when the mode is requested to generate SQL compatible strings.

It seems to me the enhancement you're requesting could be made backward
compatible by using  p->colSeparator instead of the hardwired ',' and then
setting the  p->colSeparator to the backward compatible comma in the .mode
quote meta-command and then allowing the user to subsequently override with
the .separator command.

Maybe there is a different reason for the hardwiring or reason why it can't
be changed.  That would be Richard's decision.

Peter
--
/*from shell.c*/
case MODE_Quote: {
  if( azArg==0 ) break;
  if( p->cnt==0 && p->showHeader ){
for(i=0; i0 ) raw_printf(p->out, ",");
  output_quoted_string(p->out, azCol[i]);
}
raw_printf(p->out,"\n");
  }
  p->cnt++;
  for(i=0; i0 ) raw_printf(p->out, ",");
if( (azArg[i]==0) || (aiType && aiType[i]==SQLITE_NULL) ){
  utf8_printf(p->out,"NULL");
}else if( aiType && aiType[i]==SQLITE_TEXT ){
  output_quoted_string(p->out, azArg[i]);
}else if( aiType && aiType[i]==SQLITE_INTEGER ){
  utf8_printf(p->out,"%s", azArg[i]);
}else if( aiType && aiType[i]==SQLITE_FLOAT ){
  char z[50];
  double r = sqlite3_column_double(p->pStmt, i);
  sqlite3_snprintf(50,z,"%!.20g", r);
  raw_printf(p->out, "%s", z);
}else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){
  const void *pBlob = sqlite3_column_blob(p->pStmt, i);
  int nBlob = sqlite3_column_bytes(p->pStmt, i);
  output_hex_blob(p->out, pBlob, nBlob);
}else if( isNumber(azArg[i], 0) ){
  utf8_printf(p->out,"%s", azArg[i]);
}else{
  output_quoted_string(p->out, azArg[i]);
}
  }
  raw_printf(p->out,"\n");
  break;
}


On Sun, Jan 14, 2018 at 9:29 AM, p dev  wrote:

> I have been using SQLite for a small data-processing application.
> I am hoping to get quote mode output with a field separator that is not a
> comma.Many thanks,
> Pamela--
> Problem
>
> Quote mode output ignores separator string
> Background
> https://www.sqlite.org/cli.html5. Changing Output Formats
> ...
> In "quote" mode, the output is formatted as SQL literals. Strings are
> enclosed in single-quotes and internal single-quotes are escaped by
> doubling. Blobs are displayed in hexadecimal blob literal notation (Ex:
> x'abcd'). Numbers are displayed as ASCII text and NULL values are shown as
> "NULL". All columns are separated from each other by a comma (or whatever
> alternative character is selected using ".separator").
> ...
>
> Demonstration
>
> SQLite version 3.21.0 2017-10-24 18:55:49
> Enter ".help" for usage hints.
> sqlite> create table tbl1(one varchar(10), two smallint);
> sqlite> insert into tbl1 values('hello!',10);
> sqlite> insert into tbl1 values('goodbye', 20);
> sqlite> insert into tbl1 values(null, 30);
> sqlite> select * from tbl1;
> hello!|10
> goodbye|20
> |30
>
> sqlite> .separator #
> sqlite> select * from tbl1;
> hello!#10
> goodbye#20
> #30
>
> sqlite> .mode quote
> sqlite> select * from tbl1;
> 'hello!',10
> 'goodbye',20
> NULL,30
>
>
> Expected Output
>
> sqlite> select * from tbl1;'hello!'#10
> 'goodbye'#20
> NULL#30
>
> sqlite> .show
> echo: off
>  eqp: off
>  explain: auto
>  headers: on
> mode: quote
>nullvalue: ""
>   output: stdout
> colseparator: "#"
> rowseparator: "\n"
>stats: off
>width:
> filename: data.db3
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible bug: separator string and quote mode

[p dev]

I have been using SQLite for a small data-processing application.
I am hoping to get quote mode output with a field separator that is not a 
comma.Many thanks,
Pamela--
Problem

Quote mode output ignores separator string
Background
https://www.sqlite.org/cli.html5. Changing Output Formats
...
In "quote" mode, the output is formatted as SQL literals. Strings are enclosed 
in single-quotes and internal single-quotes are escaped by doubling. Blobs are 
displayed in hexadecimal blob literal notation (Ex: x'abcd'). Numbers are 
displayed as ASCII text and NULL values are shown as "NULL". All columns are 
separated from each other by a comma (or whatever alternative character is 
selected using ".separator").
...

Demonstration

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> insert into tbl1 values(null, 30);
sqlite> select * from tbl1;
hello!|10
goodbye|20
|30

sqlite> .separator #
sqlite> select * from tbl1;
hello!#10
goodbye#20
#30

sqlite> .mode quote
sqlite> select * from tbl1;
'hello!',10
'goodbye',20
NULL,30


Expected Output

sqlite> select * from tbl1;'hello!'#10
'goodbye'#20
NULL#30

sqlite> .show
    echo: off
 eqp: off
 explain: auto
 headers: on
    mode: quote
   nullvalue: ""
  output: stdout
colseparator: "#"
rowseparator: "\n"
   stats: off
   width:
    filename: data.db3

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

Re: [sqlite] Possible bug when adding "on delete cascade" via DB Browser for SQLite

[Chris Locke]

DB Browser for SQLite is a third party product which just uses SQLite.  Any
support issues should be directed to their gitHub support page.
https://github.com/sqlitebrowser/sqlitebrowser/issues

To confirm whether its an issue in DB Browser for SQLite or SQLite itself,
you can 'reproduce' the issue using the SQLite command line tool.  If that
too fails, then the issue should be confirmed here where it will get
properly investigated.

I know DB Browser for SQLite is still in heavy development, and it does
have a number of little quirks like this. ;)


Thanks,
Chris


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Jan 13, 2018 at 6:02 PM, Magnus Andersson <
magnus.anders...@dexicon.se> wrote:

> Today I used DB Browser for SQLite, version 3.10.1 on a windows 7 machine,
> and encountered what seems to be a bug, either in the SQLite browser or
> SQLite as such.
>
>
>
> If this is not the correct mailing list to post this in I apologize, just
> let me know.
>
>
>
> I had a table with foreign keys, and added "on delete cascade" via the
> function "Modify table". The "create table" script then ended up like this.
>
>
>
> (Strange one that caused wrong cascaded deletes)
>
> CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
> `speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
> KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
> KEY(`speechInLanguageID`), FOREIGN KEY(`speechID`) REFERENCES
> `tblSpeech`(`speechID`) ON DELETE CASCADE )
>
>
>
> I then tested the cascade action, and everything in the table was deleted,
> not just those rows that should have been deleted.
>
>
>
> I then went back to a backup, did the whole thing again, and then
> everything
> worked, but I noticed that the "create table" script was a little
> different.
>
>
>
> (Corrrect one that worked)
>
> CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
> `speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
> KEY(`speechID`) REFERENCES `tblSpeech`(`speechID`) on delete cascade,
> FOREIGN KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
> KEY(`speechInLanguageID`) )
>
>
>
>
>
> As you see (as far as I can see) everything is the same in both, except for
> the order in which they appear (if you paste both scripts on two lines
> after
> each other in something like notepad without word wrap, you can see it more
> clearly).
>
>
>
> Magnus
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug when adding "on delete cascade" via DB Browser for SQLite

[Magnus Andersson]

Today I used DB Browser for SQLite, version 3.10.1 on a windows 7 machine,
and encountered what seems to be a bug, either in the SQLite browser or
SQLite as such.

 

If this is not the correct mailing list to post this in I apologize, just
let me know.

 

I had a table with foreign keys, and added "on delete cascade" via the
function "Modify table". The "create table" script then ended up like this. 

 

(Strange one that caused wrong cascaded deletes)

CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
`speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
KEY(`speechInLanguageID`), FOREIGN KEY(`speechID`) REFERENCES
`tblSpeech`(`speechID`) ON DELETE CASCADE )

 

I then tested the cascade action, and everything in the table was deleted,
not just those rows that should have been deleted. 

 

I then went back to a backup, did the whole thing again, and then everything
worked, but I noticed that the "create table" script was a little different.

 

(Corrrect one that worked)

CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
`speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
KEY(`speechID`) REFERENCES `tblSpeech`(`speechID`) on delete cascade,
FOREIGN KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
KEY(`speechInLanguageID`) )

 

 

As you see (as far as I can see) everything is the same in both, except for
the order in which they appear (if you paste both scripts on two lines after
each other in something like notepad without word wrap, you can see it more
clearly). 

 

Magnus

 

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

[sqlite] Possible bug when compiling with SQLITE_OMIT_TRIGGER option

[M Irya]

Hello,

I'm trying to create a minimal SQLite3 library build and seems like the
foreign keys logic is not properly run on DELETE when building the sources
with -DSQLITE_OMIT_TRIGGER=1.

Here's the patch against amalgamation v3.21.0 sqlite.c:

--- sqlite3.c.orig 2017-12-08 14:06:04.814913000 +0100
+++ sqlite3.c 2017-12-08 14:06:26.125563000 +0100
@@ -105900,6 +105900,7 @@
 #else
 # define pTrigger 0
 # define isView 0
+  bComplex = sqlite3FkRequired(pParse, pTab, 0, 0);
 #endif
 #ifdef SQLITE_OMIT_VIEW
 # undef isView

Without that bComplex in sqlite3DeleteFrom() is at least undefined in the
if () condition 90 lines below.

Let me know if this looks correct/can be pushed to the official SQLite repo.

Regards,
KM
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Wout Mertens]

I feel safer now :)

On Fri, Oct 13, 2017, 12:57 PM Rowan Worth  wrote:

> In that case you would be well advised to use a monotonic clock source,
> rather than a "date-generating" clock. In linux this is the difference
> between providing CLOCK_MONOTONIC or CLOCK_REALTIME as the first argument
> to clock_gettime().
>
> But any API you might use to set a trigger for 2 seconds into the future is
> probably already based on a monotonic clock. Polling a realtime clock would
> make for a pretty convoluted implementation!
>
> -Rowan
>
> On 13 October 2017 at 18:42, Wout Mertens  wrote:
>
> > Thank you, very interesting!
> >
> > The leap second behavior is slightly worrying, basically anything
> > time-based (animations etc) will take a second longer? What if you want
> an
> > engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
> > then it's burning 50% longer?
> >
> > On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert 
> > wrote:
> >
> > > This has not much to do with the original question, but as physicist I
> > > cannot resist:
> > >
> > > National institutes (NIST in the US, NPL in the UK, PTB in Germany, to
> > name
> > > just a few) provide reference times in UTC, which are distributed
> > nowadays
> > > also via the internet, e.g. the NTP protocol. Therefore clocks of
> > > computers, smart phone etc. are, if at all, synchronized more or less
> > > successfully to UTC, and the timestamps that a software like Sqlite
> > handles
> > > are in the vast majority UTC, possibly plus a timezone offset. For
> > example,
> > > the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> > > 2017.*
> > >
> > > What can go wrong?:
> > >
> > > 1) The local clock is synchronized now and then, in some cases as a
> > > consequence of the well-know leap seconds, by setting it abruptly to a
> > new
> > > time. Obviously this does not guarantee that the timestamps become
> > ordered
> > > the same as the events really happened. In the worst case a timestamp
> has
> > > the wrong day or even the wrong year (with potentially legal
> > consequences,
> > > e.g. for financial transactions).
> > >
> > > When is it right?:
> > >
> > > 2) Using the information from NTP, only the clock speed is adjusted to
> > > compensate for drifts. Leap seconds are announced in advance via NTP.
> But
> > > none of the major operating systems, Windows, Linux, Unix can
> internally
> > > represent times within leap seconds. Therefore the system clock is
> halted
> > > for the leap second. Calls for the system time within a leap second
> > return
> > > time stamps just before the leap second, having a small difference
> > between
> > > them such that their order is correct.
> > >
> > > Sqlite and applications are here at the mercy of the underlying system,
> > no
> > > matter how the time at the Sqlite level is presented, as floating point
> > > Julian day numbers, (milli- or micro) second counters from a certain
> > point
> > > in time (epoch) or so. Normally leap seconds don't need to be
> > > representable, as Sqlite/applications are not going to get exposed to
> > such
> > > time stamps (all the OSs cannot). But any timestamps are almost
> certainly
> > > (supposed to be) UTC, plus timezone offset.
> > >
> > > Finally UT1:
> > >
> > > 1) Unless you need to do stuff like tracking satellites, planets, stars
> > and
> > > other celestial objects with high precision from the Earth, you don't
> > need
> > > to know what it is.
> > >
> > > 2) UT1 is published by the https://www.iers.org/ as a daily correction
> > in
> > > SI seconds to UTC, distinguishing between predicted and final
> correction.
> > > As such UT1 does not have seconds, days etc. You can of course define a
> > UT1
> > > day as between the times when the corrected UTC passes midnight, and
> then
> > > divide this "day" into 86400 "seconds". From the physical viewpoint
> this
> > > would be a bit weird because these seconds then have a different length
> > > than the standard SI second, and their length also varies from day to
> > day.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Rowan Worth]

In that case you would be well advised to use a monotonic clock source,
rather than a "date-generating" clock. In linux this is the difference
between providing CLOCK_MONOTONIC or CLOCK_REALTIME as the first argument
to clock_gettime().

But any API you might use to set a trigger for 2 seconds into the future is
probably already based on a monotonic clock. Polling a realtime clock would
make for a pretty convoluted implementation!

-Rowan

On 13 October 2017 at 18:42, Wout Mertens  wrote:

> Thank you, very interesting!
>
> The leap second behavior is slightly worrying, basically anything
> time-based (animations etc) will take a second longer? What if you want an
> engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
> then it's burning 50% longer?
>
> On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert 
> wrote:
>
> > This has not much to do with the original question, but as physicist I
> > cannot resist:
> >
> > National institutes (NIST in the US, NPL in the UK, PTB in Germany, to
> name
> > just a few) provide reference times in UTC, which are distributed
> nowadays
> > also via the internet, e.g. the NTP protocol. Therefore clocks of
> > computers, smart phone etc. are, if at all, synchronized more or less
> > successfully to UTC, and the timestamps that a software like Sqlite
> handles
> > are in the vast majority UTC, possibly plus a timezone offset. For
> example,
> > the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> > 2017.*
> >
> > What can go wrong?:
> >
> > 1) The local clock is synchronized now and then, in some cases as a
> > consequence of the well-know leap seconds, by setting it abruptly to a
> new
> > time. Obviously this does not guarantee that the timestamps become
> ordered
> > the same as the events really happened. In the worst case a timestamp has
> > the wrong day or even the wrong year (with potentially legal
> consequences,
> > e.g. for financial transactions).
> >
> > When is it right?:
> >
> > 2) Using the information from NTP, only the clock speed is adjusted to
> > compensate for drifts. Leap seconds are announced in advance via NTP. But
> > none of the major operating systems, Windows, Linux, Unix can internally
> > represent times within leap seconds. Therefore the system clock is halted
> > for the leap second. Calls for the system time within a leap second
> return
> > time stamps just before the leap second, having a small difference
> between
> > them such that their order is correct.
> >
> > Sqlite and applications are here at the mercy of the underlying system,
> no
> > matter how the time at the Sqlite level is presented, as floating point
> > Julian day numbers, (milli- or micro) second counters from a certain
> point
> > in time (epoch) or so. Normally leap seconds don't need to be
> > representable, as Sqlite/applications are not going to get exposed to
> such
> > time stamps (all the OSs cannot). But any timestamps are almost certainly
> > (supposed to be) UTC, plus timezone offset.
> >
> > Finally UT1:
> >
> > 1) Unless you need to do stuff like tracking satellites, planets, stars
> and
> > other celestial objects with high precision from the Earth, you don't
> need
> > to know what it is.
> >
> > 2) UT1 is published by the https://www.iers.org/ as a daily correction
> in
> > SI seconds to UTC, distinguishing between predicted and final correction.
> > As such UT1 does not have seconds, days etc. You can of course define a
> UT1
> > day as between the times when the corrected UTC passes midnight, and then
> > divide this "day" into 86400 "seconds". From the physical viewpoint this
> > would be a bit weird because these seconds then have a different length
> > than the standard SI second, and their length also varies from day to
> day.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[R Smith]

On 2017/10/13 12:42 PM, Wout Mertens wrote:

Thank you, very interesting!

The leap second behavior is slightly worrying, basically anything
time-based (animations etc) will take a second longer? What if you want an
engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
then it's burning 50% longer?


I sincerely hope you are making a bot for Kerbal space program and not 
actually working for NASA :)


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

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Wout Mertens]

Thank you, very interesting!

The leap second behavior is slightly worrying, basically anything
time-based (animations etc) will take a second longer? What if you want an
engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
then it's burning 50% longer?

On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert  wrote:

> This has not much to do with the original question, but as physicist I
> cannot resist:
>
> National institutes (NIST in the US, NPL in the UK, PTB in Germany, to name
> just a few) provide reference times in UTC, which are distributed nowadays
> also via the internet, e.g. the NTP protocol. Therefore clocks of
> computers, smart phone etc. are, if at all, synchronized more or less
> successfully to UTC, and the timestamps that a software like Sqlite handles
> are in the vast majority UTC, possibly plus a timezone offset. For example,
> the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> 2017.*
>
> What can go wrong?:
>
> 1) The local clock is synchronized now and then, in some cases as a
> consequence of the well-know leap seconds, by setting it abruptly to a new
> time. Obviously this does not guarantee that the timestamps become ordered
> the same as the events really happened. In the worst case a timestamp has
> the wrong day or even the wrong year (with potentially legal consequences,
> e.g. for financial transactions).
>
> When is it right?:
>
> 2) Using the information from NTP, only the clock speed is adjusted to
> compensate for drifts. Leap seconds are announced in advance via NTP. But
> none of the major operating systems, Windows, Linux, Unix can internally
> represent times within leap seconds. Therefore the system clock is halted
> for the leap second. Calls for the system time within a leap second return
> time stamps just before the leap second, having a small difference between
> them such that their order is correct.
>
> Sqlite and applications are here at the mercy of the underlying system, no
> matter how the time at the Sqlite level is presented, as floating point
> Julian day numbers, (milli- or micro) second counters from a certain point
> in time (epoch) or so. Normally leap seconds don't need to be
> representable, as Sqlite/applications are not going to get exposed to such
> time stamps (all the OSs cannot). But any timestamps are almost certainly
> (supposed to be) UTC, plus timezone offset.
>
> Finally UT1:
>
> 1) Unless you need to do stuff like tracking satellites, planets, stars and
> other celestial objects with high precision from the Earth, you don't need
> to know what it is.
>
> 2) UT1 is published by the https://www.iers.org/ as a daily correction in
> SI seconds to UTC, distinguishing between predicted and final correction.
> As such UT1 does not have seconds, days etc. You can of course define a UT1
> day as between the times when the corrected UTC passes midnight, and then
> divide this "day" into 86400 "seconds". From the physical viewpoint this
> would be a bit weird because these seconds then have a different length
> than the standard SI second, and their length also varies from day to day.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug with strftime('%s') < strftime('%s')

[Stephan Buchert]

This has not much to do with the original question, but as physicist I
cannot resist:

National institutes (NIST in the US, NPL in the UK, PTB in Germany, to name
just a few) provide reference times in UTC, which are distributed nowadays
also via the internet, e.g. the NTP protocol. Therefore clocks of
computers, smart phone etc. are, if at all, synchronized more or less
successfully to UTC, and the timestamps that a software like Sqlite handles
are in the vast majority UTC, possibly plus a timezone offset. For example,
the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
2017.*

What can go wrong?:

1) The local clock is synchronized now and then, in some cases as a
consequence of the well-know leap seconds, by setting it abruptly to a new
time. Obviously this does not guarantee that the timestamps become ordered
the same as the events really happened. In the worst case a timestamp has
the wrong day or even the wrong year (with potentially legal consequences,
e.g. for financial transactions).

When is it right?:

2) Using the information from NTP, only the clock speed is adjusted to
compensate for drifts. Leap seconds are announced in advance via NTP. But
none of the major operating systems, Windows, Linux, Unix can internally
represent times within leap seconds. Therefore the system clock is halted
for the leap second. Calls for the system time within a leap second return
time stamps just before the leap second, having a small difference between
them such that their order is correct.

Sqlite and applications are here at the mercy of the underlying system, no
matter how the time at the Sqlite level is presented, as floating point
Julian day numbers, (milli- or micro) second counters from a certain point
in time (epoch) or so. Normally leap seconds don't need to be
representable, as Sqlite/applications are not going to get exposed to such
time stamps (all the OSs cannot). But any timestamps are almost certainly
(supposed to be) UTC, plus timezone offset.

Finally UT1:

1) Unless you need to do stuff like tracking satellites, planets, stars and
other celestial objects with high precision from the Earth, you don't need
to know what it is.

2) UT1 is published by the https://www.iers.org/ as a daily correction in
SI seconds to UTC, distinguishing between predicted and final correction.
As such UT1 does not have seconds, days etc. You can of course define a UT1
day as between the times when the corrected UTC passes midnight, and then
divide this "day" into 86400 "seconds". From the physical viewpoint this
would be a bit weird because these seconds then have a different length
than the standard SI second, and their length also varies from day to day.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Keith Medcalf]

>Better yet, either one of the datetime() or julianday() functions
>(with the same one used consistently in all places) will work best
>for comparison since the output for either one sorts correctly
>against itself. strftime() should be saved for display formatting.

Only for a timestring with a constant offset from UT1.  The default is a fixed 
offset of 00:00, (Zulu, GMT, or UT1, whatever you want to call it).  Some 
people erroneously call this UTC but it is not.  UTC has a variable number of 
seconds in a day.  Zulu/GMT/UT1 have 86400 seconds in a day, never more and 
never less.  

If you store "localtime" or an "instant time" with an offset from GMT, then it 
cannot be sorted (unless all the offsets are the same, that is).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Don V Nielsen]

> Of course now I think about it, strftime always being a string means that
> you either should avoid the '%s' conversion or cast it to a number on
both
> sides, so you don't get the '100' < '20' situation.

>> sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s',
'1970-01-01 00:00:20');
>> 1

Ding Ding Ding. A winner. That was my fear is somehow numerics treated as
text was going to upset an apple cart somewhere.

Thanks for the followups and illustrations!
dvn

On Wed, Oct 11, 2017 at 1:11 PM, David Raymond <david.raym...@tomtom.com>
wrote:

> All correct, yes.
>
> Of course now I think about it, strftime always being a string means that
> you either should avoid the '%s' conversion or cast it to a number on both
> sides, so you don't get the '100' < '20' situation.
>
> Better yet, either one of the datetime() or julianday() functions (with
> the same one used consistently in all places) will work best for comparison
> since the output for either one sorts correctly against itself. strftime()
> should be saved for display formatting.
>
>
> sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s',
> '1970-01-01 00:00:20');
> 1
>
> sqlite> select datetime('1970-01-01 00:01:40') < datetime('1970-01-01
> 00:00:20');
> 0
>
> sqlite> select julianday('1970-01-01 00:01:40') < julianday('1970-01-01
> 00:00:20');
> 0
>
> sqlite> select datetime('now', '+300 seconds') < datetime('now');
> 0
>
> sqlite> select datetime('now', '-300 seconds') < datetime('now');
> 1
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Don V Nielsen
> Sent: Wednesday, October 11, 2017 1:15 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>
> So strftime always returns TEXT. Correct? It was the application of +300 to
> that result that changed the type to INTEGER. And had "+300 seconds" been
> applied as a modifier in the strftime function, then the addition would
> have occurred before producing the result, with the result being type TEXT.
> Correct?
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[David Raymond]

All correct, yes.

Of course now I think about it, strftime always being a string means that you 
either should avoid the '%s' conversion or cast it to a number on both sides, 
so you don't get the '100' < '20' situation.

Better yet, either one of the datetime() or julianday() functions (with the 
same one used consistently in all places) will work best for comparison since 
the output for either one sorts correctly against itself. strftime() should be 
saved for display formatting.


sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s', 
'1970-01-01 00:00:20');
1

sqlite> select datetime('1970-01-01 00:01:40') < datetime('1970-01-01 
00:00:20');
0

sqlite> select julianday('1970-01-01 00:01:40') < julianday('1970-01-01 
00:00:20');
0

sqlite> select datetime('now', '+300 seconds') < datetime('now');
0

sqlite> select datetime('now', '-300 seconds') < datetime('now');
1

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Don V Nielsen
Sent: Wednesday, October 11, 2017 1:15 PM
To: SQLite mailing list
Subject: Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

So strftime always returns TEXT. Correct? It was the application of +300 to
that result that changed the type to INTEGER. And had "+300 seconds" been
applied as a modifier in the strftime function, then the addition would
have occurred before producing the result, with the result being type TEXT.
Correct?

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

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Tim Streater]

On 11 Oct 2017, at 18:53, R Smith  wrote:

> Yes. When you concatenate/add a string and integer together some SQL 
> engines will try to give a sensible result, so that '5' + 3 will yield 8 
> because 3 is INT and it reckons that '5' probably meant 5 since it is 
> added to another INT and the 5 doesn't have an explicit type. But, if 
> you force one of the terms to be string, such as CAST( 5 AS TEXT) or 
> strftime(%s,5) or use the value as a parameter to a function that 
> expects a specific type, then the engine might assume stuff, or try to 
> make sense of it, but in general when you start these shenanigans you 
> are on thin ice over "UNDEFINED" territory, which is what bit the OP 
> since the result in one version of SQlite differed from another version 
> (which the devs might adjust, but it's not a bug since mixing types is 
> not strictly supported).

All my times and dates are stored as seconds since the epoch (in a double for 
reasons that escape me at the minute). As a result I've never had a problem 
trying to compare them.

My philosophy is: internal format, seconds. External format (for display 
purposes), convert to the format the user wants.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[R Smith]

On 2017/10/11 7:15 PM, Don V Nielsen wrote:

So strftime always returns TEXT. Correct?


Yes. The "str" in "strftime" means "string" which is text output. You 
can read the name "strftime" as "string-formatted-time value".



It was the application of +300 to
that result that changed the type to INTEGER. And had "+300 seconds" been
applied as a modifier in the strftime function, then the addition would
have occurred before producing the result, with the result being type TEXT.
Correct?


Yes. When you concatenate/add a string and integer together some SQL 
engines will try to give a sensible result, so that '5' + 3 will yield 8 
because 3 is INT and it reckons that '5' probably meant 5 since it is 
added to another INT and the 5 doesn't have an explicit type. But, if 
you force one of the terms to be string, such as CAST( 5 AS TEXT) or 
strftime(%s,5) or use the value as a parameter to a function that 
expects a specific type, then the engine might assume stuff, or try to 
make sense of it, but in general when you start these shenanigans you 
are on thin ice over "UNDEFINED" territory, which is what bit the OP 
since the result in one version of SQlite differed from another version 
(which the devs might adjust, but it's not a bug since mixing types is 
not strictly supported).


You should never mix types when you expect a certain output. use CAST to 
force the type you need, especially before arithmetic. Don't leave 
correct interpretation up to the engine, even if it works mostly.


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

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Don V Nielsen]

So strftime always returns TEXT. Correct? It was the application of +300 to
that result that changed the type to INTEGER. And had "+300 seconds" been
applied as a modifier in the strftime function, then the addition would
have occurred before producing the result, with the result being type TEXT.
Correct?

On Wed, Oct 11, 2017 at 9:56 AM, David Raymond <david.raym...@tomtom.com>
wrote:

> Sorter version of a longer bit I was writing that got complicated:
> Expressions don't have "Affinity" so when both sides of a comparison
> operator are expressions they need to be the same type for it to mean
> anything. One of the ways to do that in this example is to move the +300
> inside the strftime call, so that both sides end up as text...
>
> sqlite> select strftime('%s', '2017-10-11 10:04:43', '+300 seconds') <
> strftime('%s','2017-10-11 10:04:43');
> strftime('%s', '2017-10-11 10:04:43', '+300 seconds') <
> strftime('%s','2017-10-11 10:04:43')
> 0
>
> Other options include using cast, or adding +0 to the other expression to
> turn it into an integer as well.
>
> sqlite> select strftime('%s', '2017-10-11 10:04:43') + 300 <
> strftime('%s','2017-10-11 10:04:43') + 0;
> strftime('%s', '2017-10-11 10:04:43') + 300 < strftime('%s','2017-10-11
> 10:04:43') + 0
> 0
>
>
> -Original Message-
> From: David Raymond
> Sent: Wednesday, October 11, 2017 9:10 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: RE: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>
> See https://sqlite.org/draft/datatype3.html in section 4.1, Sort Order.
> Any integer is less than any text. So you'll want to have both as one of
> the number types to do valid comparison.
>
> 4.1 Sort Order
> The results of a comparison depend on the storage classes of the operands,
> according to the following rules:
>
> A value with storage class NULL is considered less than any other
> value (including another value with storage class NULL).
>
> An INTEGER or REAL value is less than any TEXT or BLOB value. When an
> INTEGER or REAL is compared to another INTEGER or REAL, a numerical
> comparison is performed.
>
> A TEXT value is less than a BLOB value. When two TEXT values are
> compared an appropriate collating sequence is used to determine the result.
>
> When two BLOB values are compared, the result is determined using
> memcmp().
>
> sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43') + 300);
> typeof(strftime('%s', '2017-10-11 10:04:43') + 300)
> integer
>
> sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43'));
> typeof(strftime('%s', '2017-10-11 10:04:43'))
> text
>
>
> David Raymond | GIS Engineer | TomTom | Lebanon, NH, United States
> e-mail: david.raym...@tomtom.com  | office +1 603 306 8498 |
> www.tomtom.com
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Eric Bollengier
> Sent: Wednesday, October 11, 2017 8:55 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>
> Hello,
>
> I have noticed a problem in SQLite 3.20.1 for a simple operation based
> on strftime('%s').
>
> With SQLite 3.20.1 and 3.6.18
>
> sqlite> select (strftime('%s', '2017-10-11 10:04:43') + 300) <
> strftime('%s', '2017-10-11 10:04:43');
>
> 1
>
> If I use the CAST operator on the second member, it works
>
> sqlite> select
> (strftime('%s', '2017-10-11 10:04:43') + 300) <  CAST(strftime('%s',
> '2017-10-11 10:04:43') as decimal);
>
> 0
>
> If I use the following query, it works too:
>
> sqlite> select
> (strftime('%s', '2017-10-11 10:04:43') + 300 -  strftime('%s',
> '2017-10-11 10:04:43')) < 0;
>
> 0
>
> on SQlite 2.8, the operator < with strftime('%s') works.
>
> Any idea if it is the expected behavior?
>
> Thanks,
>
> Best Regards,
> Eric
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[David Raymond]

Sorter version of a longer bit I was writing that got complicated: Expressions 
don't have "Affinity" so when both sides of a comparison operator are 
expressions they need to be the same type for it to mean anything. One of the 
ways to do that in this example is to move the +300 inside the strftime call, 
so that both sides end up as text...

sqlite> select strftime('%s', '2017-10-11 10:04:43', '+300 seconds') < 
strftime('%s','2017-10-11 10:04:43');
strftime('%s', '2017-10-11 10:04:43', '+300 seconds') < 
strftime('%s','2017-10-11 10:04:43')
0

Other options include using cast, or adding +0 to the other expression to turn 
it into an integer as well.

sqlite> select strftime('%s', '2017-10-11 10:04:43') + 300 < 
strftime('%s','2017-10-11 10:04:43') + 0;
strftime('%s', '2017-10-11 10:04:43') + 300 < strftime('%s','2017-10-11 
10:04:43') + 0
0


-Original Message-
From: David Raymond 
Sent: Wednesday, October 11, 2017 9:10 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: RE: [sqlite] Possible bug with strftime('%s') < strftime('%s')

See https://sqlite.org/draft/datatype3.html in section 4.1, Sort Order. Any 
integer is less than any text. So you'll want to have both as one of the number 
types to do valid comparison.

4.1 Sort Order
The results of a comparison depend on the storage classes of the operands, 
according to the following rules:

A value with storage class NULL is considered less than any other value 
(including another value with storage class NULL).

An INTEGER or REAL value is less than any TEXT or BLOB value. When an 
INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison 
is performed.

A TEXT value is less than a BLOB value. When two TEXT values are compared 
an appropriate collating sequence is used to determine the result.

When two BLOB values are compared, the result is determined using memcmp().

sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43') + 300);
typeof(strftime('%s', '2017-10-11 10:04:43') + 300)
integer

sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43'));
typeof(strftime('%s', '2017-10-11 10:04:43'))
text


David Raymond | GIS Engineer | TomTom | Lebanon, NH, United States
e-mail: david.raym...@tomtom.com  | office +1 603 306 8498 | www.tomtom.com

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Eric Bollengier
Sent: Wednesday, October 11, 2017 8:55 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Possible bug with strftime('%s') < strftime('%s')

Hello,

I have noticed a problem in SQLite 3.20.1 for a simple operation based
on strftime('%s').

With SQLite 3.20.1 and 3.6.18

sqlite> select (strftime('%s', '2017-10-11 10:04:43') + 300) < 
strftime('%s', '2017-10-11 10:04:43');

1

If I use the CAST operator on the second member, it works

sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300) <  CAST(strftime('%s',
'2017-10-11 10:04:43') as decimal);

0

If I use the following query, it works too:

sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300 -  strftime('%s',
'2017-10-11 10:04:43')) < 0;

0

on SQlite 2.8, the operator < with strftime('%s') works.

Any idea if it is the expected behavior?

Thanks,

Best Regards,
Eric


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

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[David Raymond]

See https://sqlite.org/draft/datatype3.html in section 4.1, Sort Order. Any 
integer is less than any text. So you'll want to have both as one of the number 
types to do valid comparison.

4.1 Sort Order
The results of a comparison depend on the storage classes of the operands, 
according to the following rules:

A value with storage class NULL is considered less than any other value 
(including another value with storage class NULL).

An INTEGER or REAL value is less than any TEXT or BLOB value. When an 
INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison 
is performed.

A TEXT value is less than a BLOB value. When two TEXT values are compared 
an appropriate collating sequence is used to determine the result.

When two BLOB values are compared, the result is determined using memcmp().

sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43') + 300);
typeof(strftime('%s', '2017-10-11 10:04:43') + 300)
integer

sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43'));
typeof(strftime('%s', '2017-10-11 10:04:43'))
text


David Raymond | GIS Engineer | TomTom | Lebanon, NH, United States
e-mail: david.raym...@tomtom.com  | office +1 603 306 8498 | www.tomtom.com

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Eric Bollengier
Sent: Wednesday, October 11, 2017 8:55 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Possible bug with strftime('%s') < strftime('%s')

Hello,

I have noticed a problem in SQLite 3.20.1 for a simple operation based
on strftime('%s').

With SQLite 3.20.1 and 3.6.18

sqlite> select (strftime('%s', '2017-10-11 10:04:43') + 300) < 
strftime('%s', '2017-10-11 10:04:43');

1

If I use the CAST operator on the second member, it works

sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300) <  CAST(strftime('%s',
'2017-10-11 10:04:43') as decimal);

0

If I use the following query, it works too:

sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300 -  strftime('%s',
'2017-10-11 10:04:43')) < 0;

0

on SQlite 2.8, the operator < with strftime('%s') works.

Any idea if it is the expected behavior?

Thanks,

Best Regards,
Eric


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

[sqlite] Possible bug with strftime('%s') < strftime('%s')

[Eric Bollengier]

Hello,

I have noticed a problem in SQLite 3.20.1 for a simple operation based
on strftime('%s').

With SQLite 3.20.1 and 3.6.18

sqlite> select (strftime('%s', '2017-10-11 10:04:43') + 300) < 
strftime('%s', '2017-10-11 10:04:43');

1

If I use the CAST operator on the second member, it works

sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300) <  CAST(strftime('%s',
'2017-10-11 10:04:43') as decimal);

0

If I use the following query, it works too:

sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300 -  strftime('%s',
'2017-10-11 10:04:43')) < 0;

0

on SQlite 2.8, the operator < with strftime('%s') works.

Any idea if it is the expected behavior?

Thanks,

Best Regards,
Eric


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

[sqlite] Possible bug in cli: .schema --indent mishandles trailing comments

[Trevor]

Here is a sample output to illustrate the problem of mishandled
trailing comments. The original create table statement included
two leading spaces for each attribute.

$ sqlite3 ~/db-lib/data.db

SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
sqlite> .schema rating_answer
CREATE TABLE rating_answer (
  idinteger primary key,
  idQuestionreferences rating_question (id),
  response  integer not null,  -- 1=strongly dislike, 5=strongly like
  answertexttext not null,
  statusinteger, -- 0 not active, 1=active
  datecreated   text not null
);
CREATE UNIQUE INDEX rating_answer_idx on rating_answer (idQuestion, response);

sqlite> .schema --indent rating_answer
CREATE TABLE rating_answer(
  id integer primary key,
  idQuestion references rating_question(id),
  response integer not null,
  -- 1=strongly dislike,
  5=strongly like
  answertext text not null,
  status integer,
  -- 0 not active,
  1=active
  datecreated text not null
);
CREATE UNIQUE INDEX rating_answer_idx on rating_answer(
  idQuestion,
  response
);

sqlite> .q
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Simon Slavin]

On 26 Jan 2017, at 11:33pm, Ersin Akinci  wrote:

> (Please someone correct me if I'm wrong here. I'm a SQLite n00b who
> managed to stumble onto a subtle problem while debugging Rails. =)

Nope, you got it right.  And Michael has too.  I took your original report and 
tried to isolate the part of it I felt was a bug.  Although we were able to 
explain some of what you saw, I thought part of it signalled a genuine problem.

It’s a silly bug because it emerged when doing something nobody could ever want 
to do: make an index on a constant string.  But there’s a chance that the 
underlying fault could affect some command someone might want to do, so it’s 
probably best that someone on the development team investigate, even if just to 
say "We looked at it and it could never affect anything useful.".

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Thank you Simon,

I do see the inconsistency and thanks for those examples. I had answered
previously before I saw your explanation and I now see why there is
concern. It certainly appears to be inconsistent given such use cases.

On 27 January 2017 at 10:26, Michael Falconer 
wrote:

> Ersin,
>
> apologies if I seem to be suffering from terminal thickness, but I still
> don't get it. Why would I expect anything other than column interpretation
> from a single quoted argument. I *want to be told* that my column does
> not exist, I don't want a calculated index so why should I be expecting
> one. On the other hand if I choose double quotes I'm probably doing
> something different. Maybe someone else should weigh in and the penny will
> finally drop if I am missing the point, but I'm still not seeing crawly
> things. :-)
>
>
> On 27 January 2017 at 10:01, Ersin Akinci  wrote:
>
>> Michael,
>>
>> If I understood DRH and Simon correctly, I think the cause for concern
>> is that SQLite should be interpreting the single quotes as a string
>> literal, yet it interprets it as a column. Perhaps it's a strange
>> example (i.e., why would you want to index a string literal?), but
>> still, the behavior deviates from what's expected, the expected
>> behavior being that we should get a calculated index.
>>
>> Best,
>> Ersin
>>
>> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>>  wrote:
>> > Simon,
>> >
>> > as I see it there is no problem here. Explicit quoting regardless, the
>> > column does not exist and an error is returned, isn't this the expected
>> > outcome? In the DRH quoted section a reason is presented as to why no
>> error
>> > is returned due to a built in default action. This may or may not be a
>> > point for further analysis (ie. is this an appropriate default) but I'm
>> not
>> > seeing obvious crawly things. Perhaps it's me missing something Simon
>> but
>> > I'm not overly concerned about the above. Don't use double quotes
>> (always
>> > single) and it would appear things are just fine. You'll get told if
>> your
>> > column is non-existent.
>> >
>> >
>> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
>> >
>> >>
>> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>> >>
>> >> > Bug is as follows:
>> >>
>> >> Anyone ?  Did I miss something and you’re all too polite to point it
>> out ?
>> >>
>> >> Simon.
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Regards,
>> >  Michael.j.Falconer.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Ersin Y. Akinci -- ersinakinci.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Ersin Akinci]

Michael,

I think part of the problem is that the "obviously" (or 'obviously',
ha) useful behavior here would be for single quoted arguments to
result in column interpretation. You're quite right to say that you
want to be told that your column doesn't exist--that is very helpful
information!. But if my understanding is correct, you *should* expect
a calculated index even though you don't *want* one because single
quoted arguments are always supposed to be interpreted as string
literals, not interpolated columns. So the correct behavior is the
unhelpful one, in this case.

tl;dr: You should expect something other than column interpretation
because single quoted arguments are never supposed to be interpolated,
even when that leads to not useful results (like indexing a string
literal).

(Please someone correct me if I'm wrong here. I'm a SQLite n00b who
managed to stumble onto a subtle problem while debugging Rails. =)

-Ersin

On Thu, Jan 26, 2017 at 3:26 PM, Michael Falconer
 wrote:
> Ersin,
>
> apologies if I seem to be suffering from terminal thickness, but I still
> don't get it. Why would I expect anything other than column interpretation
> from a single quoted argument. I *want to be told* that my column does not
> exist, I don't want a calculated index so why should I be expecting one. On
> the other hand if I choose double quotes I'm probably doing something
> different. Maybe someone else should weigh in and the penny will finally
> drop if I am missing the point, but I'm still not seeing crawly things. :-)
>
>
> On 27 January 2017 at 10:01, Ersin Akinci  wrote:
>
>> Michael,
>>
>> If I understood DRH and Simon correctly, I think the cause for concern
>> is that SQLite should be interpreting the single quotes as a string
>> literal, yet it interprets it as a column. Perhaps it's a strange
>> example (i.e., why would you want to index a string literal?), but
>> still, the behavior deviates from what's expected, the expected
>> behavior being that we should get a calculated index.
>>
>> Best,
>> Ersin
>>
>> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>>  wrote:
>> > Simon,
>> >
>> > as I see it there is no problem here. Explicit quoting regardless, the
>> > column does not exist and an error is returned, isn't this the expected
>> > outcome? In the DRH quoted section a reason is presented as to why no
>> error
>> > is returned due to a built in default action. This may or may not be a
>> > point for further analysis (ie. is this an appropriate default) but I'm
>> not
>> > seeing obvious crawly things. Perhaps it's me missing something Simon but
>> > I'm not overly concerned about the above. Don't use double quotes (always
>> > single) and it would appear things are just fine. You'll get told if your
>> > column is non-existent.
>> >
>> >
>> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
>> >
>> >>
>> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>> >>
>> >> > Bug is as follows:
>> >>
>> >> Anyone ?  Did I miss something and you’re all too polite to point it
>> out ?
>> >>
>> >> Simon.
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Regards,
>> >  Michael.j.Falconer.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Ersin Y. Akinci -- ersinakinci.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Ersin Y. Akinci -- ersinakinci.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Ersin,

apologies if I seem to be suffering from terminal thickness, but I still
don't get it. Why would I expect anything other than column interpretation
from a single quoted argument. I *want to be told* that my column does not
exist, I don't want a calculated index so why should I be expecting one. On
the other hand if I choose double quotes I'm probably doing something
different. Maybe someone else should weigh in and the penny will finally
drop if I am missing the point, but I'm still not seeing crawly things. :-)


On 27 January 2017 at 10:01, Ersin Akinci  wrote:

> Michael,
>
> If I understood DRH and Simon correctly, I think the cause for concern
> is that SQLite should be interpreting the single quotes as a string
> literal, yet it interprets it as a column. Perhaps it's a strange
> example (i.e., why would you want to index a string literal?), but
> still, the behavior deviates from what's expected, the expected
> behavior being that we should get a calculated index.
>
> Best,
> Ersin
>
> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>  wrote:
> > Simon,
> >
> > as I see it there is no problem here. Explicit quoting regardless, the
> > column does not exist and an error is returned, isn't this the expected
> > outcome? In the DRH quoted section a reason is presented as to why no
> error
> > is returned due to a built in default action. This may or may not be a
> > point for further analysis (ie. is this an appropriate default) but I'm
> not
> > seeing obvious crawly things. Perhaps it's me missing something Simon but
> > I'm not overly concerned about the above. Don't use double quotes (always
> > single) and it would appear things are just fine. You'll get told if your
> > column is non-existent.
> >
> >
> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
> >
> >>
> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
> >>
> >> > Bug is as follows:
> >>
> >> Anyone ?  Did I miss something and you’re all too polite to point it
> out ?
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Ersin Y. Akinci -- ersinakinci.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Simon Slavin]

On 26 Jan 2017, at 10:56pm, Michael Falconer  
wrote:

> as I see it there is no problem here. Explicit quoting regardless, the
> column does not exist and an error is returned, isn't this the expected
> outcome?

Yet it’s not consistent.  I should have given more examples, so here they are.  
I can do this:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> CREATE TABLE reports (a INT, b TEXT);
sqlite> CREATE INDEX index_reports_addition ON reports (1 + 2);
sqlite> PRAGMA index_xinfo(index_reports_addition);
0|-2||0|BINARY|1
1|-1||0|BINARY|0

which is an index based on a constant numeric expression, so that works.  I can 
do this:

sqlite> CREATE INDEX index_reports_string ON reports ('a' + b + 'c');
sqlite> PRAGMA index_xinfo(index_reports_string);
0|-2||0|BINARY|1
1|-1||0|BINARY|0

but I cannot do this:

sqlite> CREATE INDEX index_reports_3strings ON reports ('a',b,'c');
Error: no such column: c

Why is it objecting to 'c' here and not 'a' ?  If I remove the 'c' I get

sqlite> CREATE INDEX index_reports_2strings ON reports ('a',b);
sqlite> PRAGMA index_xinfo(index_reports_2strings);
0|0|a|0|BINARY|1
1|1|b|0|BINARY|1
2|-1||0|BINARY|0
sqlite> CREATE INDEX index_reports_1string ON reports ('a');
sqlite> PRAGMA index_xinfo(index_reports_1string);
0|0|a|0|BINARY|1
1|-1||0|BINARY|0

so it seems there’s no problem with the 'a'.  So why is it objecting to the 'c' 
?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Ersin Akinci]

Michael,

If I understood DRH and Simon correctly, I think the cause for concern
is that SQLite should be interpreting the single quotes as a string
literal, yet it interprets it as a column. Perhaps it's a strange
example (i.e., why would you want to index a string literal?), but
still, the behavior deviates from what's expected, the expected
behavior being that we should get a calculated index.

Best,
Ersin

On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
 wrote:
> Simon,
>
> as I see it there is no problem here. Explicit quoting regardless, the
> column does not exist and an error is returned, isn't this the expected
> outcome? In the DRH quoted section a reason is presented as to why no error
> is returned due to a built in default action. This may or may not be a
> point for further analysis (ie. is this an appropriate default) but I'm not
> seeing obvious crawly things. Perhaps it's me missing something Simon but
> I'm not overly concerned about the above. Don't use double quotes (always
> single) and it would appear things are just fine. You'll get told if your
> column is non-existent.
>
>
> On 27 January 2017 at 00:12, Simon Slavin  wrote:
>
>>
>> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>>
>> > Bug is as follows:
>>
>> Anyone ?  Did I miss something and you’re all too polite to point it out ?
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Ersin Y. Akinci -- ersinakinci.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Simon,

as I see it there is no problem here. Explicit quoting regardless, the
column does not exist and an error is returned, isn't this the expected
outcome? In the DRH quoted section a reason is presented as to why no error
is returned due to a built in default action. This may or may not be a
point for further analysis (ie. is this an appropriate default) but I'm not
seeing obvious crawly things. Perhaps it's me missing something Simon but
I'm not overly concerned about the above. Don't use double quotes (always
single) and it would appear things are just fine. You'll get told if your
column is non-existent.


On 27 January 2017 at 00:12, Simon Slavin  wrote:

>
> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>
> > Bug is as follows:
>
> Anyone ?  Did I miss something and you’re all too polite to point it out ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Simon Slavin]

On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:

> Bug is as follows:

Anyone ?  Did I miss something and you’re all too polite to point it out ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug when creating a calculated index

[Simon Slavin]

Bug is as follows:

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.

sqlite> CREATE TABLE reports (a INT, b TEXT);

sqlite> CREATE INDEX index_reports_quotes ON reports ("yearz_doesnt_exist");

sqlite> PRAGMA index_xinfo('index_reports_quotes');
0|-2||0|BINARY|1
1|-1||0|BINARY|0

DRH says that, having failed to find a column called 'yearz_doesnt_exist' 
SQLite is interpreting that name as a fixed text string and creating a 
calculated index.  This one isn’t useful but it’s legal.  So far so good.  Now 
continue.

sqlite> CREATE INDEX index_reports_apostrophes ON reports
   ...> ('yearz_doesnt_exist');
Error: no such column: yearz_doesnt_exist

Here I am intentionally trying to create the same calculated index.  I have 
explicitly quoted the string 'yearz_doesnt_exist', which means I am explicitly 
supplying an expression.  Why doesn’t SQLite understand this ?

If this is a bug, credit for spotting it belongs to Ersin Akinci.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Hick Gunter]

Incorrect. All data is stored as NULL, integer, float, text or blob. The 
"declared type" is taken as a hint from the designer in respect to the kind of 
values he intends to store there. The result of the hint is called an 
"affinity", i.e. the kind of data the field "likes to" store.  If the type of 
the value presented differs from the affinity, it may be converted, if 
losslessly and reversibly possible (e.g. '1' <=> 1); this is called "applying 
affinity". Or, if not possible ('hugo' cannot be converted to a number), simply 
stored without conversion. This is the sense in which SQLite is "typeless". 
Even if the field has a declared type, any kind of value may be stored there 
without error.

"Affinity" is a volatile property. It may be lost (by using a value in a 
general expression) or gained (by using a cast or in the context of a 
comparison). This is mainly for the benefit of users who for some reason or 
other need to compare (text, e.g. a text constant) '1' with (integer, e.g. a 
value from a field) 1 and have it come out equal. Note that constants have a 
type but no affinity, so SELECT '1' = 1; returns 0.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Dienstag, 27. Dezember 2016 16:18
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] Possible bug with union and join.

Theory related question. I'm being argumentative, I know. But this issue is in 
the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore blob). 
Correct? It is when one casts a column to something other than text that 
triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024, etc..., 
are they "truly" integers? If the value is not used in a mathematical formula, 
why think of it as an integer? It is still just text...a string of ascii 
digits... but still text. Is there something behind the scenes of how text data 
comprised of numeric digits is stored?

Like the previous issue I suggested keeping the keys between tables the same 
data type. The issue resolves itself. The same would be true, here. One table 
has text which could be '1,10'. But in the other table, it is integer 1 & 10. 
It could be text '1' & '10'. No type conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski 
<adrianstachlew...@gmail.com> wrote:
> Fortunately names of columns are much more transparent and documented
> in our internal specification. 'Id' was created only for example, but
> thanks for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin <slav...@bigfraud.org>:
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski
>> <adrianstachlew...@gmail.com>
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example
>> > ("4", "4,5", "10-1") (to be precise this map is created on the fly
>> > by concatenating some ids and names from another tables).  In
>> > second table there are stored identifiers which are integer only.
>> > This ids means something entirely different, but there is one case,
>> > when table with date keeps ids from both tables. Unfortunately I
>> > cannot change input data - it is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import
>> from the CSV files, I might recommend that you do not call the TEXT field 
>> 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might
>> confuse other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___

Re: [sqlite] Possible bug with union and join.

[Don V Nielsen]

Thanks for the correction/information! Merry Christmas and Happy New
Year to you.

On Tue, Dec 27, 2016 at 9:48 AM, Richard Hipp  wrote:
> On 12/27/16, Don V Nielsen  wrote:
>> Theory related question. I'm being argumentative, I know. But this
>> issue is in the same category as one discussed weeks ago.
>>
>> SQLite is, in a sense, typeless. All data is stored as text (ignore
>> blob). Correct? It is when one casts a column to something other than
>> text that triggers SQLite to treat the text differently.
>
> Incorrect.  SQLite stores content in memory and on disk in multiple
> formats, including 2's-complement integers, IEEE 754 floating point
> numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
> blobs.  See, for example,
> https://www.sqlite.org/fileformat2.html#serialtype
>
>>
>> Disregarding auto-incremented key values, why have an integer key.
>
> Special optimizations apply to tables with an INTEGER PRIMARY KEY that
> make such tables particularly fast.
> --
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Richard Hipp]

On 12/27/16, Don V Nielsen  wrote:
> Theory related question. I'm being argumentative, I know. But this
> issue is in the same category as one discussed weeks ago.
>
> SQLite is, in a sense, typeless. All data is stored as text (ignore
> blob). Correct? It is when one casts a column to something other than
> text that triggers SQLite to treat the text differently.

Incorrect.  SQLite stores content in memory and on disk in multiple
formats, including 2's-complement integers, IEEE 754 floating point
numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
blobs.  See, for example,
https://www.sqlite.org/fileformat2.html#serialtype

>
> Disregarding auto-incremented key values, why have an integer key.

Special optimizations apply to tables with an INTEGER PRIMARY KEY that
make such tables particularly fast.
-- 
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] Possible bug with union and join.

[Don V Nielsen]

Theory related question. I'm being argumentative, I know. But this
issue is in the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore
blob). Correct? It is when one casts a column to something other than
text that triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024,
etc..., are they "truly" integers? If the value is not used in a
mathematical formula, why think of it as an integer? It is still just
text...a string of ascii digits... but still text. Is there something
behind the scenes of how text data comprised of numeric digits is
stored?

Like the previous issue I suggested keeping the keys between tables
the same data type. The issue resolves itself. The same would be true,
here. One table has text which could be '1,10'. But in the other
table, it is integer 1 & 10. It could be text '1' & '10'. No type
conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski
 wrote:
> Fortunately names of columns are much more transparent and documented in
> our internal specification. 'Id' was created only for example, but thanks
> for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin :
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski 
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example ("4",
>> > "4,5", "10-1") (to be precise this map is created on the fly by
>> > concatenating some ids and names from another tables).  In second table
>> > there are stored identifiers which are integer only. This ids means
>> > something entirely different, but there is one case, when table with date
>> > keeps ids from both tables. Unfortunately I cannot change input data - it
>> > is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import from
>> the CSV files, I might recommend that you do not call the TEXT field 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse
>> other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Fortunately names of columns are much more transparent and documented in
our internal specification. 'Id' was created only for example, but thanks
for advice :)

Adrian

2016-12-25 13:44 GMT+01:00 Simon Slavin :

>
> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski 
> wrote:
>
> > Id field in one table is defined as TEXT, because there are stored
> > identifiers which can be numeric or text mostly like in the example ("4",
> > "4,5", "10-1") (to be precise this map is created on the fly by
> > concatenating some ids and names from another tables).  In second table
> > there are stored identifiers which are integer only. This ids means
> > something entirely different, but there is one case, when table with date
> > keeps ids from both tables. Unfortunately I cannot change input data - it
> > is taken from some APIs using csv files.
>
> Okay.  You’re wedded to a data format created by someone else.  That
> explains the problem.
>
> If you have the opportunity to rename your columns when you import from
> the CSV files, I might recommend that you do not call the TEXT field 'id'.
> The convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse
> other people who see your database.
>
> Good luck with problem you posted about.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Simon Slavin]

On 23 Dec 2016, at 4:55pm, Adrian Stachlewski  
wrote:

> Id field in one table is defined as TEXT, because there are stored
> identifiers which can be numeric or text mostly like in the example ("4",
> "4,5", "10-1") (to be precise this map is created on the fly by
> concatenating some ids and names from another tables).  In second table
> there are stored identifiers which are integer only. This ids means
> something entirely different, but there is one case, when table with date
> keeps ids from both tables. Unfortunately I cannot change input data - it
> is taken from some APIs using csv files.

Okay.  You’re wedded to a data format created by someone else.  That explains 
the problem.

If you have the opportunity to rename your columns when you import from the CSV 
files, I might recommend that you do not call the TEXT field 'id'.  The 
convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse other 
people who see your database.

Good luck with problem you posted about.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Simon,
Id field in one table is defined as TEXT, because there are stored
identifiers which can be numeric or text mostly like in the example ("4",
"4,5", "10-1") (to be precise this map is created on the fly by
concatenating some ids and names from another tables).  In second table
there are stored identifiers which are integer only. This ids means
something entirely different, but there is one case, when table with date
keeps ids from both tables. Unfortunately I cannot change input data - it
is taken from some APIs using csv files. Real schema of this database is
mostly determined by input data and hole database is treated like some kind
of cache.

As you wrote, there are many problems with data, that we're storing. Schema
of database is not stable, new features often need new data from other API
and it's hard to predict new types of data. As I wrote in my previous
message, I was quite sure, that comparing text columns and integer columns
should work. Hopefully in other cases fields which are compared are same
types.

Adrian

2016-12-23 13:00 GMT+01:00 Simon Slavin :

>
> On 22 Dec 2016, at 5:55pm, Adrian Stachlewski 
> wrote:
>
> > In this case I think that the best way to do this is cast integer column
> to
> > text.
> > CREATE VIEW id_map(id, name) as
> >  SELECT CAST(id AS TEXT), name
> >  FROM map_integer
> > UNION ALL
> >  SELECT id, name
> >  FROM map_text;
>
> In an earlier post
>
> > CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
> > CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
>
> Your problem is not the form of your enquiry, or the precise way that
> UNION works, it’s the data you’re storing.
>
> Sorry if you’ve already explained this, but can you tell me why you have
> an id field which holds numbers defined as TEXT in one table, but an id
> field which holds numbers defined as INTEGER in another table ?  I know
> that the above schema is an example you made up for discussing the problem,
> and not your real schema, but it still points to an underlying problem with
> the data you’re storing.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Simon Slavin]

On 22 Dec 2016, at 5:55pm, Adrian Stachlewski  
wrote:

> In this case I think that the best way to do this is cast integer column to
> text.
> CREATE VIEW id_map(id, name) as
>  SELECT CAST(id AS TEXT), name
>  FROM map_integer
> UNION ALL
>  SELECT id, name
>  FROM map_text;

In an earlier post

> CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
> CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);

Your problem is not the form of your enquiry, or the precise way that UNION 
works, it’s the data you’re storing.

Sorry if you’ve already explained this, but can you tell me why you have an id 
field which holds numbers defined as TEXT in one table, but an id field which 
holds numbers defined as INTEGER in another table ?  I know that the above 
schema is an example you made up for discussing the problem, and not your real 
schema, but it still points to an underlying problem with the data you’re 
storing.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Richard,

Thank you for your answer. Datatypes in sqlite was always weird for me,
mostly the fact that for example TEXT can be inserted in INTEGER column.

In this case I think that the best way to do this is cast integer column to
text.
CREATE VIEW id_map(id, name) as
  SELECT CAST(id AS TEXT), name
  FROM map_integer
 UNION ALL
  SELECT id, name
  FROM map_text;

Is there any other recipe to do this?
Is there any documentation when '4'==4 (just to satisfy my curiosity)? I've
read https://www.sqlite.org/datatype3.html, but from this doc I was quite
sure that comparing text and integer columns is safe.

--
Adrian Stachlewski

2016-12-22 16:06 GMT+01:00 Richard Hipp :

> Adrian:
>
> String are not equal to numbers.  You should not expect that '4'==4.
> Yes, I know that SQLite will sometimes do this.  The complex type
> coercion rules were added so that SQL scripts that worked on
> PostgreSQL would also work on SQLite.  But really, you should avoid
> depending on automatic type coercion.  If you are having to think
> about the type coercion rules, then you are doing it wrong.
>
> Yes - SQLite should give consistent and predictable answers.  We will
> fix that.  Eventually.  Once I figure out what it ought to be doing.
> But you should not wait on that fix.  You should go ahead redesign
> your application so that it does not depend on the nuances of type
> coercions and so that it does not compare strings against integers and
> expect them to be equal.
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Richard Hipp]

On 12/22/16, David Raymond  wrote:
> Problem appears to be coming from an automatic index.


Thanks for the insight, David.  Automatic indexes do appear to be a
factor, but not the only factor.   The script below shows different
answers depending on whether the VIEW is created with or without
explicit column names:

- begin script ---
CREATE TABLE map_integer (id INT, name);
INSERT INTO map_integer VALUES(1,'a');
CREATE TABLE map_text (id TEXT, name);
INSERT INTO map_text VALUES('4','e');
CREATE TABLE data (id TEXT, name);
INSERT INTO data VALUES(1,'abc');
INSERT INTO data VALUES('4','xyz');

CREATE VIEW id_map1 as
SELECT * FROM map_integer
UNION ALL
SELECT * FROM map_text;

CREATE VIEW id_map2(id,name) as
SELECT * FROM map_integer
UNION ALL
SELECT * FROM map_text;

PRAGMA automatic_index=off;
.print -- id_map1:
SELECT * FROM data LEFT JOIN id_map1 USING(id);
.print -- id_map2:
SELECT * FROM data LEFT JOIN id_map2 USING(id);
 end script --

Adrian:

String are not equal to numbers.  You should not expect that '4'==4.
Yes, I know that SQLite will sometimes do this.  The complex type
coercion rules were added so that SQL scripts that worked on
PostgreSQL would also work on SQLite.  But really, you should avoid
depending on automatic type coercion.  If you are having to think
about the type coercion rules, then you are doing it wrong.

Yes - SQLite should give consistent and predictable answers.  We will
fix that.  Eventually.  Once I figure out what it ought to be doing.
But you should not wait on that fix.  You should go ahead redesign
your application so that it does not depend on the nuances of type
coercions and so that it does not compare strings against integers and
expect them to be equal.
-- 
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] Possible bug with union and join.

[David Raymond]

Problem appears to be coming from an automatic index.

sqlite> select * from data left join id_map using (id);
--EQP-- 2,0,0,SCAN TABLE map_integer
--EQP-- 3,0,0,SCAN TABLE map_text
--EQP-- 1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
--EQP-- 0,0,0,SCAN TABLE data
--EQP-- 0,1,1,SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (id=?)
id|flag|name
1|0|a
2|1|b
3|1|c
4|0|NULL
3,12|1|d

sqlite> pragma automatic_index = off;

sqlite> select * from data left join id_map using (id);
--EQP-- 2,0,0,SCAN TABLE map_integer
--EQP-- 3,0,0,SCAN TABLE map_text
--EQP-- 1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
--EQP-- 0,0,0,SCAN TABLE data
--EQP-- 0,1,1,SCAN SUBQUERY 1
id|flag|name
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Adrian Stachlewski
Sent: Wednesday, December 21, 2016 8:14 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Possible bug with union and join.

Hi everyone.

When I was working with sqlite3 I've found weird behavior of JOIN clause
when I was trying to merge table with union of tables. Let me explain this
using database with reproduced problem.

Database dump:
CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "map_integer" VALUES(1,'a');
INSERT INTO "map_integer" VALUES(2,'b');
INSERT INTO "map_integer" VALUES(3,'c');
CREATE TABLE map_text (id TEXT PRIMARY KEY, name TEXT);
INSERT INTO "map_text" VALUES('4','e');
INSERT INTO "map_text" VALUES('3,12','d');
CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
INSERT INTO "data" VALUES('1',0);
INSERT INTO "data" VALUES('2',1);
INSERT INTO "data" VALUES('3',1);
INSERT INTO "data" VALUES('4',0);
INSERT INTO "data" VALUES('3,12',1);

View for map:
CREATE VIEW id_map as
SELECT *
  FROM map_integer
UNION ALL
SELECT *
  FROM map_text;

Select statements:
SELECT * FROM data LEFT JOIN id_map USING(id);
SELECT * FROM data LEFT JOIN id_map ON data.id = id_map.id;

Both of select statements give wrong output:
1|0|a
2|1|b
3|1|c
4|0|
3,12|1|d

Expected result:
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d

Another tests I've made:
1. view to CTE - same problem
2. left join to join - same problem, there's no row with id=4
3. change the order of select statements in view - not working, output:
1|0|
2|1|
3|1|
4|0|e
3,12|1|d

My explanation of the problem and workaround:
There is problem when UNION is made on different types in one column. When
I've cast id to text in view or when joining (... ON data.id = CAST(
id_map.id AS TEXT) everything it's working fine. Unfortunately this
workaround makes no sense for me, as long as
SELECT CAST(1 AS TEXT) = CAST(1 AS INTEGER);
gives True.

SQLite versions:
- 3.13.0
- 3.15.2
- pre-release snapshot

OS:
Ubuntu 16.04.1 LTS with 4.4.0-57-generic

I hope that I've described all necessary informations. Of course feel free
to ask me some questions if there's some ambiguous part.

Best regards,
Adrian Stachlewski
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Hi everyone.

When I was working with sqlite3 I've found weird behavior of JOIN clause
when I was trying to merge table with union of tables. Let me explain this
using database with reproduced problem.

Database dump:
CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "map_integer" VALUES(1,'a');
INSERT INTO "map_integer" VALUES(2,'b');
INSERT INTO "map_integer" VALUES(3,'c');
CREATE TABLE map_text (id TEXT PRIMARY KEY, name TEXT);
INSERT INTO "map_text" VALUES('4','e');
INSERT INTO "map_text" VALUES('3,12','d');
CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
INSERT INTO "data" VALUES('1',0);
INSERT INTO "data" VALUES('2',1);
INSERT INTO "data" VALUES('3',1);
INSERT INTO "data" VALUES('4',0);
INSERT INTO "data" VALUES('3,12',1);

View for map:
CREATE VIEW id_map as
SELECT *
  FROM map_integer
UNION ALL
SELECT *
  FROM map_text;

Select statements:
SELECT * FROM data LEFT JOIN id_map USING(id);
SELECT * FROM data LEFT JOIN id_map ON data.id = id_map.id;

Both of select statements give wrong output:
1|0|a
2|1|b
3|1|c
4|0|
3,12|1|d

Expected result:
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d

Another tests I've made:
1. view to CTE - same problem
2. left join to join - same problem, there's no row with id=4
3. change the order of select statements in view - not working, output:
1|0|
2|1|
3|1|
4|0|e
3,12|1|d

My explanation of the problem and workaround:
There is problem when UNION is made on different types in one column. When
I've cast id to text in view or when joining (... ON data.id = CAST(
id_map.id AS TEXT) everything it's working fine. Unfortunately this
workaround makes no sense for me, as long as
SELECT CAST(1 AS TEXT) = CAST(1 AS INTEGER);
gives True.

SQLite versions:
- 3.13.0
- 3.15.2
- pre-release snapshot

OS:
Ubuntu 16.04.1 LTS with 4.4.0-57-generic

I hope that I've described all necessary informations. Of course feel free
to ask me some questions if there's some ambiguous part.

Best regards,
Adrian Stachlewski
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug with union and join.

[Adrian Stachlewski]

Hi everyone.

When I was working with sqlite3 I've found weird behavior of JOIN clause
when I was trying to merge table with union of tables. Let me explain this
using database with reproduced problem.

Database dump:
CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "map_integer" VALUES(1,'a');
INSERT INTO "map_integer" VALUES(2,'b');
INSERT INTO "map_integer" VALUES(3,'c');
CREATE TABLE map_text (id TEXT PRIMARY KEY, name TEXT);
INSERT INTO "map_text" VALUES('4','e');
INSERT INTO "map_text" VALUES('3,12','d');
CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
INSERT INTO "data" VALUES('1',0);
INSERT INTO "data" VALUES('2',1);
INSERT INTO "data" VALUES('3',1);
INSERT INTO "data" VALUES('4',0);
INSERT INTO "data" VALUES('3,12',1);

View for map:
CREATE VIEW id_map as
SELECT *
  FROM map_integer
UNION ALL
SELECT *
  FROM map_text;

Select statements:
SELECT * FROM data LEFT JOIN id_map USING(id);
SELECT * FROM data LEFT JOIN id_map ON data.id = id_map.id;

Both of select statements give wrong output:
1|0|a
2|1|b
3|1|c
4|0|
3,12|1|d

Expected result:
1|0|a
2|1|b
3|1|c
4|0|e
3,12|1|d

Another tests I've made:
1. view to CTE - same problem
2. left join to join - same problem, there's no row with id=4
3. change the order of select statements in view - not working, output:
1|0|
2|1|
3|1|
4|0|e
3,12|1|d

My explanation of the problem and workaround:
There is problem when UNION is made on different types in one column. When
I've cast id to text in view or when joining (... ON data.id = CAST(
id_map.id AS TEXT) everything it's working fine. Unfortunately this
workaround makes no sense for me, as long as
SELECT CAST(1 AS TEXT) = CAST(1 AS INTEGER);
gives True.

SQLite versions:
- 3.13.0
- 3.15.2
- pre-release snapshot

OS:
Ubuntu 16.04.1 LTS with 4.4.0-57-generic

I hope that I've described all necessary informations. Of course feel free
to ask me some questions if there's some ambiguous part.

Best regards,
Adrian Stachlewski
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible Bug in VTable handling in SQLite 3.14.2

[Hick Gunter]

Dan,

no, they don't, which is why I included "possible". It is probably not seen 
elsewhere.

We are using SQLite's virtual table feature to implement a query front end for 
several diverse data storage methods in an OLTP application, with the two 
writeable stores being memory sections and CTree files. Both do their own table 
level locking, and SQLite's additional database level locking was creating 
concurrency issues between otherwise unrelated (trans)actions (e.g. creating a 
new virtual table via a housekeeping job would block OLTP). This was resolved 
by suppressing OP_Transaction for virtual tables. OP_Transaction is only 
generated for statements that involve native tables.

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dan Kennedy
Gesendet: Freitag, 14. Oktober 2016 20:33
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] Possible Bug in VTable handling in SQLite 3.14.2

On 10/14/2016 04:59 PM, Hick Gunter wrote:
> In the vdbeaux.c source, the function
>
> resolveP2Values(...)
>
> is not resetting p->readOnly when it encounters an OP_VUpdate opcode
>
> is not setting p->bIsReader when it encounters an OP_VFilter opcode
>
>
> Additionally, the frunction
>
> sqlite3VdbeHalt(...)
>
> is only checking p->bIsReader and omitting to check p->readOnly
>
> even though the comment claims to check "if the program never started or if 
> the SQL Statement does not read or write a database file".
>
> These omissions conspire to make SQLite refrain from calling the xSync and 
> xCommit entrypoints of the virtual table named in an UPDATE, DELETE or INSERT 
> statement.

Do you have a test case for this?

Trying to reproduce here, all VM programs for UPDATE/DELETE/INSERT also contain 
OP_Transaction opcodes. Which cause Vdbe.bIsReader to be set and so the 
xSync/xCommit methods are called. Do the VM programs (EXPLAIN
output) for whatever statements you're testing with contain the OP_Transaction?

Dan.


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Possible Bug in VTable handling in SQLite 3.14.2

[Dan Kennedy]

On 10/14/2016 04:59 PM, Hick Gunter wrote:

In the vdbeaux.c source, the function

resolveP2Values(...)

is not resetting p->readOnly when it encounters an OP_VUpdate opcode

is not setting p->bIsReader when it encounters an OP_VFilter opcode


Additionally, the frunction

sqlite3VdbeHalt(...)

is only checking p->bIsReader and omitting to check p->readOnly

even though the comment claims to check "if the program never started or if the SQL 
Statement does not read or write a database file".

These omissions conspire to make SQLite refrain from calling the xSync and 
xCommit entrypoints of the virtual table named in an UPDATE, DELETE or INSERT 
statement.


Do you have a test case for this?

Trying to reproduce here, all VM programs for UPDATE/DELETE/INSERT also 
contain OP_Transaction opcodes. Which cause Vdbe.bIsReader to be set and 
so the xSync/xCommit methods are called. Do the VM programs (EXPLAIN 
output) for whatever statements you're testing with contain the 
OP_Transaction?


Dan.


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

[sqlite] Possible Bug in VTable handling in SQLite 3.14.2

[Hick Gunter]

In the vdbeaux.c source, the function

resolveP2Values(...)

is not resetting p->readOnly when it encounters an OP_VUpdate opcode

is not setting p->bIsReader when it encounters an OP_VFilter opcode


Additionally, the frunction

sqlite3VdbeHalt(...)

is only checking p->bIsReader and omitting to check p->readOnly

even though the comment claims to check "if the program never started or if the 
SQL Statement does not read or write a database file".

These omissions conspire to make SQLite refrain from calling the xSync and 
xCommit entrypoints of the virtual table named in an UPDATE, DELETE or INSERT 
statement.


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] Possible bug in SQLite in sqlite3VdbeSorterInit for PMA sorter size

[Andrew Cunningham]

I am using the option to set the main cache size

*PRAGMA **schema.**cache_size = -**kibibytes**;*

In function sqlite3VdbeSorterInit it seems that the calculation of mxCache
does not take into account the above alternate way to set cache_size that
uses the *-**kibibytes*  option.



 mxCache = db->aDb[0].pSchema->cache_size;
  if( mxCache<(int)szPma ) mxCache = (int)szPma;
  pSorter->mxPmaSize = MIN((i64)mxCache*pgsz, SQLITE_MAX_PMASZ);

[sqlite] Possible bug in the SQL parser

[João Ramos]

Sorry for the late reply. That output (--1 etc.) was me manually
"formatting" the results.
I came across this issue using SQLiteStudio v3.0.7 on Windows. I just
create a new DB and run that script: it outputs two rows, with one column
each, with the values 1 and 2 respectively, instead of an error.


On Mon, Feb 29, 2016 at 11:18 AM, R Smith  wrote:

>
>
> On 2016/02/29 12:49 PM, Jo?o Ramos wrote:
>
>> Maybe this has been fixed then? This is what I'm getting:
>>
>> select sqlite_version(); -- 3.8.10
>>
>> select sqlite_source_id(); -- 2015-05-04 19:13:25
>> 850c11866686a7b39d7b163fb60898c11283688e
>>
>>
>> WITH
>>
>> tA(id, name) AS
>>
>> (
>>
>> SELECT 1, "a" UNION ALL SELECT 2, "b"
>>
>> ),
>>
>> tB(name) AS
>>
>> (
>>
>> SELECT "a" UNION ALL SELECT "b"
>>
>> )
>>
>> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>>
>>
>> -- 1
>>
>> -- 2
>>
>
> This output ( -- 1 etc.) looks like it is produced by some SQLite
> interface type thing or perhaps an admin tool, it doesn't look like the
> SQLite cli, so maybe there's possibly something wrong there? Many of those
> tools substitute the column names a bit in queries.
>
> Show us the exact tool you use to get it and also the OS version etc.
> please.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Jo?o Ramos*

[sqlite] Possible bug

[Simon Slavin]

On 10 Mar 2016, at 4:43am, Marv Anderson  wrote:

> SQL Logic error or missing database near "SELECT": syntax error
> 
>SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]

This involves two things which are not built into SQLite:

CONVERT()
SCOPE_IDENTITY()

Assuming that they're provided by your development environment that's fine.

Unfortunately, the SQLite interface you're using ignored the detailed error 
message produced by SQLite, which would tell you where the syntax error was, 
and instead is just giving you a generic error message.  I suspect you should 
talk to people who know about the SQLite Linq package to get a better 
understanding of the fault.

Simon.

[sqlite] Possible bug

[Marv Anderson]

Hello,

I am a member of this list, but I am not sure which email address you 
have.  I have tried the ones that I usually use, but my messages are 
getting held due to not recognizing my address.

I am having a problem doing a Linq Insert using SQLite in Visual Studio 
2015 (Community version) in WIndows 10.  I installed the latest release 
of all of the SQLite code using NuGet immediately before running this 
latest test.

I am assuming that I am doing something wrong, since it's hard to 
believe that a bug this obvious exists in this product, but I sure can't 
see what my error is. I hope someone can point out the problem.  I have 
had this problem in earlier releases, with other tables, with other 
versions of VS, and other versions of WIndows.  I finally decided to 
isolate the logic to the simplest possible situation. and the problem 
persists.

I am not sure if this is a proper way to submit a potential bug report, 
but I can't find any other mechanism for bug reports, and saw an earlier 
mention of submitting bugs to this list.  I hope that I have included 
everything that might be pertinent to this issue - if not, please let me 
know what else is needed.

Thanks

Marv Anderson

I get the following message every time:

SQL Logic error or missing database near "SELECT": syntax error

I think that the problem is with the CONVERT(?), since I cannot find any 
SQLite documentation that mentions this command. But it appears that the 
CONVERT is generated by the SQLite Linq package.

Here is the log file produced by the operation.

 INSERT INTO [Test]([Date1], [Char1], [Int1])
 VALUES (@p0, @p1, @p2)

 SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
 -- @p0: Input DateTime (Size = -1; Prec = 0; Scale = 0) [1/1/0001 
12:00:00 AM]
 -- @p1: Input String (Size = 4000; Prec = 0; Scale = 0) [Testing 
Insert]
 -- @p2: Input Int32 (Size = -1; Prec = 0; Scale = 0) [0]

 this calls the testing logic

 TestInsert test = new TestInsert();
 test.DoTheInsert("Testing Insert");

 this is the class containing the testing logic

 class TestInsert{
  [Table(Name="Test")]
  public  class qTest{
[Column(AutoSync = AutoSync.OnInsert, IsPrimaryKey = 
true, IsDbGenerated = true)]
 public int Id{get;set;}
[Column(UpdateCheck=UpdateCheck.Never)]
 public DateTime Date1{get;set;}
[Column(UpdateCheck=UpdateCheck.Never)]
 public String Char1{get;set;}
[Column(UpdateCheck=UpdateCheck.Never)]
 public int Int1{get;set;}
  publicqTest( string Char1){//this let's us set all of 
the values in one line of code
}//c
  publicqTest(){//this let's us set all of the values in 
one line of code
}//c
  }//cl:qProgress
  publicTestInsert(){
//DoTheInsert( "Test");
}
  publicvoid DoTheInsert( string pChar){//
DataContext context = new DataContext( cTb.Db.DB );
context.Log = new 
StreamWriter("linq-to-sql-Test.log"){ AutoFlush = true };
qTest test = new qTest();
test.Char1 = pChar;
context.GetTable().InsertOnSubmit( test);
try {
   context.SubmitChanges();
} catch(Exception ex) {
MessageBox.Show(ex.Message);
}
}//m
  }

Here is the DDL for the table into which I am trying to do the insert.

 CREATE TABLE [Test] (
   [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   [Char1] NCHAR DEFAULT "Char",
   [Int1] INTEGER NOT NULL DEFAULT 0,
   [Date1] DATETIME DEFAULT (datetime('now')));

[sqlite] Possible bug in the SQL parser

[R Smith]

On 2016/02/29 12:49 PM, Jo?o Ramos wrote:
> Maybe this has been fixed then? This is what I'm getting:
>
> select sqlite_version(); -- 3.8.10
>
> select sqlite_source_id(); -- 2015-05-04 19:13:25
> 850c11866686a7b39d7b163fb60898c11283688e
>
>
> WITH
>
> tA(id, name) AS
>
> (
>
> SELECT 1, "a" UNION ALL SELECT 2, "b"
>
> ),
>
> tB(name) AS
>
> (
>
> SELECT "a" UNION ALL SELECT "b"
>
> )
>
> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>
>
> -- 1
>
> -- 2

This output ( -- 1 etc.) looks like it is produced by some SQLite 
interface type thing or perhaps an admin tool, it doesn't look like the 
SQLite cli, so maybe there's possibly something wrong there? Many of 
those tools substitute the column names a bit in queries.

Show us the exact tool you use to get it and also the OS version etc. 
please.

[sqlite] Possible bug in the SQL parser

[João Ramos]

Maybe this has been fixed then? This is what I'm getting:

select sqlite_version(); -- 3.8.10

select sqlite_source_id(); -- 2015-05-04 19:13:25
850c11866686a7b39d7b163fb60898c11283688e


WITH

tA(id, name) AS

(

SELECT 1, "a" UNION ALL SELECT 2, "b"

),

tB(name) AS

(

SELECT "a" UNION ALL SELECT "b"

)

SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);


-- 1

-- 2


On Fri, Feb 26, 2016 at 6:19 PM, Dan Kennedy  wrote:

> On 02/27/2016 12:49 AM, Jo?o Ramos wrote:
>
>> I'm using SQLite 3.8.10.2 and the following query illustrates the problem:
>>
>> WITH
>> tA(id, name) AS
>> (
>>  SELECT 1, "a" UNION ALL SELECT 2, "b"
>> ),
>> tB(name) AS
>> (
>>  SELECT "a" UNION ALL SELECT "b"
>> )
>> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>>
>>
>> There is no _id_ column in the tB table, yet the statement doesn't produce
>> any error and in fact will return the ids of table tA. This doesn't seem
>> correct to me.
>>
>>
>>
> Cannot reproduce this problem here:
>
> dan at darkstar:~/work/sqlite/bld$ ./sqlite3
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> WITH tA(id, name) AS (
>...>   SELECT 1, "a" UNION ALL SELECT 2, "b"
>...> ),
>...> tB(name) AS (
>...>   SELECT "a" UNION ALL SELECT "b"
>...> )
>...> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
> Error: no such column: tB.id
>
>
> If you were using "oid", "rowid" or "_rowid_" instead of "id", then it
> might look like it was returning the values from table tA. All views and
> CTEs in SQLite have such columns for historical reasons, but the contents
> of them is both undefined and unstable. Sometimes it's a sequence of
> integers starting at 1.
>
> Dan.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Jo?o Ramos*

[sqlite] Possible bug in the SQL parser

[Richard Hipp]

On 2/29/16, Jo?o Ramos  wrote:
> Maybe this has been fixed then? This is what I'm getting:
>
> select sqlite_version(); -- 3.8.10
>
> select sqlite_source_id(); -- 2015-05-04 19:13:25
> 850c11866686a7b39d7b163fb60898c11283688e

Compiling with that exact same version of the source code, it works fine for me:

drh at bella:~/sqlite/bld$ cat x1.txt
SELECT sqlite_version();
SELECT sqlite_source_id();
WITH
tA(id, name) AS
(
SELECT 1, "a" UNION ALL SELECT 2, "b"
),
tB(name) AS
(
SELECT "a" UNION ALL SELECT "b"
)
SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
drh at bella:~/sqlite/bld$ ./sqlite3 
>
> WITH
>
> tA(id, name) AS
>
> (
>
> SELECT 1, "a" UNION ALL SELECT 2, "b"
>
> ),
>
> tB(name) AS
>
> (
>
> SELECT "a" UNION ALL SELECT "b"
>
> )
>
> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>
>
> -- 1
>
> -- 2
>
>
> On Fri, Feb 26, 2016 at 6:19 PM, Dan Kennedy  wrote:
>
>> On 02/27/2016 12:49 AM, Jo?o Ramos wrote:
>>
>>> I'm using SQLite 3.8.10.2 and the following query illustrates the
>>> problem:
>>>
>>> WITH
>>> tA(id, name) AS
>>> (
>>>  SELECT 1, "a" UNION ALL SELECT 2, "b"
>>> ),
>>> tB(name) AS
>>> (
>>>  SELECT "a" UNION ALL SELECT "b"
>>> )
>>> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>>>
>>>
>>> There is no _id_ column in the tB table, yet the statement doesn't
>>> produce
>>> any error and in fact will return the ids of table tA. This doesn't seem
>>> correct to me.
>>>
>>>
>>>
>> Cannot reproduce this problem here:
>>
>> dan at darkstar:~/work/sqlite/bld$ ./sqlite3
>> SQLite version 3.8.10.2 2015-05-20 18:17:19
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> WITH tA(id, name) AS (
>>...>   SELECT 1, "a" UNION ALL SELECT 2, "b"
>>...> ),
>>...> tB(name) AS (
>>...>   SELECT "a" UNION ALL SELECT "b"
>>...> )
>>...> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>> Error: no such column: tB.id
>>
>>
>> If you were using "oid", "rowid" or "_rowid_" instead of "id", then it
>> might look like it was returning the values from table tA. All views and
>> CTEs in SQLite have such columns for historical reasons, but the contents
>> of them is both undefined and unstable. Sometimes it's a sequence of
>> integers starting at 1.
>>
>> Dan.
>>
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> *Jo?o Ramos*
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Possible bug in the SQL parser

[Dan Kennedy]

On 02/27/2016 12:49 AM, Jo?o Ramos wrote:
> I'm using SQLite 3.8.10.2 and the following query illustrates the problem:
>
> WITH
> tA(id, name) AS
> (
>  SELECT 1, "a" UNION ALL SELECT 2, "b"
> ),
> tB(name) AS
> (
>  SELECT "a" UNION ALL SELECT "b"
> )
> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>
>
> There is no _id_ column in the tB table, yet the statement doesn't produce
> any error and in fact will return the ids of table tA. This doesn't seem
> correct to me.
>
>

Cannot reproduce this problem here:

dan at darkstar:~/work/sqlite/bld$ ./sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> WITH tA(id, name) AS (
...>   SELECT 1, "a" UNION ALL SELECT 2, "b"
...> ),
...> tB(name) AS (
...>   SELECT "a" UNION ALL SELECT "b"
...> )
...> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
Error: no such column: tB.id


If you were using "oid", "rowid" or "_rowid_" instead of "id", then it 
might look like it was returning the values from table tA. All views and 
CTEs in SQLite have such columns for historical reasons, but the 
contents of them is both undefined and unstable. Sometimes it's a 
sequence of integers starting at 1.

Dan.

[sqlite] Possible bug in the SQL parser

[João Ramos]

I'm using SQLite 3.8.10.2 and the following query illustrates the problem:

WITH
tA(id, name) AS
(
SELECT 1, "a" UNION ALL SELECT 2, "b"
),
tB(name) AS
(
SELECT "a" UNION ALL SELECT "b"
)
SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);


There is no _id_ column in the tB table, yet the statement doesn't produce
any error and in fact will return the ids of table tA. This doesn't seem
correct to me.


-- 
*Jo?o Ramos*

[sqlite] Possible bug?

[Quan Yong Zhai]

SQLite version 3.9.2 2015-11-02 18:31:45
sqlite> .header on
sqlite> select 0x1zzz;
zzz
1
sqlite>

[sqlite] Possible bug?

[Richard Hipp]

On 11/13/15, Quan Yong Zhai  wrote:
> SQLite version 3.9.2 2015-11-02 18:31:45
> sqlite> .header on
> sqlite> select 0x1zzz;
> zzz
> 1
> sqlite>

This is parsed as:

SELECT 0x1 AS zzz;

PostgreSQL the same thing (modulo the fact that postgres 7.3 does not
support hexadecimal integer literals).  So then according to the WWPD
principle, this is not a bug.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Possible bug

[Simon Slavin]

On 11 Nov 2015, at 10:38pm, Adams, Justin  
wrote:

> VARCHAR(20)

Not a solution to the problem or a diagnosis of the bug, it's worth saying that 
SQLite has no VARCHAR type.  Columns where you specify VARCHAR will be 
understood as TEXT, and truncation will never occur.

Simon.

[sqlite] Possible bug

[Richard Hipp]

On 11/11/15, Adams, Justin  wrote:
>
> I was trying to create a table with a column for IPv4 addresses as VARCHAR.
> It seems when I set the .mode to column that some records are truncated on
> the right. Changing the .mode fixes the problem, so I would think it has to
> do with the column width calculation.

There is a separate ".width" command to set the widths of all columns.  The
default width is 10, iirc.  The column widths are not auto-computed in
".mode column".

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Possible bug

[Adams, Justin]

Hello,

I searched the bug tracker and the only thing I saw that looked relevant was 
ticket UUID 6c266900a22574d4d6474503da5bfe849c8b244f.
http://www.sqlite.org/src/tktview?name=6c266900a2

However, that seems to deal with special Unicode characters and I'm not dealing 
with that here.

I'm using SQLite 3.8.11.1 on Windows 7 Pro 64 built with Visual C++ 2010.

I was trying to create a table with a column for IPv4 addresses as VARCHAR. It 
seems when I set the .mode to column that some records are truncated on the 
right. Changing the .mode fixes the problem, so I would think it has to do with 
the column width calculation. Following my sig is some example code. You will 
see .mode is changed to column then "172.16.18.239" is truncated to 
"172.16.18.23". In the second example you will see "10.999.987.25" gets 
truncated to "10.999.987.".

Thanks to everyone for your help. If there is any more info you need please let 
me know. Also, if anybody can tell me what file/function in the source deals 
with this I would appreciate it.

Thank you,
Justin Adams

Example 1

C:\>C:\app\sqlite\sqlite3.exe C:\Temp\bugdemo.sqlite
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE ipAddresses(ip VARCHAR(20) PRIMARY KEY);
sqlite> INSERT INTO ipAddresses VALUES ('10.186.34.23'), ('192.168.1.4'), 
('172.16.18.239'), ('54.6.74.21'), ('8.8.8.8');
sqlite> SELECT * FROM ipAddresses;
10.186.34.23
192.168.1.4
172.16.18.239
54.6.74.21
8.8.8.8
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM ipAddresses;
ip

10.186.34.23
192.168.1.4
172.16.18.23
54.6.74.21
8.8.8.8
sqlite> .mode list
sqlite> SELECT * FROM ipAddresses;
ip
10.186.34.23
192.168.1.4
172.16.18.239
54.6.74.21
8.8.8.8
sqlite> .mode column
sqlite> SELECT * FROM ipAddresses;
ip

10.186.34.23
192.168.1.4
172.16.18.23
54.6.74.21
8.8.8.8




Example 2

C:\home\db\static>C:\app\sqlite\sqlite3.exe static.sqlite
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE tableCategories(category VARCHAR(16) PRIMARY KEY);
CREATE TABLE tableSites(siteName VARCHAR(64) PRIMARY KEY);
CREATE TABLE tableStatic
(
ip VARCHAR(16) PRIMARY KEY,
location VARCHAR(64) REFERENCES tableSites (siteName) ON UPDATE CASCADE,
category VARCHAR(16) REFERENCES tableCategories (category) ON UPDATE 
CASCADE,
description VARCHAR(64)
);
sqlite> SELECT ip, category FROM tableStatic;
10.537.79.7|PBX
10.999.987.25|PBX
sqlite> .mode column
sqlite> SELECT ip, category FROM tableStatic;
10.537.79.7  PBX
10.999.987.  PBX
sqlite> SELECT category, ip FROM tableStatic;
PBX 10.537.79.7
PBX 10.999.987.
sqlite> .mode list
sqlite> SELECT category, ip FROM tableStatic;
PBX|10.537.79.7
PBX|10.999.987.25

Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of the intended recipient(s) and may contain confidential and 
privileged information. Any unauthorized review, use, disclosure, or 
distribution is prohibited. If you are not the intended recipient, please 
contact the sender by reply e-mail and destroy all copies of the original 
message.

[sqlite] Possible bug in shell .open command?

[Jean Chevalier]

The aftermath...

dir *. /x /b

h8 08 @_8 0o8 hN Q N xa8 b8 0f8

10 soubor?, 14,336 bajt?

[sqlite] Possible bug in shell .open command?

[Jean Chevalier]

I took that the .open command could be issued as ".open" to open a new
in-memory database and ".open ''" (followed by a pair of single quotes)
to open a new unnamed temporary file database.

I wonder what is going on here: After issuing a short combination of
these commands with/without putting anything into the databases, or
saving them, it ends up printing an error "unable to open database
; unable to open file."? Would the printing of gibberish mean
is trying to read a file name where there isn't one, i.e., reading a
memory address that wasn't previously written to?

To reproduce, save the following as a script and invoke the shell with
the init clause to call the script.? If you see no error, quit the shell
and try again, you should soon see it.? Notice it's not a problem of the
init clause since the same happens if I type this interactively.

.open '' .open .open create table x(y); .open '' .open '' .open .open

This would not be a problem unless someone could potentially write a
function as part of an API that would attempt to reset the database
prior to doing some other work, a reset sometimes unnecessarily but
not expected to be harmful.? Then repeated calls to a db open command
that normally doesn't cause a problem on its own could cause the
above problem.

[sqlite] Possible bug in shell .open command?

[Richard Hipp]

Should be fixed now on trunk.

On 6/17/15, Jean Chevalier  wrote:
> I took that the .open command could be issued as ".open" to open a new
> in-memory database and ".open ''" (followed by a pair of single quotes)
> to open a new unnamed temporary file database.
>
> I wonder what is going on here: After issuing a short combination of
> these commands with/without putting anything into the databases, or
> saving them, it ends up printing an error "unable to open database
> ; unable to open file."  Would the printing of gibberish mean
> is trying to read a file name where there isn't one, i.e., reading a
> memory address that wasn't previously written to?
>
> To reproduce, save the following as a script and invoke the shell with
> the init clause to call the script.  If you see no error, quit the shell
> and try again, you should soon see it.  Notice it's not a problem of the
> init clause since the same happens if I type this interactively.
>
> .open '' .open .open create table x(y); .open '' .open '' .open .open
>
> This would not be a problem unless someone could potentially write a
> function as part of an API that would attempt to reset the database
> prior to doing some other work, a reset sometimes unnecessarily but
> not expected to be harmful.  Then repeated calls to a db open command
> that normally doesn't cause a problem on its own could cause the
> above problem.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Possible Bug

[Simon Slavin]

On 31 May 2015, at 2:22am, Alan Bryan  wrote:

> SELECT *, Sum(MyField) AS MySumField FROM MyTable
> 
> Now run sqlite3  and type the following:
> 
> PRAGMA table_info(MyView);
> 
> You will notice there is no data type for some reason.

I think you get datatypes only for exact copies of a source column.  Here's my 
experiment:

SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (intF INTEGER, realF REAL);
sqlite> INSERT INTO myTable VALUES (1,1);
sqlite> INSERT INTO myTable VALUES (2.0,2.0);
sqlite> INSERT INTO myTable VALUES (3,3.0);
sqlite> CREATE VIEW myView2 AS SELECT intF,realF,intF+1,realF*realF FROM 
myTable;
sqlite> SELECT * FROM myView2;
1|1.0|2|1.0
2|2.0|3|4.0
3|3.0|4|9.0
sqlite> PRAGMA table_info(myView2);
0|intF|INTEGER|0||0
1|realF|REAL|0||0
2|intF+1||0||0
3|realF*realF||0||0

The only columns which have a datatype are those which are exact copies of 
source columns.  I conclude that PRAGMA table_info() just isn't that clever.

Simon.

[sqlite] Possible Bug

[Keith Medcalf]

The "data type" is associated with the data value itself.  Columns do not have 
Datatypes -- they have affinities.  The column affinity is only used when doing 
certain and specific operations on real tables.  Since you cannot do these 
operations on a view, the column affinity is meaningless and is just a comment. 
 You can create these comments for views, however, if you declare the view such 
that it includes them (ie, just as you would declare a table).  However, if you 
use a shortcut method to declare a view, then the comments are left out simply 
beause to compute them would serve no purpose, and having them provides no 
advantage.


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Alan Bryan
> Sent: Saturday, 30 May, 2015 19:22
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Possible Bug
> 
> I am running into an issue with SQLite that I think might be a bug. When I
> run the PRAGMA table_info
> <http://t.signauxcinq.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XZsRz
> DgTW5vwkZs8pTgPFVRbxS056dNRVf9jsl6n02?t=https%3A%2F%2Fwww.sqlite.org%2Fpra
> gma.html%23pragma_table_info=5896992917028864=edf25f00-c978-4b44-
> 96cc-867a8bd93485>
> command
> on a view that uses an aggregate function, the data type always comes up
> empty.
> 
> To duplicate the issue, create a new SQLite 3 database and add a table.
> Add
> at least one field in the table and make sure its a numeric field (such as
> integer or float or something). Then create a new view with a SELECT
> statement
> similar to the following:
> 
> SELECT *, Sum(MyField) AS MySumField FROM MyTable
> 
> Now run sqlite3  and type the following:
> 
> PRAGMA table_info(MyView);
> 
> You will notice there is no data type for some reason. Here is my output
> when I issued the command:
> 
> 0|Field1|INTEGER|0||01|Field2|INTEGER|0||02|Field3|FLOAT|0||03|SumField2||
> 0||0
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users