typeorm generates queries that cause 'SQLITE_ERROR: too many SQL variables'

[danelkhen]

Sometimes TypeORM generates multiple queries, first one gets the ids, and the second one gets the actual selected data. It looks like the 2nd query uses IN operator, but generates a variable for each id:

SELECT ... FROM ... LEFT JOIN ... ON ... WHERE "tbl1"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88...

this causes:

'SQLITE_ERROR: too many SQL variables'

Sample query (assume two tables with a join):

createQueryBuilder("tbl1")
    .leftJoinAndMapOne("tbl1.tbl2", Tbl2, "tbl2", "tbl1.tbl2_id=tbl2.id")
    .orderBy("tbl1.name", "DESC").setMaxResults(1000);

[pleerock]

The problem is that underlying sqlite cannot handle so many params, however orm needs to generate such query to make sure that your pagination works correctly with your left joins. Underlying driver also does not support arrays as for example mysql driver. I have created issue in their repo, lets see what they answer.

For now I have added dirty check if your ids are numbers then prevent escaping. It works only for numeric ids. Released in 0.0.7-alpha.9