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