Mantis Bugtracker          
testlink.org

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007221TestLinkFilterspublic2015-08-10 08:172015-08-11 05:35
Reporteranushajoisa 
Assigned Tofman 
PrioritynormalSeveritymajorReproducibilityalways
StatusassignedResolutionopen 
PlatformWindowsOSWindowsOS VersionServer 2012 R2
Product Version1.9.13 (2015 #1) 
Fixed in Version 
Summary0007221: MSSQL - Filters on execution results causes DB access error
DescriptionMSSQL database.
Filters in the 'Test Execution' section for 'Result' cause DB access errors and are not working in the case of MSSQL database.
Steps To Reproduce1. Create a test project T1.
2. Create 3 test cases TC1, TC2 and TC3.
3. Create a test plan TP1 and a platform P1 for this test plan. Add this platform to the test plan TP1.
4. Add the test cases TC1, TC2 and TC3 on the platform P1 to the test plan TP1.
5. Create builds B1 and B2.
6. Go to 'Test Execution' section. Execute the all the three test cases as pass on build B1. On build B2, execute any one or two test cases.
7. In the filters section on the left hand pane, select 'Passed' for 'Result ' and 'ALL Builds' for 'on' and click 'Apply'.
Additional InformationThe log in the 'Events' section shows:


ERROR ON exec_query() - database.class.php
207 - SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'tcase_id'. SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'tcase_id'. - /* Class:testplan - Method: getHitsStatusSetFullOnPlatform */ /* Count() to be used on HAVING */ SELECT COUNT(0) AS COUNTER ,NHTCV.parent_id AS tcase_id FROM testplan_tcversions TPTCV JOIN builds B ON B.testplan_id = TPTCV.testplan_id /* Get Test Case ID */ JOIN nodes_hierarchy NHTCV ON NHTCV.id = TPTCV.tcversion_id /* Get Latest Execution by BUILD and PLATFORM */ JOIN ( SELECT EE.tcversion_id,EE.testplan_id ,EE.platform_id ,EE.build_id , MAX(EE.id) AS id FROM executions EE WHERE EE.testplan_id = 203924 AND EE.build_id IN (566,621) AND EE.platform_id = 31 GROUP BY EE.tcversion_id,EE.testplan_id ,EE.platform_id ,EE.build_id ) AS LEBBP ON LEBBP.testplan_id = TPTCV.testplan_id AND LEBBP.platform_id = TPTCV.platform_id AND LEBBP.build_id = B.id AND LEBBP.tcversion_id = TPTCV.tcversion_id /* Get STATUS INFO From Executions */ JOIN executions E ON E.id = LEBBP.id AND E.tcversion_id = LEBBP.tcversion_id AND E.testplan_id = LEBBP.testplan_id AND E.platform_id = LEBBP.platform_id AND E.build_id = LEBBP.build_id WHERE TPTCV.testplan_id = 203924 AND TPTCV.platform_id=31 AND E.build_id IN (566,621) AND E.status IN ('p') GROUP BY tcase_id HAVING COUNT(0) = 2



THE MESSAGE : /* Class:testplan - Method: getHitsStatusSetFullOnPlatform */ /* Count() to be used on HAVING */ SELECT COUNT(0) AS COUNTER ,NHTCV.parent_id AS tcase_id FROM testplan_tcversions TPTCV JOIN builds B ON B.testplan_id = TPTCV.testplan_id /* Get Test Case ID */ JOIN nodes_hierarchy NHTCV ON NHTCV.id = TPTCV.tcversion_id /* Get Latest Execution by BUILD and PLATFORM */ JOIN ( SELECT EE.tcversion_id,EE.testplan_id ,EE.platform_id ,EE.build_id , MAX(EE.id) AS id FROM executions EE WHERE EE.testplan_id = 203924 AND EE.build_id IN (566,621) AND EE.platform_id = 31 GROUP BY EE.tcversion_id,EE.testplan_id ,EE.platform_id ,EE.build_id ) AS LEBBP ON LEBBP.testplan_id = TPTCV.testplan_id AND LEBBP.platform_id = TPTCV.platform_id AND LEBBP.build_id = B.id AND LEBBP.tcversion_id = TPTCV.tcversion_id /* Get STATUS INFO From Executions */ JOIN executions E ON E.id = LEBBP.id AND E.tcversion_id = LEBBP.tcversion_id AND E.testplan_id = LEBBP.testplan_id AND E.platform_id = LEBBP.platform_id AND E.build_id = LEBBP.build_id WHERE TPTCV.testplan_id = 203924 AND TPTCV.platform_id=31 AND E.build_id IN (566,621) AND E.status IN ('p') GROUP BY tcase_id HAVING COUNT(0) = 2 Query failed: errorcode[207] errormsg:SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'tcase_id'.


Here, in the function getHitsStatusSetFullOnPlatform() in testplan.class.php, in the query, if tcase_id is replaced with parent_id, the filter works successfully.

Similar DB access errors are seen with result filters for 'ANY build', 'latest execution' and 'Specific build'.

What I have observed is, in the file testplan.class.php, if all the statements "GROUP BY tcase_id" are replaced with "GROUP BY parent_id", except for the "GROUP BY tcase_id" statement inside the "if($get['otherStatus'])" of the function "getHitsSameStatusFullALOP()", the filters on result works fine.
TagsNo tags attached.
Database (MySQL,Postgres,etc)MSSQL
BrowserChrome
PHP Version
TestCaseID
QA Team - Task Workflow StatusTBD
Attached Filespng file icon Result_filter_DB_error.png [^] (145,253 bytes) 2015-08-10 08:17

- Relationships
related to 0007226closedfman MSSQL - DB SPECIFIC - Group by issue - 'Requirements Overview 

-  Notes
(0023708)
fman (administrator)
2015-08-10 09:57

>> What I have observed is, in the file testplan.class.php, if all the statements >>"GROUP BY tcase_id" are replaced with "GROUP BY parent_id", except for the >>"GROUP BY tcase_id" statement inside the "if($get['otherStatus'])" of the >>function "getHitsSameStatusFullALOP()", the filters on result works fine.


This can be as usual an issue regarding HOW each DB TYPE manage ALIAS or NOT ON GROUP by.
I'm going to check for a cross-db solution.
(0023713)
anushajoisa (reporter)
2015-08-10 10:31

Ok. I have also seen a DB access error when 'Requirements Overview' Section is accessed. It lists the requirements, but DB access error is seen. This is due to the same issue mentioned in this ticket: In the file requiremen_mgr.class.php, in the function getRelationsCounters(), if the column name is mentioned for the "GROUP BY" instead of the alias, the issue is not seen.
(0023719)
fman (administrator)
2015-08-10 18:03

Would you mind getting latest code
(https://github.com/TestLinkOpenSourceTRMS/testlink-code/commit/ce593dafb27337e5dfd8404a9ee33ee72692a67b [^])

retest & provide feedback?
(0023721)
fman (administrator)
2015-08-10 18:10

Please check also releated issue with it's own commit
(0023727)
anushajoisa (reporter)
2015-08-11 04:12

All the filters are working fine with the code you provided except one: results filter on "ALL builds" in the case where there is no platform on the test plan.

The DB error seen is:

ERROR ON exec_query() - database.class.php
207 - SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'parent_id'. SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'parent_id'. - /* Class:testplan - Method: getHitsSameStatusFullALOP */ /* Count() to be used on HAVING - ALOP */ SELECT COUNT(0) AS COUNTER ,tcase_id FROM ( SELECT DISTINCT NHTCV.parent_id AS tcase_id, E.build_id FROM testplan_tcversions TPTCV JOIN builds B ON B.testplan_id = TPTCV.testplan_id /* Get Test Case ID */ JOIN nodes_hierarchy NHTCV ON NHTCV.id = TPTCV.tcversion_id /* Get Latest Execution by BUILD IGNORE PLATFORM */ JOIN ( SELECT EE.tcversion_id,EE.testplan_id ,EE.build_id , MAX(EE.id) AS id FROM executions EE WHERE EE.testplan_id = 95626 AND EE.build_id IN (371,217,157,188,196,213,214,229,231,236,299,339,341,363,384,392,480,485,489,505,514,521,526,528,532,536,555,558,570,576,582,583,585,587,586,549,320,143,329,513,512,553,567,572,575) GROUP BY EE.tcversion_id,EE.testplan_id ,EE.build_id ) AS LEX ON LEX.testplan_id = TPTCV.testplan_id AND LEX.build_id = B.id AND LEX.tcversion_id = TPTCV.tcversion_id /* Get STATUS INFO From Executions */ JOIN executions E ON E.id = LEX.id AND E.tcversion_id = LEX.tcversion_id AND E.testplan_id = LEX.testplan_id AND E.build_id = LEX.build_id WHERE TPTCV.testplan_id = 95626 AND E.build_id IN (371,217,157,188,196,213,214,229,231,236,299,339,341,363,384,392,480,485,489,505,514,521,526,528,532,536,555,558,570,576,582,583,585,587,586,549,320,143,329,513,512,553,567,572,575) AND E.status IN ('p') ) SQX GROUP BY parent_id HAVING COUNT(0) = 45


THE MESSAGE : /* Class:testplan - Method: getHitsSameStatusFullALOP */ /* Count() to be used on HAVING - ALOP */ SELECT COUNT(0) AS COUNTER ,tcase_id FROM ( SELECT DISTINCT NHTCV.parent_id AS tcase_id, E.build_id FROM testplan_tcversions TPTCV JOIN builds B ON B.testplan_id = TPTCV.testplan_id /* Get Test Case ID */ JOIN nodes_hierarchy NHTCV ON NHTCV.id = TPTCV.tcversion_id /* Get Latest Execution by BUILD IGNORE PLATFORM */ JOIN ( SELECT EE.tcversion_id,EE.testplan_id ,EE.build_id , MAX(EE.id) AS id FROM executions EE WHERE EE.testplan_id = 95626 AND EE.build_id IN (371,217,157,188,196,213,214,229,231,236,299,339,341,363,384,392,480,485,489,505,514,521,526,528,532,536,555,558,570,576,582,583,585,587,586,549,320,143,329,513,512,553,567,572,575) GROUP BY EE.tcversion_id,EE.testplan_id ,EE.build_id ) AS LEX ON LEX.testplan_id = TPTCV.testplan_id AND LEX.build_id = B.id AND LEX.tcversion_id = TPTCV.tcversion_id /* Get STATUS INFO From Executions */ JOIN executions E ON E.id = LEX.id AND E.tcversion_id = LEX.tcversion_id AND E.testplan_id = LEX.testplan_id AND E.build_id = LEX.build_id WHERE TPTCV.testplan_id = 95626 AND E.build_id IN (371,217,157,188,196,213,214,229,231,236,299,339,341,363,384,392,480,485,489,505,514,521,526,528,532,536,555,558,570,576,582,583,585,587,586,549,320,143,329,513,512,553,567,572,575) AND E.status IN ('p') ) SQX GROUP BY parent_id HAVING COUNT(0) = 45 Query failed: errorcode[207] errormsg:SQLState: 42S22 Error Code: 207 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'parent_id'.

This is because as I had mentioned previously, 'The "GROUP BY tcase_id" statement inside the "if($get['otherStatus'])" of the function "getHitsSameStatusFullALOP()" should not be replaced with "GROUP BY parent_id" '. This is the only place where the "GROUP BY tcase_id" should be replaced as it is in the case of MSSQL also.
(0023728)
anushajoisa (reporter)
2015-08-11 04:13

Note that the above error is seen only in the case where the no platform has been added to the test plan and filter is done for any execution status except 'Not Run'.
(0023731)
fman (administrator)
2015-08-11 05:35

ok will see

- Issue History
Date Modified Username Field Change
2015-08-10 08:17 anushajoisa New Issue
2015-08-10 08:17 anushajoisa File Added: Result_filter_DB_error.png
2015-08-10 09:57 fman Note Added: 0023708
2015-08-10 09:58 fman QA Team - Task Workflow Status => TBD
2015-08-10 09:58 fman Summary Filters on execution results causes DB access error => MSSQL - Filters on execution results causes DB access error
2015-08-10 09:58 fman Description Updated View Revisions
2015-08-10 10:31 anushajoisa Note Added: 0023713
2015-08-10 10:33 fman Relationship added related to 0007226
2015-08-10 18:03 fman Note Added: 0023719
2015-08-10 18:03 fman Assigned To => fman
2015-08-10 18:03 fman Status new => feedback
2015-08-10 18:10 fman Note Added: 0023721
2015-08-11 04:12 anushajoisa Note Added: 0023727
2015-08-11 04:12 anushajoisa Status feedback => assigned
2015-08-11 04:13 anushajoisa Note Added: 0023728
2015-08-11 05:35 fman Note Added: 0023731



Copyright © 2000 - 2019 MantisBT Team
Powered by Mantis Bugtracker