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