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,
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
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
