vrana/notorm — Issue #41: Extra parameters in UNION clause are ignored, causing 'Invalid parameter number: number of bound variables does not match number of tokens'
Author: honzajavorek
Created: 2012-02-19 17:46:56+00:00
State: open

Hello,

as title says, using extra parameters in `->search()` clause of `UNION` causes `Invalid parameter number: number of bound variables does not match number of tokens`. Failing example:

$term = 'Brno';
$termRE = '[[:<:]]' . preg_quote($term);

$search = $notorm->location
->select(new \NotORM_Literal("
location.name AS name,
IF(STRCMP(location.name, ?), 0, 1) AS equal_to_term
", $term))
->where('location.name REGEXP ?', $termRE)
->group('location.name')
->union($notorm->venue
->select(new \NotORM_Literal("
venue.city AS name,
IF(STRCMP(venue.city, ?), 0, 1) AS equal_to_term
", $term))
->where('venue.city REGEXP ?', $termRE)
->group('venue.city')
)
->order('equal_to_term DESC')
->limit(10);

foreach ($search as $place) {
echo $place['name'] . '
';
}

Results in generating this:

SELECT location.name AS name IF(STRCMP(location.name, ?), 0, 1) AS equal_to_term FROM location WHERE (location.name REGEXP ?) GROUP BY location.name) UNION (SELECT venue.city AS name, IF(STRCMP(venue.city, ?), 0, 1) AS equal_to_term FROM venue WHERE (venue.city REGEXP ?) GROUP BY venue.city) ORDER BY equal_to_term DESC LIMIT 10

array(3) [
0 => "Brno" (4)
1 => "[[:<:]]Brno" (11)
2 => "[[:<:]]Brno" (11)
]

All works when UNION clause is dropped from the query. Workaround is using already quoted parameters:

$qTerm = $pdo->quote($term);
...
->search("venue.city AS name IF(STRCMP(venue.name, $qTerm), 0, 1) AS equal_to_term")
...

Thanks for fixing.