Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Journal of ambs (3914)

Monday June 29, 2009
04:24 PM

You know you are doing too complex SQL queries...

[ #39190 ]

You know you are doing too complex SQL queries... when DBI/DBD::SQLite refuses to parse the SQL statement.

The statement is simple: select a field, where it is one of a set of words:

SELECT word FROM dict WHERE word='aaaaa' OR word='bbbbb' OR word='ccccc' OR...

The first error was: parser depth of 1000 was reached.

No problem. Decided to construct a binary tree of ORs:

SELECT ... WHERE ((word = a or word = b) or (word = c or word = d))

Now, DBD::SQLite::db prepare failed: parser stack overflow.

I think the solution is to slipt this thing in more than one query. Damn!

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • SELECT word FROM dict WHERE word IN ('aaaaa','bbbbb','ccccc',....)
    • This means I did not know SQL enough. Let me try it :)
    • It worked. THANK YOU!
      • I will (possibly) fail when the list of word in the 'IN'-clause gets long enough.
        I had problems with clauses longer than approx 100_000 in an old version of Ingres.

        The solution was to create a temporary table with all the words and then use this in the 'IN'-clause.

          CREATE TEMPORARY TABLE words(word VARCHAR(100));
          INSERT INTO words VALUES('aaaaa'); ....
          INSERT INTO words VALUES('xxxxx');
          SELECT word FROM dict WHERE word IN (SELECT word FROM WORDS);

        (SQl-code is suboptimal, but you

  • That's not complex, just big. I once did a 18 fold join: that is much more complex (without using views it wouldn't have been manageable).