Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.34, 10.0.7
-
Debian wheezy
Description
Hello,
We have a strange behaviour with a query with 2 UNION.
SELECT r.path,a.count,r.label |
FROM
|
(
|
SELECT si.branchid AS id,_sph_distinct AS count |
FROM sphinxindexad si |
WHERE si.query=';mode=extended2;range=paymin,0,1000000000;groupby=attr:branchid;distinct=adid;limit=1000000;maxmatches=500' |
) AS a |
INNER JOIN reference r ON r.id=a.id |
UNION
|
SELECT r.path,a.count,r.label |
FROM
|
(
|
SELECT si.criteriaid AS id,_sph_distinct AS count |
FROM sphinxindexad si |
WHERE si.query=';mode=extended2;range=paymin,0,1000000000;groupby=attr:criteriaid;distinct=adid;limit=1000000;maxmatches=500' |
) AS a |
INNER JOIN reference r ON r.id=a.id |
UNION
|
SELECT IF(r.path IS NULL,'/',r.path) as path,a.count,IF(r.label IS NULL,'test',r.label) |
FROM
|
(
|
SELECT si.subcriteriaid AS id,_sph_distinct AS count |
FROM sphinxindexad si |
WHERE si.query=';mode=extended2;range=paymin,0,1000000000;groupby=attr:subcriteriaid;distinct=adid;limit=1000000;maxmatches=500' |
) AS a |
INNER JOIN reference r ON r.id=a.id; |
The first UNION returns 439 rows.
The last query returns an empty sets.
As a result of this 2 UNION we have an empty set instead of 439 rows.
If we move the last query in first position, it's ok, we have our 439 rows.
SELECT IF(r.path IS NULL,'/',r.path) as path,a.count,IF(r.label IS NULL,'test',r.label) |
FROM
|
(
|
SELECT si.subcriteriaid AS id,_sph_distinct AS count |
FROM sphinxindexad si |
WHERE si.query=';mode=extended2;range=paymin,0,1000000000;groupby=attr:subcriteriaid;distinct=adid;limit=1000000;maxmatches=500' |
) AS a |
INNER JOIN reference r ON r.id=a.id |
UNION
|
SELECT r.path,a.count,r.label |
FROM
|
(
|
SELECT si.branchid AS id,_sph_distinct AS count |
FROM sphinxindexad si |
WHERE si.query=';mode=extended2;range=paymin,0,1000000000;groupby=attr:branchid;distinct=adid;limit=1000000;maxmatches=500' |
) AS a |
INNER JOIN reference r ON r.id=a.id |
UNION
|
SELECT r.path,a.count,r.label |
FROM
|
(
|
SELECT si.criteriaid AS id,_sph_distinct AS count |
FROM sphinxindexad si |
WHERE si.query=';mode=extended2;range=paymin,0,1000000000;groupby=attr:criteriaid;distinct=adid;limit=1000000;maxmatches=500' |
) AS a |
INNER JOIN reference r ON r.id=a.id; |