1/* 2Copyright 2019 Google LLC 3 4Licensed under the Apache License, Version 2.0 (the "License"); 5you may not use this file except in compliance with the License. 6You may obtain a copy of the License at 7 8 http://www.apache.org/licenses/LICENSE-2.0 9 10Unless required by applicable law or agreed to in writing, software 11distributed under the License is distributed on an "AS IS" BASIS, 12WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13See the License for the specific language governing permissions and 14limitations under the License. 15*/ 16 17package spansql 18 19import ( 20 "reflect" 21 "testing" 22 "time" 23 24 "cloud.google.com/go/civil" 25) 26 27func boolAddr(b bool) *bool { 28 return &b 29} 30 31func TestSQL(t *testing.T) { 32 reparseDDL := func(s string) (interface{}, error) { 33 ddl, err := ParseDDLStmt(s) 34 if err != nil { 35 return nil, err 36 } 37 ddl.clearOffset() 38 return ddl, nil 39 } 40 reparseDML := func(s string) (interface{}, error) { 41 dml, err := ParseDMLStmt(s) 42 if err != nil { 43 return nil, err 44 } 45 return dml, nil 46 } 47 reparseQuery := func(s string) (interface{}, error) { 48 q, err := ParseQuery(s) 49 return q, err 50 } 51 reparseExpr := func(s string) (interface{}, error) { 52 e, pe := newParser("f-expr", s).parseExpr() 53 if pe != nil { 54 return nil, pe 55 } 56 return e, nil 57 } 58 59 latz, err := time.LoadLocation("America/Los_Angeles") 60 if err != nil { 61 t.Fatalf("Loading Los Angeles time zone info: %v", err) 62 } 63 64 line := func(n int) Position { return Position{Line: n} } 65 tests := []struct { 66 data interface{ SQL() string } 67 sql string 68 reparse func(string) (interface{}, error) 69 }{ 70 { 71 &CreateTable{ 72 Name: "Ta", 73 Columns: []ColumnDef{ 74 {Name: "Ca", Type: Type{Base: Bool}, NotNull: true, Position: line(2)}, 75 {Name: "Cb", Type: Type{Base: Int64}, Position: line(3)}, 76 {Name: "Cc", Type: Type{Base: Float64}, Position: line(4)}, 77 {Name: "Cd", Type: Type{Base: String, Len: 17}, Position: line(5)}, 78 {Name: "Ce", Type: Type{Base: String, Len: MaxLen}, Position: line(6)}, 79 {Name: "Cf", Type: Type{Base: Bytes, Len: 4711}, Position: line(7)}, 80 {Name: "Cg", Type: Type{Base: Bytes, Len: MaxLen}, Position: line(8)}, 81 {Name: "Ch", Type: Type{Base: Date}, Position: line(9)}, 82 {Name: "Ci", Type: Type{Base: Timestamp}, Options: ColumnOptions{AllowCommitTimestamp: boolAddr(true)}, Position: line(10)}, 83 {Name: "Cj", Type: Type{Array: true, Base: Int64}, Position: line(11)}, 84 {Name: "Ck", Type: Type{Array: true, Base: String, Len: MaxLen}, Position: line(12)}, 85 {Name: "Cl", Type: Type{Base: Timestamp}, Options: ColumnOptions{AllowCommitTimestamp: boolAddr(false)}, Position: line(13)}, 86 {Name: "Cm", Type: Type{Base: Int64}, Generated: Func{Name: "CHAR_LENGTH", Args: []Expr{ID("Ce")}}, Position: line(14)}, 87 {Name: "Cn", Type: Type{Base: JSON}, Position: line(15)}, 88 }, 89 PrimaryKey: []KeyPart{ 90 {Column: "Ca"}, 91 {Column: "Cb", Desc: true}, 92 }, 93 Position: line(1), 94 }, 95 `CREATE TABLE Ta ( 96 Ca BOOL NOT NULL, 97 Cb INT64, 98 Cc FLOAT64, 99 Cd STRING(17), 100 Ce STRING(MAX), 101 Cf BYTES(4711), 102 Cg BYTES(MAX), 103 Ch DATE, 104 Ci TIMESTAMP OPTIONS (allow_commit_timestamp = true), 105 Cj ARRAY<INT64>, 106 Ck ARRAY<STRING(MAX)>, 107 Cl TIMESTAMP OPTIONS (allow_commit_timestamp = null), 108 Cm INT64 AS (CHAR_LENGTH(Ce)) STORED, 109 Cn JSON, 110) PRIMARY KEY(Ca, Cb DESC)`, 111 reparseDDL, 112 }, 113 { 114 &CreateTable{ 115 Name: "Tsub", 116 Columns: []ColumnDef{ 117 {Name: "SomeId", Type: Type{Base: Int64}, NotNull: true, Position: line(2)}, 118 {Name: "OtherId", Type: Type{Base: Int64}, NotNull: true, Position: line(3)}, 119 // This column name uses a reserved keyword. 120 {Name: "Hash", Type: Type{Base: Bytes, Len: 32}, Position: line(4)}, 121 }, 122 PrimaryKey: []KeyPart{ 123 {Column: "SomeId"}, 124 {Column: "OtherId"}, 125 }, 126 Interleave: &Interleave{ 127 Parent: "Ta", 128 OnDelete: CascadeOnDelete, 129 }, 130 Position: line(1), 131 }, 132 `CREATE TABLE Tsub ( 133 SomeId INT64 NOT NULL, 134 OtherId INT64 NOT NULL, 135 ` + "`Hash`" + ` BYTES(32), 136) PRIMARY KEY(SomeId, OtherId), 137 INTERLEAVE IN PARENT Ta ON DELETE CASCADE`, 138 reparseDDL, 139 }, 140 { 141 &CreateTable{ 142 Name: "WithRowDeletionPolicy", 143 Columns: []ColumnDef{ 144 {Name: "Name", Type: Type{Base: String, Len: MaxLen}, NotNull: true, Position: line(2)}, 145 {Name: "DelTimestamp", Type: Type{Base: Timestamp}, NotNull: true, Position: line(3)}, 146 }, 147 PrimaryKey: []KeyPart{{Column: "Name"}}, 148 RowDeletionPolicy: &RowDeletionPolicy{ 149 Column: ID("DelTimestamp"), 150 NumDays: 30, 151 }, 152 Position: line(1), 153 }, 154 `CREATE TABLE WithRowDeletionPolicy ( 155 Name STRING(MAX) NOT NULL, 156 DelTimestamp TIMESTAMP NOT NULL, 157) PRIMARY KEY(Name), 158 ROW DELETION POLICY ( OLDER_THAN ( DelTimestamp, INTERVAL 30 DAY ))`, 159 reparseDDL, 160 }, 161 { 162 &DropTable{ 163 Name: "Ta", 164 Position: line(1), 165 }, 166 "DROP TABLE Ta", 167 reparseDDL, 168 }, 169 { 170 &CreateIndex{ 171 Name: "Ia", 172 Table: "Ta", 173 Columns: []KeyPart{ 174 {Column: "Ca"}, 175 {Column: "Cb", Desc: true}, 176 }, 177 Position: line(1), 178 }, 179 "CREATE INDEX Ia ON Ta(Ca, Cb DESC)", 180 reparseDDL, 181 }, 182 { 183 &DropIndex{ 184 Name: "Ia", 185 Position: line(1), 186 }, 187 "DROP INDEX Ia", 188 reparseDDL, 189 }, 190 { 191 &CreateView{ 192 Name: "SingersView", 193 OrReplace: true, 194 Query: Query{ 195 Select: Select{ 196 List: []Expr{ID("SingerId"), ID("FullName"), ID("Picture")}, 197 From: []SelectFrom{SelectFromTable{ 198 Table: "Singers", 199 }}, 200 }, 201 Order: []Order{ 202 {Expr: ID("LastName")}, 203 {Expr: ID("FirstName")}, 204 }, 205 }, 206 Position: line(1), 207 }, 208 "CREATE OR REPLACE VIEW SingersView SQL SECURITY INVOKER AS SELECT SingerId, FullName, Picture FROM Singers ORDER BY LastName, FirstName", 209 reparseDDL, 210 }, 211 { 212 &DropView{ 213 Name: "SingersView", 214 Position: line(1), 215 }, 216 "DROP VIEW SingersView", 217 reparseDDL, 218 }, 219 { 220 &AlterTable{ 221 Name: "Ta", 222 Alteration: AddColumn{Def: ColumnDef{Name: "Ca", Type: Type{Base: Bool}, Position: line(1)}}, 223 Position: line(1), 224 }, 225 "ALTER TABLE Ta ADD COLUMN Ca BOOL", 226 reparseDDL, 227 }, 228 { 229 &AlterTable{ 230 Name: "Ta", 231 Alteration: DropColumn{Name: "Ca"}, 232 Position: line(1), 233 }, 234 "ALTER TABLE Ta DROP COLUMN Ca", 235 reparseDDL, 236 }, 237 { 238 &AlterTable{ 239 Name: "Ta", 240 Alteration: SetOnDelete{Action: NoActionOnDelete}, 241 Position: line(1), 242 }, 243 "ALTER TABLE Ta SET ON DELETE NO ACTION", 244 reparseDDL, 245 }, 246 { 247 &AlterTable{ 248 Name: "Ta", 249 Alteration: SetOnDelete{Action: CascadeOnDelete}, 250 Position: line(1), 251 }, 252 "ALTER TABLE Ta SET ON DELETE CASCADE", 253 reparseDDL, 254 }, 255 { 256 &AlterTable{ 257 Name: "Ta", 258 Alteration: AlterColumn{ 259 Name: "Cg", 260 Alteration: SetColumnType{ 261 Type: Type{Base: String, Len: MaxLen}, 262 }, 263 }, 264 Position: line(1), 265 }, 266 "ALTER TABLE Ta ALTER COLUMN Cg STRING(MAX)", 267 reparseDDL, 268 }, 269 { 270 &AlterTable{ 271 Name: "Ta", 272 Alteration: AlterColumn{ 273 Name: "Ci", 274 Alteration: SetColumnOptions{ 275 Options: ColumnOptions{ 276 AllowCommitTimestamp: boolAddr(false), 277 }, 278 }, 279 }, 280 Position: line(1), 281 }, 282 "ALTER TABLE Ta ALTER COLUMN Ci SET OPTIONS (allow_commit_timestamp = null)", 283 reparseDDL, 284 }, 285 { 286 &AlterTable{ 287 Name: "WithRowDeletionPolicy", 288 Alteration: DropRowDeletionPolicy{}, 289 Position: line(1), 290 }, 291 "ALTER TABLE WithRowDeletionPolicy DROP ROW DELETION POLICY", 292 reparseDDL, 293 }, 294 { 295 &AlterTable{ 296 Name: "WithRowDeletionPolicy", 297 Alteration: AddRowDeletionPolicy{ 298 RowDeletionPolicy: RowDeletionPolicy{ 299 Column: ID("DelTimestamp"), 300 NumDays: 30, 301 }, 302 }, 303 Position: line(1), 304 }, 305 "ALTER TABLE WithRowDeletionPolicy ADD ROW DELETION POLICY ( OLDER_THAN ( DelTimestamp, INTERVAL 30 DAY ))", 306 reparseDDL, 307 }, 308 { 309 &AlterTable{ 310 Name: "WithRowDeletionPolicy", 311 Alteration: ReplaceRowDeletionPolicy{ 312 RowDeletionPolicy: RowDeletionPolicy{ 313 Column: ID("DelTimestamp"), 314 NumDays: 30, 315 }, 316 }, 317 Position: line(1), 318 }, 319 "ALTER TABLE WithRowDeletionPolicy REPLACE ROW DELETION POLICY ( OLDER_THAN ( DelTimestamp, INTERVAL 30 DAY ))", 320 reparseDDL, 321 }, 322 { 323 &AlterDatabase{ 324 Name: "dbname", 325 Alteration: SetDatabaseOptions{Options: DatabaseOptions{ 326 EnableKeyVisualizer: func(b bool) *bool { return &b }(true), 327 }}, 328 Position: line(1), 329 }, 330 "ALTER DATABASE dbname SET OPTIONS (enable_key_visualizer=true)", 331 reparseDDL, 332 }, 333 { 334 &AlterDatabase{ 335 Name: "dbname", 336 Alteration: SetDatabaseOptions{Options: DatabaseOptions{ 337 OptimizerVersion: func(i int) *int { return &i }(2), 338 }}, 339 Position: line(1), 340 }, 341 "ALTER DATABASE dbname SET OPTIONS (optimizer_version=2)", 342 reparseDDL, 343 }, 344 { 345 &AlterDatabase{ 346 Name: "dbname", 347 Alteration: SetDatabaseOptions{Options: DatabaseOptions{ 348 VersionRetentionPeriod: func(s string) *string { return &s }("7d"), 349 OptimizerVersion: func(i int) *int { return &i }(2), 350 EnableKeyVisualizer: func(b bool) *bool { return &b }(true), 351 }}, 352 Position: line(1), 353 }, 354 "ALTER DATABASE dbname SET OPTIONS (optimizer_version=2, version_retention_period='7d', enable_key_visualizer=true)", 355 reparseDDL, 356 }, 357 { 358 &AlterDatabase{ 359 Name: "dbname", 360 Alteration: SetDatabaseOptions{Options: DatabaseOptions{ 361 VersionRetentionPeriod: func(s string) *string { return &s }(""), 362 OptimizerVersion: func(i int) *int { return &i }(0), 363 EnableKeyVisualizer: func(b bool) *bool { return &b }(false), 364 }}, 365 Position: line(1), 366 }, 367 "ALTER DATABASE dbname SET OPTIONS (optimizer_version=null, version_retention_period=null, enable_key_visualizer=null)", 368 reparseDDL, 369 }, 370 { 371 &Delete{ 372 Table: "Ta", 373 Where: ComparisonOp{ 374 LHS: ID("C"), 375 Op: Gt, 376 RHS: IntegerLiteral(2), 377 }, 378 }, 379 "DELETE FROM Ta WHERE C > 2", 380 reparseDML, 381 }, 382 { 383 &Update{ 384 Table: "Ta", 385 Items: []UpdateItem{ 386 {Column: "Cb", Value: IntegerLiteral(4)}, 387 {Column: "Ce", Value: StringLiteral("wow")}, 388 {Column: "Cf", Value: ID("Cg")}, 389 {Column: "Cg", Value: Null}, 390 {Column: "Ch", Value: nil}, 391 }, 392 Where: ID("Ca"), 393 }, 394 `UPDATE Ta SET Cb = 4, Ce = "wow", Cf = Cg, Cg = NULL, Ch = DEFAULT WHERE Ca`, 395 reparseDML, 396 }, 397 { 398 Query{ 399 Select: Select{ 400 List: []Expr{ID("A"), ID("B")}, 401 From: []SelectFrom{SelectFromTable{Table: "Table"}}, 402 Where: LogicalOp{ 403 LHS: ComparisonOp{ 404 LHS: ID("C"), 405 Op: Lt, 406 RHS: StringLiteral("whelp"), 407 }, 408 Op: And, 409 RHS: IsOp{ 410 LHS: ID("D"), 411 Neg: true, 412 RHS: Null, 413 }, 414 }, 415 ListAliases: []ID{"", "banana"}, 416 }, 417 Order: []Order{{Expr: ID("OCol"), Desc: true}}, 418 Limit: IntegerLiteral(1000), 419 }, 420 `SELECT A, B AS banana FROM Table WHERE C < "whelp" AND D IS NOT NULL ORDER BY OCol DESC LIMIT 1000`, 421 reparseQuery, 422 }, 423 { 424 Query{ 425 Select: Select{ 426 List: []Expr{ID("A")}, 427 From: []SelectFrom{SelectFromTable{ 428 Table: "Table", 429 Hints: map[string]string{"FORCE_INDEX": "Idx"}, 430 }}, 431 Where: ComparisonOp{ 432 LHS: ID("B"), 433 Op: Eq, 434 RHS: Param("b"), 435 }, 436 }, 437 }, 438 `SELECT A FROM Table@{FORCE_INDEX=Idx} WHERE B = @b`, 439 reparseQuery, 440 }, 441 { 442 Query{ 443 Select: Select{ 444 List: []Expr{ID("A")}, 445 From: []SelectFrom{SelectFromTable{ 446 Table: "Table", 447 Hints: map[string]string{"FORCE_INDEX": "Idx", "GROUPBY_SCAN_OPTIMIZATION": "TRUE"}, 448 }}, 449 Where: ComparisonOp{ 450 LHS: ID("B"), 451 Op: Eq, 452 RHS: Param("b"), 453 }, 454 }, 455 }, 456 `SELECT A FROM Table@{FORCE_INDEX=Idx,GROUPBY_SCAN_OPTIMIZATION=TRUE} WHERE B = @b`, 457 reparseQuery, 458 }, 459 { 460 Query{ 461 Select: Select{ 462 List: []Expr{IntegerLiteral(7)}, 463 }, 464 }, 465 `SELECT 7`, 466 reparseQuery, 467 }, 468 { 469 ComparisonOp{LHS: ID("X"), Op: NotBetween, RHS: ID("Y"), RHS2: ID("Z")}, 470 `X NOT BETWEEN Y AND Z`, 471 reparseExpr, 472 }, 473 { 474 Query{ 475 Select: Select{ 476 List: []Expr{ 477 ID("Desc"), 478 }, 479 }, 480 }, 481 "SELECT `Desc`", 482 reparseQuery, 483 }, 484 { 485 DateLiteral(civil.Date{Year: 2014, Month: time.September, Day: 27}), 486 `DATE '2014-09-27'`, 487 reparseExpr, 488 }, 489 { 490 TimestampLiteral(time.Date(2014, time.September, 27, 12, 34, 56, 123456e3, latz)), 491 `TIMESTAMP '2014-09-27 12:34:56.123456 -07:00'`, 492 reparseExpr, 493 }, 494 { 495 Query{ 496 Select: Select{ 497 List: []Expr{ 498 ID("A"), ID("B"), 499 }, 500 From: []SelectFrom{ 501 SelectFromJoin{ 502 Type: InnerJoin, 503 LHS: SelectFromTable{Table: "Table1"}, 504 RHS: SelectFromTable{Table: "Table2"}, 505 On: ComparisonOp{ 506 LHS: PathExp{"Table1", "A"}, 507 Op: Eq, 508 RHS: PathExp{"Table2", "A"}, 509 }, 510 }, 511 }, 512 }, 513 }, 514 "SELECT A, B FROM Table1 INNER JOIN Table2 ON Table1.A = Table2.A", 515 reparseQuery, 516 }, 517 { 518 Query{ 519 Select: Select{ 520 List: []Expr{ 521 ID("A"), ID("B"), 522 }, 523 From: []SelectFrom{ 524 SelectFromJoin{ 525 Type: InnerJoin, 526 LHS: SelectFromJoin{ 527 Type: InnerJoin, 528 LHS: SelectFromTable{Table: "Table1"}, 529 RHS: SelectFromTable{Table: "Table2"}, 530 On: ComparisonOp{ 531 LHS: PathExp{"Table1", "A"}, 532 Op: Eq, 533 RHS: PathExp{"Table2", "A"}, 534 }, 535 }, 536 RHS: SelectFromTable{Table: "Table3"}, 537 Using: []ID{"X"}, 538 }, 539 }, 540 }, 541 }, 542 "SELECT A, B FROM Table1 INNER JOIN Table2 ON Table1.A = Table2.A INNER JOIN Table3 USING (X)", 543 reparseQuery, 544 }, 545 } 546 for _, test := range tests { 547 sql := test.data.SQL() 548 if sql != test.sql { 549 t.Errorf("%v.SQL() wrong.\n got %s\nwant %s", test.data, sql, test.sql) 550 continue 551 } 552 553 // As a confidence check, confirm that parsing the SQL produces the original input. 554 data, err := test.reparse(sql) 555 if err != nil { 556 t.Errorf("Reparsing %q: %v", sql, err) 557 continue 558 } 559 if !reflect.DeepEqual(data, test.data) { 560 t.Errorf("Reparsing %q wrong.\n got %v\nwant %v", sql, data, test.data) 561 } 562 } 563} 564