sql.lisp

(defpackage :endb-test/sql
  (:use :cl :fiveam :endb/sql)
  (:import-from :endb/arrow)
  (:import-from :endb/lib)
  (:import-from :endb/sql/db)
  (:import-from :endb/sql/expr)
  (:import-from :endb/storage)
  (:import-from :alexandria)
  (:import-from :cl-ppcre)
  (:import-from :fset)
  (:import-from :asdf)
  (:import-from :uiop))
(in-package :endb-test/sql)

(in-suite* :sql)

(test create-table-and-insert
  (let ((endb/sql/expr:*sqlite-mode* t)
        (db (begin-write-tx (make-db))))
    (multiple-value-bind (result result-code)
        (execute-sql db "CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER)")
      (is (null result))
      (is (eq t result-code))
      (is (equal '("a" "b" "c" "d" "e")
                 (endb/sql/db:table-columns db "t1")))
      (is (zerop (endb/sql/db:base-table-size db "t1"))))

    (multiple-value-bind (result result-code)
        (execute-sql db "INSERT INTO t1 VALUES(103,102,100,101,104)")
      (is (null result))
      (is (= 1 result-code))
      (is (equalp '(#(103 102 100 101 104))
                  (endb/sql/db:base-table-visible-rows db "t1")))
      (is (= 1 (endb/sql/db:base-table-size db "t1"))))

    (multiple-value-bind (result result-code)
        (execute-sql db "INSERT INTO t1(e,c,b,d,a) VALUES(103,102,100,101,104), (NULL,102,NULL,101,104)")
      (is (null result))
      (is (= 2 result-code))
      (is (equalp '(#(103 102 100 101 104)
                    #(104 100 102 101 103)
                    #(104 :null 102 101 :null))
                  (endb/sql/db:base-table-visible-rows db "t1")))
      (is (= 3 (endb/sql/db:base-table-size db "t1"))))

    (multiple-value-bind (result result-code)
        (execute-sql db "CREATE INDEX t1i0 ON t1(a1,b1,c1,d1,e1,x1)")
      (is (null result))
      (is (eq t result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql db "CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER)")
      (is (null result))
      (is (null result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql db "DROP TABLE t1")
      (is (null result))
      (is (eq t result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql db "DROP TABLE t1")
      (is (null result))
      (is (null result-code)))))

(test isolation
  (let* ((endb/sql/expr:*sqlite-mode* t)
         (db (make-db))
         (write-db (begin-write-tx db)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "CREATE TABLE t1(a INTEGER)")
      (is (null result))
      (is (eq t result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t1 VALUES(103)")
      (is (null result))
      (is (= 1 result-code)))

    (is (equalp '(#(103)) (endb/sql/db:base-table-visible-rows write-db "t1")))
    (is (= 1 (endb/sql/db:base-table-size write-db "t1")))

    (is (null (endb/sql/db:base-table-visible-rows db "t1")))
    (is (zerop (endb/sql/db:base-table-size db "t1")))

    (setf db (commit-write-tx db write-db))

    (is (equalp '(#(103)) (endb/sql/db:base-table-visible-rows db "t1")))
    (is (= 1 (endb/sql/db:base-table-size db "t1")))))

(defmacro signals-with-msg (e msg &body body)
  (alexandria:with-gensyms (e-sym)
    `(signals ,e (handler-case
                     ,@body
                   (,e (,e-sym)
                     (if (ppcre:scan ,msg (format nil "~A" ,e-sym))
                         (error ,e-sym)
                         (is (ppcre:scan ,msg (format nil "~A" ,e-sym)))))))))

(test explicit-transactions
  (let* ((endb/sql/expr:*sqlite-mode* t)
         (db (make-db))
         (write-db (begin-write-tx db)))

    (signals endb/sql/db:sql-begin-error (execute-sql write-db "BEGIN TRANSACTION"))

    (signals endb/sql/db:sql-commit-error (execute-sql write-db "COMMIT")))

  (let* ((endb/sql/expr:*sqlite-mode* t)
         (db (make-db))
         (write-db (begin-write-tx db)))

    (signals endb/sql/db:sql-rollback-error (execute-sql write-db "ROLLBACK TRANSACTION"))))

(test empty-db
  (let* ((db (make-db))
         (write-db (begin-write-tx db)))

    (signals-with-msg
        endb/sql/expr:sql-runtime-error
        "Unknown table"
      (execute-sql db "SELECT * FROM t1"))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t1 {a: 103, b: 104} ON CONFLICT (b) DO NOTHING")
      (is (null result))
      (is (= 1 result-code)))

    (signals-with-msg
        endb/sql/expr:sql-runtime-error
        "Unknown table"
      (execute-sql db "SELECT * FROM t1"))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t1")
      (is (equalp '(#(103 104)) result))
      (is (equal '("a" "b") columns)))

    (setf db (commit-write-tx db write-db))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM t1")
      (is (equalp '(#(103 104)) result))
      (is (equal '("a" "b") columns)))))

(test no-ddl
  (let* ((db (make-db))
         (write-db (begin-write-tx db)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t1(a, b) VALUES(103, 104)")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t1(b, c) VALUES(105, FALSE)")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "UPDATE t1 SET a = 101 WHERE a = 103")
      (is (null result))
      (is (= 1 result-code)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Column names are required for values"
      (execute-sql write-db "INSERT INTO t1 VALUES(105, FALSE)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot insert into table: t1 without all values containing same number of columns: 1"
      (execute-sql write-db "INSERT INTO t1(a) VALUES(105, FALSE)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All VALUES must have the same number of columns: 2"
      (execute-sql write-db "INSERT INTO t1(a, b) VALUES(105, FALSE), (106)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown table"
      (execute-sql write-db "DELETE FROM t2 WHERE a = 103"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown table"
      (execute-sql write-db "UPDATE t2 SET a = 101 WHERE a = 103"))

    (setf db (commit-write-tx db write-db))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown table"
      (execute-sql db "SELECT * FROM t2"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown column"
      (execute-sql db "SELECT d FROM t1"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown column"
      (execute-sql db "SELECT t1.d FROM t1"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown built-in function: foo"
      (execute-sql db "SELECT foo(1) FROM t1"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot use table function outside FROM: unnest"
      (execute-sql db "SELECT unnest(1) FROM t1"))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM t1 ORDER BY b")
      (is (equalp '(#(101 104 :null) #(:null 105 nil)) result))
      (is (equal '("a" "b" "c") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT b, b FROM t1 ORDER BY b")
      (is (equalp '(#(104 104) #(105 105)) result))
      (is (equal '("b" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (VALUES (1, 2)) AS foo(a, b)")
      (is (equalp '(#(1 2)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT a FROM (VALUES (1, 2)) AS foo(a, b)")
      (is (equalp '(#(1)) result))
      (is (equal '("a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT foo.b FROM (VALUES (1, 2)) AS foo(a, b)")
      (is (equalp '(#(2)) result))
      (is (equal '("b") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Number of column names does not match projection"
      (execute-sql db "SELECT * FROM (VALUES (1, 2)) AS foo(a)"))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2 {a: 2, b: 3}, {a: 3, c: 4}")
      (is (null result))
      (is (= 2 result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2 OBJECTS {a: 1, b: 2, c: 1 + 1}")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY a")
      (is (equalp '(#(1 2 2) #(2 3 :null) #(3 :null 4)) result))
      (is (equal '("a" "b" "c") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "UPDATE t2 SET d = 4 WHERE c = 2")
      (is (null result))
      (is (= 1 result-code)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Update requires at least one set or unset column or patch"
      (execute-sql write-db "UPDATE t2"))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY a")
      (is (equalp '(#(1 2 2 4) #(2 3 :null :null) #(3 :null 4 :null)) result))
      (is (equal '("a" "b" "c" "d") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2(a, b, c, d) VALUES(1, 2, 5, 6), (4, 5, 7, 8) ON CONFLICT (a, b) DO NOTHING")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2(a, b, c, d) VALUES(1, 2, 5, 6), (4, 5, 7, 8) ON CONFLICT (a, b) DO NOTHING")
      (is (null result))
      (is (zerop result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY a")
      (is (equalp '(#(1 2 2 4) #(2 3 :null :null) #(3 :null 4 :null) #(4 5 7 8)) result))
      (is (equal '("a" "b" "c" "d") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2 {a: 1, b: 2, c: 4, d: 6} ON CONFLICT (a, b) DO UPDATE SET c = excluded.c, d = t2.d")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2 {a: 1, b: 2, c: 4, d: 6} ON CONFLICT (a, b) DO UPDATE SET c = excluded.c WHERE t2.d = 6")
      (is (null result))
      (is (zerop result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY a")
      (is (equalp '(#(1 2 4 4) #(2 3 :null :null) #(3 :null 4 :null) #(4 5 7 8)) result))
      (is (equal '("a" "b" "c" "d") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2 {c: 4, d: 6} ON CONFLICT (c) DO UPDATE SET d = excluded.d WHERE b IS NULL")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY a")
      (is (equalp '(#(1 2 4 4) #(2 3 :null :null) #(3 :null 4 6) #(4 5 7 8)) result))
      (is (equal '("a" "b" "c" "d") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All inserted values needs to provide the on conflict columns"
      (execute-sql write-db "INSERT INTO t2 {c: 4, e: 5}, {c: 4, d: 6} ON CONFLICT (c, e) DO NOTHING"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All inserted values needs to provide the on conflict columns"
      (execute-sql write-db "INSERT INTO t2 {} ON CONFLICT (a) DO NOTHING"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Column names needs to contain the on conflict columns"
      (execute-sql write-db "INSERT INTO t2(a) VALUES (1) ON CONFLICT (b) DO NOTHING"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All OBJECTS must have literal keys"
      (execute-sql write-db "INSERT INTO t2 ? ON CONFLICT (a) DO NOTHING" (fset:seq (fset:map ("b" 1)))))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "UPDATE t2 SET c = 5 UNSET $.a WHERE b = 3")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY c")
      (is (equalp '(#(3 :null 4 6) #(1 2 4 4) #(:null 3 5 :null) #(4 5 7 8)) result))
      (is (equal '("a" "b" "c" "d") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Inserted values cannot contain duplicated on conflict columns"
      (execute-sql write-db "INSERT INTO t2 {c: 4, e: 5}, {c: 4, e: 5} ON CONFLICT (c, e) DO UPDATE SET f = 1"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot update the on conflict columns"
      (execute-sql write-db "INSERT INTO t2 {c: 4, e: 5} ON CONFLICT (c, e) DO UPDATE SET c = 1"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot update the on conflict columns"
      (execute-sql write-db "INSERT INTO t2 {c: 4, e: 5} ON CONFLICT (c, e) DO UPDATE REMOVE c"))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2 {c: 4, e: 5} ON CONFLICT (c, e) DO UPDATE SET f = 1")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY c")
      (is (equalp '(#(:null :null 4 :null 5) #(3 :null 4 6 :null) #(1 2 4 4 :null)
                    #(:null 3 5 :null :null) #(4 5 7 8 :null))
                  result))
      (is (equal '("a" "b" "c" "d" "e") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t2 {a: 4, e: 5} ON CONFLICT (e) DO UPDATE SET $.a = excluded.a")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t2 ORDER BY c")
      (is (equalp '(#(4 :null 4 :null 5) #(3 :null 4 6 :null) #(1 2 4 4 :null)
                    #(:null 3 5 :null :null) #(4 5 7 8 :null))
                  result))
      (is (equal '("a" "b" "c" "d" "e") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t3 SELECT a, b, c FROM t2 WHERE c > 4 ON CONFLICT (b) DO NOTHING")
      (is (null result))
      (is (= 2 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t3 ORDER BY c")
      (is (equalp '(#(:null 3 5) #(4 5 7)) result))
      (is (equal '("a" "b" "c") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "UPDATE t3 PATCH { a: 2 + t3.b, b: NULL } WHERE b = 3")
      (is (null result))
      (is (= 1 result-code)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot insert empty object"
      (execute-sql write-db "UPDATE t3 { a: NULL, b: NULL, c: NULL } WHERE a = 5"))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t3 ORDER BY c")
      (is (equalp '(#(5 :null 5) #(4 5 7)) result))
      (is (equal '("a" "b" "c") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "UPDATE t3 SET $.d.c[0] = 1 WHERE b = 5")
      (is (null result))
      (is (= 1 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM t3 ORDER BY c")
      (is (equalp `(#(5 :null 5 :null) #(4 5 7 ,(fset:map ("c" (fset:seq 1))))) result))
      (is (equal '("a" "b" "c" "d") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot insert empty object"
      (execute-sql write-db "INSERT INTO users {}"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot insert into table: users invalid column name: !foo"
      (execute-sql write-db "INSERT INTO users {'!foo': 2}"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All inserted values needs to provide the on conflict columns"
      (execute-sql write-db "INSERT INTO foo { ...:a } ON CONFLICT (name) DO NOTHING" (fset:map ("a" (fset:map ("b" 1))))))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All inserted values needs to provide the on conflict columns"
      (execute-sql write-db "INSERT INTO foo { [1 + 1]: 2 } ON CONFLICT (name) DO NOTHING"))

    (let ((write-db (begin-write-tx db)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO t4 {addresses: [{city: 'bar'}], name: 'foo'}")
        (is (null result))
        (is (= 1 result-code)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "UPDATE t4 UNSET $.addresses[0].city WHERE name = 'foo'")
        (is (null result))
        (is (= 1 result-code)))

      (setf db (commit-write-tx db write-db))

      (multiple-value-bind (result columns)
          (execute-sql db "SELECT * FROM t4")
        (is (equalp `(#(,(fset:seq (fset:empty-map)) "foo")) result))
        (is (equal '("addresses" "name") columns)))

      (let ((write-db (begin-write-tx db)))

        (multiple-value-bind (result result-code)
            (execute-sql write-db "UPDATE t4 UNSET $.addresses[0] WHERE name = 'foo'")
          (is (null result))
          (is (= 1 result-code)))

        (multiple-value-bind (result columns)
            (execute-sql write-db "SELECT * FROM t4")
          (is (equalp `(#(,(fset:empty-seq) "foo")) result))
          (is (equal '("addresses" "name") columns)))

        (setf db (commit-write-tx db write-db))))))

(test constraints
  (let* ((db (make-db))
         (endb/lib:*log-level* (endb/lib:resolve-log-level :off)))

    (let ((write-db (begin-write-tx db)))

      (signals-with-msg endb/sql/expr:sql-runtime-error
          "Assertions do not support parameters"
        (execute-sql write-db "CREATE ASSERTION foo CHECK (1 = ?)"))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "CREATE ASSERTION unique_email CHECK (1 >= (SELECT MAX(c.cnt) FROM (SELECT COUNT(*) AS cnt FROM users GROUP BY email) AS c))")
        (is (null result))
        (is (eq t result-code)))

      (setf db (commit-write-tx db write-db)))

    (let ((write-db (begin-write-tx db)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO users {email: 'joe@example.com'}, {email: 'bob@example.com'}")
        (is (null result))
        (is (= 2 result-code)))

      (setf db (commit-write-tx db write-db)))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO users {email: 'rob@example.com'}")
        (is (null result))
        (is (= 1 result-code)))

      (setf db (commit-write-tx db write-db)))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO users {email: 'joe@example.com'}")
        (is (null result))
        (is (= 1 result-code)))

      (signals-with-msg endb/sql/expr:sql-runtime-error
          "Constraint failed: unique_email"
        (commit-write-tx db write-db)))

    (let ((write-db (begin-write-tx db)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "CREATE ASSERTION string_email CHECK (NOT EXISTS (SELECT * FROM users WHERE TYPEOF(email) != 'text'))")
        (is (null result))
        (is (eq t result-code)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO users {email: x'fab'}")
        (is (null result))
        (is (= 1 result-code)))

      (signals-with-msg endb/sql/expr:sql-runtime-error
          "Constraint failed: string_email"
        (commit-write-tx db write-db)))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "CREATE ASSERTION invalid_assertion CHECK (SQRT(-1))")
        (is (null result))
        (is (eq t result-code)))

      (commit-write-tx db write-db))))

(test multiple-statments
  (let* ((db (make-db)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 AS a; SELECT 2 AS b;")
      (is (equalp '(#(2)) result))
      (is (equal '("b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2 AS b;")
      (is (equalp '(#(2)) result))
      (is (equal '("b") columns)))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result columns)
          (execute-sql write-db "INSERT INTO foo(a) VALUES (?); SELECT ? + foo.a AS b FROM foo;" (fset:seq 1 2))
        (is (equalp '(#(3)) result))
        (is (equal '("b") columns))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result columns)
          (execute-sql write-db "INSERT INTO t1(a, b) VALUES(103, 104); INSERT INTO t1(b, c) VALUES(105, FALSE); SELECT * FROM t1 ORDER BY b;")
        (is (equalp '(#(103 104 :null) #(:null 105 nil)) result))
        (is (equal '("a" "b" "c") columns))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "CREATE ASSERTION assertion_one CHECK ('foo;bar' < 2); CREATE VIEW view_two AS SELECT \"b;a\\\"z\" > 4;")
        (is (null result))
        (is (eq t result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM information_schema.check_constraints")
        (is (equalp '(#(:null "main" "assertion_one" "('foo;bar' < 2)"))
                    result))
        (is (equal '("constraint_catalog" "constraint_schema" "constraint_name" "check_clause")
                   columns)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM information_schema.views")
        (is (equalp '(#(:null "main" "view_two" "SELECT \"b;a\\\"z\" > 4"))
                    result))
        (is (equal '("table_catalog" "table_schema" "table_name" "view_definition")
                   columns))))))

(test with
  (let* ((db (make-db)))
    (multiple-value-bind (result columns)
        (execute-sql db "WITH foo(c) AS (SELECT 1), bar(a, b) AS (SELECT 2, 3) SELECT * FROM foo, bar")
      (is (equalp '(#(1 2 3)) result))
      (is (equal '("c" "a" "b") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Number of column names does not match projection"
      (execute-sql db "WITH bar(a, b) AS (SELECT 2) SELECT * FROM bar"))

    (multiple-value-bind (result columns)
        (execute-sql db "WITH foo AS (SELECT 1) SELECT * FROM foo")
      (is (equalp '(#(1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100) SELECT sum(n) FROM t")
      (is (equalp '(#(5050)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<5) SELECT x FROM cnt ORDER BY x")
      (is (equalp '(#(1) #(2) #(3) #(4) #(5)) result))
      (is (equal '("x") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt AS c1 WHERE (SELECT c1.x FROM cnt AS c2)<5) SELECT x FROM cnt ORDER BY x")
      (is (equalp '(#(1) #(2) #(3) #(4) #(5)) result))
      (is (equal '("x") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Recursion not supported without UNION / UNION ALL"
      (execute-sql db "WITH RECURSIVE cnt(x) AS (SELECT x+1 FROM cnt WHERE x<5) SELECT x FROM cnt ORDER BY x"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Left recursion not supported"
      (execute-sql db "WITH RECURSIVE cnt(x) AS (SELECT x+1 FROM cnt UNION ALL VALUES(1)) SELECT x FROM cnt"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Non-linear recursion not supported"
      (execute-sql db "WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt AS c1, cnt AS c2 WHERE c1.x<5) SELECT x FROM cnt"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot use aggregate functions in recursion: COUNT"
      (execute-sql db "WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT COUNT(x) FROM cnt WHERE x<5) SELECT x FROM cnt"))

    (let* ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO org(name, boss) VALUES('Alice',NULL), ('Bob','Alice'), ('Cindy','Alice'), ('Dave','Bob'), ('Emma','Bob'), ('Fred','Cindy'), ('Gail','Cindy')")
        (is (null result))
        (is (= 7 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "WITH RECURSIVE
  under_alice(name,level) AS (
    VALUES('Alice',0)
    UNION ALL
    SELECT org.name, under_alice.level+1
      FROM org JOIN under_alice ON org.boss = under_alice.name
  )
SELECT substr('..........',1,level*3) || name FROM under_alice ORDER BY under_alice.level, under_alice.name")
        (is (equalp '(#("Alice")
                      #("...Bob")
                      #("...Cindy")
                      #("......Dave")
                      #("......Emma")
                      #("......Fred")
                      #("......Gail"))
                    result))
        (is (equal '("column1") columns))))

    (multiple-value-bind (result columns)
        (execute-sql db
                     "WITH RECURSIVE
      xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
      yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
      m(iter, cx, cy, x, y) AS (
        SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
        UNION ALL
        SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
         WHERE (x*x + y*y) < 4.0 AND iter<28 ORDER BY cx, cy
      ),
      m2(iter, cx, cy) AS (
        SELECT max(iter), cx, cy FROM m GROUP BY cx, cy ORDER BY cx, cy
      ),
      a(t) AS (
        SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
        FROM m2 GROUP BY cy ORDER BY cy DESC
      )
    SELECT group_concat(rtrim(t),x'0a') FROM a")
      (is (equalp '(#(
                      "                                    +.
                                    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####....   +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####....   +
                                 ..+####+.
                                   ..#*..
                                    ....#"))
                  result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "WITH RECURSIVE
  input(sud) AS (
    VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
  ),
  digits(z, lp) AS (
    VALUES('1', 1)
    UNION ALL SELECT
    CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
  ),
  x(s, ind) AS (
    SELECT sud, instr(sud, '.') FROM input
    UNION ALL
    SELECT
      substr(s, 1, ind-1) || z || substr(s, ind+1),
      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
     FROM x, digits AS z
    WHERE ind>0
      AND NOT EXISTS (
            SELECT 1
              FROM digits AS lp
             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
                OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
                OR z.z = substr(s, (((ind-1)/3) % 3) * 3
                        + ((ind-1)/27) * 27 + lp
                        + ((lp-1) / 3) * 6, 1)
         )
  )
SELECT s FROM x WHERE ind=0")
      (is (equalp '(#("534678912672195348198342567859761423426853791713924856961537284287419635345286179")) result))
      (is (equal '("s") columns)))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "WITH foo(a) AS (VALUES (1), (2)) INSERT INTO bar (a) SELECT * FROM foo")
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM bar")
        (is (equalp '(#(1) #(2)) result))
        (is (equal '("a") columns)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "WITH foo(a) AS (VALUES (1)) UPDATE bar SET a = 3 WHERE bar.a IN (SELECT * FROM foo)")
        (is (null result))
        (is (= 1 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM bar")
        (is (equalp '(#(3) #(2)) result))
        (is (equal '("a") columns)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "WITH foo(a) AS (VALUES (2)) DELETE FROM bar WHERE bar.a IN (SELECT * FROM foo)")
        (is (null result))
        (is (= 1 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM bar")
        (is (equalp '(#(3)) result))
        (is (equal '("a") columns))))))

(test parameters
  (let* ((db (make-db)))
    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ? + ?" (fset:seq 1 3))
      (is (equalp '(#(4)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [?, ?]" (fset:seq 1 3))
      (is (equalp `(#(,(fset:seq 1 3))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT :x + :y" (fset:map ("x" 1) ("y" 3)))
      (is (equalp '(#(4)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT :x" (fset:map ("x" 1)))
      (is (equalp '(#(1)) result))
      (is (equal '("x") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Required parameters: \\(0\\) does not match given: \\(\\)"
      (execute-sql db "SELECT ?"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Parameters must be an array or an object"
      (execute-sql db "SELECT ?" 1))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Parameters must be an array or an object"
      (execute-sql db "SELECT ?" nil))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ? + ?" (fset:seq (fset:seq 1 3) (fset:seq 2 4)) t)
      (is (equalp '(#(6)) result))
      (is (equal '("column1") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Many parameters must be an array"
      (execute-sql db "SELECT ?" (fset:map ("x" 1) ("y" 3)) t))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo(x, y) VALUES (?, ?)" (fset:seq (fset:seq 1 3) (fset:seq 2 4)) t)
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM foo ORDER BY x")
        (is (equalp '(#(1 3) #(2 4)) result))
        (is (equal '("x" "y") columns))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo(x, y) VALUES (:x, :y)" (fset:seq (fset:map ("x" 1) ("y" 3)) (fset:map ("x" 2) ("y" 4))) t)
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM foo ORDER BY x")
        (is (equalp '(#(1 3) #(2 4)) result))
        (is (equal '("x" "y") columns)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo(x, y) VALUES (:x, :y)" (fset:seq) t)
        (is (null result))
        (is (null result-code))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo {x: ?, y: ?};"  (fset:seq (fset:seq 1 3) (fset:seq 2 4)) t)
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM foo ORDER BY x")
        (is (equalp '(#(1 3) #(2 4)) result))
        (is (equal '("x" "y") columns))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo { ...? }"  (fset:seq (fset:seq (fset:map ("x" 1) ("y" 3)))
                                                                      (fset:seq (fset:map ("x" 2) ("y" 4))))
                       t)
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM foo ORDER BY x")
        (is (equalp '(#(1 3) #(2 4)) result))
        (is (equal '("x" "y") columns))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo OBJECTS ?" (fset:seq (fset:seq (fset:map ("x" 1) ("y" 3)))
                                                                      (fset:seq (fset:map ("x" 2) ("y" 4))))
                       t)
        (is (null result))
        (is (= 2 result-code)))


      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM foo ORDER BY x")
        (is (equalp '(#(1 3) #(2 4)) result))
        (is (equal '("x" "y") columns))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo ?" (fset:seq (fset:seq (fset:map ("x" 1) ("y" 3)))
                                                              (fset:seq (fset:map ("x" 2) ("y" 4))))
                       t)
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM foo ORDER BY x")
        (is (equalp '(#(1 3) #(2 4)) result))
        (is (equal '("x" "y") columns))))

    (let ((write-db (begin-write-tx db)))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO foo { :x, :y }"  (fset:seq (fset:map ("x" 1) ("y" 3))
                                                                        (fset:map ("x" 2) ("y" 4)))
                       t)
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM foo ORDER BY x")
        (is (equalp '(#(1 3) #(2 4)) result))
        (is (equal '("x" "y") columns))))))

(test information-schema
  (let* ((db (make-db))
         (write-db (begin-write-tx db)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.tables")
      (is (null result))
      (is (equal '("table_catalog" "table_schema" "table_name" "table_type")
                 columns)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.columns")
      (is (null result))
      (is (equal '("table_catalog" "table_schema" "table_name" "column_name" "ordinal_position")
                 columns)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.views")
      (is (null result))
      (is (equal '("table_catalog" "table_schema" "table_name" "view_definition")
                 columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "INSERT INTO t1(a, b) VALUES(103, 104); INSERT INTO t1(b, c) VALUES(105, FALSE);")
      (is (null result))
      (is (equal 1 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.tables")
      (is (equalp '(#(:null "main" "t1" "BASE TABLE")) result))
      (is (equal '("table_catalog" "table_schema" "table_name" "table_type")
                 columns)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.columns ORDER BY column_name")
      (is (equalp '(#(:null "main" "t1" "a" 0)
                    #(:null "main" "t1" "b" 0)
                    #(:null "main" "t1" "c" 0))
                  result))
      (is (equal '("table_catalog" "table_schema" "table_name" "column_name" "ordinal_position")
                 columns)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT tables.table_name, table_type FROM information_schema.tables")
      (is (equalp '(#("t1" "BASE TABLE")) result))
      (is (equal '("table_name" "table_type") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "CREATE VIEW foo(a, b) AS SELECT 1, 2")
      (is (null result))
      (is (eq t result-code)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Number of column names does not match projection"
      (execute-sql write-db "CREATE VIEW bar(a, b) AS SELECT 1"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Views do not support parameters"
      (execute-sql write-db "CREATE VIEW bar(a) AS SELECT ?"))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.tables WHERE table_type = 'VIEW'")
      (is (equalp '(#(:null "main" "foo" "VIEW")) result))
      (is (equal '("table_catalog" "table_schema" "table_name" "table_type")
                 columns)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.views")
      (is (equalp '(#(:null "main" "foo" "SELECT 1, 2")) result))
      (is (equal '("table_catalog" "table_schema" "table_name" "view_definition")
                 columns)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.columns WHERE table_name = 'foo' ORDER BY ordinal_position")
      (is (equalp '(#(:null "main" "foo" "a" 1)
                    #(:null "main" "foo" "b" 2))
                  result))
      (is (equal '("table_catalog" "table_schema" "table_name" "column_name" "ordinal_position")
                 columns)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM foo")
      (is (equalp '(#(1 2)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "DROP VIEW foo")
      (is (null result))
      (is (eq t result-code)))

    (is (null (execute-sql write-db "SELECT * FROM information_schema.tables WHERE table_type = 'VIEW'")))
    (is (null (execute-sql write-db "SELECT * FROM information_schema.views")))
    (is (null (execute-sql write-db "SELECT * FROM information_schema.columns WHERE table_name = 'foo' ORDER BY ordinal_position")))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "CREATE ASSERTION bar CHECK (1 = 1)")
      (is (null result))
      (is (eq t result-code)))

    (multiple-value-bind (result columns)
        (execute-sql write-db "SELECT * FROM information_schema.check_constraints")
      (is (equalp '(#(:null "main" "bar" "(1 = 1)")) result))
      (is (equal '("constraint_catalog" "constraint_schema" "constraint_name" "check_clause")
                 columns)))

    (multiple-value-bind (result result-code)
        (execute-sql write-db "DROP ASSERTION bar")
      (is (null result))
      (is (eq t result-code)))

    (is (null (execute-sql write-db "SELECT * FROM information_schema.check_constraints")))))

(test temporal-scalars
  (let* ((db (make-db)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-01-01")
      (is (equalp (list (vector (endb/arrow:parse-arrow-date-millis "2001-01-01"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT DATE '2001-01-01'")
      (is (equalp (list (vector (endb/arrow:parse-arrow-date-millis "2001-01-01"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 12:01:20")
      (is (equalp (list (vector (endb/arrow:parse-arrow-time-micros "12:01:20"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT TIME '12:01:20'")
      (is (equalp (list (vector (endb/arrow:parse-arrow-time-micros "12:01:20"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2023-05-16T14:43:39.970062Z")
      (is (equalp (list (vector (endb/arrow:parse-arrow-timestamp-micros "2023-05-16T14:43:39.970062Z"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT TIMESTAMP '2023-05-16 14:43:39.970062'")
      (is (equalp (list (vector (endb/arrow:parse-arrow-timestamp-micros "2023-05-16T14:43:39.970062Z"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-01-01 = 2001-01-01T00:00:00")
      (is (equalp (list (vector t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2002-01-01 > 2001-01-01T00:00:00")
      (is (equalp (list (vector t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CAST(2023-05-16T14:43:39.970062 AS TIME)")
      (is (equalp (list (vector (endb/arrow:parse-arrow-time-micros "14:43:39.970062Z"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CAST(2023-05-16T14:43:39.970062 AS DATE)")
      (is (equalp (list (vector (endb/arrow:parse-arrow-date-millis "2023-05-16"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT P3Y6M")
      (is (equalp (list (vector (endb/arrow:parse-arrow-interval-month-day-nanos "P3Y6M"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT PT12H30M5S")
      (is (equalp (list (vector (endb/arrow:parse-arrow-interval-month-day-nanos "PT12H30M5S"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT INTERVAL '14:43:39.970062' HOUR TO SECOND")
      (is (equalp (list (vector (endb/arrow:parse-arrow-interval-month-day-nanos "PT14H43M39.970062S"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-01-01T00:00:00 + P1Y1DT1H")
      (is (equalp (list (vector (endb/arrow:parse-arrow-timestamp-micros "2002-01-02T01:00:00"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-01-01 - P1D")
      (is (equalp (list (vector (endb/arrow:parse-arrow-date-millis "2000-12-31"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-01-01 + PT1H")
      (is (equalp (list (vector (endb/arrow:parse-arrow-timestamp-micros "2001-01-01T01:00:00"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT PT1H + 10:00:00")
      (is (equalp (list (vector (endb/arrow:parse-arrow-time-micros "11:00:00"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT P1D + P1D")
      (is (equalp (list (vector (endb/arrow:parse-arrow-interval-month-day-nanos "P2D"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT P2M1D - P1M")
      (is (equalp (list (vector (endb/arrow:parse-arrow-interval-month-day-nanos "P1M1D"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-01-02 - 2001-01-01")
      (is (equalp (list (vector (endb/arrow:parse-arrow-interval-month-day-nanos "PT24H"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 09:00:00 - 10:30:00")
      (is (equalp (list (vector (endb/arrow:parse-arrow-interval-month-day-nanos "PT1H30M"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {start: 2001-01-01, end: 2001-03-01} OVERLAPS {start: 2001-02-01, end: 2001-04-01}")
      (is (equalp (list (vector t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {start: 2001-01-01, end: 2001-03-01} CONTAINS {start: 2001-02-01, end: 2001-04-01}")
      (is (equalp (list (vector nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {start: 2001-01-01, end: 2001-04-01} CONTAINS {start: 2001-02-01, end: 2001-04-01}")
      (is (equalp (list (vector t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-04-01 IMMEDIATELY PRECEDES [2001-04-01T00:00:00Z, 2001-05-01]")
      (is (equalp (list (vector t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2001-05-01 IMMEDIATELY SUCCEEDS PERIOD(2001-04-01T00:00:00Z, 2001-05-01)")
      (is (equalp (list (vector t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(YEAR FROM 2001-01-01)")
      (is (equalp (list (vector 2001)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(MONTH FROM 2001-01-01)")
      (is (equalp (list (vector 1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(DAY FROM 2001-01-01)")
      (is (equalp (list (vector 1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(YEAR FROM 2023-05-16T14:43:39.970062Z)")
      (is (equalp (list (vector 2023)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(MONTH FROM 2023-05-16T14:43:39.970062Z)")
      (is (equalp (list (vector 5)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(DAY FROM 2023-05-16T14:43:39.970062Z)")
      (is (equalp (list (vector 16)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(HOUR FROM 2023-05-16T14:43:39.970062Z)")
      (is (equalp (list (vector 14)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(MINUTE FROM 2023-05-16T14:43:39.970062Z)")
      (is (equalp (list (vector 43)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(SECOND FROM 2023-05-16T14:43:39.970062Z)")
      (is (equalp (list (vector 39.970062d0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(HOUR FROM 14:43:39.970062)")
      (is (equalp (list (vector 14)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(MINUTE FROM 14:43:39.970062)")
      (is (equalp (list (vector 43)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(SECOND FROM 14:43:39.970062)")
      (is (equalp (list (vector 39.970062d0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(YEAR FROM P3Y6M)")
      (is (equalp (list (vector 3)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(MONTH FROM P3Y6M)")
      (is (equalp (list (vector 6)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(DAY FROM P2DT14H43M39.970062S)")
      (is (equalp (list (vector 2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(HOUR FROM P2DT14H43M39.970062S)")
      (is (equalp (list (vector 14)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(MINUTE FROM P2DT14H43M39.970062S)")
      (is (equalp (list (vector 43)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(SECOND FROM P2DT14H43M39.970062S)")
      (is (equalp (list (vector 39.970062d0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT EXTRACT(SECOND FROM NULL)")
      (is (equalp (list (vector :null)) result))
      (is (equal '("column1") columns)))))

(test temporal-current-literals
  (let* ((db (make-db))
         (now (endb/arrow:parse-arrow-timestamp-micros "2023-05-16T14:43:39.970062Z")))

    (setf (endb/sql/db:db-current-timestamp db) now)

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CURRENT_TIMESTAMP")
      (is (equalp (list (vector now)) result))
      (is (equal '("CURRENT_TIMESTAMP") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CURRENT_TIME")
      (is (equalp (list (vector (endb/arrow:parse-arrow-time-micros "14:43:39.970062"))) result))
      (is (equal '("CURRENT_TIME") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CURRENT_DATE")
      (is (equalp (list (vector (endb/arrow:parse-arrow-date-millis "2023-05-16"))) result))
      (is (equal '("CURRENT_DATE") columns)))))

(test system-time
  (let* ((db (make-db))
         (system-time-as-of-empty (endb/sql/db:syn-current_timestamp db)))

    (sleep #+sbcl 0.01d0 #-sbcl 1.0d0)

    (let* ((write-db (begin-write-tx db))
           (system-time-as-of-insert (endb/sql/db:syn-current_timestamp write-db)))

      (is (not (equalp system-time-as-of-empty system-time-as-of-insert)))

      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO t1(a, b) VALUES(103, 104)")
        (is (null result))
        (is (= 1 result-code)))

      (setf db (commit-write-tx db write-db))

      (is (equalp '(#(103 104)) (execute-sql db "SELECT * FROM t1")))

      (is (null (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME AS OF ?" (fset:seq system-time-as-of-empty))))

      (sleep #+sbcl 0.01d0 #-sbcl 1.0d0)

      (let* ((write-db (begin-write-tx db))
             (system-time-as-of-update (endb/sql/db:syn-current_timestamp write-db)))
        (is (not (equalp system-time-as-of-insert system-time-as-of-update)))

        (multiple-value-bind (result result-code)
            (execute-sql write-db "UPDATE t1 SET a = 101 WHERE a = 103")
          (is (null result))
          (is (= 1 result-code)))

        (is (equalp '(#(101 104)) (execute-sql write-db "SELECT * FROM t1")))

        (setf db (commit-write-tx db write-db))

        (is (equalp '(#(101 104)) (execute-sql db "SELECT * FROM t1")))
        (is (equalp '(#(101 104)) (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME AS OF ?" (fset:seq system-time-as-of-update))))
        (is (equalp '(#(103 104)) (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME AS OF ?" (fset:seq system-time-as-of-insert))))
        (is (null (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME AS OF ?" (fset:seq system-time-as-of-empty))))

        (is (equalp '(#(101 104) #(103 104)) (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME BETWEEN ? AND ?" (fset:seq system-time-as-of-insert system-time-as-of-update))))
        (is (equalp '(#(103 104)) (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME BETWEEN ? AND ?" (fset:seq system-time-as-of-empty system-time-as-of-insert))))
        (is (null (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME BETWEEN ? AND ?" (fset:seq system-time-as-of-empty system-time-as-of-empty))))

        (is (equalp '(#(103 104)) (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME FROM ? TO ?" (fset:seq system-time-as-of-insert system-time-as-of-update))))
        (is (null (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME FROM ? TO ?" (fset:seq system-time-as-of-empty system-time-as-of-insert))))

        (multiple-value-bind (result columns)
            (execute-sql db "SELECT t1.*, t1.system_time FROM t1 FOR SYSTEM_TIME BETWEEN ? AND ?" (fset:seq system-time-as-of-insert system-time-as-of-update))
          (is (equalp (list (vector 101 104 (fset:map ("start" system-time-as-of-update) ("end" endb/sql/expr:+end-of-time+)))
                            (vector 103 104 (fset:map ("start" system-time-as-of-insert) ("end" system-time-as-of-update))))
                      result))
          (is (equal '("a" "b" "system_time") columns)))

        (multiple-value-bind (result columns)
            (execute-sql db "SELECT t1.*, t1.system_time FROM t1 FOR SYSTEM_TIME ALL")
          (is (equalp (list (vector 101 104 (fset:map ("start" system-time-as-of-update) ("end" endb/sql/expr:+end-of-time+)))
                            (vector 103 104 (fset:map ("start" system-time-as-of-insert) ("end" system-time-as-of-update))))
                      result))
          (is (equal '("a" "b" "system_time") columns)))

        (multiple-value-bind (result result-code)
            (execute-sql write-db "ERASE FROM t1 WHERE a = 101")
          (is (null result))
          (is (= 1 result-code)))

        (setf db (commit-write-tx db write-db))

        (multiple-value-bind (result columns)
            (execute-sql db "SELECT t1.*, t1.system_time FROM t1 FOR SYSTEM_TIME ALL")
          (is (equalp (list (vector 103 104 (fset:map ("start" system-time-as-of-insert) ("end" system-time-as-of-update))))
                      result))
          (is (equal '("a" "b" "system_time") columns)))

        (is (null (endb/sql/db:base-table-visible-rows db "t1")))

        (signals-with-msg endb/sql/expr:sql-runtime-error
            "Cannot insert value into SYSTEM_TIME column"
          (execute-sql write-db "INSERT INTO t1(a, b, system_time) VALUES(103, 104, {start: 2001-01-01, end: 2002-01-01})"))

        (signals-with-msg endb/sql/expr:sql-runtime-error
            "SYSTEM_TIME start must be a date or timestamp"
          (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME AS OF ?" (fset:seq "foo")))

        (signals-with-msg endb/sql/expr:sql-runtime-error
            "SYSTEM_TIME end must be a date or timestamp"
          (execute-sql db "SELECT * FROM t1 FOR SYSTEM_TIME BETWEEN ? AND ?" (fset:seq system-time-as-of-insert 2)))))))

(test semi-structured
  (let* ((db (make-db)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT []")
      (is (equalp `(#(,(fset:seq))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY []")
      (is (equalp `(#(,(fset:seq))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2]")
      (is (equalp `(#(,(fset:seq 1 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY [1, 2]")
      (is (equalp `(#(,(fset:seq 1 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY(1, 2)")
      (is (equalp `(#(,(fset:seq 1 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY (VALUES (1), (2))")
      (is (equalp `(#(,(fset:seq 1 2))) result))
      (is (equal '("column1") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "ARRAY query must return single column"
      (execute-sql db "SELECT ARRAY (VALUES (1, 2), (2, 3))"))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY_AGG(x.column1) FROM (VALUES (1), (2)) AS x")
      (is (equalp `(#(,(fset:seq 1 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY_AGG(x.column1 ORDER BY x.column1 DESC) FROM (VALUES (1), (2)) AS x")
      (is (equalp `(#(,(fset:seq 2 1))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY_AGG(x.column1 ORDER BY x.column2 ASC) FROM (VALUES (3, 2), (4, 1)) AS x")
      (is (equalp `(#(,(fset:seq 4 3))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ARRAY_AGG(x.column1) FROM (VALUES (1)) AS x WHERE x.column1 = 2")
      (is (equalp `(#(,(fset:seq))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_AGG(x.column1, x.column2) FROM (VALUES ('foo', 1), ('bar', 2)) AS x")
      (is (equalp `(#(,(fset:map ("bar" 2) ("foo" 1)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_AGG(x.column1, x.column2) FROM (VALUES ('foo', 1), ('baz', 1), ('bar', 2)) AS x GROUP BY x.column2 ORDER BY x.column2")
      (is (equalp `(#(,(fset:map ("baz" 1) ("foo" 1)))
                     #(,(fset:map ("bar" 2))))
                  result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_AGG(x.column1, x.column2) FROM (VALUES ('foo', 1), ('foo', 2)) AS x")
      (is (equalp `(#(,(fset:map ("foo" 2)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_AGG(x.column1, x.column2) FROM (VALUES ('foo', 1)) AS x WHERE x.column1 = 'bar'")
      (is (equalp `(#(,(fset:empty-map))) result))
      (is (equal '("column1") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid number of arguments: 1 to: OBJECT_AGG min: 2 max: 2"
      (execute-sql db "SELECT OBJECT_AGG(x.column1) FROM (VALUES ('foo', 1), ('bar', 2)) AS x"))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM UNNEST([1, 2, 3]) AS foo(bar)")
      (is (equalp '(#(1) #(2) #(3)) result))
      (is (equal '("bar") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown table function"
      (execute-sql db "SELECT * FROM FOO([1, 2, 3]) AS foo(bar)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Number of column names does not match projection"
      (execute-sql db "SELECT * FROM UNNEST([1, 2, 3]) AS foo(bar, baz)"))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM UNNEST({a: 1, b: 2, c: 3}) AS foo(bar)")
      (is (equalp `(#(,(fset:seq "a" 1)) #(,(fset:seq "b" 2)) #(,(fset:seq "c" 3))) result))
      (is (equal '("bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_KEYS({a: 1, b: 2, c: 3})")
      (is (equalp `(#(,(fset:seq "a" "b" "c"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_VALUES({a: 1, b: 2, c: 3})")
      (is (equalp `(#(,(fset:seq 1 2 3))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_ENTRIES({a: 1, b: 2, c: 3})")
      (is (equalp `(#(,(fset:seq (fset:seq "a" 1) (fset:seq "b" 2) (fset:seq "c" 3)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT_FROM_ENTRIES([['a', 1], ['b', 2], ['c', 3]])")
      (is (equalp `(#(,(fset:map ("a" 1) ("b" 2) ("c" 3)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x.column1 AS foo, y.column1 AS bar FROM (VALUES ('foo', [1, 2, 3]), ('bar', [5, 6]), ('baz', 2), ('boz', [])) AS x, UNNEST(x.column2) AS y ORDER BY foo")
      (is (equalp '(#("bar" 5) #("bar" 6) #("foo" 1) #("foo" 2) #("foo" 3))
                  result))
      (is (equal '("foo" "bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT y.* FROM (VALUES (['a', 'b', 'c'])) AS x(foo), UNNEST(x.foo) WITH ORDINALITY AS y(foo, bar)")
      (is (equalp '(#("a" 0) #("b" 1) #("c" 2))
                  result))
      (is (equal '("foo" "bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT y.* FROM (VALUES (['a', 'b', 'c'], ['d', 'e'])) AS x(foo, baz), UNNEST(x.foo, x.baz) WITH ORDINALITY AS y(foo, baz, pos)")
      (is (equalp '(#("a" "d" 0) #("b" "e" 1) #("c" :null 2))
                  result))
      (is (equal '("foo" "baz" "pos") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {}")
      (is (equalp `(#(,(fset:empty-map))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT()")
      (is (equalp `(#(,(fset:empty-map))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {foo: 2, bar: 'baz'}")
      (is (equalp `(#(,(fset:map ("foo" 2) ("bar" "baz")))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {\"foo\": 2, 'bar': 'baz'}")
      (is (equalp `(#(,(fset:map ("foo" 2) ("bar" "baz")))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OBJECT(foo: 2, bar: 'baz')")
      (is (equalp `(#(,(fset:map ("foo" 2) ("bar" "baz")))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {address: {street: 'Street', number: 42}, friends: [1, 2]}")
      (is (equalp `(#(,(fset:map ("address" (fset:map ("street" "Street") ("number" 42)))
                        ("friends" (fset:seq 1 2)))))
                  result))
      (is (equal '("column1") columns)))

    (let ((write-db (begin-write-tx (make-db))))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO users(user) VALUES ({address: {street: 'Street', number: 42}, friends: [1, 2]}), ({address: {street: 'Street', number: 43}, friends: [3, 1]})")
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT * FROM users")
        (is (equalp `(#(,(fset:map ("address" (fset:map ("street" "Street") ("number" 43)))
                          ("friends" (fset:seq 3 1))))
                       #(,(fset:map ("address" (fset:map ("street" "Street") ("number" 42)))
                           ("friends" (fset:seq 1 2)))))
                    result))
        (is (equal '("user") columns))))

    (let ((write-db (begin-write-tx (make-db))))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO users2 {address: {street: 'Street', number: 42}, friends: [1, 2]}, {boss: TRUE, address: {street: 'Street', number: 43}, friends: [3, 1]}")
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT users2.* FROM users2")
        (is (equalp `(#(,(fset:map ("street" "Street") ("number" 43))
                        t
                        ,(fset:seq 3 1))
                       #(,(fset:map ("street" "Street") ("number" 42))
                         :null
                         ,(fset:seq 1 2)))
                    result))
        (is (equal '("address" "boss" "friends") columns)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT { users2.* } FROM users2")
        (is (equalp `(#(,(fset:map ("address" (fset:map ("street" "Street") ("number" 43)))
                          ("boss" t)
                          ("friends" (fset:seq 3 1))))
                       #(,(fset:map ("address" (fset:map ("street" "Street") ("number" 42)))
                           ("friends" (fset:seq 1 2)))))
                    result))
        (is (equal '("users2") columns))))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT { foo: 2, bar, [2 + 2]: 5, ...baz } FROM (VALUES (1, [6, 7]), (2, {boz: 7, foo: 1})) AS foo(bar, baz)")
      (is (equalp `(#(,(fset:map ("bar" 2) ("4" 5) ("boz" 7) ("foo" 1)))
                     #(,(fset:map ("foo" 2) ("bar" 1) ("4" 5) ("0" 6) ("1" 7))))
                  result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT { foo.* } FROM (VALUES (1, [6, 7]), (2, {boz: 7, foo: 1})) AS foo(bar, baz) ORDER BY bar")
      (is (equalp `(#(,(fset:map ("bar" 1) ("baz" (fset:seq 6 7))))
                     #(,(fset:map ("bar" 2) ("baz" (fset:map ("boz" 7) ("foo" 1))))))
                  result))
      (is (equal '("foo") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, ...[3, 4], 5]")
      (is (equalp `(#(,(fset:seq 1 2 3 4 5))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, ...4, 5]")
      (is (equalp `(#(,(fset:seq 1 2 5))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, ...\"foo\", 5]")
      (is (equalp `(#(,(fset:seq 1 2 "f" "o" "o" 5))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT { a: 1, ...4 }")
      (is (equalp `(#(,(fset:map ("a" 1)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT { a: 1, ...{} }")
      (is (equalp `(#(,(fset:map ("a" 1)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT { a: 1, ...[2, 3], ...\"f\" }")
      (is (equalp `(#(,(fset:map ("a" 1) ("1" 3) ("0" "f")))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 'foo' MATCH 'foo'")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, 3] MATCH [1, 3]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, 3] MATCH [3, 1]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, 3] MATCH [1, 2, 2]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {user: 'foo', age: 42} MATCH {age: 42}")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {user: {name: ['foo', 'bar']}, age: 42} MATCH {user: {name: 'bar'}}")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, [1, 3]] MATCH [1, 3]")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, [1, 3]] MATCH [[1, 3]]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {foo: {bar: 'baz'}} @> {bar: 'baz'}")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {foo: {bar: 'baz'}} MATCH {foo: {}}")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT  {foo: {}} <@ {foo: {bar: 'baz'}}")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ['foo', 'bar'] MATCH 'bar'")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: [1, 2, {c: 3, x: 4}], c: 'b'} MATCH {a: [{c: 3}]}")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: [1, 2, {c: 3, x: 4}], c: 'b'} MATCH {a: [{x: 4}]}")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: [1, 2, {c: 3, x: 4}], c: 'b'} MATCH {a: [{x: 4}, 3]}")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: [1, 2, {c: 3, x: 4}], c: 'b'} MATCH {a: [{x: 4}, 1]}")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "OBJECTS {a: 1}, {b: 2}")
      (is (equalp '(#(1 :null) #(:null 2)) result))
      (is (equal '("a" "b") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All OBJECTS must have literal keys"
      (execute-sql db "OBJECTS {a: 1}, {[2 + 2]: 2}"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All OBJECTS must have literal keys"
      (execute-sql db "OBJECTS ?" (fset:seq (fset:map ("a" 1)))))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All OBJECTS must have literal keys"
      (execute-sql db "OBJECTS ?" (fset:seq 1)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All OBJECTS must have literal keys"
      (execute-sql db "?" (fset:seq (fset:map ("a" 1)))))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All OBJECTS must have literal keys"
      (execute-sql db "SELECT * FROM (?) AS foo" (fset:seq (fset:map ("a" 1)))))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x.* FROM (OBJECTS {a: 1}, {a: 3,  b: 2}) AS x ORDER BY a")
      (is (equalp `(#(1 :null) #(3 2)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT { x.* } FROM (OBJECTS {a: 1}, {a: 3,  b: 2}) AS x ORDER BY x.a")
      (is (equalp `(#(,(fset:map ("a" 1))) #(,(fset:map ("a" 3) ("b" 2)))) result))
      (is (equal '("x") columns)))))

(test semi-structured-access
  (let* ((db (make-db)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [][0]")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2][0]")
      (is (equalp '(#(1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2][2 - 1]")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2][-1]")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [NULL, 2][0]")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2][-3]")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 5[0]")
      (is (equalp '(#(5)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT NULL[1]")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 5[1]")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 3.14.foo")
      (is (equalp '(#(:null)) result))
      (is (equal '("foo") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT NULL.foo")
      (is (equalp '(#(:null)) result))
      (is (equal '("foo") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {foo: 5}[1]")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {}.bar")
      (is (equalp '(#(:null)) result))
      (is (equal '("bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {bar: NULL}.bar")
      (is (equalp '(#(:null)) result))
      (is (equal '("bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {foo: 2, bar: ['baz']}.bar[0]")
      (is (equalp '(#("baz")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {foo: 2, bar: ['baz']}['bar']")
      (is (equalp `(#(,(fset:seq "baz"))) result))
      (is (equal '("bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x.foo[0] FROM (VALUES ([{foo: [2]}, {foo: [3]}]), ([{foo: [4]}, {foo: [5]}])) AS x(x)")
      (is (equalp `(#(,(fset:seq 4 5)) #(,(fset:seq 2 3))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x.`foo bar` FROM (OBJECTS {'foo bar': 2}) AS x")
      (is (equalp `(#(2)) result))
      (is (equal '("foo bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x['foo bar'] FROM (OBJECTS {'foo bar': 2}) AS x")
      (is (equalp `(#(2)) result))
      (is (equal '("foo bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x FROM (OBJECTS {'foo bar': 2}) AS x")
      (is (equalp `(#(,(fset:map ("foo bar" 2)))) result))
      (is (equal '("x") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x FROM (OBJECTS {x: 2}) AS y")
      (is (equalp `(#(2)) result))
      (is (equal '("x") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x.x FROM (OBJECTS {x: 2}) AS x")
      (is (equalp `(#(2)) result))
      (is (equal '("x") columns)))

    (let ((write-db (begin-write-tx (make-db))))
      (multiple-value-bind (result result-code)
          (execute-sql write-db "INSERT INTO users(user) VALUES ({address: {street: 'Street', number: 42}, friends: [1, 2]}), ({address: {street: 'Street', number: 43}, friends: [3, 1]})")
        (is (null result))
        (is (= 2 result-code)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT users.user.address FROM users")
        (is (equalp `(#(,(fset:map ("street" "Street") ("number" 43)))
                       #(,(fset:map ("street" "Street") ("number" 42))))
                    result))
        (is (equal '("address") columns)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT user.address FROM users")
        (is (equalp `(#(,(fset:map ("street" "Street") ("number" 43)))
                       #(,(fset:map ("street" "Street") ("number" 42))))
                    result))
        (is (equal '("address") columns)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT users.user.address.street FROM users")
        (is (equalp '(#("Street") #("Street")) result))
        (is (equal '("street") columns)))

      (multiple-value-bind (result columns)
          (execute-sql write-db "SELECT user.address.street FROM users")
        (is (equalp '(#("Street") #("Street")) result))
        (is (equal '("street") columns))))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: 2, b: 3}[*]")
      (is (equalp `(#(,(fset:seq 2 3))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: 2, b: 3}..c")
      (is (equalp `(#(,(fset:seq))) result))
      (is (equal '("c") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: 2, b: {a: NULL}, c: [{a: 3}, 2]}..a")
      (is (equalp `(#(,(fset:seq 2 :null 3))) result))
      (is (equal '("a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {a: {b: 2}, b: {a: 3}, c: [{a: {b: 1}}, {b: 2}]}..a.b")
      (is (equalp `(#(,(fset:seq 2 1))) result))
      (is (equal '("b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [{a: NULL}, {a: 3}, {b: 4}].a")
      (is (equalp `(#(,(fset:seq :null 3))) result))
      (is (equal '("a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [{a: 2}, {a: 3}, {b: 4}, 5][*]")
      (is (equalp `(#(,(fset:seq (fset:map ("a" 2)) (fset:map ("a" 3)) (fset:map ("b" 4)) 5))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [{a: 2, b: {c: [1, 2]}}, {b: 4}, 5]..[*]")
      (is (equalp `(#(,(fset:seq (fset:map ("a" 2) ("b" (fset:map ("c" (fset:seq 1 2)))))
                        (fset:map ("b" 4)) 5 2 (fset:map ("c" (fset:seq 1 2))) (fset:seq 1 2) 1 2 4)))
                  result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [{a: 2, b: {c: [1, 2]}}, {b: 4}, 5]..[-1]")
      (is (equalp `(#(,(fset:seq 5 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [{a: 2, b: {c: [1, 2]}}, {b: 4}, 5]..b")
      (is (equalp `(#(,(fset:seq (fset:map ("c" (fset:seq 1 2))) 4))) result))
      (is (equal '("b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2, 3, 4][*]")
      (is (equalp `(#(,(fset:seq 1 2 3 4))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1[*]")
      (is (equalp `(#(,(fset:seq))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT {}[*]")
      (is (equalp `(#(,(fset:seq))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ''..a")
      (is (equalp `(#(,(fset:seq))) result))
      (is (equal '("a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove([0,1,2,3,4], $[2])")
      (is (equalp `(#(,(fset:seq 0 1 3 4))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove([0,1,2,3,4], $[2], $[0])")
      (is (equalp `(#(,(fset:seq 1 3 4))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove([0,1,2,3,4], $[0], $[2])")
      (is (equalp `(#(,(fset:seq 1 2 4))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove([0,1,2,3,4], $[#-1], $[0])")
      (is (equalp `(#(,(fset:seq 1 2 3))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove({x: 25, y: 42})")
      (is (equalp `(#(,(fset:map ("x" 25) ("y" 42)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove({x: 25, y: 42}, $.z)")
      (is (equalp `(#(,(fset:map ("x" 25) ("y" 42)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove({x: 25, y: 42}, $.y)")
      (is (equalp `(#(,(fset:map ("x" 25)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_remove({x: 25, y: 42}, $)")
      (is (equalp `(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_insert([1,2,3,4], $[#], 99)")
      (is (equalp `(#(,(fset:seq 1 2 3 4 99))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_insert([1,[2,3],4], $[1][#], 99)")
      (is (equalp `(#(,(fset:seq 1 (fset:seq 2 3 99) 4))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_insert({a: 2, c: 4}, $.a, 99)")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" 4)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_insert({a: 2, c: 4}, $.e, 99)")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" 4) ("e" 99)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_replace({a: 2, c: 4}, $.a, 99)")
      (is (equalp `(#(,(fset:map ("a" 99) ("c" 4)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_replace({a: 2, c: 4}, $.e, 99)")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" 4)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_set({a: 2, c: 4}, $.a, 99)")
      (is (equalp `(#(,(fset:map ("a" 99) ("c" 4)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_set({a: 2, c: 4}, $.e, 99)")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" 4) ("e" 99)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_set({a: 2, c: 4}, $.c, [97,96])")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" (fset:seq 97 96))))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_set({a: 2, c: 4}, $.d.e[0][0], 97)")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" 4) ("d" (fset:map ("e" (fset:seq (fset:seq 97)))))))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_set({a: 2, c: 4}, $.d[#], 97)")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" 4)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5, {f: 7}]}, $)")
      (is (equalp `(#(,(fset:map ("a" 2) ("c" (fset:seq 4 5 (fset:map ("f" 7))))))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5, {f: 7}]}, $.c)")
      (is (equalp `(#(,(fset:seq 4 5 (fset:map ("f" 7))))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5, {f: 7}]}, $.c[2])")
      (is (equalp `(#(,(fset:map ("f" 7)))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5, {f: 7}]}, $.c[2].f)")
      (is (equalp '(#(7)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5], f: 7}, $.c, $.a)")
      (is (equalp `(#(,(fset:seq (fset:seq 4 5) 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5], f: 7}, $.c[#-1])")
      (is (equalp '(#(5)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5], f: 7}, $.x)")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 2, c: [4, 5], f: 7}, $.x, $.a)")
      (is (equalp `(#(,(fset:seq :null 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: 'xyz'}, $.a)")
      (is (equalp '(#("xyz")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT path_extract({a: NULL}, $.a)")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))))

#-ecl
(test directory-db
  (let* ((endb/lib:*log-level* (endb/lib:resolve-log-level :off))
         (target-dir (asdf:system-relative-pathname :endb-test "target/"))
         (test-dir (merge-pathnames "endb_data_directory/" target-dir)))
    (unwind-protect
         (let ((db (make-directory-db :directory test-dir)))
           (unwind-protect
                (let ((write-db (begin-write-tx db)))

                  (multiple-value-bind (result result-code)
                      (execute-sql write-db "INSERT INTO t1(a) VALUES(103)")
                    (is (null result))
                    (is (= 1 result-code)))

                  (is (equalp '(#(103)) (execute-sql write-db "SELECT * FROM t1")))

                  (setf db (commit-write-tx db write-db)))
             (db-close db))

           (let ((db (make-directory-db :directory test-dir)))
             (unwind-protect
                  (progn
                    (is (equalp '(#(103)) (execute-sql db "SELECT * FROM t1")))

                    (let ((write-db (begin-write-tx db)))

                      (multiple-value-bind (result result-code)
                          (execute-sql write-db "INSERT INTO t1(a) VALUES(104)")
                        (is (null result))
                        (is (= 1 result-code)))

                      (is (equalp '(#(103) #(104)) (execute-sql write-db "SELECT * FROM t1 ORDER BY a")))

                      (multiple-value-bind (result result-code)
                          (execute-sql write-db "DELETE FROM t1 WHERE a = 103")
                        (is (null result))
                        (is (= 1 result-code)))

                      (is (equalp '(#(104)) (execute-sql write-db "SELECT * FROM t1 ORDER BY a")))

                      (setf db (commit-write-tx db write-db))))
               (db-close db)))

           (let ((db (make-directory-db :directory test-dir)))
             (unwind-protect
                  (is (equalp '(#(104)) (execute-sql db "SELECT * FROM t1 ORDER BY a")))
               (db-close db))))
      (when (probe-file test-dir)
        (uiop:delete-directory-tree test-dir :validate t)))))

#-ecl
(test directory-db-tx-log-version
  (let* ((endb/lib:*log-level* (endb/lib:resolve-log-level :off))
         (target-dir (asdf:system-relative-pathname :endb-test "target/"))
         (test-dir (merge-pathnames "endb_data_tx_log_version/" target-dir)))
    (unwind-protect
         (let ((db (make-directory-db :directory test-dir)))
           (unwind-protect
                (let ((write-db (begin-write-tx db)))

                  (multiple-value-bind (result result-code)
                      (execute-sql write-db "INSERT INTO t1(a) VALUES(103)")
                    (is (null result))
                    (is (= 1 result-code)))

                  (is (equalp '(#(103)) (execute-sql write-db "SELECT * FROM t1")))

                  (setf db (commit-write-tx db write-db)))
             (db-close db))

           (let ((endb/storage:*tx-log-version* (1+ endb/storage:*tx-log-version*)))
             (signals-with-msg simple-error
                 "Transaction log version mismatch: 6 does not match stored: 5"
               (make-directory-db :directory test-dir))))
      (when (probe-file test-dir)
        (uiop:delete-directory-tree test-dir :validate t)))))

(test join-bug-projecting-system-time
  (let* ((db (begin-write-tx (make-db)))
         (system-time-as-of-insert (endb/sql/db:syn-current_timestamp db)))
    (multiple-value-bind (result result-code)
        (execute-sql db "INSERT INTO products {name: 'Mangoes'}, {name: 'Apples'}")
      (is (null result))
      (is (= 2 result-code)))
    (multiple-value-bind (result result-code)
        (execute-sql db "INSERT INTO sales {name: 'Mangoes'}")
      (is (null result))
      (is (= 1 result-code)))
    (is (equalp (list (vector "Mangoes"
                              (fset:map ("start" system-time-as-of-insert)
                                        ("end" endb/sql/expr:+end-of-time+))
                              (fset:map ("start" system-time-as-of-insert)
                                        ("end" endb/sql/expr:+end-of-time+))))
                (execute-sql db "SELECT s.name, s.system_time, p.system_time FROM sales s JOIN products p ON s.name = p.name WHERE p.name = 'Mangoes'")))))

(test join-bug-multiple-variables-with-type-widening
  (let* ((db (make-db)))
    (is (equalp `(#(2 ,(endb/arrow:parse-arrow-date-millis "2001-01-01") 2.0 ,(endb/arrow:parse-arrow-timestamp-micros "2001-01-01")))
                (execute-sql db "SELECT * FROM (VALUES (2, 2001-01-01)) AS foo(a, b) JOIN (VALUES (2.0, 2001-01-01T00:00:00)) AS bar(a, b) ON foo.a = bar.a AND foo.b = bar.b")))))

(test dml
  (let ((endb/sql/expr:*sqlite-mode* t)
        (db (begin-write-tx (make-db))))
    (multiple-value-bind (result result-code)
        (execute-sql db "CREATE TABLE t1(a INTEGER, b INTEGER)")
      (is (null result))
      (is (eq t result-code)))

    (multiple-value-bind (result result-code)
        (execute-sql db "INSERT INTO t1 VALUES(1,2), (1,3)")
      (is (null result))
      (is (= 2 result-code))
      (is (equalp '(#(1 2) #(1 3))
                  (endb/sql/db:base-table-visible-rows db "t1")))
      (is (equalp '(#(1 2) #(1 3)) (execute-sql db "SELECT * FROM t1 ORDER BY 2"))))

    (multiple-value-bind (result result-code)
        (execute-sql db "UPDATE t1 SET a = 2 WHERE b = 2")
      (is (null result))
      (is (= 1 result-code))
      (is (equalp '(#(1 3) #(2 2))
                  (endb/sql/db:base-table-visible-rows db "t1")))
      (is (equalp '(#(2 2) #(1 3)) (execute-sql db "SELECT * FROM t1 ORDER BY 2"))))

    (multiple-value-bind (result result-code)
        (execute-sql db "DELETE FROM t1 WHERE b = 3")
      (is (null result))
      (is (= 1 result-code))
      (is (equalp '(#(2 2))
                  (endb/sql/db:base-table-visible-rows db "t1")))
      (is (equalp '(#(2 2)) (execute-sql db "SELECT * FROM t1 ORDER BY 2"))))

    (multiple-value-bind (result result-code)
        (execute-sql db "INSERT INTO josé(神) VALUES(2), (3)")
      (is (null result))
      (is (= 2 result-code)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM josé ORDER BY 1")
      (is (equalp '(#(2) #(3)) result))
      (is (equal '("神") columns)))))

(defparameter +random-uuid-scanner+
  (ppcre:create-scanner "^[\\da-f]{8}-[\\da-f]{4}-4[\\da-f]{3}-[89ab][\\da-f]{3}-[\\da-f]{12}$"))

(defun %random-uuid-p (x)
  (and (stringp x)
       (not (null (ppcre:scan +random-uuid-scanner+ x)))))

(test dql
  (let ((endb/sql/expr:*sqlite-mode* t)
        (db (begin-write-tx (make-db))))
    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 + 1")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CASE 1 + 1 WHEN 3 THEN 1 WHEN 2 THEN 2 END")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CASE WHEN TRUE THEN 2 WHEN FALSE THEN 1 END")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CASE WHEN FALSE THEN 2 ELSE 1 END")
      (is (equalp '(#(1)) result))
      (is (equal '("column1") columns)))

    (execute-sql db "CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER)")
    (execute-sql db "INSERT INTO t1 VALUES(103,102,100,101,104)")

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT a FROM t1")
      (is (equalp '(#(103)) result))
      (is (equal '("a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT a FROM t1 LIMIT 0 OFFSET 0")
      (is (equalp '() result))
      (is (equal '("a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT t1.b + t1.c AS x FROM t1")
      (is (equalp '(#(202)) result))
      (is (equal '("x") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM t1")
      (is (equalp '(#(103 102 100 101 104)) result))
      (is (equal '("a" "b" "c" "d" "e") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM t1 WHERE a IN (102, 103)")
      (is (equalp '(#(103 102 100 101 104)) result))
      (is (equal '("a" "b" "c" "d" "e") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM t1 WHERE a IN (VALUES (102), (103))")
      (is (equalp '(#(103 102 100 101 104)) result))
      (is (equal '("a" "b" "c" "d" "e") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM t1 WHERE b = 102")
      (is (equalp '(#(103 102 100 101 104)) result))
      (is (equal '("a" "b" "c" "d" "e") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT b, COUNT(t1.a) FROM t1 GROUP BY b")
      (is (equalp '(#(102 1)) result))
      (is (equal '("b" "column2") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT SUM(a) FROM t1")
      (is (equalp '(#(103)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 FROM t1 HAVING SUM(a) = 103")
      (is (equalp '(#(1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT t1.a, x.a FROM t1, t1 AS x WHERE t1.a = x.a")
      (is (equalp '(#(103 103)) result))
      (is (equal '("a" "a") columns)))

    (execute-sql db "INSERT INTO t1(e,c,b,d,a) VALUES(103,102,102,101,104), (NULL,102,NULL,101,104)")

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT COUNT(*), COUNT(e), SUM(e) FILTER (WHERE TRUE), AVG(a), MIN(b), MAX(c), b FROM t1 GROUP BY b ORDER BY COUNT(*)")
      (is (equalp '(#(1 0 :null 104.0d0 :null 102 :null) #(2 2 207 103.5d0 102 102 102)) result))
      (is (equal '("column1" "column2" "column3" "column4" "column5" "column6" "b") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot nest aggregate functions: SUM"
      (execute-sql db "SELECT COUNT(SUM(e)) FROM t1 WHERE FALSE"))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT COUNT(*) FROM t1 WHERE FALSE")
      (is (equalp '(#(0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT COUNT(*) FILTER (WHERE a = 104) FROM t1")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT ALL 74 * - COALESCE ( + CASE - CASE WHEN NOT ( NOT - 79 >= NULL ) THEN 48 END WHEN + + COUNT( * ) THEN 6 END, MIN( ALL + - 30 ) * 45 * 77 ) * - 14")
      (is (equalp '(#(-107692200)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "VALUES(0,6,5.6,'jtqxx',9,5.19,'qvgba')")
      (is (equalp '(#(0 6 5.6d0 "jtqxx" 9 5.19d0 "qvgba")) result))
      (is (equal '("column1" "column2" "column3" "column4" "column5" "column6" "column7") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 IN (2)")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT SUM(t1.b) FROM t1 HAVING SUM(t1.b) = 204")
      (is (equalp '(#(204)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x JOIN (SELECT 1 AS b) y ON x.a = y.b")
      (is (equalp '(#(1 1)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x JOIN (SELECT 1 AS a) y USING (a)")
      (is (equalp '(#(1 1)) result))
      (is (equal '("a" "a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a, 2 AS b) x JOIN (SELECT 1 AS a, 2 AS b) y USING (a, b)")
      (is (equalp '(#(1 2 1 2)) result))
      (is (equal '("a" "b" "a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a, 2 AS b) x JOIN (SELECT 1 AS a, 1 AS b) y USING (a, b)")
      (is (null result))
      (is (equal '("a" "b" "a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x JOIN (SELECT 1 AS b) y ON x.a = y.b AND x.a > y.b")
      (is (null result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM LATERAL (SELECT 1 AS a) x JOIN (SELECT 2 AS b) y ON x.a < y.b")
      (is (equalp '(#(1 2)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x JOIN (SELECT 2 AS b) y ON x.a = y.b")
      (is (null result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x LEFT JOIN (SELECT 1 AS b) y ON x.a = y.b")
      (is (equalp '(#(1 1)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x LEFT JOIN LATERAL (SELECT 1 AS a) y USING (a)")
      (is (equalp '(#(1 1)) result))
      (is (equal '("a" "a") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a, 2 AS b) x LEFT JOIN (SELECT 1 AS a, 2 AS b) y USING (a, b)")
      (is (equalp '(#(1 2 1 2)) result))
      (is (equal '("a" "b" "a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a, 2 AS b) x LEFT JOIN (SELECT 1 AS a, 1 AS b) y USING (a, b)")
      (is (equalp '(#(1 2 :null :null)) result))
      (is (equal '("a" "b" "a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x LEFT JOIN (SELECT 1 AS b) y ON x.a = y.b AND x.a > y.b")
      (is (equalp '(#(1 :null)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x LEFT JOIN (SELECT 2 AS b) y ON x.a < y.b")
      (is (equalp '(#(1 2)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (SELECT 1 AS a) x LEFT JOIN (SELECT 2 AS b) y ON x.a = y.b")
      (is (equalp '(#(1 :null)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CARDINALITY([1, 2])")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT LENGTH([1, 2])")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT LENGTH({a: 1})")
      (is (equalp '(#(1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT LENGTH({})")
      (is (equalp '(#(0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CHARACTER_LENGTH('josé')")
      (is (equalp '(#(4)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT OCTET_LENGTH('josé')")
      (is (equalp '(#(5)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT \"josé\"")
      (is (equalp '(#("josé")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2] || [3, 4]")
      (is (equalp `(#(,(fset:seq 1 2 3 4))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2] || 3")
      (is (equalp `(#(,(fset:seq 1 2 3))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT NULL || [1, 2]")
      (is (equalp `(#(,(fset:seq :null 1 2))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UUID()")
      (is (%random-uuid-p (aref (first result) 0)))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UUID_BLOB('d2ce21c9')")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UUID_BLOB('d2ce21c9-d268-409a-b1e0-49e1200bfa47')")
      (is (equalp '(#(#(210 206 33 201 210 104 64 154 177 224 73 225 32 11 250 71))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UUID_BLOB(x'd2ce21c9d268409ab1e049e1200bfa47')")
      (is (equalp '(#(#(210 206 33 201 210 104 64 154 177 224 73 225 32 11 250 71))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UUID_STR(x'd2ce21c9d268409ab1e049e1200bfa47')")
      (is (equalp '(#("d2ce21c9-d268-409a-b1e0-49e1200bfa47")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UUID_STR('d2ce21c9d268409ab1e049e1200bfa47')")
      (is (equalp '(#("d2ce21c9-d268-409a-b1e0-49e1200bfa47")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT BASE64(x'cafe')")
      (is (equalp '(#("yv4=")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT BASE64('yv4=')")
      (is (equalp '(#(#(202 254))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT BASE64(NULL)")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT SHA1(x'cafe')")
      (is (equalp '(#("ac3c34dd3b4d1c52245d8e5cad42987b5027ca3d")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT SHA1(2)")
      (is (equalp '(#("da4b9237bacccdf19c0760cab7aec4a8359010b0")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT SHA1('2')")
      (is (equalp '(#("da4b9237bacccdf19c0760cab7aec4a8359010b0")) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT SHA1(NULL)")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT CAST([1, 'foo', {a: 2001-01-01, b: NULL}] AS VARCHAR)")
      (is (equalp `(#("[1,\"foo\",{\"a\":2001-01-01,\"b\":null}]")) result))
      (is (equalp (fset:seq 1 "foo" (fset:map ("a" (endb/arrow:parse-arrow-date-millis "2001-01-01")) ("b" :null)))
                  (interpret-sql-literal (aref (first result) 0))))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UNHEX('ABCD')")
      (is (equalp '(#(#(171 205))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UNHEX('A ')")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UNHEX('AB CD')")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UNHEX('ab CD', ' ')")
      (is (equalp '(#(#(171 205))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x'3135' = UNHEX('3135')")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x'3135' = UNHEX(3135)")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT RANDOMBLOB(8)")
      (is (typep (aref (first result) 0) '(vector (unsigned-byte 8))))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UNIXEPOCH('2001-01-01')")
      (is (equalp '(#(9.783072d8)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT UNIXEPOCH('1970-01-01T00:00:00Z')")
      (is (equalp '(#(0.0d0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT PATCH({a: 'b', c: {d: 'e', f: 'g'}}, {a: 'z', c: {f: NULL}})")
      (is (equalp `(#(,(fset:map ("a" "z") ("c" (fset:map ("d" "e")))))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 'barfoo' REGEXP '.*fo'")
      (is (equalp `(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2] < [2, 2]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [2, 2] < [2]")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [3] > [2, 2]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2] <= [1, 2]")
      (is (equalp `(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [1, 2] >= [2, 2]")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [2, 2, 3] > [2, 2, 1]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [2, 2, 3] >= [2, 2, 3]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [2, 2, 3] < [2, 2, 3]")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [2, 2, NULL] < [2, 2, 3]")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT [2, NULL] >= [2]")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 'fo\\no'")
      (is (equalp `(#(,(format nil "fo~%o"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT \"fo\\\"o\"")
      (is (equalp `(#(,(format nil "fo\"o"))) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x AS y FROM (VALUES (1), (2)) AS foo(x) ORDER BY -foo.x")
      (is (equalp '(#(2) #(1)) result))
      (is (equal '("y") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT x FROM (VALUES ('foo'), ('bar'), ('food')) AS foo(x) ORDER BY LENGTH(x), x DESC")
      (is (equalp '(#("foo") #("bar") #("food")) result))
      (is (equal '("x") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT COUNT(x) FROM (VALUES (1), (2)) AS foo(x) WHERE FALSE")
      (is (equalp '(#(0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT COUNT(x) FROM (VALUES (1), (2)) AS foo(x) WHERE FALSE GROUP BY x")
      (is (null result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (VALUES (NULL, 2)) AS foo(a, b) JOIN (VALUES (NULL, 2)) AS bar(a, b) ON foo.a = bar.a")
      (is (null result))
      (is (equal '("a" "b" "a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT a, SUM(b) AS b FROM (VALUES (NULL, 1), (NULL, 2)) AS foo(a, b) GROUP BY a")
      (is (equalp '(#(:null 3)) result))
      (is (equal '("a" "b") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "VALUES (NULL, 2) UNION VALUES (NULL, 2)")
      (is (equalp '(#(:null 2)) result))
      (is (equal '("column1" "column2") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT NULL IS UNKNOWN")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 < SOME (VALUES (1), (2))")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 = SOME (VALUES (1), (2))")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 == SOME (VALUES (1), (2))")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 != SOME (VALUES (1))")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 <> SOME (VALUES (1))")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 <> SOME (VALUES (NULL))")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 < ALL (VALUES (1), (2))")
      (is (equalp '(#(nil)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 < SOME (VALUES (1), (NULL))")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 < SOME (VALUES (NULL), (2))")
      (is (equalp '(#(t)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 < SOME (VALUES (NULL))")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 1 < ALL (VALUES (NULL), (2))")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM generate_series(1, 10) AS foo")
      (is (equalp '(#(1) #(2) #(3) #(4) #(5) #(6) #(7) #(8) #(9) #(10)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM LATERAL generate_series(1, 10, 2) AS foo")
      (is (equalp '(#(1) #(3) #(5) #(7) #(9)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT POSITION('o' IN 'foo')")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT 2 AS `foo bar`")
      (is (equalp '(#(2)) result))
      (is (equal '("foo bar") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (VALUES ([0,0,0]), ([1,2,3]), ([1,1,1]), (NULL), ([1,2,4])) AS t(val) WHERE val NOT NULL ORDER BY t.val <-> [3,3,3]")
      (is (equalp `(#(,(fset:seq 1 2 3))
                     #(,(fset:seq 1 2 4))
                     #(,(fset:seq 1 1 1))
                     #(,(fset:seq 0 0 0)))
                  result))
      (is (equal '("val") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT l2_distance([0,0], [3,4])")
      (is (equalp '(#(5)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT l2_distance([0,0], [0,1])")
      (is (equalp '(#(1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT l2_distance(NULL, [0,1])")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT * FROM (VALUES ([0,0,0]), ([1,2,3]), ([1,1,1]), (NULL), ([1,2,4])) AS t(val) WHERE val IS NOT NULL ORDER BY t.val <#> [3,3,3]")
      (is (equalp `(#(,(fset:seq 1 2 4))
                     #(,(fset:seq 1 2 3))
                     #(,(fset:seq 1 1 1))
                     #(,(fset:seq 0 0 0)))
                  result))
      (is (equal '("val") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT inner_product([1,2], [3,4])")
      (is (equalp '(#(11)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT val FROM (VALUES ([0,0,0]), ([1,2,3]), ([1,1,1]), ([1,2,4])) AS t(val) WHERE t.val <=> [3,3,3] NOT NULL ORDER BY t.val <=> [3,3,3]")
      (is (equalp `(#(,(fset:seq 1 1 1))
                     #(,(fset:seq 1 2 3))
                     #(,(fset:seq 1 2 4)))
                  result))
      (is (equal '("val") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT cosine_distance([1,2], [2,4])")
      (is (equalp '(#(0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT cosine_distance([1,2], [0,0])")
      (is (equalp '(#(:null)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT cosine_distance([1,1], [1,1])")
      (is (equalp '(#(0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT cosine_distance([1,0], [0,2])")
      (is (equalp '(#(1)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT cosine_distance([1,1], [-1,-1])")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT cosine_distance([1,1], [1.1,1.1])")
      (is (equalp '(#(0)) result))
      (is (equal '("column1") columns)))

    (multiple-value-bind (result columns)
        (execute-sql db "SELECT cosine_distance([1,1], [-1.1,-1.1])")
      (is (equalp '(#(2)) result))
      (is (equal '("column1") columns)))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Vectors need same dimensions: 2 is not 1"
      (execute-sql db "SELECT l2_distance([1,2], [3])"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Vectors need same dimensions: 2 is not 1"
      (execute-sql db "SELECT inner_product([1,2], [3])"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Vectors need same dimensions: 2 is not 1"
      (execute-sql db "SELECT cosine_distance([1,2], [3])"))

    #-ecl
    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid number of arguments: 1 to: GENERATE_SERIES min: 2 max: 3"
      (execute-sql db "SELECT * FROM generate_series(1) AS foo"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "ALL query must return single column"
      (execute-sql db "SELECT 1 < ALL (VALUES (1, 2))"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Scalar subquery must return single column"
      (execute-sql db "SELECT 1 = (VALUES (1, 2))"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown column"
      (execute-sql db "SELECT * FROM (SELECT 1 AS a) x JOIN (SELECT 1 AS b) y USING (a)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown column"
      (execute-sql db "SELECT x AS y FROM (VALUES (1), (2)) AS foo(x) ORDER BY -y"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid ORDER BY expression"
      (execute-sql db "VALUES (1), (2) ORDER BY -y"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Cannot resolve ORDER BY column: column2"
      (execute-sql db "VALUES (1), (2) ORDER BY column2"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "ORDER BY index not in range: 2"
      (execute-sql db "VALUES (1), (2) ORDER BY 2"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Number of UNION left columns: 2 does not match right columns: 1"
      (execute-sql db "VALUES (1, 2) UNION VALUES (1)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Complex number as result: \\(0.0, 1.0\\) to: SQRT"
      (execute-sql db "SELECT SQRT(-1)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "All VALUES must have the same number of columns: 2"
      (execute-sql db "VALUES (1, 2), (1)"))

    #-ecl
    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid number of arguments: 1 to: LIKE min: 2 max: 3"
      (execute-sql db "SELECT LIKE('foo')"))

    #-ecl
    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid number of arguments: 3 to: MATCH min: 2 max: 2"
      (execute-sql db "SELECT MATCH('foo', 'bar', 'baz')"))

    #-ecl
    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid argument types: LIKE\\(2, \"\"\\)"
      (execute-sql db "SELECT LIKE(2, '')"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid escape character: foo"
      (execute-sql db "SELECT '' LIKE '' ESCAPE 'foo'"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid escape character: 2"
      (execute-sql db "SELECT '' LIKE '' ESCAPE 2"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid end index: b"
      (execute-sql db "SELECT substring('foo', 1, 'b')"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Invalid number of arguments: 2 to: AVG min: 1 max: 1"
      (execute-sql db "SELECT AVG(2, 1)"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown table"
      (execute-sql db "SELECT y.* FROM (VALUES (['a', 'b', 'c'])) AS x"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Unknown table"
      (execute-sql db "SELECT { y.* } FROM (VALUES (['a', 'b', 'c'])) AS x"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "Selecting columns: \\(foo.x\\) that does not match group by: \\(y\\)"
      (execute-sql db "SELECT AVG(x), foo.* FROM (VALUES (1, 2)) AS foo(x, y) GROUP BY y"))

    (signals-with-msg endb/sql/expr:sql-runtime-error
        "IN query must return single column"
      (execute-sql db "SELECT 2 IN (VALUES (1, 2), (2, 3))"))))

(test interpret-sql-literal
  (is (equal "foo" (interpret-sql-literal "'foo'")))
  (is (equal "foo" (interpret-sql-literal "\"foo\"")))
  (is (equal (format nil "fo~%o") (interpret-sql-literal "\"fo\\no\"")))
  (is (= 2.0 (interpret-sql-literal "2.0")))
  (is (= 9223372036854775807 (interpret-sql-literal "9223372036854775807")))
  (is (= -9223372036854775808 (interpret-sql-literal "-9223372036854775808")))

  (is (= 170141183460469231731687303715884105727 (interpret-sql-literal "170141183460469231731687303715884105727")))
  (is (= -170141183460469231731687303715884105727 (interpret-sql-literal "-170141183460469231731687303715884105727")))

  (is (eq t (interpret-sql-literal "TRUE")))
  (is (null (interpret-sql-literal "FALSE")))
  (is (eq :null (interpret-sql-literal "NULL")))

  (is (equalp #(171 205) (interpret-sql-literal "X'ABCD'")))

  (is (equalp (endb/arrow:parse-arrow-date-millis "2001-01-01") (interpret-sql-literal "2001-01-01")))
  (is (equalp (endb/arrow:parse-arrow-time-micros "12:01:20") (interpret-sql-literal "12:01:20")))
  (is (equalp (endb/arrow:parse-arrow-timestamp-micros "2023-05-16T14:43:39.970062Z") (interpret-sql-literal "2023-05-16T14:43:39.970062Z")))
  (is (equalp (endb/arrow:parse-arrow-interval-month-day-nanos "P3Y2MT12H30M5S") (interpret-sql-literal "P3Y2MT12H30M5S")))

  (is (equalp (endb/arrow:parse-arrow-interval-month-day-nanos "P1Y") (interpret-sql-literal "INTERVAL '1' YEAR")))
  (is (equalp (endb/arrow:parse-arrow-interval-month-day-nanos "P1Y2M") (interpret-sql-literal "INTERVAL '1-2' YEAR TO MONTH")))

  (is (equalp (fset:map ("address" (fset:map ("street" "Street") ("number" 42)))
                        ("friends" (fset:seq 1 2)))
              (interpret-sql-literal "{address: {street: 'Street', number: 42}, friends: [1, 2]}")))
  (is (equalp (fset:empty-map) (interpret-sql-literal "{}")))

  (signals-with-msg endb/sql/expr:sql-runtime-error
      "Invalid literal: 2001-01-01ASFOO"
    (interpret-sql-literal "2001-01-01ASFOO"))

  (signals-with-msg endb/sql/expr:sql-runtime-error
      "Invalid literal: 2001-01"
    (interpret-sql-literal "2001-01"))

  (signals-with-msg endb/sql/expr:sql-runtime-error
      "Invalid literal: 1 \\+ 2"
    (interpret-sql-literal "1 + 2"))

  (signals-with-msg endb/sql/expr:sql-runtime-error
      "Invalid literal: INTERVAL '1-2' MONTH TO YEAR"
    (interpret-sql-literal "INTERVAL '1-2' MONTH TO YEAR"))

  (signals-with-msg endb/sql/expr:sql-runtime-error
      "Invalid literal: INTERVAL '1-2' YEAR"
    (interpret-sql-literal "INTERVAL '1-2' YEAR"))

  (signals-with-msg endb/sql/expr:sql-runtime-error
      "Invalid literal: \\[1 \\+ 2\\]"
    (interpret-sql-literal "[1 + 2]"))

  (signals-with-msg endb/sql/expr:sql-runtime-error
      "Invalid literal: {foo: 1 \\+ 2}"
    (interpret-sql-literal "{foo: 1 + 2}"))

  (is (equalp (endb/arrow:parse-arrow-date-millis "2001-01-01")
              (endb/json:resolve-json-ld-xsd-scalars (interpret-sql-literal "{\"@value\": \"2001-01-01\", \"@type\": \"xsd:date\"}")))))