Consistent SQL Generation

Example 17. SQL Generation for Equivalent Queries

XQuery (all of these queries are equivalent)

collection("USERS")/USERS/FIRSTNAME[.='John']


collection("USERS")//FIRSTNAME[.='John']


collection("USERS")/USERS[FIRSTNAME='John']/FIRSTNAME


for $u in collection("USERS")/USERS
where $u/FIRSTNAME = 'John'
return $u/FIRSTNAME


declare function local:testUser($firstname) {$firstname = 'John'};
for $u in collection("USERS")/USERS
where local:testUser ($u/FIRSTNAME)
return $u/FIRSTNAME


for $u in collection("USERS")/USERS
return if ($u/FIRSTNAME = 'John') then $u/FIRSTNAME else ()

Generated SQL (generated for all the above queries)

SELECT ALL
  nrm6."FIRSTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm6
WHERE
  nrm6."FIRSTNAME" = 'John' AND
  LENGTH(nrm6."FIRSTNAME") = LENGTH('John')