choosing my poison: sqlite problems

rjbs on 2005-04-12T13:34:38

I really, really like DBD::SQLite, but I wish it didn't have such irritating little quirks. I'm not sure how much of the problem is SQLite and how much is DBD::SQLite, but it drives me batty. Here's today's example:

In Rubric, you can query for entries with a given set of tags, or you can specify you want exact_tags, and it queries for entries with /only/ those tags. I do that by using the normal "has these tags" query and then checking the number of total tags against the requested set. It's simple, though maybe not elegant.

I had written the count-checking clause like this:

(SELECT COUNT(tag) FROM entrytags WHERE entry = entries.id) = $count

This should have worked, but with DBD::SQLite I kept getting the error that there was no such column as entries.id. I'm fairly certain that the problem is that the subselect is not run in the context of the containing select, so it can't see the entries table against the tuples of which it should be comparing entrytags tuples.

I've had to code around this subselection bug once or twice before, and I was sick of it. I saw there was a newer DBD::SQLite, so I updated.

Then another bug showed up. In Rubric::User, I say the following:

__PACKAGE__->set_sql(tags_counted => <<'' );
  SELECT DISTINCT tag, COUNT(*) AS count
  FROM entrytags
  WHERE entry IN (SELECT id FROM entries WHERE user = ?)
  GROUP BY tag
  ORDER BY tag

This has always worked fine, but with DBD::SQLite 1.08 it seemed to stop quoting usernames, so I get DBIx::ContextualFetch complaining that it's getting a non-numeric value. I don't know just what's going on there, but something that was working now isn't. Argh!

So, I just went back to 1.07 and rewrote my count as follows:

id IN (SELECT entry FROM entrytags GROUP BY entry HAVING COUNT(tag) = $count)

---

SQLite

runrig on 2005-04-12T16:19:29

SELECT DISTINCT tag, COUNT(*) AS count
FROM entrytags
WHERE entry IN (SELECT id FROM entries WHERE user = ?)
GROUP BY tag
ORDER BY tag

That works for me. When you say it doesn't seem to quote user, do you mean user has spaces, or some other weird characters? I did try spaces in 'user' and it still seemed to work. Got any specific data?

Re:SQLite

rjbs on 2005-04-12T16:51:18

I mean that the value being passed to replace the ? as a bind parameter is potentially not being quoted. I need to investigate further to be sure what's happening.

Re:

Aristotle on 2005-04-12T18:17:10

How about keeping a window open on SQL As Understood By SQLite? expression shows that its parser expects subselects only in conjunction with IN, not =. (Is the latter even valid in SQL in general?)

Re:

rjbs on 2005-04-12T18:35:02

I'll definitely keep an eye on that... but it doesn't explain why the query seemed to work as written in DBD::SQLite 1.08, which uses 3.1.3 instead of 3.0.8. 3.1.0 introduced correleated subqueries, which is what I want. The lang_expr page would, then, seem to be wrong.

Re:

rjbs on 2005-04-12T18:36:22

...oh, and as for valid SQL! I'm not sure. I found some SQL92 validator that claimed that it was. It works in MSSQL and pgSQL. I just expected it would work in SQLite -- and it does, in the more recent versions.

What I really need to do is track down the problem with the query under DBD::SQLite 1.08.