+(deftest :fdml/select/13
+ (with-dataset *ds-employees*
+ (multiple-value-bind (results field-names)
+ (clsql:select [emplid] [last-name] :from [employee]
+ :where [= [emplid] 1])
+ (values results (mapcar #'string-downcase field-names))))
+ ((1 "Lenin"))
+ ("emplid" "last_name"))
+
+(deftest :fdml/select/14
+ (with-dataset *ds-employees*
+ (floatp (car (clsql:select [height] :from [employee] :where [= [emplid] 1]
+ :flatp t))))
+ t)
+
+(deftest :fdml/select/15
+ (with-dataset *ds-employees*
+ (multiple-value-bind (rows field-names)
+ (clsql:select [addressid] [street-number] [street-name] [city_field] [zip]
+ :from [addr]
+ :where [= 1 [addressid]])
+ (values
+ rows
+ (mapcar #'string-downcase field-names))))
+ ((1 10 "Park Place" "Leningrad" 123))
+ ("addressid" "street_number" "street_name" "city_field" "zip"))
+
+(deftest :fdml/select/16
+ (with-dataset *ds-employees*
+ (clsql:select [emplid] :from [employee] :where [= 1 [emplid]]
+ :field-names nil))
+ ((1)))
+
+(deftest :fdml/select/17
+ (with-dataset *ds-employees*
+ (clsql:select [emplid] [last-name] :from [employee] :where [= 1 [emplid]]
+ :field-names nil))
+ ((1 "Lenin")))
+
+(deftest :fdml/select/18
+ (with-dataset *ds-employees*
+ (clsql:select [emplid :string] [last-name] :from [employee] :where [= 1 [emplid]]
+ :field-names nil))
+ (("1" "Lenin")))
+
+(deftest :fdml/select/19
+ (with-dataset *ds-employees*
+ (mapcar
+ #'%get-int
+ (clsql:select [emplid] :from [employee] :order-by [emplid]
+ :where [between [* [emplid] 10] [* 5 10] [* 10 10]]
+ :field-names nil :result-types nil :flatp t)))
+ (5 6 7 8 9 10))
+
+(deftest :fdml/select/20
+ (with-dataset *ds-employees*
+ (mapcar #'%get-int
+ (clsql:select [emplid] :from [employee] :order-by [emplid]
+ :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
+ :field-names nil :result-types nil :flatp t)))
+ (1 2 3 4))
+
+(deftest :fdml/select/21
+ (with-dataset *ds-employees*
+ (clsql:select [substring [first-name] 1 4] :from [employee]
+ :flatp t :order-by [emplid] :field-names nil))
+ ("Vlad" "Jose" "Leon" "Niki" "Leon" "Yuri" "Kons" "Mikh" "Bori" "Vlad"))
+
+(deftest :fdml/select/22
+ (with-dataset *ds-employees*
+ (case *test-database-underlying-type*
+ (:mssql (clsql:select [+ [first-name] " " [last-name]] :from [employee]
+ :flatp t :order-by [emplid] :field-names nil))
+ (t (clsql:select [|| [first-name] " " [last-name]] :from [employee]
+ :flatp t :order-by [emplid] :field-names nil))))
+ ("Vladimir Lenin" "Josef Stalin" "Leon Trotsky" "Nikita Kruschev"
+ "Leonid Brezhnev" "Yuri Andropov" "Konstantin Chernenko" "Mikhail Gorbachev"
+ "Boris Yeltsin" "Vladimir Putin"))
+
+(deftest :fdml/select/23
+ (with-dataset *ds-employees*
+ (mapcar #'%get-int
+ (clsql:select [emplid] :from [employee] :where [in [emplid] '(1 2 3 4)]
+ :flatp t :order-by [emplid] :field-names nil
+ :result-types nil)))
+ (1 2 3 4))
+
+(deftest :fdml/select/24
+ (with-dataset *ds-employees*
+ (clsql:select [distinct [first-name]] :from [employee] :flatp t
+ :order-by [first-name] :field-names nil :result-types nil))
+ ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
+ "Yuri"))
+
+(deftest :fdml/select/25
+ (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
+ #'%get-int
+ (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*
+ (loop for column in `([*] [emplid])
+ collect
+ (%get-int
+ (car
+ (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*
+ (mapcar
+ #'%get-int
+ (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))