/* * test_sql1999.prg -- SQL:1999/2003 Comprehensive Feature Test Suite * * Tests ALL SQL:1999 and SQL:2003 features implemented in FiveSql: * - WITH (CTE) non-recursive * - WITH RECURSIVE * - Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG/COUNT OVER) * - SQL-92 Full Features (LIKE ESCAPE, SAVEPOINT, TRUNCATE, CHECK, UNIQUE, FK) * - MERGE / UPSERT * - Combined Advanced Queries * * FiveSql -- SQL Engine for Harbour DBF/NTX * * Copyright (c) 2025-2026 Charles KWON (Charles KWON OhJun) * Email: charleskwonohjun@gmail.com * * All rights reserved. */ #include "dbstruct.ch" STATIC s_nPass := 0 STATIC s_nFail := 0 STATIC s_nTotal := 0 PROCEDURE Main() ErrorBlock( {|e| QOut( "TRAP: " + e:description + " " + e:operation ), Break(e) } ) ? "================================================================" ? " FiveSql SQL:1999/2003 Comprehensive Feature Test Suite" ? "================================================================" ? BEGIN SEQUENCE SetupData() RECOVER ? "FATAL: SetupData() failed -- aborting" QUIT END SEQUENCE ? "--- Section 1: WITH (CTE) Non-Recursive ---" TestCTE() ? "" ? "--- Section 2: WITH RECURSIVE ---" TestRecursiveCTE() ? "" ? "--- Section 3: Window Functions ---" TestWindowFunctions() ? "" ? "--- Section 4: SQL-92 Full Features ---" TestSQL92Full() ? "" ? "--- Section 5: MERGE / UPSERT ---" TestMerge() ? "" ? "--- Section 6: Combined Advanced Queries ---" TestCombined() BEGIN SEQUENCE CleanupData() RECOVER ? " (cleanup encountered errors, continuing)" END SEQUENCE ? "" ? "================================================================" ? " RESULTS" ? " Pass: " + hb_ntos( s_nPass ) ? " Fail: " + hb_ntos( s_nFail ) ? " Total: " + hb_ntos( s_nTotal ) ? " Rate: " + hb_ntos( Int( s_nPass * 100 / Max( s_nTotal, 1 ) ) ) + "%" ? "================================================================" RETURN /* ====================================================================== */ /* Assertion helper */ /* ====================================================================== */ STATIC FUNCTION Assert( cLabel, lOK ) s_nTotal++ IF lOK s_nPass++ ? " PASS: " + cLabel ELSE s_nFail++ ? " FAIL: " + cLabel ENDIF RETURN NIL /* ====================================================================== */ /* Safe query wrapper -- returns error result on RECOVER */ /* ====================================================================== */ STATIC FUNCTION Try( cSQL ) LOCAL aR BEGIN SEQUENCE aR := five_SQL( cSQL ) RECOVER aR := { { "__error__" }, {} } END SEQUENCE RETURN aR STATIC FUNCTION Rows( aR ) IF ValType( aR ) == "A" .AND. Len( aR ) >= 2 RETURN Len( aR[ 2 ] ) ENDIF RETURN 0 STATIC FUNCTION Val1( aR ) IF ValType( aR ) == "A" .AND. Len( aR ) >= 2 .AND. Len( aR[ 2 ] ) > 0 .AND. Len( aR[ 2 ][ 1 ] ) > 0 RETURN aR[ 2 ][ 1 ][ 1 ] ENDIF RETURN NIL STATIC FUNCTION IsErr( aR ) IF ValType( aR ) == "A" .AND. Len( aR ) >= 1 .AND. Len( aR[ 1 ] ) > 0 RETURN aR[ 1 ][ 1 ] == "__error__" ENDIF RETURN .F. STATIC FUNCTION CellVal( aR, nRow, nCol ) IF ValType( aR ) == "A" .AND. Len( aR ) >= 2 .AND. ; nRow <= Len( aR[ 2 ] ) .AND. nCol <= Len( aR[ 2 ][ nRow ] ) RETURN aR[ 2 ][ nRow ][ nCol ] ENDIF RETURN NIL STATIC FUNCTION ColName( aR, nCol ) IF ValType( aR ) == "A" .AND. Len( aR ) >= 1 .AND. nCol <= Len( aR[ 1 ] ) RETURN Upper( AllTrim( aR[ 1 ][ nCol ] ) ) ENDIF RETURN "" /* ====================================================================== */ /* Setup: Create employees, orders, products tables */ /* ====================================================================== */ STATIC FUNCTION SetupData() LOCAL aStruct /* ---- employees table (10 rows) ---- */ aStruct := { ; { "ID", "N", 10, 0 }, ; { "NAME", "C", 30, 0 }, ; { "DEPT", "C", 20, 0 }, ; { "SALARY", "N", 12, 2 }, ; { "MGR_ID", "N", 10, 0 } ; } IF hb_FileExists( "employees.dbf" ) FErase( "employees.dbf" ) ENDIF dbCreate( "employees.dbf", aStruct ) USE employees.dbf NEW EXCLUSIVE /* id, name, dept, salary, mgr_id (0=NULL) */ dbAppend() ; FieldPut(1, 1) ; FieldPut(2, "Alice") ; FieldPut(3, "Engineering") ; FieldPut(4, 8000) ; FieldPut(5, 0) dbAppend() ; FieldPut(1, 2) ; FieldPut(2, "Bob") ; FieldPut(3, "Engineering") ; FieldPut(4, 7000) ; FieldPut(5, 1) dbAppend() ; FieldPut(1, 3) ; FieldPut(2, "Charlie") ; FieldPut(3, "Engineering") ; FieldPut(4, 6000) ; FieldPut(5, 1) dbAppend() ; FieldPut(1, 4) ; FieldPut(2, "Diana") ; FieldPut(3, "Sales") ; FieldPut(4, 7500) ; FieldPut(5, 0) dbAppend() ; FieldPut(1, 5) ; FieldPut(2, "Eve") ; FieldPut(3, "Sales") ; FieldPut(4, 5000) ; FieldPut(5, 4) dbAppend() ; FieldPut(1, 6) ; FieldPut(2, "Frank") ; FieldPut(3, "Sales") ; FieldPut(4, 4500) ; FieldPut(5, 4) dbAppend() ; FieldPut(1, 7) ; FieldPut(2, "Grace") ; FieldPut(3, "Marketing") ; FieldPut(4, 6500) ; FieldPut(5, 0) dbAppend() ; FieldPut(1, 8) ; FieldPut(2, "Henry") ; FieldPut(3, "Marketing") ; FieldPut(4, 5500) ; FieldPut(5, 7) dbAppend() ; FieldPut(1, 9) ; FieldPut(2, "Ivy") ; FieldPut(3, "HR") ; FieldPut(4, 6000) ; FieldPut(5, 0) dbAppend() ; FieldPut(1, 10) ; FieldPut(2, "Jack") ; FieldPut(3, "HR") ; FieldPut(4, 5000) ; FieldPut(5, 9) dbCommit() CLOSE employees /* ---- orders table (15 rows) ---- */ aStruct := { ; { "ID", "N", 10, 0 }, ; { "EMP_ID", "N", 10, 0 }, ; { "PRODUCT", "C", 30, 0 }, ; { "AMOUNT", "N", 12, 2 }, ; { "ORDER_DATE", "C", 10, 0 } ; } IF hb_FileExists( "orders.dbf" ) FErase( "orders.dbf" ) ENDIF dbCreate( "orders.dbf", aStruct ) USE orders.dbf NEW EXCLUSIVE dbAppend() ; FieldPut(1, 1) ; FieldPut(2, 1) ; FieldPut(3, "Laptop") ; FieldPut(4, 2500) ; FieldPut(5, "2024-01-15") dbAppend() ; FieldPut(1, 2) ; FieldPut(2, 1) ; FieldPut(3, "Monitor") ; FieldPut(4, 800) ; FieldPut(5, "2024-03-20") dbAppend() ; FieldPut(1, 3) ; FieldPut(2, 2) ; FieldPut(3, "Keyboard") ; FieldPut(4, 150) ; FieldPut(5, "2024-02-10") dbAppend() ; FieldPut(1, 4) ; FieldPut(2, 3) ; FieldPut(3, "Mouse") ; FieldPut(4, 100) ; FieldPut(5, "2024-04-05") dbAppend() ; FieldPut(1, 5) ; FieldPut(2, 4) ; FieldPut(3, "Printer") ; FieldPut(4, 1200) ; FieldPut(5, "2024-05-12") dbAppend() ; FieldPut(1, 6) ; FieldPut(2, 4) ; FieldPut(3, "Scanner") ; FieldPut(4, 500) ; FieldPut(5, "2024-06-18") dbAppend() ; FieldPut(1, 7) ; FieldPut(2, 5) ; FieldPut(3, "Tablet") ; FieldPut(4, 900) ; FieldPut(5, "2024-07-22") dbAppend() ; FieldPut(1, 8) ; FieldPut(2, 6) ; FieldPut(3, "Phone") ; FieldPut(4, 1100) ; FieldPut(5, "2024-08-30") dbAppend() ; FieldPut(1, 9) ; FieldPut(2, 7) ; FieldPut(3, "Camera") ; FieldPut(4, 3000) ; FieldPut(5, "2024-09-05") dbAppend() ; FieldPut(1, 10) ; FieldPut(2, 7) ; FieldPut(3, "Lens") ; FieldPut(4, 1500) ; FieldPut(5, "2024-10-14") dbAppend() ; FieldPut(1, 11) ; FieldPut(2, 8) ; FieldPut(3, "Headset") ; FieldPut(4, 250) ; FieldPut(5, "2024-11-01") dbAppend() ; FieldPut(1, 12) ; FieldPut(2, 9) ; FieldPut(3, "Desk") ; FieldPut(4, 800) ; FieldPut(5, "2025-01-10") dbAppend() ; FieldPut(1, 13) ; FieldPut(2, 9) ; FieldPut(3, "Chair") ; FieldPut(4, 600) ; FieldPut(5, "2025-02-15") dbAppend() ; FieldPut(1, 14) ; FieldPut(2, 10); FieldPut(3, "Lamp") ; FieldPut(4, 200) ; FieldPut(5, "2025-03-20") dbAppend() ; FieldPut(1, 15) ; FieldPut(2, 2) ; FieldPut(3, "Webcam") ; FieldPut(4, 350) ; FieldPut(5, "2025-04-01") dbCommit() CLOSE orders /* ---- products table (6 rows) ---- */ aStruct := { ; { "ID", "N", 10, 0 }, ; { "NAME", "C", 40, 0 }, ; { "CATEGORY", "C", 20, 0 }, ; { "PRICE", "N", 12, 2 } ; } IF hb_FileExists( "products.dbf" ) FErase( "products.dbf" ) ENDIF dbCreate( "products.dbf", aStruct ) USE products.dbf NEW EXCLUSIVE dbAppend() ; FieldPut(1, 1) ; FieldPut(2, "Widget A") ; FieldPut(3, "Hardware") ; FieldPut(4, 29.99) dbAppend() ; FieldPut(1, 2) ; FieldPut(2, "Widget B") ; FieldPut(3, "Hardware") ; FieldPut(4, 49.99) dbAppend() ; FieldPut(1, 3) ; FieldPut(2, "Software Pro") ; FieldPut(3, "Software") ; FieldPut(4, 199.99) dbAppend() ; FieldPut(1, 4) ; FieldPut(2, "10% Off Special") ; FieldPut(3, "Discount") ; FieldPut(4, 9.99) dbAppend() ; FieldPut(1, 5) ; FieldPut(2, "Service Plan") ; FieldPut(3, "Service") ; FieldPut(4, 99.99) dbAppend() ; FieldPut(1, 6) ; FieldPut(2, "Gadget X") ; FieldPut(3, "Hardware") ; FieldPut(4, 149.99) dbCommit() CLOSE products RETURN NIL /* ====================================================================== */ /* Cleanup: Remove all test tables */ /* ====================================================================== */ STATIC FUNCTION CleanupData() LOCAL aFiles, i aFiles := { ; "employees.dbf", "employees.ntx", ; "orders.dbf", "orders.ntx", ; "products.dbf", "products.ntx", ; "mergetgt.dbf", "mergetgt.ntx", ; "mergesrc.dbf", "mergesrc.ntx", ; "trunc_test.dbf", "trunc_test.ntx", ; "cktbl.dbf", "cktbl.fsc", "cktbl.ntx", ; "uqtbl.dbf", "uqtbl.fsc", "uqtbl.ntx", "uqtbl_uq.ntx", ; "fk_parent.dbf", "fk_parent.ntx", ; "fk_child.dbf", "fk_child.fsc", "fk_child.ntx", ; "target_tbl.dbf", "target_tbl.ntx", ; "source_tbl.dbf", "source_tbl.ntx", ; "temp_data.dbf", "temp_data.ntx" ; } FOR i := 1 TO Len( aFiles ) IF hb_FileExists( aFiles[ i ] ) FErase( aFiles[ i ] ) ENDIF NEXT /* Also erase CTE temp files */ FErase( "__cte_high_earners.dbf" ) FErase( "__cte_dept_stats.dbf" ) FErase( "__cte_eng.dbf" ) FErase( "__cte_sales.dbf" ) FErase( "__cte_top_emps.dbf" ) FErase( "__cte_active.dbf" ) FErase( "__cte_dept_avg.dbf" ) FErase( "__cte_nums.dbf" ) FErase( "__cte_powers.dbf" ) FErase( "__cte_fib.dbf" ) FErase( "__cte_org.dbf" ) FErase( "__cte_ranked.dbf" ) FErase( "__cte_order_totals.dbf" ) FErase( "__cte_dept_summary.dbf" ) FErase( "__cte_hier.dbf" ) RETURN NIL /* ====================================================================== */ /* Section 1: WITH (CTE) -- Non-Recursive (6 tests) */ /* ====================================================================== */ STATIC PROCEDURE TestCTE() LOCAL aR /* 1a: Simple CTE */ BEGIN SEQUENCE aR := five_SQL( ; "WITH high_earners AS (SELECT * FROM employees WHERE salary > 6000) " + ; "SELECT name, salary FROM high_earners ORDER BY salary DESC" ) Assert( "1a CTE simple: high earners (salary>6000)", ; Rows( aR ) == 4 .AND. ; CellVal( aR, 1, 2 ) >= CellVal( aR, 2, 2 ) ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 1a CTE simple (exception)" END SEQUENCE /* 1b: CTE with aggregation */ BEGIN SEQUENCE aR := five_SQL( ; "WITH dept_stats AS (" + ; "SELECT dept, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees GROUP BY dept" + ; ") SELECT dept, cnt, avg_sal FROM dept_stats WHERE cnt > 1" ) Assert( "1b CTE with aggregation: dept stats cnt>1", ; Rows( aR ) >= 2 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 1b CTE with aggregation (exception)" END SEQUENCE /* 1c: Multiple CTEs */ BEGIN SEQUENCE aR := five_SQL( ; "WITH eng AS (SELECT * FROM employees WHERE dept = 'Engineering'), " + ; "sales AS (SELECT * FROM employees WHERE dept = 'Sales') " + ; "SELECT name FROM eng UNION ALL SELECT name FROM sales" ) Assert( "1c Multiple CTEs: eng + sales UNION ALL", ; Rows( aR ) == 6 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 1c Multiple CTEs (exception)" END SEQUENCE /* 1d: CTE referenced in JOIN */ BEGIN SEQUENCE aR := five_SQL( ; "WITH top_emps AS (SELECT * FROM employees WHERE salary > 6000) " + ; "SELECT t.name, o.product FROM top_emps t JOIN orders o ON t.id = o.emp_id" ) Assert( "1d CTE in JOIN: top_emps JOIN orders", ; Rows( aR ) >= 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 1d CTE in JOIN (exception)" END SEQUENCE /* 1e: CTE with subquery */ BEGIN SEQUENCE aR := five_SQL( ; "WITH active AS (SELECT * FROM employees WHERE id IN (SELECT emp_id FROM orders)) " + ; "SELECT name, dept FROM active" ) Assert( "1e CTE with subquery: active employees", ; Rows( aR ) >= 5 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 1e CTE with subquery (exception)" END SEQUENCE /* 1f: CTE in WHERE comparison */ BEGIN SEQUENCE aR := five_SQL( ; "WITH dept_avg AS (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) " + ; "SELECT e.name, e.salary, d.avg_sal FROM employees e " + ; "JOIN dept_avg d ON e.dept = d.dept WHERE e.salary > d.avg_sal" ) Assert( "1f CTE + WHERE: salary > dept average", ; ! IsErr( aR ) .AND. Rows( aR ) >= 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 1f CTE + WHERE comparison (exception)" END SEQUENCE RETURN /* ====================================================================== */ /* Section 2: WITH RECURSIVE (4 tests) */ /* ====================================================================== */ STATIC PROCEDURE TestRecursiveCTE() LOCAL aR /* 2a: Generate sequence 1-10 */ BEGIN SEQUENCE aR := five_SQL( ; "WITH RECURSIVE nums AS (" + ; "SELECT 1 AS n " + ; "UNION ALL " + ; "SELECT n + 1 FROM nums WHERE n < 10" + ; ") SELECT * FROM nums" ) Assert( "2a RECURSIVE: sequence 1-10", ; Rows( aR ) == 10 .AND. ; CellVal( aR, 1, 1 ) == 1 .AND. CellVal( aR, 10, 1 ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 2a RECURSIVE sequence (exception)" END SEQUENCE /* 2b: Generate powers of 2 */ BEGIN SEQUENCE aR := five_SQL( ; "WITH RECURSIVE powers AS (" + ; "SELECT 1 AS n, 1 AS val " + ; "UNION ALL " + ; "SELECT n + 1, val * 2 FROM powers WHERE n < 8" + ; ") SELECT n, val FROM powers" ) Assert( "2b RECURSIVE: powers of 2", ; Rows( aR ) == 8 .AND. ; CellVal( aR, 1, 2 ) == 1 .AND. CellVal( aR, 8, 2 ) == 128 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 2b RECURSIVE powers of 2 (exception)" END SEQUENCE /* 2c: Fibonacci-like */ BEGIN SEQUENCE aR := five_SQL( ; "WITH RECURSIVE fib AS (" + ; "SELECT 1 AS n, 1 AS a, 0 AS b " + ; "UNION ALL " + ; "SELECT n + 1, a + b, a FROM fib WHERE n < 8" + ; ") SELECT n, a FROM fib" ) Assert( "2c RECURSIVE: fibonacci-like sequence", ; Rows( aR ) == 8 .AND. ; CellVal( aR, 1, 2 ) == 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 2c RECURSIVE fibonacci (exception)" END SEQUENCE /* 2d: Org hierarchy traversal */ BEGIN SEQUENCE aR := five_SQL( ; "WITH RECURSIVE org AS (" + ; "SELECT id, name FROM employees WHERE mgr_id = 0 " + ; "UNION ALL " + ; "SELECT e.id, e.name FROM employees e " + ; "JOIN org o ON e.mgr_id = o.id" + ; ") SELECT * FROM org" ) Assert( "2d RECURSIVE: org hierarchy traversal", ; Rows( aR ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 2d RECURSIVE org hierarchy (exception)" END SEQUENCE RETURN /* ====================================================================== */ /* Section 3: Window Functions (12 tests) */ /* ====================================================================== */ STATIC PROCEDURE TestWindowFunctions() LOCAL aR /* 3a: ROW_NUMBER() basic */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees" ) Assert( "3a ROW_NUMBER() basic", ; Rows( aR ) == 10 .AND. CellVal( aR, 1, 3 ) == 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3a ROW_NUMBER() basic (exception)" END SEQUENCE /* 3b: ROW_NUMBER() with PARTITION BY */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees" ) Assert( "3b ROW_NUMBER() PARTITION BY dept", ; Rows( aR ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3b ROW_NUMBER() PARTITION BY (exception)" END SEQUENCE /* 3c: RANK() with ties */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees" ) Assert( "3c RANK() with ties", ; Rows( aR ) == 10 .AND. CellVal( aR, 1, 3 ) == 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3c RANK() with ties (exception)" END SEQUENCE /* 3d: DENSE_RANK() */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drank FROM employees" ) Assert( "3d DENSE_RANK() no gaps", ; Rows( aR ) == 10 .AND. CellVal( aR, 1, 3 ) == 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3d DENSE_RANK() (exception)" END SEQUENCE /* 3e: LAG() */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary FROM employees" ) Assert( "3e LAG() previous salary", ; Rows( aR ) == 10 .AND. CellVal( aR, 1, 3 ) == NIL ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3e LAG() (exception)" END SEQUENCE /* 3f: LEAD() */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary FROM employees" ) Assert( "3f LEAD() next salary", ; Rows( aR ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3f LEAD() (exception)" END SEQUENCE /* 3g: SUM() OVER PARTITION BY */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, dept, salary, SUM(salary) OVER (PARTITION BY dept) AS dept_total FROM employees" ) Assert( "3g SUM() OVER PARTITION BY: dept totals", ; Rows( aR ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3g SUM() OVER PARTITION BY (exception)" END SEQUENCE /* 3h: AVG() OVER PARTITION BY */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, dept, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees" ) Assert( "3h AVG() OVER PARTITION BY: dept averages", ; Rows( aR ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3h AVG() OVER PARTITION BY (exception)" END SEQUENCE /* 3i: COUNT(*) OVER PARTITION BY */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, dept, COUNT(*) OVER (PARTITION BY dept) AS dept_count FROM employees" ) Assert( "3i COUNT(*) OVER PARTITION BY: dept counts", ; Rows( aR ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3i COUNT(*) OVER PARTITION BY (exception)" END SEQUENCE /* 3j: Running SUM (cumulative) */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, dept, salary, SUM(salary) OVER (PARTITION BY dept ORDER BY salary) AS running_total FROM employees" ) Assert( "3j Running SUM: cumulative within partition", ; Rows( aR ) == 10 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3j Running SUM (exception)" END SEQUENCE /* 3k: Multiple window functions in same query */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, salary, " + ; "ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, " + ; "RANK() OVER (ORDER BY salary DESC) AS rnk, " + ; "SUM(salary) OVER () AS total " + ; "FROM employees" ) Assert( "3k Multiple window funcs in one query", ; Rows( aR ) == 10 .AND. CellVal( aR, 1, 3 ) == 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3k Multiple window funcs (exception)" END SEQUENCE /* 3l: Window function with WHERE */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, dept, salary, " + ; "ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn " + ; "FROM employees WHERE dept = 'Engineering'" ) Assert( "3l Window func with WHERE: Engineering only", ; Rows( aR ) == 3 .AND. CellVal( aR, 1, 3 ) == 8000 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 3l Window func with WHERE (exception)" END SEQUENCE RETURN /* ====================================================================== */ /* Section 4: SQL-92 Full Features (8 tests) */ /* ====================================================================== */ STATIC PROCEDURE TestSQL92Full() LOCAL aR, hC, lValid, lInsertFailed /* 4a: LIKE ESCAPE */ BEGIN SEQUENCE aR := five_SQL( "SELECT * FROM products WHERE name LIKE '10\% Off%' ESCAPE '\'" ) Assert( "4a LIKE ESCAPE: find literal % in name", ; Rows( aR ) == 1 .AND. ; "10%" $ AllTrim( CellVal( aR, 1, 2 ) ) ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 4a LIKE ESCAPE (exception)" END SEQUENCE /* 4b: SAVEPOINT + ROLLBACK TO */ BEGIN SEQUENCE dbCloseAll() aR := five_SQL( "SELECT salary FROM employees WHERE id = 1" ) five_SQL( "BEGIN" ) five_SQL( "UPDATE employees SET salary = salary + 1000 WHERE id = 1" ) five_SQL( "SAVEPOINT sp1" ) five_SQL( "UPDATE employees SET salary = 0 WHERE id = 1" ) five_SQL( "ROLLBACK TO sp1" ) five_SQL( "COMMIT" ) aR := five_SQL( "SELECT salary FROM employees WHERE id = 1" ) Assert( "4b SAVEPOINT + ROLLBACK TO: salary = original + 1000", ; CellVal( aR, 1, 1 ) == 9000 ) /* restore original */ five_SQL( "UPDATE employees SET salary = 8000 WHERE id = 1" ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 4b SAVEPOINT + ROLLBACK TO (exception)" END SEQUENCE /* 4c: TRUNCATE TABLE */ BEGIN SEQUENCE five_SQL( "CREATE TABLE temp_data (id INTEGER, val CHAR(10))" ) five_SQL( "INSERT INTO temp_data (id, val) VALUES (1, 'test')" ) five_SQL( "INSERT INTO temp_data (id, val) VALUES (2, 'data')" ) aR := five_SQL( "SELECT COUNT(*) AS cnt FROM temp_data" ) Assert( "4c-pre TRUNCATE: table has rows", ; CellVal( aR, 1, 1 ) == 2 ) five_SQL( "TRUNCATE TABLE temp_data" ) aR := five_SQL( "SELECT COUNT(*) AS cnt FROM temp_data" ) Assert( "4c TRUNCATE TABLE: table is empty", ; CellVal( aR, 1, 1 ) == 0 ) RECOVER s_nTotal += 2 ; s_nFail += 2 ; ? " FAIL: 4c TRUNCATE TABLE (exception)" END SEQUENCE /* 4d: CHECK constraint */ BEGIN SEQUENCE aR := five_SQL( "CREATE TABLE cktbl (id INTEGER, age INTEGER, CHECK(age >= 0 AND age <= 150))" ) hC := SqlLoadConstraints( "cktbl" ) Assert( "4d CHECK constraint: metadata stored", ; Len( hC[ "check" ] ) >= 1 ) aR := Try( "INSERT INTO cktbl (id, age) VALUES (1, 25)" ) Assert( "4d CHECK: valid insert (age=25) succeeds", ; ! IsErr( aR ) ) aR := Try( "INSERT INTO cktbl (id, age) VALUES (2, -5)" ) Assert( "4d CHECK: invalid insert (age=-5) rejected", ; IsErr( aR ) .OR. Val1( aR ) == 0 .OR. Val1( aR ) == NIL ) RECOVER s_nTotal += 3 ; s_nFail += 3 ; ? " FAIL: 4d CHECK constraint (exception)" END SEQUENCE /* 4e: UNIQUE constraint */ BEGIN SEQUENCE aR := five_SQL( "CREATE TABLE uqtbl (id INTEGER, email CHAR(40), UNIQUE(email))" ) hC := SqlLoadConstraints( "uqtbl" ) Assert( "4e UNIQUE constraint: metadata stored", ; Len( hC[ "unique" ] ) >= 1 ) five_SQL( "INSERT INTO uqtbl (id, email) VALUES (1, 'a@b.com')" ) USE uqtbl.dbf NEW SHARED lValid := SqlValidateUnique( "uqtbl", "email", "b@c.com", 0 ) Assert( "4e UNIQUE: new email allowed", lValid ) lValid := SqlValidateUnique( "uqtbl", "email", "a@b.com", 0 ) Assert( "4e UNIQUE: duplicate email rejected", ! lValid ) CLOSE uqtbl RECOVER s_nTotal += 3 ; s_nFail += 3 ; ? " FAIL: 4e UNIQUE constraint (exception)" END SEQUENCE /* 4f: FOREIGN KEY */ BEGIN SEQUENCE dbCloseAll() five_SQL( "CREATE TABLE fk_parent (id INTEGER, PRIMARY KEY(id))" ) five_SQL( "INSERT INTO fk_parent (id) VALUES (1)" ) five_SQL( "INSERT INTO fk_parent (id) VALUES (2)" ) aR := five_SQL( "CREATE TABLE fk_child (id INTEGER, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES fk_parent(id))" ) hC := SqlLoadConstraints( "fk_child" ) Assert( "4f FOREIGN KEY: metadata stored", ; Len( hC[ "fk" ] ) >= 1 ) dbCloseAll() /* Test FK via INSERT — SqlValidateFKRecord is called internally */ aR := Try( "INSERT INTO fk_child (id, parent_id) VALUES (10, 1)" ) Assert( "4f FK: valid reference (parent=1) passes", ! IsErr( aR ) ) aR := Try( "INSERT INTO fk_child (id, parent_id) VALUES (20, 999)" ) Assert( "4f FK: invalid reference (parent=999) fails", IsErr( aR ) ) RECOVER s_nTotal += 3 ; s_nFail += 3 ; ? " FAIL: 4f FOREIGN KEY (exception)" END SEQUENCE RETURN /* ====================================================================== */ /* Section 5: MERGE / UPSERT (3 tests) */ /* ====================================================================== */ STATIC PROCEDURE TestMerge() LOCAL aR /* Setup MERGE tables */ BEGIN SEQUENCE five_SQL( "CREATE TABLE target_tbl (id INTEGER, name CHAR(20), val INTEGER)" ) five_SQL( "INSERT INTO target_tbl (id, name, val) VALUES (1, 'Old', 100)" ) five_SQL( "CREATE TABLE source_tbl (id INTEGER, name CHAR(20), val INTEGER)" ) five_SQL( "INSERT INTO source_tbl (id, name, val) VALUES (1, 'New', 200)" ) RECOVER ? " (MERGE setup error, continuing)" END SEQUENCE /* 5a: MERGE update existing */ BEGIN SEQUENCE aR := five_SQL( ; "MERGE INTO target_tbl USING source_tbl ON target_tbl.id = source_tbl.id " + ; "WHEN MATCHED THEN UPDATE SET name = source_tbl.name, val = source_tbl.val " + ; "WHEN NOT MATCHED THEN INSERT (id, name, val) VALUES (source_tbl.id, source_tbl.name, source_tbl.val)" ) aR := five_SQL( "SELECT name, val FROM target_tbl WHERE id = 1" ) Assert( "5a MERGE update existing: id=1 name=New val=200", ; Upper( AllTrim( CellVal( aR, 1, 1 ) ) ) == "NEW" .AND. ; CellVal( aR, 1, 2 ) == 200 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 5a MERGE update existing (exception)" END SEQUENCE /* 5b: MERGE insert new */ BEGIN SEQUENCE dbCloseAll() five_SQL( "INSERT INTO source_tbl (id, name, val) VALUES (2, 'Brand', 300)" ) aR := five_SQL( ; "MERGE INTO target_tbl USING source_tbl ON target_tbl.id = source_tbl.id " + ; "WHEN MATCHED THEN UPDATE SET name = source_tbl.name, val = source_tbl.val " + ; "WHEN NOT MATCHED THEN INSERT (id, name, val) VALUES (source_tbl.id, source_tbl.name, source_tbl.val)" ) aR := five_SQL( "SELECT name FROM target_tbl WHERE id = 2" ) Assert( "5b MERGE insert new: id=2 added", ; Rows( aR ) == 1 .AND. ; Upper( AllTrim( CellVal( aR, 1, 1 ) ) ) == "BRAND" ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 5b MERGE insert new (exception)" END SEQUENCE /* 5c: MERGE mixed (update + insert) */ BEGIN SEQUENCE dbCloseAll() five_SQL( "INSERT INTO source_tbl (id, name, val) VALUES (3, 'Third', 400)" ) aR := five_SQL( ; "MERGE INTO target_tbl USING source_tbl ON target_tbl.id = source_tbl.id " + ; "WHEN MATCHED THEN UPDATE SET name = source_tbl.name, val = source_tbl.val " + ; "WHEN NOT MATCHED THEN INSERT (id, name, val) VALUES (source_tbl.id, source_tbl.name, source_tbl.val)" ) aR := five_SQL( "SELECT COUNT(*) AS cnt FROM target_tbl" ) Assert( "5c MERGE mixed: target has 3 rows total", ; CellVal( aR, 1, 1 ) == 3 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 5c MERGE mixed (exception)" END SEQUENCE RETURN /* ====================================================================== */ /* Section 6: Combined Advanced Queries (5 tests) */ /* ====================================================================== */ STATIC PROCEDURE TestCombined() LOCAL aR, oErr /* Ensure clean state: close all workareas and remove stale CTE temps */ dbCloseAll() FErase( "__cte_ranked.dbf" ) FErase( "__cte_order_totals.dbf" ) FErase( "__cte_dept_summary.dbf" ) FErase( "__cte_org.dbf" ) /* 6a: CTE + Window + JOIN: top earner per department */ BEGIN SEQUENCE aR := five_SQL( ; "WITH ranked AS (" + ; "SELECT e.name, e.dept, e.salary, " + ; "ROW_NUMBER() OVER (PARTITION BY e.dept ORDER BY e.salary DESC) AS rn " + ; "FROM employees e" + ; ") SELECT name, dept, salary FROM ranked WHERE rn = 1" ) Assert( "6a CTE+Window: top earner per dept", ; Rows( aR ) == 4 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 6a CTE+Window top earner (exception)" END SEQUENCE /* 6b: CTE + Aggregate + HAVING via WHERE */ /* Clean stale workareas from 6a to prevent alias collisions */ IF Select( "RANKED" ) > 0 dbSelectArea( "RANKED" ) ; dbCloseArea() ENDIF IF Select( "E" ) > 0 dbSelectArea( "E" ) ; dbCloseArea() ENDIF IF Select( "ORDER_TOTALS" ) > 0 dbSelectArea( "ORDER_TOTALS" ) ; dbCloseArea() ENDIF IF hb_FileExists( "__cte_ranked.dbf" ) FErase( "__cte_ranked.dbf" ) ENDIF BEGIN SEQUENCE aR := five_SQL( ; "WITH order_totals AS (" + ; "SELECT emp_id, SUM(amount) AS total FROM orders GROUP BY emp_id" + ; ") SELECT e.name, t.total FROM employees e " + ; "JOIN order_totals t ON e.id = t.emp_id " + ; "WHERE t.total > 1000 ORDER BY t.total DESC" ) ? " 6b debug: rows=" + hb_ntos( Rows( aR ) ) Assert( "6b CTE+Agg: emp order totals > 1000", ; Rows( aR ) >= 1 ) RECOVER USING oErr s_nTotal++ ; s_nFail++ ; ? " FAIL: 6b CTE+Agg: " + IIF( oErr != NIL, oErr:description, "no error obj" ) END SEQUENCE /* 6c: Window + Subquery: diff from avg for active employees */ BEGIN SEQUENCE aR := five_SQL( ; "SELECT name, salary, " + ; "salary - AVG(salary) OVER () AS diff_from_avg " + ; "FROM employees " + ; "WHERE id IN (SELECT emp_id FROM orders)" ) Assert( "6c Window+Subquery: diff from avg", ; Rows( aR ) >= 5 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 6c Window+Subquery (exception)" END SEQUENCE /* 6d: RECURSIVE CTE + JOIN: org hierarchy with levels */ BEGIN SEQUENCE aR := five_SQL( ; "WITH RECURSIVE org AS (" + ; "SELECT id, name, 1 AS lvl FROM employees WHERE mgr_id = 0 " + ; "UNION ALL " + ; "SELECT e.id, e.name, o.lvl + 1 FROM employees e JOIN org o ON e.mgr_id = o.id" + ; ") SELECT name, lvl FROM org ORDER BY lvl, name" ) Assert( "6d RECURSIVE CTE+JOIN: org levels", ; Rows( aR ) == 10 .AND. CellVal( aR, 1, 2 ) == 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 6d RECURSIVE CTE org levels (exception)" END SEQUENCE /* 6e: Window over GROUP BY CTE */ BEGIN SEQUENCE aR := five_SQL( ; "WITH dept_summary AS (" + ; "SELECT dept, COUNT(*) AS cnt, SUM(salary) AS total FROM employees GROUP BY dept" + ; ") SELECT dept, cnt, total, " + ; "RANK() OVER (ORDER BY total DESC) AS rank " + ; "FROM dept_summary" ) Assert( "6e CTE+Window: dept summary ranked by total", ; Rows( aR ) == 4 .AND. CellVal( aR, 1, 4 ) == 1 ) RECOVER s_nTotal++ ; s_nFail++ ; ? " FAIL: 6e CTE+Window dept summary (exception)" END SEQUENCE RETURN