Kirill Simonov wrote:
> The following code reproduces the problem:
> ==========
> CREATE TABLE person (
> org_id TEXT NOT NULL,
> nickname TEXT 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
Yes, reproduces here too. Certainly looks like a bug. If I simplify WHERE
clause to "where license != '' " then it works as expected.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
