Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5539

Empty results in UNION with Sphinx engine

    XMLWordPrintable

    Details

      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;

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            llafage Laurent Lafage
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: