Details

    • Sprint:

      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

          Issue links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 30 minutes
                  30m