Empty results in UNION with Sphinx engine

Description

Hello,

We have a strange behaviour with a query with 2 UNION.

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 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.

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 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;

Environment

Debian wheezy

Status

Assignee

Unassigned

Reporter

Laurent Lafage

External issue ID

None

External issue ID

None

Time tracking

0m

Components

Fix versions

Affects versions

5.3.12
5.5.34
10.0.7

Priority

Major
Configure