I'm using SQLiteStudio.
It's not easy to run it manually because of how the parameters must be passed,
and the function that doesn't exist within the database.
I tried replacing all parameters by their value, and replacing the function
with a simple multiplication. It throws an error, but doesn't tell me anything
about the error. So it fails... but it could be because I made an error in
editing it, or because it is too long.
Here's the script I'm running.
Most likely I'm running into a limit of SQLite engine?
SELECT
[Project27].[MediaTypeId] AS [MediaTypeId],
[Project27].[MediaId] AS [MediaId],
[Project27].[Artist] AS [Artist],
[Project27].[Title] AS [Title],
[Project27].[Album] AS [Album],
[Project27].[MediaCategoryId] AS [MediaCategoryId],
[Project27].[FileName] AS [FileName],
[Project27].[Preference] AS [Preference],
[Project27].[C1] AS [C1],
[Project27].[C2] AS [C2],
[Project27].[BuyUrl] AS [BuyUrl],
[Project27].[C5] AS [C3],
[Project27].[C6] AS [C4],
[Project27].[C7] AS [C5],
[Project27].[C8] AS [C6],
[Project27].[C9] AS [C7],
[Project27].[C10] AS [C8],
[Project27].[C11] AS [C9],
[Project27].[C12] AS [C10],
[Project27].[C3] AS [C11],
[Project27].[C4] AS [C12]
FROM ( SELECT
[Project26].[MediaId] AS [MediaId],
[Project26].[MediaTypeId] AS [MediaTypeId],
[Project26].[Artist] AS [Artist],
[Project26].[Title] AS [Title],
[Project26].[Album] AS [Album],
[Project26].[MediaCategoryId] AS [MediaCategoryId],
[Project26].[FileName] AS [FileName],
[Project26].[Preference] AS [Preference],
[Project26].[BuyUrl] AS [BuyUrl],
[Project26].[Length] AS [C1],
CASE WHEN ('' <> [Project26].[DownloadUrl]) THEN 1 WHEN ('' =
[Project26].[DownloadUrl]) THEN 0 END AS [C2],
CASE WHEN (('' IS NULL) OR ((LENGTH('')) = 0)) THEN NULL ELSE
[Project26].[C9] END AS [C3],
1 AS [C4],
[Project26].[C1] AS [C5],
[Project26].[C2] AS [C6],
[Project26].[C3] AS [C7],
[Project26].[C4] AS [C8],
[Project26].[C5] AS [C9],
[Project26].[C6] AS [C10],
[Project26].[C7] AS [C11],
[Project26].[C8] AS [C12]
FROM ( SELECT
[Project24].[MediaId] AS [MediaId],
[Project24].[MediaTypeId] AS [MediaTypeId],
[Project24].[Artist] AS [Artist],
[Project24].[Title] AS [Title],
[Project24].[Album] AS [Album],
[Project24].[MediaCategoryId] AS [MediaCategoryId],
[Project24].[FileName] AS [FileName],
[Project24].[Preference] AS [Preference],
[Project24].[Length] AS [Length],
[Project24].[DownloadUrl] AS [DownloadUrl],
[Project24].[BuyUrl] AS [BuyUrl],
[Project24].[C1] AS [C1],
[Project24].[C2] AS [C2],
[Project24].[C3] AS [C3],
[Project24].[C4] AS [C4],
[Project24].[C5] AS [C5],
[Project24].[C6] AS [C6],
[Project24].[C7] AS [C7],
[Project24].[C8] AS [C8],
(SELECT
([Extent18].[Height] * [Extent18].[Depth]) AS [C1]
FROM [MediaRatings] AS [Extent18]
INNER JOIN [RatingCategories] AS [Extent19] ON
[Extent18].[RatingId] = [Extent19].[RatingId]
WHERE (([Project24].[MediaId] = [Extent18].[MediaId]) AND
(([Extent19].[Name] = '') OR (1 = 0))) AND ([Extent19].[Custom] = 1) LIMIT 1)
AS [C9]
FROM ( SELECT
[Project23].[MediaId] AS [MediaId],
[Project23].[MediaTypeId] AS [MediaTypeId],
[Project23].[Artist] AS [Artist],
[Project23].[Title] AS [Title],
[Project23].[Album] AS [Album],
[Project23].[MediaCategoryId] AS [MediaCategoryId],
[Project23].[FileName] AS [FileName],
[Project23].[Preference] AS [Preference],
[Project23].[Length] AS [Length],
[Project23].[DownloadUrl] AS [DownloadUrl],
[Project23].[BuyUrl] AS [BuyUrl],
[Project23].[C1] AS [C1],
[Project23].[C2] AS [C2],
[Project23].[C3] AS [C3],
[Project23].[C4] AS [C4],
[Project23].[C5] AS [C5],
[Project23].[C6] AS [C6],
[Project23].[C7] AS [C7],
[Project23].[C8] AS [C8]
FROM ( SELECT
[Project21].[MediaId] AS [MediaId],
[Project21].[MediaTypeId] AS [MediaTypeId],
[Project21].[Artist] AS [Artist],
[Project21].[Title] AS [Title],
[Project21].[Album] AS [Album],
[Project21].[MediaCategoryId] AS [MediaCategoryId],
[Project21].[FileName] AS [FileName],
[Project21].[Preference] AS [Preference],
[Project21].[Length] AS [Length],
[Project21].[DownloadUrl] AS [DownloadUrl],
[Project21].[BuyUrl] AS [BuyUrl],
[Project21].[C1] AS [C1],
[Project21].[C2] AS [C2],
[Project21].[C3] AS [C3],
[Project21].[C4] AS [C4],
[Project21].[C5] AS [C5],
[Project21].[C6] AS [C6],
[Project21].[C7] AS [C7],
(SELECT
([Extent16].[Height] * [Extent16].[Depth] AS [C1]
FROM [MediaRatings] AS [Extent16]
INNER JOIN [RatingCategories] AS [Extent17] ON
[Extent16].[RatingId] = [Extent17].[RatingId]
WHERE ([Project21].[MediaId] = [Extent16].[MediaId]) AND
('Egoless' = [Extent17].[Name]) LIMIT 1) AS [C8]
FROM ( SELECT
[Project20].[MediaId] AS [MediaId],
[Project20].[MediaTypeId] AS [MediaTypeId],
[Project20].[Artist] AS [Artist],
[Project20].[Title] AS [Title],
[Project20].[Album] AS [Album],
[Project20].[MediaCategoryId] AS [MediaCategoryId],
[Project20].[FileName] AS [FileName],
[Project20].[Preference] AS [Preference],
[Project20].[Length] AS [Length],
[Project20].[DownloadUrl] AS [DownloadUrl],
[Project20].[BuyUrl] AS [BuyUrl],
[Project20].[C1] AS [C1],
[Project20].[C2] AS [C2],
[Project20].[C3] AS [C3],
[Project20].[C4] AS [C4],
[Project20].[C5] AS [C5],
[Project20].[C6] AS [C6],
[Project20].[C7] AS [C7]
FROM ( SELECT
[Project18].[MediaId] AS [MediaId],
[Project18].[MediaTypeId] AS [MediaTypeId],
[Project18].[Artist] AS [Artist],
[Project18].[Title] AS [Title],
[Project18].[Album] AS [Album],
[Project18].[MediaCategoryId] AS [MediaCategoryId],
[Project18].[FileName] AS [FileName],
[Project18].[Preference] AS [Preference],
[Project18].[Length] AS [Length],
[Project18].[DownloadUrl] AS [DownloadUrl],
[Project18].[BuyUrl] AS [BuyUrl],
[Project18].[C1] AS [C1],
[Project18].[C2] AS [C2],
[Project18].[C3] AS [C3],
[Project18].[C4] AS [C4],
[Project18].[C5] AS [C5],
[Project18].[C6] AS [C6],
(SELECT
([Extent14].[Height] * [Extent14].[Depth]) AS [C1]
FROM [MediaRatings] AS [Extent14]
INNER JOIN [RatingCategories] AS [Extent15] ON
[Extent14].[RatingId] = [Extent15].[RatingId]
WHERE ([Project18].[MediaId] =
[Extent14].[MediaId]) AND ('Love' = [Extent15].[Name]) LIMIT 1) AS [C7]
FROM ( SELECT
[Project17].[MediaId] AS [MediaId],
[Project17].[MediaTypeId] AS [MediaTypeId],
[Project17].[Artist] AS [Artist],
[Project17].[Title] AS [Title],
[Project17].[Album] AS [Album],
[Project17].[MediaCategoryId] AS [MediaCategoryId],
[Project17].[FileName] AS [FileName],
[Project17].[Preference] AS [Preference],
[Project17].[Length] AS [Length],
[Project17].[DownloadUrl] AS [DownloadUrl],
[Project17].[BuyUrl] AS [BuyUrl],
[Project17].[C1] AS [C1],
[Project17].[C2] AS [C2],
[Project17].[C3] AS [C3],
[Project17].[C4] AS [C4],
[Project17].[C5] AS [C5],
[Project17].[C6] AS [C6]
FROM ( SELECT
[Project15].[MediaId] AS [MediaId],
[Project15].[MediaTypeId] AS [MediaTypeId],
[Project15].[Artist] AS [Artist],
[Project15].[Title] AS [Title],
[Project15].[Album] AS [Album],
[Project15].[MediaCategoryId] AS
[MediaCategoryId],
[Project15].[FileName] AS [FileName],
[Project15].[Preference] AS [Preference],
[Project15].[Length] AS [Length],
[Project15].[DownloadUrl] AS [DownloadUrl],
[Project15].[BuyUrl] AS [BuyUrl],
[Project15].[C1] AS [C1],
[Project15].[C2] AS [C2],
[Project15].[C3] AS [C3],
[Project15].[C4] AS [C4],
[Project15].[C5] AS [C5],
(SELECT
([Extent12].[Height] * [Extent12].[Depth])
AS [C1]
FROM [MediaRatings] AS [Extent12]
INNER JOIN [RatingCategories] AS [Extent13]
ON [Extent12].[RatingId] = [Extent13].[RatingId]
WHERE ([Project15].[MediaId] =
[Extent12].[MediaId]) AND ('Spiritual Feminine' = [Extent13].[Name]) LIMIT 1)
AS [C6]
FROM ( SELECT
[Project14].[MediaId] AS [MediaId],
[Project14].[MediaTypeId] AS [MediaTypeId],
[Project14].[Artist] AS [Artist],
[Project14].[Title] AS [Title],
[Project14].[Album] AS [Album],
[Project14].[MediaCategoryId] AS
[MediaCategoryId],
[Project14].[FileName] AS [FileName],
[Project14].[Preference] AS [Preference],
[Project14].[Length] AS [Length],
[Project14].[DownloadUrl] AS [DownloadUrl],
[Project14].[BuyUrl] AS [BuyUrl],
[Project14].[C1] AS [C1],
[Project14].[C2] AS [C2],
[Project14].[C3] AS [C3],
[Project14].[C4] AS [C4],
[Project14].[C5] AS [C5]
FROM ( SELECT
[Project12].[MediaId] AS [MediaId],
[Project12].[MediaTypeId] AS
[MediaTypeId],
[Project12].[Artist] AS [Artist],
[Project12].[Title] AS [Title],
[Project12].[Album] AS [Album],
[Project12].[MediaCategoryId] AS
[MediaCategoryId],
[Project12].[FileName] AS [FileName],
[Project12].[Preference] AS
[Preference],
[Project12].[Length] AS [Length],
[Project12].[DownloadUrl] AS
[DownloadUrl],
[Project12].[BuyUrl] AS [BuyUrl],
[Project12].[C1] AS [C1],
[Project12].[C2] AS [C2],
[Project12].[C3] AS [C3],
[Project12].[C4] AS [C4],
(SELECT
([Extent10].[Height] *
[Extent10].[Depth]) AS [C1]
FROM [MediaRatings] AS [Extent10]
INNER JOIN [RatingCategories] AS
[Extent11] ON [Extent10].[RatingId] = [Extent11].[RatingId]
WHERE ([Project12].[MediaId] =
[Extent10].[MediaId]) AND ('Spiritual Masculine' = [Extent11].[Name]) LIMIT 1)
AS [C5]
FROM ( SELECT
[Project11].[MediaId] AS [MediaId],
[Project11].[MediaTypeId] AS
[MediaTypeId],
[Project11].[Artist] AS [Artist],
[Project11].[Title] AS [Title],
[Project11].[Album] AS [Album],
[Project11].[MediaCategoryId] AS
[MediaCategoryId],
[Project11].[FileName] AS
[FileName],
[Project11].[Preference] AS
[Preference],
[Project11].[Length] AS [Length],
[Project11].[DownloadUrl] AS
[DownloadUrl],
[Project11].[BuyUrl] AS [BuyUrl],
[Project11].[C1] AS [C1],
[Project11].[C2] AS [C2],
[Project11].[C3] AS [C3],
[Project11].[C4] AS [C4]
FROM ( SELECT
[Project9].[MediaId] AS
[MediaId],
[Project9].[MediaTypeId] AS
[MediaTypeId],
[Project9].[Artist] AS
[Artist],
[Project9].[Title] AS [Title],
[Project9].[Album] AS [Album],
[Project9].[MediaCategoryId] AS
[MediaCategoryId],
[Project9].[FileName] AS
[FileName],
[Project9].[Preference] AS
[Preference],
[Project9].[Length] AS
[Length],
[Project9].[DownloadUrl] AS
[DownloadUrl],
[Project9].[BuyUrl] AS
[BuyUrl],
[Project9].[C1] AS [C1],
[Project9].[C2] AS [C2],
[Project9].[C3] AS [C3],
(SELECT
([Extent8].[Height] *
[Extent8].[Depth]) AS [C1]
FROM [MediaRatings] AS
[Extent8]
INNER JOIN
[RatingCategories] AS [Extent9] ON [Extent8].[RatingId] = [Extent9].[RatingId]
WHERE ([Project9].[MediaId]
= [Extent8].[MediaId]) AND ('Emotional Feminine' = [Extent9].[Name]) LIMIT 1)
AS [C4]
FROM ( SELECT
[Project8].[MediaId] AS
[MediaId],
[Project8].[MediaTypeId] AS
[MediaTypeId],
[Project8].[Artist] AS
[Artist],
[Project8].[Title] AS
[Title],
[Project8].[Album] AS
[Album],
[Project8].[MediaCategoryId] AS [MediaCategoryId],
[Project8].[FileName] AS
[FileName],
[Project8].[Preference] AS
[Preference],
[Project8].[Length] AS
[Length],
[Project8].[DownloadUrl] AS
[DownloadUrl],
[Project8].[BuyUrl] AS
[BuyUrl],
[Project8].[C1] AS [C1],
[Project8].[C2] AS [C2],
[Project8].[C3] AS [C3]
FROM ( SELECT
[Project6].[MediaId] AS
[MediaId],
[Project6].[MediaTypeId] AS [MediaTypeId],
[Project6].[Artist] AS
[Artist],
[Project6].[Title] AS
[Title],
[Project6].[Album] AS
[Album],
[Project6].[MediaCategoryId] AS [MediaCategoryId],
[Project6].[FileName]
AS [FileName],
[Project6].[Preference]
AS [Preference],
[Project6].[Length] AS
[Length],
[Project6].[DownloadUrl] AS [DownloadUrl],
[Project6].[BuyUrl] AS
[BuyUrl],
[Project6].[C1] AS
[C1],
[Project6].[C2] AS
[C2],
(SELECT
([Extent6].[Height]
* [Extent6].[Depth]) AS [C1]
FROM
[MediaRatings] AS [Extent6]
INNER JOIN
[RatingCategories] AS [Extent7] ON [Extent6].[RatingId] = [Extent7].[RatingId]
WHERE
([Project6].[MediaId] = [Extent6].[MediaId]) AND ('Emotional Masculine' =
[Extent7].[Name]) LIMIT 1) AS [C3]
FROM ( SELECT
[Project5].[MediaId] AS [MediaId],
[Project5].[MediaTypeId] AS [MediaTypeId],
[Project5].[Artist]
AS [Artist],
[Project5].[Title]
AS [Title],
[Project5].[Album]
AS [Album],
[Project5].[MediaCategoryId] AS [MediaCategoryId],
[Project5].[FileName] AS [FileName],
[Project5].[Preference] AS [Preference],
[Project5].[Length]
AS [Length],
[Project5].[DownloadUrl] AS [DownloadUrl],
[Project5].[BuyUrl]
AS [BuyUrl],
[Project5].[C1] AS
[C1],
[Project5].[C2] AS
[C2]
FROM ( SELECT
[Project3].[MediaId] AS [MediaId],
[Project3].[MediaTypeId] AS [MediaTypeId],
[Project3].[Artist] AS [Artist],
[Project3].[Title] AS [Title],
[Project3].[Album] AS [Album],
[Project3].[MediaCategoryId] AS [MediaCategoryId],
[Project3].[FileName] AS [FileName],
[Project3].[Preference] AS [Preference],
[Project3].[Length] AS [Length],
[Project3].[DownloadUrl] AS [DownloadUrl],
[Project3].[BuyUrl] AS [BuyUrl],
[Project3].[C1]
AS [C1],
(SELECT
([Extent4].[Height] * [Extent4].[Depth]) AS [C1]
FROM
[MediaRatings] AS [Extent4]
INNER JOIN
[RatingCategories] AS [Extent5] ON [Extent4].[RatingId] = [Extent5].[RatingId]
WHERE
([Project3].[MediaId] = [Extent4].[MediaId]) AND ('Physical Feminine' =
[Extent5].[Name]) LIMIT 1) AS [C2]
FROM ( SELECT
[Project2].[MediaId] AS [MediaId],
[Project2].[MediaTypeId] AS [MediaTypeId],
[Project2].[Artist] AS [Artist],
[Project2].[Title] AS [Title],
[Project2].[Album] AS [Album],
[Project2].[MediaCategoryId] AS [MediaCategoryId],
[Project2].[FileName] AS [FileName],
[Project2].[Preference] AS [Preference],
[Project2].[Length] AS [Length],
[Project2].[DownloadUrl] AS [DownloadUrl],
[Project2].[BuyUrl] AS [BuyUrl],
[Project2].[C1] AS [C1]
FROM (
SELECT
[Extent1].[MediaId] AS [MediaId],
[Extent1].[MediaTypeId] AS [MediaTypeId],
[Extent1].[Artist] AS [Artist],
[Extent1].[Title] AS [Title],
[Extent1].[Album] AS [Album],
[Extent1].[MediaCategoryId] AS [MediaCategoryId],
[Extent1].[FileName] AS [FileName],
[Extent1].[Preference] AS [Preference],
[Extent1].[Length] AS [Length],
[Extent1].[DownloadUrl] AS [DownloadUrl],
[Extent1].[BuyUrl] AS [BuyUrl],
(SELECT
([Extent2].[Height] * [Extent2].[Depth]) AS [C1]
FROM [MediaRatings] AS [Extent2]
INNER JOIN [RatingCategories] AS [Extent3] ON [Extent2].[RatingId] =
[Extent3].[RatingId]
WHERE ([Extent1].[MediaId] = [Extent2].[MediaId]) AND ('Physical Masculine' =
[Extent3].[Name]) LIMIT 1) AS [C1]
FROM
[Media] AS [Extent1]
WHERE
[Extent1].[MediaTypeId] = 0
) AS
[Project2]
) AS [Project3]
) AS [Project5]
) AS [Project6]
) AS [Project8]
) AS [Project9]
) AS [Project11]
) AS [Project12]
) AS [Project14]
) AS [Project15]
) AS [Project17]
) AS [Project18]
) AS [Project20]
) AS [Project21]
) AS [Project23]
) AS [Project24]
) AS [Project26]
) AS [Project27]
ORDER BY [Project27].[C4] DESC, [Project27].[Artist] ASC, [Project27].[Title]
ASC
> From: slavins at bigfraud.org
> Date: Sat, 30 May 2015 01:40:23 +0100
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Query returns "stack overflow error"
>
>
> On 30 May 2015, at 1:26am, Etienne Charland <mysteryx93 at hotmail.com> wrote:
>
> > there are requests that are very long because they return a grid where each
> > column must be calculated individually, and this crashes with "stack
> > overflow error". These queries run fast on a SQL Server database -- and not
> > at all with SQLite.
> >
> > How can I get these queries to run?
>
> Does the query run if you submit it with the SQLite shell tool ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
