Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Merge the latest trunk changes into the WinRT branch (fixes for tickets [2a5629202f] and [385a5b56b9]). |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | winrt |
Files: | files | file ages | folders |
SHA1: |
25478dcff59690a5f59c3b9660037418 |
User & Date: | mistachkin 2012-04-21 00:31:21.773 |
Context
2012-05-04
| ||
23:11 | Sync the latest trunk changes into the winRT branch. (check-in: be4ab188cf user: drh tags: winrt) | |
2012-04-21
| ||
00:31 | Merge the latest trunk changes into the WinRT branch (fixes for tickets [2a5629202f] and [385a5b56b9]). (check-in: 25478dcff5 user: mistachkin tags: winrt) | |
2012-04-20
| ||
16:59 | Do not consider a DISTINCT clause redundant unless a subset of the result-set is collectively subject to a UNIQUE constraint and it can be guaranteed that all columns of the subset are NOT NULL (either due to NOT NULL constraints WHERE clause terms). Fix for [385a5b56b9]. (check-in: 7b8548b187 user: dan tags: trunk) | |
08:23 | Merge the latest trunk changes into the WinRT branch (fixes various MSVC compiler warnings). (check-in: 294a5cca50 user: mistachkin tags: winrt) | |
Changes
Deleted art/2005osaward.gif.
cannot compute difference between binary files
Deleted art/SQLite.eps.
cannot compute difference between binary files
Deleted art/SQLite.gif.
cannot compute difference between binary files
Deleted art/SQLiteLogo3.tiff.
cannot compute difference between binary files
Deleted art/SQLite_big.gif.
cannot compute difference between binary files
Deleted art/nocopy.gif.
cannot compute difference between binary files
Deleted art/powered_by_sqlite.gif.
cannot compute difference between binary files
Deleted art/src_logo.gif.
cannot compute difference between binary files
Changes to src/where.c.
︙ | ︙ | |||
1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 | ** ** 1. The index is itself UNIQUE, and ** ** 2. All of the columns in the index are either part of the pDistinct ** list, or else the WHERE clause contains a term of the form "col=X", ** where X is a constant value. The collation sequences of the ** comparison and select-list expressions must match those of the index. */ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_None ) continue; for(i=0; i<pIdx->nColumn; i++){ int iCol = pIdx->aiColumn[i]; | > > > | | | | > | 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 | ** ** 1. The index is itself UNIQUE, and ** ** 2. All of the columns in the index are either part of the pDistinct ** list, or else the WHERE clause contains a term of the form "col=X", ** where X is a constant value. The collation sequences of the ** comparison and select-list expressions must match those of the index. ** ** 3. All of those index columns for which the WHERE clause does not ** contain a "col=X" term are subject to a NOT NULL constraint. */ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_None ) continue; for(i=0; i<pIdx->nColumn; i++){ int iCol = pIdx->aiColumn[i]; if( 0==findTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){ int iIdxCol = findIndexCol(pParse, pDistinct, iBase, pIdx, i); if( iIdxCol<0 || pTab->aCol[pIdx->aiColumn[i]].notNull==0 ){ break; } } } if( i==pIdx->nColumn ){ /* This index implies that the DISTINCT qualifier is redundant. */ return 1; } } |
︙ | ︙ | |||
1714 1715 1716 1717 1718 1719 1720 | if( j>=nTerm ){ /* All terms of the ORDER BY clause are covered by this index so ** this index can be used for sorting. */ return 1; } if( pIdx->onError!=OE_None && i==pIdx->nColumn && (wsFlags & WHERE_COLUMN_NULL)==0 | | > > > > | | | > | > | > > > | > > > | | 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 | if( j>=nTerm ){ /* All terms of the ORDER BY clause are covered by this index so ** this index can be used for sorting. */ return 1; } if( pIdx->onError!=OE_None && i==pIdx->nColumn && (wsFlags & WHERE_COLUMN_NULL)==0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){ Column *aCol = pIdx->pTable->aCol; int i; /* All terms of this index match some prefix of the ORDER BY clause, ** the index is UNIQUE, and no terms on the tail of the ORDER BY ** refer to other tables in a join. So, assuming that the index entries ** visited contain no NULL values, then this index delivers rows in ** the required order. ** ** It is not possible for any of the first nEqCol index fields to be ** NULL (since the corresponding "=" operator in the WHERE clause would ** not be true). So if all remaining index columns have NOT NULL ** constaints attached to them, we can be confident that the visited ** index entries are free of NULLs. */ for(i=nEqCol; i<pIdx->nColumn; i++){ if( aCol[pIdx->aiColumn[i]].notNull==0 ) break; } return (i==pIdx->nColumn); } return 0; } /* ** Prepare a crude estimate of the logarithm of the input value. ** The results need not be exact. This is only used for estimating |
︙ | ︙ |
Changes to test/distinct.test.
︙ | ︙ | |||
73 74 75 76 77 78 79 | do_execsql_test 1.0 { CREATE TABLE t1(a, b, c, d); CREATE UNIQUE INDEX i1 ON t1(b, c); CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase); CREATE TABLE t2(x INTEGER PRIMARY KEY, y); | | > > > > > | | > > | | > | > > | > | > | | > | 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 | do_execsql_test 1.0 { CREATE TABLE t1(a, b, c, d); CREATE UNIQUE INDEX i1 ON t1(b, c); CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase); CREATE TABLE t2(x INTEGER PRIMARY KEY, y); CREATE TABLE t3(c1 PRIMARY KEY NOT NULL, c2 NOT NULL); CREATE INDEX i3 ON t3(c2); CREATE TABLE t4(a, b NOT NULL, c NOT NULL, d NOT NULL); CREATE UNIQUE INDEX t4i1 ON t4(b, c); CREATE UNIQUE INDEX t4i2 ON t4(d COLLATE nocase); } foreach {tn noop sql} { 1.1 0 "SELECT DISTINCT b, c FROM t1" 1.2 1 "SELECT DISTINCT b, c FROM t4" 2.1 0 "SELECT DISTINCT c FROM t1 WHERE b = ?" 2.2 1 "SELECT DISTINCT c FROM t4 WHERE b = ?" 3 1 "SELECT DISTINCT rowid FROM t1" 4 1 "SELECT DISTINCT rowid, a FROM t1" 5 1 "SELECT DISTINCT x FROM t2" 6 1 "SELECT DISTINCT * FROM t2" 7 1 "SELECT DISTINCT * FROM (SELECT * FROM t2)" 8.1 0 "SELECT DISTINCT * FROM t1" 8.2 1 "SELECT DISTINCT * FROM t4" 8 0 "SELECT DISTINCT a, b FROM t1" 9 0 "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)" 10 0 "SELECT DISTINCT c FROM t1" 11 0 "SELECT DISTINCT b FROM t1" 12.1 0 "SELECT DISTINCT a, d FROM t1" 12.2 0 "SELECT DISTINCT a, d FROM t4" 13.1 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t1" 13.2 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t4" 14.1 0 "SELECT DISTINCT a, d COLLATE nocase FROM t1" 14.2 1 "SELECT DISTINCT a, d COLLATE nocase FROM t4" 15 0 "SELECT DISTINCT a, d COLLATE binary FROM t1" 16.1 0 "SELECT DISTINCT a, b, c COLLATE binary FROM t1" 16.2 1 "SELECT DISTINCT a, b, c COLLATE binary FROM t4" 16 0 "SELECT DISTINCT t1.rowid FROM t1, t2" 17 0 { /* Technically, it would be possible to detect that DISTINCT ** is a no-op in cases like the following. But SQLite does not ** do so. */ SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid } 18 1 "SELECT DISTINCT c1, c2 FROM t3" 19 1 "SELECT DISTINCT c1 FROM t3" 20 1 "SELECT DISTINCT * FROM t3" 21 0 "SELECT DISTINCT c2 FROM t3" 22 0 "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)" 23 1 "SELECT DISTINCT rowid FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)" 24 0 "SELECT DISTINCT rowid/2 FROM t1" 25 1 "SELECT DISTINCT rowid/2, rowid FROM t1" 26.1 0 "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?" 26.2 1 "SELECT DISTINCT rowid/2, b FROM t4 WHERE c = ?" } { if {$noop} { do_distinct_noop_test 1.$tn $sql } else { do_distinct_not_noop_test 1.$tn $sql } } |
︙ | ︙ |
Added test/tkt-2a5629202f.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | # 2012 April 19 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # The tests in this file were used while developing the SQLite 4 code. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix tkt-2a5629202f # This procedure executes the SQL. Then it checks to see if the OP_Sort # opcode was executed. If an OP_Sort did occur, then "sort" is appended # to the result. If no OP_Sort happened, then "nosort" is appended. # # This procedure is used to check to make sure sorting is or is not # occurring as expected. # proc cksort {sql} { set data [execsql $sql] if {[db status sort]} {set x sort} {set x nosort} lappend data $x return $data } do_execsql_test 1.1 { CREATE TABLE t8(b TEXT, c TEXT); INSERT INTO t8 VALUES('a', 'one'); INSERT INTO t8 VALUES('b', 'two'); INSERT INTO t8 VALUES(NULL, 'three'); INSERT INTO t8 VALUES(NULL, 'four'); } do_execsql_test 1.2 { SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c } {null/four null/three a/one b/two} do_execsql_test 1.3 { CREATE UNIQUE INDEX i1 ON t8(b); SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c } {null/four null/three a/one b/two} #------------------------------------------------------------------------- # do_execsql_test 2.1 { CREATE TABLE t2(a, b NOT NULL, c); CREATE UNIQUE INDEX t2ab ON t2(a, b); CREATE UNIQUE INDEX t2ba ON t2(b, a); } do_test 2.2 { cksort { SELECT * FROM t2 WHERE a = 10 ORDER BY a, b, c } } {nosort} do_test 2.3 { cksort { SELECT * FROM t2 WHERE b = 10 ORDER BY a, b, c } } {sort} do_test 2.4 { cksort { SELECT * FROM t2 WHERE a IS NULL ORDER BY a, b, c } } {sort} finish_test |
Added test/tkt-385a5b56b9.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | # 2012 April 02 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # The tests in this file were used while developing the SQLite 4 code. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix tkt-385a5b56b9 do_execsql_test 1.0 { CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(1, NULL); INSERT INTO t1 VALUES(2, NULL); INSERT INTO t1 VALUES(1, NULL); } do_execsql_test 1.1 { SELECT DISTINCT x, y FROM t1 } {1 {} 2 {}} do_execsql_test 1.2 { CREATE UNIQUE INDEX i1 ON t1(x, y) } do_execsql_test 1.3 { SELECT DISTINCT x, y FROM t1 } {1 {} 2 {}} #------------------------------------------------------------------------- do_execsql_test 2.0 { CREATE TABLE t2(x, y NOT NULL); CREATE UNIQUE INDEX t2x ON t2(x); CREATE UNIQUE INDEX t2y ON t2(y); } do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)} } do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } { 0 0 0 {SCAN TABLE t2 (~1000000 rows)} } do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } { 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)} } do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } { 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)} } finish_test |
Changes to test/where.test.
︙ | ︙ | |||
1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 | } {1/1 1/4 4/1 4/4 nosort} do_test where-14.4 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC } } {1/1 1/4 4/1 4/4 nosort} do_test where-14.5 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b } | > | > | | 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 | } {1/1 1/4 4/1 4/4 nosort} do_test where-14.4 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC } } {1/1 1/4 4/1 4/4 nosort} do_test where-14.5 { # This test case changed from "nosort" to "sort". See ticket 2a5629202f. cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b } } {4/1 4/4 1/1 1/4 sort} do_test where-14.6 { # This test case changed from "nosort" to "sort". See ticket 2a5629202f. cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC } } {4/1 4/4 1/1 1/4 sort} do_test where-14.7 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b } } {4/1 4/4 1/1 1/4 sort} do_test where-14.7.1 { cksort { |
︙ | ︙ |