Thanks for your quick response!
On Mon, May 13, 2019 at 1:17 AM Simon Slavin wrote:
> On 12 May 2019, at 11:56pm, Manuel Rigger wrote:
>
> > Is this intended?
>
> Yes. Because the temp database disappears when you close your connection.
> So you would open a database
Okay, thanks for the clarification!
Best,
Manuel
On Mon, May 13, 2019 at 1:38 AM J. King wrote:
> On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger
> wrote:
>
> >As far as I understood, the main and temp databases are always loaded
> >and
> >cannot be detached. But
Hi everyone,
It seems that a table created in the temp database cannot have a parent
table that is created in the main database and vice versa:
PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 PRIMARY KEY);
CREATE TEMP TABLE t1 (c0, FOREIGN KEY(c0) REFERENCES t0(c0));
INSERT INTO t1(c0) VALUES (1);
Hi everyone,
Consider the following example:
CREATE TABLE t0(c0 TEXT);
INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT
SELECT (+ c0) IS c0 FROM t0; -- expected: 0, actual: 1
I would expect that a row with a value 0 is returned. I suspect that this
is a misunderstanding on my
, I
would expect it to be performed.
Best,
Manuel
On Sun, Jun 9, 2019 at 3:51 PM Keith Medcalf wrote:
>
> On Sunday, 9 June, 2019 05:20, Manuel Rigger
> wrote:
>
> >CREATE TABLE t0(c0 TEXT);
> >INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT
> &
It could be surprising in the example below:
SELECT CAST('-0.0' AS NUMERIC); -- 0.0
SELECT CAST('0.0' AS NUMERIC); -- 0
SELECT CAST('+0.0' AS NUMERIC); -- 0
Best,
Manuel
On Wed, Jun 12, 2019 at 3:57 PM John McKown
wrote:
> On Wed, Jun 12, 2019 at 8:35 AM Richard Hipp wrote:
>
> > IEEE754
(and admittedly obscure) test case was necessary.
Best,
Manuel
On Thu, May 9, 2019 at 10:54 AM E.Pasma wrote:
>
> > Op 9 mei 2019, om 00:07 heeft Manuel Rigger
> het volgende geschreven:
> > Hi,
> >
> > I discovered another bug that is triggered when "PRAGMA
> >
Hi,
I discovered a sequence of statements that results in a malformed database
disk image:
CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
UPDATE t1 SET c0 = NULL;
UPDATE OR REPLACE t1 SET c1 = 1;
SELECT DISTINCT * FROM t1 WHERE
Thanks for the fix!
Best,
Manuel
On Thu, May 9, 2019 at 7:12 PM Richard Hipp wrote:
> On 5/9/19, Manuel Rigger wrote:
> >
> > I discovered a sequence of statements that results in a malformed
> database
> > disk image:
> >
>
> Should be fixed now.
>
&
Hi everyone,
I found another test case that demonstrates a malfunctioning index:
CREATE TABLE IF NOT EXISTS t0 (c0);
CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL;
INSERT INTO t0(c0) VALUES(NULL);
SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row
If the index is created, no
Great, thanks, Dan!
Best,
Manuel
On Sat, May 11, 2019 at 4:05 PM Dan Kennedy wrote:
>
> On 11/5/62 16:54, Manuel Rigger wrote:
> > Hi everyone,
> >
> > I found another test case that demonstrates a malfunctioning index:
> >
> > CREATE TABLE IF NOT EXISTS t0
Hi Ryan,
I hope my question did not offend you. I didn't expect that the answer to
this question would be considered to be that obvious.
Best,
Manuel
On Mon, May 13, 2019 at 9:57 AM R Smith wrote:
> On 2019/05/13 12:56 AM, Manuel Rigger wrote:
> > Hi everyone,
> >
> >
Hi everyone,
Consider the following test case:
CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
REINDEX;
SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL; --
returns 5|
I would expect that the
Hi Ryan,
Okay, thanks for the clarification! Your explanation makes sense!
Best,
Manuel
On Mon, May 13, 2019 at 8:25 PM R Smith wrote:
> On 2019/05/13 11:42 AM, Manuel Rigger wrote:
> > Hi Ryan,
> >
> > I hope my question did not offend you. I didn't e
2 1 500 r[5]=t1.c0
> 28 Column 2 0 600 r[6]=t1.c1
> 29 RealAffinity 6 0 000
> 30 ResultRow 5 2 000 output=r[5..6]
> 31 Noop 0 0
Hi everyone,
I was surprised by how the ROUND function handles "Inf" REAL values.
A REAL infinity value can be created by using an overly-large number:
SELECT 1e5000; -- Inf
When casting infinity to an integer value, the largest integer is returned,
which seems intuitive to me:
SELECT
Hi,
I found an issue where a row is not fetched when using a LIKE operator on
an INT UNIQUE COLLATE NOCASE column:
CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
INSERT INTO t0(c0) VALUES ('./');
SELECT * FROM t0 WHERE t0.c0 LIKE './'; -- fetches no rows
The following query returns TRUE:
Hi,
I discovered another bug that is triggered when "PRAGMA
reverse_unordered_selects=true" is used. It's similar to a previous bug
that I reported [1], but the statement triggering the bug has a compound
expression that should always be true (for values that are not NULL) in the
WHERE clause:
I noticed that this has been fixed now (
https://www.sqlite.org/src/info/db9acef14d492121). Thanks!
Best,
Manuel
On Fri, May 10, 2019 at 12:09 PM Manuel Rigger
wrote:
> Hi everyone,
>
> I was surprised by how the ROUND function handles "Inf" REAL values.
>
>
Hi Donald,
You are right. Sorry for not mentioning that. I was working on trunk and
using Linux. The bug has already been fixed:
https://www.sqlite.org/src/info/bc7d2c1656396bb4
Best,
Manuel
On Wed, May 15, 2019 at 3:13 PM Donald Griggs wrote:
> On Tue, May 14, 2019 at 10:18 AM Manuel Rig
Hi everyone,
I found a curious bug, which I could reproduce only with a very specific
statement sequence:
PRAGMA reverse_unordered_selects=true;
CREATE TABLE t1 (c0, c1);
CREATE TABLE t2 (c0 INT UNIQUE);
INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL);
INSERT INTO t2(c0) VALUES (1);
SELECT 1,
Hi everyone,
the following sequence of SQL statements results in "Error: malformed
database schema (index_0) - non-deterministic functions prohibited in index
expressions".
CREATE TABLE test (c0);
CREATE INDEX index_0 ON test(c0 LIKE '');
PRAGMA case_sensitive_like=false;
VACUUM;
SELECT * from
Okay, thanks!
Best,
Manuel
Am So., 28. Apr. 2019 um 21:02 Uhr schrieb Simon Slavin <
slav...@bigfraud.org>:
> On 28 Apr 2019, at 7:58pm, Manuel Rigger wrote:
>
> > It seems that setting "PRAGMA case_sensitive_like" to either false (the
> default behavior) or
Hi everyone,
It think that I found a bug where I could circumvent a UNIQUE check of an
index. Consider the example below, which causes "Error: UNIQUE constraint
failed" when invoking REINDEX:
CREATE TABLE test (c0, c1 REAL);
CREATE UNIQUE INDEX index_1 ON test(c0 GLOB c1);
INSERT INTO test(c0,
Hi everyone,
I think that I found a bug that occurs when setting legacy_file_format to
true and calling REINDEX, which then results in "Error: UNIQUE constraint
failed: index 'index_0'" in the specific example below:
CREATE TABLE test (c0, c1 TEXT);
CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON
Hi everyone,
Consider the example below:
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 '/');
Unexpectedly, the SELECT statement does not return a result. If the
statement for the creation of the
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);
ume.
>
> >-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
> >
Hi everyone,
when executing the example below, I get "Error: no such column: asdf". This
behavior is surprising to me, as I would have expected "asdf" to be
interpreted as a string and not as a column name.
CREATE TABLE test (c0);
CREATE INDEX index_1 ON test('asdf');
According to the docs,
est(c0, c1) VALUES ("c1", 0);
SELECT * FROM test WHERE c0 = 'c1';
Best,
Manuel
Am Sa., 27. Apr. 2019 um 21:14 Uhr schrieb Richard Hipp :
> On 4/27/19, Manuel Rigger wrote:
> >
> > when executing the example below, I get "Error: no such column: asdf".
> This
&
Great, thanks a lot, Dan!
Best,
Manuel
On Mon, May 6, 2019 at 6:18 PM Dan Kennedy wrote:
>
> On 6/5/62 16:42, Manuel Rigger wrote:
> > Hi everyone,
> >
> > the following example fails with an error "no such column: c0":
> >
> > CREATE TABLE t
Hi everyone,
consider the following example:
PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1));
INSERT OR FAIL INTO t0(c0, c1) VALUES
(0, 1),
(0, 2);
SELECT * FROM t0; -- returns 0|1
I expect the INSERT to fail, since both the UNIQUE and the
Hi everyone,
I discovered what I think is a bug, as demonstrated below:
CREATE TABLE t0(c0);
CREATE TABLE t1(c0 INTEGER PRIMARY KEY);
PRAGMA reverse_unordered_selects=true;
INSERT INTO t1(c0) VALUES (0);
INSERT INTO t0(c0) VALUES ('a');
SELECT * FROM t1, t0 WHERE t1.c0 < t0.c0;
I would expect
xed on trunk.
>
> On 5/6/19, Manuel Rigger wrote:
> > Hi everyone,
> >
> > consider the following example:
> >
> > PRAGMA foreign_keys=true;
> > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES
> t0(c1));
> > INSERT OR FAIL INTO t0(c0, c
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
Unfortunately, I deleted some other similar cases that I found before
reading your reply. I'll gather any new cases (I assume you are talking
about cases that the int-real branch should address).
Best,
Manuel
On Fri, May 3, 2019 at 2:34 PM Richard Hipp wrote:
> On 5/3/19, Manuel Rigger wr
, the row is not fetched.
Best,
Manuel
On Sat, May 4, 2019 at 3:45 PM Manuel Rigger
wrote:
> Hi,
>
> I discovered a bug, which is demonstrated through the following test case:
>
> CREATE TABLE t0(c0);
> CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL;
> INSERT INT
Hi everyone,
Consider the following example:
CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
INSERT INTO t1(c1) VALUES (0X7ff);;
SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
I would expect the row to be fetched, which is not the case.
I confirmed that the real value
Hi,
I discovered a bug, which is demonstrated through the following test case:
CREATE TABLE t0(c0);
CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL;
INSERT INTO t0(c0) VALUES (NULL);
SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE);
No row is fetched, although the WHERE clause is always TRUE. I
e> select c1 - 1 from t1;
> >5.76460752303423e+17
> >sqlite>
> >
> >
> >---
> >The fact that there's a Highway to Hell but only a Stairway to Heaven
> >says a lot about anticipated traffic volume.
> >
> >
> >>-Origina
that if the expression
> "a OR 1" is true, then "a IS NOT NULL" must also be true. And that
> assumption is correct for most binary operators - just not for OR.
> Fixed now.
>
> On 5/4/19, Manuel Rigger wrote:
> > This similar test case, that I just found now
Hi everyone,
the following example fails with an error "no such column: c0":
CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID;
ALTER TABLE t0 RENAME COLUMN c0 TO c1;
However, specifying c0 as the PRIMARY KEY in the column definition rather
than in a table constraint seems to work:
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!
> >
&g
NG 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
> > t
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
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
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
)?
Best,
Manuel
On Fri, May 3, 2019 at 1:22 PM Manuel Rigger
wrote:
> Hi,
>
> I found another very specific corner case for which an index fails
> ("UNIQUE constraint failed: index 'index_0'"):
>
> CREATE TABLE test (c0 REAL);
> CREATE UNIQUE INDEX index_0 ON test(LENG
Hi,
I found another very specific corner case for which an index fails ("UNIQUE
constraint failed: index 'index_0'"):
CREATE TABLE test (c0 REAL);
CREATE UNIQUE INDEX index_0 ON test(LENGTH(-c0));
INSERT INTO test(c0) VALUES (0.0), ('10:');
REINDEX;
Best,
Manuel
st,
Manuel
On Fri, May 3, 2019 at 1:49 PM Richard Hipp wrote:
> On 5/3/19, Manuel Rigger wrote:
> > I found another similar example with GLOB:
> >
> > CREATE TABLE test (c0 REAL);
> > CREATE UNIQUE INDEX index_0 ON test(('0' GLOB -c0));
> > INSERT INT
Hi everyone,
I found what could be a bug. Consider the example below:
CREATE TABLE test (c1 TEXT PRIMARY KEY) WITHOUT ROWID;
CREATE INDEX index_0 ON test(c1 COLLATE NOCASE);
INSERT INTO test(c1) VALUES ('A');
INSERT INTO test(c1) VALUES ('a');
SELECT * FROM test;
The query returns only one
st,
Manuel
Am Sa., 27. Apr. 2019 um 22:14 Uhr schrieb Richard Hipp :
> On 4/27/19, Manuel Rigger wrote:
> > Thanks for your quick and helpful reply! So if I understood correctly,
> > there is no way to ensure that a string is not interpreted as a column in
> > an arbitrar
Great, thanks for opening the bug report and for the attribution!
Best,
Manuel
Am So., 28. Apr. 2019 um 17:07 Uhr schrieb Richard Hipp :
> On 4/28/19, Manuel Rigger wrote:
> >
> > CREATE TABLE test (c1 TEXT PRIMARY KEY) WITHOUT ROWID;
> > CREATE INDEX index_0 ON t
Hi everyone,
It seems that setting "PRAGMA case_sensitive_like" to either false (the
default behavior) or true results in no longer being able to use a LIKE or
GLOB clause when creating an index. For example, the example below results
in "Error: non-deterministic functions prohibited in index
Hi everyone,
Is there a simple way to determine the collating function of a column?
PRAGMA table_info does not seem to provide this information. The
information could be extracted from sqlite_master, which contains the
SQL statements used to create the table or view. While parsing the SQL
string
Thanks for all your great work, Richard and Dan! Among all DBMS that we
have been testing, we have put most of our effort and energy into testing
SQLite. The reason for that is that you were by far the most responsive to
our bug reports, and typically address bugs immediately after we find them!
Thanks for adding these functions! My test generator/fuzzer is written in
Java and uses the JDBC interface. In order to call the C function, I would
probably need to modify the driver code or use JNI, which would both be a
bit hacky. Is there a compile-time option that can be set to provide
Hi Yongheng and Rui,
This might be a duplicate to a bug that I reported, since both test cases
trigger the same assertion error. See
https://sqlite.org/src/tktview?name=37823501c6.
Best,
Manuel
On Fri, Dec 27, 2019 at 6:09 PM Yongheng Chen wrote:
> Hi,
>
> We found an assertion violation bug
58 matches
Mail list logo
