From: Gavin Kistner <gavin.kistner@...>
Date: 2006-09-30T08:08:04+09:00
Subject: SQLite3 Help

------_=_NextPart_001_01C6E41C.174F22E5
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have a web app (not rails) that is using sqlite3 directly. (Almost)
all queries are working, all is largely well.
=20
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.
=20
Can anyone think why this might be happening?
=20
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.)
=20
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=20
 INNER JOIN statuses ON bugs.status_id =3D statuses.id
 INNER JOIN users users1 ON bugs.fixer_user_id =3D users1.id
 INNER JOIN users users2 ON bugs.owner_user_id =3D users2.id
 LEFT OUTER JOIN (
  SELECT bug_id, SUM(number) AS votes
  FROM votes
  GROUP BY bug_id=20
 ) vote_totals ON bugs.id =3D vote_totals.bug_id
 WHERE ( bugs.status_id=3D2 or bugs.status_id=3D3 );
=20
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 =3D id
)
GROUP BY id
ORDER BY total_score DESC
=20

Ruby Code
------------------------------------------------------------------------
-
db =3D SQLite3::Database.new( "bugdb" )
puts theSQL
$theBugs =3D db.execute( theSQL )
p $theBugs
#=3D> []
=20

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 =3D statuses.id INNER JOIN users users1
ON bugs.fixer_user_id =3D users1.id INNER JOIN users users2 ON
bugs.owner_user_id =3D users2.id LEFT OUTER JOIN ( SELECT bug_id,
SUM(number) AS votes FROM votes GROUP BY bug_id ) vote_totals ON
bugs.id =3D vote_totals.bug_id WHERE ( bugs.status_id=3D2 or
bugs.status_id=3D3 );
=20
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 =3D id ) GROUP BY id ORDER BY total_score DESC;
=20
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
=20
=20
=20
=20

------_=_NextPart_001_01C6E41C.174F22E5--