CODEWITHSUNDEEP
mysqlsqlsql-limit
farincz
farincz

Reputation: 5173

MySQL LIMIT statement, wrong number of rows are returned

I have pretty complicated query (using MySQL 8.0.11)

SELECT
  x.id,
  x.text,
  x.campaign_id,
  x.ad_group_id,
  x.close_variant,
  x.keywords,
  x.terms,
  x.impressions,
  x.clicks,
  x.conversion_value,
  x.avg_position,
  x.conversions,
  x.cost,
  x.campaigns,
  x.ad_groups,
  EXISTS (
    SELECT
        *
    FROM
        reports_biddablekeyword cbk
        JOIN reports_keyword ck ON (cbk.keyword_id = ck.id)
    wHERE
        cbk.customer_id = 3512658134 AND
        cbk.campaign_id = x.campaign_id AND
        cbk.ad_group_id = x.ad_group_id AND
        phrase_conflict(x.text, ck.text, ck.match_type)
) AS conflict,

    EXISTS (
        SELECT
            *
        FROM
            reports_negativekeyword bnk
            JOIN reports_keyword bk ON (bnk.keyword_id = bk.id)
        wHERE
            bnk.customer_id = 3512658134 AND
            bnk.campaign_id = x.campaign_id AND
            bnk.ad_group_id = x.ad_group_id AND
            bk.match_type = 'PHRASE' AND
            phrase_match(x.text, bk.text, bk.match_type)
    )
 AS blocked,
  ROUND(x.clicks / x.impressions * 100, 1) AS ctr,
  ROUND(x.cost / x.conversions, 2) AS cpa,
  ROUND(x.cost / x.clicks, 2) AS cpc,
  ROUND(x.conversion_value / x.cost, 2) AS roi
FROM
(
  SELECT
    p.id AS id,
    p.text AS text,
    r.campaign_id AS campaign_id,
    r.ad_group_id AS ad_group_id,
    IF(SUM(r.match_type_close_variant) = COUNT(*), "only", IF(SUM(r.match_type_close_variant) > 0, "some", "none")) AS close_variant,
    COUNT(DISTINCT r.keyword_id) AS keywords,
    COUNT(DISTINCT r.term) AS terms,
    IFNULL(SUM(r.impressions), 0) AS impressions,
    IFNULL(SUM(r.clicks), 0) AS clicks,
    SUM(r.conversion_value) AS conversion_value,
    ROUND(SUM(r.avg_position * r.impressions) / SUM(r.impressions), 2) AS avg_position,
    ROUND(IFNULL(SUM(r.conversions), 0), 2) AS conversions,
    ROUND(IFNULL(SUM(r.cost), 0), 2) AS cost,
    COUNT(DISTINCT r.campaign_id) AS campaigns,
    COUNT(DISTINCT r.ad_group_id) AS ad_groups
  FROM
    reports_row r
    JOIN reports_phraserow pr ON (r.id = pr.row_id)
    JOIN reports_phrase p ON (p.id = pr.phrase_id)
  WHERE
    (r.customer_id) = 3512658134 AND
    (p.text != p.common_term) = 0
  GROUP BY
    r.campaign_id,
    r.ad_group_id,
    pr.phrase_id
  HAVING
    conversions = 0 AND
    cost > 43.97137500000000091533811780
  ) AS x
HAVING
  conflict = 0 AND
  blocked = 0
ORDER BY
  cost DESC

Running the query as is I get 173 rows in result.

Adding LIMIT after ORDER BY I am getting curious number of records

I have no idea what's wrong.

UPDATE: I realized that wrapping all to SELECT xxx.* FROM (...) AS xxx LIMIT N returns correct result. But I am still curious why is this needed.

UPDATE2: also replacing exists sub-queries with just constant value makes limit working properly

Upvotes: 2

Views: 447

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You have an aggregation query with no GROUP BY.

You should be getting a syntax error, but you have clearly overridden the default settings for MySQL 8.

The unaggregated columns come from arbitrary rows. These values can change from run-to-run.

Far from being a mystery, your inconsistent results are why ONLY_FULL_GROUP_BY is now the default setting and why almost all other databases follow the SQL standard in not allowing a query such as yours.

Upvotes: 2

Related Questions