Now I have two questions:
1. I created database from scratch using new version of SQLITE and PRAGMA
integrity_check; produces "missing index" as before. How could I broke it if
all indexing is done on insertion?
2. Replacing "=" with "IS" in the original WHERE solved the problem. However,
manual says "=" and "IS" are identical except when treating "NULL". What is the
difference?
Roman
________________________________________
From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at
mailinglists.sqlite.org] on behalf of Roman Fleysher
[roman.fleys...@einstein.yu.edu]
Sent: Tuesday, May 19, 2015 11:23 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select * where is/like ?
I guess not good:
sqlite> PRAGMA integrity_check;
integrity_check
-------------------------------------------------
row 37 missing from index sqlite_autoindex_Exam_1
row 38 missing from index sqlite_autoindex_Exam_1
row 39 missing from index sqlite_autoindex_Exam_1
row 40 missing from index sqlite_autoindex_Exam_1
row 41 missing from index sqlite_autoindex_Exam_1
row 42 missing from index sqlite_autoindex_Exam_1
row 43 missing from index sqlite_autoindex_Exam_1
row 44 missing from index sqlite_autoindex_Exam_1
row 45 missing from index sqlite_autoindex_Exam_1
row 46 missing from index sqlite_autoindex_Exam_1
row 47 missing from index sqlite_autoindex_Exam_1
row 48 missing from index sqlite_autoindex_Exam_1
row 49 missing from index sqlite_autoindex_Exam_1
row 50 missing from index sqlite_autoindex_Exam_1
row 51 missing from index sqlite_autoindex_Exam_1
row 52 missing from index sqlite_autoindex_Exam_1
________________________________________
From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 19, 2015 11:20 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select * where is/like ?
On 19 May 2015, at 4:07pm, Roman Fleysher <roman.fleysher at einstein.yu.edu>
wrote:
> CREATE TABLE Exam(
> examID TEXT PRIMARY KEY NOT NULL
Works fine for me. I tested inserting as text, integer and real in case they
did something weird to LIKE.
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Exam(
...> examID TEXT PRIMARY KEY NOT NULL);
sqlite> INSERT INTO Exam VALUES ('30');
sqlite> SELECT * FROM Exam;
30
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID
like 30;
a30a|text
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
a30a|text
sqlite> DELETE FROM Exam;
sqlite> INSERT INTO Exam VALUES (30);
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID
like 30;
a30a|text
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
a30a|text
sqlite>
sqlite> DELETE FROM Exam;
sqlite> INSERT INTO Exam VALUES (30.0);
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID
like 30;
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
sqlite> select 'a'||examID||'a', typeof(examID) from Exam;
a30.0a|text
Is your database corrupt ? Can you run the PRAGMA on it ?
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
