Re: [sqlite] Bug

[D. Richard Hipp]

On Dec 31, 2009, at 6:27 AM, Wiktor Adamski wrote:

> SQLite version 3.6.21
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t1(a int);
> sqlite> create table t2(a int);
> sqlite> create table t3(a int);
> sqlite> insert into t1 values(1);
> sqlite> insert into t2 values(2);
> sqlite> insert into t3 values(1);
> sqlite> select * from t1 join t2 on t1.a < t2.a join t3 using(a);
> sqlite> update t3 set a = 2;
> sqlite> select * from t1 join t2 on t1.a < t2.a join t3 using(a);
> 1|2
>
> First join produces 2 columns named a so result of the query should by
> similar to:
> MySQL: Column 'a' in from clause is ambiguous
> PostgreSQL: ERROR: common column name "a" appears more than once in
> left table

Already fixed.  See http://www.sqlite.org/src/info/f74beaabde

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

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Bug

[Wiktor Adamski]

> Already fixed.  Seehttp://www.sqlite.org/src/info/f74beaabde

I'm might be wrong because I didn't check with the newest binary, but
if I remember correctly this fix will compare t3.a to t1.a (instead of
t2.a). So it will produce different results (first select will be 1|2
and second will be empty) but still incorrect because error should be
returned.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[P Kishor]

On Thu, Apr 15, 2010 at 3:36 AM, Wiktor Adamski
 wrote:
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(a);
> sqlite> insert into t values(1);
> sqlite> insert into t values(2);
> sqlite> select * from (select * from t limit 1)
>   ...> union all
>   ...> select 3;
> 1
> sqlite>


fwiw, the above works correctly in 3.6.23 (see below), so that minor
version number bump might have introduced the issue

punk...@lucknow ~$sqlite3
-- Loading resources from /Users/punkish/.sqliterc
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (a);
sqlite> INSERT INTO t VALUES (1);
sqlite> INSERT INTO t VALUES (2);
sqlite> SELECT * FROM t;
a
--
1
2
sqlite> SELECT * FROM t LIMIT 1;
a
--
1
sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1);
a
--
1
sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1) UNION SELECT 3;
a
--
1
3
sqlite>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Pavel Ivanov]

Puneet, probably you are wrong. UNION and UNION ALL behave differently:

SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (a);
sqlite> INSERT INTO t VALUES (1);
sqlite> INSERT INTO t VALUES (2);
sqlite> SELECT * FROM t;
1
2
sqlite> SELECT * FROM t LIMIT 1;
1
sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1);
1
sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1) UNION SELECT 3;
1
3
sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1) UNION ALL SELECT 3;
1
sqlite>

Pavel

On Thu, Apr 15, 2010 at 10:22 AM, P Kishor  wrote:
> On Thu, Apr 15, 2010 at 3:36 AM, Wiktor Adamski
>  wrote:
>> SQLite version 3.6.23.1
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> create table t(a);
>> sqlite> insert into t values(1);
>> sqlite> insert into t values(2);
>> sqlite> select * from (select * from t limit 1)
>>   ...> union all
>>   ...> select 3;
>> 1
>> sqlite>
>
>
> fwiw, the above works correctly in 3.6.23 (see below), so that minor
> version number bump might have introduced the issue
>
> punk...@lucknow ~$sqlite3
> -- Loading resources from /Users/punkish/.sqliterc
> SQLite version 3.6.23
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t (a);
> sqlite> INSERT INTO t VALUES (1);
> sqlite> INSERT INTO t VALUES (2);
> sqlite> SELECT * FROM t;
> a
> --
> 1
> 2
> sqlite> SELECT * FROM t LIMIT 1;
> a
> --
> 1
> sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1);
> a
> --
> 1
> sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1) UNION SELECT 3;
> a
> --
> 1
> 3
> sqlite>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[D. Richard Hipp]

On Apr 15, 2010, at 10:22 AM, P Kishor wrote:

> On Thu, Apr 15, 2010 at 3:36 AM, Wiktor Adamski
>  wrote:
>> SQLite version 3.6.23.1
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> create table t(a);
>> sqlite> insert into t values(1);
>> sqlite> insert into t values(2);
>> sqlite> select * from (select * from t limit 1)
>>   ...> union all
>>   ...> select 3;
>> 1
>> sqlite>
>
>
> fwiw, the above works correctly in 3.6.23 (see below), so that minor
> version number bump might have introduced the issue

The problem is with UNION ALL, not UNION.  The problem was actually  
introduced on 2005-10-06 for version 3.2.8 by check-in:

   http://www.sqlite.org/src/info/edca8913ca012fc0c17343a27f819de95147b1bd

This problem has been in every version of SQLite for the past four and  
a half years and nobody before now has noticed.

>
> punk...@lucknow ~$sqlite3
> -- Loading resources from /Users/punkish/.sqliterc
> SQLite version 3.6.23
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t (a);
> sqlite> INSERT INTO t VALUES (1);
> sqlite> INSERT INTO t VALUES (2);
> sqlite> SELECT * FROM t;
> a
> --
> 1
> 2
> sqlite> SELECT * FROM t LIMIT 1;
> a
> --
> 1
> sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1);
> a
> --
> 1
> sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1) UNION SELECT 3;
> a
> --
> 1
> 3
> sqlite>
>
>
>
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/ 
> kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is  
> science
> = 
> ==
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] BUG

[Richard Hipp]

On Mon, Oct 25, 2010 at 1:18 PM, Alexey Pechnikov wrote:

>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id
> ORDER BY name ASC;
>

The result of the view above is undefined.  It will choose one of the
user_record rows for each distinct user.id, but you don't know which row.
Your queries below return different results depending on which of the
user_record rows is choosen.  3.7.2 just happened to choose a different
result row from 3.7.3.  But that is not a bug.



>
> -- returns two rows
> select * from main.view_user where record_id in (select record_id from
> main.view_user where name like '%');
> -- but count(*) returns 1
> select count(*) from main.view_user where record_id in (select record_id
> from main.view_user where name like '%');
> -- equal query returns only single row!
> select * from main.view_user where record_id in (select record_id from
> main.view_user where name like '%') order by name;
>
> ---
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] BUG

[Alexey Pechnikov]

> The result of the view above is undefined.  It will choose one of the
> user_record rows for each distinct user.id, but you don't know which row.

Yes! But it choose only single user_record row for two distinct user_id in
count(*) expression.

Are you really think that count(*)=1 for _two_ rows is not the bug?

sqlite> select * from test;
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1

CREATE TABLE user
(
  id INTEGER PRIMARY KEY
);
INSERT INTO "user" VALUES(4);
INSERT INTO "user" VALUES(11);

CREATE TABLE user_record
(
  record_id INTEGER PRIMARY KEY,
  record_version INTEGER,
  user_id INTEGER NOT NULL,
  name TEXT
);
INSERT INTO "user_record" VALUES(76,8,11,'A');
INSERT INTO "user_record" VALUES(86,11,4,'B');
INSERT INTO "user_record" VALUES(87,3,4,'B');

CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id;

create temp view test as select * from main.view_user where record_id in
(select record_id from main.view_user where name like '%');

select * from test;
select count(*) from test;


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

[Black, Michael (IS)]

You failed to say what version you are using.  Running your SQL on 3.7.2 works 
just fine.
 
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE user
   ...> (
   ...>   id INTEGER PRIMARY KEY
   ...> );
sqlite> INSERT INTO "user" VALUES(4);
sqlite> INSERT INTO "user" VALUES(11);
sqlite>
sqlite> CREATE TABLE user_record
   ...> (
   ...>   record_id INTEGER PRIMARY KEY,
   ...>   record_version INTEGER,
   ...>   user_id INTEGER NOT NULL,
   ...>   name TEXT
   ...> );
sqlite> INSERT INTO "user_record" VALUES(76,8,11,'A');
sqlite> INSERT INTO "user_record" VALUES(86,11,4,'B');
sqlite> INSERT INTO "user_record" VALUES(87,3,4,'B');
sqlite>
sqlite> CREATE VIEW view_user AS
   ...> SELECT user.id,user_record.*
   ...> FROM user, user_record
   ...> WHERE user.id=user_record.user_id
   ...> GROUP BY user.id;
sqlite>
sqlite> create temp view test as select * from main.view_user where record_id in
   ...> (select record_id from main.view_user where name like '%');
sqlite>
sqlite> select * from test;
4|86|11|4|B
11|76|8|11|A
sqlite> select count(*) from test;
2
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
Sent: Mon 10/25/2010 3:06 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] BUG



> The result of the view above is undefined.  It will choose one of the
> user_record rows for each distinct user.id, but you don't know which row.

Yes! But it choose only single user_record row for two distinct user_id in
count(*) expression.

Are you really think that count(*)=1 for _two_ rows is not the bug?

sqlite> select * from test;
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1


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

Re: [sqlite] BUG

[Richard Hipp]

On Mon, Oct 25, 2010 at 4:06 PM, Alexey Pechnikov wrote:

> > The result of the view above is undefined.  It will choose one of the
> > user_record rows for each distinct user.id, but you don't know which
> row.
>
> Yes! But it choose only single user_record row for two distinct user_id in
> count(*) expression.
>
> Are you really think that count(*)=1 for _two_ rows is not the bug?
>

You miss my point.  "test" in this case doesn't have one row or two rows.
It has an arbitrary number of rows due to indeterminacy in your view.
Sometimes "test" will return one row.  Sometimes it will return two.  You
can never predict which.  Both are "correct" in the sense that both are
allowed interpretations of what SQLite ought to do.

The above will never happen for a simple table named "test".  It only
happens for things like:

 ... WHERE record_id IN (SELECT record_id FROM view_user WHERE name LIKE
'%');

where the record_id value returned from the view_user view is
indeterminate.  The WHERE clause above might be equivalent to

 ... WHERE record_id IN (76,86)

and in that case count(*) will return 2.  But the WHERE clause might also be
equivalent to

 ... WHERE record_id IN (76,87)

in which case count(*) will return 1.  SQLite is free to choose either
interpretation for the subquery in your WHERE clause, and hence might get
either 1 or 2 as the count(*) result.  Version 3.7.2 happened to get 2.
Version 3.7.3 happens to get 1.  Who knows what 3.7.4 will get - both
answers are correct


> sqlite> select * from test;
> 4|87|3|4|B
> 11|76|8|11|A
> sqlite> select count(*) from test;
> 1
> 
> CREATE TABLE user
> (
>  id INTEGER PRIMARY KEY
> );
> INSERT INTO "user" VALUES(4);
> INSERT INTO "user" VALUES(11);
>
> CREATE TABLE user_record
> (
>  record_id INTEGER PRIMARY KEY,
>  record_version INTEGER,
>  user_id INTEGER NOT NULL,
>  name TEXT
> );
> INSERT INTO "user_record" VALUES(76,8,11,'A');
> INSERT INTO "user_record" VALUES(86,11,4,'B');
> INSERT INTO "user_record" VALUES(87,3,4,'B');
>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id;
>
> create temp view test as select * from main.view_user where record_id in
> (select record_id from main.view_user where name like '%');
>
> select * from test;
> select count(*) from test;
> 
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] BUG

[Black, Michael (IS)]

To add on...it does NOT work under 3.7.3...so perhaps that's what you're using? 
 I agree this doesn't seem intuitive at all...
 
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

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

Re: [sqlite] BUG

[Black, Michael (IS)]

And...if you drop the "group by" from the view it's correct again
 
sqlite> drop view view_user;
sqlite> CREATE VIEW view_user AS
   ...> SELECT user.id,user_record.*
   ...> FROM user, user_record
   ...> WHERE user.id=user_record.user_id
   ...> ;
sqlite> select * from test;
11|76|8|11|A
4|86|11|4|B
4|87|3|4|B
sqlite> select count(*) from test;
3
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
Sent: Mon 10/25/2010 3:06 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] BUG



> The result of the view above is undefined.  It will choose one of the
> user_record rows for each distinct user.id, but you don't know which row.

Yes! But it choose only single user_record row for two distinct user_id in
count(*) expression.

Are you really think that count(*)=1 for _two_ rows is not the bug?

sqlite> select * from test;
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1

CREATE TABLE user
(
  id INTEGER PRIMARY KEY
);
INSERT INTO "user" VALUES(4);
INSERT INTO "user" VALUES(11);

CREATE TABLE user_record
(
  record_id INTEGER PRIMARY KEY,
  record_version INTEGER,
  user_id INTEGER NOT NULL,
  name TEXT
);
INSERT INTO "user_record" VALUES(76,8,11,'A');
INSERT INTO "user_record" VALUES(86,11,4,'B');
INSERT INTO "user_record" VALUES(87,3,4,'B');

CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id;

create temp view test as select * from main.view_user where record_id in
(select record_id from main.view_user where name like '%');

select * from test;
select count(*) from test;


--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] BUG

[Alexey Pechnikov]

I did get this frustrate behaviour in my prototype of the versioning
datastore where all versions of records are stored permanently. In my
example the foreign identifiers are stored in the user table and all rows
versions are stored in the user_record table. For visualization we need to
get only last versions of records and so"group by timestamp" is needed in
view (in the example above I did replace "timestamp" to "name" field). But
some search operations can use all versions of records. My tests on the
datastore are successfully tested with previous SQLite builds but returns
strange results now and I simplificate "wrong" tests to this example. I
think it may be not so trivial to find this "not bug" in production code...
Of cource in development stage is not very difficult to rewrite such
queries.

2010/10/26 Black, Michael (IS) 

> And...if you drop the "group by" from the view it's correct again
>
> sqlite> drop view view_user;
> sqlite> CREATE VIEW view_user AS
>...> SELECT user.id,user_record.*
>   ...> FROM user, user_record
>   ...> WHERE user.id=user_record.user_id
>...> ;
> sqlite> select * from test;
> 11|76|8|11|A
> 4|86|11|4|B
> 4|87|3|4|B
> sqlite> select count(*) from test;
> 3
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
> Sent: Mon 10/25/2010 3:06 PM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] BUG
>
>
>
> > The result of the view above is undefined.  It will choose one of the
> > user_record rows for each distinct user.id, but you don't know which
> row.
>
> Yes! But it choose only single user_record row for two distinct user_id in
> count(*) expression.
>
> Are you really think that count(*)=1 for _two_ rows is not the bug?
>
> sqlite> select * from test;
> 4|87|3|4|B
> 11|76|8|11|A
> sqlite> select count(*) from test;
> 1
> 
> CREATE TABLE user
> (
>  id INTEGER PRIMARY KEY
> );
> INSERT INTO "user" VALUES(4);
> INSERT INTO "user" VALUES(11);
>
> CREATE TABLE user_record
> (
>  record_id INTEGER PRIMARY KEY,
>  record_version INTEGER,
>  user_id INTEGER NOT NULL,
>  name TEXT
> );
> INSERT INTO "user_record" VALUES(76,8,11,'A');
> INSERT INTO "user_record" VALUES(86,11,4,'B');
> INSERT INTO "user_record" VALUES(87,3,4,'B');
>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id;
>
> create temp view test as select * from main.view_user where record_id in
> (select record_id from main.view_user where name like '%');
>
> select * from test;
> select count(*) from test;
> 
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

[Igor Tandetnik]

Alexey Pechnikov  wrote:
> I did get this frustrate behaviour in my prototype of the versioning
> datastore where all versions of records are stored permanently. In my
> example the foreign identifiers are stored in the user table and all rows
> versions are stored in the user_record table. For visualization we need to
> get only last versions of records

But view_user statement makes no attempt to select the last version. It picks 
some arbitrary random version. You might want to consider something like this:

CREATE VIEW view_user AS
SELECT * FROM user_record
WHERE record_id in
(select max(record_id) from user_record group by user_id)
ORDER BY name ASC;

-- or

CREATE VIEW view_user AS
SELECT * FROM user_record r1
WHERE r1.record_id =
(select max(record_id) from user_record r2 where r1.user_id = r2.user_id)
ORDER BY name ASC;

-- 
Igor Tandetnik


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

Re: [sqlite] BUG

[Alexey Pechnikov]

> But view_user statement makes no attempt to select the last version. It
picks some arbitrary random version. You might want to consider something
like this:

Why you wrote about "some arbitrary random version" when we have sorting by
"ts"?..

CREATE TABLE user
(
  id INTEGER PRIMARY KEY
);
CREATE TABLE user_record
(
  record_id INTEGER PRIMARY KEY,
  record_version INTEGER,
  ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
  user_id INTEGER NOT NULL,
  name TEXT,
  FOREIGN KEY(user_id) REFERENCES user
);

CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id
ORDER BY ts ASC;

This view returns last by "ts" row for each unique "user_id". There is used
the SQLite hint with "group by" (non-grouped values returns too).

And conflict with equal "ts" can be resolved by trigger as:

CREATE TRIGGER view_user_update instead of update on view_user
begin
...
  SELECT RAISE(ABORT, 'User wait 1 second.')
WHERE EXISTS(select 1 from user_record where user_id=OLD.user_id and
ts=strftime('%s','now'));
...
end;

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

[Dan Kennedy]

On Oct 26, 2010, at 2:22 PM, Alexey Pechnikov wrote:

>> But view_user statement makes no attempt to select the last  
>> version. It
> picks some arbitrary random version. You might want to consider  
> something
> like this:
>
> Why you wrote about "some arbitrary random version" when we have  
> sorting by
> "ts"?..

The sorting happens after the grouping. And it is while processing
the GROUP BY clause that SQLite is forced to select an arbitrary
record from the user_record table.

See here:

   http://www.sqlite.org/lang_select.html#resultset

Third paragraph under the third bullet point.



>
> CREATE TABLE user
> (
>  id INTEGER PRIMARY KEY
> );
> CREATE TABLE user_record
> (
>  record_id INTEGER PRIMARY KEY,
>  record_version INTEGER,
>  ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
>  user_id INTEGER NOT NULL,
>  name TEXT,
>  FOREIGN KEY(user_id) REFERENCES user
> );
>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id
> ORDER BY ts ASC;
>
> This view returns last by "ts" row for each unique "user_id". There  
> is used
> the SQLite hint with "group by" (non-grouped values returns too).
>
> And conflict with equal "ts" can be resolved by trigger as:
>
> CREATE TRIGGER view_user_update instead of update on view_user
> begin
> ...
>  SELECT RAISE(ABORT, 'User wait 1 second.')
>WHERE EXISTS(select 1 from user_record where user_id=OLD.user_id  
> and
> ts=strftime('%s','now'));
> ...
> end;
>
> -- 
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] BUG

[Alexey Pechnikov]

2010/10/26 Dan Kennedy 

> The sorting happens after the grouping. And it is while processing
> the GROUP BY clause that SQLite is forced to select an arbitrary
> record from the user_record table.
>
> See here:
>
>   http://www.sqlite.org/lang_select.html#resultset
>
> Third paragraph under the third bullet point.


No. See paragraph under bullet points: "The list of expressions between the
SELECT and FROM keywords". Sort condition IS NOT the expression from the
third bullet point. For sorting we may to see the part "ORDER BY and
LIMIT/OFFSET Clauses".

In the original test script the problem was when two rows are equal for
sorting condition.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

[Alexey Pechnikov]

Richard, with "PRAGMA reverse_unordered_selects = 1;". it's returns
count(*)=0. This is strange for me and not help for development.

2010/10/26 Richard Hipp 

> On Mon, Oct 25, 2010 at 4:06 PM, Alexey Pechnikov  >wrote:
>
> > > The result of the view above is undefined.  It will choose one of the
> > > user_record rows for each distinct user.id, but you don't know which
> > row.
> >
> > Yes! But it choose only single user_record row for two distinct user_id
> in
> > count(*) expression.
> >
> > Are you really think that count(*)=1 for _two_ rows is not the bug?
> >
>
> You miss my point.  "test" in this case doesn't have one row or two rows.
> It has an arbitrary number of rows due to indeterminacy in your view.
> Sometimes "test" will return one row.  Sometimes it will return two.  You
> can never predict which.  Both are "correct" in the sense that both are
> allowed interpretations of what SQLite ought to do.
>
> The above will never happen for a simple table named "test".  It only
> happens for things like:
>
> ... WHERE record_id IN (SELECT record_id FROM view_user WHERE name LIKE
> '%');
>
> where the record_id value returned from the view_user view is
> indeterminate.  The WHERE clause above might be equivalent to
>
> ... WHERE record_id IN (76,86)
>
> and in that case count(*) will return 2.  But the WHERE clause might also
> be
> equivalent to
>
> ... WHERE record_id IN (76,87)
>
> in which case count(*) will return 1.  SQLite is free to choose either
> interpretation for the subquery in your WHERE clause, and hence might get
> either 1 or 2 as the count(*) result.  Version 3.7.2 happened to get 2.
> Version 3.7.3 happens to get 1.  Who knows what 3.7.4 will get - both
> answers are correct
>
>
> > sqlite> select * from test;
> > 4|87|3|4|B
> > 11|76|8|11|A
> > sqlite> select count(*) from test;
> > 1
> > 
> > CREATE TABLE user
> > (
> >  id INTEGER PRIMARY KEY
> > );
> > INSERT INTO "user" VALUES(4);
> > INSERT INTO "user" VALUES(11);
> >
> > CREATE TABLE user_record
> > (
> >  record_id INTEGER PRIMARY KEY,
> >  record_version INTEGER,
> >  user_id INTEGER NOT NULL,
> >  name TEXT
> > );
> > INSERT INTO "user_record" VALUES(76,8,11,'A');
> > INSERT INTO "user_record" VALUES(86,11,4,'B');
> > INSERT INTO "user_record" VALUES(87,3,4,'B');
> >
> > CREATE VIEW view_user AS
> > SELECT user.id,user_record.*
> > FROM user, user_record
> > WHERE user.id=user_record.user_id
> > GROUP BY user.id;
> >
> > create temp view test as select * from main.view_user where record_id in
> > (select record_id from main.view_user where name like '%');
> >
> > select * from test;
> > select count(*) from test;
> > 
> >
> > --
> > Best regards, Alexey Pechnikov.
> > http://pechnikov.tel/
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

[Igor Tandetnik]

Alexey Pechnikov  wrote:
> 2010/10/26 Dan Kennedy 
> 
>> The sorting happens after the grouping. And it is while processing
>> the GROUP BY clause that SQLite is forced to select an arbitrary
>> record from the user_record table.
>> 
>> See here:
>> 
>>   http://www.sqlite.org/lang_select.html#resultset
>> 
>> Third paragraph under the third bullet point.
> 
> 
> No. See paragraph under bullet points: "The list of expressions between the
> SELECT and FROM keywords".

I don't see how this is relevant. ORDER BY doesn't care about the expressions 
you list between SELECT and FROM.

>Sort condition IS NOT the expression from the
> third bullet point.

These bullet points describe which rows are returned by the statement. 
Specifically, you start with "input data from the FROM clause", which is then 
"filtered by the WHERE clause", and finally, in the presence of GROUP BY, "each 
group of input dataset rows contributes a single row to the set of result rows".

ORDER BY describes the order in which those result rows are returned: "if a 
SELECT statement does have an ORDER BY clause, then the list of expressions 
attached to the ORDER BY determine the order in which rows are returned". Note 
again that it works on result rows, not on input rows. In other words, ORDER BY 
is applied *after* GROUP BY. You cannot use ORDER BY clause to influence which 
single result row is chosen as a representative for its group of input rows.

Igor Tandetnik

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

Re: [sqlite] BUG

[Alexey Pechnikov]

Yes, I was wrong. Phisical order of records is equal to the useless sort
condition in the view... Your examples can help me, thanks!

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Simon Slavin]

On 23 Nov 2017, at 12:40am, Ivan De La Cruz  
wrote:

> SQLiteDataAdapater is dropping characters after a space in the field when 
> filling a datatable (c# winforms).
> 
> I.e.
> Field   : 100 
> ml
> Select returns  : 100 
> ml
> DataTable column after Fill : 100
> If I add ' marks around the text: 
> '100 ml'

Is the column defined as numeric ?  If so, then SQLite is doing the right 
thing.  It is parsing the string and retrieving a number from it.

If not, please tell us which versions of which development tools you’re using 
so other people can reproduce your results.

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

Re: [sqlite] Bug

[Marc L. Allen]

I think attachments are dropped. If the SQL is reasonable size, just post it. 

Otherwise, you'll need to host the screen shot somewhere and link to it. 


> On Nov 19, 2014, at 10:00 PM, Josef Handsuch  wrote:
> 
> Dear developer, I'd like to thank you for you brilliant software. There
> just seems to be one little bug to report. If I filter a field that I have
> dedicated to date, the result is always wrong. Please take a look at the
> screenshot (attached) to see what I mean. What you can see there is that I
> was trying to view records that were made on November 19th and there should
> only be 19 records. The progam says there have been made over 150 thousand
> records that day... Whatever date I try to filter, the result is always
> wrong like this.
> Would you please fix this issue?
> Kind regards, Josef
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[GB]

As Marc already stated, this mailing List doesn't allow attachments.

Please also note that SQLite does not support something like a "Date" 
Type. See http://www.sqlite.org/datatype3.html 
.


You may store date attributes in a numeric or TEXT format. See 
http://www.sqlite.org/lang_datefunc.html for more information. 
Personally, I prefer ISO-Strings since they are human readable without 
any transformation.


hth

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

Re: [sqlite] Bug

[Artur Reilin]

> SQLite version 3.7.5
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t1 (a INT);
> sqlite> CREATE TABLE t2 (b INT);
> sqlite> CREATE TABLE t3 (a INT);
> sqlite> SELECT * FROM t1 JOIN t2 ON t1.a = t2.b AND a /* obviously
> t1.a */ != 1 JOIN t3 ON t1.a = t3.a;
> Error: ambiguous column name: a


I think for SQlite it's not that obviously, that t1.a = a. If you join
tables which have the same column names, you need to use the table names.

(that's what i read in the documentation..)

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

Re: [sqlite] Bug

[Jay A. Kreibich]

On Wed, Feb 23, 2011 at 04:24:14AM -0800, Wiktor Adamski scratched on the wall:
> SQLite version 3.7.5
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t1 (a INT);
> sqlite> CREATE TABLE t2 (b INT);
> sqlite> CREATE TABLE t3 (a INT);
> sqlite> SELECT * FROM t1 JOIN t2 ON t1.a = t2.b AND a /* obviously
> t1.a */ != 1 JOIN t3 ON t1.a = t3.a;
> Error: ambiguous column name: a

  Not a bug, not obviously anything, and just the joy of SQL.

  In short, you cannot assume conditions are processed left-to-right,
  including JOIN conditions.  You have two tables in your statement
  with an "a" column, so any reference, anywhere in the statement, must
  be qualified or it will be ambiguous.
  
  In this case the expression "t1.a != 1" would be much more appropriate
  in a WHERE clause, since it is just a row filter and has nothing to do
  with the JOIN itself (it only involves one table, after all).  If we
  rewrite the query with that condition in the WHERE clause, the column
  name is clearly ambiguous (even if the structure of the query tells
  us that all "a" columns must have the same value):

SELECT * 
  FROM   t1 
JOIN t2 ON t1.a = t2.b 
JOIN t3 ON t1.a = t3.a
  WHERE a != 1;  -- which "a"?

  Internally, SQLite actually moves all JOIN conditions to the WHERE
  clause, effectively processing all statement conditions in one batch.
  That means the statement that SQLite is actually processing looks a lot
  more like this:

SELECT * 
  FROM   t1 
JOIN t2
JOIN t3
  WHERE t1.a = t2.b
AND t1.a = t3.a
AND a != 1;  -- which "a"?

  Again, viewed this way, the "a" reference is clearly ambiguous.

  Moving the conditions to the WHERE clause is allowed under the SQL spec,
  as is reordering those conditions, mostly because this is how all JOINs
  used to be written before the "ANSI JOIN syntax" came about (which I
  greatly prefer).

  You can see more evidence of this in a statement like this, which, at
  face value, is even more clear about which "a" you want but still
  throws an "ambiguous column name" error:

SELECT * 
  FROM   t1 
JOIN t2 ON a = b   -- can't figure out which "a" this is.
JOIN t3 ON t1.a = t3.a;

  Once again, things become more clear when you realize the statement
  being processed might look like this:

SELECT * 
  FROM   t1 
JOIN t2
JOIN t3
  WHERE t1.a = t3.a
AND a = b;  -- which "a"?

  While these kinds of issues are the root of many headaches and a lot
  of cursing at the designers of SQL, there are very good reasons for
  this behavior.  First, there are the historical issues in how the
  language has evolved.  It would be very bad to have a query output
  change just because an alternate syntax was used, especially a syntax
  that is supposed to be equivalent.

  But more importantly, moving all the conditions into the WHERE clause
  allows the query optimizer to consider all the different JOINs and
  all the different filter conditions at once.  This allows it to re-order
  conditions, filters and joins.  For example, the query optimizer
  might reorder a series of JOINs based off table size and available
  indexes.  Given the ability of a JOIN to generate a vast number of
  rows, this reordering can have an extremely significant impact on the
  processing time for a query.  So the actual query might join t2 to t3,
  and then add t1 to the mix, especially if the optimizer could guess
  that the output of (t2 JOIN t3) was only a handful of rows, while the
  output of (t1 JOIN t2) might produce a vast number of rows.



  The take-away from all this is, if you ever mix tables with similar
  column names, make sure you always qualify your column references,
  because you really don't know exactly what the query optimizer might
  do with your statement, and it is better to be safe than sorry.  This
  is not unlike using extra ()s in complex math statements, even if the
  language doesn't actually require them.  

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Wiktor Adamski]

> If you join
> tables which have the same column names, you need to use the table names.

You are right, but joined tables don't have the same column names.
SELECT * FROM t1 JOIN t2 ON a = b -- there is only one 'a' and that
select is correctly implemented
SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON 1 -- adding another
select does't change column names in first join
Reported error would be correct in following query:
SELECT * FROM t1 JOIN t2 ON t1.a = t2.b JOIN t3 ON t1.a = t3.a AND a !
= 1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Wiktor Adamski]

>   In short, you cannot assume conditions are processed left-to-right,
>   including JOIN conditions.  

I admit that i haven't checked the snadart but I did check other
engines and they evaluate from left to right. I think that long time
ago I'v read in a standart that 3 table join is basicly equivalent to
joining first 2 tables and than third (I'm not sure though) which
implies left to right.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Jay A. Kreibich]

On Wed, Feb 23, 2011 at 07:21:22AM -0800, Wiktor Adamski scratched on the wall:
> > If you join
> > tables which have the same column names, you need to use the table names.
> 
> You are right, but joined tables don't have the same column names.

  When I say "join tables" I'm referring to the collective output of ALL
  join operations in a statement, not individual JOIN expressions.
  JOINs are associative, so the individual ordering and grouping doesn't
  really matter, only the final result.  So, even in the given
  statements, the query optimizer may join t1 directly to t3, and then
  mix in t2.

  You're assuming "...FROM t1 JOIN t2 ... JOIN t3..." is being
  processed as "... FROM ( ( t1 JOIN t2 ) ... JOIN t3 )..." and that
  assumption is wrong.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Simon Slavin]

On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote:

> sqlite> select 1 from t order by avg(a); -- should be possible

Why should this be possible ?  For an 'ORDER BY' you need a value for each row. 
 But aggregate functions produce only one value for the whole SELECT command.

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

Re: [sqlite] Bug

[Petite Abeille]

On Nov 23, 2011, at 5:17 PM, Wiktor Adamski wrote:

> sqlite> select 1 from t order by avg(a); -- should be possible

> Error: misuse of aggregate: avg();

As it says on the tin: nonsensical.



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

Re: [sqlite] Bug

[Pavel Ivanov]

On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin  wrote:
> On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote:
>
>> sqlite> select 1 from t order by avg(a); -- should be possible
>
> Why should this be possible ?  For an 'ORDER BY' you need a value for each 
> row.  But aggregate functions produce only one value for the whole SELECT 
> command.

My first reaction was the same. But although it's completely senseless
just syntactically it looks correct - should produce just one row and
thus ORDER BY will be a no-op. And FWIW, this query works as expected
on MS SQL (query with GROUP BY 1 doesn't work though).


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

Re: [sqlite] Bug

[Bart Smissaert]

> And FWIW, this query works as expected on MS SQL

Works on Firebird and produces one record with value 1.

RBS


On Wed, Nov 23, 2011 at 4:35 PM, Pavel Ivanov  wrote:
> On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin  wrote:
>> On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote:
>>
>>> sqlite> select 1 from t order by avg(a); -- should be possible
>>
>> Why should this be possible ?  For an 'ORDER BY' you need a value for each 
>> row.  But aggregate functions produce only one value for the whole SELECT 
>> command.
>
> My first reaction was the same. But although it's completely senseless
> just syntactically it looks correct - should produce just one row and
> thus ORDER BY will be a no-op. And FWIW, this query works as expected
> on MS SQL (query with GROUP BY 1 doesn't work though).
>
>
> Pavel
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Petite Abeille]

On Nov 23, 2011, at 5:35 PM, Pavel Ivanov wrote:

> But although it's completely senseless
> just syntactically it looks correct - should produce just one row and
> thus ORDER BY will be a no-op.

Well, if this is about Alice in Wonderland, then, what about:

> select max( 1 ) from t order by avg( a );
1


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

Re: [sqlite] Bug

[Pavel Ivanov]

On Wed, Nov 23, 2011 at 11:50 AM, Petite Abeille
 wrote:
> On Nov 23, 2011, at 5:35 PM, Pavel Ivanov wrote:
>
>> But although it's completely senseless
>> just syntactically it looks correct - should produce just one row and
>> thus ORDER BY will be a no-op.
>
> Well, if this is about Alice in Wonderland, then, what about:
>
>> select max( 1 ) from t order by avg( a );
> 1

Well, apparently you did this on non-empty table. This query gives
different and kind of unexpected result on empty table. ;)


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

Re: [sqlite] Bug

[Petite Abeille]

On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote:

>> Well, if this is about Alice in Wonderland, then, what about:
>> 
>>> select max( 1 ) from t order by avg( a );
>> 1
> 
> Well, apparently you did this on non-empty table. This query gives
> different and kind of unexpected result on empty table. ;)

Well, it returns null, no? What else would you expect?

Getting deeper and deeper in the rabbit hole... :))
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Petite Abeille]

On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote:

>  This query gives
> different and kind of unexpected result on empty table. ;)

Ooops... I see what you mean... on an empty table... this returns one row with 
a null value:

sqlite> select max( 1 ) from t;


That would qualify as a bug I guess :))

[SQLite version 3.7.9 2011-11-01 00:52:41]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Igor Tandetnik]

On 11/23/2011 11:17 AM, Wiktor Adamski wrote:

SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>  create table t(a);
sqlite>  select avg(a) from t order by avg(a); -- order by aggregate
possible

sqlite>  select 1 from t order by a; -- order by column not in result
possible
sqlite>  select 1 from t group by 1 order by avg(a); -- order by
aggregate not in result possible
sqlite>  select 1 from t order by avg(a); -- should be possible
Error: misuse of aggregate: avg()
sqlite>


Using an aggregate function anywhere in the list of selected fields, 
and/or specifying a GROUP BY clause, turns a regular SELECT statement 
into an aggregate one. Apparently, using such a function in ORDER BY 
clause alone doesn't make the statement aggregate (whether it should is 
perhaps debatable), so the condition in ORDER BY applies to each row, 
not to each group. But then, it is an error to use an aggregate function 
there (for the same reason that using one in a WHERE clause is an error).


The other two queries in your example that have ORDER BY avg(a) are made 
in fact aggregate statements, either by using avg() in the SELECT 
clause, or by having GROUP BY.

--
Igor Tandetnik

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

Re: [sqlite] Bug

[Pavel Ivanov]

On Wed, Nov 23, 2011 at 12:20 PM, Petite Abeille
 wrote:
> On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote:
>
>>  This query gives
>> different and kind of unexpected result on empty table. ;)
>
> Ooops... I see what you mean... on an empty table... this returns one row 
> with a null value:
>
> sqlite> select max( 1 ) from t;
>
>
> That would qualify as a bug I guess :))

No, it's not a bug. It's SQL standard that such form of aggregate
query always returns one row. And when there's no rows in the table it
should return NULL (for all aggregate functions except count() which
returns 0). I said it's kind of unexpected because it seems like
max(1) should always return 1 but it turns out it doesn't always do
so.


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

Re: [sqlite] Bug

[Petite Abeille]

On Nov 23, 2011, at 6:28 PM, Pavel Ivanov wrote:

> No, it's not a bug. It's SQL standard that such form of aggregate
> query always returns one row. And when there's no rows in the table it
> should return NULL (for all aggregate functions except count() which
> returns 0). I said it's kind of unexpected because it seems like
> max(1) should always return 1 but it turns out it doesn't always do
> so.

Right you are. Lets forget about this entire nonsense then :)

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

Re: [sqlite] Bug

[Jay A. Kreibich]

On Wed, Nov 23, 2011 at 08:17:17AM -0800, Wiktor Adamski scratched on the wall:
> SQLite version 3.7.9 2011-11-01 00:52:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(a);

> sqlite> select avg(a) from t order by avg(a);
>  -- order by aggregate possible
> sqlite> select 1 from t order by a;
>  -- order by column not in result possible
> sqlite> select 1 from t group by 1 order by avg(a);
>  -- order by aggregate not in result possible
> sqlite> select 1 from t order by avg(a);
>  -- should be possible
> Error: misuse of aggregate: avg()


  Not a bug.  As documented (kind of):

  http://sqlite.org/lang_select.html#resultset

  Also a non-sense query.

  The core issue is when an aggregate can be used without a GROUP BY
  clause.  Normally the use of an aggregate requires a GROUP BY clause
  so it knows *what* to aggregate.  There is a special exception that
  if an aggregate appears IN THE RESULT SET, then an implied group is
  made over the whole data set.
  
  However, any query with an implied group will always return exactly
  one row.   This makes any kind of ORDER BY statement meaningless.  In
  the case of the last query, the ORDER BY is doubly meaningless, as
  your asking the result to be ordered by an expression that will
  always be the same for every row.

  In the first query, there is an aggregate in the result set, so an
  implicit GROUP BY is used.  The ORDER BY is meaningless, but not an
  error (and could be more easily written "ORDER BY 1"; see below).

  The second query has no aggregates.

  The third query has an explicit GROUP BY, allowing aggregate
  expressions.

  The final expression has an aggregate, but no GROUP BY, nor any
  aggregate in the result set.  It is an invalid query, which is fine,
  because it is also a nonsense query.  Get rid of the ORDER BY
  statement and you'll get the same single-row result.


  It should also be pointed out that the third query is likely not
  doing what you think it is doing.  In this specific case, the GROUP
  BY 1 will provide the same result as an implied, whole-dataset
  grouping.  That is not because of the "1" in the GROUP BY clause,
  however, but the "1" in the SELECT clause.  Literal integers passed
  to GROUP BY or ORDER BY are assumed to be column indexes, not literal
  values.  "GROUP BY 1" means "group by the first column", not "group
  by the literal value '1'". 
  
  Now it happens that in this specific query the first column is a literal
  value '1', resulting in a GROUP BY with a static, literal value.  That,
  in turn, results in a single group across all rows, returning exactly
  one row, just as an implied GROUP BY does.  This is not universally
  true, however.  You have no idea how many rows a "GROUP BY 1"
  statement may return unless you know the expression used to compute
  the first column of the result set.
  
   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Wiktor Adamski]

>   In the first query, there is an aggregate in the result set, so an
>   implicit GROUP BY is used.  The ORDER BY is meaningless, but not an
>   error (and could be more easily written "ORDER BY 1"; see below).

The order is not meaningless. It can return an error or do nothing. If
aggregate in order by isn't allowed it should return an error. From
the first query we can see that it is allowed. It would be nice if
database behaved consistently. We already know that MS SQL and
Firebird do. Same with MySQL. I'm not sure, but I think I'v tried also
postgres with same result. I'm sure that in sqlite it was missed and
it is not an intentional behaviour.
And yes - obviously the query with a bug makes no sense and there is
no reason to ever use it. Just like adding order by to any other query
that return 1 row before ordering.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Wiktor Adamski]

> Apparently, using such a function in ORDER BY
> clause alone doesn't make the statement aggregate (whether it should is
> perhaps debatable)

I suppose this may be in the standart. I'm 100% sure that this one is
allowed by standart:
... ORDER BY avg(a) OVER()
so likely ORDER BY avg(a) is also allowed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Igor Tandetnik]

On 11/23/2011 1:29 PM, Wiktor Adamski wrote:

Apparently, using such a function in ORDER BY
clause alone doesn't make the statement aggregate (whether it should is
perhaps debatable)


I suppose this may be in the standart.


If I recall correctly, the standard doesn't allow ORDER BY to reference 
anything that doesn't also appear in SELECT, which renders the issue moot.

--
Igor Tandetnik

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

Re: [sqlite] Bug

[Pavel Ivanov]

On Wed, Nov 23, 2011 at 1:29 PM, Wiktor Adamski
 wrote:
>> Apparently, using such a function in ORDER BY
>> clause alone doesn't make the statement aggregate (whether it should is
>> perhaps debatable)
>
> I suppose this may be in the standart. I'm 100% sure that this one is
> allowed by standart:
> ... ORDER BY avg(a) OVER()
> so likely ORDER BY avg(a) is also allowed.

I believe OVER() is an Oracle-specific extension to SQL, not a
standard in any way.


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

Re: [sqlite] Bug

[Petite Abeille]

On Nov 23, 2011, at 7:58 PM, Pavel Ivanov wrote:

> I believe OVER() is an Oracle-specific extension to SQL, not a
> standard in any way.

Well, over( partition by... order by ... ) is part of the analytical syntax of 
Oracle... nothing to do with ordering a result set...

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <>] 
[] )

http://orafaq.com/node/55

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

Re: [sqlite] Bug

[Wiktor Adamski]

> I believe OVER() is an Oracle-specific extension to SQL, not a
> standard in any way.

ISO/IEC 9075-2:2003:
 ::=  OVER 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Petite Abeille]

On Nov 23, 2011, at 8:50 PM, Wiktor Adamski wrote:

> ISO/IEC 9075-2:2003:
>  ::=  OVER  specification>

This is related to so-called analytics in Oracle parlance. Not quite related to 
the topic at hand.

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

Re: [sqlite] Bug

[Jay A. Kreibich]

On Wed, Nov 23, 2011 at 10:24:12AM -0800, Wiktor Adamski scratched on the wall:
> > ? In the first query, there is an aggregate in the result set, so an
> > ? implicit GROUP BY is used. ?The ORDER BY is meaningless, but not an
> > ? error (and could be more easily written "ORDER BY 1"; see below).
> 
> The order is not meaningless. 

  In your last example, you did not have an explicit GROUP BY clause.
  You were expecting an implicit GROUP BY, which *always* returns
  exactly one row-- even for an empty table (as your earlier example
  showed-- that blank line is a one-row NULL result).  
  
  I would assert attempting to order a one-row result is a meaningless
  operation.

  I'm specifically using the word "meaningless", and not "incorrect."
  It isn't an error, it just has no bearing on the end result.

  Ordering a multi-row result does have meaning-- including ordering by
  the result of an aggregate function.  And this is allowed.  However,
  the only way to get a multi-row grouped result is with an explicit
  GROUP BY.  If the statement had an explicit GROUP BY, the aggregate
  in the ORDER BY would not be an error.

> aggregate in order by isn't allowed it should return an error.

  It *is* allowed.  The error is not the fact there was an aggregate
  in an ORDER BY.  The error is that there was an aggregate in a query
  that had no groups.  This whole issue is about when an implicit group
  is created, and when it is not.  It is not about the ability to put
  an aggregate into an ORDER BY-- that is absolutely allowed.

> It would be nice if database behaved consistently. 

  It does.  The behavior is easy to define.  A query with any type of
  aggregate requires groups.  You can create groups with an explicit
  GROUP BY statement **OR** you can have the database create an
  implicit GROUP BY over the entire result set.  However, an implicit
  GROUP BY will only be created if there is an aggregate function in
  the result set (that is, between SELECT and FROM).

  Aggregates *are* allowed in non-result expressions, such as ORDER BY
  (as your examples show).  However, this is only allowed if there is
  an explicit GROUP BY clause in the query, as a lone aggregate in a
  non-result expression will not trigger an implicit GROUP BY.  While
  the docs URL I sent out before doesn't spell it out quite that
  explicitly, it seems clear enough to me.

  Your last example has no explicit GROUP BY, nor does it have any
  aggregate in the result set.  Therefore, the query has no groups,
  and attempting to use an aggregate function in a query without any
  groups is an error-- as reported. 

  However, as your other examples show, any explicit GROUP BY statement 
  will allow the aggregate to be processed correctly.  This includes a
  trivial GROUP BY statement, such as "GROUP BY 1=1", which is (almost)
  equivalent to an implicit whole-result GROUP BY ("GROUP BY 1" alone
  is not always a trivial grouping due to the indexing short-cut). 
  
  Of course, having a trivial grouping across the whole result set will
  never return more than one row (unlike an implicit grouping, an empty
  table will return no rows on an empty table), which brings us back to
  the wisdom of trying to order one row.

> We already know that MS SQL and Firebird do. Same with MySQL.
> I'm not sure, but I think I'v tried also postgres with same result.

  That might be true, but I'm not sure that's a very strong point. 
  Unless someone can point out a standards issue, just because other
  databases do it one way doesn't make them any more right or wrong.

  And yes, PostgreSQL will trigger an implicit GROUP BY if an aggregate
  function appears anywhere in the query, not just in the result set.

> I'm sure that in sqlite it was missed and
> it is not an intentional behaviour.

  Given the way the docs are phrased, I'm sure it is intentional.  I
  have no idea what the SQL standard says (my guess is that it doesn't,
  and this is a gray area), but I understand the logic behind the
  decision.

  Triggering (or not) an automatic grouping by a non-result aggregate
  is one of those things that doesn't strike me as "right" or "wrong",
  it simply is a question of if you want to allow nonsense queries
  that aren't technically "wrong", but don't make a lot of sense, of if
  you want to guide the user away from queries that are very likely not
  doing what the user thinks they are doing.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug help

[Dennis Cote]

[EMAIL PROTECTED] wrote:
> Is this the correct list for airing SQLite amalgamation compilation problems?
> 

Yes.

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

Re: [sqlite] bug help

[Regnirps]

I tried to post this once and didn't see it.   Is there a restriction on long 
listings? I'll cut off most of it this time.

Hi all. I'm new to SQLite. It was recommended by a friend who uses it on 
PDA's and cell phones. I am trying to compile the Amalgamation on a version of 
gcc 
set up for ARM (devkitpro). I wasn't expecting a bunch of errors from the 
Amalgamation since no header files are needed or other usual sources of 
problems.

Any ideas on where to start?

Thanks,
Charlie Springer

sqlite3.c
c:/devkitpro/ident/helloworld/source/sqlite3.c:13259: error: expected 
specifier-qualifier-list before 'pthread_mutex_t'
c:/devkitpro/ident/helloworld/source/sqlite3.c: In function 
'sqlite3_mutex_alloc':
c:/devkitpro/ident/helloworld/source/sqlite3.c:13316: error: 
'PTHREAD_MUTEX_INITIALIZER' undeclared (first use in this function)
c:/devkitpro/ident/helloworld/source/sqlite3.c:13316: error: (Each undeclared 
identifier is reported only once
c:/devkitpro/ident/helloworld/source/sqlite3.c:13316: error: for each 
function it appears in.)
c:/devkitpro/ident/helloworld/source/sqlite3.c:13316: warning: excess 
elements in struct initializer
c:/devkitpro/ident/helloworld/source/sqlite3.c:13316: warning: (near 
initialization for 'staticMutexes[0]')
c:/devkitpro/ident/helloworld/source/sqlite3.c:13316: warning: excess 
elements in struct initializer
c:/devkitpro/ident/helloworld/source/sqlite3.c:13316: warning: (near 
initialization for 'staticMutexes[0]')
etc.


**
Gas prices getting you down? Search AOL Autos for 
fuel-efficient used cars.
  (http://autos.aol.com/used?ncid=aolaut000507)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug help

[Jay A. Kreibich]

On Wed, Jun 25, 2008 at 08:19:35PM -0400, [EMAIL PROTECTED] scratched on the 
wall:
> I tried to post this once and didn't see it.   Is there a restriction on long 
> listings? I'll cut off most of it this time.
> 
> Hi all. I'm new to SQLite. It was recommended by a friend who uses it on 
> PDA's and cell phones. I am trying to compile the Amalgamation on a version 
> of gcc 
> set up for ARM (devkitpro). I wasn't expecting a bunch of errors from the 
> Amalgamation since no header files are needed or other usual sources of 
> problems.
> 
> Any ideas on where to start?

  It looks like you changed some of the build flags.  That doesn't work
  with the amalgamation.  If you need to alter the build flags you need
  to build from source OR re-build the amalgamation from a full source set.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG - Documentation

[Simon Slavin]

On 15 Jan 2010, at 12:29am, Dennis Cote wrote:

> "Disability the mutexes as compile-time is a recommended optimization 
> for applications were it makes sense."
> 
> I think it should be changed to:
> 
> "Disabling the mutexes at compile-time is a recommended optimization for 
> applications were it makes sense."

s/were/where

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

Re: [sqlite] BUG - Documentation

[Clark Christensen]

Perhaps "...to whole underlying operating system..." should be "...to the whole 
underlying operating system..."


- Original Message 
From: Dennis Cote 
To: General Discussion of SQLite Database 
Sent: Thu, January 14, 2010 4:38:48 PM
Subject: [sqlite] BUG - Documentation

On the website page at http://www.sqlite.org/custombuild.html

The following sentence appears:

"This object is somewhat misnamed since it is really an interface to 
whole underlying operating system, just the filesystem."

I think it should be changed to:

"This object is somewhat misnamed since it is really an interface to 
whole underlying operating system, *NOT* just the filesystem."

Or something similar. Maybe the NOT doesn't need that much emphasis, but 
it should be there.

HTH
Dennis Cote

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

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

Re: [sqlite] Bug Report

[Dan Kennedy]

> RecNo cid name   typenotnull dflt_value pk
> - --- -- --- --- -- --
>1 0   ID CHAR(32)0   (null) 0
>2 1   Active BOOLEAN 0   (null) 0
>3 2   Name   VARCHAR(64) 0   (null) 0
>4 3   PasswordExpiration INTEGER 0   (null) 0
>5 4   CreatedDATE0   (null) 0
>6 5   Modified   DATE0   (null) 0
>7 6   UserCount  0   (null) 0
>
> Notice that UserCount (my sub-query) does not return a data type.  
> Even with
> CAST(subquery AS INTEGER) does not work =(

The problem is that CAST(...) casts the type of the value. Does not  
affect
for the declared type of the column.



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

Re: [sqlite] Bug Report

[Júlio César Ködel]

CAST was just an attempt to workaround the problem...
I just need a way to know what type is "UserCount"... in the query, it is an
Integer (because it is a aggregated COUNT function).
If SQLite is unable to determine it's type, at least pragma table_info
should then get it from a CAST =\

On Sat, Jan 30, 2010 at 2:23 PM, Dan Kennedy  wrote:

> > RecNo cid name   typenotnull dflt_value pk
> > - --- -- --- --- -- --
> >1 0   ID CHAR(32)0   (null) 0
> >2 1   Active BOOLEAN 0   (null) 0
> >3 2   Name   VARCHAR(64) 0   (null) 0
> >4 3   PasswordExpiration INTEGER 0   (null) 0
> >5 4   CreatedDATE0   (null) 0
> >6 5   Modified   DATE0   (null) 0
> >7 6   UserCount  0   (null) 0
> >
> > Notice that UserCount (my sub-query) does not return a data type.
> > Even with
> > CAST(subquery AS INTEGER) does not work =(
>
> The problem is that CAST(...) casts the type of the value. Does not
> affect
> for the declared type of the column.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
[]
Júlio César Ködel G.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Re: SQLite bug ?

[D. Richard Hipp]

fab wrote:
Hi!
I have a field declared as:
  tel varchar(20) NOT NULL default '',
which owns a serial number, under 2.8.14 I get the same I entered in the
database but with the last 3.x it removes the heading '0' if there is
one.
Works for me:
  [EMAIL PROTECTED] bld]$ ./sqlite3 test.db
  SQLite version 3.0.2
  Enter ".help" for instructions
  sqlite> create table t1(tel varchar(20) not null default '');
  sqlite> insert into t1 values('0001234');
  sqlite> select * from t1;
  0001234
Perhaps you could be more specific about your problem.  You should
also post this to the mailing list.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Bug report

[Richard Hipp]

On 2/27/18, Alexander Ananin  wrote:
>
> I've found the strange behavior in the rtree.c file.

Are you trying to compile rtree.c separately, rather than using the
version that is bundled into the sqlite3.c amalgmation?  May I ask why
you want to do that?
-- 
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] BUG REPORT

[Richard Hipp]

On 8/15/18, Mr Max  wrote:
> When running this with the  ODBC driver it fails to return all the
> appropriate record in the range. I tried the exact same query in a DB
> Browser for Sqlite and it recovers 127 records only.

What answer do you get when you run your query using the
officially-supported "sqlite3.exe" command-line tool available from
(https://www.sqlite.org/download.html)?

The "DB Browser for SQLite" is a third-party tool, about which I know
very little.
-- 
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] BUG REPORT

[Chris Locke]

> I am using a query to check a date field between a range of dates

Can you provide example values of the date in your database?
Are you storing the EXACT date (eg, '2018-02-01 12:21'), or just the date?

> When running this with the  ODBC driver it fails to return all the
appropriate record in the range. I tried the exact same query in a
> DB Browser for Sqlite and it recovers 127 records only.

How many records were returned with the ODBC driver?



On Wed, Aug 15, 2018 at 10:16 AM Mr Max  wrote:

> To whom it may concern,
>
>
>
>
>
> Whilst using an ODBC driver for SQLite acquired from:
>
>
>
> http://www.ch-werner.de/sqliteodbc/
>
>
>
> I came across a potential bug in SQLite.
>
>
>
> I have an application running VB.NET on a Windows 7 32-bit machine and
> have
> installed the sqliteodbc.exe from the website above. I am using a query to
> check a date field between a range of dates, the exact query being:
>
>
>
> SELECT ind.CUSTOMERU, ind.XTRANU, ind.DDATE, SUM(ind.DAMOUNT) as REVENUE,
> MIN(inc.SURNAME) as CNAME
>
> FROM INV_DETAIL ind inner JOIN CUSTOMER inc ON ind.CUSTOMERU=inc.UNIQ
> WHERE
> ind.DDATE BETWEEN '2018-02-01' AND '2018-02-28' AND ind.DTYPE='3'
>
> AND ind.DAMOUNT<0 AND ind.SUBCONTRU<>'666' AND ind.SUBCONTRU<>'555' GROUP
> BY
> ind.CUSTOMERU, ind.XTRANU ORDER BY ind.CUSTOMERU, ind.XTRANU;
>
>
>
>
>
> When running this with the  ODBC driver it fails to return all the
> appropriate record in the range. I tried the exact same query in a DB
> Browser for Sqlite and it recovers 127 records only.
>
> I have run the same query using ODBC and Access (office 2003) and it
> recovers 138 records. Doing a manual filter of the records from the
> INV_DETAIL table I can extract 138 records!!
>
> The records  apparently omitted by SQLite are one with DDATE equal to the
> start date of 2018-02-01. If I make the start date one day earlier the
> SQLite query returns 138 records!!
>
>
>
> I have attached a spreadsheet with the data from the report I am generating
> and with the INV_DETAIL data for the whole month of Feb 2018.
>
>
>
>
>
> Regards
>
> Bob Maxwell
>
>
>
> ___
> 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] Bug report

[Igor Tandetnik]

Radovan Antloga  wrote:
> create table test (a integer not null, b float not null);
> 
> update test
> set b = 0.0 / 0;
> 
> you will get error: test.b may not be null

Appears to be working as intended. Division by zero produces null, which cannot 
be stored in test.b . What exactly seems to be the problem?
-- 
Igor Tandetnik

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

Re: [sqlite] Bug report

[Radovan Antloga]

S, Igor Tandetnik piše:
> Radovan Antloga  wrote:
>> create table test (a integer not null, b float not null);
>>
>> update test
>> set b = 0.0 / 0;
>>
>> you will get error: test.b may not be null
> Appears to be working as intended. Division by zero produces null, which 
> cannot be stored in test.b . What exactly seems to be the problem?

Oh I see sqlite allows division by zero but result
is null. Sorry I didn't expect it. Some other DB
gives division by zero error in this case.

But actually I like sqlite way more. I will fix some
of my sql-s to use this sqlite feature :-).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug reports

[Pavel Ivanov]

> I did try sending a mail over the dev-list, but got no reply. Could
> someone tell me how it should be done?

You should send it here with the exact steps of reproducing the bug.

Hopefully your tool is not code analyzer because "potential bugs"
without any real life steps to catch it won't be paid attention to.


Pavel


On Thu, Jun 2, 2011 at 9:40 AM, Ronald Burgman
 wrote:
> Hi everybody,
>
> I'm currently using a tool to find bugs in the SQLite source code. I've
> found some interesting things and want to submit some bug reports so the
> developers can also have a look at it.
>
> However I'm new to SQLite so I'm not sure where and how to submit these
> reports. I did try sending a mail over the dev-list, but got no reply. Could
> someone tell me how it should be done?
>
> Cheers,
> Ronald
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug reports

[Simon Slavin]

On 2 Jun 2011, at 2:40pm, Ronald Burgman wrote:

> I'm currently using a tool to find bugs in the SQLite source code. I've
> found some interesting things and want to submit some bug reports so the
> developers can also have a look at it.

Report them here.

Although you may be using a tool to /find/ the bugs, the authors will 
completely mistrust the tool.  So you must reproduce the bug yourself: show us 
some source code, and what SQLite does with it, and what you expected it to do 
instead.

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

Re: [sqlite] Bug Report

[Richard Hipp]

On 2/20/19, William ESCANDE  wrote:
> Hi everyone !
>
> TL;DR:
> with sanitizer
> in func columnName (l.82210)
> calling xFunc(l.82235) trigger a cfi_check (and then ABORT)
>
> Fix to do :
> change prototype of sqlite3_value_text to let him return a `void *`

There are millions and millions of applications that are coded to the
existing API, so we cannot change APIs.

I rewrote columnName() to completely avoid the use of function
pointers.  Perhaps this will appease your compiler.
-- 
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] Bug report

[Kees Nuyt]

On Tue, 19 Nov 2019 00:19:13 -0500, you wrote:

> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU.
> We found 7 crashes for sqlite of  the newest commit
> 3842e8f166e23a1ed6e6094105e7a23502d414da. 
> We have attached the samples that crash sqlite in the email. 

The mailing list strips attachemnts. Please insert them in the body text of your
message, or mail them to Richard Hipp.

> FYI, we have also reported the bugs for CVE
> at cve.mitre.org . 

Can you tell us the CVE nunber?


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Bug report

[Jose Isaias Cabrera]

NameDescription
CVE-2019-9937<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-9937>  
In SQLite 3.27.2, interleaving reads and writes in a single transaction with an 
fts5 virtual table will lead to a NULL Pointer Dereference in fts5ChunkIterate 
in sqlite3.c. This is related to ext/fts5/fts5_hash.c and ext/fts5/fts5_index.c.
CVE-2019-9936<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-9936>  
In SQLite 3.27.2, running fts5 prefix queries inside a transaction could 
trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, which 
may lead to an information leak. This is related to ext/fts5/fts5_hash.c.
CVE-2019-5827<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-5827>  
Integer overflow in SQLite via WebSQL in Google Chrome prior to 74.0.3729.131 
allowed a remote attacker to potentially exploit heap corruption via a crafted 
HTML page.
CVE-2019-3784<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-3784>  
Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session 
that can be spoofed. When deployed on cloud foundry with multiple instances 
using the default embedded SQLite database, a remote authenticated malicious 
user can switch sessions to another user with the same session id.
CVE-2019-16168<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-16168>
In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a 
browser or other application because of missing validation of a sqlite_stat1 sz 
field, aka a "severe division by zero in the query planner."
CVE-2019-10752<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-10752>
Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to 
SQL Injection due to sequelize.json() helper function not escaping values 
properly when formatting sub paths for JSON queries for MySQL, MariaDB and 
SQLite.
CVE-2018-8740<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-8740>  
In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE 
TABLE AS statement could cause a NULL pointer dereference, related to build.c 
and prepare.c.
CVE-2018-7774<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-7774>  
The vulnerability exists within processing of localize.php in Schneider 
Electric U.motion Builder software versions prior to v1.3.4. The underlying 
SQLite database query is subject to SQL injection on the username input 
parameter.



From: sqlite-users  on behalf of 
Kees Nuyt 
Sent: Thursday, November 21, 2019 09:51 AM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Bug report

On Tue, 19 Nov 2019 00:19:13 -0500, you wrote:

> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU.
> We found 7 crashes for sqlite of  the newest commit
> 3842e8f166e23a1ed6e6094105e7a23502d414da.
> We have attached the samples that crash sqlite in the email.

The mailing list strips attachemnts. Please insert them in the body text of your
message, or mail them to Richard Hipp.

> FYI, we have also reported the bugs for CVE
> at cve.mitre.org <http://cve.mitre.org/>.

Can you tell us the CVE nunber?


--
Regards,

Kees Nuyt

___
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] Bug report

[Richard Hipp]

On 11/19/19, Yongheng Chen  wrote:
> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU. We found 7 crashes
> for sqlite of  the newest commit 3842e8f166e23a1ed6e6094105e7a23502d414da.
> We have attached the samples that crash sqlite in the email. FYI, we have
> also reported the bugs for CVE at cve.mitre.org .

There were just two bugs, both related to the new (unreleased)
generated column feature.  Both have now been fixed on trunk.  Thank
you for the bug reports.

In as much as these problems have never appeared in a released version
of SQLite, I think a CVE would be inappropriate.  But I don't really
understand CVEs so perhaps I am wrong.

Please consider following SQLite development on the official
source-code repository.  You can see the latest changes here:

https://sqlite.org/src/timeline

If you click on any of the check-in hashes, that will take you to a
page that contains links to download tarballs and/or ZIP archives of
the latest code.  Or you can use Fossil to clone the repository.  See
https://www.sqlite.org/getthecode.html for additional information
about how to get the official SQLite source code.

The filenames of your test cases suggest that they were generated by
AFL.  How did you find these issues?  Do you have new and enhanced AFL
fuzzer, perhaps one in which you have replaced the default mutator
with an SQL-language generator?  Can you tell us more about your new
fuzzer?

-- 
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] Bug report

[Kees Nuyt]

Thanks, Jose.

I see no CVE entered by the OP, but maybe I missed something.

A quick look to your list :

> NameDescription
> CVE-2019-9937 
> In SQLite 3.27.2, interleaving reads and writes in a single transaction with
> an fts5 virtual table will lead to a NULL Pointer Dereference in
> fts5ChunkIterate in sqlite3.c. This is related to ext/fts5/fts5_hash.c and
> ext/fts5/fts5_index.c.

Resolved 2019-03-18


> CVE-2019-9936 
> In SQLite 3.27.2, running fts5 prefix queries inside a transaction could
> trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, which
> may lead to an information leak. This is related to ext/fts5/fts5_hash.c.

Resolved 2019-03-18


> CVE-2019-5827 
> Integer overflow in SQLite via WebSQL in Google Chrome prior to 74.0.3729.131
> allowed a remote attacker to potentially exploit heap corruption via a crafted
> HTML page.

Resolved 2019-04-13


> CVE-2019-3784 
> Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session
> that can be spoofed. When deployed on cloud foundry with multiple instances
> using the default embedded SQLite database, a remote authenticated malicious
> user can switch sessions to another user with the same session id.

Application error


> CVE-2019-1616 8
> In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a
> browser or other application because of missing validation of a sqlite_stat1
> sz field, aka a "severe division by zero in the query planner."

Resolved 2019-08-15


> CVE-2019-1075 2
> Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to
> SQL Injection due to sequelize.json() helper function not escaping values
> properly when formatting sub paths for JSON queries for MySQL, MariaDB and
> SQLite.

Application error


> CVE-2018-8740 
> In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE
> TABLE AS statement could cause a NULL pointer dereference, related to build.c
> and prepare.c.

Resolved 2018-03-16


> CVE-2018-7774 
> The vulnerability exists within processing of localize.php in Schneider
> Electric U.motion Builder software versions prior to v1.3.4. The underlying
> SQLite database query is subject to SQL injection on the username input
> parameter.

Application error


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

Re: [sqlite] Bug report

[Jose Isaias Cabrera]

Kees Nuyt, on Thursday, November 21, 2019 03:48 PM, wrote...
>
>
> Thanks, Jose.
>
> I see no CVE entered by the OP, but maybe I missed something.

Yes, you are right.  After pasting it, I went through the top 5 and none of 
these aren't/weren't the one. Apologies.  I thought that by searching on sqlite 
the top 5 or so would be the one that was just opened, but for some reason, it 
was not.  Sorry about that.  Fast fingers Jose.

josé

> A quick look to your list :
>
> > NameDescription
> > CVE-2019-9937, on
> > In SQLite 3.27.2, interleaving reads and writes in a single transaction with
> > an fts5 virtual table will lead to a NULL Pointer Dereference in
> > fts5ChunkIterate in sqlite3.c. This is related to ext/fts5/fts5_hash.c and
> > ext/fts5/fts5_index.c.
>
> Resolved 2019-03-18
>
>
> > CVE-2019-9936, on
> > In SQLite 3.27.2, running fts5 prefix queries inside a transaction could
> > trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, 
> > which
> > may lead to an information leak. This is related to ext/fts5/fts5_hash.c.
>
> Resolved 2019-03-18
>
>
> > CVE-2019-5827, on
> > Integer overflow in SQLite via WebSQL in Google Chrome prior to 
> > 74.0.3729.131
> > allowed a remote attacker to potentially exploit heap corruption via a 
> > crafted
> > HTML page.
>
> Resolved 2019-04-13
>
>
> > CVE-2019-3784, on
> > Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session
> > that can be spoofed. When deployed on cloud foundry with multiple instances
> > using the default embedded SQLite database, a remote authenticated malicious
> > user can switch sessions to another user with the same session id.
>
> Application error
>
>
> > CVE-2019-1616 
> > 8
> > In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a
> > browser or other application because of missing validation of a sqlite_stat1
> > sz field, aka a "severe division by zero in the query planner."
>
> Resolved 2019-08-15
>
>
> > CVE-2019-1075 
> > 2
> > Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to
> > SQL Injection due to sequelize.json() helper function not escaping values
> > properly when formatting sub paths for JSON queries for MySQL, MariaDB and
> > SQLite.
>
> Application error
>
>
> > CVE-2018-8740, on
> > In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE
> > TABLE AS statement could cause a NULL pointer dereference, related to 
> > build.c
> > and prepare.c.
>
> Resolved 2018-03-16
>
>
> > CVE-2018-7774, on
> > The vulnerability exists within processing of localize.php in Schneider
> > Electric U.motion Builder software versions prior to v1.3.4. The underlying
> > SQLite database query is subject to SQL injection on the username input
> > parameter.
>
> Application error
>
>
> --
> Regards,
> Kees Nuyt
> ___
> 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] Bug report

[Kees Nuyt]

On Thu, 21 Nov 2019 21:02:57 +, Jose Isaias Cabrera wrote:

>Kees Nuyt, on Thursday, November 21, 2019 03:48 PM, wrote...
[...]
>>
>> I see no CVE entered by the OP, but maybe I missed something.
>
> Yes, you are right.  After pasting it, I went through the top 5
> and none of these aren't/weren't the one. Apologies. 
> I thought that by searching on sqlite the top 5 or so
> would be the one that was just opened, but for some reason,
> it was not.  Sorry about that.  Fast fingers Jose.

No problem!
We'll wait for more input from the OP.

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

Re: [sqlite] Bug report

[Simon Slavin]

CVE will not record this bug if it doesn't affect a /released/ version of any 
product.  One hopes that none of the products which incorporate SQLite would 
incorporate a version of SQLite which never received a release number.

In other words, the reporters told the developer team before the bug became a 
problem.  Very good.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report

[Igor Korot]

Hi,

On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do  wrote:
>
> Dear sqlite developers:
>
> We met an accidental crash in sqlite with the following sample:
>
> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) 
> SELECT col2 FROM table1 ORDER BY 1 ;
> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY 
> col1 DESC ) FROM table1 ;

Could you please provide the schema for table1?

Thank you.

>
>
> We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10 
> 20:19:45`
>
> Thanks
>
> Ming Jia
> ___
> 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] Bug Report

[Keith Medcalf]

On Friday, 27 December, 2019 12:50, Igor Korot  wrote:

>On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do  wrote:

>> We met an accidental crash in sqlite with the following sample:

>> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) 
>> SELECT col2 FROM table1 ORDER BY 1 ;
>> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY 
>> col1 DESC ) FROM table1 ;

>Could you please provide the schema for table1?

table1 is a circular view ... that is table1 is a view that tries to select 
from table1 which is a view which selects from table1 which is a view which 
selects from table1 ... until eventually all memory and stack is consumed and 
sqlite crashes.

-- 
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] Bug Report

[Bigthing Do]

Hi,

We tried debugging a little bit with the core dump, it crashes with a null 
reference actually:

`
Program received signal SIGSEGV, Segmentation fault.
[--registers---]
RAX: 0x74 ('t')
RBX: 0x782550 --> 0x76b088 --> 0x1
RCX: 0x61 ('a')
RDX: 0x0
RSI: 0x0
RDI: 0x782098 --> 0x31656c626174 ('table1')
RBP: 0x782548 --> 0x10001
RSP: 0x7fffb6b0 --> 0x78d1b0 --> 0x78d1e8 --> 0x50804496
RIP: 0x4b4237 (:movzx  ecx,BYTE PTR [rdx+rsi*1])
R8 : 0x77d0e8 --> 0x1
R9 : 0x0
R10: 0x77d0f8 --> 0x0
R11: 0x0
R12: 0x1
R13: 0x7fffc680 --> 0x76a9b8 --> 0x73c300 --> 0x780003
R14: 0x7fffc680 --> 0x76a9b8 --> 0x73c300 --> 0x780003
R15: 0x0
EFLAGS: 0x10246 (carry PARITY adjust ZERO sign trap INTERRUPT direction 
overflow)
[-code-]
   0x4b422d :   jne0x4b4270 
   0x4b422f :   addrsi,0x1
   0x4b4233 :   movzx  eax,BYTE PTR [rdi+rsi*1]
=> 0x4b4237 :   movzx  ecx,BYTE PTR [rdx+rsi*1]
`

We got the same result if we debug with address sanitizer, not an out of memory 
error.


Thanks,
Ming Jia

> On Dec 27, 2019, at 2:56 PM, Keith Medcalf  wrote:
> 
> 
> On Friday, 27 December, 2019 12:50, Igor Korot  wrote:
> 
>> On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do  wrote:
> 
>>> We met an accidental crash in sqlite with the following sample:
> 
>>> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) 
>>> SELECT col2 FROM table1 ORDER BY 1 ;
>>> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY 
>>> col1 DESC ) FROM table1 ;
> 
>> Could you please provide the schema for table1?
> 
> table1 is a circular view ... that is table1 is a view that tries to select 
> from table1 which is a view which selects from table1 which is a view which 
> selects from table1 ... until eventually all memory and stack is consumed and 
> sqlite crashes.
> 
> -- 
> 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

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

Re: [sqlite] Bug Report

[Jose Isaias Cabrera]

Bigthing Do, on Friday, December 27, 2019 01:56 PM, wrote...
>
> Dear sqlite developers:
>
> We met an accidental crash in sqlite with the following sample:
>
> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1
> ) SELECT col2 FROM table1 ORDER BY 1 ;
> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY
> col1 DESC ) FROM table1 ;
>
>
> We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10
> 20:19:45`

Also with 3.30.0...

16:41:27.70>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM 
table1 ) SELECT col2 FROM table1 ORDER BY 1 ;
sqlite> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER 
BY col1 DESC ) FROM table1 ;

16:42:07.53>

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

Re: [sqlite] Bug report

[Mark Benningfield]

Well, I kinda thought that this would be fixed on the next release. The
"value_frombind" typo in particular prevents FTS3/4 from being built as a
loadable extension. I only have one legacy application that uses FTS3/4 that
way, and fixing these typos whenever I do a Fossil pull of the latest
version takes a grand total of about 2 seconds, but it would be nice not to
have to remember to do it every time :)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report

[Warren Young]

On Jan 23, 2020, at 7:02 AM, Mark Benningfield  wrote:
> 
> ...whenever I do a Fossil pull of the latest
> version takes a grand total of about 2 seconds, but it would be nice not to
> have to remember to do it every time :)

If you’re having to reapply the change on every Fossil update, you’re probably 
making the change to the wrong place in the code: you’re changing a generated 
file rather than a proper source file.

Saying “fossil up” or “fossil up release” should merge your local edits into 
the new release automatically unless upstream changes something nearby or on 
those same lines.

I don’t say this expecting that these problems will remain unfixed upstream, 
just as general forward-looking advice.  Fossil can be a useful aide in 
carrying local changes from one release to the next.

There are more advanced methods beyond that, such as private branches and 
autosync=0, but at that point we should take it up on the Fossil forum.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report.

[Richard Hipp]

On Fri, Oct 11, 2013 at 7:57 AM, Alexander Syvak wrote:

> Hello,
>
> I am using the @ as a column name delimeter. For instance, if there's a
> table named woman, then a column describing the size of hips should be
> named woman@hip_size.
>
> There's a table created using
> CREATE TABLE country
> (
> 'country@id' INTEGER,
> 'country@name' TEXT,
> 'country@printable'_name TEXT,
>

Either quote the entire name or none of it.  The fact that you ended the
quote mid-name causes the name to be parsed as two separate tokens:

  "country@printable"  _name  TEXT,

Hence the column name is "country@printable" and the datatype is "_name
TEXT".

Also, you should be using double-quotes not single-quotes for quoting
column names.




> 'country@iso2' TEXT,
> 'country@iso3' TEXT,
> 'country@numcode' INTEGER,
> CONSTRAINT PK_country PRIMARY KEY ('country@id')
> )
>
> After execution of the next query
> pragma table_info('country');
> Sqliteman 1.2.2 yields
> 
> 
> Sqliteman export
> 
> 
> 
>
> cidnametypenotnulldflt_valuepk
> 0country@id
> INTEGER01
> 1country@name
> TEXT00
> 2country@printable_name
> TEXT00
> 3country@iso2
> TEXT00
> 4country@iso3
> TEXT00
> 5country@numcode
> INTEGER00
> 
> 
> 
> As you can see country@printable has the type _name TEXT.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Bug Report

[RSmith]

That is just the compiler trying to be helpful without being able to completely grasp the code and can safely be ignored, but thank 
you for the notice and feel free to initialize the variable in your version.


As an aside, that does not qualify as a "bug", the word "bug" means a whole other thing - which I am not going to elaborate on since 
I'm sure it was just a thought and not an actual contention.


Have a great day!
Ryan

On 2014/04/17 18:23, jalal Mostafa wrote:

Hey,
I downloaded the "Legacy Source Code Distribution Formats - sqlite-preprocessed-3080403.zip", while 
compiling in Microsoft Visual Studio 2013 a bug appeared in file "fts3_tokenize_vtab.c","error 
C4703: potentially uninitialized local pointer variable 'pTab' used" Line:203.Assigning the pointer to 
NULL will solve the problem.
Sincerely,Jalal.


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


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

Re: [sqlite] Bug Report

[Jim Dodgen]

Ryan, The late Grace Hopper would be happy about your responce

*Jim Dodgen*








On Thu, Apr 17, 2014 at 10:40 AM, RSmith  wrote:

> That is just the compiler trying to be helpful without being able to
> completely grasp the code and can safely be ignored, but thank you for the
> notice and feel free to initialize the variable in your version.
>
> As an aside, that does not qualify as a "bug", the word "bug" means a
> whole other thing - which I am not going to elaborate on since I'm sure it
> was just a thought and not an actual contention.
>
> Have a great day!
> Ryan
>
>
> On 2014/04/17 18:23, jalal Mostafa wrote:
>
>> Hey,
>> I downloaded the "Legacy Source Code Distribution Formats -
>> sqlite-preprocessed-3080403.zip", while compiling in Microsoft Visual
>> Studio 2013 a bug appeared in file "fts3_tokenize_vtab.c","error C4703:
>> potentially uninitialized local pointer variable 'pTab' used"
>> Line:203.Assigning the pointer to NULL will solve the problem.
>> Sincerely,Jalal.
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQlite ?

[Jay Sprenkle]

On 9/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I am seeing an assert crash on my system here. I think it is caused by the
following code,

--select.c  (line 88)--

/*
** Delete the given Select structure and all of its substructures.
*/
void sqlite3SelectDelete(Select *p){
  if( p ){
clearSelect(p);
sqliteFree(p);
  }
}
--

I think it should be:

if ( *p )


Why do you think so? The other way seems a good way to check for a non null
pointer. "*p" is a structure. What does testing a structure for non-zero do?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Bug in SQlite ?

[Rob Richardson]

What assertion failure are you seeing?  What is the exact message?  Can
you use a debugger to step into the code where the assertion failure
happens?

RobR

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] BUG in RTree ?

[Kees Nuyt]

On Sun, 20 Jul 2008 09:03:44 +0200, you wrote:

>Hello to all,
>
>I think I have found a bug in the RTree extension (I'm using version 3.6.0)
>If I run this script :

It works perfectly for me. 
Here is my version of the script:

sqlite_version():3.6.0

DROP TABLE IF EXISTS GEO_TEST;
DROP TABLE IF EXISTS SI_GEO_TEST;
CREATE TABLE GEO_TEST (FID INTEGER PRIMARY KEY NOT NULL,
NAME CHAR NOT NULL, E_UTMX REAL, E_UTMY REAL);
CREATE VIRTUAL TABLE SI_GEO_TEST USING rtree(si_pkid_si,
si_xmin_si, si_xmax_si, si_ymin_si, si_ymax_si);
CREATE TRIGGER TSII_GEO_TEST
AFTER INSERT ON GEO_TEST FOR EACH ROW 
BEGIN 
INSERT INTO SI_GEO_TEST (si_pkid_si, si_xmin_si,
si_xmax_si, si_ymin_si, si_ymax_si) VALUES (NEW.ROWID,
NEW.E_UTMX, NEW.E_UTMX, NEW.E_UTMY, NEW.E_UTMY);
END;
CREATE TRIGGER TSIU_GEO_TEST 
AFTER UPDATE ON GEO_TEST FOR EACH ROW 
BEGIN 
UPDATE SI_GEO_TEST 
SET si_xmin_si = NEW.E_UTMX, si_xmax_si = NEW.E_UTMX,
si_ymin_si = NEW.E_UTMY, si_ymax_si = NEW.E_UTMY 
WHERE si_pkid_si = NEW.ROWID;
END;
CREATE TRIGGER TSID_GEO_TEST
AFTER DELETE ON GEO_TEST FOR EACH ROW 
BEGIN
DELETE FROM SI_GEO_TEST WHERE si_pkid_si = OLD.ROWID;
END;
BEGIN;
INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (1,
'A', 10.0, 10.0);
INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (2,
'B', 20.0, 20.0);
INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (3,
'C', 30.0, 30.0);
INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (4,
'D', 40.0, 40.0);
INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (5,
'E', 50.0, 50.0);
END;
SELECT * FROM GEO_TEST;
1|A|10.0|10.0
2|B|20.0|20.0
3|C|30.0|30.0
4|D|40.0|40.0
5|E|50.0|50.0
SELECT * FROM SI_GEO_TEST;
1|10.0|10.0|10.0|10.0
2|20.0|20.0|20.0|20.0
3|30.0|30.0|30.0|30.0
4|40.0|40.0|40.0|40.0
5|50.0|50.0|50.0|50.0

>
>The first row is not inserted in the GEO_TEST table.
>
>If I do the inserts this way (with the first insert out of the transaction):
>
>INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (1, 'A', 
>10.0, 10.0);
>BEGIN;
>INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (2, 'B', 
>20.0, 20.0);

[snip]

>then every thing goes right.
>
>Am I doing something wrong ?

I don't see any errors, I copied your code verbatim, only
changed some indentation.

>Thanks in advance.
>
>Xevi
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] BUG in RTree ?

[Xevi]

Thank You,

I'll keep looking further.

Xevi

En/na Kees Nuyt ha escrit:
> On Sun, 20 Jul 2008 09:03:44 +0200, you wrote:
>
>   
>> Hello to all,
>>
>> I think I have found a bug in the RTree extension (I'm using version 3.6.0)
>> If I run this script :
>> 
>
> It works perfectly for me. 
>   
[snip]

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

Re: [sqlite] BUG REPORT: 3.6.21;

[Dan Kennedy]

On Jan 5, 2010, at 6:25 AM, Noah Hart wrote:

> Using the command line tools from the website
> 3.6.18 reports the error correctly;
>
> SQLite version 3.6.18
> sqlite> PRAGMA recursive_triggers = on;
> sqlite> CREATE TABLE t5 (a primary key, b, c);
> sqlite> INSERT INTO t5 values (1, 2, 3);
> sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR  
> IGNORE
> t5 SET a = new.a, c = 10;   END;
> sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ;
> SQL error: too many levels of trigger recursion
>
>
> 3.6.21 does not handle it properly
>
> SQLite version 3.6.21
> sqlite> PRAGMA recursive_triggers = on;
> sqlite> CREATE TABLE t5 (a primary key, b, c);
> sqlite> INSERT INTO t5 values (1, 2, 3);
> sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR  
> IGNORE
> t5 SET a = new.a, c = 10;   END;
> sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ;
> Error: SQL logic error or missing database

Thanks. Turns out this was just a shell tool problem, not a problem
with the library. Fixed here:

   http://www.sqlite.org/src/vinfo/e5d07045fa

> BACKGROUND:
> I was trying to get the test triggerC-1.11 in triggerC.test to work
> correctly;
> The assert in btree.c at line 3699 in the routine sqlite3BtreeRollback
> was
> failing returning a 2000, rather than 0
>
> assert( countWriteCursors(pBt)==0 );

How did you make this happen?

Dan.

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

Re: [sqlite] bug in proxyGetHostID()

[Jens Miltner]

Am 16.01.2010 um 19:00 schrieb Thomas Stüfe:

> Greetings,
>
> I think I found a small bug in sqlite 3.6.22 in the function  
> proxyGetHostID().
>
> There, pread() and pwrite() are used to read a host id file. Return  
> value of these functions is assigned to a size_t variable. size_t is  
> unsigned. Should pread fail for any reason (IO Error or whatever)  
> and return -1, the error handling would not kick in. Instead, the  
> function would probably just leave the host id uninitialized and  
> return ok.
>
> A valid fix might be using ssize_t (signed size_t) instead of  
> size_t, but I cannot of course say whether this would work on all  
> platforms.

AFAICS, this is for Mac OS X only anyway...
pread returns an ssize_t according to the docs, so the proposed change  
looks safe to me...

Another potential problem is when for some reason the hostid file is  
empty: in this case pread will return 0, which will leave pHostID  
uninitialized, too.
It will return a result code, but the OSTRACE3 inside proxyGetHostID  
still attempts to dump the host ID, which at this point will be  
uninitialized.


>
> My Platform is MacOS X, I compile with gcc 4.2. I compile the  
> amalgamation for sqlite 3.6.22. I only found this because gcc warns:
>
> sqlite3.c:26120: warning: comparison of unsigned expression < 0 is  
> always false
> sqlite3.c:26134: warning: comparison of unsigned expression < 0 is  
> always false




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

Re: [sqlite] bug in proxyGetHostID()

[Adam Swift]

That code hasn't really been stress tested or qualified properly.  I submitted 
some changes to the os_unix.c sources to address this and some other issues 
(proxyGetHostID now uses the gethostuuid() system api).  The changes I 
submitted are significant and are still undergoing review prior to integration 
on trunk. I don't know if/when those changes will be pulled into the main 
repository trunk (I do know it's a significant effort to fully review, audit 
and ensure test & branch coverage).

- adam

On Jan 19, 2010, at 12:40 AM, Jens Miltner wrote:

> 
> Am 16.01.2010 um 19:00 schrieb Thomas Stüfe:
> 
>> Greetings,
>> 
>> I think I found a small bug in sqlite 3.6.22 in the function  
>> proxyGetHostID().
>> 
>> There, pread() and pwrite() are used to read a host id file. Return  
>> value of these functions is assigned to a size_t variable. size_t is  
>> unsigned. Should pread fail for any reason (IO Error or whatever)  
>> and return -1, the error handling would not kick in. Instead, the  
>> function would probably just leave the host id uninitialized and  
>> return ok.
>> 
>> A valid fix might be using ssize_t (signed size_t) instead of  
>> size_t, but I cannot of course say whether this would work on all  
>> platforms.
> 
> AFAICS, this is for Mac OS X only anyway...
> pread returns an ssize_t according to the docs, so the proposed change  
> looks safe to me...
> 
> Another potential problem is when for some reason the hostid file is  
> empty: in this case pread will return 0, which will leave pHostID  
> uninitialized, too.
> It will return a result code, but the OSTRACE3 inside proxyGetHostID  
> still attempts to dump the host ID, which at this point will be  
> uninitialized.
> 
> 
>> 
>> My Platform is MacOS X, I compile with gcc 4.2. I compile the  
>> amalgamation for sqlite 3.6.22. I only found this because gcc warns:
>> 
>> sqlite3.c:26120: warning: comparison of unsigned expression < 0 is  
>> always false
>> sqlite3.c:26134: warning: comparison of unsigned expression < 0 is  
>> always false
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] bug: round problem?

[Simon Slavin]

On 15 Feb 2010, at 8:51am, zabusovm...@mail.ru wrote:

> sqlite> select round(40223+0.5);
> 40224.0
> sqlite> select round(40224+0.5);
> 40224.0
> sqlite> select round(40225+0.5);
> 40226.0

Nice try.  The rounding rule is that a .5 rounds to the nearest even number.  
This is so that rounding does not introduce its own statistical bias to a 
collection of results.  For instance, rounding ten thousand random numbers then 
taking the average, should give you roughly the same result as taking the 
average of those numbers, then rounding.

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

Re: [sqlite] bug: round problem?

[Simon Slavin]

On 15 Feb 2010, at 6:40pm, Simon Slavin wrote:

> .5 rounds to the nearest even number

Whoops.  My email software put this post in a different place to its followups. 
 Apologies.

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

Re: [sqlite] bug: round problem?

[Phil Hibbs]

Simon Slavin:
> Nice try.  The rounding rule is that a .5 rounds to the nearest even number.

No it isn't:
sqlite> select round(40226+0.5);
40227.0
sqlite>  select round(40227+0.5);
40228.0
sqlite>  select round(40228+0.5);
40229.0
sqlite>  select round(40229+0.5);
40230.0

Phil Hibbs.
-- 
Don't you just hate self-referential sigs?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ?bug causing sigsegv

[Pavel Ivanov]

Looking at this output from gdb:

> #0  0x in ?? ()
> #1  0x08058f8e in sqlite3_mutex_enter (p=0x90d98c0) at
> 3rdparty/sqlite3/sqlite3.c:14549

I can say that you don't have any mutex module installed (even a no-op
mutex module). It means that sqlite3_intialize() wasn't called because
mutex system is always initialized in sqlite3_initialize(). But you
work with SQLite via Qt API without problems, i.e. Qt called
sqlite3_initialize() which seems to be contradictory. But
sqlite3_initialize() initializes static structures which means that
SQLite API used inside Qt and SQLite API that you use yourself see the
same static variables as different memory locations. I suspect this
can happen if Qt is some dynamically linked library which has SQLite
library statically linked inside. And you link SQLite statically with
your application, so you've got 2 completely different instances of
SQLite API working with completely different static variables.

If everything is indeed how I said then I doubt the code where Qt API
and SQLite API are used interchangeably will work properly. I guess
you can fix your SIGSEGV by calling sqlite3_initialize() somewhere at
the beginning of your application but I think inconsistency in static
variables of SQLite can surface in some other errors along the way.


Pavel

On Sun, Apr 4, 2010 at 4:48 PM, Neville Dastur  wrote:
> |Hi I am using Qt to connect to a Sqlite3 database (3.6.23-1). Source is
> just included with my app, not a dll / lib.
> System: Mac OSX 10.6 and Ubuntu 9.10 (GCC 4.4)
> The method of getting a sqlite* is described in the Qt docs and I have
> checked the memory addresses in gdb and the returned sqlite3* really is
> valid. sqlite.magic also = OPEN.
> Other ref:
> |http://www.mail-archive.com/sqlite-users@sqlite.org/msg25807.html
> |
> The issues is I get a SIGSEGV on the sqlite_exec call
>
>    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
>    db.setDatabaseName("test.db");
>    if ( db.open() ) {
>
>       // If uncommented this works fine
>       //db.exec("CREATE TABLE normalMethod1 ( id INT(64), value
> CHAR(32) )");
>
>        // This is taken from the Qt Docs
>        QVariant v = db.driver()->handle();
>        if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0) {
>            // v.data() returns a pointer to the handle
>            sqlite3 *handle = *static_cast(v.data());
>            if (handle != 0) { // check that it is not NULL
>
>                qint64 id = sqlite3_last_insert_rowid((sqlite3
> *)handle); // <-- This works fine
>
>                char* localError=0;
>                const char* sql = "CREATE TABLE tblOne (id int(32), name
> char(16))";
>                int rc = sqlite3_exec(handle, sql, 0, 0, &localError);
> // <-- This causes SIGSEGV
>                if (rc != SQLITE_OK) {
>                    qFatal("Sqlite3 failed with %i", rc);
>                }
>            }
>        }
>    } |
>
> The gdb output and stack backtrace is:
>
> Core was generated by `./pcs_debug'.
> Program terminated with signal 11, Segmentation fault.
> [New process 31016]
> [New process 31017]
> #0  0x in ?? ()
> (gdb) run ./pcs_debug
> Starting program: /./pcs_debug ./pcs_debug
> [Thread debugging using libthread_db enabled]
> [New Thread 0xb7749970 (LWP 31147)]
> Xlib:  extension "RANDR" missing on display ":0.0".
>
> ** (:31147): CRITICAL **: atk_object_set_name: assertion `name
> != NULL' failed
> [New Thread 0xb7516b70 (LWP 31152)]
> [Thread 0xb7516b70 (LWP 31152) exited]
>
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 0xb7749970 (LWP 31147)]
> 0x in ?? ()
> (gdb) backtrace
> #0  0x in ?? ()
> #1  0x08058f8e in sqlite3_mutex_enter (p=0x90d98c0) at
> 3rdparty/sqlite3/sqlite3.c:14549
> #2  0x080bc6d5 in sqlite3_exec (db=0x90d3ab0, zSql=0x80f4f34 "CREATE
> TABLE tblOne (id int(32), name char(16))", xCallback=0, pArg=0x0,
>    pzErrMsg=0xbfda0388) at 3rdparty/sqlite3/sqlite3.c:76821
> #3  0x0804f732 in main (argc=2, argv=0xbfda04b4) at src/main.cpp:58
>
> I have to say that I'm stuck and think this is an issue with threading
> in sqlite3.
>
> Thanks
>
> Neville
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ?bug causing sigsegv

[Neville Dastur]

On 05/04/2010 22:04, Pavel Ivanov wrote:

Thank you for reply.
> Looking at this output from gdb:
>
>
>> #0  0x in ?? ()
>> #1  0x08058f8e in sqlite3_mutex_enter (p=0x90d98c0) at
>> 3rdparty/sqlite3/sqlite3.c:14549
>>  
> I can say that you don't have any mutex module installed (even a no-op
> mutex module). It means that sqlite3_intialize() wasn't called because
> mutex system is always initialized in sqlite3_initialize(). But you
> work with SQLite via Qt API without problems, i.e. Qt called
> sqlite3_initialize() which seems to be contradictory. But
> sqlite3_initialize() initializes static structures which means that
> SQLite API used inside Qt and SQLite API that you use yourself see the
> same static variables as different memory locations. I suspect this
> can happen if Qt is some dynamically linked library which has SQLite
> library statically linked inside. And you link SQLite statically with
> your application, so you've got 2 completely different instances of
> SQLite API working with completely different static variables.
>
I did consider this as the issue. However tracing through with gdb the 
sqlite3 "handle" (ie sqlite3*) is the same memory location from Qt calls 
and the returned handle from

sqlite3 *handle = *static_cast(v.data());

I also thought that sqlite3_initialize shouldn't need to be called and looking 
through the Qt code it isn't. Also just to be clear I open the sqlite3 database 
with a Qt call. I then use the returned handle to execute sqlite3 functions 
directly. This is to extend the Qt sqlite3 implementation. So I can't see how I 
would have two instances. But willing to be corrected!

> If everything is indeed how I said then I doubt the code where Qt API
> and SQLite API are used interchangeably will work properly. I guess
> you can fix your SIGSEGV by calling sqlite3_initialize() somewhere at
> the beginning of your application but I think inconsistency in static
> variables of SQLite can surface in some other errors along the way.
>
>
> Pavel
>
> On Sun, Apr 4, 2010 at 4:48 PM, Neville Dastur  wrote:
>
>> |Hi I am using Qt to connect to a Sqlite3 database (3.6.23-1). Source is
>> just included with my app, not a dll / lib.
>> System: Mac OSX 10.6 and Ubuntu 9.10 (GCC 4.4)
>> The method of getting a sqlite* is described in the Qt docs and I have
>> checked the memory addresses in gdb and the returned sqlite3* really is
>> valid. sqlite.magic also = OPEN.
>> Other ref:
>> |http://www.mail-archive.com/sqlite-users@sqlite.org/msg25807.html
>> |
>> The issues is I get a SIGSEGV on the sqlite_exec call
>>
>> QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
>> db.setDatabaseName("test.db");
>> if ( db.open() ) {
>>
>>// If uncommented this works fine
>>//db.exec("CREATE TABLE normalMethod1 ( id INT(64), value
>> CHAR(32) )");
>>
>> // This is taken from the Qt Docs
>> QVariant v = db.driver()->handle();
>> if (v.isValid()&&  qstrcmp(v.typeName(), "sqlite3*")==0) {
>> // v.data() returns a pointer to the handle
>> sqlite3 *handle = *static_cast(v.data());
>> if (handle != 0) { // check that it is not NULL
>>
>> qint64 id = sqlite3_last_insert_rowid((sqlite3
>> *)handle); //<-- This works fine
>>
>> char* localError=0;
>> const char* sql = "CREATE TABLE tblOne (id int(32), name
>> char(16))";
>> int rc = sqlite3_exec(handle, sql, 0, 0,&localError);
>> //<-- This causes SIGSEGV
>> if (rc != SQLITE_OK) {
>> qFatal("Sqlite3 failed with %i", rc);
>> }
>> }
>> }
>> } |
>>
>> The gdb output and stack backtrace is:
>>
>> Core was generated by `./pcs_debug'.
>> Program terminated with signal 11, Segmentation fault.
>> [New process 31016]
>> [New process 31017]
>> #0  0x in ?? ()
>> (gdb) run ./pcs_debug
>> Starting program: /./pcs_debug ./pcs_debug
>> [Thread debugging using libthread_db enabled]
>> [New Thread 0xb7749970 (LWP 31147)]
>> Xlib:  extension "RANDR" missing on display ":0.0".
>>
>> ** (:31147): CRITICAL **: atk_object_set_name: assertion `name
>> != NULL' failed
>> [New Thread 0xb7516b70 (LWP 31152)]
>> [Thread 0xb7516b70 (LWP 31152) exited]
>>
>> Program received signal SIGSEGV, Segmentation fault.
>> [Switching to Thread 0xb7749970 (LWP 31147)]
>> 0x in ?? ()
>> (gdb) backtrace
>> #0  0x in ?? ()
>> #1  0x08058f8e in sqlite3_mutex_enter (p=0x90d98c0) at
>> 3rdparty/sqlite3/sqlite3.c:14549
>> #2  0x080bc6d5 in sqlite3_exec (db=0x90d3ab0, zSql=0x80f4f34 "CREATE
>> TABLE tblOne (id int(32), name char(16))", xCallback=0, pArg=0x0,
>> pzErrMsg=0xbfda0388) at 3rdparty/sqlite3/sqlite3.c:76821
>> #3  0x0804f732 in main (argc=2, argv=0xbfda04b4) at src/main.cpp:58
>>
>> I have to say that I'm stuck and think this is an issue with threading
>> in sqlite3.
>>
>> Thanks
>>
>> Neville
>>
>> 

Re: [sqlite] ?bug causing sigsegv

[Pavel Ivanov]

> I did consider this as the issue. However tracing through with gdb the
> sqlite3 "handle" (ie sqlite3*) is the same memory location from Qt calls
> and the returned handle from

Sure thing, it will be the same because you requested pointer from
inside Qt. But it's not a static object which I talked about.

> I also thought that sqlite3_initialize shouldn't need to be called and 
> looking through the Qt code it isn't.

It is called automatically from inside some of SQLite functions
including sqlite3_open*(), but not from sqlite3_exec().

> Also just to be clear I open the sqlite3 database with a Qt call. I then use 
> the returned handle to execute sqlite3 functions directly. This is to extend 
> the Qt sqlite3 implementation.

That's exactly why problem of several instances of static objects can appear.

> So I can't see how I would have two instances. But willing to be corrected!

If you can trace with gdb then enter into sqlite3_exec, then enter
into sqlite3_mutex_enter and then look on the address of
sqlite3GlobalConfig.mutex that is used there (or to the same extent on
the address of just sqlite3GlobalConfig). I guess db.exec() will also
enter into sqlite3_exec so you'll be able to go the same path and look
at the address of sqlite3GlobalConfig.mutex there (if the path is
different then just go through all SQLite functions until you see
sqlite3GlobalConfig somewhere). I bet you'll discover that addresses
of this variable are different inside db.exec() and inside
sqlite3_exec().


Pavel

On Tue, Apr 6, 2010 at 3:51 PM, Neville Dastur  wrote:
> On 05/04/2010 22:04, Pavel Ivanov wrote:
>
> Thank you for reply.
>> Looking at this output from gdb:
>>
>>
>>> #0  0x in ?? ()
>>> #1  0x08058f8e in sqlite3_mutex_enter (p=0x90d98c0) at
>>> 3rdparty/sqlite3/sqlite3.c:14549
>>>
>> I can say that you don't have any mutex module installed (even a no-op
>> mutex module). It means that sqlite3_intialize() wasn't called because
>> mutex system is always initialized in sqlite3_initialize(). But you
>> work with SQLite via Qt API without problems, i.e. Qt called
>> sqlite3_initialize() which seems to be contradictory. But
>> sqlite3_initialize() initializes static structures which means that
>> SQLite API used inside Qt and SQLite API that you use yourself see the
>> same static variables as different memory locations. I suspect this
>> can happen if Qt is some dynamically linked library which has SQLite
>> library statically linked inside. And you link SQLite statically with
>> your application, so you've got 2 completely different instances of
>> SQLite API working with completely different static variables.
>>
> I did consider this as the issue. However tracing through with gdb the
> sqlite3 "handle" (ie sqlite3*) is the same memory location from Qt calls
> and the returned handle from
>
> sqlite3 *handle = *static_cast(v.data());
>
> I also thought that sqlite3_initialize shouldn't need to be called and 
> looking through the Qt code it isn't. Also just to be clear I open the 
> sqlite3 database with a Qt call. I then use the returned handle to execute 
> sqlite3 functions directly. This is to extend the Qt sqlite3 implementation. 
> So I can't see how I would have two instances. But willing to be corrected!
>
>> If everything is indeed how I said then I doubt the code where Qt API
>> and SQLite API are used interchangeably will work properly. I guess
>> you can fix your SIGSEGV by calling sqlite3_initialize() somewhere at
>> the beginning of your application but I think inconsistency in static
>> variables of SQLite can surface in some other errors along the way.
>>
>>
>> Pavel
>>
>> On Sun, Apr 4, 2010 at 4:48 PM, Neville Dastur  wrote:
>>
>>> |Hi I am using Qt to connect to a Sqlite3 database (3.6.23-1). Source is
>>> just included with my app, not a dll / lib.
>>> System: Mac OSX 10.6 and Ubuntu 9.10 (GCC 4.4)
>>> The method of getting a sqlite* is described in the Qt docs and I have
>>> checked the memory addresses in gdb and the returned sqlite3* really is
>>> valid. sqlite.magic also = OPEN.
>>> Other ref:
>>> |http://www.mail-archive.com/sqlite-users@sqlite.org/msg25807.html
>>> |
>>> The issues is I get a SIGSEGV on the sqlite_exec call
>>>
>>>     QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
>>>     db.setDatabaseName("test.db");
>>>     if ( db.open() ) {
>>>
>>>        // If uncommented this works fine
>>>        //db.exec("CREATE TABLE normalMethod1 ( id INT(64), value
>>> CHAR(32) )");
>>>
>>>         // This is taken from the Qt Docs
>>>         QVariant v = db.driver()->handle();
>>>         if (v.isValid()&&  qstrcmp(v.typeName(), "sqlite3*")==0) {
>>>             // v.data() returns a pointer to the handle
>>>             sqlite3 *handle = *static_cast(v.data());
>>>             if (handle != 0) { // check that it is not NULL
>>>
>>>                 qint64 id = sqlite3_last_insert_rowid((sqlite3
>>> *)handle); //<-- This works fine
>>>
>>>                 char* localError=0;
>>

Re: [sqlite] Bug Report - Documentation

[Mark Benningfield]

Hello All:

Sorry for the misdirection.
The correct link is 
http://sqlite.org/lang_transaction.html

Mark Benningfield

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

Re: [sqlite] Bug: Column Constraint

[Richard Hipp]

Not a bug.  See the 4th paragraph at
http://www.sqlite.org/lang_createtable.html#rowid

On Thu, Jul 22, 2010 at 8:03 AM, sanjiv  wrote:

> When I use the following command string:
>"CREATE TABLE tbl1 (Id INT CONSTRAINT PK_Id PRIMARY KEY
> AUTOINCREMENT NOT NULL ,Name VARCHAR(20) )"
> I get the following SQLite exception:
>"AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY"
>
> wherease no exception is thrown if INT in the command is changed to
> INTEGER. As per the affinity rules, INT should be treated as INTEGER.
>
> Thanks
>
> Sanjiv
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] bug in time() ?

[D. Richard Hipp]

Kenneth Lo wrote:
I think the time() function is not reporting the correct time. The date part
is OK. I also note that CVSTrac uses C codes to handle datetime i.e. not relying
on sqlite. Is there a bug?
Some investigation ...
bash-2.05b$ date
Wed Mar 31 11:12:03 HKT 2004
bash-2.05b$ sqlite
SQLite version 2.8.12
Enter ".help" for instructions
sqlite> SELECT strftime('%s','now');
1080701286
sqlite> select datetime(1080701286,'unixepoch');
2004-03-31 02:48:06
sqlite> select datetime(1080701286,'localtime');
2954147-07-10 19:59:59
sqlite> select datetime('now');
2004-03-31 02:49:07
Looks like it isn't rounding the right way someplace.
SQLite computes dates internally using Julian Day Numbers,
which means that the seconds are represented as a tiny
fraction of a day.  In hindsight, this is probably a
bad idea since it tends to lead to roundoff errors of
this kind.  On the other hand, I suppose it isn't too
late to change it
As for CVSTrac using its own date/time functions, that's
because CVSTrac was written long before I added date/time
functions to SQLite.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] bug in time() ?

[Doug Currie]

sqlite> select datetime(1080701286,'unixepoch');
2004-03-31 02:48:06
sqlite> select datetime(1080701286,'localtime');
2954147-07-10 07:00:00
sqlite> select datetime(1080701286,'unixepoch','localtime');
2004-03-30 21:48:06
sqlite> select julianday('now');
2453095.66955468
sqlite> select datetime(2453095.66955468,'localtime');
2004-03-30 23:04:09

I think the problem is that unixepoch seconds are being used as Julian
Day Numbers, which they aren't.

e

Tuesday, March 30, 2004, 10:28:37 PM, you wrote:

> Kenneth Lo wrote:
>> I think the time() function is not reporting the correct time. The date part
> is OK. I also note that CVSTrac uses C codes to handle datetime i.e. not relying
> on sqlite. Is there a bug?
>> 
>> Some investigation ...
>> bash-2.05b$ date
>> Wed Mar 31 11:12:03 HKT 2004
>> bash-2.05b$ sqlite
>> SQLite version 2.8.12
>> Enter ".help" for instructions
>> sqlite> SELECT strftime('%s','now');
>> 1080701286
>> sqlite> select datetime(1080701286,'unixepoch');
>> 2004-03-31 02:48:06
>> sqlite> select datetime(1080701286,'localtime');
>> 2954147-07-10 19:59:59
>> sqlite> select datetime('now');
>> 2004-03-31 02:49:07


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Bug with UNION

[Darren Duncan]

On Mon, 31 May 2004, George Ionescu wrote:
> I think I have found a bug related to UNION SELECT; try this:
> SELECT * FROM Test1 UNION SELECT * FROM Test2
> And I get only one record ?
> Shouldn't I get two records, both having 1 value ?

You got the correct answer.  A plain union will return exactly one copy of 
every unique row in the source queries.  If you want all the rows to be 
returned, including duplicates, then you need to say "UNION ALL" instead 
of "UNION". -- Darren Duncan


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Bug with UNION

[rbundy]

UNION working correctly. UNION returns distinct values; UNION ALL returns
all rows (including duplicates).

rayB




*** Confidentiality and Privilege Notice ***

This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Bug with sqlite3_column_decltype?

[Clay Dowling]

George Ionescu said:
> CREATE TABLE [Orders] ([OrderID] AFFINITY INT, [CustomerID] AFFINITY
> VARCHAR(5))

>
> AFFINITYINT,[Cu

Try this instead:

CREATE TABLE Orders (OrderID int, CustomerID varchar(5));

That should give you something closer to the expected results.

Clay

-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

Re: [sqlite] Bug with sqlite3_column_decltype?

[Gerhard Haering]

On Tue, Oct 26, 2004 at 03:58:07PM +0200, George Ionescu wrote:
> [...] I have the following table definition:
> 
> CREATE TABLE [Orders] ([OrderID] AFFINITY INT, [CustomerID] AFFINITY
> VARCHAR(5))

Where did you get this interesting syntax with AFFINITY from?

-- Gerhard
-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?


signature.asc
Description: Digital signature