RT 3.0.10 -> 3.0.12 upgrade; bug introduced

RT Users
1

I’ll preface this with the “Yeah, I’m running perl 5.6.1 and I know I
shouldn’t be, and it COULD be the problem, BUT”

Was running 3.0.10 and upgraded to .12 recently. I noticed that my tweaked
“weekly.html” stopped working. The following code produces an invalid MySQL
query on .12 but not on .10:

my $startobj = RT::Date->new($RT::SystemUser);
my $endobj = RT::Date->new($RT::SystemUser);
$startobj->Set(Format => ‘unix’, Value => (time - (86400 * 7)));
$endobj->Set(Format => ‘unix’, Value => time);

my $Creator = 96;

my $queue = new RT::Queue($RT::SystemUser);

my $tickets = new RT::Tickets($RT::SystemUser);
$tickets->LimitTransactionDate( VALUE => $startobj->ISO, OPERATOR => ‘>’);
$tickets->LimitTransactionDate( VALUE => $endobj->ISO, OPERATOR => ‘<’);
$tickets->DEBUG(1);
$tickets->Next();

I’m testing on the same machine, simply switching my “use lib” statement at
the top between versions of RT3, so all my perl libraries are remaining the
same.

The two queries produced follows - first 3.0.10’s and then 3.0.12’s:

SELECT DISTINCT main.* FROM Tickets main , Transactions Transactions_1,
Attachments Attachments_2 WHERE ((Attachments_2.TransactionId =
Transactions_1.id)AND(Attachments_2.TransactionId = Transactions_1.id)) AND
((main.EffectiveId = main.id)) AND ((main.Type = ‘ticket’)) AND ((main.id =
Transactions_1.Ticket)AND(main.id = Transactions_1.Ticket)) AND ( ( (
(Attachments_2.Created > ‘2004-09-13 18:26:39’) ) AND (
(Attachments_2.Created < ‘2004-09-20 18:26:39’) ) ) )

SELECT DISTINCT main.* FROM Tickets main , Transactions Transactions_1,
Attachments Attachments_2 WHERE ((main.EffectiveId = main.id)) AND
((main.Type = ‘ticket’)) AND ( ( ( (Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.Ticket)(Attachments_2.Created

‘2004-09-13 18:26:58’) ) AND ( (Attachments_2.TransactionId =
Transactions_1.id)AND(main.id =
Transactions_1.Ticket)AND(Attachments_2.Created < ‘2004-09-20 18:26:58’) )
) )

I’ll probably continue hunting (knowing me) but wanted to throw this out
there in case someone knows something …

-=| Ben
2

Please, update to the latest DBIx::SB.
Did you see that .10 query is also weird:
“(Attachments_2.TransactionId =
Transactions_1.id)AND(Attachments_2.TransactionId = Transactions_1.id)”
This shouldn’t happen.

ben@atomicmatrix.net wrote:

3

I’m at 1.10 for both tests.
I haven’t had a chance to test this in a ‘clean room’ yet, though.

-=| Ben
4

If I change SUBCLAUSE in _SQLJoin() from “ticketsql” to “” then the problem
goes away. This stuff is getting into the SQL guts of RT which I’m not
familiar enough with to debug in a timely fashion. This bug looks to have
been introduced by the move to the “one SB subclause” …
Can someone help me fix this bug?

-=| Ben
5

A fresh install of RT 3.0.12 on FC2 with perl 5.8.3 and SB 1.11 results in
the same problem:

SELECT DISTINCT main.* FROM Tickets main , Transactions Transactions_1,
Attachments Attachments_2 WHERE ((main.EffectiveId = main.id)) AND
((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( ( (
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket)(Attachments_2.Created > ‘2004-09-17 15:22:50’) ) AND
( (Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket)AND(Attachments_2.Created < ‘2004-09-26 23:59:59’) )
) );

It’s missing an AND up there - between the “main” clause and my “search”
clause it seems. This problem doesn’t show up with LimitStarts() - possibly
because it’s dealing with tickets instead of transactions.

Can someone help me with this? Thanks,

-=| Ben
6

This bug is introduced at the 3.0.10 to 3.0.11 upgrade.
This code

my $tickets = new RT::Tickets($RT::SystemUser);
$tickets->LimitTransactionDate(VALUE => “2004-09-17 15:22:50”, OPERATOR =>
‘>’);
$tickets->DEBUG(1);
$tickets->Next();

Produces this

SELECT DISTINCT main.* FROM Tickets main , Transactions Transactions_1,
Attachments Attachments_2 WHERE ((main.EffectiveId = main.id)) AND
((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( ( (
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket)(Attachments_2.Created > ‘2004-09-17 15:22:50’) ) ) )

I’m still no closer to finding out how to fix this. Can someone help me
through this? Perhaps show me how to debug clause building so I can find
out why there’s no “AND” above?

BTW if I have two LimitTransactionDate() statements, such as

$tickets->LimitTransactionDate(VALUE => “2004-09-17 15:22:50”, OPERATOR =>
‘>’);
$tickets->LimitTransactionDate(VALUE => “2004-09-24 15:22:50”, OPERATOR =>
‘<’);

Check out what happens:

SELECT DISTINCT main.* FROM Tickets main , Transactions Transactions_1,
Attachments Attachments_2 WHERE ((main.EffectiveId = main.id)) AND
((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( ( (
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket)(Attachments_2.Created > ‘2004-09-17 15:22:50’) ) AND
( (Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket)AND(Attachments_2.Created < ‘2004-09-24 15:22:50’) )
) )

The “Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket)” clause is duplicated as well as the missind AND
word.

Any help would be greatly appreciated. I’m really slamming into a wall on
this one.

(please note my software revisions previously mentioned in this thread)

Thanks,

-=| Ben