[sqlite] drop table if link view error pragma_table_info view

2019-05-20 Thread Kirill
Hello

If you delete a table that is associated with a view.
An error occurs when calling pragma_table_info

Example:
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY NOT NULL,
   NAME   TEXTNOT NULL,
   AGEINT NOT NULL,
   ADDRESSCHAR(50),
   SALARY REAL
);

CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

DROP table 'COMPANY';

pragma table_info('COMPANY_VIEW');

Error: Uncaught Error: no such table: main.COMPANY

As an option to automatically destroy all connected VIEW


-- 
Best regards,
  Kirill
  mailto:kir...@aidagw.com

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


Re: [sqlite] Allow overriding unsigned 64-bit integer

2017-01-08 Thread Kirill Müller

On 08.01.2017 12:54, Clemens Ladisch wrote:

Kirill Müller wrote:

... there's no portable support for 64-bit integers.

That's what SQLite looks at SQLITE_INT64_TYPE.

The patch introduces SQLITE_UINT64_TYPE.



I'm working around this issue by using a struct of size 8

A struct of size 8 does not behave the same as an integer type.
For compiling sqlite.c, I'm using a 64-bit integer type. I'm only using 
the struct for compiling C++ modules that include sqlite.h. On the C++ 
side I'll find a suitable way to handle the data.



Currently I'm applying the attached patch

This mailing list does not allow attachments.
Copied the patch to 
https://gist.github.com/krlmlr/951cb742d5105ffac163fb7b8dc86986 .



-Kirill



Regards,
Clemens
___
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] Allow overriding unsigned 64-bit integer

2017-01-08 Thread Kirill Müller

On 08.01.2017 21:24, Richard Hipp wrote:

On 1/7/17, Kirill Müller <krlmlr...@mailbox.org> wrote:

I'm in a situation [1] where I compile RSQLite as part of a C++ project
that still uses the C++98 standard.

Can you instead compile SQLite as a library (libsqlite3.a) using gcc
or clang, then link the compiled *.a file into your project, so that
your C++98 compiler never needs to know about 64-bit integers?

Thanks. This is effectively what I'm doing, but the C++98 compiler needs 
to see sqlite.h because C++ code is calling into SQLite. The patch 
allows me to override both signed and unsigned int64 types to something 
the compiler is happy with.


https://gist.github.com/krlmlr/951cb742d5105ffac163fb7b8dc86986


-Kirill

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


Re: [sqlite] Allow overriding unsigned 64-bit integer

2017-01-08 Thread Kirill Müller

On 08.01.2017 17:38, Clemens Ladisch wrote:

Kirill Müller wrote:

... there's no portable support for 64-bit integers.
I'm working around this issue by using a struct of size 8

I'm open to alternatives.

What's wrong with SQLITE_INT64_TYPE?
(Even older C++ compilers should have the C .)
Thanks. I'm currently exploring boost/cstdint.hpp, hope this is 
supported on all target platforms.



-Kirill


Regards,
Clemens
___
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] Allow overriding unsigned 64-bit integer

2017-01-08 Thread Kirill Müller

On 08.01.2017 14:20, Clemens Ladisch wrote:

Kirill Müller wrote:

On 08.01.2017 12:54, Clemens Ladisch wrote:

Kirill Müller wrote:

... there's no portable support for 64-bit integers.
I'm working around this issue by using a struct of size 8

A struct of size 8 does not behave the same as an integer type.


For compiling sqlite.c, I'm using a 64-bit integer type. I'm only
using the struct for compiling C++ modules that include sqlite.h.

This means that you cannot link the C and C++ code together, because
they use different types.
Good point. I was planning to cross fingers that an 8-byte POD struct 
will align nicely with a 64-bit integer. (Would need to take care about 
endianness, too.) I'm open to alternatives.



On the C++ side I'll find a suitable way to handle the data.

How exactly do you get the C++ compiler to handle an 8-byte struct the
same as a 64-bit integer (a type which it supposedly does not have)?
I'd use a multiprecision C++ class. My code uses 64-bit integers only 
for reading and writing column data.



-Kirill


Regards,
Clemens
___
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] Allow overriding unsigned 64-bit integer

2017-01-08 Thread Kirill Müller

On 08.01.2017 12:54, Clemens Ladisch wrote:

Kirill Müller wrote:

... there's no portable support for 64-bit integers.

That's what SQLite looks at SQLITE_INT64_TYPE.

The patch introduces SQLITE_UINT64_TYPE.



I'm working around this issue by using a struct of size 8

A struct of size 8 does not behave the same as an integer type.
For compiling sqlite.c, I'm using a 64-bit integer type. I'm only using 
the struct for compiling C++ modules that include sqlite.h. On the C++ 
side I'll find a suitable way to handle the data.



Currently I'm applying the attached patch

This mailing list does not allow attachments.
Copied the patch to 
https://gist.github.com/krlmlr/951cb742d5105ffac163fb7b8dc86986 .



-Kirill



Regards,
Clemens
___
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] Allow overriding unsigned 64-bit integer

2017-01-07 Thread Kirill Müller

Hi all


I'm in a situation [1] where I compile RSQLite as part of a C++ project 
that still uses the C++98 standard. This means there's no portable 
support for 64-bit integers. I'm working around this issue by using a 
struct of size 8, but this means I need to override the "unsigned 64-bit 
type" separately.


Currently I'm applying the attached patch to the released SQLite3 
sources. It uses the macro SQLITE_UINT64_TYPE if defined, instead of 
"unsigned SQLITE_INT64_TYPE". Please consider merging. Thanks.



Best regards

Kirill


[1] https://github.com/rstats-db/RSQLite

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


Re: [sqlite] ":memory:" path does not seem to work on Windows anymore

2016-06-20 Thread Kirill Müller

On 30.05.2016 17:09, Richard Hipp wrote:

On 5/30/16, Kirill Müller <krlmlr...@mailbox.org> wrote:

  I'd appreciate any pointers on bisecting
SQLite. Thanks.

(1) Make sure you have tclsh 8.5 or later installed on your system, as
there are various TCL scripts that SQLite makefile needs to run in
order to build the amalgamation.

(2) Install Fossil.  You can either download the precompiled
stand-alone binary from https://www.fossil-scm.org/download.html and
put that binary somewhere on your $PATH, or you can compile the
stand-alone binary yourself, from sources.
(https://www.fossil-scm.org/fossil/doc/trunk/www/build.wiki)  Either
way, installing is just a matter of putting the "fossil" or
"fossil.exe" file on your $PATH or %PATH% and uninstalling is simply
deleting the binary.

(3) Clone the SQLite repo:  fossil clone http://www.sqlite.org/src sqlite.fossil

(4) Open a check-out on the SQLite repo:  fossil open sqlite.fossil

(5) Make sure you can build:  ./configure; make sqlite3.c

(6) Start the bisect:  "fossil bisect reset; fossil bisect good
version-3.11.0; fossil bisect bad trunk".  In place of
"version-3.11.0" and "trunk", substitute whatever tags and/or SHA-1
check-in hashes and/or ISO8601 timestamps are appropriate to define
the end-points of the bisection.

(7) Rebuild and test.  Then type "fossil bisect good|bad" as
appropriate.  "fossil bisect undo" is available if you make a mistake
and need to back up.

(8) When the bisect finishes (or at any time in between) you can do
"fossil bisect ui" to see a timeline graph of the bisection.
Thanks for the very helpful detailed instructions. The original issue 
probably comes from how we compile and link the SQLite files, this is 
most likely not a regression.


I have noticed that fossil 1.33 is missing a "bisect skip" command which 
would allow ignoring revisions that are "untestable" but cannot be 
decided to be good or bad.



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


Re: [sqlite] ":memory:" path does not seem to work on Windows anymore

2016-05-30 Thread Kirill Müller

On 30.05.2016 09:57, Stephan Beal wrote:

On Mon, May 30, 2016 at 8:35 AM, Kirill Müller <krlmlr...@mailbox.org>
wrote:


I can't reproduce the issue on Windows with the current command-line
client, but it is real in our environment. How can I help you replicate it?
Thanks.


-Kirill

On 26.05.2016 14:57, Kirill Müller wrote:


Hi


In the R interface to SQLite [1], we observe that opening a database with
":memory:" does not work anymore on Windows (both 32- and 64-bit versions),



might it be a problem with the R wrapper? If it cannot be reproduced in the
cli client then the wrapper is the most likely culprit.

The R wrapper is rather thin at this point, perhaps the build 
environment is triggering this. It used to work with an older version of 
SQLite, thoujgh. I'd be happy to run a bisection against SQLite, but I'm 
having trouble finding intermediate amalgamation sources, and I'm not 
really familiar with Fossil. I'd appreciate any pointers on bisecting 
SQLite. Thanks.



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


Re: [sqlite] ":memory:" path does not seem to work on Windows anymore

2016-05-30 Thread Kirill Müller
I can't reproduce the issue on Windows with the current command-line 
client, but it is real in our environment. How can I help you replicate 
it? Thanks.



-Kirill

On 26.05.2016 14:57, Kirill Müller wrote:

Hi


In the R interface to SQLite [1], we observe that opening a database 
with ":memory:" does not work anymore on Windows (both 32- and 64-bit 
versions), possibly related to an upgrade from sqlite 3.8.6 to 3.11.1. 
Using "file::memory:" works on 3.11.1, but not for 3.8.6 . The 
sqlite3_open_v2() function returns SQLITE_CANTOPEN, and is called with 
(":memory:", a valid pointer to a sqlite3*, SQLITE_RWC, NULL) 
according to code inspection. Please advise.



Best regards

Kirill


[1] https://github.com/rstats-db/RSQLite
___
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] ":memory:" path does not seem to work on Windows anymore

2016-05-26 Thread Kirill Müller

Hi


In the R interface to SQLite [1], we observe that opening a database 
with ":memory:" does not work anymore on Windows (both 32- and 64-bit 
versions), possibly related to an upgrade from sqlite 3.8.6 to 3.11.1. 
Using "file::memory:" works on 3.11.1, but not for 3.8.6 . The 
sqlite3_open_v2() function returns SQLITE_CANTOPEN, and is called with 
(":memory:", a valid pointer to a sqlite3*, SQLITE_RWC, NULL) according 
to code inspection. Please advise.



Best regards

Kirill


[1] https://github.com/rstats-db/RSQLite
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Query flattening for left joins involving subqueries on the right-hand side

2015-11-27 Thread Kirill Müller
On 27.11.2015 10:38, Clemens Ladisch wrote:
> Kirill M?ller wrote:
>> I see no reason why the following two queries can't be executed with the 
>> same plans:
>>
>> ... t1 LEFT JOIN t2 ...
>> ... t1 LEFT JOIN (SELECT * FROM t2) ...
> In this case, the queries are identical.  But SQLite's query optimizer
> does not try to optimize this because such trivial subqueries are
> (thought to be) unlikely to occur in practice.
Thanks. It seems to work better for inner joins, though. The practical 
application is a query generator that relies on the SQL engine to be 
able to optimize this. Is there any chance that SQLite will be able to 
treat such queries more efficiently?


-Kirill


[sqlite] Query flattening for left joins involving subqueries on the right-hand side

2015-11-27 Thread Kirill Müller
Exactly. And I'd pretty much like SQLite to figure that out for me ;-)


-Kirill


On 27.11.2015 03:19, Keith Medcalf wrote:
> Would it not be more efficient to say:
>
> select 1 from t1 limit 1;
>
> ?
>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> bounces at mailinglists.sqlite.org] On Behalf Of Kirill M?ller
>> Sent: Thursday, 26 November, 2015 15:03
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Query flattening for left joins involving subqueries
>> on the right-hand side
>>
>> On 26.11.2015 21:12, Clemens Ladisch wrote:
>>> Kirill M?ller wrote:
>>>> On 25.11.2015 16:32, Clemens Ladisch wrote:
>>>>> Kirill M?ller wrote:
>>>>>> For a left join with a subquery on the right-hand side, that subquery
>>>>>> doesn't seem to be flattened.
>>>>> This is rule 3 of <http://www.sqlite.org/optoverview.html#flattening>.
>>>> I wonder if this rule might be relaxed a bit.
>>> Only if you relax your requirement that the results must be correct.
>>>
>>>
>>> In the general case, a left outer join can be rewritten like this:
>>>
>>> SELECT ... FROM A JOIN B ON ...
>>> UNION ALL
>>> SELECT ... FROM A WHERE NOT EXISTS (look up in B)
>>>
>>> This query would be more likely to be flattenable, but also be slower.
>>>
>> Thanks. Let's not focus on terminology -- I thought "flattening" was the
>> right word to use, but it probably isn't. Of course I'm looking for
>> correct results.
>>
>> Originally, I attached a script but it seems that it's been stripped.
>> I've pasted it below. I see no reason why the following two queries (1
>> and 3 in the script) can't be executed with the same plans:
>>
>> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)
>> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) zzz2
>> USING (a) LIMIT 1)
>>
>> This is for two tables t1 and t2 with a single column "a". The script
>> creates them and populates them with 20 rows each.
>>
>>
>> -Kirill
>>
>>
>> #!/bin/bash
>>
>> db=test.sqlite
>>
>> #if false; then
>> rm -f $db
>>
>> n=20
>>
>> sqlite3 $db "CREATE TABLE t1 (a int primary key)"
>> seq 1 $n | sqlite3 $db ".import /dev/stdin t1"
>>
>> sqlite3 $db "CREATE TABLE t2 (a int primary key)"
>> seq 1 $n | sqlite3 $db ".import /dev/stdin t2"
>> #fi
>>
>> q() {
>>   sqlite3 $db "EXPLAIN QUERY PLAN $1"
>>   time sqlite3 $db "$1"
>> }
>>
>> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)"
>> q "SELECT count(*) FROM (SELECT * FROM (SELECT * FROM t1) zzz1 LEFT JOIN
>> t2 USING (a) LIMIT 1)"
>> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2)
>> zzz2 USING (a) LIMIT 1)"
>> q "SELECT count(*) FROM (SELECT * FROM t1 INNER JOIN (SELECT * FROM t2)
>> zzz2 USING (a) LIMIT 1)"
>>
>> ___
>> 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



[sqlite] Query flattening for left joins involving subqueries on the right-hand side

2015-11-26 Thread Kirill Müller
On 26.11.2015 21:12, Clemens Ladisch wrote:
> Kirill M?ller wrote:
>> On 25.11.2015 16:32, Clemens Ladisch wrote:
>>> Kirill M?ller wrote:
>>>> For a left join with a subquery on the right-hand side, that subquery
>>>> doesn't seem to be flattened.
>>> This is rule 3 of <http://www.sqlite.org/optoverview.html#flattening>.
>> I wonder if this rule might be relaxed a bit.
> Only if you relax your requirement that the results must be correct.
>
>
> In the general case, a left outer join can be rewritten like this:
>
>SELECT ... FROM A JOIN B ON ...
>UNION ALL
>SELECT ... FROM A WHERE NOT EXISTS (look up in B)
>
> This query would be more likely to be flattenable, but also be slower.
>
Thanks. Let's not focus on terminology -- I thought "flattening" was the 
right word to use, but it probably isn't. Of course I'm looking for 
correct results.

Originally, I attached a script but it seems that it's been stripped. 
I've pasted it below. I see no reason why the following two queries (1 
and 3 in the script) can't be executed with the same plans:

SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)
SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) zzz2 
USING (a) LIMIT 1)

This is for two tables t1 and t2 with a single column "a". The script 
creates them and populates them with 20 rows each.


-Kirill


#!/bin/bash

db=test.sqlite

#if false; then
rm -f $db

n=20

sqlite3 $db "CREATE TABLE t1 (a int primary key)"
seq 1 $n | sqlite3 $db ".import /dev/stdin t1"

sqlite3 $db "CREATE TABLE t2 (a int primary key)"
seq 1 $n | sqlite3 $db ".import /dev/stdin t2"
#fi

q() {
 sqlite3 $db "EXPLAIN QUERY PLAN $1"
 time sqlite3 $db "$1"
}

q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)"
q "SELECT count(*) FROM (SELECT * FROM (SELECT * FROM t1) zzz1 LEFT JOIN 
t2 USING (a) LIMIT 1)"
q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) 
zzz2 USING (a) LIMIT 1)"
q "SELECT count(*) FROM (SELECT * FROM t1 INNER JOIN (SELECT * FROM t2) 
zzz2 USING (a) LIMIT 1)"



[sqlite] Query flattening for left joins involving subqueries on the right-hand side

2015-11-26 Thread Kirill Müller
On 25.11.2015 16:32, Clemens Ladisch wrote:
> Kirill M?ller wrote:
>> For a left join with a subquery on the right-hand side, that subquery
>> doesn't seem to be flattened.
> This is rule 3 of <http://www.sqlite.org/optoverview.html#flattening>.
Thanks, missed that. While true, I wonder if this rule might be relaxed 
a bit. The SQL is produced by a very generic SQL query generator, which 
just uses subqueries for everything table-like.


-Kirill


[sqlite] Query flattening for left joins involving subqueries on the right-hand side

2015-11-24 Thread Kirill Müller
Hi


For a left join with a subquery on the right-hand side, that subquery 
doesn't seem to be flattened. This seems to work well with an inner join.

I have attached a reprex. It creates two tables with $n rows and one ID 
column each (200k is enough to show substantial slowdown), and joins 
them with and without subqueries. The third example seems to create a 
suboptimal query plan and takes much longer than necessary to run. The 
output on my system is below the message.

Thanks for your attention.


Best regards

Kirill



1|0|0|SCAN TABLE t1
0|0|0|SCAN SUBQUERY 1
1

real0m0.003s
user0m0.000s
sys0m0.000s
1|0|0|SCAN TABLE t1
0|0|0|SCAN SUBQUERY 1
1

real0m0.003s
user0m0.000s
sys0m0.000s
2|0|0|SCAN TABLE t2
1|0|0|SCAN TABLE t1
1|1|1|SEARCH SUBQUERY 2 AS zzz2 USING AUTOMATIC COVERING INDEX (a=?)
0|0|0|SCAN SUBQUERY 1
1

real0m0.277s
user0m0.264s
sys0m0.012s
1|0|0|SCAN TABLE t1
1|1|1|SEARCH TABLE t2 USING COVERING INDEX sqlite_autoindex_t2_1 (a=?)
0|0|0|SCAN SUBQUERY 1
1

real0m0.003s
user0m0.000s
sys0m0.000s



[sqlite] sqliteonline + Fiddle

2014-11-19 Thread Kirill
Good day,


New version sqliteonline.com

add fiddle
* easy creation of links
* complete preservation of the entire structure
* no registration
* limit db 100kb
* If within three months no complaints data deleted

http://sqliteonline.com/#fiddle-546c6e508557d5350601fd78e686aaefc9104419fdcde14468

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


[sqlite] sliteonline or sqlite in js

2014-11-12 Thread Kirill
Good day,

Full line manager to work with sqlite directly
from the browser on any platform:
http://sqliteonline.com/

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


[sqlite] bug: output contains values of a column not listed in SELECT

2010-03-31 Thread Kirill Simonov
Hi,

I encountered a weird bug.  My query has the form
 SELECT col_x, ... FROM my_table ... GROUP BY 1
but the output of the query looks as if it was produced by a query of 
the form
 SELECT col_y, ... FROM my_table ... GROUP BY 1
That is, instead of values of col_x, I'm getting values of col_y, which 
is not in the SELECT clause.


The following code reproduces the problem:
==
CREATE TABLE person (
 org_id  TEXT NOT NULL,
 nicknameTEXT NOT NULL,
 license TEXT,
 CONSTRAINT person_pk
 PRIMARY KEY (org_id, nickname),
 CONSTRAINT person_license_uk
 UNIQUE (license)
);

INSERT INTO person (org_id, nickname, license) VALUES
 ('meyers', 'jack', '2GAT123');
INSERT INTO person (org_id, nickname, license) VALUES
 ('meyers', 'hill', 'V345FMP');
INSERT INTO person (org_id, nickname, license) VALUES
 ('meyers', 'jim', '2GAT138');
INSERT INTO person (org_id, nickname, license) VALUES
 ('smith', 'maggy', '');
INSERT INTO person (org_id, nickname, license) VALUES
 ('smith', 'jose', 'JJZ109');
INSERT INTO person (org_id, nickname, license) VALUES
 ('smith', 'jack', 'THX138');
INSERT INTO person (org_id, nickname, license) VALUES
 ('lakeside', 'dave', '953OKG');
INSERT INTO person (org_id, nickname, license) VALUES
 ('lakeside', 'amy', NULL);
INSERT INTO person (org_id, nickname, license) VALUES
 ('lake-apts', 'tom', NULL);
INSERT INTO person (org_id, nickname, license) VALUES
 ('acorn', 'hideo', 'CQB421');

SELECT
 org_id,
 COUNT((NOT (org_id IS NULL)) AND (NOT (nickname IS NULL)))
FROM person
WHERE (CASE WHEN license != '' THEN 1 ELSE 0 END)
GROUP BY 1;
==

The final SELECT statement produces the following output:
953OKG|1
V345FMP|1
THX138|3
|2

which is totally incorrect.  Note that the values in the first column of 
the output are not from the column 'org_id', which is listed in the 
SELECT clause, but from the column 'license'.

The correct output is:
acorn|1
lakeside|1
meyers|3
smith|2

Minor modifications either in the query itself or in the table data or 
metadata make the problem disappear.

Tested with the latest sqlite binary from www.sqlite.org under Linux.


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


[sqlite] unicode like UPPER and LOWER ?

2007-05-07 Thread Kirill
Good day,

SQLite version 3.3.17
Enter ".help" for instructions
sqlite> create table tbl1(t1 varchar(10));
sqlite> insert into tbl1 values('софт'); - lowChar
sqlite> insert into tbl1 values('СОФТ'); - upChar
sqlite> select * from tbl1;
софт
СОФТ
sqlite> select * from tbl1 where t1 like '%оф%';  - lowChar
софт - lowChar

:(

what do?:

sqlite> select * from tbl1 where t1 like '%оф%';  - lowChar
софт - lowChar
СОФТ - upChar


-- 
Best regards,
  Kirill
  mailto:[EMAIL PROTECTED]




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



[sqlite] Mac OS

2006-02-14 Thread Kirill
Whether will be SqlLite in the future and under Mac OS if there will be that as 
soon?







Re[2]: [sqlite] To whom to inform on a bug?

2006-02-14 Thread Kirill
sorry :(

>> version:
>>   3.3.4 or 3.x
>> 
>> System test:
>>   Win2003(NTFS)
>> 
>> Script:
>>   select * from tResult where tex like '%ra%'
>>   result = 0
>> 
>>   select tex from tResult where id  = 3229
>>   tex = "...Oracle..."
>> 
>> Soft on broblem:
>>   sqlite3explorer,
>>   and my soft
>> 
>> sample db:
>>   http://www.aidagw.com/files/dba.zip
>> 

dhc> Thank you for the excellent information.

dhc> Your query works correctly when run in the
dhc> sqlite3 shell.  Probably this is a bug in
dhc> sqlite3explorer.

dhc> --
dhc> D. Richard Hipp   <[EMAIL PROTECTED]>







Re[2]: [sqlite] To whom to inform on a bug?

2006-02-14 Thread Kirill
version:
  3.3.4 or 3.x

System test:
  Win2003(NTFS)

Script:
  select * from tResult where tex like '%ra%'
  result = 0

  select tex from tResult where id  = 3229
  tex = "...Oracle..."

Soft on broblem:
  sqlite3explorer,
  and my soft

sample db:
  http://www.aidagw.com/files/dba.zip


dhc> http://www.sqlite.org/cvstrac/tktnew

dhc> Hints:  Unless you provide more information that you have
dhc> shown above, your ticket will likely be ignored.  The
dhc> following kinds of information will help us to isolate
dhc> and fix the problem:

dhc>* What version of SQLite you are running.
dhc>* What operating system you are using.
dhc>* Did you build it yourself or used a precompiled
dhc>  binary.
dhc>* What language bindings you are using.
dhc>* The database schema.
dhc>* The specific SQL statement that fails.
dhc>* Can you reproduce the problem using the command-line
dhc>  shell or does it only appear in your program?
dhc>* Include a reproducible script that demonstrates
dhc>  the problem.
dhc>* If appropriate, attach a ZIP archive of your database
dhc>  file to the ticket.

dhc> --
dhc> D. Richard Hipp   <[EMAIL PROTECTED]>









[sqlite] To whom to inform on a bug?

2006-02-14 Thread Kirill
hello

select * from tResult where tex like '%ra%'
result = 0

select tex from tResult where id  = 3229
tex = "...Oracle..."

bag?

-- 
Kirill