Hi,
I encountered such problem a while ago. So I will try to tell you
what I know about it.
There should not be an explicit limit in the number of joins SQLite
can perform, actually it seems that the first 32 tables could use an
index, while for the following ones indexes should be ignored, but
the join should still work.
I think there's a bug in this handling, so, when working on more than
32 joins, there have been 2 different behaviours :
up to 2.8.14:
the join silently fails with no result.
in 2.8.15 (and 3.0.4):
the join works, but conditions set on tables bejond the 32th are
not enforced.
If you feel like that, you can try the attached patches which
(at least for me) kind of solve the problem moving the limit to
64 tables. This is done following a suggestion I had from Mr Hipp
(but that I may have completely broken ;-)) to enlarge some bitmaps
to 64 bits. This is done using long long integers, so I don't know
whether it works on any other compiler than gcc...
I think a cleaner solution would be to inplement the bitmaps in
a more portable way, or even better fixing the intended behaviour
and handling joins bejond 32 albeit ignoring indexes ...
I hope this can help you (or anybody else on the list).
Best Regards,
Lorenzo
Simon Berthiaume wrote:
Hi, I would like to know if there is any maximum to the number of JOIN
(LEFT JOIN actualy) I can put in a query. I tried to make a query with
31-32 JOIN and the engine returned no row, but returned no error message
either so I don't know. Just a single JOIN before, the query was still
returning data, but not anymore.
Thank you for your time.
Simon Berthiaume
--- sqlite.orig/src/where.c 2004-08-08 21:50:43.0 +0200
+++ sqlite/src/where.c 2004-08-14 17:03:58.0 +0200
@@ -21,6 +21,7 @@
** help it analyze the subexpressions of the WHERE clause. Each WHERE
** clause subexpression is separated from the others by an AND operator.
*/
+typedef unsigned long long mask_type;
typedef struct ExprInfo ExprInfo;
struct ExprInfo {
Expr *p;/* Pointer to the subexpression */
@@ -29,9 +30,9 @@
** p->pLeft is not the column of any table */
short int idxRight; /* p->pRight is a column in this table number. -1 if
** p->pRight is not the column of any table */
- unsigned prereqLeft;/* Bitmask of tables referenced by p->pLeft */
- unsigned prereqRight; /* Bitmask of tables referenced by p->pRight */
- unsigned prereqAll; /* Bitmask of tables referenced by p */
+ mask_type prereqLeft;/* Bitmask of tables referenced by p->pLeft */
+ mask_type prereqRight; /* Bitmask of tables referenced by p->pRight */
+ mask_type prereqAll; /* Bitmask of tables referenced by p */
};
/*
@@ -46,7 +47,7 @@
typedef struct ExprMaskSet ExprMaskSet;
struct ExprMaskSet {
int n; /* Number of assigned cursor values */
- int ix[31]; /* Cursor assigned to each bit */
+ int ix[63]; /* Cursor assigned to each bit */
};
/*
@@ -89,15 +90,15 @@
** Return the bitmask for the given cursor. Assign a new bitmask
** if this is the first time the cursor has been seen.
*/
-static int getMask(ExprMaskSet *pMaskSet, int iCursor){
+static mask_type getMask(ExprMaskSet *pMaskSet, int iCursor){
int i;
for(i=0; in; i++){
-if( pMaskSet->ix[i]==iCursor ) return 1n++;
pMaskSet->ix[i] = iCursor;
-return 1iTable);
@@ -446,13 +447,13 @@
Vdbe *v = pParse->pVdbe; /* The virtual database engine */
int brk, cont = 0; /* Addresses used during code generation */
int nExpr; /* Number of subexpressions in the WHERE clause */
- int loopMask;/* One bit set for each outer loop */
+ mask_type loopMask;/* One bit set for each outer loop */
int haveKey = 0; /* True if KEY is on the stack */
ExprInfo *pTerm; /* A single term in the WHERE clause; ptr to aExpr[] */
ExprMaskSet maskSet; /* The expression mask set */
- int iDirectEq[32]; /* Term of the form ROWID==X for the N-th table */
- int iDirectLt[32]; /* Term of the form ROWIDX or ROWID>=X */
+ int iDirectEq[64]; /* Term of the form ROWID==X for the N-th table */
+ int iDirectLt[64]; /* Term of the form ROWIDX or ROWID>=X */
ExprInfo aExpr[101]; /* The WHERE clause is divided into these terms */
/* pushKey is only allowed if there is a single table (as in an INSERT or
@@ -507,13 +508,13 @@
if( (pStack = pParse->trigStack)!=0 ){
int x;
if( (x=pStack->newIdx) >= 0 ){
-int mask = ~getMask(&maskSet, x);
+mask_type mask = ~getMask(&maskSet, x);
pTerm->prereqRight &= mask;
pTerm->prereqLeft &= mask;
pTerm->prereqAll &= mask;
}
if( (x=pStack->oldIdx) >= 0 ){
-int mask = ~getMask(&maskSet, x);
+mask_type mask = ~g
