1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("SQL Server functions tests", "[sqlserver]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA Sales;"));
13
14 REQUIRE_NO_FAIL(con.Query(
15 "CREATE TABLE Sales.SalesPerson( BusinessEntityID int NOT NULL, TerritoryID int, SalesQuota decimal(22,4), "
16 "Bonus decimal(22,4) NOT NULL, CommissionPct decimal(10,4) NOT NULL, SalesYTD decimal(22,4) NOT NULL, "
17 "SalesLastYear decimal(22,4) NOT NULL , rowguid string , ModifiedDate datetime NOT NULL );"));
18 REQUIRE_NO_FAIL(con.Query("COPY Sales.SalesPerson FROM 'test/sqlserver/data/SalesPerson.csv.gz' DELIMITER '\t';"));
19
20 REQUIRE_NO_FAIL(con.Query(" CREATE TABLE Sales.SalesTaxRate( SalesTaxRateID int NOT NULL, StateProvinceID int NOT "
21 "NULL, TaxType tinyint NOT NULL, TaxRate decimal(10,4) NOT NULL , Name string NOT NULL, "
22 "rowguid string , ModifiedDate datetime NOT NULL ); "));
23 REQUIRE_NO_FAIL(
24 con.Query("COPY Sales.SalesTaxRate FROM 'test/sqlserver/data/SalesTaxRate.csv.gz' DELIMITER '\t';"));
25
26 REQUIRE_NO_FAIL(con.Query(
27 "CREATE TABLE Sales.SalesPersonQuotaHistory( BusinessEntityID int NOT NULL, QuotaDate datetime NOT NULL, "
28 "SalesQuota decimal(22,4) NOT NULL, rowguid string , ModifiedDate datetime NOT NULL);"));
29 REQUIRE_NO_FAIL(con.Query("COPY Sales.SalesPersonQuotaHistory FROM "
30 "'test/sqlserver/data/SalesPersonQuotaHistory.csv.gz' DELIMITER '\t';"));
31
32 REQUIRE_NO_FAIL(
33 con.Query("CREATE TABLE Sales.SalesTerritory( TerritoryID int NOT NULL, Name string NOT NULL, "
34 "CountryRegionCode string NOT NULL, Group_ string NOT NULL, SalesYTD decimal(22,4) NOT NULL , "
35 "SalesLastYear decimal(22,4) NOT NULL , CostYTD decimal(22,4) NOT NULL , CostLastYear decimal(22,4) "
36 "NOT NULL , rowguid string , ModifiedDate datetime NOT NULL );"));
37 REQUIRE_NO_FAIL(
38 con.Query("COPY Sales.SalesTerritory FROM 'test/sqlserver/data/SalesTerritory.csv.gz' DELIMITER '\t';"));
39
40 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA HumanResources;"));
41
42 REQUIRE_NO_FAIL(con.Query(
43 "CREATE TABLE HumanResources.Employee( BusinessEntityID int NOT NULL, NationalIDNumber string NOT NULL, "
44 "LoginID string NOT NULL, OrganizationNode VARCHAR, OrganizationLevel integer not null, JobTitle string "
45 "NOT NULL, BirthDate date NOT NULL, MaritalStatus string NOT NULL, Gender string NOT NULL, HireDate date NOT "
46 "NULL, SalariedFlag string NOT NULL , VacationHours smallint NOT NULL , SickLeaveHours smallint NOT NULL, "
47 "CurrentFlag string NOT NULL , rowguid string , ModifiedDate datetime NOT NULL); "));
48 REQUIRE_NO_FAIL(
49 con.Query("COPY HumanResources.Employee FROM 'test/sqlserver/data/Employee.csv.gz' DELIMITER '\t';"));
50
51 REQUIRE_NO_FAIL(con.Query(
52 "CREATE TABLE HumanResources.EmployeePayHistory( BusinessEntityID int NOT NULL, RateChangeDate datetime NOT "
53 "NULL, Rate decimal(22,4) NOT NULL, PayFrequency tinyint NOT NULL, ModifiedDate datetime NOT NULL); "));
54 REQUIRE_NO_FAIL(con.Query("COPY HumanResources.EmployeePayHistory FROM "
55 "'test/sqlserver/data/EmployeePayHistory.csv.gz' DELIMITER '\t';"));
56
57 REQUIRE_NO_FAIL(con.Query("CREATE TABLE HumanResources.EmployeeDepartmentHistory( BusinessEntityID int NOT NULL, "
58 "DepartmentID smallint NOT NULL, ShiftID tinyint NOT NULL, StartDate date NOT NULL, "
59 "EndDate date, ModifiedDate datetime NOT NULL ); "));
60 REQUIRE_NO_FAIL(con.Query("COPY HumanResources.EmployeeDepartmentHistory FROM "
61 "'test/sqlserver/data/EmployeeDepartmentHistory.csv.gz' DELIMITER '\t';"));
62
63 REQUIRE_NO_FAIL(con.Query("CREATE TABLE HumanResources.Department( DepartmentID smallint NOT NULL, Name string NOT "
64 "NULL, GroupName string NOT NULL, ModifiedDate datetime NOT NULL ); "));
65 REQUIRE_NO_FAIL(
66 con.Query("COPY HumanResources.Department FROM 'test/sqlserver/data/Department.csv.gz' DELIMITER '\t';"));
67
68 REQUIRE_NO_FAIL(
69 con.Query("CREATE TABLE HumanResources.Shift( ShiftID tinyint NOT NULL, Name string NOT NULL, StartTime string "
70 "NOT NULL, EndTime string NOT NULL, ModifiedDate datetime NOT NULL ); "));
71 REQUIRE_NO_FAIL(con.Query("COPY HumanResources.Shift FROM 'test/sqlserver/data/Shift.csv.gz' DELIMITER '\t';"));
72
73 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA Person;"));
74
75 REQUIRE_NO_FAIL(
76 con.Query("CREATE TABLE Person.Person( BusinessEntityID int NOT NULL, PersonType string NOT NULL, NameStyle "
77 "string NOT NULL, Title string , FirstName string NOT NULL, MiddleName string, LastName string NOT "
78 "NULL, Suffix string, EmailPromotion int NOT NULL, AdditionalContactInfo string, Demographics "
79 "string, rowguid string, ModifiedDate datetime NOT NULL); "));
80 REQUIRE_NO_FAIL(
81 con.Query("COPY Person.Person FROM 'test/sqlserver/data/Person.csv.gz' (DELIMITER '|', QUOTE '*');"));
82
83 REQUIRE_NO_FAIL(
84 con.Query("CREATE TABLE Person.BusinessEntityAddress( BusinessEntityID int NOT NULL, AddressID int NOT NULL, "
85 "AddressTypeID int NOT NULL, rowguid string, ModifiedDate datetime NOT NULL ) ; "));
86 REQUIRE_NO_FAIL(con.Query("COPY Person.BusinessEntityAddress FROM "
87 "'test/sqlserver/data/BusinessEntityAddress.csv.gz' DELIMITER '|';"));
88
89 REQUIRE_NO_FAIL(
90 con.Query("CREATE TABLE Person.Address( AddressID int NOT NULL, AddressLine1 string NOT NULL, AddressLine2 "
91 "string, City string NOT NULL, StateProvinceID int NOT NULL, PostalCode string NOT NULL, "
92 "SpatialLocation string, rowguid string, ModifiedDate datetime NOT NULL ); "));
93 REQUIRE_NO_FAIL(con.Query("COPY Person.Address FROM 'test/sqlserver/data/Address.csv.gz' DELIMITER '\t';"));
94
95 REQUIRE_NO_FAIL(
96 con.Query("CREATE TABLE Person.StateProvince( StateProvinceID int NOT NULL, StateProvinceCode string NOT NULL, "
97 "CountryRegionCode string NOT NULL, IsOnlyStateProvinceFlag string NOT NULL , Name string NOT NULL, "
98 "TerritoryID int NOT NULL, rowguid string , ModifiedDate datetime NOT NULL); "));
99 REQUIRE_NO_FAIL(
100 con.Query("COPY Person.StateProvince FROM 'test/sqlserver/data/StateProvince.csv.gz' DELIMITER '\t';"));
101
102 REQUIRE_NO_FAIL(con.Query("CREATE TABLE Person.CountryRegion( CountryRegionCode string NOT NULL, Name string NOT "
103 "NULL, ModifiedDate datetime NOT NULL ); "));
104 REQUIRE_NO_FAIL(
105 con.Query("COPY Person.CountryRegion FROM 'test/sqlserver/data/CountryRegion.csv.gz' DELIMITER '\t';"));
106
107 REQUIRE_NO_FAIL(
108 con.Query("CREATE TABLE Person.EmailAddress( BusinessEntityID int NOT NULL, EmailAddressID int NOT NULL, "
109 "EmailAddress string, rowguid string , ModifiedDate datetime NOT NULL ); "));
110 REQUIRE_NO_FAIL(
111 con.Query("COPY Person.EmailAddress FROM 'test/sqlserver/data/EmailAddress.csv.gz' DELIMITER '|';"));
112
113 REQUIRE_NO_FAIL(con.Query("CREATE TABLE Person.PersonPhone( BusinessEntityID int NOT NULL, PhoneNumber string NOT "
114 "NULL, PhoneNumberTypeID int NOT NULL, ModifiedDate datetime NOT NULL ); "));
115 REQUIRE_NO_FAIL(con.Query("COPY Person.PersonPhone FROM 'test/sqlserver/data/PersonPhone.csv.gz' DELIMITER '|';"));
116
117 REQUIRE_NO_FAIL(con.Query("CREATE TABLE Person.PhoneNumberType( PhoneNumberTypeID int NOT NULL, Name string NOT "
118 "NULL, ModifiedDate datetime NOT NULL ); "));
119 REQUIRE_NO_FAIL(
120 con.Query("COPY Person.PhoneNumberType FROM 'test/sqlserver/data/PhoneNumberType.csv.gz' DELIMITER '|';"));
121
122 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA Production;"));
123
124 REQUIRE_NO_FAIL(con.Query(
125 "CREATE TABLE Production.Product( ProductID int NOT NULL, Name string NOT NULL, ProductNumber string NOT NULL, "
126 "MakeFlag string NOT NULL , FinishedGoodsFlag string NOT NULL , Color string, SafetyStockLevel smallint NOT "
127 "NULL, ReorderPoint smallint NOT NULL, StandardCost decimal(22,4) NOT NULL, ListPrice decimal(22,4) NOT NULL, "
128 "Size string, SizeUnitMeasureCode string, WeightUnitMeasureCode string, Weight decimal(8, 2), "
129 "DaysToManufacture int NOT NULL, ProductLine string, Class string, Style string, ProductSubcategoryID int, "
130 "ProductModelID int, SellStartDate datetime NOT NULL, SellEndDate datetime, DiscontinuedDate datetime, rowguid "
131 "string , ModifiedDate datetime NOT NULL );"));
132 REQUIRE_NO_FAIL(con.Query(" COPY Production.Product FROM 'test/sqlserver/data/Product.csv.gz' DELIMITER '\t';"));
133
134 REQUIRE_NO_FAIL(con.Query(" CREATE TABLE Production.ProductInventory( ProductID int NOT NULL, LocationID smallint "
135 "NOT NULL, Shelf string NOT NULL, Bin tinyint NOT NULL, Quantity smallint NOT NULL , "
136 "rowguid string , ModifiedDate datetime NOT NULL ); "));
137 REQUIRE_NO_FAIL(con.Query(
138 " COPY Production.ProductInventory FROM 'test/sqlserver/data/ProductInventory.csv.gz' DELIMITER '\t';"));
139
140 // views
141
142 REQUIRE_NO_FAIL(con.Query(
143 "CREATE VIEW Sales.vSalesPerson AS SELECT s.BusinessEntityID ,p.Title ,p.FirstName ,p.MiddleName ,p.LastName "
144 ",p.Suffix ,e.JobTitle ,pp.PhoneNumber ,pnt.Name AS PhoneNumberType ,ea.EmailAddress ,p.EmailPromotion "
145 ",a.AddressLine1 ,a.AddressLine2 ,a.City ,sp.Name AS StateProvinceName ,a.PostalCode ,cr.Name AS "
146 "CountryRegionName ,st.Name AS TerritoryName ,st.Group_ AS TerritoryGroup ,s.SalesQuota ,s.SalesYTD "
147 ",s.SalesLastYear FROM Sales.SalesPerson s INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = "
148 "s.BusinessEntityID INNER JOIN Person.Person p ON p.BusinessEntityID = s.BusinessEntityID INNER JOIN "
149 "Person.BusinessEntityAddress bea ON bea.BusinessEntityID = s.BusinessEntityID INNER JOIN Person.Address a ON "
150 "a.AddressID = bea.AddressID INNER JOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID "
151 "INNER JOIN Person.CountryRegion cr ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN "
152 "Sales.SalesTerritory st ON st.TerritoryID = s.TerritoryID LEFT OUTER JOIN Person.EmailAddress ea ON "
153 "ea.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN Person.PersonPhone pp ON pp.BusinessEntityID = "
154 "p.BusinessEntityID LEFT OUTER JOIN Person.PhoneNumberType pnt ON pnt.PhoneNumberTypeID = "
155 "pp.PhoneNumberTypeID; "));
156
157 REQUIRE_NO_FAIL(
158 con.Query("CREATE VIEW HumanResources.vEmployeeDepartmentHistory AS SELECT e.BusinessEntityID ,p.Title "
159 ",p.FirstName ,p.MiddleName ,p.LastName ,p.Suffix ,s.Name AS Shift ,d.Name AS Department "
160 ",d.GroupName ,edh.StartDate ,edh.EndDate FROM HumanResources.Employee e INNER JOIN Person.Person p "
161 "ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN HumanResources.EmployeeDepartmentHistory edh "
162 "ON e.BusinessEntityID = edh.BusinessEntityID INNER JOIN HumanResources.Department d ON "
163 "edh.DepartmentID = d.DepartmentID INNER JOIN HumanResources.Shift s ON s.ShiftID = edh.ShiftID; "));
164
165 // code below generated using scrape.py
166
167 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql?view=sql-server-2017
168
169 result = con.Query("SELECT AVG(VacationHours)AS a, SUM(SickLeaveHours) AS b "
170 " FROM HumanResources.Employee WHERE JobTitle LIKE 'Vice President%';");
171 REQUIRE(result->success);
172 REQUIRE(result->types.size() == 2);
173 REQUIRE(CHECK_COLUMN(result, 0, {25}));
174 REQUIRE(CHECK_COLUMN(result, 1, {97}));
175
176 result = con.Query("SELECT TerritoryID, AVG(Bonus)as a, SUM(SalesYTD) as b FROM "
177 "Sales.SalesPerson GROUP BY TerritoryID order by TerritoryID; ");
178 REQUIRE(result->success);
179 REQUIRE(result->types.size() == 3);
180 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10}));
181 REQUIRE(CHECK_COLUMN(
182 result, 1, {0.00, 4133.3333, 4100.00, 2500.00, 2775.00, 6700.00, 2750.00, 985.00, 75.00, 5650.00, 5150.00}));
183 REQUIRE(CHECK_COLUMN(result, 2,
184 {1252127.9471, 4502152.2674, 3763178.1787, 3189418.3662, 6709904.1666, 2315185.611,
185 4058260.1825, 3121616.3202, 1827066.7118, 1421810.9242, 4116871.2277}));
186
187 result = con.Query("SELECT AVG(DISTINCT ListPrice) FROM Production.Product;");
188 REQUIRE(result->success);
189 REQUIRE(result->types.size() == 1);
190 // FIXME mismatch, probably down to missing DECIMAL type
191 // REQUIRE(CHECK_COLUMN(result, 0, {437.4042}));
192
193 result = con.Query("SELECT AVG(ListPrice) FROM Production.Product;");
194 REQUIRE(result->success);
195 REQUIRE(result->types.size() == 1);
196 REQUIRE(CHECK_COLUMN(result, 0, {438.6662}));
197
198 result =
199 con.Query("SELECT BusinessEntityID, TerritoryID ,YEAR(ModifiedDate) AS SalesYear "
200 ",SalesYTD ,AVG(SalesYTD) OVER (PARTITION BY "
201 "TerritoryID ORDER BY YEAR(ModifiedDate) ) AS MovingAvg ,SUM(SalesYTD) "
202 "OVER (PARTITION BY TerritoryID ORDER BY YEAR(ModifiedDate) ) AS CumulativeTotal FROM "
203 "Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY TerritoryID, SalesYear;");
204 REQUIRE(result->success);
205 REQUIRE(result->types.size() == 6);
206 // TODO needs double checking, probably using different adventureworks db
207 // REQUIRE(CHECK_COLUMN(result, 0, {274, 287, 285, 283, 280, 284, 275, 277, 276, 281}));
208 // REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), 1, 1, 1, 2, 3, 4, 4}));
209 // REQUIRE(CHECK_COLUMN(result, 2, {2005, 2006, 2007, 2005, 2005, 2006, 2005, 2005, 2005, 2005}));
210 // REQUIRE(CHECK_COLUMN(result, 3,
211 // {559697.56, 519905.93, 172524.45, 1573012.94, 1352577.13, 1576562.20, 3763178.18,
212 // 3189418.37, 4251368.55, 2458535.62})); REQUIRE(CHECK_COLUMN(result, 4, {559697.56,
213 // 539801.75, 417375.98, 1462795.04, 1462795.04, 1500717.42, 3763178.18, 3189418.37, 3354952.08, 3354952.08}));
214 // REQUIRE(CHECK_COLUMN(result, 5,
215 // {559697.56, 1079603.50, 1252127.95, 2925590.07, 2925590.07, 4502152.27, 3763178.18,
216 // 3189418.37, 6709904.17, 6709904.17}));
217 //
218
219 result = con.Query("SELECT BusinessEntityID, TerritoryID ,YEAR(ModifiedDate) AS SalesYear ,SalesYTD "
220 " ,AVG(SalesYTD) OVER (ORDER BY YEAR(ModifiedDate) ) AS MovingAvg "
221 ",SUM(SalesYTD) OVER (ORDER BY YEAR(ModifiedDate) ) AS CumulativeTotal FROM "
222 "Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY SalesYear;");
223 REQUIRE(result->success);
224 REQUIRE(result->types.size() == 6);
225 // TODO needs double checking, probably using different adventureworks db
226 //
227 // REQUIRE(CHECK_COLUMN(result, 0, {274, 275, 276, 277, 280, 281, 283, 284, 287, 285}));
228 // REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 4, 3, 1, 4, 1, 1, Value(), Value()}));
229 // REQUIRE(CHECK_COLUMN(result, 2, {2005, 2005, 2005, 2005, 2005, 2005, 2005, 2006, 2006, 2007}));
230 // REQUIRE(CHECK_COLUMN(result, 3,
231 // {559697.56, 3763178.18, 4251368.55, 3189418.37, 1352577.13, 2458535.62, 1573012.94,
232 // 1576562.20, 519905.93, 172524.45})); REQUIRE(CHECK_COLUMN(result, 4, {2449684.05,
233 // 2449684.05, 2449684.05, 2449684.05, 2449684.05, 2449684.05, 2449684.05, 2138250.72,
234 // 2138250.72, 1941678.09})); REQUIRE(CHECK_COLUMN(result, 5, {17147788.35, 17147788.35,
235 // 17147788.35, 17147788.35,
236 // 17147788.35, 17147788.35, 17147788.35, 19244256.47, 19244256.47, 19416780.93}));
237
238 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-2017
239
240 // TODO report bug, original query has "Title"
241 // TODO casting issue
242 result = con.Query("SELECT COUNT(DISTINCT JobTitle) FROM HumanResources.Employee; ");
243 // REQUIRE(result->success);
244 // REQUIRE(result->types.size() == 1);
245 // REQUIRE(CHECK_COLUMN(result, 0, {67}));
246
247 result = con.Query("SELECT COUNT(*) FROM HumanResources.Employee; ");
248 REQUIRE(result->success);
249 REQUIRE(result->types.size() == 1);
250 REQUIRE(CHECK_COLUMN(result, 0, {290}));
251
252 result = con.Query("SELECT COUNT(*), AVG(Bonus) FROM Sales.SalesPerson WHERE SalesQuota > 25000; ");
253 REQUIRE(result->success);
254 REQUIRE(result->types.size() == 2);
255 REQUIRE(CHECK_COLUMN(result, 0, {14}));
256 REQUIRE(CHECK_COLUMN(result, 1, {3472.1428}));
257
258 result =
259 con.Query("SELECT DISTINCT Name , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary , MAX(Rate) OVER "
260 "(PARTITION BY edh.DepartmentID) AS MaxSalary , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS "
261 "AvgSalary ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept "
262 "FROM HumanResources.EmployeePayHistory AS eph JOIN HumanResources.EmployeeDepartmentHistory AS edh "
263 "ON eph.BusinessEntityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON "
264 "d.DepartmentID = edh.DepartmentID WHERE edh.EndDate IS NULL ORDER BY Name;");
265 REQUIRE(result->success);
266 REQUIRE(result->types.size() == 5);
267 REQUIRE(CHECK_COLUMN(result, 0,
268 {"Document Control", "Engineering", "Executive", "Facilities and Maintenance", "Finance",
269 "Human Resources", "Information Services", "Marketing", "Production", "Production Control",
270 "Purchasing", "Quality Assurance", "Research and Development", "Sales",
271 "Shipping and Receiving", "Tool Design"}));
272 REQUIRE(CHECK_COLUMN(result, 1,
273 {10.25, 32.6923, 39.06, 9.25, 13.4615, 13.9423, 27.4038, 13.4615, 6.50, 8.62, 9.86, 10.5769,
274 40.8654, 23.0769, 9.00, 8.62}));
275 REQUIRE(CHECK_COLUMN(result, 2,
276 {17.7885, 63.4615, 125.50, 24.0385, 43.2692, 27.1394, 50.4808, 37.50, 84.1346, 24.5192, 30.00,
277 28.8462, 50.4808, 72.1154, 19.2308, 29.8462}));
278 // TODO numeric drift
279 // REQUIRE(CHECK_COLUMN(result, 3,
280 // {14.3884, 40.1442, 68.3034, 13.0316, 23.935, 18.0248, 34.1586, 18.4318, 13.5537, 16.7746,
281 // 18.0202, 15.4647, 43.6731, 29.9719, 10.8718, 23.5054}));
282 REQUIRE(CHECK_COLUMN(result, 4, {5, 6, 4, 7, 10, 6, 10, 11, 195, 8, 14, 6, 4, 18, 6, 6}));
283
284 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-2017
285
286 result = con.Query("SELECT MAX(TaxRate) FROM Sales.SalesTaxRate; ");
287 REQUIRE(result->success);
288 REQUIRE(result->types.size() == 1);
289 REQUIRE(CHECK_COLUMN(result, 0, {19.60}));
290
291 result =
292 con.Query("SELECT DISTINCT Name , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary , MAX(Rate) OVER "
293 "(PARTITION BY edh.DepartmentID) AS MaxSalary , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS "
294 "AvgSalary ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept "
295 "FROM HumanResources.EmployeePayHistory AS eph JOIN HumanResources.EmployeeDepartmentHistory AS edh "
296 "ON eph.BusinessEntityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON "
297 "d.DepartmentID = edh.DepartmentID WHERE edh.EndDate IS NULL ORDER BY Name;");
298 REQUIRE(result->success);
299 REQUIRE(result->types.size() == 5);
300 REQUIRE(CHECK_COLUMN(result, 0,
301 {"Document Control", "Engineering", "Executive", "Facilities and Maintenance", "Finance",
302 "Human Resources", "Information Services", "Marketing", "Production", "Production Control",
303 "Purchasing", "Quality Assurance", "Research and Development", "Sales",
304 "Shipping and Receiving", "Tool Design"}));
305 REQUIRE(CHECK_COLUMN(result, 1,
306 {10.25, 32.6923, 39.06, 9.25, 13.4615, 13.9423, 27.4038, 13.4615, 6.50, 8.62, 9.86, 10.5769,
307 40.8654, 23.0769, 9.00, 8.62}));
308 REQUIRE(CHECK_COLUMN(result, 2,
309 {17.7885, 63.4615, 125.50, 24.0385, 43.2692, 27.1394, 50.4808, 37.50, 84.1346, 24.5192, 30.00,
310 28.8462, 50.4808, 72.1154, 19.2308, 29.8462}));
311 // TODO numeric drift
312 // REQUIRE(CHECK_COLUMN(result, 3,
313 // {14.3884, 40.1442, 68.3034, 13.0316, 23.935, 18.0248, 34.1586, 18.4318, 13.5537, 16.7746,
314 // 18.0202, 15.4647, 43.6731, 29.9719, 10.8718, 23.5054}));
315 REQUIRE(CHECK_COLUMN(result, 4, {5, 6, 4, 7, 10, 6, 10, 11, 195, 8, 14, 6, 4, 18, 6, 6}));
316
317 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/min-transact-sql?view=sql-server-2017
318
319 result = con.Query("SELECT MIN(TaxRate) FROM Sales.SalesTaxRate; ");
320 REQUIRE(result->success);
321 REQUIRE(result->types.size() == 1);
322 REQUIRE(CHECK_COLUMN(result, 0, {5.00}));
323
324 result =
325 con.Query("SELECT DISTINCT Name , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary , MAX(Rate) OVER "
326 "(PARTITION BY edh.DepartmentID) AS MaxSalary , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS "
327 "AvgSalary ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept "
328 "FROM HumanResources.EmployeePayHistory AS eph JOIN HumanResources.EmployeeDepartmentHistory AS edh "
329 "ON eph.BusinessEntityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON "
330 "d.DepartmentID = edh.DepartmentID WHERE edh.EndDate IS NULL ORDER BY Name;");
331 REQUIRE(result->success);
332 REQUIRE(result->types.size() == 5);
333 REQUIRE(CHECK_COLUMN(result, 0,
334 {"Document Control", "Engineering", "Executive", "Facilities and Maintenance", "Finance",
335 "Human Resources", "Information Services", "Marketing", "Production", "Production Control",
336 "Purchasing", "Quality Assurance", "Research and Development", "Sales",
337 "Shipping and Receiving", "Tool Design"}));
338 REQUIRE(CHECK_COLUMN(result, 1,
339 {10.25, 32.6923, 39.06, 9.25, 13.4615, 13.9423, 27.4038, 13.4615, 6.50, 8.62, 9.86, 10.5769,
340 40.8654, 23.0769, 9.00, 8.62}));
341 REQUIRE(CHECK_COLUMN(result, 2,
342 {17.7885, 63.4615, 125.50, 24.0385, 43.2692, 27.1394, 50.4808, 37.50, 84.1346, 24.5192, 30.00,
343 28.8462, 50.4808, 72.1154, 19.2308, 29.8462}));
344 // TODO numeric drift
345 // REQUIRE(CHECK_COLUMN(result, 3,
346 // {14.3884, 40.1442, 68.3034, 13.0316, 23.935, 18.0248, 34.1586, 18.4318, 13.5537, 16.7746,
347 // 18.0202, 15.4647, 43.6731, 29.9719, 10.8718, 23.5054}));
348 REQUIRE(CHECK_COLUMN(result, 4, {5, 6, 4, 7, 10, 6, 10, 11, 195, 8, 14, 6, 4, 18, 6, 6}));
349
350 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/stdev-transact-sql?view=sql-server-2017
351
352 // TODO unclear what the result is
353 // result = con.Query("SELECT STDEV(Bonus) FROM Sales.SalesPerson; ");
354 // REQUIRE(result->success);
355 // REQUIRE(result->types.size() == 2);
356 // REQUIRE(CHECK_COLUMN(result, 0, {398974.27}));
357 // REQUIRE(CHECK_COLUMN(result, 1, {398450.57}));
358
359 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-2017
360
361 result = con.Query("SELECT Color, SUM(ListPrice), SUM(StandardCost) FROM Production.Product WHERE Color IS NOT "
362 "NULL AND ListPrice != 0.00 AND Name LIKE 'Mountain%' GROUP BY Color ORDER BY Color; ");
363 REQUIRE(result->success);
364 REQUIRE(result->types.size() == 3);
365 REQUIRE(CHECK_COLUMN(result, 0, {"Black", "Silver", "White"}));
366 REQUIRE(CHECK_COLUMN(result, 1, {27404.84, 26462.84, 19.00}));
367 REQUIRE(CHECK_COLUMN(result, 2, {15214.9616, 14665.6792, 6.7926}));
368 // TODO report error on third column, first val. in doc its 5214.9616
369
370 result =
371 con.Query("SELECT BusinessEntityID, TerritoryID ,YEAR(ModifiedDate) AS SalesYear "
372 ",SalesYTD ,AVG(SalesYTD) OVER (PARTITION BY "
373 "TerritoryID ORDER BY YEAR(ModifiedDate) ) AS MovingAvg ,SUM(SalesYTD) "
374 "OVER (PARTITION BY TerritoryID ORDER BY YEAR(ModifiedDate) ) AS CumulativeTotal FROM "
375 "Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY TerritoryID,SalesYear;");
376 REQUIRE(result->success);
377 REQUIRE(result->types.size() == 6);
378 // TODO needs double checking, probably using different adventureworks db
379 //
380 // REQUIRE(CHECK_COLUMN(result, 0, {274, 287, 285, 283, 280, 284, 275, 277, 276, 281}));
381 // REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), 1, 1, 1, 2, 3, 4, 4}));
382 // REQUIRE(CHECK_COLUMN(result, 2, {2005, 2006, 2007, 2005, 2005, 2006, 2005, 2005, 2005, 2005}));
383 // REQUIRE(CHECK_COLUMN(result, 3,
384 // {559697.56, 519905.93, 172524.45, 1573012.94, 1352577.13, 1576562.20, 3763178.18,
385 // 3189418.37, 4251368.55, 2458535.62})); REQUIRE(CHECK_COLUMN(result, 4, {559697.56,
386 // 539801.75, 417375.98, 1462795.04, 1462795.04, 1500717.42, 3763178.18, 3189418.37, 3354952.08, 3354952.08}));
387 // REQUIRE(CHECK_COLUMN(result, 5,
388 // {559697.56, 1079603.50, 1252127.95, 2925590.07, 2925590.07, 4502152.27, 3763178.18,
389 // 3189418.37, 6709904.17, 6709904.17}));
390
391 result = con.Query("SELECT BusinessEntityID, TerritoryID ,YEAR(ModifiedDate) AS SalesYear ,SalesYTD "
392 " ,AVG(SalesYTD) OVER (ORDER BY YEAR(ModifiedDate) ) AS MovingAvg "
393 ",SUM(SalesYTD) OVER (ORDER BY YEAR(ModifiedDate) ) AS CumulativeTotal FROM "
394 "Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY SalesYear;");
395 REQUIRE(result->success);
396 REQUIRE(result->types.size() == 6);
397 // TODO needs double checking, probably using different adventureworks db
398 //
399 // REQUIRE(CHECK_COLUMN(result, 0, {274, 275, 276, 277, 280, 281, 283, 284, 287, 285}));
400 // REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 4, 3, 1, 4, 1, 1, Value(), Value()}));
401 // REQUIRE(CHECK_COLUMN(result, 2, {2005, 2005, 2005, 2005, 2005, 2005, 2005, 2006, 2006, 2007}));
402 // REQUIRE(CHECK_COLUMN(result, 3,
403 // {559697.56, 3763178.18, 4251368.55, 3189418.37, 1352577.13, 2458535.62, 1573012.94,
404 // 1576562.20, 519905.93, 172524.45})); REQUIRE(CHECK_COLUMN(result, 4, {2449684.05,
405 // 2449684.05, 2449684.05, 2449684.05, 2449684.05, 2449684.05, 2449684.05, 2138250.72,
406 // 2138250.72, 1941678.09})); REQUIRE(CHECK_COLUMN(result, 5, {17147788.35, 17147788.35,
407 // 17147788.35, 17147788.35,
408 // 17147788.35, 17147788.35, 17147788.35, 19244256.47, 19244256.47, 19416780.93}));
409
410 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/var-transact-sql?view=sql-server-2017
411 // TODO not supported
412 // result = con.Query("SELECT VAR(Bonus) FROM Sales.SalesPerson; ");
413 // REQUIRE(result->success);
414 // REQUIRE(result->types.size() == 2);
415 // REQUIRE(CHECK_COLUMN(result, 0, {159180469909.18}));
416 // REQUIRE(CHECK_COLUMN(result, 1, {158762853821.10}));
417
418 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/cume-dist-transact-sql?view=sql-server-2017
419
420 // TODO fix casting issue in this query
421 // result = con.Query(" SELECT Department, LastName, Rate, CUME_DIST () OVER (PARTITION BY Department ORDER BY
422 // Rate) " "AS CumeDist, PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
423 // FROM " "HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN
424 // HumanResources.EmployeePayHistory " "AS e ON e.BusinessEntityID = edh.BusinessEntityID WHERE Department IN
425 // (N'Information " "Services',N'Document Control')
426 // ORDER BY Department, Rate DESC;"); REQUIRE(result->success); REQUIRE(result->types.size() == 5); REQUIRE(
427 // CHECK_COLUMN(result, 0,
428 // {"Document Control", "Document Control", "Document Control", "Document Control",
429 // "Document Control", "Information Services", "Information Services", "Information Services",
430 // "Information Services", "Information Services", "Information Services", "Information
431 // Services", "Information Services", "Information Services", "Information Services"}));
432 // REQUIRE(CHECK_COLUMN(result, 1,
433 // {"Arifin", "Norred", "Kharatishvili", "Chai", "Berge", "Trenary", "Conroy", "Ajenstat",
434 // "Wilson", "Sharma", "Connelly", "Berg", "Meyyappan", "Bacon", "Bueno"}));
435 // REQUIRE(CHECK_COLUMN(result, 2,
436 // {17.7885, 16.8269, 16.8269, 10.25, 10.25, 50.4808, 39.6635, 38.4615, 38.4615,
437 // 32.4519, 32.4519, 27.4038, 27.4038, 27.4038, 27.4038})); REQUIRE(CHECK_COLUMN(result,
438 // 3, {1, 0.8, 0.8, 0.4, 0.4, 1, 0.9, 0.8, 0.8, 0.6, 0.6, 0.4, 0.4, 0.4, 0.4})); REQUIRE(CHECK_COLUMN(result, 4,
439 // {1, 0.5, 0.5, 0, 0, 1, 0.888888888888889, 0.666666666666667, 0.666666666666667, 0.444444444444444,
440 // 0.444444444444444, 0, 0, 0, 0}));
441
442 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-2017
443
444 result = con.Query(" SELECT Name, ListPrice, FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive "
445 "FROM Production.Product WHERE ProductSubcategoryID = 37 ORDER BY ListPrice, Name DESC;");
446 REQUIRE(result->success);
447 REQUIRE(result->types.size() == 3);
448 REQUIRE(CHECK_COLUMN(result, 0,
449 {"Patch Kit/8 Patches", "Road Tire Tube", "Touring Tire Tube", "Mountain Tire Tube",
450 "LL Road Tire", "ML Road Tire", "LL Mountain Tire", "Touring Tire", "ML Mountain Tire",
451 "HL Road Tire", "HL Mountain Tire"}));
452 REQUIRE(CHECK_COLUMN(result, 1, {2.29, 3.99, 4.99, 4.99, 21.49, 24.99, 24.99, 28.99, 29.99, 32.60, 35.00}));
453 REQUIRE(CHECK_COLUMN(result, 2,
454 {"Patch Kit/8 Patches", "Patch Kit/8 Patches", "Patch Kit/8 Patches", "Patch Kit/8 Patches",
455 "Patch Kit/8 Patches", "Patch Kit/8 Patches", "Patch Kit/8 Patches", "Patch Kit/8 Patches",
456 "Patch Kit/8 Patches", "Patch Kit/8 Patches", "Patch Kit/8 Patches"}));
457
458 // TODO fix casting issue
459 // result =
460 // con.Query(" SELECT JobTitle, LastName, VacationHours, FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle
461 // ORDER " "BY VacationHours ASC ROWS UNBOUNDED PRECEDING ) AS FewestVacationHours FROM
462 // HumanResources.Employee " "AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID =
463 // p.BusinessEntityID ORDER BY JobTitle;"); REQUIRE(result->success); REQUIRE(result->types.size() == 4);
464 // REQUIRE(CHECK_COLUMN(result, 0,
465 // {"Accountant", "Accountant", "Accounts Manager", "Accounts Payable Specialist",
466 // "Accounts Payable Specialist", "Accounts Receivable Specialist",
467 // "Accounts Receivable Specialist", "Accounts Receivable Specialist"}));
468 // REQUIRE(CHECK_COLUMN(result, 1, {"Moreland", "Seamans", "Liu", "Tomic", "Sheperdigian", "Poe", "Spoon",
469 //"Walton"})); REQUIRE(CHECK_COLUMN(result, 2, {58, 59, 57, 63, 64, 60, 61, 62})); REQUIRE(CHECK_COLUMN(result,
470 // 3,
471 //{"Moreland", "Moreland", "Liu", "Tomic", "Tomic", "Poe", "Poe", "Poe"}));
472
473 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/last-value-transact-sql?view=sql-server-2017
474
475 // TODO fix casting issue
476 result = con.Query(" SELECT Department, LastName, Rate, HireDate, LAST_VALUE(HireDate) OVER (PARTITION BY "
477 "Department ORDER BY Rate) AS LastValue FROM HumanResources.vEmployeeDepartmentHistory AS edh "
478 "INNER JOIN HumanResources.EmployeePayHistory AS eph ON eph.BusinessEntityID = "
479 "edh.BusinessEntityID INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = "
480 "edh.BusinessEntityID WHERE Department IN (N'Information Services',N'Document Control');");
481 // REQUIRE(result->success);
482 // REQUIRE(result->types.size() == 5);
483 // REQUIRE(
484 // CHECK_COLUMN(result, 0,
485 // {"Document Control", "Document Control", "Document Control", "Document Control",
486 // "Document Control", "Information Services", "Information Services", "Information Services",
487 // "Information Services", "Information Services", "Information Services", "Information
488 // Services", "Information Services", "Information Services", "Information Services"}));
489 // REQUIRE(CHECK_COLUMN(result, 1,
490 // {"Chai", "Berge", "Norred", "Kharatishvili", "Arifin", "Berg", "Meyyappan", "Bacon",
491 //"Bueno", "Sharma", "Connelly", "Ajenstat", "Wilson", "Conroy", "Trenary"}));
492 // REQUIRE(CHECK_COLUMN(result, 2, {10.25, 10.25, 16.8269, 16.8269, 17.7885, 27.4038,
493 // 27.4038, 27.4038, 27.4038,
494 // 32.4519, 32.4519, 38.4615, 38.4615, 39.6635, 50.4808})); REQUIRE(CHECK_COLUMN(result,
495 // 3,
496 // {"2003-02-23", "2003-03-13", "2003-04-07", "2003-01-17", "2003-02-05", "2003-03-20",
497 // "2003-03-07", "2003-02-12", "2003-01-24", "2003-01-05", "2003-03-27", "2003-02-18",
498 // "2003-02-23", "2003-03-08", "2003-01-12"}));
499 // REQUIRE(CHECK_COLUMN(result, 4,
500 // {"2003-03-13", "2003-03-13", "2003-01-17", "2003-01-17", "2003-02-05", "2003-01-24",
501 // "2003-01-24", "2003-01-24", "2003-01-24", "2003-03-27", "2003-03-27", "2003-02-23",
502 // "2003-02-23", "2003-03-08", "2003-01-12"}));
503
504 // TODO date part quarter impl
505 result = con.Query(
506 " SELECT BusinessEntityID, DATEPART(QUARTER,QuotaDate)AS Quarter, YEAR(QuotaDate) AS SalesYear, SalesQuota AS "
507 "QuotaThisQuarter, SalesQuota - FIRST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) "
508 "ORDER BY DATEPART(QUARTER,QuotaDate) ) AS DifferenceFromFirstQuarter, SalesQuota - LAST_VALUE(SalesQuota) "
509 "OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER,QuotaDate) RANGE BETWEEN "
510 "CURRENT ROW AND UNBOUNDED FOLLOWING ) AS DifferenceFromLastQuarter FROM Sales.SalesPersonQuotaHistory WHERE "
511 "YEAR(QuotaDate) > 2005 AND BusinessEntityID BETWEEN 274 AND 275 ORDER BY BusinessEntityID, SalesYear, "
512 "Quarter;");
513 // REQUIRE(result->success);
514 // REQUIRE(result->types.size() == 6);
515 // REQUIRE(CHECK_COLUMN(result, 0, {274, 274, 274, 274, 274, 274, 274, 274, 274, 274,
516 // 275, 275, 275, 275, 275, 275, 275, 275, 275, 275}));
517 // REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2}));
518 // REQUIRE(CHECK_COLUMN(result, 2, {2006, 2006, 2006, 2006, 2007, 2007, 2007, 2007, 2008, 2008,
519 // 2006, 2006, 2006, 2006, 2007, 2007, 2007, 2007, 2008, 2008}));
520 // REQUIRE(CHECK_COLUMN(result, 3, {91000.00, 140000.00, 70000.00, 154000.00, 107000.00, 58000.00, 263000.00,
521 // 116000.00, 84000.00, 187000.00, 502000.00, 550000.00, 1429000.00,
522 // 1324000.00, 729000.00, 1194000.00, 1575000.00, 1218000.00, 849000.00,
523 // 869000.00})); REQUIRE(CHECK_COLUMN(result, 4, {0.00, 49000.00, -21000.00, 63000.00, 0.00, -49000.00,
524 // 156000.00, 9000.00, 0.00, 103000.00, 0.00, 48000.00, 927000.00,
525 // 822000.00, 0.00, 465000.00, 846000.00, 489000.00, 0.00, 20000.00}));
526 // REQUIRE(CHECK_COLUMN(result, 5, {-63000.00, -14000.00, -84000.00, 0.00, -9000.00, -58000.00,
527 // 147000.00, 0.00, -103000.00, 0.00, -822000.00, -774000.00,
528 // 105000.00, 0.00, -489000.00, -24000.00, 357000.00, 0.00, -20000.00,
529 // 0.00}));
530
531 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017
532
533 // TODO wrong years
534 result =
535 con.Query(" SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, "
536 "1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory WHERE "
537 "BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');");
538 REQUIRE(result->success);
539 REQUIRE(result->types.size() == 4);
540 // REQUIRE(CHECK_COLUMN(result, 0, {275, 275, 275, 275, 275, 275}));
541 // REQUIRE(CHECK_COLUMN(result, 1, {2005, 2005, 2006, 2006, 2006, 2006}));
542 // REQUIRE(CHECK_COLUMN(result, 2, {367000.00, 556000.00, 502000.00, 550000.00, 1429000.00, 1324000.00}));
543 // REQUIRE(CHECK_COLUMN(result, 3, {0.00, 367000.00, 556000.00, 502000.00, 550000.00, 1429000.00}));
544
545 result = con.Query(" SELECT TerritoryName, BusinessEntityID, SalesYTD, LAG (SalesYTD, 1, 0) OVER (PARTITION BY "
546 "TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales FROM Sales.vSalesPerson WHERE "
547 "TerritoryName IN (N'Northwest', N'Canada') ORDER BY TerritoryName, SalesYTD DESC;");
548 REQUIRE(result->success);
549 REQUIRE(result->types.size() == 4);
550 REQUIRE(CHECK_COLUMN(result, 0, {"Canada", "Canada", "Northwest", "Northwest", "Northwest"}));
551 REQUIRE(CHECK_COLUMN(result, 1, {282, 278, 284, 283, 280}));
552 REQUIRE(CHECK_COLUMN(result, 2, {2604540.7172, 1453719.4653, 1576562.1966, 1573012.9383, 1352577.1325}));
553 REQUIRE(CHECK_COLUMN(result, 3, {0.00, 2604540.7172, 0.00, 1576562.1966, 1573012.9383}));
554
555 REQUIRE_NO_FAIL(con.Query("CREATE TABLE T (a int, b int, c int);"));
556 REQUIRE_NO_FAIL(
557 con.Query("INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);"));
558
559 result = con.Query("SELECT b, c, LAG(2*c, b*(SELECT MIN(b) FROM T), "
560 "-c/2.0) OVER (ORDER BY a) AS i FROM T;");
561 REQUIRE(result->success);
562 REQUIRE(result->types.size() == 3);
563 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 1, 3, 2, 1}));
564 REQUIRE(CHECK_COLUMN(result, 1, {-3, 4, Value(), 1, Value(), 5}));
565 REQUIRE(CHECK_COLUMN(result, 2, {1, -2, 8, -6, Value(), Value()}));
566
567 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017
568 result =
569 con.Query(" SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, "
570 "LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota FROM "
571 "Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');");
572 REQUIRE(result->success);
573 // TODO wrong years
574 // REQUIRE(result->types.size() == 4);
575 // REQUIRE(CHECK_COLUMN(result, 0, {275, 275, 275, 275, 275, 275}));
576 // REQUIRE(CHECK_COLUMN(result, 1, {2005, 2005, 2006, 2006, 2006, 2006}));
577 // REQUIRE(CHECK_COLUMN(result, 2, {367000.00, 556000.00, 502000.00, 550000.00, 1429000.00, 1324000.00}));
578 // REQUIRE(CHECK_COLUMN(result, 3, {556000.00, 502000.00, 550000.00, 1429000.00, 1324000.00, 0.00}));
579
580 result = con.Query(" SELECT TerritoryName, BusinessEntityID, SalesYTD, LEAD (SalesYTD, 1, 0) OVER (PARTITION BY "
581 "TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales FROM Sales.vSalesPerson WHERE "
582 "TerritoryName IN (N'Northwest', N'Canada') ORDER BY TerritoryName, BusinessEntityID DESC;");
583 REQUIRE(result->success);
584 REQUIRE(result->types.size() == 4);
585 REQUIRE(CHECK_COLUMN(result, 0, {"Canada", "Canada", "Northwest", "Northwest", "Northwest"}));
586 REQUIRE(CHECK_COLUMN(result, 1, {282, 278, 284, 283, 280}));
587 REQUIRE(CHECK_COLUMN(result, 2, {2604540.7172, 1453719.4653, 1576562.1966, 1573012.9383, 1352577.1325}));
588 REQUIRE(CHECK_COLUMN(result, 3, {1453719.4653, 0.00, 1573012.9383, 1352577.1325, 0.00}));
589
590 result = con.Query("SELECT b, c, LEAD(2*c, b*(SELECT MIN(b) FROM T), "
591 "-c/2.0) OVER (ORDER BY a) AS i FROM T;");
592 REQUIRE(result->success);
593 REQUIRE(result->types.size() == 3);
594 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 1, 3, 2, 1}));
595 REQUIRE(CHECK_COLUMN(result, 1, {-3, 4, Value(), 1, Value(), 5}));
596 // TODO unclear whats going on
597 // REQUIRE(CHECK_COLUMN(result, 2, {8, 2, 2, 0, Value(), -2}));
598
599 REQUIRE_NO_FAIL(con.Query("DROP TABLE T;"));
600
601 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/percent-rank-transact-sql?view=sql-server-2017
602
603 result = con.Query("SELECT Department, LastName, Rate, CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) "
604 "AS CumeDist, PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank FROM "
605 "HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN HumanResources.EmployeePayHistory "
606 "AS e ON e.BusinessEntityID = edh.BusinessEntityID WHERE Department IN (N'Information "
607 "Services',N'Document Control') ORDER BY Department, Rate DESC;");
608 REQUIRE(result->success);
609 REQUIRE(result->types.size() == 5);
610 // TODO ORDER
611 // REQUIRE(
612 // CHECK_COLUMN(result, 0,
613 // {"Document Control", "Document Control", "Document Control", "Document Control",
614 // "Document Control", "Information Services", "Information Services", "Information Services",
615 // "Information Services", "Information Services", "Information Services", "Information
616 // Services", "Information Services", "Information Services", "Information Services"}));
617 // REQUIRE(CHECK_COLUMN(result, 1,
618 // {"Arifin", "Norred", "Kharatishvili", "Chai", "Berge", "Trenary", "Conroy", "Ajenstat",
619 // "Wilson", "Sharma", "Connelly", "Berg", "Meyyappan", "Bacon", "Bueno"}));
620 // REQUIRE(CHECK_COLUMN(result, 2,
621 // {17.7885, 16.8269, 16.8269, 10.25, 10.25, 50.4808, 39.6635, 38.4615, 38.4615,
622 // 32.4519, 32.4519, 27.4038, 27.4038, 27.4038, 27.4038})); REQUIRE(CHECK_COLUMN(result,
623 // 3, {1, 0.8, 0.8, 0.4, 0.4, 1, 0.9, 0.8, 0.8, 0.6, 0.6, 0.4, 0.4, 0.4, 0.4})); REQUIRE(CHECK_COLUMN(result, 4,
624 // {1, 0.5, 0.5, 0, 0, 1, 0.888888888888889, 0.666666666666667, 0.666666666666667, 0.444444444444444,
625 // 0.444444444444444, 0, 0, 0, 0}));
626
627 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-2017
628
629 result = con.Query(
630 " SELECT i.ProductID, p.Name, i.LocationID, i.Quantity ,DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY "
631 "i.Quantity DESC) AS Rank FROM Production.ProductInventory AS i INNER JOIN Production.Product AS p ON "
632 "i.ProductID = p.ProductID WHERE i.LocationID BETWEEN 3 AND 4 ORDER BY i.LocationID, Quantity DESC; ");
633 REQUIRE(result->success);
634 REQUIRE(result->types.size() == 5);
635 REQUIRE(CHECK_COLUMN(result, 0, {494, 495, 493, 496, 492, 495, 496, 493, 492, 494}));
636 REQUIRE(CHECK_COLUMN(result, 1,
637 {"Paint - Silver", "Paint - Blue", "Paint - Red", "Paint - Yellow", "Paint - Black",
638 "Paint - Blue", "Paint - Yellow", "Paint - Red", "Paint - Black", "Paint - Silver"}));
639 REQUIRE(CHECK_COLUMN(result, 2, {3, 3, 3, 3, 3, 4, 4, 4, 4, 4}));
640 REQUIRE(CHECK_COLUMN(result, 3, {49, 49, 41, 30, 17, 35, 25, 24, 14, 12}));
641 REQUIRE(CHECK_COLUMN(result, 4, {1, 1, 2, 3, 4, 1, 2, 3, 4, 5}));
642
643 result = con.Query(" SELECT BusinessEntityID, Rate, DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary "
644 "FROM HumanResources.EmployeePayHistory order by Rate DESC, BusinessEntityID DESC LIMIT 10;");
645 REQUIRE(result->success);
646 REQUIRE(result->types.size() == 3);
647 REQUIRE(
648 CHECK_COLUMN(result, 0, {1, 25, 273, 2, 234, 263, 7, 234, 287, 285})); // fixed because ambiguity with rank 8
649 REQUIRE(CHECK_COLUMN(result, 1,
650 {125.50, 84.1346, 72.1154, 63.4615, 60.0962, 50.4808, 50.4808, 48.5577, 48.101, 48.101}));
651 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 4, 5, 6, 6, 7, 8, 8}));
652
653 result = con.Query(
654 " SELECT p.FirstName, p.LastName ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS \"Row Number\" ,RANK() OVER "
655 "(ORDER BY a.PostalCode) AS Rank ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS \"Dense Rank\" ,NTILE(4) OVER "
656 "(ORDER BY a.PostalCode) AS Quartile ,s.SalesYTD ,a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN "
657 "Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = "
658 "p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;");
659 REQUIRE(result->success);
660 REQUIRE(result->types.size() == 8);
661 // TODO order with postal code highly underspec
662
663 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql?view=sql-server-2017
664
665 result =
666 con.Query(" SELECT p.FirstName, p.LastName ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile "
667 ",s.SalesYTD AS SalesYTD , a.PostalCode FROM Sales.SalesPerson AS s INNER "
668 "JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a "
669 "ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; ");
670 REQUIRE(result->success);
671 REQUIRE(result->types.size() == 5);
672 REQUIRE(CHECK_COLUMN(result, 0,
673 {"Linda", "Jae", "Michael", "Jillian", "Ranjit", "José", "Shu", "Tsvi", "Rachel", "Tete",
674 "David", "Garrett", "Lynn", "Pamela"}));
675 REQUIRE(CHECK_COLUMN(result, 1,
676 {"Mitchell", "Pak", "Blythe", "Carson", "Varkey Chudukatil", "Saraiva", "Ito", "Reiter",
677 "Valdez", "Mensa-Annan", "Campbell", "Vargas", "Tsoflias", "Ansman-Wolfe"}));
678 REQUIRE(CHECK_COLUMN(result, 2, {1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 4}));
679 REQUIRE(CHECK_COLUMN(result, 3,
680 {4251368.55, 4116871.23, 3763178.18, 3189418.37, 3121616.32, 2604540.72, 2458535.62,
681 2315185.61, 1827066.71, 1576562.20, 1573012.94, 1453719.47, 1421810.92, 1352577.13}));
682 REQUIRE(CHECK_COLUMN(result, 4,
683 {"98027", "98055", "98027", "98027", "98055", "98055", "98055", "98027", "98055", "98055",
684 "98055", "98027", "98055", "98027"}));
685
686 result = con.Query(
687 " SELECT p.FirstName, p.LastName ,NTILE(4) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "
688 "Quartile ,s.SalesYTD AS SalesYTD ,a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN "
689 "Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = "
690 "p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; ");
691 REQUIRE(result->success);
692 REQUIRE(result->types.size() == 5);
693 REQUIRE(CHECK_COLUMN(result, 0,
694 {"Linda", "Michael", "Jillian", "Tsvi", "Garrett", "Pamela", "Jae", "Ranjit", "José", "Shu",
695 "Rachel", "Tete", "David", "Lynn"}));
696 REQUIRE(CHECK_COLUMN(result, 1,
697 {"Mitchell", "Blythe", "Carson", "Reiter", "Vargas", "Ansman-Wolfe", "Pak",
698 "Varkey Chudukatil", "Saraiva", "Ito", "Valdez", "Mensa-Annan", "Campbell", "Tsoflias"}));
699 // FIXME something wrong here with NTILE
700 // REQUIRE(CHECK_COLUMN(result, 2, {1, 1, 2, 2, 3, 4, 1, 1, 2, 2, 3, 3, 4, 4}));
701 REQUIRE(CHECK_COLUMN(result, 3,
702 {4251368.55, 3763178.18, 3189418.37, 2315185.61, 1453719.47, 1352577.13, 4116871.23,
703 3121616.32, 2604540.72, 2458535.62, 1827066.71, 1576562.20, 1573012.94, 1421810.92}));
704 REQUIRE(CHECK_COLUMN(result, 4,
705 {"98027", "98027", "98027", "98027", "98027", "98027", "98055", "98055", "98055", "98055",
706 "98055", "98055", "98055", "98055"}));
707
708 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-2017
709
710 result =
711 con.Query(" SELECT i.ProductID, p.Name, i.LocationID, i.Quantity ,RANK() OVER (PARTITION BY i.LocationID ORDER "
712 "BY i.Quantity DESC) AS Rank FROM Production.ProductInventory AS i INNER JOIN Production.Product AS "
713 "p ON i.ProductID = p.ProductID WHERE i.LocationID BETWEEN 3 AND 4 ORDER BY i.LocationID, i.Quantity "
714 "DESC, i.ProductID; ");
715 REQUIRE(result->success);
716 REQUIRE(result->types.size() == 5);
717 REQUIRE(CHECK_COLUMN(result, 0, {494, 495, 493, 496, 492, 495, 496, 493, 492, 494}));
718 REQUIRE(CHECK_COLUMN(result, 1,
719 {"Paint - Silver", "Paint - Blue", "Paint - Red", "Paint - Yellow", "Paint - Black",
720 "Paint - Blue", "Paint - Yellow", "Paint - Red", "Paint - Black", "Paint - Silver"}));
721 REQUIRE(CHECK_COLUMN(result, 2, {3, 3, 3, 3, 3, 4, 4, 4, 4, 4}));
722 REQUIRE(CHECK_COLUMN(result, 3, {49, 49, 41, 30, 17, 35, 25, 24, 14, 12}));
723 REQUIRE(CHECK_COLUMN(result, 4, {1, 1, 3, 4, 5, 1, 2, 3, 4, 5}));
724
725 result = con.Query(" SELECT BusinessEntityID, Rate, RANK() OVER (ORDER BY Rate DESC) AS RankBySalary FROM "
726 "HumanResources.EmployeePayHistory AS eph1 WHERE RateChangeDate = (SELECT MAX(RateChangeDate) "
727 "FROM HumanResources.EmployeePayHistory AS eph2 WHERE eph1.BusinessEntityID = "
728 "eph2.BusinessEntityID) ORDER BY BusinessEntityID LIMIT 10;");
729 REQUIRE(result->success);
730 REQUIRE(result->types.size() == 3);
731 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}));
732 REQUIRE(CHECK_COLUMN(result, 1,
733 {125.50, 63.4615, 43.2692, 29.8462, 32.6923, 32.6923, 50.4808, 40.8654, 40.8654, 42.4808}));
734 // TODO: fix an initialization issue with RANK here
735 // REQUIRE(CHECK_COLUMN(result, 2, {1, 4, 8, 19, 16, 16, 6, 10, 10, 9}));
736
737 // FROM https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017
738
739 result = con.Query(" SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, FirstName, LastName, SalesYTD FROM "
740 "Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;");
741 REQUIRE(result->success);
742 REQUIRE(result->types.size() == 4);
743 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14}));
744 REQUIRE(CHECK_COLUMN(result, 1,
745 {"Linda", "Jae", "Michael", "Jillian", "Ranjit", "José", "Shu", "Tsvi", "Rachel", "Tete",
746 "David", "Garrett", "Lynn", "Pamela"}));
747 REQUIRE(CHECK_COLUMN(result, 2,
748 {"Mitchell", "Pak", "Blythe", "Carson", "Varkey Chudukatil", "Saraiva", "Ito", "Reiter",
749 "Valdez", "Mensa-Annan", "Campbell", "Vargas", "Tsoflias", "Ansman-Wolfe"}));
750 REQUIRE(CHECK_COLUMN(result, 3,
751 {4251368.54, 4116871.22, 3763178.17, 3189418.36, 3121616.32, 2604540.71, 2458535.61,
752 2315185.61, 1827066.71, 1576562.19, 1573012.93, 1453719.46, 1421810.92, 1352577.13}));
753
754 result = con.Query(" SELECT FirstName, LastName, TerritoryName, SalesYTD, ROW_NUMBER() "
755 "OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row FROM Sales.vSalesPerson WHERE "
756 "TerritoryName IS NOT NULL AND SalesYTD <> 0 ORDER BY TerritoryName, SalesYTD DESC;");
757 REQUIRE(result->success);
758 REQUIRE(result->types.size() == 5);
759 REQUIRE(CHECK_COLUMN(result, 0,
760 {"Lynn", "José", "Garrett", "Jillian", "Ranjit", "Rachel", "Michael", "Tete", "David",
761 "Pamela", "Tsvi", "Linda", "Shu", "Jae"}));
762 REQUIRE(CHECK_COLUMN(result, 1,
763 {"Tsoflias", "Saraiva", "Vargas", "Carson", "Varkey Chudukatil", "Valdez", "Blythe",
764 "Mensa-Annan", "Campbell", "Ansman-Wolfe", "Reiter", "Mitchell", "Ito", "Pak"}));
765 REQUIRE(CHECK_COLUMN(result, 2,
766 {"Australia", "Canada", "Canada", "Central", "France", "Germany", "Northeast", "Northwest",
767 "Northwest", "Northwest", "Southeast", "Southwest", "Southwest", "United Kingdom"}));
768 REQUIRE(CHECK_COLUMN(result, 3,
769 {1421810.92, 2604540.71, 1453719.46, 3189418.36, 3121616.32, 1827066.71, 3763178.17,
770 1576562.19, 1573012.93, 1352577.13, 2315185.61, 4251368.54, 2458535.61, 4116871.22}));
771 REQUIRE(CHECK_COLUMN(result, 4, {1, 1, 2, 1, 1, 1, 1, 1, 2, 3, 1, 1, 2, 1}));
772}
773