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:
- if any CF is included in the ORDER BY criteria, then all are used
- they are used in increasing ID order
- 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:
: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;
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@localhost
RT::CorrespondAddress request@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 ‘id/TITLE:#’, ‘Subject/TITLE:Subject’,
Status, QueueName, OwnerName, Priority, ‘NEWLINE’, ‘’,
‘Requestors’, ‘CreatedRelative’,
‘ToldRelative’,
‘LastUpdatedRelative’, ‘TimeLeft’
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@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@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@oracle.com