DTML-IN Sorting bug

[Andreas N]

When looping thru a dictionary or SQL Resultset using a dtml-in the sort property will fail on some keys and give the following error:
----------------------------------------------------------------------------------------
Error Type: TypeError
Error Value: r.__cmp__(x,y) requires y to be a 'r', not a 'ImplicitAcquirerWrapper'
----------------------------------------------------------------------------------------

Sorting by product_id works, price however does not work.
Dictionary: {'art_num': 'UC3H2SH', 'custom_name': '', 'price': 34859.0, 'product_id': 22537, 'instock': 1, 'Description': 'ThinkPad T43p 2668 - Pentium M 770 2.13 GHz - RAM 1 GB - HD 60 GB - DVD Multi Recorder - Mdm - LAN EN, Fast EN, Gigabit EN, Bluetooth, 802.11b, 802.11g - Centrino - Win XP Pro - 15" TFT UXGA (1600 x 1200)\r', 'custom_price': 0.0}

Example Code:
<dtml-in mydict prefix=list sort=price>
  <dtml-var price><br>
</dtml-in>

[Clemens Robbenhaar (crobbenhaar)]

This might be a problem with the database adapter,
of maybe some Brains plugged in there
Could You add more information about your setup?
With the information You gave so far at least I have no idea
how to reproduce the issue.

[Andreas N (subzane)]

How to reproduce:

SQL:
CREATE TABLE `status` (
  `status_id` int(11) unsigned NOT NULL auto_increment,
  `value` varchar(50) NOT NULL default '',
  `type_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `status` */
insert into `status` values (1,'Inaktiv',1),(2,'Aktiv',1),(3,'Special',0),(4,'Private',0);

QUERY:
select * from status

DTML:
<dtml-in expr="testsql()" prefix=list sort=type_id>
  <dtml-var expr="list_item.value">
</dtml-in>

Hope this will help to reproduce the error. I get the following error when run:
--------------------------------------------------
Error Type: TypeError
Error Value: r.__cmp__(x,y) requires y to be a 'r', not a 'ImplicitAcquirerWrapper'
--------------------------------------------------

[Andreas N (subzane)]

Bug located:
When using sort on a column that has duplicated values (i.e. 1,1,6,5,3,9) it will crash.
Using the example below you can change the type_id column contents from (1,1,0,0) to be (1,5,6,9) instead and it will work just fine.
But as not all table rows are unique this is quite disturbing.

[Andreas N (subzane)]

Additional info:
Zope Version: (Zope 2.8.4-final, python 2.3.5, freebsd4)
Python Version: 2.3.5 (#2, Nov 24 2005, 13:23:04) [GCC 2.95.4 20020320 [FreeBSD]]
System Platform: freebsd4
Product: ZMySQLDA (Installed product ZMySQLDA (ZMySQLDA 2.0.8))
MySQL Version: 4.0.2.0

[Andreas Jung (ajung)]

Changes: submitter email, edited transcript, importance (critical => medium)

[Clemens Robbenhaar (crobbenhaar)]

Uploaded: Results_ugly_workaround.patch

I can easily reproduce this issue even with a Gadfly DB
(Zope 2.8.5 here)
Unfortunately I do not have enough zen to figure out
why an aquisition wrapper got in here.

The attached patch works around the problem for me,
so people needing a hotfix might try it. Of course
such an ugly hack not meant to make it to the SVN repository.

[Andreas Jung (ajung)]

Changes: edited transcript, importance (medium => low)

[Florent Guillaume (efge)]

See also http://www.zope.org/Collectors/Zope/1884

[Clemens Robbenhaar (crobbenhaar)]

The difference to #1884 is that no ZSQL Brains are involved here.

It seems that sorting never works if objects defining their own
__cmp__ get wrapped into AcquisitionWrappers.

The code for the __cmp__ on acquisition wrappers seems to
be function "Wrapper_compare" in _Acquisition.c, and this
seems to unwrap the objects only in case they do not
define a __cmp__ on their own.
 If the wrapped objects define __cmp__ this is called with the
wrappers as the arguments. This might cause the problem;
but I do not understand the code enough to make more
than wild guesses about it.

[Andreas Jung (ajung)]

Sorting a resultset from a ZSQL method seems to work in Zope 2.7.
Possibly issue is related to the ExtensionClass implementation based on new-style classes in Zope 2.8+....but I have no further idea.

As a workaround: sort your ZSQL resultset by using SQL functionality.

[John Eikenberry (jae)]

Zope-2.9.4, python-2.4.3 - bug still present.

Having to use the workaround patch in a monkey patch to fix.

I'm assuming you haven't just used the patch as you'd like to figure out the cause and fix that. While understandable it is not getting done. So please consider including the workaround patch until then.

[Flemming Bjerke (flem)]

The problem persists in zope-2.9.7. And the ugly workaround still works.

If no one have the time to fix the bug, why don't put the ugly workaround into the code?

[Tres Seaver (tseaver)]

The other collector issue is now lp:143504

[Colin Watson (cjwatson)]

The products.zsqlmethods project on Launchpad has been archived at the request of the Zope developers (see https://answers.launchpad.net/launchpad/+question/683589 and https://answers.launchpad.net/launchpad/+question/685285). If this bug is still relevant, please refile it at https://github.com/zopefoundation/products.zsqlmethods.