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

Ruslan Zakirov ruslan.zakirov at gmail.com
Mon Feb 12 11:11:52 EST 2007


I've applied the Joop's patch to DBIx::SearchBuilder module, please,
try the latest development version from the CPAN.

On 2/10/07, Joe Casadonte <joe.casadonte at oracle.com> wrote:
> 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
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>


-- 
Best regards, Ruslan.



More information about the rt-users mailing list