[rt-users] 3.6.3 - sorting on Custom Fields still not working?

Joe Casadonte joe.casadonte at oracle.com
Sat Feb 10 14:33:50 EST 2007


Using RT 3.6.3 with Oracle 10.2.0.2, I'm having trouble sorting on my 
custom fields.  As far as I can remember, all tests passed when I first 
installed the system, and test 23cfsort.t specifically still passes:

$ prove -I/opt/rt3/lib -v lib/t/setup_regression.t 
lib/t/regression/23cfsort.t
lib/t/setup_regression.......ok 1
ok 2 - Basic initialization and DB connectivity
ok 3 - use RT::Queue;
[Sat Feb 10 17:50:55 2007] [crit]: Regression tests not starting with a 
clean DB. Bailing at lib/t/setup_regression.t line 15. 
(/opt/rt3/lib/RT.pm:345)

Stack trace:
         RT::__ANON__() called at lib/t/setup_regression.t:15
Regression tests not starting with a clean DB. Bailing at 
lib/t/setup_regression.t line 15.
1..3
ok
lib/t/regression/23cfsort....1..15
ok 1 - CFSortQueue-11517 test queue creation. Queue created
ok 2 - Custom Field Order created
ok 3 - Custom Field Alpha created
ok 4 - Custom Field Beta created
ok 5 - Custom Field Charlie created
ok 6 - We found 2 tickets when lookign for cf charlie
ok 7 - Ordered correctly: 1 2
ok 8 - We found two tickets when sorting by cf charlie without limiting 
to it
ok 9 - Ordered correctly: 2 1
ok 10
ok 11 - Ordered correctly: 1 2
ok 12
ok 13 - Ordered correctly: 3 2 1
ok 14
ok 15 - Ordered correctly: 2 3 1
ok
All tests successful.
Files=2, Tests=18,  6 wallclock secs ( 3.84 cusr +  0.73 csys =  4.57 CPU)
$

And yet, my tickets are not being sorted on either a numeric field (ASC 
or DESC) or a select field (with 5 values).  On the simplest search I 
did I only had 2 columns in the results (id + numeric custom field). 
Pulling some SQL out of log and running it in SQL*Plus returns the same 
results as on screen (go figure).  Here's one of the queries:

SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( SELECT 
main.* FROM ( SELECT main.id FROM ((((Tickets main  LEFT JOIN 
ObjectCustomFields ObjectCustomFields_1  ON 
((ObjectCustomFields_1.ObjectId = '0')) OR ( 
ObjectCustomFields_1.ObjectId = main.Queue))  LEFT JOIN CustomFields 
CustomFields_2  ON ( CustomFields_2.id = 
ObjectCustomFields_1.CustomField))  LEFT JOIN ObjectCustomFieldValues 
ObjectCustomFieldValues_3  ON ((ObjectCustomFieldValues_3.ObjectId = 
main.id)) AND (  ObjectCustomFieldValues_3.CustomField = 
CustomFields_2.id) AND ( (ObjectCustomFieldValues_3.Disabled = '0')) AND 
( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket')))  LEFT JOIN 
CustomFieldValues CustomFieldValues_4  ON ((CustomFieldValues_4.Name = 
ObjectCustomFieldValues_3.Content)) AND ( 
CustomFieldValues_4.CustomField = 
ObjectCustomFieldValues_3.CustomField))   WHERE ((main.EffectiveId = 
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) 
AND ( ( (main.Owner = '86') ) )   GROUP BY main.id   ORDER BY 
min(CustomFieldValues_4.SortOrder) ASC, 
min(ObjectCustomFieldValues_3.Content) ASC  ) distinctquery, Tickets 
main WHERE (main.id = distinctquery.id) ) limitquery WHERE rownum <= 50 
) WHERE limitrownum >= 1;


Here's what I'm testing with.  CF1 is a single-value select with values:

Sort  Value
----  -----
  1     FOO
  2     BAR
  3     BAZ

CF2 is a simple numeric field.  Here are the tickets:

id   CF1     CF2
===  ======  ===
  1   BAR     0.1
  2   FOO     0.1
  3   BAZ     0.1
21   FOO     23
22   FOO     23
41   FOO     3
42   BAZ     0
55   FOO     0.5
56   FOO     0

With only these three fields in the results, sorting on CF2 ASC, I get:

56   FOO     0
  2   FOO     0.1
55   FOO     0.5
22   FOO     23
21   FOO     23
41   FOO     3
  1   BAR     0.1
42   BAZ     0
  3   BAZ     0.1

It seems to be sorting on CF1 ASC then CF2 ASC lexicographically.

If I switch the column order in which the results are shown, I still get 
the same sort:

56     0     FOO
  2     0.1   FOO
55     0.5   FOO
22     23    FOO
21     23    FOO
41     3     FOO
  1     0.1   BAR
42     0     BAZ
  3     0.1   BAZ

If I change the CF2 sort to DESC, I get the exact opposite results
(except 21 & 22, which have identical values for both fields):

  3     0.1   BAZ
42     0     BAZ
  1     0.1   BAR
41     3     FOO
22     23    FOO
21     23    FOO
55     0.5   FOO
  2     0.1   FOO
56     0     FOO

If I remove CF1 from the results, there is no change in the ticket
sort order:

  3     0.1
42     0
  1     0.1
41     3
22     23
21     23
55     0.5
  2     0.1
56     0

If I remove CF2 from the ORDER BY, replacing with ID, I get the
expected results:

  1     0.1
  2     0.1
  3     0.1
21     23
22     23
41     3
42     0
55     0.5
56     0

If I put CF2 back as the first sort (DESC), and add ID as the second
sort (ASC), I get the same results as before, except 21 & 22 are now
in ID order:

  3     0.1
42     0
  1     0.1
41     3
21     23
22     23
55     0.5
  2     0.1
56     0

 From all of this, it looks like:

1) if any CF is included in the ORDER BY criteria, then all are used
2) they are used in increasing ID order
3) Numeric CFs sort lexicographically

Thanks for any help given/offered!


Here's my System Configuration:

Perl v5.8.5 under linux
   Apache::Session v1.81;
   Apache::Session::File v1.54;
   Apache::Session::Generate::MD5 v2.1;
   Apache::Session::Lock::File v1.02;
   Apache::Session::Serialize::Storable v1.00;
   Apache::Session::Store::File v1.02;
   AutoLoader v5.60;
   base v2.06;
   Benchmark v1.06;
   bytes v1.01;
   Cache::Simple::TimedExpiry v0.27;
   Carp v1.03;
   CGI v3.05;
   CGI::Cookie v1.24;
   CGI::Fast v1.05;
   CGI::Util v1.5;
   Class::Container v0.12;
   Class::Data::Inheritable v0.06;
   Class::ReturnValue v0.53;
   Clone v0.22;
   constant v1.04;
   Cwd v3.24;
   Data::Dumper v2.121;
   Date::Format v2.22;
   Date::Parse v2.27;
   DBD::Oracle v1.19;
   DBI v1.53;
   DBIx::SearchBuilder v1.45;
   DBIx::SearchBuilder::Union v0;
   DBIx::SearchBuilder::Unique v0.01;
   Devel::StackTrace v1.13;
   Devel::StackTraceFrame v0.6;
   Digest::base v1.00;
   Digest::MD5 v2.33;
   DynaLoader v1.05;
   Encode v2.01;
   Encode::Alias v2.00;
   Encode::Config v2.00;
   Encode::Encoding v2.00;
   Encode::Guess v2.00;
   Encode::Unicode v2.00;
   Errno v1.09;
   Exception::Class v1.23;
   Exception::Class::Base v1.2;
   Exporter v5.58;
   Exporter::Heavy v5.58;
   FCGI v0.67;
   Fcntl v1.05;
   File::Basename v2.73;
   File::Glob v1.03;
   File::Path v1.06;
   File::Spec v3.24;
   File::Spec::Unix v1.5;
   File::Temp v0.17;
   FileHandle v2.01;
   HTML::Element v3.23;
   HTML::Entities v1.35;
   HTML::Formatter v2.04;
   HTML::FormatText v2.04;
   HTML::Mason v1.35;
   HTML::Mason::CGIHandler v1.00;
   HTML::Mason::Exception v1.1;
   HTML::Mason::Exception::Abort v1.1;
   HTML::Mason::Exception::Compilation v1.1;
   HTML::Mason::Exception::Compilation::IncompatibleCompiler v1.1;
   HTML::Mason::Exception::Compiler v1.1;
   HTML::Mason::Exception::Decline v1.1;
   HTML::Mason::Exception::Params v1.1;
   HTML::Mason::Exception::Syntax v1.1;
   HTML::Mason::Exception::System v1.1;
   HTML::Mason::Exception::TopLevelNotFound v1.1;
   HTML::Mason::Exception::VirtualMethod v1.1;
   HTML::Mason::Exceptions v1.43;
   HTML::Parser v3.56;
   HTML::Scrubber v0.08;
   HTML::Tagset v3.03;
   HTML::TreeBuilder v3.23;
   HTTP::Date v1.46;
   I18N::LangTags v0.33;
   I18N::LangTags::Detect v1.03;
   integer v1.00;
   IO v1.21;
   IO::File v1.10;
   IO::Handle v1.24;
   IO::InnerFile v2.110;
   IO::Lines v2.110;
   IO::Scalar v2.110;
   IO::ScalarArray v2.110;
   IO::Seekable v1.09;
   IO::Wrap v2.110;
   IO::WrapTie v2.110;
   IPC::Open2 v1.01;
   IPC::Open3 v1.0106;
   lib v0.5565;
   List::Util v1.19;
   Locale::Maketext v1.09;
   Locale::Maketext::Fuzzy v0.02;
   Locale::Maketext::Lexicon v0.62;
   Locale::Maketext::Lexicon::Gettext v0.15;
   Log::Dispatch v2.16;
   Log::Dispatch::Base v1.09;
   Log::Dispatch::File v1.22;
   Log::Dispatch::Output v1.26;
   Log::Dispatch::Screen v1.17;
   Log::Dispatch::Syslog v1.18;
   Mail::Address v1.74;
   Mail::Field v1.74;
   Mail::Field::AddrList v1.74;
   Mail::Field::Date v1.74;
   Mail::Header v1.74;
   Mail::Internet v1.74;
   MIME::Base64 v3.07;
   MIME::Body v5.420;
   MIME::Decoder v5.420;
   MIME::Decoder::NBit v5.420;
   MIME::Entity v5.420;
   MIME::Field::ContDisp v5.420;
   MIME::Field::ConTraEnc v5.420;
   MIME::Field::ContType v5.420;
   MIME::Field::ParamVal v5.420;
   MIME::Head v5.420;
   MIME::Parser v5.420;
   MIME::QuotedPrint v3.07;
   MIME::Tools v5.420;
   MIME::Words v5.420;
   Module::Refresh v0.09;
   Module::Versions::Report v1.02;
   overload v1.01;
   Params::Validate v0.85;
   POSIX v1.08;
   re v0.04;
   Regexp::Common v2.120;
   Regexp::Common::delimited v2.104;
   RT v3.6.3;
   RT::Interface::Email v2;
   Scalar::Util v1.19;
   SelectSaver v1.00;
   Socket v1.77;
   Storable v2.13;
   strict v1.03;
   Symbol v1.05;
   Sys::Hostname v1.11;
   Sys::Syslog v0.08;
   Text::Autoformat v1.13;
   Text::Quoted v1.8;
   Text::Reform v1.11;
   Text::Tabs v98.112801;
   Text::Template v1.44;
   Text::Wrapper v1.000;
   Time::HiRes v1.55;
   Time::JulianDay v2003.1125;
   Time::Local v1.1;
   Time::ParseDate v2006.0814;
   Time::Timezone v2006.0814;
   Time::Zone v2.22;
   Tree::Simple v1.17;
   UNIVERSAL v1.01;
   UNIVERSAL::require v0.11;
   URI v1.30;
   URI::Escape v3.22;
   URI::URL v5.03;
   URI::WithBase v2.19;
   utf8 v1.04;
   vars v1.01;
   warnings v1.03;
   warnings::register v1.00;
   XSLoader v0.02;


RT Variables
RT::BasePath /opt/rt3
RT::BinPath /opt/rt3/bin
RT::CORE_CONFIG_FILE /opt/rt3/etc/RT_Config.pm
RT::CanonicalizeOnCreate 1
RT::CommentAddress request at localhost
RT::CorrespondAddress request at localhost
RT::DatabaseHost localhost
RT::DatabaseName request
RT::DatabasePassword Password not printed
RT::DatabaseRTHost localhost
RT::DatabaseType Oracle
RT::DatabaseUser request
RT::DateDayBeforeMonth 1
RT::DefaultSearchResultFormat '<B><A 
HREF="/Ticket/Display.html?id=__id__">__id__</a></B>/TITLE:#', '<B><A 
HREF="/Ticket/Display.html?id=__id__">__Subject__</a></B>/TITLE:Subject', 
Status, QueueName, OwnerName, Priority, '__NEWLINE__', '', 
'<small>__Requestors__</small>', '<small>__CreatedRelative__</small>', 
'<small>__ToldRelative__</small>', 
'<small>__LastUpdatedRelative__</small>', '<small>__TimeLeft__</small>'
RT::DefaultSummaryRows 10
RT::DevelMode 1
RT::EmailOutputEncoding utf-8
RT::EtcPath /opt/rt3/etc
RT::FriendlyFromLineFormat "%s via RT" <%s>
RT::FriendlyToLineFormat "%s of XXX Ticket #%s":;
RT::LocalEtcPath /opt/rt3/local/etc
RT::LocalLexiconPath /opt/rt3/local/po
RT::LocalPath /opt/rt3/local
RT::LogDir /opt/rt3/var/log
RT::LogStackTraces 1
RT::LogToFile debug
RT::LogToFileNamed rt.log
RT::LogToScreen error
RT::LogToSyslog error
RT::LogoURL /NoAuth/images/bplogo.gif
RT::LoopsToRTOwner 1
RT::MailCommand sendmailpipe
RT::MasonComponentRoot /opt/rt3/share/html
RT::MasonDataDir /opt/rt3/var/mason_data
RT::MasonLocalComponentRoot /opt/rt3/local/html
RT::MasonSessionDir /opt/rt3/var/session_data
RT::MaxAttachmentSize 10000000
RT::MaxInlineBody 13456
RT::MessageBoxWidth 72
RT::MessageBoxWrap HARD
RT::MinimumPasswordLength 5
RT::Organization XXX
RT::OwnerEmail joe at localhost
RT::ParseNewMessageForTicketCcs 1
RT::RTAddressRegexp ^request\@localhost$
RT::RecordOutgoingEmail 1
RT::RedistributeAutoGeneratedMessages privileged
RT::SITE_CONFIG_FILE /opt/rt3/etc/RT_SiteConfig.pm
RT::SendmailArguments -oi -t
RT::SendmailBounceArguments -f "<>"
RT::SendmailPath /usr/sbin/sendmail
RT::ShowTransactionImages 1
RT::StatementLog debug
RT::StrictLinkACL 1
RT::Timezone US/Eastern
RT::UseFriendlyFromLine 1
RT::VERSION 3.6.3
RT::VarPath /opt/rt3/var
RT::WebBaseURL http://localhost
RT::WebDefaultStylesheet 3.5-default
RT::WebFlushDbCacheEveryRequest 1
RT::WebImagesURL /NoAuth/images/
RT::WebPort 80
RT::WebURL http://localhost/
RT::rtname XXX

Perl configuration
Summary of my perl5 (revision 5 version 8 subversion 5) configuration:
   Platform:
     osname=linux, osvers=2.6.9-22.18.bz155725.elsmp, 
archname=i386-linux-thread-multi
     uname='linux hs20-bc1-4.build.redhat.com 2.6.9-22.18.bz155725.elsmp 
#1 smp thu nov 17 15:34:08 est 2005 i686 i686 i386 gnulinux '
     config_args='-des -Doptimize=-O2 -g -pipe -m32 -march=i386 
-mtune=pentium4 -Dversion=5.8.5 -Dmyhostname=localhost 
-Dperladmin=root at localhost -Dcc=gcc -Dcf_by=Red Hat, Inc. 
-Dinstallprefix=/usr -Dprefix=/usr -Darchname=i386-linux 
-Dvendorprefix=/usr -Dsiteprefix=/usr -Duseshrplib -Dusethreads 
-Duseithreads -Duselargefiles -Dd_dosuid -Dd_semctl_semun -Di_db 
-Ui_ndbm -Di_gdbm -Di_shadow -Di_syslog -Dman3ext=3pm -Duseperlio 
-Dinstallusrbinperl -Ubincompat5005 -Uversiononly -Dpager=/usr/bin/less 
-isr -Dinc_version_list=5.8.4 5.8.3 5.8.2 5.8.1 5.8.0'
     hint=recommended, useposix=true, d_sigaction=define
     usethreads=define use5005threads=undef useithreads=define 
usemultiplicity=define
     useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
     use64bitint=undef use64bitall=undef uselongdouble=undef
     usemymalloc=n, bincompat5005=undef
   Compiler:
     cc='gcc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DDEBUGGING 
-fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE 
-D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm',
     optimize='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4',
     cppflags='-D_REENTRANT -D_GNU_SOURCE -DDEBUGGING 
-fno-strict-aliasing -pipe -I/usr/local/include -I/usr/include/gdbm'
     ccversion='', gccversion='3.4.6 20060404 (Red Hat 3.4.6-2)', 
gccosandvers=''
     intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
     d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12
     ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', 
lseeksize=8
     alignbytes=4, prototype=define
   Linker and Libraries:
     ld='gcc', ldflags =' -L/usr/local/lib'
     libpth=/usr/local/lib /lib /usr/lib
     libs=-lresolv -lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lpthread -lc
     perllibs=-lresolv -lnsl -ldl -lm -lcrypt -lutil -lpthread -lc
     libc=/lib/libc-2.3.4.so, so=so, useshrplib=true, libperl=libperl.so
     gnulibc_version='2.3.4'
   Dynamic Linking:
     dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E 
-Wl,-rpath,/usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE'
     cccdlflags='-fPIC', lddlflags='-shared -L/usr/local/lib'




-- 
Regards,


joe
Joe Casadonte
joe.casadonte at oracle.com



More information about the rt-users mailing list