Re: [sqlite] sqlite3 java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state
On Wed, 1 May 2019 at 19:30, Frank Kemmer wrote: > > https://github.com/xerial/sqlite-jdbc/blob/14839bae0ceedff805f9cda35f5e52db8c4eea88/src/main/java/org/sqlite/core/CoreResultSet.java#L86 > > Here we see, that colsMeta == null results in throwing the seen exception. > > But how can colsMeta be null in a valid resultSet? > > Does anybody have a deeper understanding how this can happen in the sqlite3 > code? > This has nothing to do with the sqlite3 code itself but judging by the comments in the jdbc binding, colsMeta being null implies that the ResultSet has been closed. But I'm not sure where colsMeta is initialised, it appears some other class is responsible for that. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the week of the month from strftime or date functions
Yes, this will work. It's a long story. I am creating a Gantt visual schedule of a project based on the tasks dates, and I want to show the visual effects per weeks. But, you have hit the hammer on the nail, as we say in Spanish. This I can use. Donald Griggs, Thursday, May 2, 2019 04:16 PM, wrote... To: SQLite mailing list Subject: Re: [sqlite] Getting the week of the month from strftime or date functions Hello Jose, Regarding: "...but I need to get the week of that month based on the date." One interpretation of your question might me: Given a date "d", which, say, falls on a Wednesday, then return 1, 2, 3, 4, or 5 denoting whether d is on the 1st, 2nd, 3rd, 4th, or 5th Wednesday of that month. If that's the question, then the sqlite (or C) expression: 1 + (d - 1) / 7 should do it (where the slash represent truncating integer division) ___ 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] Getting the week of the month from strftime or date functions
I found this very interesting,
15:52:46.71>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT strftime('%W','2019-01-01');
00
sqlite> SELECT strftime('%W','2019-01-02');
00
sqlite> SELECT strftime('%W','2019-01-03');
00
sqlite> SELECT strftime('%W','2019-01-04');
00
sqlite> SELECT strftime('%W','2019-01-05');
00
sqlite> SELECT strftime('%W','2019-01-06');
00
sqlite> SELECT strftime('%W','2019-01-07');
01
I expected 2019-01-01 to be part of week 1, since it was Tuesday. So, back to
the drawing board. ;-) Thanks.
Thomas Kurz, on Thursday, May 2, 2019 04:01 PM, wrote...
To: SQLite mailing list
Subject: Re: [sqlite] Getting the week of the month from strftime or date
functions
I think "week of the month" is not a standard value. As with week of the year,
is week #1 the week in which the month starts, the first complete week within
the month, or the first week with at least 4 days?
- Original Message -
From: Jose Isaias Cabrera
To: sqlite-users@mailinglists.sqlite.org
Sent: Thursday, May 2, 2019, 21:44:44
Subject: [sqlite] Getting the week of the month from strftime or date functions
Greetings.
To break Manuel's constant bug finding emails, :-), I want to get the week of
the month from either date or strftime functions. I know I can get the week of
the year by doing,
SELECT strftime('%W','2019-03-07');
but I need to get the week of that month based on the date. I can write a
quick function to do it, but I thought there was an option for it, but I
couldn't find it in the help site[1]. Is there such a choice?
Thanks.
[1] https://www.sqlite.org/lang_datefunc.html
SQLite Query Language: Date And Time
Functions
The only reasons for providing functions other than strftime() is for
convenience and for efficiency. Time Strings. A time string can be in any of
the following formats:
www.sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the week of the month from strftime or date functions
Hello Jose, Regarding: "...but I need to get the week of that month based on the date." One interpretation of your question might me: Given a date "d", which, say, falls on a Wednesday, then return 1, 2, 3, 4, or 5 denoting whether d is on the 1st, 2nd, 3rd, 4th, or 5th Wednesday of that month. If that's the question, then the sqlite (or C) expression: 1 + (d - 1) / 7 should do it (where the slash represent truncating integer division) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] logically stored rows
Ok I understand now. It was difficult to see why SQLite would ever choose to return rows in a different order than the order in which they were stored if the SELECT does not specify an ORDER until Dr. Hipp explained that it could get the requested columns from a separate index instead of the actual table. Those were all helpful explanations, thanks guys, Tom > Hi, > > In this page in the docs: https://sqlite.org/queryplanner.html#searching > > it says: > "The rows are logically stored in order of increasing rowid" > > Would this imply that executing a SELECT would always return the rows in > order or increasing rowid? > > So that a "SELECT * from MyTable" would return all the rows in ROWID order > because that is how they are stored. That is the way the query planner currently works but there is NO GUARANTEE that it will continue to work that way in the future. And, in fact, if you set "PRAGMA reverse_unordered_selects=ON" it will not. So you should never rely on that. Instead, write: SELECT * FROM MyTable ORDER BY rowid; If you EXPLAIN both statements: EXPLAIN SELECT * FROM MyTable; EXPLAIN SELECT * FROM MyTable ORDER BY rowid; ... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the week of the month from strftime or date functions
I think "week of the month" is not a standard value. As with week of the year,
is week #1 the week in which the month starts, the first complete week within
the month, or the first week with at least 4 days?
- Original Message -
From: Jose Isaias Cabrera
To: sqlite-users@mailinglists.sqlite.org
Sent: Thursday, May 2, 2019, 21:44:44
Subject: [sqlite] Getting the week of the month from strftime or date functions
Greetings.
To break Manuel's constant bug finding emails, :-), I want to get the week of
the month from either date or strftime functions. I know I can get the week of
the year by doing,
SELECT strftime('%W','2019-03-07');
but I need to get the week of that month based on the date. I can write a
quick function to do it, but I thought there was an option for it, but I
couldn't find it in the help site[1]. Is there such a choice?
Thanks.
[1] https://www.sqlite.org/lang_datefunc.html
SQLite Query Language: Date And Time
Functions
The only reasons for providing functions other than strftime() is for
convenience and for efficiency. Time Strings. A time string can be in any of
the following formats:
www.sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting the week of the month from strftime or date functions
Greetings.
To break Manuel's constant bug finding emails, :-), I want to get the week of
the month from either date or strftime functions. I know I can get the week of
the year by doing,
SELECT strftime('%W','2019-03-07');
but I need to get the week of that month based on the date. I can write a
quick function to do it, but I thought there was an option for it, but I
couldn't find it in the help site[1]. Is there such a choice?
Thanks.
[1] https://www.sqlite.org/lang_datefunc.html
SQLite Query Language: Date And Time
Functions
The only reasons for providing functions other than strftime() is for
convenience and for efficiency. Time Strings. A time string can be in any of
the following formats:
www.sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index on REAL column malfunctions when multiplying with a string
Hi everyone,
I found another corner case where I could break an index on a REAL column
(UNIQUE constraint failed: index 'index_0').
CREATE TABLE test (c0 REAL);
CREATE UNIQUE INDEX index_0 ON test(TRIM(('' * c0)));
INSERT INTO test(c0) VALUES (0.0), (0.1);
REINDEX;
As with the previous examples, a REAL column is combined with some string
manipulations. The multiplication operator seems necessary, but TRIM can be
replaced with another string function.
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TYPEOF index on REAL column malfunctions
Thanks a lot! Best, Manuel On Thu, May 2, 2019 at 7:52 PM Richard Hipp wrote: > On 5/2/19, Manuel Rigger wrote: > > Hi everyone, > > > > I think that I found another issue related to type affinity on real > columns: > > The typeof() function corner-case you identified has been fixed in > check-in https://www.sqlite.org/src/timeline?c=48889530a9de22fe > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TYPEOF index on REAL column malfunctions
On 5/2/19, Manuel Rigger wrote: > Hi everyone, > > I think that I found another issue related to type affinity on real columns: The typeof() function corner-case you identified has been fixed in check-in https://www.sqlite.org/src/timeline?c=48889530a9de22fe -- 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] UPSERT clause does not work with "NOT NULL" constraint
Great, thanks! Best, Manuel On Thu, May 2, 2019 at 6:25 PM Richard Hipp wrote: > Documentation has been updated in an attempt to clarify when UPSERT > applies and when it does not. > > On 5/2/19, Manuel Rigger wrote: > > Okay, thanks for the clarification! > > > > I think that this part of the documentation is ambiguous. The part of the > > documentation that you quoted mentions a "conflict target", but there is > no > > conflict target in the example that I provided. The documentation > > continues by stating that a conflict target is not necessary and that "A > DO > > NOTHING upsert without a conflict target works the same as an INSERT OR > > IGNORE." Would it maybe be helpful to update the documentation to > > explicitly state that UPSERT does not apply to NOT NULL constraints, and > > that apart from this case DO NOTHING works in the same way as INSERT OR > > IGNORE? > > > > Best, > > Manuel > > > > On Thu, May 2, 2019 at 5:38 PM Richard Hipp wrote: > > > >> On 5/2/19, Manuel Rigger wrote: > >> > Hi everyone, > >> > > >> > It seems that upsert does not take into account "NOT NULL" > constraints. > >> In > >> > the example below, I get an error "NOT NULL constraint failed: > >> > test.c0": > >> > > >> > CREATE TABLE test (c0 NOT NULL); > >> > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; > >> > > >> > I would have expected that the second statement has the same effect as > >> the > >> > following statement, which would also be confirmed by the docs [1]: > >> > > >> > INSERT OR IGNORE INTO test(c0) VALUES (NULL); > >> > > >> > The example seems to work for the UNIQUE and PRIMARY KEY constraints. > >> > >> UPSERT is not standard SQL - it is a PostgreSQL extension that we have > >> attempted to replicate. PostgreSQL behaves the same way in this test > >> (a fact that I have just now verified on sqlfiddle.com). The ON > >> CONFLICT clause is only triggered by uniqueness constraints, not NOT > >> NULL constraints. > >> > >> The UPSERT documentation says "The conflict target specifies a > >> specific uniqueness constraint that will trigger the upsert." So it > >> does not explicitly say that UPSERT does not work for NOT NULL > >> constraints, but that is the implication. > >> > >> > >> -- > >> D. Richard Hipp > >> d...@sqlite.org > >> > > > > > -- > 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] UPSERT clause does not work with "NOT NULL" constraint
Documentation has been updated in an attempt to clarify when UPSERT applies and when it does not. On 5/2/19, Manuel Rigger wrote: > Okay, thanks for the clarification! > > I think that this part of the documentation is ambiguous. The part of the > documentation that you quoted mentions a "conflict target", but there is no > conflict target in the example that I provided. The documentation > continues by stating that a conflict target is not necessary and that "A DO > NOTHING upsert without a conflict target works the same as an INSERT OR > IGNORE." Would it maybe be helpful to update the documentation to > explicitly state that UPSERT does not apply to NOT NULL constraints, and > that apart from this case DO NOTHING works in the same way as INSERT OR > IGNORE? > > Best, > Manuel > > On Thu, May 2, 2019 at 5:38 PM Richard Hipp wrote: > >> On 5/2/19, Manuel Rigger wrote: >> > Hi everyone, >> > >> > It seems that upsert does not take into account "NOT NULL" constraints. >> In >> > the example below, I get an error "NOT NULL constraint failed: >> > test.c0": >> > >> > CREATE TABLE test (c0 NOT NULL); >> > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; >> > >> > I would have expected that the second statement has the same effect as >> the >> > following statement, which would also be confirmed by the docs [1]: >> > >> > INSERT OR IGNORE INTO test(c0) VALUES (NULL); >> > >> > The example seems to work for the UNIQUE and PRIMARY KEY constraints. >> >> UPSERT is not standard SQL - it is a PostgreSQL extension that we have >> attempted to replicate. PostgreSQL behaves the same way in this test >> (a fact that I have just now verified on sqlfiddle.com). The ON >> CONFLICT clause is only triggered by uniqueness constraints, not NOT >> NULL constraints. >> >> The UPSERT documentation says "The conflict target specifies a >> specific uniqueness constraint that will trigger the upsert." So it >> does not explicitly say that UPSERT does not work for NOT NULL >> constraints, but that is the implication. >> >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TYPEOF index on REAL column malfunctions
Hi everyone, I think that I found another issue related to type affinity on real columns: CREATE TABLE test (c0 REAL); CREATE UNIQUE INDEX index_0 ON test(TYPEOF(c0)); INSERT OR IGNORE INTO test(c0) VALUES (0.1); INSERT OR IGNORE INTO test(c0) VALUES (FALSE); REINDEX; In this example, the REINDEX fails with "UNIQUE constraint failed: index 'index_0'". FALSE is converted to 0.0, so two REALs are stored. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint
Okay, thanks for the clarification! I think that this part of the documentation is ambiguous. The part of the documentation that you quoted mentions a "conflict target", but there is no conflict target in the example that I provided. The documentation continues by stating that a conflict target is not necessary and that "A DO NOTHING upsert without a conflict target works the same as an INSERT OR IGNORE." Would it maybe be helpful to update the documentation to explicitly state that UPSERT does not apply to NOT NULL constraints, and that apart from this case DO NOTHING works in the same way as INSERT OR IGNORE? Best, Manuel On Thu, May 2, 2019 at 5:38 PM Richard Hipp wrote: > On 5/2/19, Manuel Rigger wrote: > > Hi everyone, > > > > It seems that upsert does not take into account "NOT NULL" constraints. > In > > the example below, I get an error "NOT NULL constraint failed: test.c0": > > > > CREATE TABLE test (c0 NOT NULL); > > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; > > > > I would have expected that the second statement has the same effect as > the > > following statement, which would also be confirmed by the docs [1]: > > > > INSERT OR IGNORE INTO test(c0) VALUES (NULL); > > > > The example seems to work for the UNIQUE and PRIMARY KEY constraints. > > UPSERT is not standard SQL - it is a PostgreSQL extension that we have > attempted to replicate. PostgreSQL behaves the same way in this test > (a fact that I have just now verified on sqlfiddle.com). The ON > CONFLICT clause is only triggered by uniqueness constraints, not NOT > NULL constraints. > > The UPSERT documentation says "The conflict target specifies a > specific uniqueness constraint that will trigger the upsert." So it > does not explicitly say that UPSERT does not work for NOT NULL > constraints, but that is the implication. > > > -- > 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] UPSERT clause does not work with "NOT NULL" constraint
On 5/2/19, Manuel Rigger wrote: > Hi everyone, > > It seems that upsert does not take into account "NOT NULL" constraints. In > the example below, I get an error "NOT NULL constraint failed: test.c0": > > CREATE TABLE test (c0 NOT NULL); > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; > > I would have expected that the second statement has the same effect as the > following statement, which would also be confirmed by the docs [1]: > > INSERT OR IGNORE INTO test(c0) VALUES (NULL); > > The example seems to work for the UNIQUE and PRIMARY KEY constraints. UPSERT is not standard SQL - it is a PostgreSQL extension that we have attempted to replicate. PostgreSQL behaves the same way in this test (a fact that I have just now verified on sqlfiddle.com). The ON CONFLICT clause is only triggered by uniqueness constraints, not NOT NULL constraints. The UPSERT documentation says "The conflict target specifies a specific uniqueness constraint that will trigger the upsert." So it does not explicitly say that UPSERT does not work for NOT NULL constraints, but that is the implication. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPSERT clause does not work with "NOT NULL" constraint
Hi everyone, It seems that upsert does not take into account "NOT NULL" constraints. In the example below, I get an error "NOT NULL constraint failed: test.c0": CREATE TABLE test (c0 NOT NULL); INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; I would have expected that the second statement has the same effect as the following statement, which would also be confirmed by the docs [1]: INSERT OR IGNORE INTO test(c0) VALUES (NULL); The example seems to work for the UNIQUE and PRIMARY KEY constraints. Best, Manuel [1] "A DO NOTHING upsert without a conflict target works the same as an INSERT OR IGNORE." at https://sqlite.org/lang_UPSERT.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: CTE name leaking through views
This mailing list disallows attachments as a anti-spam measure. You
can send attachments directly to me, if you like.
On 5/1/19, Stephen Hunt wrote:
> Hi,
>
> We use SQLite extensively here at Zaber and are quite pleased with it.
> However, we recently added a view that incorrectly returns incorrect/NULL
> data. It appears to be cause by CTE names leaking outside of the view and
> being confused with another CTE with the same name but only if both CTEs
> produce the same number of rows.
>
> I have attached some simple SQL and a procedure to reproduce the issue.
>
>1. Create a new database
>2. Read the attached SQL in demo.sql (which creates one table, Data_Demo,
>and two views, View_Proto and View_Demo, each of which have a CTE named
>Temp that produces 2 rows)
>3. Run SELECT * FROM View_Demo; which incorrectly produces (note the
>incorrect/NULL values for columns Att, Val, and Act):
>
>Id ProtocolAtt Val Act
>-- -- -- -- --
>1 A A
>1 A B
>1 B A
>1 B B
>2 ...
>
>4. Run SELECT * FROM View_Demo WHERE Val IS NOT NULL; which correctly
>produces:
>
>Id ProtocolAtt Val Act
>-- -- -- -- --
>1 A C D E
>1 A F G H
>1 B C D E
>1 B F G H
>2 ...
>
>
> Note that this problem disappears if the two Temp CTEs are given different
> names:
>
> CREATE VIEW View_ProtoASWITH Temp2(Name) AS (VALUES ('A'),
> ('B'))SELECT Name Name FROM Temp2;
>
> OR if they have a different number of rows:
>
> CREATE VIEW View_ProtoASWITH Temp(Name) AS (VALUES ('A'), ('B'),
> ('X'))SELECT Name Name FROM Temp;
>
> Also if one of the CTEs is a SELECT statement instead of a VALUES
> statement, the problem also disappears, even if the CTE names and number of
> rows returned are the same:
>
> CREATE VIEW View_ProtoASWITH Temp(Name) AS (SELECT Name FROM Data_Demo
> LIMIT 2)SELECT Name Name FROM Temp;
>
> I have confirmed this on both versions 3.22 and 3.28. I’m running Ubuntu
> 18.04.
>
> Thanks for looking into this. I really appreciate it! Please let me know if
> you need any more information.
>
> Regards,
>
> Steve
> --
> Stephen Hunt
> Zaber Technologies Inc.
> #2 - 605 West Kent Ave. N.
> Vancouver, British Columbia
> Canada, V6P 6T7
> Toll free (Canada and USA): 1-888-276-8033
> Phone: +1-604-569-3780 ext. 134
> Fax: +1-604-648-8033
> www.zaber.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COLLATE NOCASE index on REAL column malfunctions
Fixed at https://www.sqlite.org/src/timeline?c=b043a54c3de54b28
On 5/1/19, Manuel Rigger wrote:
> I'm very sorry, after finding the issue using the latest stable Linux
> version, I accidentally used an outdated version (3.24.0) to produce a
> minimal failing case. Here is a reduced example that triggers the bug on
> the latest stable [1] and snapshot [2] versions:
>
> CREATE TABLE test (c0 REAL);
> CREATE INDEX index_0 ON test(c0 COLLATE NOCASE);
> INSERT INTO test(c0) VALUES ('+/');
> SELECT * FROM test WHERE (c0 LIKE '+/');
>
> Best,
> Manuel
>
>
> [1] 3.28.0 2019-04-16 19:49:53
> 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
> [2] 3.29.0 2019-04-27 20:30:19
> 50fe48458942fa7a6bcc76316c6321f95b23dc34f2f8e0a483826483b2fb16f6
>
> On Wed, May 1, 2019 at 9:55 PM Warren Young wrote:
>
>> On May 1, 2019, at 1:18 PM, Richard Hipp wrote:
>> >
>> > I am unable to reproduce the observed behavior.
>>
>> Nor I, on 3.28.0 release with our custom build.
>>
>> Thank you for providing a simple test case, Manuel: it helps greatly!
>>
>> > What version of
>> > SQLite are you testing with? Are you compiling it yourself? If so,
>> > what compile-time options do you use?
>>
>> Also, what is your locale setting? The fact that COLLATE NOCASE affects
>> it makes this smell like a locale/i18n issue, to me.
>> ___
>> 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
>
--
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] COLLATE NOCASE index on REAL column malfunctions
@Warren: I'm building a tool to test DBMS by automatically generating
queries and checking their results. Since the statement sequence was
generated automatically, it looks like artificial.
@Keith Thanks again for the explanation!
Best,
Manuel
On Thu, May 2, 2019 at 1:24 AM Keith Medcalf wrote:
>
> Again, I think this is a problem with applying affinity when the index is
> created. The result of applying real affinity to the string '+/' should
> probably be the string '+/' not the real value 0. On the gripping hand,
> '+/' looks like a number with "crud" at the end of the string. I believe
> this is documented somewhere for the application of numeric affinity to a
> string that looks like a number but with crud at the end.
>
> sqlite> select cast('27 dollars' as real);
> 27.0
> sqlite> select cast('+/' as real);
> 0.0
>
> Although c0 is stored in the table as TEXT, the application of real
> affinity to the string '+/' when building the index results in a real(0).
>
> The COLLATE NOCASE simply allows the LIKE operator to use the index (since
> using an index for LIKE in the default case insensitive mode requires an
> index with COLLATE NOCASE). Since the index is incorrect (the index key is
> real(0) not text('+/') the index lookup fails.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Manuel Rigger
> >Sent: Wednesday, 1 May, 2019 15:31
> >To: SQLite mailing list
> >Subject: Re: [sqlite] COLLATE NOCASE index on REAL column
> >malfunctions
> >
> >I'm very sorry, after finding the issue using the latest stable Linux
> >version, I accidentally used an outdated version (3.24.0) to produce
> >a
> >minimal failing case. Here is a reduced example that triggers the bug
> >on
> >the latest stable [1] and snapshot [2] versions:
> >
> >CREATE TABLE test (c0 REAL);
> >CREATE INDEX index_0 ON test(c0 COLLATE NOCASE);
> >INSERT INTO test(c0) VALUES ('+/');
> >SELECT * FROM test WHERE (c0 LIKE '+/');
> >
> >Best,
> >Manuel
> >
> >
> >[1] 3.28.0 2019-04-16 19:49:53
> >884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
> >[2] 3.29.0 2019-04-27 20:30:19
> >50fe48458942fa7a6bcc76316c6321f95b23dc34f2f8e0a483826483b2fb16f6
> >
> >On Wed, May 1, 2019 at 9:55 PM Warren Young
> >wrote:
> >
> >> On May 1, 2019, at 1:18 PM, Richard Hipp wrote:
> >> >
> >> > I am unable to reproduce the observed behavior.
> >>
> >> Nor I, on 3.28.0 release with our custom build.
> >>
> >> Thank you for providing a simple test case, Manuel: it helps
> >greatly!
> >>
> >> > What version of
> >> > SQLite are you testing with? Are you compiling it yourself? If
> >so,
> >> > what compile-time options do you use?
> >>
> >> Also, what is your locale setting? The fact that COLLATE NOCASE
> >affects
> >> it makes this smell like a locale/i18n issue, to me.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
