[rt-devel] Limit with IN operator and empty VALUES results in invalid SQL queries

[Christian Loos]

Am 03.12.2014 um 09:39 schrieb Christian Loos:
> Hi,
> 
> yesterday I saw some invalid MySQL queries in my logs:
> SELECT ObjectCustomFieldValues_1.Content AS col0, COUNT(main.id) AS id
> FROM Tickets main
> LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1
> ON ( ObjectCustomFieldValues_1.CustomField = '90' )
> AND ( ObjectCustomFieldValues_1.Disabled = '0' )
> AND ( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' )
> AND ( ObjectCustomFieldValues_1.ObjectId = main.id )
> WHERE (main.Id IN ())
> GROUP BY ObjectCustomFieldValues_1.Content
> 
> I think this comes from [1].
> Recently there was also a branch merged [2] where you switched from
> looping over value lists to using the IN operator.
> 
> May there need to be a check that the VALUE list is not empty to avoid
> invalid SQL queries?
> 
> 
> Chris
> 
> [1]
> https://github.com/bestpractical/rt/blob/stable/lib/RT/Report/Tickets.pm#L490
> [2] https://github.com/bestpractical/rt/commit/b14df0e
> 

I changed
$self->Limit( FIELD => 'Id', OPERATOR => 'IN', VALUE => \@match );
to
$self->Limit( FIELD => 'Id', OPERATOR => 'IN', VALUE => @match ? \@match
: [0] );
which fixes the bug for me.

It would be great if one of the developers can review this and give some
feedback.

I'm not sure if this should only be fixed in RT or if this should be
fixed in DBIx::SearchBuilder (always generate valid SQL even if the
provided VALUE is an empty list).


Chris