Last updated December 15, 2013.

  1. 8.x criticals in their current or final state

    This query includes issues that are currently filed as 8.x critical bugs or tasks in all statuses (including closed issues). It does not include issues that were previously marked as 8.x criticals but later downgraded or moved to a different branch (for example, backports or criticals currently marked major for change notifications).

    select n.nid, date(from_unixtime(n.created)) as posted_date
    from node n
    inner join field_data_field_project project on (project.entity_id = n.nid and project.field_project_target_id=3060) -- core
    inner join field_data_field_issue_version version on (version.entity_id = n.nid and version.field_issue_version_value = '8.x-dev') -- 8.x issues
    inner join field_data_field_issue_priority p on p.entity_id = n.nid
    inner join field_data_field_issue_category c on c.entity_id = n.nid
    inner join field_data_field_issue_status s on s.entity_id = n.nid
    where p.field_issue_priority_value = 400 -- critical
    and c.field_issue_category_value in (1, 2) -- bug, task
    and s.field_issue_status_value in (1, 13, 8, 14, 2, 7, 4) -- active, nw, nr, rtbc, fixed, closed (fixed), postponed
    and n.status = 1
    order by n.created
    ;
  2. Fixed dates for 8.x criticals in their final state

    Note: Due to #2130059: Issue status change not consistently saved in node revisions, this query misses nodes that are missing their "fixed" revisions. See the supplemental queries below to find the missing nodes.

    This selects the most recent fixed revision of an issue from all issues that are currently marked either Fixed or Closed (fixed) against 8.x. It returns a subset of the issues from the query above. It does not include issues that were previously marked as 8.x criticals but later downgraded or moved to a different branch (for example, backports or criticals currently marked major for change notifications), nor 8.x criticals that were previously fixed but then reopened.

    select nr.nid, date(from_unixtime(max(nr.timestamp))) as fixed_date -- only the most recent fixed revision
    from node_revision nr
    inner join
    ( -- use a subquery to ensure we have no false positives from unusual issue workflows
    select n.nid, date(from_unixtime(n.created)) as posted_date
    from node n
    inner join field_data_field_project project on (project.entity_id = n.nid and project.field_project_target_id=3060) -- core
    inner join field_data_field_issue_version version on (version.entity_id = n.nid and version.field_issue_version_value = '8.x-dev') -- 8.x issues
    inner join field_data_field_issue_priority p on p.entity_id = n.nid
    inner join field_data_field_issue_category c on c.entity_id = n.nid
    inner join field_data_field_issue_status s on s.entity_id = n.nid
    where p.field_issue_priority_value = 400 -- critical
    and c.field_issue_category_value in (1, 2) -- bug, task
    and s.field_issue_status_value in (2, 7) -- fixed, closed (fixed)
    and n.status = 1
    order by n.created
    ) fixed_issues on nr.nid = fixed_issues.nid
    inner join field_revision_field_issue_status rs on rs.entity_id = nr.nid and rs.revision_id = nr.vid and rs.field_issue_status_value = 2 -- fixed
    group by nr.nid
    ;

    Closed (fixed) dates

    Workaround for issues affected by #2130059: Issue status change not consistently saved in node revisions. This will fetch the Closed (fixed) revision instead of the fixed revision, which can be used to estimate the fixed date by subtracting two weeks. Of course, if the Closed (fixed) revisions was similarly lost, the query will not return the issue.

    select nr.nid, date(from_unixtime(min(nr.timestamp))) as closed_fixed_date -- the first closed (fixed) revision
    from node_revision nr
    inner join
    ( -- use a subquery to ensure we have no false positives from unusual issue workflows
    select n.nid, date(from_unixtime(n.created)) as posted_date
    from node n
    inner join field_data_field_project project on (project.entity_id = n.nid and project.field_project_target_id=3060) -- core
    inner join field_data_field_issue_version version on (version.entity_id = n.nid and version.field_issue_version_value = '8.x-dev') -- 8.x issues
    inner join field_data_field_issue_priority p on p.entity_id = n.nid
    inner join field_data_field_issue_category c on c.entity_id = n.nid
    inner join field_data_field_issue_status s on s.entity_id = n.nid
    where p.field_issue_priority_value = 400 -- critical
    and c.field_issue_category_value in (1, 2) -- bug, task
    and s.field_issue_status_value in (2, 7) -- fixed, closed (fixed)
    and n.status = 1
    order by n.created
    ) fixed_issues on nr.nid = fixed_issues.nid
    inner join field_revision_field_issue_status rs on rs.entity_id = nr.nid and rs.revision_id = nr.vid and rs.field_issue_status_value = 7 -- closed (fixed)
    group by nr.nid
    ;

    Updated date

    Workaround for issues affected by #2130059: Issue status change not consistently saved in node revisions that returns the node's last updated date as a fallback for missing both Fixed and Closed (fixed) revisions. This can be used to approximate the fixed date by subtracting two weeks if no revisions were made after the issue was closed.

    select n.nid, date(from_unixtime(n.changed)) as final_date
    from node n
    inner join field_data_field_project project on (project.entity_id = n.nid and project.field_project_target_id=3060) -- core
    inner join field_data_field_issue_version version on (version.entity_id = n.nid and version.field_issue_version_value = '8.x-dev') -- 8.x issues
    inner join field_data_field_issue_priority p on p.entity_id = n.nid
    inner join field_data_field_issue_category c on c.entity_id = n.nid
    inner join field_data_field_issue_status s on s.entity_id = n.nid
    where p.field_issue_priority_value = 400 -- critical
    and c.field_issue_category_value in (1, 2) -- bug, task
    and s.field_issue_status_value in (2, 7) -- fixed, closed (fixed)
    and n.status = 1
    order by n.created
    ;
  3. Critical D7 backports that are no longer filed against 8.x

    In the normal workflow for core backports, a committer moves the issue to the 7.x branch or relevant contrib project immediately when fixing the issue in 8.x. Sometimes, this happens in a later revision instead. Some issues are also committed to 7.x and 8.x at the same time and left filed against 8.x; those issues will be included in the query above instead of the one below.

    This query assumes the first workflow described above, so it uses the date of the first non-8.x core revision as the date when the issue was "fixed" in 8.x. Sometimes issues are reopened for backport much later, so those issues will have an incorrect fix date.

    The query will miss issues that were fixed in 8.x as critical bugs or tasks, but later downgraded or recategorized. (We have no way to distinguish cleanly between issues that were considered critical at commit and those that were downgraded prior to commit.) It also misses issues where the backport tag has mistakenly been removed. Similarly, it will falsely match issues that were never committed to 8.x but mistakenly have the backport tag.

    select nr2.nid, last_8x.posted_date, last_8x.8x_date, date(from_unixtime(min(nr2.timestamp))) as fixed_date -- first non-8.x core revision as the backport date
    from node_revision nr2
    inner join
    ( -- Subquery to select issue revisions that were 8.x core issues once and then moved for backport
    select nr.nid, posted_date, date(from_unixtime(max(nr.timestamp))) as 8x_date -- only the most recent 8.x revision
    from node_revision nr
    inner join
    ( -- Subquery to select backport-tagged issues that are no longer 8.x core issues
    select n.nid, date(from_unixtime(n.created)) as posted_date
    from node n
    inner join taxonomy_index ti on ti.nid = n.nid and ti.tid = 21556 -- Needs backport to D7
    inner join field_data_field_project project on (project.entity_id = n.nid)
    inner join field_data_field_issue_version version on version.entity_id = n.nid
    inner join field_data_field_issue_priority p on p.entity_id = n.nid
    inner join field_data_field_issue_category c on c.entity_id = n.nid
    inner join field_data_field_issue_status s on s.entity_id = n.nid
    where p.field_issue_priority_value = 400 -- critical
    and c.field_issue_category_value in (1, 2) -- bug, task
    and (version.field_issue_version_value <> '8.x-dev' or project.field_project_target_id <> 3060) -- Non-8.x core issues only
    and s.field_issue_status_value in (1, 13, 8, 14, 2, 7, 4, 15) -- active, nw, nr, rtbc, fixed, closed (fixed), postponed, patch (to be ported)
    and n.status = 1
    order by n.created
    ) backported_issues on nr.nid = backported_issues.nid
    inner join field_revision_field_project rp on (rp.entity_id = nr.nid and rp.revision_id = nr.vid and rp.field_project_target_id=3060) -- core revisions
    inner join field_revision_field_issue_version rv on (rv.entity_id = nr.nid and rv.revision_id = nr.vid and rv.field_issue_version_value = '8.x-dev') -- 8.x revisions
    group by nr.nid
    ) last_8x on last_8x.nid = nr2.nid
    where date(from_unixtime(nr2.timestamp)) >= last_8x.8x_date -- more recent than the final 8.x revision
    group by nr2.nid
    ;
  4. Outstanding 8.x change records for issues that were previously 8.x criticals

    These are issues caught in a limbo state between committed and fixed, since our current change record worfklow moves issues to "major task" for the creation of a change record following commit, regardless of the issue's previous status. The issues are supposed to be marked fixed and have their priority and category restored once the change record is completed.

    This query still needs work to pick an accurate fix date -- e.g., for:
    #1535868: Convert all blocks into plugins
    #1871696: Convert block instances to configuration entities to resolve architectural issues
    #1984766: Change notice: Start relying on Request/Response objects for cache handling
    #2003892: Convert date formats to config entities
    #2032453: WebTestBase::randomString returning a string containing a $ followed by a number causes assertLink() to fail (Was marked fixed without tagging for change record)

    It also still has some false positives (typically due to change record tasks being re-prioritized from critical to major during summer 2013):
    #1903746: Replace the views grid table template with one using divs
    #1943846: Improve ParamConverterManager and developer experience for ParamConverters
    #1984766: Change notice: Start relying on Request/Response objects for cache handling
    #1987298: Shorten directory structure and PSR-0 namespacing for plugins
    #1987896: Convert user_page() to a new style controller
    #1996868: Start converting image.inc to an Image component
    #2011018: Reconcile entity forms and confirm forms
    #2014215: Shift render array defaults back out onto BlockRenderController
    #2014821: Introduce form modes UI and their configuration entity
    #2020001: Make datatype callback handling more flexible
    #2033383: Provide a default plugin bag
    #2050227: Add local action plugin deriver to use YAML discovery for static definitions
    previously #2073759: Convert toolkit operations to plugins (was previously tagged prematurely and downgraded from critical before that)
    #2073813: Add a UrlGenerator helper to FormBase and ControllerBase

    select nr2.nid, cr_8x.posted_date, date(from_unixtime(min(nr2.timestamp))) as 8x_fix_date
    from node_revision nr2
    inner join
    (
    select nr.nid, cr_issues.posted_date, date(from_unixtime(max(nr.timestamp))) as last_critical_date
    from node_revision nr
    inner join
    ( -- Subquery for nodes that are currently tagged for change records but are not 8.x criticals
    select n.nid, date(from_unixtime(n.created)) as posted_date
    from node n
    inner join taxonomy_index ti on ti.nid = n.nid and ti.tid = 29726 -- Needs change notification
    inner join field_data_field_project project on project.entity_id = n.nid
    inner join field_data_field_issue_version version on version.entity_id = n.nid
    inner join field_data_field_issue_priority p on p.entity_id = n.nid
    inner join field_data_field_issue_category c on c.entity_id = n.nid
    inner join field_data_field_issue_status s on s.entity_id = n.nid
    where ( -- filter to issues that are not currently marked as 8.x criticals
    p.field_issue_priority_value <> 400 -- critical
    or c.field_issue_category_value not in (1, 2) -- bug, task
    or project.field_project_target_id <> 3060 -- core
    or version.field_issue_version_value <> '8.x-dev'
    )
    and s.field_issue_status_value in (1, 13, 8, 14, 2, 7, 4, 15) -- active, nw, nr, rtbc, fixed, closed (fixed), postponed, patch (to be ported)
    and n.status = 1
    order by n.created
    ) cr_issues on nr.nid = cr_issues.nid
    inner join field_revision_field_project r_project on r_project.entity_id = nr.nid and r_project.revision_id = nr.vid and r_project.field_project_target_id = 3060 -- core
    inner join field_revision_field_issue_version rv on rv.entity_id = nr.nid and rv.revision_id = nr.vid and rv.field_issue_version_value = '8.x-dev'
    inner join field_revision_field_issue_priority rp on rp.entity_id = nr.nid and rp.revision_id = nr.vid
    inner join field_revision_field_issue_category rc on rc.entity_id = nr.nid and rc.revision_id = nr.vid
    where rc.field_issue_category_value in (1, 2) -- bug, task
    and rp.field_issue_priority_value = 400 -- critical
    group by nr.nid
    ) cr_8x on cr_8x.nid = nr2.nid
    inner join field_revision_taxonomy_vocabulary_9 tags on tags.entity_id = nr2.nid and tags.revision_id = nr2.vid and tags.taxonomy_vocabulary_9_tid = 29726 -- Needs change notification
    where date(from_unixtime(nr2.timestamp)) >= cr_8x.last_critical_date
    group by nr2.nid
    ;