The following SQL produces an incorrect result with sqlite-3.8.7.1:
CREATE TABLE A(
symbol TEXT,
type TEXT
);
INSERT INTO A VALUES('ABCDEFG','chars');
INSERT INTO A VALUES('1234567890','num');
CREATE TABLE B(
chars TEXT,
num TEXT
);
CREATE TABLE IF NOT EXISTS C AS
SELECT A.symbol AS symbol,A.type,
CASE A.type
WHEN 'chars' THEN A.symbol
WHEN 'num' THEN B.chars
ELSE NULL
END AS chars
FROM A LEFT OUTER JOIN B ON A.type='num' AND B.num=A.symbol;
SELECT * FROM C;
with 3.8.7.1:
sqlite> SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|1234567
with 3.8.6:
sqlite> SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|
--
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you should not
disseminate, distribute, alter or copy this e-mail. Please notify the
sender immediately by e-mail if you have received this e-mail by mistake
and delete this e-mail from your system. E-mail transmissions cannot be
guaranteed to be secure or without error as information could be
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The
sender, therefore, does not accept liability for any errors or omissions in
the contents of this message which arise during or as a result of e-mail
transmission. If verification is required, please request a hard-copy
version. This message is provided for information purposes and should not
be construed as a solicitation or offer to buy or sell any securities or
related financial instruments in any jurisdiction.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
