redundant conditions in paginate-query

Bug #1212994 reported by Amala Basha
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Glance
Fix Released
Undecided
Amala Basha

Bug Description

For a list call on images, when a sort_key is applied, the query generated has redundant values for 'created_at' condition. This happens when there are duplicate values in the sort_keys list. eg) The following is the query captured for a simple image_get_all with a marker image provided:

SELECT anon_1.images_created_at AS anon_1_images_created_at, anon_1.images_updated_at AS anon_1_images_updated_at, anon_1.images_deleted_at AS anon_1_images_deleted_at, anon_1.images_deleted AS anon_1_images_deleted, anon_1.images_id AS anon_1_images_id, anon_1.images_name AS anon_1_images_name, anon_1.images_disk_format AS anon_1_images_disk_format, anon_1.images_container_format AS anon_1_images_container_format, anon_1.images_size AS anon_1_images_size, anon_1.images_status AS anon_1_images_status, anon_1.images_is_public AS anon_1_images_is_public, anon_1.images_checksum AS anon_1_images_checksum, anon_1.images_min_disk AS anon_1_images_min_disk, anon_1.images_min_ram AS anon_1_images_min_ram, anon_1.images_owner AS anon_1_images_owner, anon_1.images_protected AS anon_1_images_protected
FROM (SELECT images.created_at AS images_created_at, images.updated_at AS images_updated_at, images.deleted_at AS images_deleted_at, images.deleted AS images_deleted, images.id AS images_id, images.name AS images_name, images.disk_format AS images_disk_format, images.container_format AS images_container_format, images.size AS images_size, images.status AS images_status, images.is_public AS images_is_public, images.checksum AS images_checksum, images.min_disk AS images_min_disk, images.min_ram AS images_min_ram, images.owner AS images_owner, images.protected AS images_protected
FROM images
WHERE images.is_public = :is_public_1 UNION SELECT images.created_at AS images_created_at, images.updated_at AS images_updated_at, images.deleted_at AS images_deleted_at, images.deleted AS images_deleted, images.id AS images_id, images.name AS images_name, images.disk_format AS images_disk_format, images.container_format AS images_container_format, images.size AS images_size, images.status AS images_status, images.is_public AS images_is_public, images.checksum AS images_checksum, images.min_disk AS images_min_disk, images.min_ram AS images_min_ram, images.owner AS images_owner, images.protected AS images_protected
FROM images JOIN image_members ON images.id = image_members.image_id
WHERE image_members.deleted = :deleted_1) AS anon_1
WHERE CASE WHEN (anon_1.images_created_at IS NOT NULL) THEN anon_1.images_created_at ELSE :param_1 END < :param_2 OR CASE WHEN (anon_1.images_created_at IS NOT NULL) THEN anon_1.images_created_at ELSE :param_3 END = :param_4 AND CASE WHEN (anon_1.images_created_at IS NOT NULL) THEN anon_1.images_created_at ELSE :param_5 END < :param_6 OR CASE WHEN (anon_1.images_created_at IS NOT NULL) THEN anon_1.images_created_at ELSE :param_7 END = :param_8 AND CASE WHEN (anon_1.images_created_at IS NOT NULL) THEN anon_1.images_created_at ELSE :param_9 END = :param_10 AND CASE WHEN (anon_1.images_id IS NOT NULL) THEN anon_1.images_id ELSE :param_11 END < :param_12 ORDER BY anon_1.images_created_at DESC, anon_1.images_created_at DESC, anon_1.images_id DESC

Note the multiple 'created_at' condition in the final WHERE clause. This should be fixed.

Amala Basha (amalabasha)
description: updated
Changed in glance:
assignee: nobody → Amala Basha (amalabasha)
Amala Basha (amalabasha)
description: updated
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to glance (master)

Fix proposed to branch: master
Review: https://review.openstack.org/42312

Changed in glance:
status: New → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to glance (master)

Reviewed: https://review.openstack.org/42312
Committed: http://github.com/openstack/glance/commit/7faa1ff14c6cc93962a2c389c15173456827698e
Submitter: Jenkins
Branch: master

commit 7faa1ff14c6cc93962a2c389c15173456827698e
Author: AmalaBasha <email address hidden>
Date: Fri Aug 16 14:53:50 2013 +0530

    redundant conditions in paginate-query

    For a list call on images, when a sort_key is applied, the query
    generated has redundant values for 'created_at' condition. This happens
    when the sort_key list has duplicate values.

    Fixes Bug #1212994

    Change-Id: Icd62c2886d9ab608418ba9b4dc25cd06a4146992

Changed in glance:
status: In Progress → Fix Committed
Thierry Carrez (ttx)
Changed in glance:
milestone: none → havana-3
status: Fix Committed → Fix Released
Revision history for this message
Yang Yu (yuyangbj) wrote : I will be on vacation from 9/5 to 9/15, urgent call: 13811509950

I will be out of the office starting 2013-09-05 and will not return until
2013-09-15.

I will be on my marriage leave from 9/5 to 9/15, for any urgent issue
please call me before 9/7.

For daily work, please ask my scrum master Zhu Zhu for help.
For glance issue, please ask glance SME Feilong Wang for help.
For defect report, there will be no report next week.

Thierry Carrez (ttx)
Changed in glance:
milestone: havana-3 → 2013.2
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.