Glance image-list returns different results for MySQL and PostgreSQL

[Abhishek Kekane]

Glance image-list is returning different results for MySQL and PostgreSQL with sort_key=name

Steps to reproduce:

1. Assume images with following names are present in database

   'image--1439541877',
   'Fedora-x86_64-20-20131211.1-sda',
   'cirros-0.3.1-x86_64-uec-ramdisk',
   'cirros-0.3.1-x86_64-uec-kernel',
   'cirros-0.3.1-x86_64-uec'

2.
a. For MySQL :

List Images with sort_key = name
   $ glance image-list --sort-key=name --sort-dir=desc

Output:
+--------------------------------------+---------------------------------+-------------+------------------+----------+-------+
| ID | Name | Disk Format | Container Format | Size | Status |
+--------------------------------------+---------------------------------+-------------+------------------+----------+--------+
| 662998c2-4e19-4ddf-9eb4-91ad1ffae8c9 | image--1439541877 | qcow2 | bare | 12185586 | active
| d067e2bd-7679-4e36-a1d0-0715e1d11a84 | cirros-0.3.1-x86_64-uec | ami | ami | 25165824 | active |
| 61ebb7f7-56ff-423b-8816-838a31ada1bf | cirros-0.3.1-x86_64-uec-kernel | aki | aki | 4955792 | active |
| 87f3642c-a1b7-4925-948c-05684b5fcb93 | cirros-0.3.1-x86_64-uec-ramdisk | ari | ari | 3714968 | active |
| e6aeba3c-07d9-4b40-96c9-8013c52e8816 | Fedora-x86_64-20-20131211.1-sda | qcow2 | bare | 2341 | queued |
-------------------------------------------------------------------------------------------------------------------------------
Here ,'Fedora-x86_64-20-20131211.1-sda' is at last position .

b. For PostgreSQL :

List Images with sort_key = name
   $ glance image-list --sort-key=name --sort-dir=desc

Output:
+--------------------------------------+---------------------------------+-------------+------------------+----------+-------+
| ID | Name | Disk Format | Container Format | Size | Status |
+--------------------------------------+---------------------------------+-------------+------------------+----------+--------+
| 662998c2-4e19-4ddf-9eb4-91ad1ffae8c9 | image--1439541877 | qcow2 | bare | 12185586 | active
| e6aeba3c-07d9-4b40-96c9-8013c52e8816 | Fedora-x86_64-20-20131211.1-sda | qcow2 | bare | 2341 | queued |
| d067e2bd-7679-4e36-a1d0-0715e1d11a84 | cirros-0.3.1-x86_64-uec | ami | ami | 25165824 | active |
| 61ebb7f7-56ff-423b-8816-838a31ada1bf | cirros-0.3.1-x86_64-uec-kernel | aki | aki | 4955792 | active |
| 87f3642c-a1b7-4925-948c-05684b5fcb93 | cirros-0.3.1-x86_64-uec-ramdisk | ari | ari | 3714968 | active |
-------------------------------------------------------------------------------------------------------------------------------
Here 'Fedora-x86_64-20-20131211.1-sda' is at 2nd position.

Note :
I am adding a new test case in glance tempest to 'list image with sort_key'
Blueprint: testcases-expansion-icehouse

But after submitting the patch to the community, Jenkins is continuously failing with mismatch error for Test: check-tempest-dsvm-postgres-full
Please refer:
http://logs.openstack.org/69/81769/12/check/check-tempest-dsvm-postgres-full/411e98c/console.html

Locally this test case is getting passed where i am using MySQL Database.

[Steve Lewis (steve-lewis)]

Notes gathered in research
- http://www.postgresql.org/docs/current/static/citext.html
- http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html

PostgreSQL uses system collation, which in this case will provide POSIX behavior which is explains the behavior noted.

[Ukesh (ukeshkumar)]

In which version it occurs ?

I checked in Juno, it is working fine.