gopkg.in/cznic/ql.v1@v1.1.0/doc.go (about) 1 // Copyright 2014 The ql Authors. All rights reserved. 2 // Use of this source code is governed by a BSD-style 3 // license that can be found in the LICENSE file. 4 5 //MAYBE set operations 6 //MAYBE +=, -=, ... 7 8 //TODO verify there's a graceful failure for a 2G+ blob on a 32 bit machine. 9 10 // Package ql implements a pure Go embedded SQL database engine. 11 // 12 // QL is a member of the SQL family of languages. It is less complex and less 13 // powerful than SQL (whichever specification SQL is considered to be). 14 // 15 // Change list 16 // 17 // 2017-01-10: Release v1.1.0 fixes some bugs and adds a configurable WAL 18 // headroom. 19 // 20 // https://github.com/cznic/ql/issues/140 21 // 22 // 2016-07-29: Release v1.0.6 enables alternatively using = instead of == for 23 // equality operation. 24 // 25 // https://github.com/cznic/ql/issues/131 26 // 27 // 2016-07-11: Release v1.0.5 undoes vendoring of lldb. QL now uses stable lldb 28 // (github.com/cznic/lldb). 29 // 30 // https://github.com/cznic/ql/issues/128 31 // 32 // 2016-07-06: Release v1.0.4 fixes a panic when closing the WAL file. 33 // 34 // https://github.com/cznic/ql/pull/127 35 // 36 // 2016-04-03: Release v1.0.3 fixes a data race. 37 // 38 // https://github.com/cznic/ql/issues/126 39 // 40 // 2016-03-23: Release v1.0.2 vendors github.com/cznic/exp/lldb and 41 // github.com/camlistore/go4/lock. 42 // 43 // 2016-03-17: Release v1.0.1 adjusts for latest goyacc. Parser error messages 44 // are improved and changed, but their exact form is not considered a API 45 // change. 46 // 47 // 2016-03-05: The current version has been tagged v1.0.0. 48 // 49 // 2015-06-15: To improve compatibility with other SQL implementations, the 50 // count built-in aggregate function now accepts * as its argument. 51 // 52 // 2015-05-29: The execution planner was rewritten from scratch. It should use 53 // indices in all places where they were used before plus in some additional 54 // situations. It is possible to investigate the plan using the newly added 55 // EXPLAIN statement. The QL tool is handy for such analysis. If the planner 56 // would have used an index, but no such exists, the plan includes hints in 57 // form of copy/paste ready CREATE INDEX statements. 58 // 59 // The planner is still quite simple and a lot of work on it is yet ahead. You 60 // can help this process by filling an issue with a schema and query which 61 // fails to use an index or indices when it should, in your opinion. Bonus 62 // points for including output of `ql 'explain <query>'`. 63 // 64 // 2015-05-09: The grammar of the CREATE INDEX statement now accepts an 65 // expression list instead of a single expression, which was further limited to 66 // just a column name or the built-in id(). As a side effect, composite 67 // indices are now functional. However, the values in the expression-list style 68 // index are not yet used by other statements or the statement/query planner. 69 // The composite index is useful while having UNIQUE clause to check for 70 // semantically duplicate rows before they get added to the table or when such 71 // a row is mutated using the UPDATE statement and the expression-list style 72 // index tuple of the row is thus recomputed. 73 // 74 // 2015-05-02: The Schema field of table __Table now correctly reflects any 75 // column constraints and/or defaults. Also, the (*DB).Info method now has that 76 // information provided in new ColumInfo fields NotNull, Constraint and 77 // Default. 78 // 79 // 2015-04-20: Added support for {LEFT,RIGHT,FULL} [OUTER] JOIN. 80 // 81 // 2015-04-18: Column definitions can now have constraints and defaults. 82 // Details are discussed in the "Constraints and defaults" chapter below the 83 // CREATE TABLE statement documentation. 84 // 85 // 2015-03-06: New built-in functions formatFloat and formatInt. Thanks 86 // urandom! (https://github.com/urandom) 87 // 88 // 2015-02-16: IN predicate now accepts a SELECT statement. See the updated 89 // "Predicates" section. 90 // 91 // 2015-01-17: Logical operators || and && have now alternative spellings: OR 92 // and AND (case insensitive). AND was a keyword before, but OR is a new one. 93 // This can possibly break existing queries. For the record, it's a good idea 94 // to not use any name appearing in, for example, [7] in your queries as the 95 // list of QL's keywords may expand for gaining better compatibility with 96 // existing SQL "standards". 97 // 98 // 2015-01-12: ACID guarantees were tightened at the cost of performance in 99 // some cases. The write collecting window mechanism, a formerly used 100 // implementation detail, was removed. Inserting rows one by one in a 101 // transaction is now slow. I mean very slow. Try to avoid inserting single 102 // rows in a transaction. Instead, whenever possible, perform batch updates of 103 // tens to, say thousands of rows in a single transaction. See also: 104 // http://www.sqlite.org/faq.html#q19, the discussed synchronization principles 105 // involved are the same as for QL, modulo minor details. 106 // 107 // Note: A side effect is that closing a DB before exiting an application, both 108 // for the Go API and through database/sql driver, is no more required, 109 // strictly speaking. Beware that exiting an application while there is an open 110 // (uncommitted) transaction in progress means losing the transaction data. 111 // However, the DB will not become corrupted because of not closing it. Nor 112 // that was the case before, but formerly failing to close a DB could have 113 // resulted in losing the data of the last transaction. 114 // 115 // 2014-09-21: id() now optionally accepts a single argument - a table name. 116 // 117 // 2014-09-01: Added the DB.Flush() method and the LIKE pattern matching 118 // predicate. 119 // 120 // 2014-08-08: The built in functions max and min now accept also time values. 121 // Thanks opennota! (https://github.com/opennota) 122 // 123 // 2014-06-05: RecordSet interface extended by new methods FirstRow and Rows. 124 // 125 // 2014-06-02: Indices on id() are now used by SELECT statements. 126 // 127 // 2014-05-07: Introduction of Marshal, Schema, Unmarshal. 128 // 129 // 2014-04-15: 130 // 131 // Added optional IF NOT EXISTS clause to CREATE INDEX and optional IF EXISTS 132 // clause to DROP INDEX. 133 // 134 // 2014-04-12: 135 // 136 // The column Unique in the virtual table __Index was renamed to IsUnique 137 // because the old name is a keyword. Unfortunately, this is a breaking change, 138 // sorry. 139 // 140 // 2014-04-11: Introduction of LIMIT, OFFSET. 141 // 142 // 2014-04-10: Introduction of query rewriting. 143 // 144 // 2014-04-07: Introduction of indices. 145 // 146 // Building non CGO QL 147 // 148 // QL imports zappy[8], a block-based compressor, which speeds up its 149 // performance by using a C version of the compression/decompression 150 // algorithms. If a CGO-free (pure Go) version of QL, or an app using QL, is 151 // required, please include 'purego' in the -tags option of go 152 // {build,get,install}. For example: 153 // 154 // $ go get -tags purego github.com/cznic/ql 155 // 156 // If zappy was installed before installing QL, it might be necessary to 157 // rebuild zappy first (or rebuild QL with all its dependencies using the -a 158 // option): 159 // 160 // $ touch "$GOPATH"/src/github.com/cznic/zappy/*.go 161 // $ go install -tags purego github.com/cznic/zappy 162 // $ go install github.com/cznic/ql 163 // 164 // Notation 165 // 166 // The syntax is specified using Extended Backus-Naur Form (EBNF) 167 // 168 // Production = production_name "=" [ Expression ] "." . 169 // Expression = Alternative { "|" Alternative } . 170 // Alternative = Term { Term } . 171 // Term = production_name | token [ "…" token ] | Group | Option | Repetition . 172 // Group = "(" Expression ")" . 173 // Option = "[" Expression "]" . 174 // Repetition = "{" Expression "}" . 175 // Productions are expressions constructed from terms and the following operators, in increasing precedence 176 // 177 // | alternation 178 // () grouping 179 // [] option (0 or 1 times) 180 // {} repetition (0 to n times) 181 // 182 // Lower-case production names are used to identify lexical tokens. 183 // Non-terminals are in CamelCase. Lexical tokens are enclosed in double quotes 184 // "" or back quotes ``. 185 // 186 // The form a … b represents the set of characters from a through b as 187 // alternatives. The horizontal ellipsis … is also used elsewhere in the spec 188 // to informally denote various enumerations or code snippets that are not 189 // further specified. 190 // 191 // QL source code representation 192 // 193 // QL source code is Unicode text encoded in UTF-8. The text is not 194 // canonicalized, so a single accented code point is distinct from the same 195 // character constructed from combining an accent and a letter; those are 196 // treated as two code points. For simplicity, this document will use the 197 // unqualified term character to refer to a Unicode code point in the source 198 // text. 199 // 200 // Each code point is distinct; for instance, upper and lower case letters are 201 // different characters. 202 // 203 // Implementation restriction: For compatibility with other tools, the parser 204 // may disallow the NUL character (U+0000) in the statement. 205 // 206 // Implementation restriction: A byte order mark is disallowed anywhere in QL 207 // statements. 208 // 209 // Characters 210 // 211 // The following terms are used to denote specific character classes 212 // 213 // newline = . // the Unicode code point U+000A 214 // unicode_char = . // an arbitrary Unicode code point except newline 215 // ascii_letter = "a" … "z" | "A" … "Z" . 216 // 217 // Letters and digits 218 // 219 // The underscore character _ (U+005F) is considered a letter. 220 // 221 // letter = ascii_letter | "_" . 222 // decimal_digit = "0" … "9" . 223 // octal_digit = "0" … "7" . 224 // hex_digit = "0" … "9" | "A" … "F" | "a" … "f" . 225 // 226 // Lexical elements 227 // 228 // Lexical elements are comments, tokens, identifiers, keywords, operators and 229 // delimiters, integer, floating-point, imaginary, rune and string literals and 230 // QL parameters. 231 // 232 // Comments 233 // 234 // There are three forms of comments 235 // 236 // Line comments start with the character sequence // or -- and stop at the end 237 // of the line. A line comment acts like a space. 238 // 239 // General comments start with the character sequence /* and continue through 240 // the character sequence */. A general comment acts like a space. 241 // 242 // Comments do not nest. 243 // 244 // Tokens 245 // 246 // Tokens form the vocabulary of QL. There are four classes: identifiers, 247 // keywords, operators and delimiters, and literals. White space, formed from 248 // spaces (U+0020), horizontal tabs (U+0009), carriage returns (U+000D), and 249 // newlines (U+000A), is ignored except as it separates tokens that would 250 // otherwise combine into a single token. 251 // 252 // Semicolons 253 // 254 // The formal grammar uses semicolons ";" as separators of QL statements. A 255 // single QL statement or the last QL statement in a list of statements can 256 // have an optional semicolon terminator. (Actually a separator from the 257 // following empty statement.) 258 // 259 // Identifiers 260 // 261 // Identifiers name entities such as tables or record set columns. An 262 // identifier is a sequence of one or more letters and digits. The first 263 // character in an identifier must be a letter. 264 // 265 // identifier = letter { letter | decimal_digit } . 266 // 267 // For example 268 // 269 // price 270 // _tmp42 271 // Sales 272 // 273 // No identifiers are predeclared, however note that no keyword can be used as 274 // an identifier. Identifiers starting with two underscores are used for meta 275 // data virtual tables names. For forward compatibility, users should generally 276 // avoid using any identifiers starting with two underscores. For example 277 // 278 // __Column 279 // __Column2 280 // __Index 281 // __Table 282 // 283 // Keywords 284 // 285 // The following keywords are reserved and may not be used as identifiers. 286 // 287 // ADD COLUMN false int32 ORDER uint16 288 // ALTER complex128 float int64 OUTER uint32 289 // AND complex64 float32 int8 RIGHT uint64 290 // AS CREATE float64 INTO SELECT uint8 291 // ASC DEFAULT FROM JOIN SET UNIQUE 292 // BETWEEN DELETE GROUP LEFT string UPDATE 293 // bigint DESC IF LIMIT TABLE VALUES 294 // bigrat DISTINCT IN LIKE time WHERE 295 // blob DROP INDEX NOT true 296 // bool duration INSERT NULL OR 297 // BY EXISTS int OFFSET TRUNCATE 298 // byte EXPLAIN int16 ON uint 299 // 300 // Keywords are not case sensitive. 301 // 302 // Operators and Delimiters 303 // 304 // The following character sequences represent operators, delimiters, and other 305 // special tokens 306 // 307 // + & && == != ( ) 308 // - | || < <= [ ] 309 // * ^ > >= , ; 310 // / << = . 311 // % >> ! 312 // &^ 313 // 314 // Operators consisting of more than one character are referred to by names in 315 // the rest of the documentation 316 // 317 // andand = "&&" . 318 // andnot = "&^" . 319 // lsh = "<<" . 320 // le = "<=" . 321 // eq = "==" | "=" . 322 // ge = ">=" . 323 // neq = "!=" . 324 // oror = "||" . 325 // rsh = ">>" . 326 // 327 // Integer literals 328 // 329 // An integer literal is a sequence of digits representing an integer constant. 330 // An optional prefix sets a non-decimal base: 0 for octal, 0x or 0X for 331 // hexadecimal. In hexadecimal literals, letters a-f and A-F represent values 332 // 10 through 15. 333 // 334 // int_lit = decimal_lit | octal_lit | hex_lit . 335 // decimal_lit = ( "1" … "9" ) { decimal_digit } . 336 // octal_lit = "0" { octal_digit } . 337 // hex_lit = "0" ( "x" | "X" ) hex_digit { hex_digit } . 338 // 339 // For example 340 // 341 // 42 342 // 0600 343 // 0xBadFace 344 // 1701411834604692 345 // 346 // Floating-point literals 347 // 348 // A floating-point literal is a decimal representation of a floating-point 349 // constant. It has an integer part, a decimal point, a fractional part, and an 350 // exponent part. The integer and fractional part comprise decimal digits; the 351 // exponent part is an e or E followed by an optionally signed decimal 352 // exponent. One of the integer part or the fractional part may be elided; one 353 // of the decimal point or the exponent may be elided. 354 // 355 // float_lit = decimals "." [ decimals ] [ exponent ] | 356 // decimals exponent | 357 // "." decimals [ exponent ] . 358 // decimals = decimal_digit { decimal_digit } . 359 // exponent = ( "e" | "E" ) [ "+" | "-" ] decimals . 360 // 361 // For example 362 // 363 // 0. 364 // 72.40 365 // 072.40 // == 72.40 366 // 2.71828 367 // 1.e+0 368 // 6.67428e-11 369 // 1E6 370 // .25 371 // .12345E+5 372 // 373 // Imaginary literals 374 // 375 // An imaginary literal is a decimal representation of the imaginary part of a 376 // complex constant. It consists of a floating-point literal or decimal integer 377 // followed by the lower-case letter i. 378 // 379 // imaginary_lit = (decimals | float_lit) "i" . 380 // 381 // For example 382 // 383 // 0i 384 // 011i // == 11i 385 // 0.i 386 // 2.71828i 387 // 1.e+0i 388 // 6.67428e-11i 389 // 1E6i 390 // .25i 391 // .12345E+5i 392 // 393 // Rune literals 394 // 395 // A rune literal represents a rune constant, an integer value identifying a 396 // Unicode code point. A rune literal is expressed as one or more characters 397 // enclosed in single quotes. Within the quotes, any character may appear 398 // except single quote and newline. A single quoted character represents the 399 // Unicode value of the character itself, while multi-character sequences 400 // beginning with a backslash encode values in various formats. 401 // 402 // The simplest form represents the single character within the quotes; since 403 // QL statements are Unicode characters encoded in UTF-8, multiple 404 // UTF-8-encoded bytes may represent a single integer value. For instance, the 405 // literal 'a' holds a single byte representing a literal a, Unicode U+0061, 406 // value 0x61, while 'ä' holds two bytes (0xc3 0xa4) representing a literal 407 // a-dieresis, U+00E4, value 0xe4. 408 // 409 // Several backslash escapes allow arbitrary values to be encoded as ASCII 410 // text. There are four ways to represent the integer value as a numeric 411 // constant: \x followed by exactly two hexadecimal digits; \u followed by 412 // exactly four hexadecimal digits; \U followed by exactly eight hexadecimal 413 // digits, and a plain backslash \ followed by exactly three octal digits. In 414 // each case the value of the literal is the value represented by the digits in 415 // the corresponding base. 416 // 417 // Although these representations all result in an integer, they have different 418 // valid ranges. Octal escapes must represent a value between 0 and 255 419 // inclusive. Hexadecimal escapes satisfy this condition by construction. The 420 // escapes \u and \U represent Unicode code points so within them some values 421 // are illegal, in particular those above 0x10FFFF and surrogate halves. 422 // 423 // After a backslash, certain single-character escapes represent special 424 // values 425 // 426 // \a U+0007 alert or bell 427 // \b U+0008 backspace 428 // \f U+000C form feed 429 // \n U+000A line feed or newline 430 // \r U+000D carriage return 431 // \t U+0009 horizontal tab 432 // \v U+000b vertical tab 433 // \\ U+005c backslash 434 // \' U+0027 single quote (valid escape only within rune literals) 435 // \" U+0022 double quote (valid escape only within string literals) 436 // 437 // All other sequences starting with a backslash are illegal inside rune 438 // literals. 439 // 440 // rune_lit = "'" ( unicode_value | byte_value ) "'" . 441 // unicode_value = unicode_char | little_u_value | big_u_value | escaped_char . 442 // byte_value = octal_byte_value | hex_byte_value . 443 // octal_byte_value = `\` octal_digit octal_digit octal_digit . 444 // hex_byte_value = `\` "x" hex_digit hex_digit . 445 // little_u_value = `\` "u" hex_digit hex_digit hex_digit hex_digit . 446 // big_u_value = `\` "U" hex_digit hex_digit hex_digit hex_digit 447 // hex_digit hex_digit hex_digit hex_digit . 448 // escaped_char = `\` ( "a" | "b" | "f" | "n" | "r" | "t" | "v" | `\` | "'" | `"` ) . 449 // 450 // For example 451 // 452 // 'a' 453 // 'ä' 454 // '本' 455 // '\t' 456 // '\000' 457 // '\007' 458 // '\377' 459 // '\x07' 460 // '\xff' 461 // '\u12e4' 462 // '\U00101234' 463 // 'aa' // illegal: too many characters 464 // '\xa' // illegal: too few hexadecimal digits 465 // '\0' // illegal: too few octal digits 466 // '\uDFFF' // illegal: surrogate half 467 // '\U00110000' // illegal: invalid Unicode code point 468 // 469 // String literals 470 // 471 // A string literal represents a string constant obtained from concatenating a 472 // sequence of characters. There are two forms: raw string literals and 473 // interpreted string literals. 474 // 475 // Raw string literals are character sequences between back quotes ``. Within 476 // the quotes, any character is legal except back quote. The value of a raw 477 // string literal is the string composed of the uninterpreted (implicitly 478 // UTF-8-encoded) characters between the quotes; in particular, backslashes 479 // have no special meaning and the string may contain newlines. Carriage 480 // returns inside raw string literals are discarded from the raw string value. 481 // 482 // Interpreted string literals are character sequences between double quotes 483 // "". The text between the quotes, which may not contain newlines, forms the 484 // value of the literal, with backslash escapes interpreted as they are in rune 485 // literals (except that \' is illegal and \" is legal), with the same 486 // restrictions. The three-digit octal (\nnn) and two-digit hexadecimal (\xnn) 487 // escapes represent individual bytes of the resulting string; all other 488 // escapes represent the (possibly multi-byte) UTF-8 encoding of individual 489 // characters. Thus inside a string literal \377 and \xFF represent a single 490 // byte of value 0xFF=255, while ÿ, \u00FF, \U000000FF and \xc3\xbf represent 491 // the two bytes 0xc3 0xbf of the UTF-8 encoding of character U+00FF. 492 // 493 // string_lit = raw_string_lit | interpreted_string_lit . 494 // raw_string_lit = "`" { unicode_char | newline } "`" . 495 // interpreted_string_lit = `"` { unicode_value | byte_value } `"` . 496 // 497 // For example 498 // 499 // `abc` // same as "abc" 500 // `\n 501 // \n` // same as "\\n\n\\n" 502 // "\n" 503 // "" 504 // "Hello, world!\n" 505 // "日本語" 506 // "\u65e5本\U00008a9e" 507 // "\xff\u00FF" 508 // "\uD800" // illegal: surrogate half 509 // "\U00110000" // illegal: invalid Unicode code point 510 // 511 // These examples all represent the same string 512 // 513 // "日本語" // UTF-8 input text 514 // `日本語` // UTF-8 input text as a raw literal 515 // "\u65e5\u672c\u8a9e" // the explicit Unicode code points 516 // "\U000065e5\U0000672c\U00008a9e" // the explicit Unicode code points 517 // "\xe6\x97\xa5\xe6\x9c\xac\xe8\xaa\x9e" // the explicit UTF-8 bytes 518 // 519 // If the statement source represents a character as two code points, such as a 520 // combining form involving an accent and a letter, the result will be an error 521 // if placed in a rune literal (it is not a single code point), and will appear 522 // as two code points if placed in a string literal. 523 // 524 // QL parameters 525 // 526 // Literals are assigned their values from the respective text representation 527 // at "compile" (parse) time. QL parameters provide the same functionality as 528 // literals, but their value is assigned at execution time from an expression 529 // list passed to DB.Run or DB.Execute. Using '?' or '$' is completely 530 // equivalent. 531 // 532 // ql_parameter = ( "?" | "$" ) "1" … "9" { "0" … "9" } . 533 // 534 // For example 535 // 536 // SELECT DepartmentID 537 // FROM department 538 // WHERE DepartmentID == ?1 539 // ORDER BY DepartmentName; 540 // 541 // SELECT employee.LastName 542 // FROM department, employee 543 // WHERE department.DepartmentID == $1 && employee.LastName > $2 544 // ORDER BY DepartmentID; 545 // 546 // Constants 547 // 548 // Keywords 'false' and 'true' (not case sensitive) represent the two possible 549 // constant values of type bool (also not case sensitive). 550 // 551 // Keyword 'NULL' (not case sensitive) represents an untyped constant which is 552 // assignable to any type. NULL is distinct from any other value of any type. 553 // 554 // Types 555 // 556 // A type determines the set of values and operations specific to values of 557 // that type. A type is specified by a type name. 558 // 559 // Type = "bigint" // http://golang.org/pkg/math/big/#Int 560 // | "bigrat" // http://golang.org/pkg/math/big/#Rat 561 // | "blob" // []byte 562 // | "bool" 563 // | "byte" // alias for uint8 564 // | "complex128" 565 // | "complex64" 566 // | "duration" // http://golang.org/pkg/time/#Duration 567 // | "float" // alias for float64 568 // | "float32" 569 // | "float64" 570 // | "int" // alias for int64 571 // | "int16" 572 // | "int32" 573 // | "int64" 574 // | "int8" 575 // | "rune" // alias for int32 576 // | "string" 577 // | "time" // http://golang.org/pkg/time/#Time 578 // | "uint" // alias for uint64 579 // | "uint16" 580 // | "uint32" 581 // | "uint64" 582 // | "uint8" . 583 // 584 // Named instances of the boolean, numeric, and string types are keywords. The 585 // names are not case sensitive. 586 // 587 // Note: The blob type is exchanged between the back end and the API as []byte. 588 // On 32 bit platforms this limits the size which the implementation can handle 589 // to 2G. 590 // 591 // Boolean types 592 // 593 // A boolean type represents the set of Boolean truth values denoted by the 594 // predeclared constants true and false. The predeclared boolean type is bool. 595 // 596 // Duration type 597 // 598 // A duration type represents the elapsed time between two instants as an int64 599 // nanosecond count. The representation limits the largest representable 600 // duration to approximately 290 years. 601 // 602 // Numeric types 603 // 604 // A numeric type represents sets of integer or floating-point values. The 605 // predeclared architecture-independent numeric types are 606 // 607 // uint8 the set of all unsigned 8-bit integers (0 to 255) 608 // uint16 the set of all unsigned 16-bit integers (0 to 65535) 609 // uint32 the set of all unsigned 32-bit integers (0 to 4294967295) 610 // uint64 the set of all unsigned 64-bit integers (0 to 18446744073709551615) 611 // 612 // int8 the set of all signed 8-bit integers (-128 to 127) 613 // int16 the set of all signed 16-bit integers (-32768 to 32767) 614 // int32 the set of all signed 32-bit integers (-2147483648 to 2147483647) 615 // int64 the set of all signed 64-bit integers (-9223372036854775808 to 9223372036854775807) 616 // duration the set of all signed 64-bit integers (-9223372036854775808 to 9223372036854775807) 617 // bigint the set of all integers 618 // 619 // bigrat the set of all rational numbers 620 // 621 // float32 the set of all IEEE-754 32-bit floating-point numbers 622 // float64 the set of all IEEE-754 64-bit floating-point numbers 623 // 624 // complex64 the set of all complex numbers with float32 real and imaginary parts 625 // complex128 the set of all complex numbers with float64 real and imaginary parts 626 // 627 // byte alias for uint8 628 // float alias for float64 629 // int alias for int64 630 // rune alias for int32 631 // uint alias for uint64 632 // 633 // The value of an n-bit integer is n bits wide and represented using two's 634 // complement arithmetic. 635 // 636 // Conversions are required when different numeric types are mixed in an 637 // expression or assignment. 638 // 639 // String types 640 // 641 // A string type represents the set of string values. A string value is a 642 // (possibly empty) sequence of bytes. The case insensitive keyword for the 643 // string type is 'string'. 644 // 645 // The length of a string (its size in bytes) can be discovered using the 646 // built-in function len. 647 // 648 // Time types 649 // 650 // A time type represents an instant in time with nanosecond precision. Each 651 // time has associated with it a location, consulted when computing the 652 // presentation form of the time. 653 // 654 // Predeclared functions 655 // 656 // The following functions are implicitly declared 657 // 658 // avg complex contains count date 659 // day formatTime formatFloat formatInt 660 // hasPrefix hasSuffix hour hours id 661 // imag len max min minute 662 // minutes month nanosecond nanoseconds now 663 // parseTime real second seconds since 664 // sum timeIn weekday year yearDay 665 // 666 // Expressions 667 // 668 // An expression specifies the computation of a value by applying operators and 669 // functions to operands. 670 // 671 // Operands 672 // 673 // Operands denote the elementary values in an expression. An operand may be a 674 // literal, a (possibly qualified) identifier denoting a constant or a function 675 // or a table/record set column, or a parenthesized expression. 676 // 677 // Operand = Literal | QualifiedIdent | "(" Expression ")" . 678 // Literal = "FALSE" | "NULL" | "TRUE" 679 // | float_lit | imaginary_lit | int_lit | rune_lit | string_lit 680 // | ql_parameter . 681 // 682 // Qualified identifiers 683 // 684 // A qualified identifier is an identifier qualified with a table/record set 685 // name prefix. 686 // 687 // QualifiedIdent = identifier [ "." identifier ] . 688 // 689 // For example 690 // 691 // invoice.Num // might denote column 'Num' from table 'invoice' 692 // 693 // Primary expressions 694 // 695 // Primary expression are the operands for unary and binary expressions. 696 // 697 // PrimaryExpression = Operand 698 // | Conversion 699 // | PrimaryExpression Index 700 // | PrimaryExpression Slice 701 // | PrimaryExpression Call . 702 // 703 // Call = "(" [ "*" | ExpressionList ] ")" . // * only in count(*). 704 // Index = "[" Expression "]" . 705 // Slice = "[" [ Expression ] ":" [ Expression ] "]" . 706 // 707 // For example 708 // 709 // x 710 // 2 711 // (s + ".txt") 712 // f(3.1415, true) 713 // s[i : j + 1] 714 // 715 // Index expressions 716 // 717 // A primary expression of the form 718 // 719 // s[x] 720 // 721 // denotes the element of a string indexed by x. Its type is byte. The value x 722 // is called the index. The following rules apply 723 // 724 // - The index x must be of integer type except bigint or duration; it is in 725 // range if 0 <= x < len(s), otherwise it is out of range. 726 // 727 // - A constant index must be non-negative and representable by a value of type 728 // int. 729 // 730 // - A constant index must be in range if the string a is a literal. 731 // 732 // - If x is out of range at run time, a run-time error occurs. 733 // 734 // - s[x] is the byte at index x and the type of s[x] is byte. 735 // 736 // If s is NULL or x is NULL then the result is NULL. 737 // 738 // Otherwise s[x] is illegal. 739 // 740 // Slices 741 // 742 // For a string, the primary expression 743 // 744 // s[low : high] 745 // 746 // constructs a substring. The indices low and high select which elements 747 // appear in the result. The result has indices starting at 0 and length equal 748 // to high - low. 749 // 750 // For convenience, any of the indices may be omitted. A missing low index 751 // defaults to zero; a missing high index defaults to the length of the sliced 752 // operand 753 // 754 // s[2:] // same s[2 : len(s)] 755 // s[:3] // same as s[0 : 3] 756 // s[:] // same as s[0 : len(s)] 757 // 758 // The indices low and high are in range if 0 <= low <= high <= len(a), 759 // otherwise they are out of range. A constant index must be non-negative and 760 // representable by a value of type int. If both indices are constant, they 761 // must satisfy low <= high. If the indices are out of range at run time, a 762 // run-time error occurs. 763 // 764 // Integer values of type bigint or duration cannot be used as indices. 765 // 766 // If s is NULL the result is NULL. If low or high is not omitted and is NULL 767 // then the result is NULL. 768 // 769 // Calls 770 // 771 // Given an identifier f denoting a predeclared function, 772 // 773 // f(a1, a2, … an) 774 // 775 // calls f with arguments a1, a2, … an. Arguments are evaluated before the 776 // function is called. The type of the expression is the result type of f. 777 // 778 // complex(x, y) 779 // len(name) 780 // 781 // In a function call, the function value and arguments are evaluated in the 782 // usual order. After they are evaluated, the parameters of the call are passed 783 // by value to the function and the called function begins execution. The 784 // return value of the function is passed by value when the function returns. 785 // 786 // Calling an undefined function causes a compile-time error. 787 // 788 // Operators 789 // 790 // Operators combine operands into expressions. 791 // 792 // Expression = Term { ( oror | "OR" ) Term } . 793 // 794 // ExpressionList = Expression { "," Expression } [ "," ]. 795 // Factor = PrimaryFactor { ( ge | ">" | le | "<" | neq | eq | "LIKE" ) PrimaryFactor } [ Predicate ] . 796 // PrimaryFactor = PrimaryTerm { ( "^" | "|" | "-" | "+" ) PrimaryTerm } . 797 // PrimaryTerm = UnaryExpr { ( andnot | "&" | lsh | rsh | "%" | "/" | "*" ) UnaryExpr } . 798 // Term = Factor { ( andand | "AND" ) Factor } . 799 // UnaryExpr = [ "^" | "!" | "-" | "+" ] PrimaryExpression . 800 // 801 // Comparisons are discussed elsewhere. For other binary operators, the operand 802 // types must be identical unless the operation involves shifts or untyped 803 // constants. For operations involving constants only, see the section on 804 // constant expressions. 805 // 806 // Except for shift operations, if one operand is an untyped constant and the 807 // other operand is not, the constant is converted to the type of the other 808 // operand. 809 // 810 // The right operand in a shift expression must have unsigned integer type or 811 // be an untyped constant that can be converted to unsigned integer type. If 812 // the left operand of a non-constant shift expression is an untyped constant, 813 // the type of the constant is what it would be if the shift expression were 814 // replaced by its left operand alone. 815 // 816 // Pattern matching 817 // 818 // Expressions of the form 819 // 820 // expr1 LIKE expr2 821 // 822 // yield a boolean value true if expr2, a regular expression, matches expr1 823 // (see also [6]). Both expression must be of type string. If any one of the 824 // expressions is NULL the result is NULL. 825 // 826 // Predicates 827 // 828 // Predicates are special form expressions having a boolean result type. 829 // 830 // Expressions of the form 831 // 832 // expr IN ( expr1, expr2, expr3, ... ) // case A 833 // 834 // expr NOT IN ( expr1, expr2, expr3, ... ) // case B 835 // 836 // are equivalent, including NULL handling, to 837 // 838 // expr == expr1 || expr == expr2 || expr == expr3 || ... // case A 839 // 840 // expr != expr1 && expr != expr2 && expr != expr3 && ... // case B 841 // 842 // The types of involved expressions must be comparable as defined in 843 // "Comparison operators". 844 // 845 // Another form of the IN predicate creates the expression list from a result 846 // of a SelectStmt. 847 // 848 // DELETE FROM t WHERE id() IN (SELECT id_t FROM u WHERE inactive_days > 365) 849 // 850 // The SelectStmt must select only one column. The produced expression list is 851 // resource limited by the memory available to the process. NULL values 852 // produced by the SelectStmt are ignored, but if all records of the SelectStmt 853 // are NULL the predicate yields NULL. The select statement is evaluated only 854 // once. If the type of expr is not the same as the type of the field returned 855 // by the SelectStmt then the set operation yields false. The type of the 856 // column returned by the SelectStmt must be one of the simple (non blob-like) 857 // types: 858 // 859 // bool 860 // byte // alias uint8 861 // complex128 862 // complex64 863 // float // alias float64 864 // float32 865 // float64 866 // int // alias int64 867 // int16 868 // int32 869 // int64 870 // int8 871 // rune // alias int32 872 // string 873 // uint // alias uint64 874 // uint16 875 // uint32 876 // uint64 877 // uint8 878 // 879 // Expressions of the form 880 // 881 // expr BETWEEN low AND high // case A 882 // 883 // expr NOT BETWEEN low AND high // case B 884 // 885 // are equivalent, including NULL handling, to 886 // 887 // expr >= low && expr <= high // case A 888 // 889 // expr < low || expr > high // case B 890 // 891 // The types of involved expressions must be ordered as defined in "Comparison 892 // operators". 893 // 894 // Predicate = ( 895 // [ "NOT" ] ( 896 // "IN" "(" ExpressionList ")" 897 // | "IN" "(" SelectStmt [ ";" ] ")" 898 // | "BETWEEN" PrimaryFactor "AND" PrimaryFactor 899 // ) 900 // | "IS" [ "NOT" ] "NULL" 901 // ). 902 // 903 // Expressions of the form 904 // 905 // expr IS NULL // case A 906 // 907 // expr IS NOT NULL // case B 908 // 909 // yield a boolean value true if expr does not have a specific type (case A) or 910 // if expr has a specific type (case B). In other cases the result is a boolean 911 // value false. 912 // 913 // Operator precedence 914 // 915 // Unary operators have the highest precedence. 916 // 917 // There are five precedence levels for binary operators. Multiplication 918 // operators bind strongest, followed by addition operators, comparison 919 // operators, && (logical AND), and finally || (logical OR) 920 // 921 // Precedence Operator 922 // 5 * / % << >> & &^ 923 // 4 + - | ^ 924 // 3 == != < <= > >= 925 // 2 && 926 // 1 || 927 // 928 // Binary operators of the same precedence associate from left to right. For 929 // instance, x / y * z is the same as (x / y) * z. 930 // 931 // +x 932 // 23 + 3*x[i] 933 // x <= f() 934 // ^a >> b 935 // f() || g() 936 // x == y+1 && z > 0 937 // 938 // Note that the operator precedence is reflected explicitly by the grammar. 939 // 940 // Arithmetic operators 941 // 942 // Arithmetic operators apply to numeric values and yield a result of the same 943 // type as the first operand. The four standard arithmetic operators (+, -, *, 944 // /) apply to integer, rational, floating-point, and complex types; + also 945 // applies to strings; +,- also applies to times. All other arithmetic 946 // operators apply to integers only. 947 // 948 // + sum integers, rationals, floats, complex values, strings 949 // - difference integers, rationals, floats, complex values, times 950 // * product integers, rationals, floats, complex values 951 // / quotient integers, rationals, floats, complex values 952 // % remainder integers 953 // 954 // & bitwise AND integers 955 // | bitwise OR integers 956 // ^ bitwise XOR integers 957 // &^ bit clear (AND NOT) integers 958 // 959 // << left shift integer << unsigned integer 960 // >> right shift integer >> unsigned integer 961 // 962 // Strings can be concatenated using the + operator 963 // 964 // "hi" + string(c) + " and good bye" 965 // 966 // String addition creates a new string by concatenating the operands. 967 // 968 // A value of type duration can be added to or subtracted from a value of type time. 969 // 970 // now() + duration("1h") // time after 1 hour from now 971 // duration("1h") + now() // time after 1 hour from now 972 // now() - duration("1h") // time before 1 hour from now 973 // duration("1h") - now() // illegal, negative times do not exist 974 // 975 // Times can subtracted from each other producing a value of type duration. 976 // 977 // now() - t0 // elapsed time since t0 978 // now() + now() // illegal, operator + not defined for times 979 // 980 // For two integer values x and y, the integer quotient q = x / y and remainder 981 // r = x % y satisfy the following relationships 982 // 983 // x = q*y + r and |r| < |y| 984 // 985 // with x / y truncated towards zero ("truncated division"). 986 // 987 // x y x / y x % y 988 // 5 3 1 2 989 // -5 3 -1 -2 990 // 5 -3 -1 2 991 // -5 -3 1 -2 992 // 993 // As an exception to this rule, if the dividend x is the most negative value 994 // for the int type of x, the quotient q = x / -1 is equal to x (and r = 0). 995 // 996 // x, q 997 // int8 -128 998 // int16 -32768 999 // int32 -2147483648 1000 // int64 -9223372036854775808 1001 // 1002 // If the divisor is a constant expression, it must not be zero. If the divisor 1003 // is zero at run time, a run-time error occurs. If the dividend is 1004 // non-negative and the divisor is a constant power of 2, the division may be 1005 // replaced by a right shift, and computing the remainder may be replaced by a 1006 // bitwise AND operation 1007 // 1008 // x x / 4 x % 4 x >> 2 x & 3 1009 // 11 2 3 2 3 1010 // -11 -2 -3 -3 1 1011 // 1012 // The shift operators shift the left operand by the shift count specified by 1013 // the right operand. They implement arithmetic shifts if the left operand is a 1014 // signed integer and logical shifts if it is an unsigned integer. There is no 1015 // upper limit on the shift count. Shifts behave as if the left operand is 1016 // shifted n times by 1 for a shift count of n. As a result, x << 1 is the same 1017 // as x*2 and x >> 1 is the same as x/2 but truncated towards negative 1018 // infinity. 1019 // 1020 // For integer operands, the unary operators +, -, and ^ are defined as follows 1021 // 1022 // +x is 0 + x 1023 // -x negation is 0 - x 1024 // ^x bitwise complement is m ^ x with m = "all bits set to 1" for unsigned x 1025 // and m = -1 for signed x 1026 // 1027 // For floating-point and complex numbers, +x is the same as x, while -x is the 1028 // negation of x. The result of a floating-point or complex division by zero is 1029 // not specified beyond the IEEE-754 standard; whether a run-time error occurs 1030 // is implementation-specific. 1031 // 1032 // Whenever any operand of any arithmetic operation, unary or binary, is NULL, 1033 // as well as in the case of the string concatenating operation, the result is 1034 // NULL. 1035 // 1036 // 42*NULL // the result is NULL 1037 // NULL/x // the result is NULL 1038 // "foo"+NULL // the result is NULL 1039 // 1040 // Integer overflow 1041 // 1042 // For unsigned integer values, the operations +, -, *, and << are computed 1043 // modulo 2n, where n is the bit width of the unsigned integer's type. Loosely 1044 // speaking, these unsigned integer operations discard high bits upon overflow, 1045 // and expressions may rely on ``wrap around''. 1046 // 1047 // For signed integers with a finite bit width, the operations +, -, *, and << 1048 // may legally overflow and the resulting value exists and is deterministically 1049 // defined by the signed integer representation, the operation, and its 1050 // operands. No exception is raised as a result of overflow. An evaluator may 1051 // not optimize an expression under the assumption that overflow does not 1052 // occur. For instance, it may not assume that x < x + 1 is always true. 1053 // 1054 // Integers of type bigint and rationals do not overflow but their handling is 1055 // limited by the memory resources available to the program. 1056 // 1057 // Comparison operators 1058 // 1059 // Comparison operators compare two operands and yield a boolean value. 1060 // 1061 // == equal 1062 // != not equal 1063 // < less 1064 // <= less or equal 1065 // > greater 1066 // >= greater or equal 1067 // 1068 // In any comparison, the first operand must be of same type as is the second 1069 // operand, or vice versa. 1070 // 1071 // The equality operators == and != apply to operands that are comparable. The 1072 // ordering operators <, <=, >, and >= apply to operands that are ordered. 1073 // These terms and the result of the comparisons are defined as follows 1074 // 1075 // - Boolean values are comparable. Two boolean values are equal if they are 1076 // either both true or both false. 1077 // 1078 // - Complex values are comparable. Two complex values u and v are equal if 1079 // both real(u) == real(v) and imag(u) == imag(v). 1080 // 1081 // - Integer values are comparable and ordered, in the usual way. Note that 1082 // durations are integers. 1083 // 1084 // - Floating point values are comparable and ordered, as defined by the 1085 // IEEE-754 standard. 1086 // 1087 // - Rational values are comparable and ordered, in the usual way. 1088 // 1089 // - String values are comparable and ordered, lexically byte-wise. 1090 // 1091 // - Time values are comparable and ordered. 1092 // 1093 // Whenever any operand of any comparison operation is NULL, the result is 1094 // NULL. 1095 // 1096 // Note that slices are always of type string. 1097 // 1098 // Logical operators 1099 // 1100 // Logical operators apply to boolean values and yield a boolean result. The 1101 // right operand is evaluated conditionally. 1102 // 1103 // && conditional AND p && q is "if p then q else false" 1104 // || conditional OR p || q is "if p then true else q" 1105 // ! NOT !p is "not p" 1106 // 1107 // The truth tables for logical operations with NULL values 1108 // 1109 // +-------+-------+---------+---------+ 1110 // | p | q | p || q | p && q | 1111 // +-------+-------+---------+---------+ 1112 // | true | true | *true | true | 1113 // | true | false | *true | false | 1114 // | true | NULL | *true | NULL | 1115 // | false | true | true | *false | 1116 // | false | false | false | *false | 1117 // | false | NULL | NULL | *false | 1118 // | NULL | true | true | NULL | 1119 // | NULL | false | NULL | false | 1120 // | NULL | NULL | NULL | NULL | 1121 // +-------+-------+---------+---------+ 1122 // * indicates q is not evaluated. 1123 // 1124 // +-------+-------+ 1125 // | p | !p | 1126 // +-------+-------+ 1127 // | true | false | 1128 // | false | true | 1129 // | NULL | NULL | 1130 // +-------+-------+ 1131 // 1132 // Conversions 1133 // 1134 // Conversions are expressions of the form T(x) where T is a type and x is an 1135 // expression that can be converted to type T. 1136 // 1137 // Conversion = Type "(" Expression ")" . 1138 // 1139 // A constant value x can be converted to type T in any of these cases: 1140 // 1141 // - x is representable by a value of type T. 1142 // 1143 // - x is a floating-point constant, T is a floating-point type, and x is 1144 // representable by a value of type T after rounding using IEEE 754 1145 // round-to-even rules. The constant T(x) is the rounded value. 1146 // 1147 // - x is an integer constant and T is a string type. The same rule as for 1148 // non-constant x applies in this case. 1149 // 1150 // Converting a constant yields a typed constant as result. 1151 // 1152 // float32(2.718281828) // 2.718281828 of type float32 1153 // complex128(1) // 1.0 + 0.0i of type complex128 1154 // float32(0.49999999) // 0.5 of type float32 1155 // string('x') // "x" of type string 1156 // string(0x266c) // "♬" of type string 1157 // "foo" + "bar" // "foobar" 1158 // int(1.2) // illegal: 1.2 cannot be represented as an int 1159 // string(65.0) // illegal: 65.0 is not an integer constant 1160 // 1161 // A non-constant value x can be converted to type T in any of these cases: 1162 // 1163 // - x has type T. 1164 // 1165 // - x's type and T are both integer or floating point types. 1166 // 1167 // - x's type and T are both complex types. 1168 // 1169 // - x is an integer, except bigint or duration, and T is a string type. 1170 // 1171 // Specific rules apply to (non-constant) conversions between numeric types or 1172 // to and from a string type. These conversions may change the representation 1173 // of x and incur a run-time cost. All other conversions only change the type 1174 // but not the representation of x. 1175 // 1176 // A conversion of NULL to any type yields NULL. 1177 // 1178 // Conversions between numeric types 1179 // 1180 // For the conversion of non-constant numeric values, the following rules 1181 // apply 1182 // 1183 // 1. When converting between integer types, if the value is a signed integer, 1184 // it is sign extended to implicit infinite precision; otherwise it is zero 1185 // extended. It is then truncated to fit in the result type's size. For 1186 // example, if v == uint16(0x10F0), then uint32(int8(v)) == 0xFFFFFFF0. The 1187 // conversion always yields a valid value; there is no indication of overflow. 1188 // 1189 // 2. When converting a floating-point number to an integer, the fraction is 1190 // discarded (truncation towards zero). 1191 // 1192 // 3. When converting an integer or floating-point number to a floating-point 1193 // type, or a complex number to another complex type, the result value is 1194 // rounded to the precision specified by the destination type. For instance, 1195 // the value of a variable x of type float32 may be stored using additional 1196 // precision beyond that of an IEEE-754 32-bit number, but float32(x) 1197 // represents the result of rounding x's value to 32-bit precision. Similarly, 1198 // x + 0.1 may use more than 32 bits of precision, but float32(x + 0.1) does 1199 // not. 1200 // 1201 // In all non-constant conversions involving floating-point or complex values, 1202 // if the result type cannot represent the value the conversion succeeds but 1203 // the result value is implementation-dependent. 1204 // 1205 // Conversions to and from a string type 1206 // 1207 // 1. Converting a signed or unsigned integer value to a string type yields a 1208 // string containing the UTF-8 representation of the integer. Values outside 1209 // the range of valid Unicode code points are converted to "\uFFFD". 1210 // 1211 // string('a') // "a" 1212 // string(-1) // "\ufffd" == "\xef\xbf\xbd" 1213 // string(0xf8) // "\u00f8" == "ø" == "\xc3\xb8" 1214 // string(0x65e5) // "\u65e5" == "日" == "\xe6\x97\xa5" 1215 // 1216 // 2. Converting a blob to a string type yields a string whose successive bytes 1217 // are the elements of the blob. 1218 // 1219 // string(b /* []byte{'h', 'e', 'l', 'l', '\xc3', '\xb8'} */) // "hellø" 1220 // string(b /* []byte{} */) // "" 1221 // string(b /* []byte(nil) */) // "" 1222 // 1223 // 3. Converting a value of a string type to a blob yields a blob whose 1224 // successive elements are the bytes of the string. 1225 // 1226 // blob("hellø") // []byte{'h', 'e', 'l', 'l', '\xc3', '\xb8'} 1227 // blob("") // []byte{} 1228 // 1229 // 4. Converting a value of a bigint type to a string yields a string 1230 // containing the decimal decimal representation of the integer. 1231 // 1232 // string(M9) // "2305843009213693951" 1233 // 1234 // 5. Converting a value of a string type to a bigint yields a bigint value 1235 // containing the integer represented by the string value. A prefix of “0x” or 1236 // “0X” selects base 16; the “0” prefix selects base 8, and a “0b” or “0B” 1237 // prefix selects base 2. Otherwise the value is interpreted in base 10. An 1238 // error occurs if the string value is not in any valid format. 1239 // 1240 // bigint("2305843009213693951") // M9 1241 // bigint("0x1ffffffffffffffffffffff") // M10 == 2^89-1 1242 // 1243 // 6. Converting a value of a rational type to a string yields a string 1244 // containing the decimal decimal representation of the rational in the form 1245 // "a/b" (even if b == 1). 1246 // 1247 // string(bigrat(355)/bigrat(113)) // "355/113" 1248 // 1249 // 7. Converting a value of a string type to a bigrat yields a bigrat value 1250 // containing the rational represented by the string value. The string can be 1251 // given as a fraction "a/b" or as a floating-point number optionally followed 1252 // by an exponent. An error occurs if the string value is not in any valid 1253 // format. 1254 // 1255 // bigrat("1.2e-34") 1256 // bigrat("355/113") 1257 // 1258 // 8. Converting a value of a duration type to a string returns a string 1259 // representing the duration in the form "72h3m0.5s". Leading zero units are 1260 // omitted. As a special case, durations less than one second format using a 1261 // smaller unit (milli-, micro-, or nanoseconds) to ensure that the leading 1262 // digit is non-zero. The zero duration formats as 0, with no unit. 1263 // 1264 // string(elapsed) // "1h", for example 1265 // 1266 // 9. Converting a string value to a duration yields a duration represented by 1267 // the string. A duration string is a possibly signed sequence of decimal 1268 // numbers, each with optional fraction and a unit suffix, such as "300ms", 1269 // "-1.5h" or "2h45m". Valid time units are "ns", "us" (or "µs"), "ms", "s", 1270 // "m", "h". 1271 // 1272 // duration("1m") // http://golang.org/pkg/time/#Minute 1273 // 1274 // 10. Converting a time value to a string returns the time formatted using the 1275 // format string 1276 // 1277 // "2006-01-02 15:04:05.999999999 -0700 MST" 1278 // 1279 // Order of evaluation 1280 // 1281 // When evaluating the operands of an expression or of function calls, 1282 // operations are evaluated in lexical left-to-right order. 1283 // 1284 // For example, in the evaluation of 1285 // 1286 // g(h(), i()+x[j()], c) 1287 // 1288 // the function calls and evaluation of c happen in the order h(), i(), j(), c. 1289 // 1290 // Floating-point operations within a single expression are evaluated according 1291 // to the associativity of the operators. Explicit parentheses affect the 1292 // evaluation by overriding the default associativity. In the expression x + (y 1293 // + z) the addition y + z is performed before adding x. 1294 // 1295 // Statements 1296 // 1297 // Statements control execution. 1298 // 1299 // Statement = EmptyStmt | AlterTableStmt | BeginTransactionStmt | CommitStmt 1300 // | CreateIndexStmt | CreateTableStmt | DeleteFromStmt | DropIndexStmt 1301 // | DropTableStmt | InsertIntoStmt | RollbackStmt | SelectStmt 1302 // | TruncateTableStmt | UpdateStmt | ExplainStmt. 1303 // 1304 // StatementList = Statement { ";" Statement } . 1305 // 1306 // Empty statements 1307 // 1308 // The empty statement does nothing. 1309 // 1310 // EmptyStmt = . 1311 // 1312 // ALTER TABLE 1313 // 1314 // Alter table statements modify existing tables. With the ADD clause it adds 1315 // a new column to the table. The column must not exist. With the DROP clause 1316 // it removes an existing column from a table. The column must exist and it 1317 // must be not the only (last) column of the table. IOW, there cannot be a 1318 // table with no columns. 1319 // 1320 // AlterTableStmt = "ALTER" "TABLE" TableName ( "ADD" ColumnDef | "DROP" "COLUMN" ColumnName ) . 1321 // 1322 // For example 1323 // 1324 // BEGIN TRANSACTION; 1325 // ALTER TABLE Stock ADD Qty int; 1326 // ALTER TABLE Income DROP COLUMN Taxes; 1327 // COMMIT; 1328 // 1329 // When adding a column to a table with existing data, the constraint clause of 1330 // the ColumnDef cannot be used. Adding a constrained column to an empty table 1331 // is fine. 1332 // 1333 // BEGIN TRANSACTION 1334 // 1335 // Begin transactions statements introduce a new transaction level. Every 1336 // transaction level must be eventually balanced by exactly one of COMMIT or 1337 // ROLLBACK statements. Note that when a transaction is roll-backed because of 1338 // a statement failure then no explicit balancing of the respective BEGIN 1339 // TRANSACTION is statement is required nor permitted. 1340 // 1341 // Failure to properly balance any opened transaction level may cause dead 1342 // locks and/or lose of data updated in the uppermost opened but never properly 1343 // closed transaction level. 1344 // 1345 // BeginTransactionStmt = "BEGIN" "TRANSACTION" . 1346 // 1347 // For example 1348 // 1349 // BEGIN TRANSACTION; 1350 // INSERT INTO foo VALUES (42, 3.14); 1351 // INSERT INTO foo VALUES (-1, 2.78); 1352 // COMMIT; 1353 // 1354 // Mandatory transactions 1355 // 1356 // A database cannot be updated (mutated) outside of a transaction. Statements 1357 // requiring a transaction 1358 // 1359 // ALTER TABLE 1360 // COMMIT 1361 // CREATE INDEX 1362 // CREATE TABLE 1363 // DELETE FROM 1364 // DROP INDEX 1365 // DROP TABLE 1366 // INSERT INTO 1367 // ROLLBACK 1368 // TRUNCATE TABLE 1369 // UPDATE 1370 // 1371 // A database is effectively read only outside of a transaction. Statements not 1372 // requiring a transaction 1373 // 1374 // BEGIN TRANSACTION 1375 // SELECT FROM 1376 // 1377 // COMMIT 1378 // 1379 // The commit statement closes the innermost transaction nesting level. If 1380 // that's the outermost level then the updates to the DB made by the 1381 // transaction are atomically made persistent. 1382 // 1383 // CommitStmt = "COMMIT" . 1384 // 1385 // For example 1386 // 1387 // BEGIN TRANSACTION; 1388 // INSERT INTO AccountA (Amount) VALUES ($1); 1389 // INSERT INTO AccountB (Amount) VALUES (-$1); 1390 // COMMIT; 1391 // 1392 // CREATE INDEX 1393 // 1394 // Create index statements create new indices. Index is a named projection of 1395 // ordered values of a table column to the respective records. As a special 1396 // case the id() of the record can be indexed. Index name must not be the same 1397 // as any of the existing tables and it also cannot be the same as of any 1398 // column name of the table the index is on. 1399 // 1400 // CreateIndexStmt = "CREATE" [ "UNIQUE" ] "INDEX" [ "IF" "NOT" "EXISTS" ] 1401 // IndexName "ON" TableName "(" ExpressionList ")" . 1402 // 1403 // For example 1404 // 1405 // BEGIN TRANSACTION; 1406 // CREATE TABLE Orders (CustomerID int, Date time); 1407 // CREATE INDEX OrdersID ON Orders (id()); 1408 // CREATE INDEX OrdersDate ON Orders (Date); 1409 // CREATE TABLE Items (OrderID int, ProductID int, Qty int); 1410 // CREATE INDEX ItemsOrderID ON Items (OrderID); 1411 // COMMIT; 1412 // 1413 // Now certain SELECT statements may use the indices to speed up joins and/or 1414 // to speed up record set filtering when the WHERE clause is used; or the 1415 // indices might be used to improve the performance when the ORDER BY clause is 1416 // present. 1417 // 1418 // The UNIQUE modifier requires the indexed values tuple to be index-wise 1419 // unique or have all values NULL. 1420 // 1421 // The optional IF NOT EXISTS clause makes the statement a no operation if the 1422 // index already exists. 1423 // 1424 // Simple index 1425 // 1426 // A simple index consists of only one expression which must be either a column 1427 // name or the built-in id(). 1428 // 1429 // Expression list index 1430 // 1431 // A more complex and more general index is one that consists of more than one 1432 // expression or its single expression does not qualify as a simple index. In 1433 // this case the type of all expressions in the list must be one of the non 1434 // blob-like types. 1435 // 1436 // Note: Blob-like types are blob, bigint, bigrat, time and duration. 1437 // 1438 // CREATE TABLE 1439 // 1440 // Create table statements create new tables. A column definition declares the 1441 // column name and type. Table names and column names are case sensitive. 1442 // Neither a table or an index of the same name may exist in the DB. 1443 // 1444 // CreateTableStmt = "CREATE" "TABLE" [ "IF" "NOT" "EXISTS" ] TableName 1445 // "(" ColumnDef { "," ColumnDef } [ "," ] ")" . 1446 // 1447 // ColumnDef = ColumnName Type [ "NOT" "NULL" | Expression ] [ "DEFAULT" Expression ] . 1448 // ColumnName = identifier . 1449 // TableName = identifier . 1450 // 1451 // For example 1452 // 1453 // BEGIN TRANSACTION; 1454 // CREATE TABLE department ( 1455 // DepartmentID int, 1456 // DepartmentName string, 1457 // ); 1458 // CREATE TABLE employee ( 1459 // LastName string, 1460 // DepartmentID int, 1461 // ); 1462 // COMMIT; 1463 // 1464 // The optional IF NOT EXISTS clause makes the statement a no operation if the 1465 // table already exists. 1466 // 1467 // The optional constraint clause has two forms. The first one is found in many 1468 // SQL dialects. 1469 // 1470 // BEGIN TRANSACTION; 1471 // CREATE TABLE department ( 1472 // DepartmentID int, 1473 // DepartmentName string NOT NULL, 1474 // ); 1475 // COMMIT; 1476 // 1477 // This form prevents the data in column DepartmentName to be NULL. 1478 // 1479 // The second form allows an arbitrary boolean expression to be used to 1480 // validate the column. If the value of the expression is true then the 1481 // validation succeeded. If the value of the expression is false or NULL then 1482 // the validation fails. If the value of the expression is not of type bool an 1483 // error occurs. 1484 // 1485 // BEGIN TRANSACTION; 1486 // CREATE TABLE department ( 1487 // DepartmentID int, 1488 // DepartmentName string DepartmentName IN ("HQ", "R/D", "Lab", "HR"), 1489 // ); 1490 // COMMIT; 1491 // 1492 // BEGIN TRANSACTION; 1493 // CREATE TABLE t ( 1494 // TimeStamp time TimeStamp < now() && since(TimeStamp) < duration("10s"), 1495 // Event string Event != "" && Event like "[0-9]+:[ \t]+.*", 1496 // ); 1497 // COMMIT; 1498 // 1499 // The optional DEFAULT clause is an expression which, if present, is 1500 // substituted instead of a NULL value when the colum is assigned a value. 1501 // 1502 // BEGIN TRANSACTION; 1503 // CREATE TABLE department ( 1504 // DepartmentID int, 1505 // DepartmentName string DepartmentName IN ("HQ", "R/D", "Lab", "HR") DEFAULT "HQ", 1506 // ); 1507 // COMMIT; 1508 // 1509 // Note that the constraint and/or default expressions may refer to other 1510 // columns by name: 1511 // 1512 // BEGIN TRANSACTION; 1513 // CREATE TABLE t ( 1514 // a int, 1515 // b int b > a && b < c DEFAULT (a+c)/2, 1516 // c int, 1517 // ); 1518 // COMMIT; 1519 // 1520 // 1521 // Constraints and defaults 1522 // 1523 // When a table row is inserted by the INSERT INTO statement or when a table 1524 // row is updated by the UPDATE statement, the order of operations is as 1525 // follows: 1526 // 1527 // 1. The new values of the affected columns are set and the values of all the 1528 // row columns become the named values which can be referred to in default 1529 // expressions evaluated in step 2. 1530 // 1531 // 2. If any row column value is NULL and the DEFAULT clause is present in the 1532 // column's definition, the default expression is evaluated and its value is 1533 // set as the respective column value. 1534 // 1535 // 3. The values, potentially updated, of row columns become the named values 1536 // which can be referred to in constraint expressions evaluated during step 4. 1537 // 1538 // 4. All row columns which definition has the constraint clause present will 1539 // have that constraint checked. If any constraint violation is detected, the 1540 // overall operation fails and no changes to the table are made. 1541 // 1542 // DELETE FROM 1543 // 1544 // Delete from statements remove rows from a table, which must exist. 1545 // 1546 // DeleteFromStmt = "DELETE" "FROM" TableName [ WhereClause ] . 1547 // 1548 // For example 1549 // 1550 // BEGIN TRANSACTION; 1551 // DELETE FROM DepartmentID 1552 // WHERE DepartmentName == "Ponies"; 1553 // COMMIT; 1554 // 1555 // If the WHERE clause is not present then all rows are removed and the 1556 // statement is equivalent to the TRUNCATE TABLE statement. 1557 // 1558 // DROP INDEX 1559 // 1560 // Drop index statements remove indices from the DB. The index must exist. 1561 // 1562 // DropIndexStmt = "DROP" "INDEX" [ "IF" "EXISTS" ] IndexName . 1563 // IndexName = identifier . 1564 // 1565 // For example 1566 // 1567 // BEGIN TRANSACTION; 1568 // DROP INDEX ItemsOrderID; 1569 // COMMIT; 1570 // 1571 // The optional IF EXISTS clause makes the statement a no operation if the 1572 // index does not exist. 1573 // 1574 // DROP TABLE 1575 // 1576 // Drop table statements remove tables from the DB. The table must exist. 1577 // 1578 // DropTableStmt = "DROP" "TABLE" [ "IF" "EXISTS" ] TableName . 1579 // 1580 // For example 1581 // 1582 // BEGIN TRANSACTION; 1583 // DROP TABLE Inventory; 1584 // COMMIT; 1585 // 1586 // The optional IF EXISTS clause makes the statement a no operation if the 1587 // table does not exist. 1588 // 1589 // INSERT INTO 1590 // 1591 // Insert into statements insert new rows into tables. New rows come from 1592 // literal data, if using the VALUES clause, or are a result of select 1593 // statement. In the later case the select statement is fully evaluated before 1594 // the insertion of any rows is performed, allowing to insert values calculated 1595 // from the same table rows are to be inserted into. If the ColumnNameList part 1596 // is omitted then the number of values inserted in the row must be the same as 1597 // are columns in the table. If the ColumnNameList part is present then the 1598 // number of values per row must be same as the same number of column names. 1599 // All other columns of the record are set to NULL. The type of the value 1600 // assigned to a column must be the same as is the column's type or the value 1601 // must be NULL. 1602 // 1603 // InsertIntoStmt = "INSERT" "INTO" TableName [ "(" ColumnNameList ")" ] ( Values | SelectStmt ) . 1604 // 1605 // ColumnNameList = ColumnName { "," ColumnName } [ "," ] . 1606 // Values = "VALUES" "(" ExpressionList ")" { "," "(" ExpressionList ")" } [ "," ] . 1607 // 1608 // For example 1609 // 1610 // BEGIN TRANSACTION; 1611 // INSERT INTO department (DepartmentID) VALUES (42); 1612 // 1613 // INSERT INTO department ( 1614 // DepartmentName, 1615 // DepartmentID, 1616 // ) 1617 // VALUES ( 1618 // "R&D", 1619 // 42, 1620 // ); 1621 // 1622 // INSERT INTO department VALUES 1623 // (42, "R&D"), 1624 // (17, "Sales"), 1625 // ; 1626 // COMMIT; 1627 // 1628 // BEGIN TRANSACTION; 1629 // INSERT INTO department (DepartmentName, DepartmentID) 1630 // SELECT DepartmentName+"/headquarters", DepartmentID+1000 1631 // FROM department; 1632 // COMMIT; 1633 // 1634 // If any of the columns of the table were defined using the optional 1635 // constraints clause or the optional defaults clause then those are processed 1636 // on a per row basis. The details are discussed in the "Constraints and 1637 // defaults" chapter below the CREATE TABLE statement documentation. 1638 // 1639 // Explain statement 1640 // 1641 // Explain statement produces a recordset consisting of lines of text which 1642 // describe the execution plan of a statement, if any. 1643 // 1644 // ExplainStmt = "EXPLAIN" Statement . 1645 // 1646 // For example, the QL tool treats the explain statement specially and outputs 1647 // the joined lines: 1648 // 1649 // $ ql 'create table t(i int); create table u(j int)' 1650 // $ ql 'explain select * from t, u where t.i > 42 && u.j < 314' 1651 // ┌Compute Cartesian product of 1652 // │ ┌Iterate all rows of table "t" 1653 // │ └Output field names ["i"] 1654 // │ ┌Iterate all rows of table "u" 1655 // │ └Output field names ["j"] 1656 // └Output field names ["t.i" "u.j"] 1657 // ┌Filter on t.i > 42 && u.j < 314 1658 // │Possibly useful indices 1659 // │CREATE INDEX xt_i ON t(i); 1660 // │CREATE INDEX xu_j ON u(j); 1661 // └Output field names ["t.i" "u.j"] 1662 // $ ql 'CREATE INDEX xt_i ON t(i); CREATE INDEX xu_j ON u(j);' 1663 // $ ql 'explain select * from t, u where t.i > 42 && u.j < 314' 1664 // ┌Compute Cartesian product of 1665 // │ ┌Iterate all rows of table "t" using index "xt_i" where i > 42 1666 // │ └Output field names ["i"] 1667 // │ ┌Iterate all rows of table "u" using index "xu_j" where j < 314 1668 // │ └Output field names ["j"] 1669 // └Output field names ["t.i" "u.j"] 1670 // $ ql 'explain select * from t where i > 12 and i between 10 and 20 and i < 42' 1671 // ┌Iterate all rows of table "t" using index "xt_i" where i > 12 && i <= 20 1672 // └Output field names ["i"] 1673 // $ 1674 // 1675 // The explanation may aid in uderstanding how a statement/query would be 1676 // executed and if indices are used as expected - or which indices may possibly 1677 // improve the statement performance. The create index statements above were 1678 // directly copy/pasted in the terminal from the suggestions provided by the 1679 // filter recordset pipeline part returned by the explain statement. 1680 // 1681 // If the statement has nothing special in its plan, the result is the original 1682 // statement. 1683 // 1684 // $ ql 'explain delete from t where 42 < i' 1685 // DELETE FROM t WHERE i > 42; 1686 // $ 1687 // 1688 // To get an explanation of the select statement of the IN predicate, use the EXPLAIN 1689 // statement with that particular select statement. 1690 // 1691 // $ ql 'explain select * from t where i in (select j from u where j > 0)' 1692 // ┌Iterate all rows of table "t" 1693 // └Output field names ["i"] 1694 // ┌Filter on i IN (SELECT j FROM u WHERE j > 0;) 1695 // └Output field names ["i"] 1696 // $ ql 'explain select j from u where j > 0' 1697 // ┌Iterate all rows of table "u" using index "xu_j" where j > 0 1698 // └Output field names ["j"] 1699 // $ 1700 // 1701 // ROLLBACK 1702 // 1703 // The rollback statement closes the innermost transaction nesting level 1704 // discarding any updates to the DB made by it. If that's the outermost level 1705 // then the effects on the DB are as if the transaction never happened. 1706 // 1707 // RollbackStmt = "ROLLBACK" . 1708 // 1709 // For example 1710 // 1711 // // First statement list 1712 // BEGIN TRANSACTION 1713 // SELECT * INTO tmp FROM foo; 1714 // INSERT INTO tmp SELECT * from bar; 1715 // SELECT * from tmp; 1716 // 1717 // The (temporary) record set from the last statement is returned and can be 1718 // processed by the client. 1719 // 1720 // // Second statement list 1721 // ROLLBACK; 1722 // 1723 // In this case the rollback is the same as 'DROP TABLE tmp;' but it can be a 1724 // more complex operation. 1725 // 1726 // SELECT FROM 1727 // 1728 // Select from statements produce recordsets. The optional DISTINCT modifier 1729 // ensures all rows in the result recordset are unique. Either all of the 1730 // resulting fields are returned ('*') or only those named in FieldList. 1731 // 1732 // RecordSetList is a list of table names or parenthesized select statements, 1733 // optionally (re)named using the AS clause. 1734 // 1735 // The result can be filtered using a WhereClause and orderd by the OrderBy 1736 // clause. 1737 // 1738 // SelectStmt = "SELECT" [ "DISTINCT" ] ( "*" | FieldList ) "FROM" RecordSetList 1739 // [ JoinClause ] [ WhereClause ] [ GroupByClause ] [ OrderBy ] [ Limit ] [ Offset ]. 1740 // 1741 // JoinClause = ( "LEFT" | "RIGHT" | "FULL" ) [ "OUTER" ] "JOIN" RecordSet "ON" Expression . 1742 // 1743 // RecordSet = ( TableName | "(" SelectStmt [ ";" ] ")" ) [ "AS" identifier ] . 1744 // RecordSetList = RecordSet { "," RecordSet } [ "," ] . 1745 // 1746 // For example 1747 // 1748 // SELECT * FROM Stock; 1749 // 1750 // SELECT DepartmentID 1751 // FROM department 1752 // WHERE DepartmentID == 42 1753 // ORDER BY DepartmentName; 1754 // 1755 // SELECT employee.LastName 1756 // FROM department, employee 1757 // WHERE department.DepartmentID == employee.DepartmentID 1758 // ORDER BY DepartmentID; 1759 // 1760 // If Recordset is a nested, parenthesized SelectStmt then it must be given a 1761 // name using the AS clause if its field are to be accessible in expressions. 1762 // 1763 // SELECT a.b, c.d 1764 // FROM 1765 // x AS a, 1766 // ( 1767 // SELECT * FROM y; 1768 // ) AS c 1769 // WHERE a.e > c.e; 1770 // 1771 // Fields naming rules 1772 // 1773 // A field is an named expression. Identifiers, not used as a type in 1774 // conversion or a function name in the Call clause, denote names of (other) 1775 // fields, values of which should be used in the expression. 1776 // 1777 // Field = Expression [ "AS" identifier ] . 1778 // 1779 // The expression can be named using the AS clause. If the AS clause is not 1780 // present and the expression consists solely of a field name, then that field 1781 // name is used as the name of the resulting field. Otherwise the field is 1782 // unnamed. 1783 // 1784 // For example 1785 // 1786 // SELECT 314, 42 as AUQLUE, DepartmentID, DepartmentID+1000, LastName as Name from employee; 1787 // // Fields are []string{"", "AUQLUE", "DepartmentID", "", "Name"} 1788 // 1789 // The SELECT statement can optionally enumerate the desired/resulting fields 1790 // in a list. 1791 // 1792 // FieldList = Field { "," Field } [ "," ] . 1793 // 1794 // No two identical field names can appear in the list. 1795 // 1796 // SELECT DepartmentID, LastName, DepartmentID from employee; 1797 // // duplicate field name "DepartmentID" 1798 // 1799 // SELECT DepartmentID, LastName, DepartmentID as ID2 from employee; 1800 // // works 1801 // 1802 // When more than one record set is used in the FROM clause record set list, 1803 // the result record set field names are rewritten to be qualified using 1804 // the record set names. 1805 // 1806 // SELECT * FROM employee, department; 1807 // // Fields are []string{"employee.LastName", "employee.DepartmentID", "department.DepartmentID", "department.DepartmentName" 1808 // 1809 // If a particular record set doesn't have a name, its respective fields became 1810 // unnamed. 1811 // 1812 // SELECT * FROM employee as e, ( SELECT * FROM department); 1813 // // Fields are []string{"e.LastName", "e.DepartmentID", "", "" 1814 // 1815 // SELECT * FROM employee AS e, ( SELECT * FROM department) AS d; 1816 // // Fields are []string{"e.LastName", "e.DepartmentID", "d.DepartmentID", "d.DepartmentName" 1817 // 1818 // Outer joins 1819 // 1820 // The optional JOIN clause, for example 1821 // 1822 // SELECT * 1823 // FROM a 1824 // LEFT OUTER JOIN b ON expr; 1825 // 1826 // is mostly equal to 1827 // 1828 // SELECT * 1829 // FROM a, b 1830 // WHERE expr; 1831 // 1832 // except that the rows from a which, when they appear in the cross join, never 1833 // made expr to evaluate to true, are combined with a virtual row from b, 1834 // containing all nulls, and added to the result set. For the RIGHT JOIN 1835 // variant the discussed rules are used for rows from b not satisfying expr == 1836 // true and the virtual, all-null row "comes" from a. The FULL JOIN adds the 1837 // respective rows which would be otherwise provided by the separate executions 1838 // of the LEFT JOIN and RIGHT JOIN variants. For more thorough OUTER JOIN 1839 // discussion please see the Wikipedia article at [10]. 1840 // 1841 // Recordset ordering 1842 // 1843 // Resultins rows of a SELECT statement can be optionally ordered by the ORDER 1844 // BY clause. Collating proceeds by considering the expressions in the 1845 // expression list left to right until a collating order is determined. Any 1846 // possibly remaining expressions are not evaluated. 1847 // 1848 // OrderBy = "ORDER" "BY" ExpressionList [ "ASC" | "DESC" ] . 1849 // 1850 // All of the expression values must yield an ordered type or NULL. Ordered 1851 // types are defined in "Comparison operators". Collating of elements having a 1852 // NULL value is different compared to what the comparison operators yield in 1853 // expression evaluation (NULL result instead of a boolean value). 1854 // 1855 // Below, T denotes a non NULL value of any QL type. 1856 // 1857 // NULL < T 1858 // 1859 // NULL collates before any non NULL value (is considered smaller than T). 1860 // 1861 // NULL == NULL 1862 // 1863 // Two NULLs have no collating order (are considered equal). 1864 // 1865 // Recordset filtering 1866 // 1867 // The WHERE clause restricts records considered by some statements, like 1868 // SELECT FROM, DELETE FROM, or UPDATE. 1869 // 1870 // expression value consider the record 1871 // ---------------- ------------------- 1872 // true yes 1873 // false or NULL no 1874 // 1875 // It is an error if the expression evaluates to a non null value of non bool 1876 // type. 1877 // 1878 // WhereClause = "WHERE" Expression . 1879 // 1880 // Recordset grouping 1881 // 1882 // The GROUP BY clause is used to project rows having common values into a 1883 // smaller set of rows. 1884 // 1885 // For example 1886 // 1887 // SELECT Country, sum(Qty) FROM Sales GROUP BY Country; 1888 // 1889 // SELECT Country, Product FROM Sales GROUP BY Country, Product; 1890 // 1891 // SELECT DISTINCT Country, Product FROM Sales; 1892 // 1893 // Using the GROUP BY without any aggregate functions in the selected fields is 1894 // in certain cases equal to using the DISTINCT modifier. The last two examples 1895 // above produce the same resultsets. 1896 // 1897 // GroupByClause = "GROUP BY" ColumnNameList . 1898 // 1899 // Skipping records 1900 // 1901 // The optional OFFSET clause allows to ignore first N records. For example 1902 // 1903 // SELECT * FROM t OFFSET 10; 1904 // 1905 // The above will produce only rows 11, 12, ... of the record set, if they 1906 // exist. The value of the expression must a non negative integer, but not 1907 // bigint or duration. 1908 // 1909 // Offset = "OFFSET" Expression . 1910 // 1911 // Limiting the result set size 1912 // 1913 // The optional LIMIT clause allows to ignore all but first N records. For 1914 // example 1915 // 1916 // SELECT * FROM t LIMIT 10; 1917 // 1918 // The above will return at most the first 10 records of the record set. The 1919 // value of the expression must a non negative integer, but not bigint or 1920 // duration. 1921 // 1922 // Limit = "Limit" Expression . 1923 // 1924 // The LIMIT and OFFSET clauses can be combined. For example 1925 // 1926 // SELECT * FROM t LIMIT 5 OFFSET 3; 1927 // 1928 // Considering table t has, say 10 records, the above will produce only records 1929 // 4 - 8. 1930 // 1931 // #1: Ignore 1/3 1932 // #2: Ignore 2/3 1933 // #3: Ignore 3/3 1934 // #4: Return 1/5 1935 // #5: Return 2/5 1936 // #6: Return 3/5 1937 // #7: Return 4/5 1938 // #8: Return 5/5 1939 // 1940 // After returning record #8, no more result rows/records are computed. 1941 // 1942 // Select statement evaluation order 1943 // 1944 // 1. The FROM clause is evaluated, producing a Cartesian product of its source 1945 // record sets (tables or nested SELECT statements). 1946 // 1947 // 2. If present, the JOIN cluase is evaluated on the result set of the 1948 // previous evaluation and the recordset specified by the JOIN clause. (... 1949 // JOIN Recordset ON ...) 1950 // 1951 // 3. If present, the WHERE clause is evaluated on the result set of the 1952 // previous evaluation. 1953 // 1954 // 4. If present, the GROUP BY clause is evaluated on the result set of the 1955 // previous evaluation(s). 1956 // 1957 // 5. The SELECT field expressions are evaluated on the result set of the 1958 // previous evaluation(s). 1959 // 1960 // 6. If present, the DISTINCT modifier is evaluated on the result set of the 1961 // previous evaluation(s). 1962 // 1963 // 7. If present, the ORDER BY clause is evaluated on the result set of the 1964 // previous evaluation(s). 1965 // 1966 // 8. If present, the OFFSET clause is evaluated on the result set of the 1967 // previous evaluation(s). The offset expression is evaluated once for the 1968 // first record produced by the previous evaluations. 1969 // 1970 // 9. If present, the LIMIT clause is evaluated on the result set of the 1971 // previous evaluation(s). The limit expression is evaluated once for the first 1972 // record produced by the previous evaluations. 1973 // 1974 // 1975 // TRUNCATE TABLE 1976 // 1977 // Truncate table statements remove all records from a table. The table must 1978 // exist. 1979 // 1980 // TruncateTableStmt = "TRUNCATE" "TABLE" TableName . 1981 // 1982 // For example 1983 // 1984 // BEGIN TRANSACTION 1985 // TRUNCATE TABLE department; 1986 // COMMIT; 1987 // 1988 // UPDATE 1989 // 1990 // Update statements change values of fields in rows of a table. 1991 // 1992 // UpdateStmt = "UPDATE" TableName [ "SET" ] AssignmentList [ WhereClause ] . 1993 // 1994 // AssignmentList = Assignment { "," Assignment } [ "," ] . 1995 // Assignment = ColumnName "=" Expression . 1996 // 1997 // For example 1998 // 1999 // BEGIN TRANSACTION 2000 // UPDATE department 2001 // DepartmentName = DepartmentName + " dpt.", 2002 // DepartmentID = 1000+DepartmentID, 2003 // WHERE DepartmentID < 1000; 2004 // COMMIT; 2005 // 2006 // Note: The SET clause is optional. 2007 // 2008 // If any of the columns of the table were defined using the optional 2009 // constraints clause or the optional defaults clause then those are processed 2010 // on a per row basis. The details are discussed in the "Constraints and 2011 // defaults" chapter below the CREATE TABLE statement documentation. 2012 // 2013 // System Tables 2014 // 2015 // To allow to query for DB meta data, there exist specially named tables, some 2016 // of them being virtual. 2017 // 2018 // Note: Virtual system tables may have fake table-wise unique but meaningless 2019 // and unstable record IDs. Do not apply the built-in id() to any system table. 2020 // 2021 // Tables Table 2022 // 2023 // The table __Table lists all tables in the DB. The schema is 2024 // 2025 // CREATE TABLE __Table (Name string, Schema string); 2026 // 2027 // The Schema column returns the statement to (re)create table Name. This table 2028 // is virtual. 2029 // 2030 // Columns Table 2031 // 2032 // The table __Colum lists all columns of all tables in the DB. The schema is 2033 // 2034 // CREATE TABLE __Column (TableName string, Ordinal int, Name string, Type string); 2035 // 2036 // The Ordinal column defines the 1-based index of the column in the record. 2037 // This table is virtual. 2038 // 2039 // Columns2 Table 2040 // 2041 // The table __Colum2 lists all columns of all tables in the DB which have the 2042 // constraint NOT NULL or which have a constraint expression defined or which 2043 // have a default expression defined. The schema is 2044 // 2045 // CREATE TABLE __Column2 (TableName string, Name string, NotNull bool, ConstraintExpr string, DefaultExpr string) 2046 // 2047 // It's possible to obtain a consolidated recordset for all properties of all 2048 // DB columns using 2049 // 2050 // SELECT 2051 // __Column.TableName, __Column.Ordinal, __Column.Name, __Column.Type, 2052 // __Column2.NotNull, __Column2.ConstraintExpr, __Column2.DefaultExpr, 2053 // FROM __Column 2054 // LEFT JOIN __Column2 2055 // ON __Column.TableName == __Column2.TableName && __Column.Name == __Column2.Name 2056 // ORDER BY __Column.TableName, __Column.Ordinal; 2057 // 2058 // The Name column is the column name in TableName. 2059 // 2060 // Indices table 2061 // 2062 // The table __Index lists all indices in the DB. The schema is 2063 // 2064 // CREATE TABLE __Index (TableName string, ColumnName string, Name string, IsUnique bool); 2065 // 2066 // The IsUnique columns reflects if the index was created using the optional 2067 // UNIQUE clause. This table is virtual. 2068 // 2069 // Built-in functions 2070 // 2071 // Built-in functions are predeclared. 2072 // 2073 // Average 2074 // 2075 // The built-in aggregate function avg returns the average of values of an 2076 // expression. Avg ignores NULL values, but returns NULL if all values of a 2077 // column are NULL or if avg is applied to an empty record set. 2078 // 2079 // func avg(e numeric) typeof(e) 2080 // 2081 // The column values must be of a numeric type. 2082 // 2083 // SELECT salesperson, avg(sales) FROM salesforce GROUP BY salesperson; 2084 // 2085 // Contains 2086 // 2087 // The built-in function contains returns true if substr is within s. 2088 // 2089 // func contains(s, substr string) bool 2090 // 2091 // If any argument to contains is NULL the result is NULL. 2092 // 2093 // Count 2094 // 2095 // The built-in aggregate function count returns how many times an expression 2096 // has a non NULL values or the number of rows in a record set. Note: count() 2097 // returns 0 for an empty record set. 2098 // 2099 // func count() int // The number of rows in a record set. 2100 // func count(*) int // Equivalent to count(). 2101 // func count(e expression) int // The number of cases where the expression value is not NULL. 2102 // 2103 // For example 2104 // 2105 // SELECT count() FROM department; // # of rows 2106 // 2107 // SELECT count(*) FROM department; // # of rows 2108 // 2109 // SELECT count(DepartmentID) FROM department; // # of records with non NULL field DepartmentID 2110 // 2111 // SELECT count()-count(DepartmentID) FROM department; // # of records with NULL field DepartmentID 2112 // 2113 // SELECT count(foo+bar*3) AS y FROM t; // # of cases where 'foo+bar*3' is non NULL 2114 // 2115 // Date 2116 // 2117 // Date returns the time corresponding to 2118 // 2119 // yyyy-mm-dd hh:mm:ss + nsec nanoseconds 2120 // 2121 // in the appropriate zone for that time in the given location. 2122 // 2123 // The month, day, hour, min, sec, and nsec values may be outside their usual 2124 // ranges and will be normalized during the conversion. For example, October 32 2125 // converts to November 1. 2126 // 2127 // A daylight savings time transition skips or repeats times. For example, in 2128 // the United States, March 13, 2011 2:15am never occurred, while November 6, 2129 // 2011 1:15am occurred twice. In such cases, the choice of time zone, and 2130 // therefore the time, is not well-defined. Date returns a time that is correct 2131 // in one of the two zones involved in the transition, but it does not 2132 // guarantee which. 2133 // 2134 // func date(year, month, day, hour, min, sec, nsec int, loc string) time 2135 // 2136 // A location maps time instants to the zone in use at that time. Typically, 2137 // the location represents the collection of time offsets in use in a 2138 // geographical area, such as "CEST" and "CET" for central Europe. "local" 2139 // represents the system's local time zone. "UTC" represents Universal 2140 // Coordinated Time (UTC). 2141 // 2142 // The month specifies a month of the year (January = 1, ...). 2143 // 2144 // If any argument to date is NULL the result is NULL. 2145 // 2146 // Day 2147 // 2148 // The built-in function day returns the day of the month specified by t. 2149 // 2150 // func day(t time) int 2151 // 2152 // If the argument to day is NULL the result is NULL. 2153 // 2154 // Format time 2155 // 2156 // The built-in function formatTime returns a textual representation of the 2157 // time value formatted according to layout, which defines the format by 2158 // showing how the reference time, 2159 // 2160 // Mon Jan 2 15:04:05 -0700 MST 2006 2161 // 2162 // would be displayed if it were the value; it serves as an example of the 2163 // desired output. The same display rules will then be applied to the time 2164 // value. 2165 // 2166 // func formatTime(t time, layout string) string 2167 // 2168 // If any argument to formatTime is NULL the result is NULL. 2169 // 2170 // NOTE: The string value of the time zone, like "CET" or "ACDT", is dependent 2171 // on the time zone of the machine the function is run on. For example, if the 2172 // t value is in "CET", but the machine is in "ACDT", instead of "CET" the 2173 // result is "+0100". This is the same what Go (time.Time).String() returns and 2174 // in fact formatTime directly calls t.String(). 2175 // 2176 // formatTime(date(2006, 1, 2, 15, 4, 5, 999999999, "CET")) 2177 // 2178 // returns 2179 // 2180 // 2006-01-02 15:04:05.999999999 +0100 CET 2181 // 2182 // on a machine in the CET time zone, but may return 2183 // 2184 // 2006-01-02 15:04:05.999999999 +0100 +0100 2185 // 2186 // on a machine in the ACDT zone. The time value is in both cases the same so 2187 // its ordering and comparing is correct. Only the display value can differ. 2188 // 2189 // Format numbers 2190 // 2191 // The built-in functions formatFloat and formatInt format numbers 2192 // to strings using go's number format functions in the `strconv` package. For 2193 // all three functions, only the first argument is mandatory. The default values 2194 // of the rest are shown in the examples. If the first argument is NULL, the 2195 // result is NULL. 2196 // 2197 // formatFloat(43.2[, 'g', -1, 64]) string 2198 // 2199 // returns 2200 // 2201 // "43.2" 2202 // 2203 // formatInt(-42[, 10]) string 2204 // 2205 // returns 2206 // 2207 // "-42" 2208 // 2209 // formatInt(uint32(42)[, 10]) string 2210 // 2211 // returns 2212 // 2213 // "42" 2214 // 2215 // Unlike the `strconv` equivalent, the formatInt function handles all integer 2216 // types, both signed and unsigned. 2217 // 2218 // HasPrefix 2219 // 2220 // The built-in function hasPrefix tests whether the string s begins with prefix. 2221 // 2222 // func hasPrefix(s, prefix string) bool 2223 // 2224 // If any argument to hasPrefix is NULL the result is NULL. 2225 // 2226 // HasSuffix 2227 // 2228 // The built-in function hasSuffix tests whether the string s ends with suffix. 2229 // 2230 // func hasSuffix(s, suffix string) bool 2231 // 2232 // If any argument to hasSuffix is NULL the result is NULL. 2233 // 2234 // Hour 2235 // 2236 // The built-in function hour returns the hour within the day specified by t, 2237 // in the range [0, 23]. 2238 // 2239 // func hour(t time) int 2240 // 2241 // If the argument to hour is NULL the result is NULL. 2242 // 2243 // Hours 2244 // 2245 // The built-in function hours returns the duration as a floating point number 2246 // of hours. 2247 // 2248 // func hours(d duration) float 2249 // 2250 // If the argument to hours is NULL the result is NULL. 2251 // 2252 // Record id 2253 // 2254 // The built-in function id takes zero or one arguments. If no argument is 2255 // provided, id() returns a table-unique automatically assigned numeric 2256 // identifier of type int. Ids of deleted records are not reused unless the DB 2257 // becomes completely empty (has no tables). 2258 // 2259 // func id() int 2260 // 2261 // For example 2262 // 2263 // SELECT id(), LastName 2264 // FROM employee; 2265 // 2266 // If id() without arguments is called for a row which is not a table record 2267 // then the result value is NULL. 2268 // 2269 // For example 2270 // 2271 // SELECT id(), e.LastName, e.DepartmentID, d.DepartmentID 2272 // FROM 2273 // employee AS e, 2274 // department AS d, 2275 // WHERE e.DepartmentID == d.DepartmentID; 2276 // // Will always return NULL in first field. 2277 // 2278 // SELECT e.ID, e.LastName, e.DepartmentID, d.DepartmentID 2279 // FROM 2280 // (SELECT id() AS ID, LastName, DepartmentID FROM employee) AS e, 2281 // department as d, 2282 // WHERE e.DepartmentID == d.DepartmentID; 2283 // // Will work. 2284 // 2285 // If id() has one argument it must be a table name of a table in a cross join. 2286 // 2287 // For example 2288 // 2289 // SELECT * 2290 // FROM foo, bar 2291 // WHERE bar.fooID == id(foo) 2292 // ORDER BY id(foo); 2293 // 2294 // Length 2295 // 2296 // The built-in function len takes a string argument and returns the lentgh of 2297 // the string in bytes. 2298 // 2299 // func len(s string) int 2300 // 2301 // The expression len(s) is constant if s is a string constant. 2302 // 2303 // If the argument to len is NULL the result is NULL. 2304 // 2305 // Maximum 2306 // 2307 // The built-in aggregate function max returns the largest value of an 2308 // expression in a record set. Max ignores NULL values, but returns NULL if 2309 // all values of a column are NULL or if max is applied to an empty record set. 2310 // 2311 // func max(e expression) typeof(e) // The largest value of the expression. 2312 // 2313 // The expression values must be of an ordered type. 2314 // 2315 // For example 2316 // 2317 // SELECT department, max(sales) FROM t GROUP BY department; 2318 // 2319 // Minimum 2320 // 2321 // The built-in aggregate function min returns the smallest value of an 2322 // expression in a record set. Min ignores NULL values, but returns NULL if 2323 // all values of a column are NULL or if min is applied to an empty record set. 2324 // 2325 // func min(e expression) typeof(e) // The smallest value of the expression. 2326 // 2327 // For example 2328 // 2329 // SELECT a, min(b) FROM t GROUP BY a; 2330 // 2331 // The column values must be of an ordered type. 2332 // 2333 // Minute 2334 // 2335 // The built-in function minute returns the minute offset within the hour 2336 // specified by t, in the range [0, 59]. 2337 // 2338 // func minute(t time) int 2339 // 2340 // If the argument to minute is NULL the result is NULL. 2341 // 2342 // Minutes 2343 // 2344 // The built-in function minutes returns the duration as a floating point 2345 // number of minutes. 2346 // 2347 // func minutes(d duration) float 2348 // 2349 // If the argument to minutes is NULL the result is NULL. 2350 // 2351 // Month 2352 // 2353 // The built-in function month returns the month of the year specified by t 2354 // (January = 1, ...). 2355 // 2356 // func month(t time) int 2357 // 2358 // If the argument to month is NULL the result is NULL. 2359 // 2360 // Nanosecond 2361 // 2362 // The built-in function nanosecond returns the nanosecond offset within the 2363 // second specified by t, in the range [0, 999999999]. 2364 // 2365 // func nanosecond(t time) int 2366 // 2367 // If the argument to nanosecond is NULL the result is NULL. 2368 // 2369 // Nanoseconds 2370 // 2371 // The built-in function nanoseconds returns the duration as an integer 2372 // nanosecond count. 2373 // 2374 // func nanoseconds(d duration) float 2375 // 2376 // If the argument to nanoseconds is NULL the result is NULL. 2377 // 2378 // Now 2379 // 2380 // The built-in function now returns the current local time. 2381 // 2382 // func now() time 2383 // 2384 // Parse time 2385 // 2386 // The built-in function parseTime parses a formatted string and returns the 2387 // time value it represents. The layout defines the format by showing how the 2388 // reference time, 2389 // 2390 // Mon Jan 2 15:04:05 -0700 MST 2006 2391 // 2392 // would be interpreted if it were the value; it serves as an example of the 2393 // input format. The same interpretation will then be made to the input string. 2394 // 2395 // Elements omitted from the value are assumed to be zero or, when zero is 2396 // impossible, one, so parsing "3:04pm" returns the time corresponding to Jan 2397 // 1, year 0, 15:04:00 UTC (note that because the year is 0, this time is 2398 // before the zero Time). Years must be in the range 0000..9999. The day of the 2399 // week is checked for syntax but it is otherwise ignored. 2400 // 2401 // In the absence of a time zone indicator, parseTime returns a time in UTC. 2402 // 2403 // When parsing a time with a zone offset like -0700, if the offset corresponds 2404 // to a time zone used by the current location, then parseTime uses that 2405 // location and zone in the returned time. Otherwise it records the time as 2406 // being in a fabricated location with time fixed at the given zone offset. 2407 // 2408 // When parsing a time with a zone abbreviation like MST, if the zone 2409 // abbreviation has a defined offset in the current location, then that offset 2410 // is used. The zone abbreviation "UTC" is recognized as UTC regardless of 2411 // location. If the zone abbreviation is unknown, Parse records the time as 2412 // being in a fabricated location with the given zone abbreviation and a zero 2413 // offset. This choice means that such a time can be parses and reformatted 2414 // with the same layout losslessly, but the exact instant used in the 2415 // representation will differ by the actual zone offset. To avoid such 2416 // problems, prefer time layouts that use a numeric zone offset. 2417 // 2418 // func parseTime(layout, value string) time 2419 // 2420 // If any argument to parseTime is NULL the result is NULL. 2421 // 2422 // Second 2423 // 2424 // The built-in function second returns the second offset within the minute 2425 // specified by t, in the range [0, 59]. 2426 // 2427 // func second(t time) int 2428 // 2429 // If the argument to second is NULL the result is NULL. 2430 // 2431 // Seconds 2432 // 2433 // The built-in function seconds returns the duration as a floating point 2434 // number of seconds. 2435 // 2436 // func seconds(d duration) float 2437 // 2438 // If the argument to seconds is NULL the result is NULL. 2439 // 2440 // Since 2441 // 2442 // The built-in function since returns the time elapsed since t. It is 2443 // shorthand for now()-t. 2444 // 2445 // func since(t time) duration 2446 // 2447 // If the argument to since is NULL the result is NULL. 2448 // 2449 // Sum 2450 // 2451 // The built-in aggregate function sum returns the sum of values of an 2452 // expression for all rows of a record set. Sum ignores NULL values, but 2453 // returns NULL if all values of a column are NULL or if sum is applied to an 2454 // empty record set. 2455 // 2456 // func sum(e expression) typeof(e) // The sum of the values of the expression. 2457 // 2458 // The column values must be of a numeric type. 2459 // 2460 // SELECT salesperson, sum(sales) FROM salesforce GROUP BY salesperson; 2461 // 2462 // Time in a specific zone 2463 // 2464 // The built-in function timeIn returns t with the location information set to 2465 // loc. For discussion of the loc argument please see date(). 2466 // 2467 // func timeIn(t time, loc string) time 2468 // 2469 // If any argument to timeIn is NULL the result is NULL. 2470 // 2471 // Weekday 2472 // 2473 // The built-in function weekday returns the day of the week specified by t. 2474 // Sunday == 0, Monday == 1, ... 2475 // 2476 // func weekday(t time) int 2477 // 2478 // If the argument to weekday is NULL the result is NULL. 2479 // 2480 // Year 2481 // 2482 // The built-in function year returns the year in which t occurs. 2483 // 2484 // func year(t time) int 2485 // 2486 // If the argument to year is NULL the result is NULL. 2487 // 2488 // Year day 2489 // 2490 // The built-in function yearDay returns the day of the year specified by t, in 2491 // the range [1,365] for non-leap years, and [1,366] in leap years. 2492 // 2493 // func yearDay(t time) int 2494 // 2495 // If the argument to yearDay is NULL the result is NULL. 2496 // 2497 // Manipulating complex numbers 2498 // 2499 // Three functions assemble and disassemble complex numbers. The built-in 2500 // function complex constructs a complex value from a floating-point real and 2501 // imaginary part, while real and imag extract the real and imaginary parts of 2502 // a complex value. 2503 // 2504 // complex(realPart, imaginaryPart floatT) complexT 2505 // real(complexT) floatT 2506 // imag(complexT) floatT 2507 // 2508 // The type of the arguments and return value correspond. For complex, the two 2509 // arguments must be of the same floating-point type and the return type is the 2510 // complex type with the corresponding floating-point constituents: complex64 2511 // for float32, complex128 for float64. The real and imag functions together 2512 // form the inverse, so for a complex value z, z == complex(real(z), imag(z)). 2513 // 2514 // If the operands of these functions are all constants, the return value is a 2515 // constant. 2516 // 2517 // complex(2, -2) // complex128 2518 // complex(1.0, -1.4) // complex128 2519 // float32(math.Cos(math.Pi/2)) // float32 2520 // complex(5, float32(-x)) // complex64 2521 // imag(b) // float64 2522 // real(complex(5, float32(-x))) // float32 2523 // 2524 // If any argument to any of complex, real, imag functions is NULL the result 2525 // is NULL. 2526 // 2527 // Size guarantees 2528 // 2529 // For the numeric types, the following sizes are guaranteed 2530 // 2531 // type size in bytes 2532 // 2533 // byte, uint8, int8 1 2534 // uint16, int16 2 2535 // uint32, int32, float32 4 2536 // uint, uint64, int, int64, float64, complex64 8 2537 // complex128 16 2538 // 2539 // License 2540 // 2541 // Portions of this specification page are modifications based on work[2] 2542 // created and shared by Google[3] and used according to terms described in the 2543 // Creative Commons 3.0 Attribution License[4]. 2544 // 2545 // This specification is licensed under the Creative Commons Attribution 3.0 2546 // License, and code is licensed under a BSD license[5]. 2547 // 2548 // References 2549 // 2550 // Links from the above documentation 2551 // 2552 // [1]: http://golang.org/ref/spec#Notation 2553 // [2]: http://golang.org/ref/spec 2554 // [3]: http://code.google.com/policies.html 2555 // [4]: http://creativecommons.org/licenses/by/3.0/ 2556 // [5]: http://golang.org/LICENSE 2557 // [6]: http://golang.org/pkg/regexp/#Regexp.MatchString 2558 // [7]: http://developer.mimer.com/validator/sql-reserved-words.tml 2559 // [8]: http://godoc.org/github.com/cznic/zappy 2560 // [9]: http://www.w3schools.com/sql/sql_default.asp 2561 // [10]: http://en.wikipedia.org/wiki/Join_(SQL)#Outer_join 2562 // 2563 // Implementation details 2564 // 2565 // This section is not part of the specification. 2566 // 2567 // Indices 2568 // 2569 // WARNING: The implementation of indices is new and it surely needs more time 2570 // to become mature. 2571 // 2572 // Indices are used currently used only by the WHERE clause. The following 2573 // expression patterns of 'WHERE expression' are recognized and trigger index 2574 // use. 2575 // 2576 // - WHERE c // For bool typed indexed column c 2577 // - WHERE !c // For bool typed indexed column c 2578 // - WHERE c relOp constExpr // For indexed column c 2579 // - WHERE c relOp parameter // For indexed column c 2580 // - WHERE parameter relOp c // For indexed column c 2581 // - WHERE constExpr relOp c // For indexed column c 2582 // 2583 // The relOp is one of the relation operators <, <=, ==, >=, >. For the 2584 // equality operator both operands must be of comparable types. For all other 2585 // operators both operands must be of ordered types. The constant expression is 2586 // a compile time constant expression. Some constant folding is still a TODO. 2587 // Parameter is a QL parameter ($1 etc.). 2588 // 2589 // Query rewriting 2590 // 2591 // Consider tables t and u, both with an indexed field f. The WHERE expression 2592 // doesn't comply with the above simple detected cases. 2593 // 2594 // SELECT * FROM t, u WHERE t.f < x && u.f < y; 2595 // 2596 // However, such query is now automatically rewritten to 2597 // 2598 // SELECT * FROM 2599 // (SELECT * FROM t WHERE f < x), 2600 // (SELECT * FROM u WHERE f < y); 2601 // 2602 // which will use both of the indices. The impact of using the indices can be 2603 // substantial (cf. BenchmarkCrossJoin*) if the resulting rows have low 2604 // "selectivity", ie. only few rows from both tables are selected by the 2605 // respective WHERE filtering. 2606 // 2607 // Note: Existing QL DBs can be used and indices can be added to them. However, 2608 // once any indices are present in the DB, the old QL versions cannot work with 2609 // such DB anymore. 2610 // 2611 // Benchmarks 2612 // 2613 // Running a benchmark with -v (-test.v) outputs information about the scale 2614 // used to report records/s and a brief description of the benchmark. For 2615 // example 2616 // 2617 // $ go test -run NONE -bench 'SelectMem.*1e[23]' -v 2618 // PASS 2619 // BenchmarkSelectMem1kBx1e2 50000 67680 ns/op 1477537.05 MB/s 2620 // --- BENCH: BenchmarkSelectMem1kBx1e2 2621 // all_test.go:310: 2622 // ============================================================= 2623 // NOTE: All benchmarks report records/s as 1000000 bytes/s. 2624 // ============================================================= 2625 // all_test.go:321: Having a table of 100 records, each of size 1kB, measure the performance of 2626 // SELECT * FROM t; 2627 // 2628 // BenchmarkSelectMem1kBx1e3 5000 634819 ns/op 1575251.01 MB/s 2629 // --- BENCH: BenchmarkSelectMem1kBx1e3 2630 // all_test.go:321: Having a table of 1000 records, each of size 1kB, measure the performance of 2631 // SELECT * FROM t; 2632 // 2633 // ok github.com/cznic/ql 7.496s 2634 // $ 2635 // 2636 // Running the full suite of benchmarks takes a lot of time. Use the -timeout 2637 // flag to avoid them being killed after the default time limit (10 minutes). 2638 package ql