(module test-sql mzscheme 
  
  ;;;
  
  ; TODO:
  ;  - finish the tests from <http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/expr.test&v=1.50>
  ;  - tests for SELECT
  
  
  (require (planet "78.ss" ("soegaard" "srfi.plt"))
           (planet "sqlite.ss" ("jaymccarthy" "sqlite.plt"))
           "sql.scm")
  
  
  (define db (open (string->path "test-sql.db")))
  ;(create-expr-test-table)
  ;(create-expr-test-table2)
  
  ;                                                                                 
  ;   ######  ### ### #####   #####   ######   ### #   ### #   #####    ###  ###  ###
  ;    #   #   #   #   #   #   #   #   #   #  #   ##  #   ##     #     #   #  ##   #
  ;    # #      # #    #   #   #   #   # #    #       #          #    #     # # #  #
  ;    ###       #     #   #   #   #   ###     ####    ####      #    #     # # #  #
  ;    # #       #     ####    ####    # #         #       #     #    #     # #  # #
  ;    #        # #    #       #  #    #           #       #     #    #     # #  # #
  ;    #   #   #   #   #       #   #   #   #  ##   #  ##   #     #     #   #  #   ##
  ;   ######  ### ### ###     ###   # ######  # ###   # ###    #####    ###  ###  ##
  ;                                                                                 
  
  ; <http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/expr.test&v=1.50>
  
  (define (create-expr-test-table)
    (exec/ignore db "CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)")
    (exec/ignore db "INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')"))
  
  (define (create-expr-test-table2)
    (exec/ignore db "CREATE TABLE test2(a int, b int)")
    (do ((i 1 (add1 i)))
      ((= i 21) (void))
      (exec/ignore db (format "INSERT INTO test2 VALUES(~a,~a)" i (expt 2 i)))))
  
  
  (define (run-sql-expr sql-str)
    (let ([result #f])
      (exec db sql-str
            (lambda (colnames rows)
              (set! result (vector-ref rows 0))
              0))
      result))
  
  (define-syntax check-expr
    (syntax-rules ()
      [(check-expr name (assignment ...) expr expected-result)
       (check (begin 'name 'expr 
                     (run-sql-expr (string-append "BEGIN; " 
                                                  (sql (UPDATE test1 SET assignment ...)) "; "
                                                  (sql (SELECT expr FROM test1)) "; "
                                                  "ROLLBACK;")))
              => expected-result)]))
  
  ;(exec db
  ;        (check-expr 'expr-1.1  ((i1 = 10) (i2 = 20)) ((+  i1 i2))  30)
  ;        (lambda (col-names rows)
  ;          (display (list col-names rows))
  ;          0))
  ; => (#(i1 + i2) #(30))
  
  ;(create-expr-test-table)
  
  (check-expr expr-1.1  ((i1 = 10) (i2 = 20)) ((+  i1 i2))   "30")
  (check-expr expr-1.2  ((i1 = 10) (i2 = 20)) ((-  i1 i2))  "-10")
  (check-expr expr-1.3  ((i1 = 10) (i2 = 20)) ((*  i1 i2))  "200")
  (check-expr expr-1.4  ((i1 = 10) (i2 = 20)) ((/  i1 i2))    "0")
  (check-expr expr-1.5  ((i1 = 10) (i2 = 20)) ((/  i2 i1))    "2")
  (check-expr expr-1.6  ((i1 = 10) (i2 = 20)) ((<  i2 i1))    "0")
  (check-expr expr-1.7  ((i1 = 10) (i2 = 20)) ((<= i2 i1))    "0")
  (check-expr expr-1.8  ((i1 = 10) (i2 = 20)) ((>  i2 i1))    "1")
  (check-expr expr-1.9  ((i1 = 10) (i2 = 20)) ((>= i2 i1))    "1")
  (check-expr expr-1.10 ((i1 = 10) (i2 = 20)) ((!= i2 i1))    "1")
  (check-expr expr-1.11 ((i1 = 10) (i2 = 20)) ((= i2 i1))     "0")
  (check-expr expr-1.12 ((i1 = 10) (i2 = 20)) ((<> i2 i1))     "1")
  (check-expr expr-1.13 ((i1 = 10) (i2 = 20)) ((== i2 i1))     "0")
  (check-expr expr-1.14 ((i1 = 20) (i2 = 20)) ((< i2 i1))      "0")
  (check-expr expr-1.15 ((i1 = 20) (i2 = 20)) ((<= i2 i1))     "1")
  (check-expr expr-1.16 ((i1 = 20) (i2 = 20)) ((>  i2 i1))     "0")
  (check-expr expr-1.17 ((i1 = 20) (i2 = 20)) ((>= i2 i1))     "1")
  (check-expr expr-1.18 ((i1 = 20) (i2 = 20)) ((!= i2 i1))     "0")
  (check-expr expr-1.19 ((i1 = 20) (i2 = 20)) ((=  i2 i1))     "1")
  (check-expr expr-1.20 ((i1 = 20) (i2 = 20)) ((>  i2 i1))     "0")
  (check-expr expr-1.21 ((i1 = 20) (i2 = 20)) ((>= i2 i1))     "1")
  (check-expr expr-1.22 ((i1 = 1)  (i2 = 2)  (r1 = 3.0))  ((+ i1 (* i2 r1)))  "7.0")
  (check-expr expr-1.23 ((i1 = 1)  (i2 = 2)  (r1 = 3.0))  ((* (+ i1 i2) r1))  "9.0")
  (check-expr expr-1.24 ((i1 = 1)  (i2 = 2))  ((min i1 i2 (+ i1 i2) (- i1 i2)))  "-1")
  (check-expr expr-1.25 ((i1 = 1)  (i2 = 2))  ((max i1 i2 (+ i1 i2) (- i1 i2)))  "3")
  (check-expr expr-1.26 ((i1 = 1)  (i2 = 2))  ((max i1 i2 (+ i1 i2) (- i1 i2)))  "3")
  (check-expr expr-1.27 ((i1 = 1)  (i2 = 2))  ((AND (== i1 1) (= i2 2)))         "1")
  (check-expr expr-1.28 ((i1 = 1)  (i2 = 2))  ((AND (= i1 2)  (= i2 1)))  "0")
  (check-expr expr-1.29 ((i1 = 1)  (i2 = 2))  ((AND (= i1 1)  (= i2 1)))  "0")
  (check-expr expr-1.30 ((i1 = 1)  (i2 = 2))  ((AND (= i1 2)  (= i2 2)))  "0")
  (check-expr expr-1.31 ((i1 = 1)  (i2 = 2))  ((AND (== i1 1) (= i2 2)))  "1")
  (check-expr expr-1.32 ((i1 = 1)  (i2 = 2))  ((OR (= i1 2)  (= i2 1)))  "0")
  (check-expr expr-1.33 ((i1 = 1)  (i2 = 2))  ((OR (= i1 1)  (= i2 1)))  "1")
  (check-expr expr-1.34 ((i1 = 1)  (i2 = 2))  ((OR (= i1 2)  (= i2 2)))  "1")
  (check-expr expr-1.35 ((i1 = 1)  (i2 = 2))  ((= (- i1 i2) -1))         "1")
  (check-expr expr-1.36 ((i1 = 1)  (i2 = 0))  ((NOT i1))  "0")
  (check-expr expr-1.37 ((i1 = 1)  (i2 = 0))  ((NOT i2))  "1")
  (check-expr expr-1.38 ((i1 = 1))            ((- i1))   "-1")
  (check-expr expr-1.39 ((i1 = 1))            ((+ i1))    "1")
  (check-expr expr-1.40 ((i1 = 1) (i2 = 2)) ((+ (+ i2 i1)))  "3")
  (check-expr expr-1.41 ((i1 = 1) (i2 = 2)) ((- (+ i2 i1)))  "-3")
  (check-expr expr-1.42 ((i1 = 1) (i2 = 2)) ((\| i1 i2))    "3")   ; | needs escaping on the Scheme side
  (check-expr expr-1.42b ((i1 = 1) (i2 = 2)) ((\| 4 2))      "6")
  (check-expr expr-1.43  ((i1 = 1) (i2 = 2))  ((& i1 i2))     "0")
  (check-expr expr-1.43b ((i1 = 1) (i2 = 2))  ((& 4 5))       "4")
  (check-expr expr-1.44  ((i1 = 1))           ((~ i1))       "-2")  
  (check-expr expr-1.45  ((i1 = 1)  (i2 = 3)) ((<< i1 i2))    "8")
  (check-expr expr-1.46  ((i1 = 32) (i2 = 3)) ((>> i1 i2))    "4")
  (check-expr expr-1.47  ((i1 = 9999999999)  (i2 = 8888888888))  ((< i1 i2)) "0")
  (check-expr expr-1.48  ((i1 = 9999999999)  (i2 = 8888888888))  ((= i1 i2)) "0")
  (check-expr expr-1.49  ((i1 = 9999999999)  (i2 = 8888888888))  ((> i1 i2)) "1")
  (check-expr expr-1.50  ((i1 = 99999999999) (i2 = 99999999998)) ((< i1 i2)) "0")
  (check-expr expr-1.51  ((i1 = 99999999999)  (i2 = 99999999998)) ((= i1 i2)) "0")
  (check-expr expr-1.52  ((i1 = 99999999999)  (i2 = 99999999998)) ((> i1 i2)) "1")
  (check-expr expr-1.53  ((i1 = 099999999999) (i2 = 99999999999)) ((< i1 i2)) "0")
  (check-expr expr-1.54  ((i1 = 099999999999) (i2 = 99999999999)) ((= i1 i2)) "1")
  (check-expr expr-1.55  ((i1 = 099999999999) (i2 = 99999999999)) ((> i1 i2)) "0")
  (check-expr expr-1.56  ((i1 = 25)   (i2 = 11))    ((% i1 i2))                "3")
  (check-expr expr-1.58  ((i1 = NULL) (i2 = 1))     ((coalesce (+ i1 i2) 99)) "99")   
  (check-expr expr-1.59  ((i1 = 1)    (i2 = NULL))  ((coalesce (+ i1 i2) 99)) "99")   
  (check-expr expr-1.60  ((i1 = NULL) (i2 = NULL))  ((coalesce (+ i1 i2) 99)) "99")
  (check-expr expr-1.61  ((i1 = NULL) (i2 = 11))    ((coalesce (- i1 i2) 99)) "99")
  (check-expr expr-1.62  ((i1 = 1)    (i2 = NULL))  ((coalesce (- i1 i2) 99)) "99")   
  (check-expr expr-1.63  ((i1 = NULL) (i2 = NULL))  ((coalesce (- i1 i2) 99)) "99")   
  (check-expr expr-1.64  ((i1 = NULL) (i2 = 1))     ((coalesce (* i1 i2) 99)) "99")
  (check-expr expr-1.65  ((i1 = 1)    (i2 = NULL))  ((coalesce (* i1 i2) 99)) "99")   
  (check-expr expr-1.66  ((i1 = NULL) (i2 = NULL))  ((coalesce (* i1 i2) 99)) "99")   
  (check-expr expr-1.67  ((i1 = NULL) (i2 = 1))     ((coalesce (/ i1 i2) 99)) "99")
  (check-expr expr-1.68  ((i1 = 1)    (i2 = NULL))  ((coalesce (/ i1 i2) 99)) "99")   
  (check-expr expr-1.69  ((i1 = NULL) (i2 = NULL))  ((coalesce (/ i1 i2) 99)) "99")   
  (check-expr expr-1.70  ((i1 = NULL) (i2 = 1))     ((coalesce (< i1 i2) 99)) "99")
  (check-expr expr-1.71  ((i1 = 1)    (i2 = NULL))  ((coalesce (> i1 i2) 99)) "99")   
  (check-expr expr-1.72  ((i1 = NULL) (i2 = NULL))  ((coalesce (<= i1 i2) 99)) "99")   
  (check-expr expr-1.73  ((i1 = NULL) (i2 = 1))     ((coalesce (>= i1 i2) 99)) "99")
  (check-expr expr-1.74  ((i1 = 1)    (i2 = NULL))  ((coalesce (!= i1 i2) 99)) "99")   
  (check-expr expr-1.75  ((i1 = NULL) (i2 = NULL))  ((coalesce (== i1 i2) 99)) "99")   
  (check-expr expr-1.76  ((i1 = NULL) (i2 = NULL))  ((coalesce (NOT i1)   99)) "99")   
  (check-expr expr-1.77  ((i1 = NULL) (i2 = NULL))  ((coalesce (AND (ISNULL i1) (= i2 5)) 99)) "99")
  (check-expr expr-1.78  ((i1 = NULL) (i2 = NULL))  ((coalesce (AND (ISNULL i1) (= i2 5))     99)) "99")
  (check-expr expr-1.79  ((i1 = NULL) (i2 = NULL))  ((coalesce (OR  (ISNULL i1) (= i2 5))     99)) "1")   
  (check-expr expr-1.80  ((i1 = NULL) (i2 = NULL))  ((coalesce (AND (= i1 5)    (ISNULL i2) ) 99)) "99")
  (check-expr expr-1.81  ((i1 = NULL) (i2 = NULL))  ((coalesce (OR  (= i1 5)    (ISNULL i2) ) 99)) "1")
  (check-expr expr-1.82  ((i1 = NULL) (i2 = 3))     ((coalesce (min i1 i2 1) 99)) "99")
  (check-expr expr-1.83  ((i1 = NULL) (i2 = 3))     ((coalesce (max i1 i2 1) 99)) "99")
  (check-expr expr-1.84  ((i1 = 3)    (i2 = NULL))  ((coalesce (min i1 i2 1) 99)) "99")   
  (check-expr expr-1.85  ((i1 = 3)    (i2 = NULL))  ((coalesce (max i1 i2 1) 99)) "99")
  (check-expr expr-1.86  ((i1 = 3) (i2 = 8))  ((BETWEEN      5 i1 i2))  "1")
  (check-expr expr-1.87  ((i1 = 3) (i2 = 8))  ((NOT BETWEEN  5 i1 i2))  "0")
  (check-expr expr-1.88  ((i1 = 3) (i2 = 8))  ((BETWEEN     55 i1 i2))  "0")   
  (check-expr expr-1.89  ((i1 = 3) (i2 = 8))  ((NOT BETWEEN 55 i1 i2))  "1")
  (check-expr expr-1.90  ((i1 = 3) (i2 = NULL))  ((BETWEEN      5 i1 i2))  #f)     
  (check-expr expr-1.91  ((i1 = 3) (i2 = NULL))  ((NOT BETWEEN  5 i1 i2))  #f)
  (check-expr expr-1.92  ((i1 = 3) (i2 = NULL))  ((BETWEEN      2 i1 i2))  "0")   
  (check-expr expr-1.93  ((i1 = 3) (i2 = NULL))  ((NOT BETWEEN  2 i1 i2))  "1")
  (check-expr expr-1.94  ((i1 = NULL) (i2 = 8))  ((BETWEEN      2 i1 i2))  #f)     
  (check-expr expr-1.95  ((i1 = NULL) (i2 = 8))  ((NOT BETWEEN  2 i1 i2))  #f)
  (check-expr expr-1.95b ((i1 = NULL) (i2 = 8))  ((BETWEEN      55 i1 i2))  "0")   
  (check-expr expr-1.95c ((i1 = NULL) (i2 = 8))  ((NOT BETWEEN  55 i1 i2))  "1")
  (check-expr expr-1.96  ((i1 = NULL) (i2 = 3))     ((coalesce (<< i1 i2) 99))  "99")     
  (check-expr expr-1.97  ((i1 = 32)   (i2 = NULL))  ((coalesce (>> i1 i2) 99))  "99")
  (check-expr expr-1.98  ((i1 = NULL) (i2 = NULL))  ((coalesce (\| i1 i2) 99))  "99")   
  (check-expr expr-1.99  ((i1 = 32)   (i2 = NULL))  ((coalesce (& i1 i2)  99))  "99")
  (check-expr expr-1.100  ((i1 = 1) (i2 = ""))  ((= i1 i2))  "0")
  (check-expr expr-1.101  ((i1 = 0) (i2 = ""))  ((= i1 i2))  "0")
  (check-expr expr-1.102  ((i1 = 40) (i2 = 1))  ((<< i2 i1))  "1099511627776")
  
  (check-expr expr-2.1  ((r1 = 1.23) (r2 = 2.34))  ((+ r1 r2))  "3.57")
  (check-expr expr-2.2  ((r1 = 1.23) (r2 = 2.34))  ((- r1 r2))  "-1.11")
  (check-expr expr-2.3  ((r1 = 1.23) (r2 = 2.34))  ((* r1 r2))  "2.8782")
  (check-expr expr-2.4  ((r1 = 1.23) (r2 = 2.34))  ((/ r1 r2))  "0.525641025641026")
  (check-expr expr-2.5  ((r1 = 1.23) (r2 = 2.34))  ((/ r2 r1))  "1.90243902439024")
  (check-expr expr-2.6  ((r1 = 1.23) (r2 = 2.34))  ((< r2 r1))  "0")
  (check-expr expr-2.7  ((r1 = 1.23) (r2 = 2.34))  ((<= r2 r1)) "0")
  (check-expr expr-2.8   ((r1 = 1.23) (r2 = 2.34))  ((>  r2 r1)) "1")
  (check-expr expr-2.9   ((r1 = 1.23) (r2 = 2.34))  ((>= r2 r1)) "1")
  (check-expr expr-2.10  ((r1 = 1.23) (r2 = 2.34))  ((!= r2 r1)) "1")
  (check-expr expr-2.11  ((r1 = 1.23) (r2 = 2.34))  ((=  r2 r1)) "0")
  (check-expr expr-2.12  ((r1 = 1.23) (r2 = 2.34))  ((<> r2 r1)) "1")
  (check-expr expr-2.13  ((r1 = 1.23) (r2 = 2.34))  ((== r2 r1)) "0")
  (check-expr expr-2.14  ((r1 = 2.34) (r2 = 2.34))  ((<  r2 r1)) "0")
  (check-expr expr-2.15  ((r1 = 2.34) (r2 = 2.34))  ((<= r2 r1)) "1")
  (check-expr expr-2.16  ((r1 = 2.34) (r2 = 2.34))  ((>  r2 r1)) "0")
  (check-expr expr-2.17  ((r1 = 2.34) (r2 = 2.34))  ((>= r2 r1)) "1")
  (check-expr expr-2.18  ((r1 = 2.34) (r2 = 2.34))  ((!= r2 r1)) "0")
  (check-expr expr-2.19  ((r1 = 2.34) (r2 = 2.34))  ((=  r2 r1)) "1")
  (check-expr expr-2.20  ((r1 = 2.34) (r2 = 2.34))  ((<> r2 r1)) "0")
  (check-expr expr-2.21  ((r1 = 2.34) (r2 = 2.34))  ((== r2 r1)) "1")
  (check-expr expr-2.22  ((r1 = 1.23) (r2 = 2.34))  ((min r1 r2 (+ r1 r2) (- r1 r2))) "-1.11")
  (check-expr expr-2.23  ((r1 = 1.23) (r2 = 2.34))  ((max r1 r2 (+ r1 r2) (- r1 r2))) "3.57")
  (check-expr expr-2.24  ((r1 = 25.0) (r2 = 11.0))  ((% r1 r2)) "3.0")
  (check-expr expr-2.25  ((r1 = 1.23) (r2 = NULL))  ((coalesce (+ r1 r2)  99.0))  "99.0")
  
  (check-expr expr-3.1  ((t1 = "abc") (t2 = "xyz"))  ((<  t1 t2)) "1")
  (check-expr expr-3.2  ((t1 = "xyz") (t2 = "abc"))  ((<  t1 t2)) "0")
  (check-expr expr-3.3  ((t1 = "abc") (t2 = "abc"))  ((<  t1 t2)) "0")
  (check-expr expr-3.4  ((t1 = "abc") (t2 = "xyz"))  ((<= t1 t2)) "1")
  (check-expr expr-3.5  ((t1 = "xyz") (t2 = "abc"))  ((<= t1 t2)) "0")
  (check-expr expr-3.6  ((t1 = "abc") (t2 = "abc"))  ((<= t1 t2)) "1")
  (check-expr expr-3.7  ((t1 = "abc") (t2 = "xyz"))  ((>  t1 t2)) "0")
  (check-expr expr-3.8  ((t1 = "xyz") (t2 = "abc"))  ((>  t1 t2)) "1")
  (check-expr expr-3.9  ((t1 = "abc") (t2 = "abc"))  ((>  t1 t2)) "0")
  (check-expr expr-3.10 ((t1 = "abc") (t2 = "xyz"))  ((>= t1 t2)) "0")
  (check-expr expr-3.11 ((t1 = "xyz") (t2 = "abc"))  ((>= t1 t2)) "1")
  (check-expr expr-3.12 ((t1 = "abc") (t2 = "abc"))  ((>= t1 t2)) "1")
  (check-expr expr-3.13 ((t1 = "abc") (t2 = "xyz"))  ((=  t1 t2)) "0")
  (check-expr expr-3.14 ((t1 = "xyz") (t2 = "abc"))  ((=  t1 t2)) "0")
  (check-expr expr-3.15 ((t1 = "abc") (t2 = "abc"))  ((=  t1 t2)) "1")
  (check-expr expr-3.16 ((t1 = "abc") (t2 = "xyz"))  ((== t1 t2)) "0")
  (check-expr expr-3.17 ((t1 = "xyz") (t2 = "abc"))  ((== t1 t2)) "0")
  (check-expr expr-3.18 ((t1 = "abc") (t2 = "abc"))  ((== t1 t2)) "1")
  (check-expr expr-3.19 ((t1 = "abc") (t2 = "xyz"))  ((<> t1 t2)) "1")
  (check-expr expr-3.20 ((t1 = "xyz") (t2 = "abc"))  ((<> t1 t2)) "1")
  (check-expr expr-3.21 ((t1 = "abc") (t2 = "abc"))  ((<> t1 t2)) "0")
  (check-expr expr-3.22 ((t1 = "abc") (t2 = "xyz"))  ((!= t1 t2)) "1")
  (check-expr expr-3.23 ((t1 = "xyz") (t2 = "abc"))  ((!= t1 t2)) "1")
  (check-expr expr-3.24 ((t1 = "abc") (t2 = "abc"))  ((!= t1 t2)) "0")
  (check-expr expr-3.25  ((t1 = NULL) (t2 = "hi"))  ((ISNULL t1))  "1")
  (check-expr expr-3.25b ((t1 = NULL) (t2 = "hi"))  ((ISNULL t1))  "1")
  (check-expr expr-3.26  ((t1 = NULL) (t2 = "hi"))  ((ISNULL t2))  "0")
  (check-expr expr-3.27  ((t1 = NULL) (t2 = "hi"))  ((NOTNULL t1)) "0")
  (check-expr expr-3.28  ((t1 = NULL)  (t2 = "hi"))  ((NOTNULL t2))  "1")
  (check-expr expr-3.28b ((t1 = NULL)  (t2 = "hi"))  ((NOTNULL t2))  "1")
  (check-expr expr-3.29  ((t1 = "xyz") (t2 = "abc")) ((\|\| t1 t2))  "xyzabc")  ; ||  double bar
  (check-expr expr-3.30  ((t1 = NULL)  (t2 = "abc")) ((\|\| t1 t2))  #f)
  (check-expr expr-3.31  ((t1 = "xyz") (t2 = NULL))  ((\|\| t1 t2))                #f)
  (check-expr expr-3.32  ((t1 = "xyz") (t2 = "abc")) ((\|\| t1 (\|\| " hi " t2)))  "xyz hi abc")
  (check-expr expr-3.33  ((t1 = "abc") (t2 = NULL))  ((coalesce (< t1 t2) 99))     "99")  
  (check-expr expr-3.34  ((t1 = "abc") (t2 = NULL))  ((coalesce (< t2 t1) 99))     "99")
  (check-expr expr-3.35  ((t1 = "abc") (t2 = NULL))  ((coalesce (>  t1 t2) 99))     "99")  
  (check-expr expr-3.36  ((t1 = "abc") (t2 = NULL))  ((coalesce (>  t2 t1) 99))     "99")
  (check-expr expr-3.37  ((t1 = "abc") (t2 = NULL))  ((coalesce (<= t1 t2) 99))     "99")  
  (check-expr expr-3.38  ((t1 = "abc") (t2 = NULL))  ((coalesce (<= t2 t1) 99))     "99")
  (check-expr expr-3.39  ((t1 = "abc") (t2 = NULL))  ((coalesce (>= t1 t2) 99))     "99")  
  (check-expr expr-3.40  ((t1 = "abc") (t2 = NULL))  ((coalesce (>= t2 t1) 99))     "99")
  (check-expr expr-3.41  ((t1 = "abc") (t2 = NULL))  ((coalesce (== t1 t2) 99))     "99")  
  (check-expr expr-3.42  ((t1 = "abc") (t2 = NULL))  ((coalesce (== t2 t1) 99))     "99")
  (check-expr expr-3.43  ((t1 = "abc") (t2 = NULL))  ((coalesce (!= t1 t2) 99))     "99")  
  (check-expr expr-3.44  ((t1 = "abc") (t2 = NULL))  ((coalesce (!= t2 t1) 99))     "99")
  
  (check-expr expr-4.1   ((t1 = "abc")    (t2 = "Abc"))  ((< t1 t2))   "0")
  (check-expr expr-4.2   ((t1 = "abc")    (t2 = "Abc"))  ((> t1 t2))   "1")
  (check-expr expr-4.3   ((t1 = "abc")    (t2 = "Bbc"))  ((< t1 t2))   "0")
  (check-expr expr-4.4   ((t1 = "abc")    (t2 = "Bbc"))  ((> t1 t2))   "1")
  (check-expr expr-4.5   ((t1 = "0")      (t2 = "0.0"))  ((== t1 t2))  "0")
  (check-expr expr-4.6   ((t1 = "0.000")  (t2 = "0.0"))  ((== t1 t2))  "0")
  (check-expr expr-4.7   ((t1 = " 0.000") (t2 = "0.0"))  ((== t1 t2))  "0")
  (check-expr expr-4.8   ((t1 = "0.0")    (t2 = "abc"))  ((< t1 t2))   "1")
  (check-expr expr-4.9   ((t1 = "0.0")    (t2 = "abc"))  ((== t1 t2))  "0")
  (check-expr expr-4.10  ((r1 = "0.0")    (r2 = "abc"))  ((> r1 r2))   "0")
  (check-expr expr-4.11  ((r1 = "abc")    (r2 = "Abc"))  ((< r1 r2))   "0")
  (check-expr expr-4.12  ((r1 = "abc")    (r2 = "Abc"))  ((> r1 r2))   "1")
  (check-expr expr-4.13  ((r1 = "abc")    (r2 = "Bbc"))  ((<  r1 r2))  "0")
  (check-expr expr-4.14  ((r1 = "abc")    (r2 = "Bbc"))  ((>  r1 r2))  "1")
  (check-expr expr-4.15  ((r1 = "0")      (r2 = "0.0"))  ((== r1 r2))  "1")
  (check-expr expr-4.16  ((r1 = "0.000")  (r2 = "0.0"))  ((== r1 r2))  "1")
  (check-expr expr-4.17  ((r1 = " 0.000") (r2 = " 0.0")) ((== r1 r2))  "0")
  (check-expr expr-4.18  ((r1 = "0.0")    (r2 = "abc"))  ((<  r1 r2))  "1")
  (check-expr expr-4.19  ((r1 = "0.0")    (r2 = "abc"))  ((== r1 r2))  "0")
  (check-expr expr-4.20  ((r1 = "0.0")    (r2 = "abc"))  ((>  r1 r2))  "0")
  
  
  ;# CSL is true if LIKE is case sensitive and false if not.
  ;# NCSL is the opposite.  Use these variables as the result
  
  (define CSL  "0")
  (define NCSL "1")
  
  
  (check-expr expr-5.1   ((t1 = "abc") (t2 = "xyz"))  ((LIKE t1 t2))  "0")
  (check-expr expr-5.2a  ((t1 = "abc") (t2 = "abc"))  ((LIKE t1 t2))  "1")
  (check-expr expr-5.2b  ((t1 = "abc") (t2 = "ABC"))  ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.2c  ((t1 = "abc") (t2 = "a_c"))  ((LIKE t1 t2))  "1")
  
  (check-expr expr-5.3b  ((t1 = "abc") (t2 = "A_C"))  ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.4   ((t1 = "abc") (t2 = "abc_")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.5a  ((t1 = "abc") (t2 = "a%c"))  ((LIKE t1 t2))  "1")
  (check-expr expr-5.5b  ((t1 = "abc") (t2 = "A%C"))  ((LIKE t1 t2))  NCSL)
  
  (check-expr expr-5.5c  ((t1 = "abdc")     (t2 = "a%c"))  ((LIKE t1 t2))  "1")
  (check-expr expr-5.5d  ((t1 = "ac")       (t2 = "a%c"))  ((LIKE t1 t2))  "1")
  (check-expr expr-5.5e  ((t1 = "ac")       (t2 = "A%C"))  ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.6a  ((t1 = "abxyzzyc") (t2 = "a%c"))  ((LIKE t1 t2))  "1")
  
  (check-expr expr-5.6b  ((t1 = "abxyzzyc")  (t2 = "A%C"))  ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.7a  ((t1 = "abxyzzy")   (t2 = "a%c"))  ((LIKE t1 t2))  "0")
  (check-expr expr-5.7b  ((t1 = "abxyzzy")   (t2 = "A%C"))  ((LIKE t1 t2))  "0")
  (check-expr expr-5.8a  ((t1 = "abxyzzycx") (t2 = "a%c"))  ((LIKE t1 t2))  "0")
  
  (check-expr expr-5.8b  ((t1 = "abxyzzycy")  (t2 = "a%cx")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.8c  ((t1 = "abxyzzycx")  (t2 = "A%C"))  ((LIKE t1 t2))  "0")
  (check-expr expr-5.8d  ((t1 = "abxyzzycy")  (t2 = "A%CX")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.9a  ((t1 = "abc")        (t2 = "a%_c")) ((LIKE t1 t2))  "1")
  
  (check-expr expr-5.9b   ((t1 = "ac")       (t2 = "a%_c")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.9c   ((t1 = "abc")      (t2 = "A%_C")) ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.9d   ((t1 = "ac")       (t2 = "A%_C")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.10a  ((t1 = "abxyzzyc") (t2 = "a%_c")) ((LIKE t1 t2))  "1")
  
  (check-expr expr-5.10b  ((t1 = "abxyzzyc") (t2 = "A%_C")) ((LIKE t1 t2))      NCSL)
  (check-expr expr-5.11   ((t1 = "abc")      (t2 = "xyz"))  ((NOT LIKE t1 t2))  "1")
  (check-expr expr-5.12a  ((t1 = "abc")      (t2 = "abc"))  ((NOT LIKE t1 t2))  "0")
  (check-expr expr-5.12b  ((t1 = "abc")      (t2 = "ABC"))  ((NOT LIKE t1 t2))  CSL)
  
  ; The following tests only work on versions of TCL that support Unicode
  ; {
  (check-expr expr-5.13a  ((t1 = "a\u0080c") (t2 = "a_c")) ((LIKE t1 t2))  "1")
  (check-expr expr-5.13b  ((t1 = "a\u0080c") (t2 = "A_C")) ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.14a  ((t1 = "a\u07FFc") (t2 = "a_c")) ((LIKE t1 t2))  "1")
  (check-expr expr-5.14b  ((t1 = "a\u07FFc") (t2 = "A_C")) ((LIKE t1 t2))  NCSL)
  
  (check-expr expr-5.15a  ((t1 = "a\u0800c") (t2 = "a_c")) ((LIKE t1 t2))  "1")
  (check-expr expr-5.15b  ((t1 = "a\u0800c") (t2 = "A_C")) ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.16a  ((t1 = "a\uFFFFc") (t2 = "a_c")) ((LIKE t1 t2))  "1")
  (check-expr expr-5.16b  ((t1 = "a\uFFFFc") (t2 = "A_C")) ((LIKE t1 t2))  NCSL)
  
  (check-expr expr-5.17  ((t1 = "a\u0080") (t2 = "A__")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.18  ((t1 = "a\u07FF") (t2 = "A__")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.19  ((t1 = "a\u0800") (t2 = "A__")) ((LIKE t1 t2))  "0")
  (check-expr expr-5.20  ((t1 = "a\uFFFF") (t2 = "A__")) ((LIKE t1 t2))  "0")
  
  (check-expr expr-5.21a  ((t1 = "ax\uABCD") (t2 = "a_\uABCD")) ((LIKE t1 t2))  "1")
  (check-expr expr-5.21b  ((t1 = "ax\uABCD") (t2 = "A_\uABCD")) ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.22a  ((t1 = "ax\u1234") (t2 = "a%\u1234")) ((LIKE t1 t2))  "1")
  (check-expr expr-5.22b  ((t1 = "ax\u1234") (t2 = "A%\u1234")) ((LIKE t1 t2))  NCSL)
  
  (check-expr expr-5.23a  ((t1 = "ax\uFEDC")        (t2 = "a_%"))      ((LIKE t1 t2))  "1")
  (check-expr expr-5.23b  ((t1 = "ax\uFEDC")        (t2 = "A_%"))      ((LIKE t1 t2))  NCSL)
  (check-expr expr-5.24a  ((t1 = "ax\uFEDCy\uFEDC") (t2 = "a%\uFEDC")) ((LIKE t1 t2))  "1")
  (check-expr expr-5.24b  ((t1 = "ax\uFEDCy\uFEDC") (t2 = "A%\uFEDC")) ((LIKE t1 t2))  NCSL)
  ; }
  
  (check-expr expr-5.54  ((t1 = "abc") (t2 = NULL)) ((LIKE t1 t2))      #f)
  (check-expr expr-5.55  ((t1 = "abc") (t2 = NULL)) ((NOT LIKE t1 t2))  #f)
  (check-expr expr-5.56  ((t1 = "abc") (t2 = NULL)) ((LIKE t2 t1))      #f)
  (check-expr expr-5.57  ((t1 = "abc") (t2 = NULL)) ((NOT LIKE t2 t1))  #f)
  
  ; LIKE expressions that use ESCAPE characters.
  
  (check-expr expr-5.58a  ((t1 = "abc") (t2 = "a_c"))  ((LIKE t1 t2 ESCAPE "7"))  "1")
  (check-expr expr-5.58b  ((t1 = "abc") (t2 = "A_C"))  ((LIKE t1 t2 ESCAPE "7"))  NCSL)
  (check-expr expr-5.59b  ((t1 = "a_c") (t2 = "a7_c")) ((LIKE t1 t2 ESCAPE "7"))  "1")
  (check-expr expr-5.60a  ((t1 = "a_c") (t2 = "A7_C")) ((LIKE t1 t2 ESCAPE "7"))  NCSL)
  
  (check-expr expr-5.60a  ((t1 = "abc")  (t2 = "a7_c")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  (check-expr expr-5.60b  ((t1 = "abc")  (t2 = "A7_C")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  (check-expr expr-5.61a  ((t1 = "a7Xc") (t2 = "a7_c")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  (check-expr expr-5.61b  ((t1 = "a7Xc") (t2 = "A7_C")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  
  (check-expr expr-5.62a  ((t1 = "abcde") (t2 = "a%e"))  ((LIKE t1 t2 ESCAPE "7"))  "1")
  (check-expr expr-5.62b  ((t1 = "abcde") (t2 = "A%E"))  ((LIKE t1 t2 ESCAPE "7"))  NCSL)
  (check-expr expr-5.63a  ((t1 = "abcde") (t2 = "a7_e")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  (check-expr expr-5.61b  ((t1 = "abcde") (t2 = "A7_E")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  
  (check-expr expr-5.64a  ((t1 = "a7cde") (t2 = "a7%e"))  ((LIKE t1 t2 ESCAPE "7"))  "0")
  (check-expr expr-5.64b  ((t1 = "a7cde") (t2 = "A7%E"))  ((LIKE t1 t2 ESCAPE "7"))  "0")
  (check-expr expr-5.65a  ((t1 = "a7cde") (t2 = "a77%e")) ((LIKE t1 t2 ESCAPE "7"))  "1")
  (check-expr expr-5.65b  ((t1 = "a7cde") (t2 = "A77%E")) ((LIKE t1 t2 ESCAPE "7"))  NCSL)
  
  (check-expr expr-5.66a  ((t1 = "abc7") (t2 = "a%77")) ((LIKE t1 t2 ESCAPE "7"))  "1")
  (check-expr expr-5.66b  ((t1 = "abc7") (t2 = "A%77")) ((LIKE t1 t2 ESCAPE "7"))  NCSL)
  (check-expr expr-5.67a  ((t1 = "abc_") (t2 = "a%7_")) ((LIKE t1 t2 ESCAPE "7"))  "1")
  (check-expr expr-5.67b  ((t1 = "abc_") (t2 = "A%7_")) ((LIKE t1 t2 ESCAPE "7"))  NCSL)
  
  (check-expr expr-5.68a  ((t1 = "abc7") (t2 = "a%7_")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  (check-expr expr-5.68b  ((t1 = "abc7") (t2 = "A%7_")) ((LIKE t1 t2 ESCAPE "7"))  "0")
  
  ; skipped 5.69a to 5.79b
  
  (check-expr expr-6.1  ((t1 = "abc") (t2 = "xyz")) ((GLOB t1 t2))  "0")
  (check-expr expr-6.2  ((t1 = "abc") (t2 = "ABC")) ((GLOB t1 t2))  "0")
  (check-expr expr-6.3  ((t1 = "abc") (t2 = "A?C")) ((GLOB t1 t2))  "0")
  (check-expr expr-6.4  ((t1 = "abc") (t2 = "a?c")) ((GLOB t1 t2))  "1")
  
  (check-expr expr-6.5  ((t1 = "abc")      (t2 = "abc?")) ((GLOB t1 t2))  "0")
  (check-expr expr-6.6  ((t1 = "abc")      (t2 = "A*C"))  ((GLOB t1 t2))  "0")
  (check-expr expr-6.7  ((t1 = "abc")      (t2 = "a*c"))  ((GLOB t1 t2))  "1")
  (check-expr expr-6.8  ((t1 = "abxyzzyc") (t2 = "a*c"))  ((GLOB t1 t2))  "1")
  
  (check-expr expr-6.9   ((t1 = "abxyzzy")   (t2 = "a*c"))  ((GLOB t1 t2))      "0")
  (check-expr expr-6.10  ((t1 = "abxyzzycx") (t2 = "a*c"))  ((GLOB t1 t2))      "0")
  (check-expr expr-6.11  ((t1 = "abc")       (t2 = "xyz"))  ((NOT GLOB t1 t2))  "1")
  (check-expr expr-6.12  ((t1 = "abc")       (t2 = "abc"))  ((NOT GLOB t1 t2))  "0")
  
  (check-expr expr-6.13  ((t1 = "abc") (t2 = "a[bx]c"))    ((GLOB t1 t2))  "1")
  (check-expr expr-6.14  ((t1 = "abc") (t2 = "a[cx]c"))    ((GLOB t1 t2))  "0")
  (check-expr expr-6.15  ((t1 = "abc") (t2 = "a[a-d]c"))   ((GLOB t1 t2))  "1")
  (check-expr expr-6.16  ((t1 = "abc") (t2 = "a[^a-d]c"))  ((GLOB t1 t2))  "0")
  
  (check-expr expr-6.17  ((t1 = "abc") (t2 = "a[A-Dc]c"))  ((GLOB t1 t2))  "0")
  (check-expr expr-6.18  ((t1 = "abc") (t2 = "a[^A-Dc]c")) ((GLOB t1 t2))  "1")
  (check-expr expr-6.19  ((t1 = "abc") (t2 = "a[]b]c"))    ((GLOB t1 t2))  "1")
  (check-expr expr-6.20  ((t1 = "abc") (t2 = "a[^]b]c"))   ((GLOB t1 t2))  "0")
  
  (check-expr expr-6.21a  ((t1 = "abcdefg") (t2 = "a*[de]g"))   ((GLOB t1 t2))  "0")
  (check-expr expr-6.21b  ((t1 = "abcdefg") (t2 = "a*[df]g"))   ((GLOB t1 t2))  "1")
  (check-expr expr-6.21c  ((t1 = "abcdefg") (t2 = "a*[d-h]g"))  ((GLOB t1 t2))  "1")
  (check-expr expr-6.21d  ((t1 = "abcdefg") (t2 = "a*[b-e]g"))  ((GLOB t1 t2))  "0")
  
  (check-expr expr-6.22a  ((t1 = "abcdefg") (t2 = "a*[^de]g"))  ((GLOB t1 t2))  "1")
  (check-expr expr-6.22b  ((t1 = "abcdefg") (t2 = "a*[^def]g")) ((GLOB t1 t2))  "0")
  (check-expr expr-6.23   ((t1 = "abcdefg") (t2 = "a*?g"))      ((GLOB t1 t2))  "1")
  (check-expr expr-6.24   ((t1 = "ac")      (t2 = "a*c"))       ((GLOB t1 t2))  "1")
  
  (check-expr expr-6.25  ((t1 = "ac")  (t2 = "a*?c"))  ((GLOB t1 t2))  "0")
  (check-expr expr-6.26  ((t1 = "a*c") (t2 = "a[*]c")) ((GLOB t1 t2))  "1")
  (check-expr expr-6.27  ((t1 = "a?c") (t2 = "a[?]c")) ((GLOB t1 t2))  "1")
  (check-expr expr-6.28  ((t1 = "a[c") (t2 = "a[[]c")) ((GLOB t1 t2))  "1")
  
  ; skipped 6.26-6.66
  
  (check-expr expr-6.67  ((t1 = "01") (t2 = 1)) ((= t1 t2))                    "0")
  (check-expr expr-6.68  ((t1 = "1")  (t2 = 1)) ((= t1 t2))                    "1")
  (check-expr expr-6.69  ((t1 = "01") (t2 = 1)) ((= (CAST t1 AS INTEGER) t2))  "1")
  
  (check-expr expr-case.1 ((i1 = 1) (i2 = 2)) ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "ne")
  
  (check-expr expr-case.2 ((i1 = 2)    (i2 = 2))    ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "eq")
  (check-expr expr-case.3 ((i1 = NULL) (i2 = 2))    ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "ne")
  (check-expr expr-case.4 ((i1 = 2)    (i2 = NULL)) ((CASE (WHEN (= i1 i2) THEN "eq") (ELSE "ne"))) "ne")
  (check-expr expr-case.5 ((i1 = 2)    (i2 = 2))    ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two") (ELSE "error"))) "two")
  
  (check-expr expr-case.6 ((i1 = 1)) ((CASE i1 (WHEN 1 THEN "one") (WHEN NULL THEN "two") (ELSE "error"))) "one")
  (check-expr expr-case.7 ((i1 = 2)) ((CASE i1 (WHEN 1 THEN "one") (WHEN NULL THEN "two") (ELSE "error"))) "error")
  (check-expr expr-case.8 ((i1 = 3)) ((CASE i1 (WHEN 1 THEN "one") (WHEN NULL THEN "two") (ELSE "error"))) "error")
  (check-expr expr-case.9 ((i1 = 3)) ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two")    (ELSE "error"))) "error")
  
  (check-expr expr-case.10 ((i1 = 3))    ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two") ))            #f)
  (check-expr expr-case.11 ((i1 = NULL)) ((CASE i1 (WHEN 1 THEN "one") (WHEN 2 THEN "two") (ELSE 3)))    "3")
  (check-expr expr-case.12 ((i1 = 1))    ((CASE i1 (WHEN 1 THEN NULL)  (WHEN 2 THEN "two") (ELSE 3)))    #f)
  (check-expr expr-case.13 ((i1 = 7))    ((CASE (WHEN (< i1 5) THEN "low")   (WHEN (< i1 10) THEN "medium")
                                                (WHEN (< i1 15) THEN "high") (ELSE "error")))               "medium")
  
  
  ; (create-expr-test-table2)
  (define-syntax run-sql-expr2 
    (syntax-rules ()
      [(_ sql-str)
       (let ((result '()))
         (exec db (sql sql-str)
               (lambda (colnames rows)
                 (set! result (append result (vector->list rows)))
                 0))
         result)]))
  
  (define-syntax check-expr2
    (syntax-rules ()
      [(check-expr name expr expected-result)
       (check (begin 'name 'expr 
                     (run-sql-expr2 (SELECT (a) FROM test2 WHERE expr ORDER-BY (a))))
              => expected-result)]))
  
  (check-expr2 'expr-7.2 (AND (< a  10) (>  a  8)) '("9"))
  (check-expr2 'expr-7.3 (AND (<= a 10) (>= a  8)) '("8" "9" "10"))
  (check-expr2 'expr-7.4 (AND (>= a  8) (<= a 10)) '("8" "9" "10"))
  (check-expr2 'expr-7.5 (OR  (>= a 20) (<= a  1)) '("1" "20"))
  
  (check-expr2 'expr-7.6 (AND (!= b 4) (<= a  3))                 '("1" "3"))
  (check-expr2 'expr-7.7 (OR  (== b 8) (OR (== b  16) (== b 32))) '("3" "4" "5"))
  (check-expr2 'expr-7.8 (OR  (NOT (<> b 8)) (== b 1024))         '("3" "10"))
  (check-expr2 'expr-7.9 (LIKE b "10%")                           '("10" "20"))
  
  (check-expr2 'expr-7.10 (LIKE b "_4")                           '("6"))
  (check-expr2 'expr-7.11 (GLOB a "1?")                           '("10" "11" "12" "13" "14" "15" "16" "17" "18" "19" ))
  (check-expr2 'expr-7.12 (GLOB b "1*4")                          '("10" "14"))
  (check-expr2 'expr-7.13 (GLOB b "1[456]")                       '("4" ))
  
  (check-expr2 'expr-7.14 (ISNULL a)                              '())
  (check-expr2 'expr-7.15 (AND (NOTNULL a) (< a 3))               '("1" "2"))
  (check-expr2 'expr-7.16 (AND a (< a 3))                         '("1" "2"))
  (check-expr2 'expr-7.17 (NOT a)                                 '())
  
  (check-expr2 'expr-7.18 (OR (== a 11) (AND (> b 1000) (< b 2000))) '("10" "11"))
  (check-expr2 'expr-7.19 (OR (<= a 1)  (>= a 20))                   '("1" "20"))
  (check-expr2 'expr-7.20 (OR (< a 1)   (> a 20))                    '())
  (check-expr2 'expr-7.21 (OR (> a 19)  (< a 1))                     '("20"))
  
  
  ;; Skipped the rest
  
  
  #|
|#
  
  
  (check-report)
  (close db)
  
  )