Searching with LIKE

Having a clue on how your search function performs its search could come in very handy. For instance, our search application makes use of SQL’s LIKE clause in the where condition. So if I were to enter a search term “hello world”, the search in the database would be something like:

select * from TABLETOSEARCH 
where searchableField like '%hello world%';

There are certain characters that work differently with LIKE. So knowing these characters could be helpful in exposing bugs that might cause the search function to behave differently from what’s expected. The table below illustrates some examples but the behavior could possible vary depending on the database being used.

Character

LIKE behavior of the character

Search term

Expected

Actual Result

‘ (apostrophe)

I don’t like patatas

Return Paul

BUG -“Sorry, an error has occurred.”

% (percent)

Allows you to match any string of any length (including zero length)

pam%ela

Shouldn’t return Pamela Lesley since her data doesn’t actually contain “pam%ela”

BUG – returned Pam

_ (underscore)

Allows you to match on a single character

pam_la le_le_

Shouldn’t return Pamela Lesley

BUG – returned Pam

[ ] (with brackets)

Allows you to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters)

[pb]amela

Shouldn’t return Pamela Lesley

BUG – returned Pam

[^] (with caret in brackets)

Allows you to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters)

[^b]amela

Shouldn’t return Pamela Lesley

BUG – returned Pam

READ MORE:

Advertisements

QC Query – test cases with related requirements

Last week, a teammate posted a question in the forums asking for a way to extract a report from ALM that will list the test cases and the requirements that they are linked to. So although, there are probably built in reports, I figured that this is something that could also be extracted via an SQL query in the dashboard.

Here’s the query I came up with:

 SELECT --REQ.*, TEST.*
       TEST.TS_TEST_ID, TEST.TS_NAME,
       REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME
 FROM  REQ_COVER,
       TEST,
       REQ
 WHERE REQ_COVER.RC_REQ_ID = REQ.RQ_REQ_ID
   AND REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID
   AND REQ_COVER.RC_ENTITY_TYPE = 'TEST'
 ORDER BY TEST.TS_TEST_ID

Another team mate shared a link to a similar forum topic where someone had already posted a similar query. The query in the other answer has the following additions:

  • It includes a join to ALL_LISTS on all_list.al_item_id = test.ts_subject — perhaps to filter by folder
  • It includes a join to REQ_TYPE on req_type.tpr_type_id = req.rq_type_id — perhaps to filter by requirement type

QC Query – Get bugs related to test cases

We were talking about ALM (formerly QC) earlier this week, and one of the test managers brought something up. So there you have your test case, and your test case will have test steps. During test execution, while at a particular test step, you can log a bug and that bug will be linked to the test step — but not to the test case. So if you look at your test case in the execution grid, it won’t have that link icon indicating that there’s a related bug. You can open each test case one-by-one and then view the indirect links, but that would be time-consuming.

We’re not sure whether the newer version of ALM would make the connection more obvious, and whether it’ll be easier to pull out info on the bugs related to the test cases. But even if it were, it might take some time for the upgrade to be available. One workaround is to go ahead and manually link the bug to the test case.

I tried sitting on it, and I came up with a query. It’s not a catch all solution but one can use it as a starting point. Given a test folder, it retrieves the test sets under that folder and also the test cases under the test sets. And based on the test cases you have, it retrieves the bugs that are linked to the test cases directly, or linked to test steps that are part of those test cases.

SELECT A.*, LN_BUG_ID, BG_STATUS
FROM
(SELECT CYCLE.CY_CYCLE_ID, CYCLE.CY_CYCLE, TEST.TS_TEST_ID, TEST.TS_NAME, TESTCYCL.TC_STATUS
 FROM CYCLE, TESTCYCL, TEST
 WHERE TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
   AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
   AND CYCLE.CY_FOLDER_ID = 103) A   -- NEED TO SPECIFY THE FOLDER ID (FROM CYCL_FOLD)
LEFT OUTER JOIN
(SELECT DISTINCT LN_BUG_ID, BG_STATUS, TEST_ID FROM (
    SELECT LINK.LN_BUG_ID, BUG.BG_STATUS, LINK.LN_ENTITY_TYPE, LINK.LN_ENTITY_ID, 
    COALESCE(STEP.ST_TEST_ID, TESTCYCL.TC_TEST_ID) AS TEST_ID
    FROM LINK
    INNER JOIN BUG 
    ON LINK.LN_BUG_ID = BUG.BG_BUG_ID
    LEFT OUTER JOIN STEP
    ON LINK.LN_ENTITY_ID = STEP.ST_ID AND LINK.LN_ENTITY_TYPE = 'STEP'
    LEFT OUTER JOIN TESTCYCL
    ON LINK.LN_ENTITY_ID = TESTCYCL.TC_TESTCYCL_ID AND LINK.LN_ENTITY_TYPE = 'TESTCYCL')) B
ON A.TS_TEST_ID = B.TEST_ID
ORDER BY CY_CYCLE_ID, TS_TEST_ID, LN_BUG_ID