[rt-users] Users Autocomplete not working 4.0.7?

[Shawn Plummer]

I further broke the query down.

SELECT DISTINCT main.id, main.EmailAddress, main.name
FROM Users main
JOIN Principals Principals_1  ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = '0')
AND (
       lower(main.RealName) LIKE '%taf2%'
       OR lower(main.EmailAddress) LIKE 'taf2%'
       OR lower(main.Name) LIKE 'taf2%'
       )
AND (lower(Principals_1.PrincipalType) = 'user')
;

Returns the user record (I added emailaddress and name to the query values to verify they were not empty.)

Adding this line makes it return no values.

AND (
       main.EmailAddress != ''
       AND main.EmailAddress IS NOT NULL
       )

Again, I can see the EmailAddress is populated as is name.

- Shawn Plummer
Systems Manager

On Aug 30, 2012, at 8:55 PM, Shawn Plummer <plummer at geneseo.edu> wrote:

> Thanks for breaking those up! I am digging into the SQL tonight too. The distinct query appears to return no results in my testing so far.
> 
> As for the username change, that user was easier to isolate since it had less activity than I did when our DBA ran the query. The user definitely exists, has an email, and is enabled. We do use external auth and LDAP Import. Both are still working since the upgrade.
> 
> select id, Name, EmailAddress from Users where Name LIKE 'taf2%' or EmailAddress LIKE 'taf2%';
> returns the user.
> 
> - Shawn Plummer
> Systems Manager
> 
> 
> On Aug 30, 2012, at 8:09 PM, Chris O'Kelly <Chris.okelly at minecorp.com.au> wrote:
> 
>> Hi Guys,
>> 
>> Have you tried separating out the subqueries there?
>> If it were me that's what I would look at next. Here they are split up into individual queries (where aliases weren't used already I have given them generic names):
>> 
>> distinctQuery:
>> SELECT DISTINCT main.id
>> FROM Users main
>> JOIN Principals Principals_1  ON ( Principals_1.id = main.id )
>> WHERE (Principals_1.Disabled = '0')
>> AND (
>>       lower(main.RealName) LIKE '%taf2%'
>>       OR lower(main.EmailAddress) LIKE 'taf2%'
>>       OR lower(main.Name) LIKE 'taf2%'
>>       )
>> AND (lower(Principals_1.PrincipalType) = 'user')
>> AND (
>>       main.EmailAddress != ''
>>       AND main.EmailAddress IS NOT NULL
>>       )
>> 
>> 
>> limitQuery:
>> SELECT main.*
>> FROM distinctquery, Users main
>> WHERE (main.id = distinctquery.id)
>> ORDER BY main.Name ASC
>> 
>> 
>> thirdQuery:
>> SELECT limitquery.*,rownum limitrownum
>> FROM limitquery
>> WHERE rownum <= 10
>> 
>> finalQuery:
>> SELECT *
>> FROM thirdQuery
>> WHERE limitrownum >= 1;
>> 
>> so first run distinctQuery - This part should essentially get you the list of users who match what you are typing (in this case, users whose real name contains taf2 or whose email or username starts with taf2; who are also user's with valid email addresses; who are not disabled). If this returns no rows there are serious problems - either there is definitely no user matching that condition or there are serious Db issues. The next three queries are trivial really. limitQuery selects the id of the matched users and orders by username. thirdQuery (I'm pretty creative when it comes to naming) limits the results to 10.
>> 
>> but yeah, to reiterate, I would suggest just running the deepest level query (the one I have marked distinctQuery above) to see if you get results from that. If you do, heck the other queries in order to find the point of failure. Otherwise, all I can really suggest is to take a serious look at the Users table and confirm there is a user there who matches the conditions. Make absolute sure the user is not disabled, make absolute sure that taf2 appears somewhere in the Real Name field, NOT the nickname field (or add nickname to $UserAutocompleteFields in RT_SiteConfig.pm). If your user's are synced from LDAP/AD, then they may have these details in that system but the configuration has not yet been setup to sync these into LDAP.
>> 
>> I saw in an earlier post you were checking with plummer, your username, but in these queries it suggests we were looking for taf2. If taf2 is not yourself you may be running into a confusion I experienced when I installed RT - that is, I set up the External Auth plugin but not the LDAP Import plugin. Your users may not yet be in the RT database if they have not yet logged in if this is the case.
>> 
>> I know I've covered a bunch of fairly basic things here, so I am really sorry if this is all stuff you have already checked out. It can be tough to guess someone's level of expertise over the internet, so while it's likely you know a whole lot more than me, I didn't want to leave anything out that might help you.
>> 
>> HTH
>> 
>> 
>> Regards
>> 
>> Chris O'Kelly
>> Web Administrator
>> 
>> Minecorp Australia
>> P: 07 3723 1000
>> M: 0450 586 190
>> 
>> 
>> Minecorp Australia
>> 37 Murdoch Circuit
>> Acacia Ridge QLD 4110
>> www.minecorp.com.au
>> Sent Via a Mobile Device.
>> 
>> 
>> -----Original Message-----
>> From: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Shawn Plummer
>> Sent: Friday, 31 August 2012 9:28 AM
>> To: RT Users
>> Subject: Re: [rt-users] Users Autocomplete not working 4.0.7?
>> 
>> Looks like it returns no rows. So that could be a problem!
>> 
>> SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012
>> 
>> Copyright (c) 1982, 2011, Oracle.  All rights reserved.
>> 
>> 
>> Connected to:
>> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
>> With the Partitioning, OLAP, Data Mining and Real Application Testing options
>> 
>> SQL> SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1  ON ( Principals_1.id = main.id )  WHERE (Principals_1.Disabled = '0') AND (lower(main.RealName) LIKE '%taf2%' OR lower(main.EmailAddress) LIKE 'taf2%' OR lower(main.Name) LIKE 'taf2%') AND (lower(Principals_1.PrincipalType) = 'user') AND (main.EmailAddress != '' AND main.EmailAddress IS NOT NULL)  ) distinctquery, Users main WHERE (main.id = distinctquery.id)  ORDER BY main.Name ASC  ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;
>> 
>> no rows selected
>> 
>> SQL>
>> 
>> 
>> On Aug 30, 2012, at 4:49 PM, Thomas Sibley <trs at bestpractical.com> wrote:
>> 
>>> Please keep replies on the list for the benefit of folks searching for
>>> answers later.
>>> 
>>> On 08/30/2012 01:16 PM, Shawn Plummer wrote:
>>>> My DBA tells me that this is the query she sees in the database when
>>>> the autocomplete fires:
>>>> 
>>>>> "SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
>>>>> SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main JOIN
>>>>> Principals Principals_1  ON ( Principals_1.id = main.id )  WHERE
>>>>> (Principals_1.Disabled = '0') AND (lower(main.RealName) LIKE
>>>>> '%taf2%' OR lower(main.EmailAddress) LIKE 'taf2%' OR
>>>>> lower(main.Name) LIKE 'taf2%') AND
>>>>> (lower(Principals_1.PrincipalType) = 'user') AND (main.EmailAddress
>>>>> != '' AND main.EmailAddress IS NOT NULL)  ) distinctquery, Users
>>>>> main WHERE (main.id = distinctquery.id)  ORDER BY main.Name ASC  )
>>>>> limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1"
>>> 
>>> If you run that (awful awful) query by hand, what does it get you?
>> 
>