Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the table name aliasing on INSERT so that it occurs before the column list rather than afterwards, just as it does for PostgreSQL. Add table name aliasing to UPDATE and DELETE. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
861a2e2a4895f96a5d8e1730e744983b |
User & Date: | drh 2018-04-19 11:45:16.962 |
Context
2018-04-19
| ||
13:52 | Fix a problem in the new upsert implemention, discovered by OSSFuzz. (check-in: b6d5ea59fe user: drh tags: trunk) | |
11:45 | Fix the table name aliasing on INSERT so that it occurs before the column list rather than afterwards, just as it does for PostgreSQL. Add table name aliasing to UPDATE and DELETE. (check-in: 861a2e2a48 user: drh tags: trunk) | |
2018-04-18
| ||
19:56 | Add the "sorter-reference" optimization, allowing SQLite to be configured so that some required values may be loaded from the database after external sorting occurs for SELECT statements with ORDER BY clauses that are not satisfied by database indexes. (check-in: ef74090a40 user: dan tags: trunk) | |
Changes
Changes to src/parse.y.
︙ | ︙ | |||
685 686 687 688 689 690 691 692 693 694 695 696 697 698 | %type fullname {SrcList*} %destructor fullname {sqlite3SrcListDelete(pParse->db, $$);} fullname(A) ::= nm(X). {A = sqlite3SrcListAppend(pParse->db,0,&X,0); /*A-overwrites-X*/} fullname(A) ::= nm(X) DOT nm(Y). {A = sqlite3SrcListAppend(pParse->db,0,&X,&Y); /*A-overwrites-X*/} %type joinop {int} joinop(X) ::= COMMA|JOIN. { X = JT_INNER; } joinop(X) ::= JOIN_KW(A) JOIN. {X = sqlite3JoinType(pParse,&A,0,0); /*X-overwrites-A*/} joinop(X) ::= JOIN_KW(A) nm(B) JOIN. {X = sqlite3JoinType(pParse,&A,&B,0); /*X-overwrites-A*/} | > > > > > > > > > > > > > > > | 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 | %type fullname {SrcList*} %destructor fullname {sqlite3SrcListDelete(pParse->db, $$);} fullname(A) ::= nm(X). {A = sqlite3SrcListAppend(pParse->db,0,&X,0); /*A-overwrites-X*/} fullname(A) ::= nm(X) DOT nm(Y). {A = sqlite3SrcListAppend(pParse->db,0,&X,&Y); /*A-overwrites-X*/} %type xfullname {SrcList*} %destructor xfullname {sqlite3SrcListDelete(pParse->db, $$);} xfullname(A) ::= nm(X). {A = sqlite3SrcListAppend(pParse->db,0,&X,0); /*A-overwrites-X*/} xfullname(A) ::= nm(X) DOT nm(Y). {A = sqlite3SrcListAppend(pParse->db,0,&X,&Y); /*A-overwrites-X*/} xfullname(A) ::= nm(X) DOT nm(Y) AS nm(Z). { A = sqlite3SrcListAppend(pParse->db,0,&X,&Y); /*A-overwrites-X*/ if( A ) A->a[0].zAlias = sqlite3NameFromToken(pParse->db, &Z); } xfullname(A) ::= nm(X) AS nm(Z). { A = sqlite3SrcListAppend(pParse->db,0,&X,0); /*A-overwrites-X*/ if( A ) A->a[0].zAlias = sqlite3NameFromToken(pParse->db, &Z); } %type joinop {int} joinop(X) ::= COMMA|JOIN. { X = JT_INNER; } joinop(X) ::= JOIN_KW(A) JOIN. {X = sqlite3JoinType(pParse,&A,0,0); /*X-overwrites-A*/} joinop(X) ::= JOIN_KW(A) nm(B) JOIN. {X = sqlite3JoinType(pParse,&A,&B,0); /*X-overwrites-A*/} |
︙ | ︙ | |||
796 797 798 799 800 801 802 | {A = sqlite3PExpr(pParse,TK_LIMIT,X,Y);} limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {A = sqlite3PExpr(pParse,TK_LIMIT,Y,X);} /////////////////////////// The DELETE statement ///////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT | | | | | | 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 | {A = sqlite3PExpr(pParse,TK_LIMIT,X,Y);} limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {A = sqlite3PExpr(pParse,TK_LIMIT,Y,X);} /////////////////////////// The DELETE statement ///////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3DeleteFrom(pParse,X,W,O,L); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt(W). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3DeleteFrom(pParse,X,W,0,0); } %endif %type where_opt {Expr*} %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);} where_opt(A) ::= . {A = 0;} where_opt(A) ::= WHERE expr(X). {A = X;} ////////////////////////// The UPDATE command //////////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); sqlite3Update(pParse,X,Y,W,R,O,L,0); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) where_opt(W). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); sqlite3Update(pParse,X,Y,W,R,0,0,0); } %endif |
︙ | ︙ | |||
854 855 856 857 858 859 860 | } setlist(A) ::= LP idlist(X) RP EQ expr(Y). { A = sqlite3ExprListAppendVector(pParse, 0, X, Y); } ////////////////////////// The INSERT command ///////////////////////////////// // | | < < < < < < < < | | 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 | } setlist(A) ::= LP idlist(X) RP EQ expr(Y). { A = sqlite3ExprListAppendVector(pParse, 0, X, Y); } ////////////////////////// The INSERT command ///////////////////////////////// // cmd ::= with insert_cmd(R) INTO xfullname(X) idlist_opt(F) select(S) upsert(U). { sqlite3Insert(pParse, X, S, F, R, U); } cmd ::= with insert_cmd(R) INTO xfullname(X) idlist_opt(F) DEFAULT VALUES. { sqlite3Insert(pParse, X, 0, F, R, 0); } %type upsert {Upsert*} // Because upsert only occurs at the tip end of the INSERT rule for cmd, |
︙ | ︙ |
Changes to test/delete.test.
︙ | ︙ | |||
398 399 400 401 402 403 404 405 406 407 | do_execsql_test delete-10.1 { DELETE FROM t1 WHERE a='1' AND b='2'; } do_execsql_test delete-10.2 { SELECT * FROM t1 WHERE a='1' AND b='2'; } finish_test | > > > > > > > > > > > > | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 | do_execsql_test delete-10.1 { DELETE FROM t1 WHERE a='1' AND b='2'; } do_execsql_test delete-10.2 { SELECT * FROM t1 WHERE a='1' AND b='2'; } do_execsql_test delete-11.0 { CREATE TABLE t11(a INTEGER PRIMARY KEY, b INT); WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<20) INSERT INTO t11(a,b) SELECT x, (x*17)%100 FROM cnt; SELECT * FROM t11; } {1 17 2 34 3 51 4 68 5 85 6 2 7 19 8 36 9 53 10 70 11 87 12 4 13 21 14 38 15 55 16 72 17 89 18 6 19 23 20 40} do_execsql_test delete-11.1 { DELETE FROM t11 AS xyz WHERE EXISTS(SELECT 1 FROM t11 WHERE t11.a>xyz.a AND t11.b<=xyz.b); SELECT * FROM t11; } {6 2 12 4 18 6 19 23 20 40} finish_test |
Changes to test/update.test.
︙ | ︙ | |||
504 505 506 507 508 509 510 511 512 513 514 515 516 517 | } {2 14 3 7} do_test update-11.2 { execsql { UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1); SELECT a,e FROM t1; } } {1 15 2 8} } integrity_check update-12.1 # Ticket 602. Updates should occur in the same order as the records # were discovered in the WHERE clause. # | > > > > > > > > > > > > | 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 | } {2 14 3 7} do_test update-11.2 { execsql { UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1); SELECT a,e FROM t1; } } {1 15 2 8} do_test update-11.3 { execsql { UPDATE t1 AS xyz SET e=e+1 WHERE xyz.a IN (SELECT a FROM t1); SELECT a,e FROM t1; } } {1 16 2 9} do_test update-11.4 { execsql { UPDATE t1 AS xyz SET e=e+1 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.a<xyz.a); SELECT a,e FROM t1; } } {1 16 2 10} } integrity_check update-12.1 # Ticket 602. Updates should occur in the same order as the records # were discovered in the WHERE clause. # |
︙ | ︙ |
Changes to test/upsert2.test.
︙ | ︙ | |||
40 41 42 43 44 45 46 | ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b; SELECT *, 'x' FROM t1 ORDER BY a; } {1 99 2 x 2 15 1 x 3 4 0 x} do_execsql_test upsert2-201 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(1,2),(3,4); WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99)) | | | | 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b; SELECT *, 'x' FROM t1 ORDER BY a; } {1 99 2 x 2 15 1 x 3 4 0 x} do_execsql_test upsert2-201 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(1,2),(3,4); WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99)) INSERT INTO main.t1 AS t2(a,b) SELECT a, b FROM nx WHERE true ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t2.c+1 WHERE t2.b<excluded.b; SELECT *, 'x' FROM t1 ORDER BY a; } {1 99 2 x 2 15 1 x 3 4 0 x} do_catchsql_test upsert2-202 { WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99)) INSERT INTO t1 AS t2(a,b) SELECT a, b FROM nx WHERE true ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t1.c+1 WHERE t1.b<excluded.b; } {1 {no such column: t1.c}} do_execsql_test upsert2-210 { DROP TABLE t1; CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID; INSERT INTO t1(a,b) VALUES(1,2),(3,4); WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99)) |
︙ | ︙ |
Changes to test/upsert3.test.
︙ | ︙ | |||
44 45 46 47 48 49 50 | CREATE TABLE excluded(a INT, b INT, c INT DEFAULT 0); CREATE UNIQUE INDEX excludedab ON excluded(a,b); INSERT INTO excluded(a,b) VALUES(1,2),(1,2),(3,4),(1,2),(5,6),(3,4) ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1; SELECT *, 'x' FROM excluded ORDER BY a; } {1 2 2 x 3 4 1 x 5 6 0 x} do_execsql_test upsert3-210 { | | | 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | CREATE TABLE excluded(a INT, b INT, c INT DEFAULT 0); CREATE UNIQUE INDEX excludedab ON excluded(a,b); INSERT INTO excluded(a,b) VALUES(1,2),(1,2),(3,4),(1,2),(5,6),(3,4) ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1; SELECT *, 'x' FROM excluded ORDER BY a; } {1 2 2 x 3 4 1 x 5 6 0 x} do_execsql_test upsert3-210 { INSERT INTO excluded AS base(a,b,c) VALUES(1,2,8),(1,2,3) ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1 WHERE base.c<excluded.c; SELECT *, 'x' FROM excluded ORDER BY a; } {1 2 9 x 3 4 1 x 5 6 0 x} finish_test |