[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