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 <[email protected]> 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 <[email protected]>
> 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: [email protected]
>> [mailto:[email protected]] 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 <[email protected]> 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?
>>
>
