/*
 * test_sql_standards.prg — SQL:2003-2023 Parser Feature Tests
 *
 * Parser-level tests: verifies that all new SQL standard constructs
 * parse without error and produce the correct AST node types.
 * No database setup required — tokenize + parse only.
 *
 * FiveSql — SQL Engine for Harbour DBF/NTX
 *
 * Copyright (c) 2025-2026 Charles KWON (Charles KWON OhJun)
 * Email: charleskwonohjun@gmail.com
 *
 * All rights reserved.
 */

#include "FiveSqlDef.ch"
#include "hbclass.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:2003-2023 Parser Feature Test Suite"
   ? "================================================================"
   ?

   ? "--- Section 1: InfixBP Operators (SQL:2003) ---"
   TestInfixOperators()

   ? ""
   ? "--- Section 2: Primary Expressions (SQL:1992-2023) ---"
   TestPrimaryExpressions()

   ? ""
   ? "--- Section 3: SELECT Clauses (SQL:2003-2008) ---"
   TestSelectClauses()

   ? ""
   ? "--- Section 4: Window Frame Specification (SQL:2003-2011) ---"
   TestWindowFrames()

   ? ""
   ? "--- Section 5: New Statement Types (SQL:2003-2008) ---"
   TestNewStatements()

   ? ""
   ? "--- Section 6: JSON Functions (SQL:2016) ---"
   TestJsonFunctions()

   ? ""
   ? "--- Section 7: XML Functions (SQL:2003) ---"
   TestXmlFunctions()

   ? ""
   ? "--- Section 8: SQL:2023 Functions ---"
   TestSql2023Functions()

   ? ""
   ? "================================================================"
   ? "  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 ) ) ) + "%"
   ? "================================================================"

   IF s_nFail > 0
      ? "  *** FAILURES DETECTED ***"
      ErrorLevel( 1 )
   ELSE
      ? "  All tests passed."
   ENDIF

RETURN


/* ---- Helpers ---- */

STATIC FUNCTION ParseSQL( cSQL )

   LOCAL oLexer, oParser, hResult

   oLexer := TSqlLexer():New( cSQL )
   oLexer:Tokenize()
   oParser := TSqlParser2():New( oLexer:GetTokens(), {} )

   BEGIN SEQUENCE
      hResult := oParser:Parse()
   RECOVER
      hResult := NIL
   END SEQUENCE

RETURN hResult


STATIC FUNCTION Assert( cLabel, lCond )

   s_nTotal++
   IF lCond
      s_nPass++
      ? "  PASS: " + cLabel
   ELSE
      s_nFail++
      ? "  FAIL: " + cLabel
   ENDIF

RETURN lCond


/* ---- Section 1: InfixBP Operators ---- */

STATIC PROCEDURE TestInfixOperators()

   LOCAL h

   /* 1.1 SIMILAR TO (SQL:2003) */
   h := ParseSQL( "SELECT * FROM t WHERE name SIMILAR TO '%pattern%'" )
   Assert( "1.1 SIMILAR TO", h != NIL .AND. h[ "type" ] == "SELECT" .AND. ;
      h[ "where" ] != NIL .AND. h[ "where" ][ 2 ] == "SIMILAR TO" )

   /* 1.2 SIMILAR TO with ESCAPE */
   h := ParseSQL( "SELECT * FROM t WHERE name SIMILAR TO '%x%' ESCAPE '\'" )
   Assert( "1.2 SIMILAR TO ESCAPE", h != NIL .AND. h[ "where" ] != NIL .AND. ;
      h[ "where" ][ 2 ] == "SIMILAR TO" .AND. h[ "where" ][ 5 ] != NIL )

   /* 1.3 NOT SIMILAR TO */
   h := ParseSQL( "SELECT * FROM t WHERE name NOT SIMILAR TO '%bad%'" )
   Assert( "1.3 NOT SIMILAR TO", h != NIL .AND. h[ "where" ] != NIL .AND. ;
      h[ "where" ][ 1 ] == ND_UNI .AND. h[ "where" ][ 2 ] == "NOT" )

   /* 1.4 IS DISTINCT FROM (SQL:2003) */
   h := ParseSQL( "SELECT * FROM t WHERE a IS DISTINCT FROM b" )
   Assert( "1.4 IS DISTINCT FROM", h != NIL .AND. h[ "where" ] != NIL .AND. ;
      h[ "where" ][ 2 ] == "IS DISTINCT FROM" )

   /* 1.5 IS NOT DISTINCT FROM (SQL:2003) */
   h := ParseSQL( "SELECT * FROM t WHERE a IS NOT DISTINCT FROM b" )
   Assert( "1.5 IS NOT DISTINCT FROM", h != NIL .AND. h[ "where" ] != NIL .AND. ;
      h[ "where" ][ 2 ] == "IS NOT DISTINCT FROM" )

RETURN


/* ---- Section 2: Primary Expressions ---- */

STATIC PROCEDURE TestPrimaryExpressions()

   LOCAL h, xCol

   /* 2.1 CAST(expr AS type) */
   h := ParseSQL( "SELECT CAST(salary AS VARCHAR(20)) FROM t" )
   Assert( "2.1 CAST", h != NIL .AND. h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "CAST" )

   /* 2.2 CAST with precision */
   h := ParseSQL( "SELECT CAST(price AS DECIMAL(10,2)) FROM t" )
   Assert( "2.2 CAST DECIMAL(10,2)", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "CAST" )

   /* 2.3 EXTRACT(part FROM expr) */
   h := ParseSQL( "SELECT EXTRACT(YEAR FROM hire_date) FROM t" )
   Assert( "2.3 EXTRACT", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "EXTRACT" )

   /* 2.4 TRIM with spec */
   h := ParseSQL( "SELECT TRIM(LEADING ' ' FROM name) FROM t" )
   Assert( "2.4 TRIM LEADING", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "TRIM" )

   /* 2.5 TRIM(TRAILING) */
   h := ParseSQL( "SELECT TRIM(TRAILING 'x' FROM name) FROM t" )
   Assert( "2.5 TRIM TRAILING", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "TRIM" )

   /* 2.6 TRIM(BOTH) */
   h := ParseSQL( "SELECT TRIM(BOTH FROM name) FROM t" )
   Assert( "2.6 TRIM BOTH", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "TRIM" )

   /* 2.7 POSITION(str IN str) */
   h := ParseSQL( "SELECT POSITION('x' IN name) FROM t" )
   Assert( "2.7 POSITION", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "POSITION" )

   /* 2.8 OVERLAY(str PLACING str FROM n FOR n) */
   h := ParseSQL( "SELECT OVERLAY(name PLACING 'X' FROM 1 FOR 3) FROM t" )
   Assert( "2.8 OVERLAY", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "OVERLAY" )

   /* 2.9 ARRAY constructor */
   h := ParseSQL( "SELECT ARRAY(1, 2, 3) FROM t" )
   Assert( "2.9 ARRAY", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "ARRAY" )

   /* 2.10 ROW value constructor */
   h := ParseSQL( "SELECT ROW(1, 'a', 3.14) FROM t" )
   Assert( "2.10 ROW", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "ROW" )

   /* 2.11 COALESCE (handled as normal function) */
   h := ParseSQL( "SELECT COALESCE(a, b, 0) FROM t" )
   Assert( "2.11 COALESCE", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "COALESCE" )

   /* 2.12 NULLIF (handled as normal function) */
   h := ParseSQL( "SELECT NULLIF(a, 0) FROM t" )
   Assert( "2.12 NULLIF", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "NULLIF" )

   /* 2.13 LISTAGG with WITHIN GROUP */
   h := ParseSQL( "SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) FROM t" )
   Assert( "2.13 LISTAGG", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "LISTAGG" )

RETURN


/* ---- Section 3: SELECT Clauses ---- */

STATIC PROCEDURE TestSelectClauses()

   LOCAL h

   /* 3.1 NULLS FIRST in ORDER BY */
   h := ParseSQL( "SELECT * FROM t ORDER BY name ASC NULLS FIRST" )
   Assert( "3.1 NULLS FIRST", h != NIL .AND. ;
      Len( h[ "order_by" ] ) > 0 .AND. Len( h[ "order_by" ][ 1 ] ) >= 3 .AND. ;
      h[ "order_by" ][ 1 ][ 3 ] == "FIRST" )

   /* 3.2 NULLS LAST in ORDER BY */
   h := ParseSQL( "SELECT * FROM t ORDER BY salary DESC NULLS LAST" )
   Assert( "3.2 NULLS LAST", h != NIL .AND. ;
      Len( h[ "order_by" ] ) > 0 .AND. Len( h[ "order_by" ][ 1 ] ) >= 3 .AND. ;
      h[ "order_by" ][ 1 ][ 3 ] == "LAST" )

   /* 3.3 FETCH FIRST n ROWS ONLY (SQL:2008) */
   h := ParseSQL( "SELECT * FROM t ORDER BY id FETCH FIRST 10 ROWS ONLY" )
   Assert( "3.3 FETCH FIRST ONLY", h != NIL .AND. ;
      hb_HHasKey( h, "fetch" ) .AND. h[ "fetch" ] == 10 .AND. ;
      h[ "fetch_ties" ] == "ONLY" )

   /* 3.4 FETCH FIRST WITH TIES (SQL:2008) */
   h := ParseSQL( "SELECT * FROM t ORDER BY id FETCH FIRST 5 ROWS WITH TIES" )
   Assert( "3.4 FETCH WITH TIES", h != NIL .AND. ;
      hb_HHasKey( h, "fetch" ) .AND. h[ "fetch" ] == 5 .AND. ;
      h[ "fetch_ties" ] == "WITH TIES" )

   /* 3.5 OFFSET n ROWS (SQL:2008) */
   h := ParseSQL( "SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY" )
   Assert( "3.5 OFFSET ROWS", h != NIL .AND. ;
      h[ "offset" ] == 20 .AND. h[ "fetch" ] == 10 )

   /* 3.6 GROUPING SETS (SQL:2003) */
   h := ParseSQL( "SELECT dept, yr, SUM(amt) FROM t GROUP BY GROUPING SETS ((dept), (yr), ())" )
   Assert( "3.6 GROUPING SETS", h != NIL .AND. ;
      Len( h[ "group_by" ] ) > 0 .AND. ;
      h[ "group_by" ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "group_by" ][ 1 ][ 2 ] == "GROUPING SETS" )

   /* 3.7 ROLLUP (SQL:2003) */
   h := ParseSQL( "SELECT dept, SUM(salary) FROM t GROUP BY ROLLUP(dept)" )
   Assert( "3.7 ROLLUP", h != NIL .AND. ;
      Len( h[ "group_by" ] ) > 0 .AND. ;
      h[ "group_by" ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "group_by" ][ 1 ][ 2 ] == "ROLLUP" )

   /* 3.8 CUBE (SQL:2003) */
   h := ParseSQL( "SELECT dept, yr, SUM(amt) FROM t GROUP BY CUBE(dept, yr)" )
   Assert( "3.8 CUBE", h != NIL .AND. ;
      Len( h[ "group_by" ] ) > 0 .AND. ;
      h[ "group_by" ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "group_by" ][ 1 ][ 2 ] == "CUBE" )

   /* 3.9 LATERAL subquery (SQL:2003) */
   h := ParseSQL( "SELECT * FROM t, LATERAL (SELECT * FROM s WHERE s.id = t.id) AS lat" )
   Assert( "3.9 LATERAL subquery", h != NIL .AND. ;
      Len( h[ "tables" ] ) >= 2 .AND. ;
      h[ "tables" ][ 2 ][ 1 ] == "__LATERAL__" )

   /* 3.10 FOR UPDATE (SQL:2003) */
   h := ParseSQL( "SELECT * FROM t WHERE id = 1 FOR UPDATE" )
   Assert( "3.10 FOR UPDATE", h != NIL .AND. ;
      hb_HHasKey( h, "for_lock" ) .AND. h[ "for_lock" ] == "UPDATE" )

   /* 3.11 FOR SHARE (SQL:2003) */
   h := ParseSQL( "SELECT * FROM t FOR SHARE" )
   Assert( "3.11 FOR SHARE", h != NIL .AND. ;
      hb_HHasKey( h, "for_lock" ) .AND. h[ "for_lock" ] == "SHARE" )

   /* 3.12 FOR UPDATE OF col (SQL:2003) */
   h := ParseSQL( "SELECT * FROM t FOR UPDATE OF name, salary" )
   Assert( "3.12 FOR UPDATE OF", h != NIL .AND. ;
      hb_HHasKey( h, "for_lock_cols" ) .AND. ;
      Len( h[ "for_lock_cols" ] ) == 2 )

   /* 3.13 WINDOW clause (SQL:2003) */
   h := ParseSQL( "SELECT name, SUM(salary) OVER w FROM t WINDOW w AS (ORDER BY id)" )
   Assert( "3.13 WINDOW clause", h != NIL .AND. ;
      hb_HHasKey( h, "window_defs" ) .AND. ;
      Len( h[ "window_defs" ] ) == 1 .AND. ;
      h[ "window_defs" ][ 1 ][ 1 ] == "W" )

RETURN


/* ---- Section 4: Window Frame Specification ---- */

STATIC PROCEDURE TestWindowFrames()

   LOCAL h, xCol

   /* 4.1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW */
   h := ParseSQL( "SELECT SUM(x) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t" )
   Assert( "4.1 ROWS frame", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.2 RANGE BETWEEN n PRECEDING AND n FOLLOWING */
   h := ParseSQL( "SELECT AVG(x) OVER (ORDER BY id RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM t" )
   Assert( "4.2 RANGE frame", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.3 GROUPS frame (SQL:2011) */
   h := ParseSQL( "SELECT SUM(x) OVER (ORDER BY id GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t" )
   Assert( "4.3 GROUPS frame", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.4 Frame with EXCLUDE (SQL:2011) */
   h := ParseSQL( "SELECT SUM(x) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) FROM t" )
   Assert( "4.4 EXCLUDE CURRENT ROW", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.5 EXCLUDE NO OTHERS */
   h := ParseSQL( "SELECT SUM(x) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) FROM t" )
   Assert( "4.5 EXCLUDE NO OTHERS", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.6 EXCLUDE GROUP */
   h := ParseSQL( "SELECT SUM(x) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) FROM t" )
   Assert( "4.6 EXCLUDE GROUP", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.7 EXCLUDE TIES */
   h := ParseSQL( "SELECT SUM(x) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE TIES) FROM t" )
   Assert( "4.7 EXCLUDE TIES", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.8 PARTITION BY + ORDER BY + frame */
   h := ParseSQL( "SELECT SUM(x) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t" )
   Assert( "4.8 Partition + Order + Frame", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

   /* 4.9 NULLS FIRST in window ORDER BY */
   h := ParseSQL( "SELECT ROW_NUMBER() OVER (ORDER BY name ASC NULLS FIRST) FROM t" )
   Assert( "4.9 Window NULLS FIRST", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_WINDOW )

RETURN


/* ---- Section 5: New Statement Types ---- */

STATIC PROCEDURE TestNewStatements()

   LOCAL h

   /* 5.1 VALUES standalone (SQL:2003) */
   h := ParseSQL( "VALUES (1, 'a'), (2, 'b'), (3, 'c')" )
   Assert( "5.1 VALUES rows", h != NIL .AND. h[ "type" ] == "VALUES" .AND. ;
      Len( h[ "rows" ] ) == 3 )

   /* 5.2 TABLE shorthand (SQL:2003) */
   h := ParseSQL( "TABLE employees" )
   Assert( "5.2 TABLE shorthand", h != NIL .AND. h[ "type" ] == "TABLE" .AND. ;
      h[ "table" ] == "EMPLOYEES" )

   /* 5.3 CALL procedure (SQL:2003) */
   h := ParseSQL( "CALL my_proc(1, 'hello')" )
   Assert( "5.3 CALL procedure", h != NIL .AND. h[ "type" ] == "CALL" .AND. ;
      h[ "procedure" ] == "MY_PROC" .AND. Len( h[ "args" ] ) == 2 )

   /* 5.4 CALL with no args */
   h := ParseSQL( "CALL refresh_cache()" )
   Assert( "5.4 CALL no args", h != NIL .AND. h[ "type" ] == "CALL" .AND. ;
      h[ "procedure" ] == "REFRESH_CACHE" .AND. Len( h[ "args" ] ) == 0 )

   /* 5.5 MERGE with AND condition (SQL:2008 extended) */
   h := ParseSQL( "MERGE INTO tgt USING src ON tgt.id = src.id " + ;
      "WHEN MATCHED AND src.active = 1 THEN UPDATE SET tgt.name = src.name " + ;
      "WHEN NOT MATCHED AND src.active = 1 THEN INSERT (id, name) VALUES (src.id, src.name)" )
   Assert( "5.5 MERGE AND condition", h != NIL .AND. h[ "type" ] == "MERGE" .AND. ;
      h[ "has_matched" ] .AND. h[ "has_not_matched" ] .AND. ;
      h[ "match_condition" ] != NIL )

   /* 5.6 MERGE WHEN MATCHED THEN DELETE (SQL:2008) */
   h := ParseSQL( "MERGE INTO tgt USING src ON tgt.id = src.id " + ;
      "WHEN MATCHED AND src.deleted = 1 THEN DELETE" )
   Assert( "5.6 MERGE DELETE", h != NIL .AND. h[ "type" ] == "MERGE" .AND. ;
      h[ "matched_delete" ] )

RETURN


/* ---- Section 6: JSON Functions (SQL:2016) ---- */

STATIC PROCEDURE TestJsonFunctions()

   LOCAL h

   /* 6.1 JSON_VALUE */
   h := ParseSQL( "SELECT JSON_VALUE(data, '$.name') FROM t" )
   Assert( "6.1 JSON_VALUE", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "JSON_VALUE" )

   /* 6.2 JSON_QUERY */
   h := ParseSQL( "SELECT JSON_QUERY(data, '$.items') FROM t" )
   Assert( "6.2 JSON_QUERY", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "JSON_QUERY" )

   /* 6.3 JSON_EXISTS */
   h := ParseSQL( "SELECT * FROM t WHERE JSON_EXISTS(data, '$.name')" )
   Assert( "6.3 JSON_EXISTS", h != NIL .AND. h[ "where" ] != NIL .AND. ;
      h[ "where" ][ 1 ] == ND_FN .AND. h[ "where" ][ 2 ] == "JSON_EXISTS" )

   /* 6.4 JSON_OBJECT */
   h := ParseSQL( "SELECT JSON_OBJECT('name' VALUE name, 'id' VALUE id) FROM t" )
   Assert( "6.4 JSON_OBJECT", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "JSON_OBJECT" )

   /* 6.5 JSON_ARRAY (handled as normal function) */
   h := ParseSQL( "SELECT JSON_ARRAY(1, 2, 3) FROM t" )
   Assert( "6.5 JSON_ARRAY", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "JSON_ARRAY" )

   /* 6.6 JSON_ARRAYAGG (handled as normal function) */
   h := ParseSQL( "SELECT JSON_ARRAYAGG(name) FROM t" )
   Assert( "6.6 JSON_ARRAYAGG", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "JSON_ARRAYAGG" )

   /* 6.7 JSON_OBJECTAGG */
   h := ParseSQL( "SELECT JSON_OBJECTAGG(name VALUE salary) FROM t" )
   Assert( "6.7 JSON_OBJECTAGG", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "JSON_OBJECTAGG" )

   /* 6.8 JSON_TABLE in expression context */
   h := ParseSQL( "SELECT JSON_TABLE(data, '$.items' COLUMNS (name VARCHAR(50) PATH '$.name')) FROM t" )
   Assert( "6.8 JSON_TABLE", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "JSON_TABLE" )

RETURN


/* ---- Section 7: XML Functions (SQL:2003) ---- */

STATIC PROCEDURE TestXmlFunctions()

   LOCAL h

   /* 7.1 XMLELEMENT */
   h := ParseSQL( "SELECT XMLELEMENT(NAME employee, name) FROM t" )
   Assert( "7.1 XMLELEMENT", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "XMLELEMENT" )

   /* 7.2 XMLFOREST */
   h := ParseSQL( "SELECT XMLFOREST(name AS empname, salary AS sal) FROM t" )
   Assert( "7.2 XMLFOREST", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "XMLFOREST" )

   /* 7.3 XMLAGG */
   h := ParseSQL( "SELECT XMLAGG(XMLELEMENT(NAME item, name) ORDER BY name) FROM t" )
   Assert( "7.3 XMLAGG", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "XMLAGG" )

RETURN


/* ---- Section 8: SQL:2023 Functions ---- */

STATIC PROCEDURE TestSql2023Functions()

   LOCAL h

   /* 8.1 ANY_VALUE */
   h := ParseSQL( "SELECT dept, ANY_VALUE(name) FROM t GROUP BY dept" )
   Assert( "8.1 ANY_VALUE", h != NIL .AND. ;
      h[ "columns" ][ 2 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 2 ][ 1 ][ 2 ] == "ANY_VALUE" )

   /* 8.2 GREATEST */
   h := ParseSQL( "SELECT GREATEST(a, b, c) FROM t" )
   Assert( "8.2 GREATEST", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "GREATEST" )

   /* 8.3 LEAST */
   h := ParseSQL( "SELECT LEAST(a, b, c) FROM t" )
   Assert( "8.3 LEAST", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "LEAST" )

   /* 8.4 LPAD */
   h := ParseSQL( "SELECT LPAD(name, 20, ' ') FROM t" )
   Assert( "8.4 LPAD", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "LPAD" )

   /* 8.5 RPAD */
   h := ParseSQL( "SELECT RPAD(name, 20, '.') FROM t" )
   Assert( "8.5 RPAD", h != NIL .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 1 ][ 1 ][ 2 ] == "RPAD" )

   /* 8.6 BOOL_AND */
   h := ParseSQL( "SELECT dept, BOOL_AND(active) FROM t GROUP BY dept" )
   Assert( "8.6 BOOL_AND", h != NIL .AND. ;
      h[ "columns" ][ 2 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 2 ][ 1 ][ 2 ] == "BOOL_AND" )

   /* 8.7 BOOL_OR */
   h := ParseSQL( "SELECT dept, BOOL_OR(active) FROM t GROUP BY dept" )
   Assert( "8.7 BOOL_OR", h != NIL .AND. ;
      h[ "columns" ][ 2 ][ 1 ][ 1 ] == ND_FN .AND. ;
      h[ "columns" ][ 2 ][ 1 ][ 2 ] == "BOOL_OR" )

RETURN