+ (with-dataset *ds-employees*
+ (clsql:select [first-name] :from (clsql-sys:convert-to-db-default-case "employee" *default-database*)
+ :flatp t :distinct t
+ :field-names nil
+ :result-types nil
+ :order-by [first-name]))
+ ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
+ "Yuri"))
+
+(deftest :fdml/select/26
+ (with-dataset *ds-employees*
+ (clsql:select ["table" first-name] ["table" last-name]
+ :from '([employee "table"] [employee "join"])
+ :where [and [= ["table" first-name]
+ ["join" first-name]]
+ [not [= ["table" emplid]
+ ["join" emplid]]]]
+ :order-by '(["table" last-name])
+ :result-types nil :field-names nil))
+ (("Vladimir" "Lenin") ("Vladimir" "Putin")))
+
+(deftest :fdml/select/27
+ (with-dataset *ds-employees*
+ (mapcar
+ (lambda (f) (truncate (read-from-string f)))
+ (clsql:select [coalesce [managerid] 10] :from [employee] :order-by [emplid]
+ :field-names nil :result-types nil :flatp t)))
+ (10 1 1 1 1 1 1 1 1 1))
+
+(deftest :fdml/select/28
+ (with-dataset *ds-employees*
+ (mapcar
+ (lambda (f) (truncate (read-from-string (car f))))
+ (loop for column in `([*] [emplid]) collect
+ (clsql:select [count column] :from [employee]
+ :flatp t :result-types nil :field-names nil))))
+ (10 10))
+
+(deftest :fdml/select/29
+ (with-dataset *ds-employees*
+ (clsql:select [first-name] [last-name] :from [employee]
+ :result-types nil :field-names nil
+ :order-by '(([first-name] :asc) ([last-name] :desc))))
+ (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
+ ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
+ ("Nikita" "Kruschev") ("Vladimir" "Putin") ("Vladimir" "Lenin")
+ ("Yuri" "Andropov")))
+
+(deftest :fdml/select/30
+ (with-dataset *ds-employees*
+ (clsql:select [first-name] [last-name] :from [employee]
+ :result-types nil :field-names nil
+ :order-by '(([first-name] :asc) ([last-name] :asc))))
+ (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
+ ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
+ ("Nikita" "Kruschev") ("Vladimir" "Lenin") ("Vladimir" "Putin")
+ ("Yuri" "Andropov")))
+
+(deftest :fdml/select/31
+ (with-dataset *ds-employees*
+ (clsql:select [last-name] :from [employee]
+ :set-operation [union [select [first-name] :from [employee]
+ :order-by [last-name]]]
+ :flatp t
+ :result-types nil
+ :field-names nil))
+ ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
+ "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
+ "Trotsky" "Vladimir" "Yeltsin" "Yuri"))
+
+(deftest :fdml/select/32
+ (with-dataset *ds-employees*
+ (clsql:select [emplid] :from [employee]
+ :where [= [emplid] [any [select [companyid] :from [company]]]]
+ :flatp t :result-types nil :field-names nil))
+ ("1"))
+
+(deftest :fdml/select/33
+ (with-dataset *ds-employees*
+ (clsql:select [last-name] :from [employee]
+ :where [> [emplid] [all [select [groupid] :from [employee]]]]
+ :order-by [last-name]
+ :flatp t :result-types nil :field-names nil))
+ ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Putin" "Stalin"
+ "Trotsky" "Yeltsin"))
+
+(deftest :fdml/select/34
+ (with-dataset *ds-employees*
+ (loop for x from 1 below 5
+ collect
+ (car
+ (clsql:select [last-name] :from [employee]
+ :where [= [emplid] x]
+ :flatp t :result-types nil :field-names nil))))
+ ("Lenin" "Stalin" "Trotsky" "Kruschev"))
+
+;; test escaping of single quotes
+(deftest :fdml/select/35
+ (with-dataset *ds-fddl*
+ (first (clsql:select "What's up doc?" :from [alpha] :flatp t :field-names nil)))
+ "What's up doc?")
+
+;; test proper treatment of backslash (depending on backend)
+(deftest :fdml/select/36
+ (with-dataset *ds-fddl*
+ (first (clsql:select "foo\\bar\\baz" :from [alpha] :flatp t :field-names nil)))
+ "foo\\bar\\baz")
+
+(deftest :fdml/select/37
+ (with-dataset *ds-employees*
+ (clsql:select [emplid] :from [employee]
+ :order-by [emplid]
+ :limit 5
+ :field-names nil
+ :flatp t))
+ (1 2 3 4 5))
+
+(deftest :fdml/select/38
+ (with-dataset *ds-employees*
+ (clsql:select [emplid] :from [employee]
+ :order-by [emplid]
+ :limit 5
+ :offset 3
+ :field-names nil
+ :flatp t))
+ (4 5 6 7 8))