# SQLite3 Help

**URL:** <https://www.ruby-forum.com/t/sqlite3-help/70550>\
**Category:** Ruby\
**Created:** [September 30, 2006, 1:08am UTC](https://www.ruby-forum.com/t/sqlite3-help/70550 "2006-09-30T01:08:46Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![phrogz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/2acd7d/32.png) [@phrogz](https://www.ruby-forum.com/u/phrogz)\
**Post date:** [September 30, 2006, 1:08am UTC](https://www.ruby-forum.com/t/sqlite3-help/70550/1 "2006-09-30T01:08:46Z")

</div>

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

- 

sqlite\> 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

* * *