On 12/06/2011 03:28 PM, Max Vlasov wrote:
Hi,
Noticed a strange regression after 3.7.7 (in 3.7.8 and inherited in 3.7.9)
I have a Russian morphology database and different queries working with it.
I narrowed it to the following case and populated with a couple of English
words (just to make sense)
The following database
CREATE TABLE [Beginnings] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Title]
TEXT, [EndingId] INTEGER);
CREATE TABLE [Endings] (Id INT,Title TEXT,EndingId INT);
INSERT INTO Beginnings (Id, Title, EndingId) VALUES (1, 'FACTOR', 18);
INSERT INTO Beginnings (Id, Title, EndingId) VALUES (2, 'SWIMM', 18);
INSERT INTO Endings (Id, Title, EndingId) VALUES (1, 'ING', 18);
There's a query that searches for primary form of a united list of some
words (here FACTORING and SWIMMING):
SELECT
SrcWord, Beginnings.Title
FROM
(SELECT 'FACTORING' AS SrcWord UNION
SELECT 'SWIMMING' AS SrcWord )
LEFT JOIN
Beginnings
WHERE
Beginnings.Id=
(SELECT BeginningId FROM
(SELECT
SrcWord, B.Id as BeginningId, B.Title || E.Title As Connected
FROM
Beginnings B
LEFT JOIN
Endings E
ON
B.EndingId=E.EndingId
WHERE
Connected=SrcWord
LIMIT
1))
There is a candidate fix for this in fossil now.
Alternatively, a workaround with 3.7.9 is to remove the "LIMIT 1"
from the sub-query. Or replace the "LEFT JOIN" in the subquery with
a regular JOIN. The query then seems to work as expected.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users