Martin,
Thank you for looking into this. The SQLite3.exe I tested with was 3.1.3.
And yes the SQL statement works fine as it. If you add one more nested OR to
the SQL statement then it stops working. I don't get a stack overflow like I
do in the ODBC driver I was testing with but it errors with a syntax error
about the "=".
Change the last to lines to:
Customer.FullName = 'Okami' OR (
Customer.FullName = 'Okami2' OR (
Customer.FullName = 'Quickspice, Inc.')))))))))))))))))))))))))))))
Matt Froncek
QODBC Development Support / FLEXquarters.com LLC Consultant
QODBC Driver for QuickBooks - Unleash your data at www.qodbc.com
-----Original Message-----
From: Martin Jenkins [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 20, 2007 10:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Nested Parens Stack Overflow
Matt Froncek wrote:
> I have a program generator that creates ORs in SQL nested. This causes a
> stack overflow in SQLite. Has this been addressed or will it be? If so
what
> version was it addressed. I am not sure how to search for the answer to
this
> question.
You ought to post a schema and some sample data and say what version is
failing. I spent about 20 minutes hacking your SQL into a sample script
and the following works with sqlite3 v3.3.13. You might have to fix the
line endings for the sales line inserts but at least there shouldn't be
any embedded spaces. Ahem. ;) Sample output is at the end.
HTH
Martin
Paste this into pp.sql and then "sqlite3 pp.db < pp.sql"
---------------------------- cut ----------------------------
DROP TABLE IF EXISTS Item;
CREATE TABLE Item(_Type, FullName, ListID);
INSERT INTO Item VALUES ('ItemInventory', 'FullName0', 0);
INSERT INTO Item VALUES ('ItemInventory', 'FullName1', 1);
INSERT INTO Item VALUES ('ItemInventory', 'FullName2', 2);
INSERT INTO Item VALUES ('ItemInventory', 'FullName3', 3);
DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer(ListID, CustomFieldLevel, FullName);
INSERT INTO Customer VALUES(1, 'CFL123', 'Amazon.com - Campbellsville');
INSERT INTO Customer VALUES(2, 'CFL123', 'Amazon.com - Coffeyville');
INSERT INTO Customer VALUES(3, 'CFL123', 'Amazon.com - Fernley');
INSERT INTO Customer VALUES(4, 'CFL123', 'Arizona Select');
INSERT INTO Customer VALUES(5, 'CFL123', 'ATW - Cobb Dist.');
INSERT INTO Customer VALUES(6, 'CFL123', 'SunOpta Food Distribution');
INSERT INTO Customer VALUES(7, 'CFL123', 'Callaway Consumer Products');
INSERT INTO Customer VALUES(8, 'CFL123', 'Cedarlane Natural Foods');
INSERT INTO Customer VALUES(9, 'CFL123', 'City Glatt, Inc.');
INSERT INTO Customer VALUES(10, 'CFL123', 'Columbus Distributing');
INSERT INTO Customer VALUES(11, 'CFL123', 'Cost Plus World');
INSERT INTO Customer VALUES(12, 'CFL123', 'DeKalb Farmers Market');
INSERT INTO Customer VALUES(13, 'CFL123', 'DPI-Midwest');
INSERT INTO Customer VALUES(14, 'CFL123', 'DPI-Northwest');
INSERT INTO Customer VALUES(15, 'CFL123', 'DPI-Rocky Mountain');
INSERT INTO Customer VALUES(16, 'CFL123', 'DPI-West');
INSERT INTO Customer VALUES(17, 'CFL123', 'Exel Pak');
INSERT INTO Customer VALUES(18, 'CFL123', 'Falcon Trading Co.');
INSERT INTO Customer VALUES(19, 'CFL123', 'Foodguys');
INSERT INTO Customer VALUES(20, 'CFL123', 'Foodology');
INSERT INTO Customer VALUES(21, 'CFL123', 'Gourmet Awards - Milwaukee');
INSERT INTO Customer VALUES(22, 'CFL123', 'Kehe Foods');
INSERT INTO Customer VALUES(23, 'CFL123', 'Marc Popcorn Company');
INSERT INTO Customer VALUES(24, 'CFL123', 'Marukai Markets');
INSERT INTO Customer VALUES(25, 'CFL123', 'Matsukas Food Company');
INSERT INTO Customer VALUES(26, 'CFL123', 'McCain Foods USA, Inc.');
INSERT INTO Customer VALUES(27, 'CFL123', 'Okami');
INSERT INTO Customer VALUES(28, 'CFL123', 'Quickspice, Inc.');
DROP TABLE IF EXISTS SalesLine;
CREATE TABLE SalesLine(SalesLineDesc, SalesLineItemRefFullName, TxnDate,
SalesLineAmount, _Type, SalesLineItemRefListID, CustomerRefListID);
INSERT INTO SalesLine VALUES ('SalesLineDesc',
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 1, 9);
INSERT INTO SalesLine VALUES ('SalesLineDesc',
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 2, 9);
INSERT INTO SalesLine VALUES ('SalesLineDesc',
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 3, 9);
INSERT INTO SalesLine VALUES ('SalesLineDesc',
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 4, 9);
SELECT SalesLine.ROWID AS FQROWID,
Item.FullName AS FQALIAS_1,
Item._Type AS FQALIAS_2,
Customer.CustomFieldLevel AS FQALIAS_3,
Customer.FullName AS FQALIAS_4,
SalesLine.SalesLineDesc AS FQALIAS_5,
SalesLine.SalesLineItemRefFullName AS FQALIAS_6,
SalesLine.TxnDate AS FQALIAS_7,
SalesLine.SalesLineAmount AS FQALIAS_8,
SalesLine._Type AS FQALIAS_9
FROM SalesLine SalesLine
LEFT OUTER JOIN Item Item ON Item.ListID = SalesLine.SalesLineItemRefListID
LEFT OUTER JOIN Customer Customer ON Customer.ListID =
SalesLine.CustomerRefListID
WHERE
(
((SalesLine.TxnDate >= '2006-01-01') AND
(SalesLine.TxnDate <= '2006-09-30'))
AND
((Item._Type = 'ItemInventory') AND
( Customer.FullName = 'Amazon.com.ksdc, Inc. - Campbellsville' OR (
Customer.FullName = 'Amazon.com.ksdc, Inc. - Coffeyville' OR (
Customer.FullName = 'Amazon.com.ksdc, Inc. - Fernley' OR (
Customer.FullName = 'Arizona Select' OR (
Customer.FullName = 'ATW - Cobb Dist.' OR (
Customer.FullName = 'SunOpta Food Distribution Group' OR (
Customer.FullName = 'Callaway Consumer Products, LLC' OR (
Customer.FullName = 'Cedarlane Natural Foods' OR (
Customer.FullName = 'City Glatt, Inc.' OR (
Customer.FullName = 'Columbus Distributing, Inc.' OR (
Customer.FullName = 'Cost Plus World Markets-West' OR (
Customer.FullName = 'DeKalb Farmers Market' OR (
Customer.FullName = 'DPI-Midwest' OR (
Customer.FullName = 'DPI-Northwest' OR (
Customer.FullName = 'DPI-Rocky Mountain' OR (
Customer.FullName = 'DPI-West' OR (
Customer.FullName = 'Exel Pak' OR (
Customer.FullName = 'Falcon Trading Co.' OR (
Customer.FullName = 'Foodguys' OR (
Customer.FullName = 'Foodology' OR (
Customer.FullName = 'Gourmet Awards - Milwaukee' OR (
Customer.FullName = 'Kehe Foods' OR (
Customer.FullName = 'Marc Popcorn Company' OR (
Customer.FullName = 'Marukai Markets' OR (
Customer.FullName = 'Matsukas Food Company' OR (
Customer.FullName = 'McCain Foods USA, Inc.' OR (
Customer.FullName = 'Okami' OR (
Customer.FullName = 'Quickspice, Inc.'))))))))))))))))))))))))))))
)
);
---------------------------- cut ----------------------------
FQROWID FQALIAS_1 FQALIAS_2 FQALIAS_3 FQALIAS_4
FQALIAS_5 FQALIAS_6 FQALIAS_7 FQALIAS_8
FQALIAS_9
---------- ---------- ------------- ---------- ----------------
------------- ------------------------ ---------- ----------
-------------
1 FullName1 ItemInventory CFL123 City Glatt, Inc.
SalesLineDesc SalesLineItemRefFullName 2006-09-01 123.45
InventoryItem
2 FullName2 ItemInventory CFL123 City Glatt, Inc.
SalesLineDesc SalesLineItemRefFullName 2006-09-01 123.45
InventoryItem
3 FullName3 ItemInventory CFL123 City Glatt, Inc.
SalesLineDesc SalesLineItemRefFullName 2006-09-01 123.45
InventoryItem
---------------------------- cut ----------------------------
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
