I have a web app (not rails) that is using sqlite3 directly. (Almost)
all queries are working, all is largely well.
However, I am trying to use a temp table as part of a complex query, and
the web app isn't returning any results. (Specifically, the query is
running but returning no rows.) I'd chalk this up to bad SQL, but if I
copy/paste the query into a command-line sqlite3 process, it returns the
results I expect.
Can anyone think why this might be happening?
Following is the query text produced by the web app. To be sure, this
text is spat out to the HTML page, and I directly copy/pasted it into
the command window. Following the SQL is the ruby code that is being run
(that is not returning what is desired). Following that is the output of
the SQL command line to prove that I'm not insane. (And yes, both the
website and the command line are working on the exact same db file.)
SQL Code
ยทยทยท
------------------------------------------------------------------------
-
CREATE TEMP TABLE filtered_bugs AS SELECT
bugs.id as id, bugs.name AS name, bugs.description AS description,
bugs.datetime AS datetime, bugs.maturity AS maturity,
vote_totals.votes AS votes,
( bugs.priority * bugs.severity * bugs.likelihood + bugs.maturity * 10
+ COALESCE( votes, 0 ) ) AS pain,
statuses.description AS status_name,
users1.name AS fixer_name,
users2.name AS owner_name
FROM bugs
INNER JOIN statuses ON bugs.status_id = statuses.id
INNER JOIN users users1 ON bugs.fixer_user_id = users1.id
INNER JOIN users users2 ON bugs.owner_user_id = users2.id
LEFT OUTER JOIN (
SELECT bug_id, SUM(number) AS votes
FROM votes
GROUP BY bug_id
) vote_totals ON bugs.id = vote_totals.bug_id
WHERE ( bugs.status_id=2 or bugs.status_id=3 );
SELECT
SUM( score ) AS total_score,
id, name, datetime, maturity, votes, pain,
status_name, fixer_name, owner_name
FROM
(
SELECT
20 as score,
id, name, datetime, maturity, votes, pain,
status_name, fixer_name, owner_name
FROM filtered_bugs
WHERE name LIKE '%bump%'
UNION
SELECT
10 as score,
id, name, datetime, maturity, votes, pain,
status_name, fixer_name, owner_name
FROM filtered_bugs
WHERE description LIKE '%bump%'
UNION
SELECT
comment_count as score,
id, name, datetime, maturity, votes, pain,
status_name, fixer_name, owner_name
FROM filtered_bugs INNER JOIN
(
SELECT
bug_id, count( bug_id ) AS comment_count
FROM comments
WHERE commenttext LIKE '%bump%'
GROUP BY bug_id
) comment_group ON comment_group.bug_id = id
)
GROUP BY id
ORDER BY total_score DESC
Ruby Code
------------------------------------------------------------------------
-
db = SQLite3::Database.new( "bugdb" )
puts theSQL
$theBugs = db.execute( theSQL )
p $theBugs
#=>
SQLite3
------------------------------------------------------------------------
-
CREATE TEMP TABLE filtered_bugs AS SELECT bugs.id as id,
bugs.name AS name, bugs.description AS description,
bugs.datetime AS datetime, bugs.maturity AS maturity,
vote_totals.votes AS votes,
( bugs.priority * bugs.severity * bugs.likelihood + bugs.maturity *
10 + COALESCE( votes, 0 ) ) AS pain, statuses.description AS
status_name,
users1.name AS fixer_name, users2.name AS owner_name FROM bugs INNER
JOIN
statuses ON bugs.status_id = statuses.id INNER JOIN users users1
ON bugs.fixer_user_id = users1.id INNER JOIN users users2 ON
bugs.owner_user_id = users2.id LEFT OUTER JOIN ( SELECT bug_id,
SUM(number) AS votes FROM votes GROUP BY bug_id ) vote_totals ON
bugs.id = vote_totals.bug_id WHERE ( bugs.status_id=2 or
bugs.status_id=3 );
SELECT SUM( score ) AS total_score, id, name, datetime, maturity, votes,
pain, status_name, fixer_name, owner_name FROM ( SELECT 20 as score, id,
name, datetime, maturity, votes, pain, status_name, fixer_name,
owner_name
FROM filtered_bugs WHERE name LIKE '%bump%' UNION SELECT 10 as score,
id,
name, datetime, maturity, votes, pain, status_name, fixer_name,
owner_name
FROM filtered_bugs WHERE description LIKE '%bump%' UNION SELECT
comment_count as score, id, name, datetime, maturity, votes, pain,
status_name, fixer_name, owner_name FROM filtered_bugs INNER JOIN (
SELECT bug_id, count( bug_id ) AS comment_count FROM comments WHERE
commenttext LIKE '%bump%' GROUP BY bug_id ) comment_group ON
comment_group.bug_id = id ) GROUP BY id ORDER BY total_score DESC;
tot id name datetime matu votes pain stat fixer_n
owner
--- --- ---------- ---------- ---- ----- ---- ---- -------
-----
34 941 Bump mappi --- 2006-0 0 36 New Unknown Matt
10 917 Zoom to Ex --- 2006-0 0 8 New Unknown
Jason
1 160 Clean up t --- 2005-0 0 5 29 New Unknown
Gavin