We're updating the issue view to help you get more done. 

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

10.0.7
5.3.12
5.5.34

Priority

Major