Setup: RT 4.2.5 with PostgreSQL and full-text indexing enabled
and completed for all tickets. Tickets 1, 2, and 3 all have
contents with the string foo.com
Searching for ‘content LIKE foo.com’ returns tickets 1, 2, 3
Searching for ‘content NOT LIKE foo.com’ returns tickets 1, 2, 3
Has anyone seen this? Any ideas what might be going wrong or how
do start debugging this?
Jeff Blaine
kickflop.net
PGP/GnuPG Key ID: 0x0C8EDD02
Values need to be quoted, I believe. Compare these two queries:
Content LIKE foo.com
Content LIKE ‘foo.com’
I wonder whether wildcards might be necessary, like they seem to be in
regular SQL:
Content LIKE '%foo.com%'On 09/08/2014 12:36 am, “Jeff Blaine” jblaine@kickflop.net wrote:
Setup: RT 4.2.5 with PostgreSQL and full-text indexing enabled
and completed for all tickets. Tickets 1, 2, and 3 all have
contents with the string foo.comSearching for ‘content LIKE foo.com’ returns tickets 1, 2, 3
Searching for ‘content NOT LIKE foo.com’ returns tickets 1, 2, 3
Has anyone seen this? Any ideas what might be going wrong or how
do start debugging this?–
Jeff Blaine
kickflop.net
PGP/GnuPG Key ID: 0x0C8EDD02RT Training - Boston, September 9-10
http://bestpractical.com/training
Values need to be quoted, I believe. Compare these two queries:
Content LIKE foo.com http://foo.com
Content LIKE ‘foo.com http://foo.com’I wonder whether wildcards might be necessary, like they seem to be in
regular SQL:Content LIKE ‘%foo.com http://foo.com%’
Setup: RT 4.2.5 with PostgreSQL and full-text indexing enabled and completed for all tickets. Tickets 1, 2, and 3 all have contents with the string foo.com <http://foo.com> Searching for 'content LIKE foo.com <http://foo.com>' returns tickets 1, 2, 3 Searching for 'content NOT LIKE foo.com <http://foo.com>' returns tickets 1, 2, 3 Has anyone seen this? Any ideas what might be going wrong or how do start debugging this?
I ran a search with StatementLogging enabled and this is the sql
statement with “content not like ‘foo.com’” and “content like
‘foo.com’”, they are the same.
SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions
Transactions_1 ON ( Transactions_1.ObjectType = ‘RT::Ticket’ ) AND (
Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON (
Attachments_2.TransactionId = Transactions_1.id ) WHERE (main.IsMerged
IS NULL) AND (main.Status != ‘deleted’) AND (main.Type = ‘ticket’) AND (
( Attachments_2.ContentIndex @@ plainto_tsquery(‘foo.com’) ) ) ;
Seems the last AND should really be a AND NOT.
In the past SearchBuilder always pre and postpended % around search
terms. Not sure if it still does but I think so.
Joop
I ran a search with StatementLogging enabled and this is the sql
statement with “content not like ‘foo.com’” and “content like
‘foo.com’”, they are the same.SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions
Transactions_1 ON ( Transactions_1.ObjectType = ‘RT::Ticket’ ) AND (
Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON
( Attachments_2.TransactionId = Transactions_1.id ) WHERE
(main.IsMerged IS NULL) AND (main.Status != ‘deleted’) AND (main.Type
= ‘ticket’) AND ( ( Attachments_2.ContentIndex @@
plainto_tsquery(‘foo.com’) ) ) ;Seems the last AND should really be a AND NOT.
In the past SearchBuilder always pre and postpended % around search
terms. Not sure if it still does but I think so.Joop
This was on rt-4.0.21 and rt-4.2.26, Searchbuilder-1.59 and 1.65
respectively
Joop
[ “I ran a search with StatementLogging enabled and this ]
[ is the sql statement with “content not like ‘foo.com’” ]
[ and “content like ‘foo.com’”, they are the same.” ]
[ --Thanks for that, Joop! ]
Alex, to be clear, these queries below were done via the search
form. I am only quoting the SearchBuilder terms for simplicity.
The form stated:
[ Content ] [ matches ] __foo.com_______
And
[ Content ] [ doesn't match ] __foo.com_______
and I clicked search to get the same 3 tickets as results for both.
I can’t see how, in some way or another, this is not a bug.On 8/8/2014 8:38 PM, Alex Peters wrote:
Values need to be quoted, I believe. Compare these two queries:
Content LIKE foo.com http://foo.com
Content LIKE ‘foo.com http://foo.com’I wonder whether wildcards might be necessary, like they seem to be in
regular SQL:Content LIKE ‘%foo.com http://foo.com%’
On 09/08/2014 12:36 am, “Jeff Blaine” <jblaine@kickflop.net mailto:jblaine@kickflop.net> wrote:
Setup: RT 4.2.5 with PostgreSQL and full-text indexing enabled and completed for all tickets. Tickets 1, 2, and 3 all have contents with the string foo.com <http://foo.com> Searching for 'content LIKE foo.com <http://foo.com>' returns tickets 1, 2, 3 Searching for 'content NOT LIKE foo.com <http://foo.com>' returns tickets 1, 2, 3 Has anyone seen this? Any ideas what might be going wrong or how do start debugging this? -- Jeff Blaine kickflop.net <http://kickflop.net> PGP/GnuPG Key ID: 0x0C8EDD02 -- RT Training - Boston, September 9-10 http://bestpractical.com/training
Jeff Blaine
kickflop.net
PGP/GnuPG Key ID: 0x0C8EDD02
[ “I ran a search with StatementLogging enabled and this ]
[ is the sql statement with “content not like ‘foo.com’” ]
[ and “content like ‘foo.com’”, they are the same.” ]
[ --Thanks for that, Joop! ]Alex, to be clear, these queries below were done via the search
form. I am only quoting the SearchBuilder terms for simplicity.The form stated:
[ Content ] [ matches ] __foo.com_______And
[ Content ] [ doesn't match ] __foo.com_______and I clicked search to get the same 3 tickets as results for both.
I can’t see how, in some way or another, this is not a bug.
“Content doesn’t match” indeed uses the same codepath as “content does
match.” The problem is that not all of the FTS backends support “NOT
MATCH” – and for those that do, it likely doesn’t do what you expect.
I can guarantee that for any particular phrase, there exists one
transaction on the ticket which does not contain that phrase – thus
always matching all tickets.
Can you explain your use case a bit? I expect that you meant it as
“exclude tickets which do contain”?
I expect we should, at least in the short term, document that “CONTENT
NOT LIKE” is not supported, and make it return the empty set.
Can you explain your use case a bit? I expect that you meant it as
“exclude tickets which do contain”?
That’s correct.
Jeff Blaine
kickflop.net
PGP/GnuPG Key ID: 0x0C8EDD02
[ “I ran a search with StatementLogging enabled and this ]
[ is the sql statement with “content not like ‘foo.com’” ]
[ and “content like ‘foo.com’”, they are the same.” ]
[ --Thanks for that, Joop! ]Alex, to be clear, these queries below were done via the search
form. I am only quoting the SearchBuilder terms for simplicity.The form stated:
[ Content ] [ matches ] __foo.com_______And
[ Content ] [ doesn't match ] __foo.com_______and I clicked search to get the same 3 tickets as results for both.
I can’t see how, in some way or another, this is not a bug.
“Content doesn’t match” indeed uses the same codepath as “content does
match.” The problem is that not all of the FTS backends support “NOT
MATCH” – and for those that do, it likely doesn’t do what you expect.
I can guarantee that for any particular phrase, there exists one
transaction on the ticket which does not contain that phrase – thus
always matching all tickets.
Running the query from the statementlog and modifying as described
(adding a not) does what it needs todo, exclude all tickets that have
that phrase in it. This is using Pg as a backend. Oracle works too.
Mysql I don’t know because I don’t use it for RT.
Joop