Overview
| SHA1 Hash: | 4f7dfc32aad1c552d09dc8fefb40cc3656d74883 |
|---|---|
| Date: | 2012-11-02 23:42:50 |
| User: | drh |
| Comment: | Merge the fix for ticket [bfbf38e5e9956ac6] into trunk. |
Tags And Properties
- branch=trunk inherited from [704b122e53]
- sym-trunk inherited from [704b122e53]
Changes
Changes to src/expr.c
4026 } 4026 } 4027 /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry 4027 /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry 4028 */ 4028 */ 4029 assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) ); 4029 assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) ); 4030 ExprSetIrreducible(pExpr); 4030 ExprSetIrreducible(pExpr); 4031 pExpr->iAgg = (i16)i; 4031 pExpr->iAgg = (i16)i; 4032 pExpr->pAggInfo = pAggInfo; 4032 pExpr->pAggInfo = pAggInfo; 4033 } < 4034 return WRC_Prune; | 4033 return WRC_Prune; > 4034 }else{ > 4035 return WRC_Continue; > 4036 } 4035 } 4037 } 4036 } 4038 } 4037 return WRC_Continue; 4039 return WRC_Continue; 4038 } 4040 } 4039 static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){ 4041 static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){ 4040 UNUSED_PARAMETER(pWalker); 4042 UNUSED_PARAMETER(pWalker); 4041 UNUSED_PARAMETER(pSelect); 4043 UNUSED_PARAMETER(pSelect);
Changes to test/aggnested.test
63 REPLACE INTO t2 VALUES(2,99,999,9999); 63 REPLACE INTO t2 VALUES(2,99,999,9999); 64 SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2), 64 SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2), 65 t1.* 65 t1.* 66 FROM t1; 66 FROM t1; 67 } 67 } 68 } {A,B,B 3 33 333 3333} 68 } {A,B,B 3 33 333 3333} 69 db2 close 69 db2 close 70 70 > 71 ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############ > 72 # > 73 # This first test case is the original problem report: > 74 do_test aggnested-3.0 { > 75 db eval { > 76 CREATE TABLE AAA ( > 77 aaa_id INTEGER PRIMARY KEY AUTOINCREMENT > 78 ); > 79 CREATE TABLE RRR ( > 80 rrr_id INTEGER PRIMARY KEY AUTOINCREMENT, > 81 rrr_date INTEGER NOT NULL, > 82 rrr_aaa INTEGER > 83 ); > 84 CREATE TABLE TTT ( > 85 ttt_id INTEGER PRIMARY KEY AUTOINCREMENT, > 86 target_aaa INTEGER NOT NULL, > 87 source_aaa INTEGER NOT NULL > 88 ); > 89 insert into AAA (aaa_id) values (2); > 90 insert into TTT (ttt_id, target_aaa, source_aaa) > 91 values (4469, 2, 2); > 92 insert into TTT (ttt_id, target_aaa, source_aaa) > 93 values (4476, 2, 1); > 94 insert into RRR (rrr_id, rrr_date, rrr_aaa) > 95 values (0, 0, NULL); > 96 insert into RRR (rrr_id, rrr_date, rrr_aaa) > 97 values (2, 4312, 2); > 98 SELECT i.aaa_id, > 99 (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END) > 100 FROM TTT t > 101 ) AS segfault > 102 FROM > 103 (SELECT curr.rrr_aaa as aaa_id > 104 FROM RRR curr > 105 -- you also can comment out the next line > 106 -- it causes segfault to happen after one row is outputted > 107 INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id) > 108 LEFT JOIN RRR r ON (r.rrr_id <> 0 AND r.rrr_date < curr.rrr_date) > 109 GROUP BY curr.rrr_id > 110 HAVING r.rrr_date IS NULL > 111 ) i; > 112 } > 113 } {2 1} > 114 > 115 # Further variants of the test case, as found in the ticket > 116 # > 117 do_test aggnested-3.1 { > 118 db eval { > 119 DROP TABLE IF EXISTS t1; > 120 DROP TABLE IF EXISTS t2; > 121 CREATE TABLE t1 ( > 122 id1 INTEGER PRIMARY KEY AUTOINCREMENT, > 123 value1 INTEGER > 124 ); > 125 INSERT INTO t1 VALUES(4469,2),(4476,1); > 126 CREATE TABLE t2 ( > 127 id2 INTEGER PRIMARY KEY AUTOINCREMENT, > 128 value2 INTEGER > 129 ); > 130 INSERT INTO t2 VALUES(0,1),(2,2); > 131 SELECT > 132 (SELECT sum(value2==xyz) FROM t2) > 133 FROM > 134 (SELECT curr.value1 as xyz > 135 FROM t1 AS curr LEFT JOIN t1 AS other > 136 GROUP BY curr.id1); > 137 } > 138 } {1 1} > 139 do_test aggnested-3.2 { > 140 db eval { > 141 DROP TABLE IF EXISTS t1; > 142 DROP TABLE IF EXISTS t2; > 143 CREATE TABLE t1 ( > 144 id1 INTEGER, > 145 value1 INTEGER, > 146 x1 INTEGER > 147 ); > 148 INSERT INTO t1 VALUES(4469,2,98),(4469,1,99),(4469,3,97); > 149 CREATE TABLE t2 ( > 150 value2 INTEGER > 151 ); > 152 INSERT INTO t2 VALUES(1); > 153 SELECT > 154 (SELECT sum(value2==xyz) FROM t2) > 155 FROM > 156 (SELECT value1 as xyz, max(x1) AS pqr > 157 FROM t1 > 158 GROUP BY id1); > 159 } > 160 } {0} > 161 do_test aggnested-3.3 { > 162 db eval { > 163 DROP TABLE IF EXISTS t1; > 164 DROP TABLE IF EXISTS t2; > 165 CREATE TABLE t1(id1, value1); > 166 INSERT INTO t1 VALUES(4469,2),(4469,1); > 167 CREATE TABLE t2 (value2); > 168 INSERT INTO t2 VALUES(1); > 169 SELECT (SELECT sum(value2=value1) FROM t2), max(value1) > 170 FROM t1 > 171 GROUP BY id1; > 172 } > 173 } {0 2} > 174 > 175 # A batch of queries all doing approximately the same operation involving > 176 # two nested aggregate queries. > 177 # > 178 do_test aggnested-3.11 { > 179 db eval { > 180 DROP TABLE IF EXISTS t1; > 181 DROP TABLE IF EXISTS t2; > 182 CREATE TABLE t1(id1, value1); > 183 INSERT INTO t1 VALUES(4469,12),(4469,11),(4470,34); > 184 CREATE INDEX t1id1 ON t1(id1); > 185 CREATE TABLE t2 (value2); > 186 INSERT INTO t2 VALUES(12),(34),(34); > 187 INSERT INTO t2 SELECT value2 FROM t2; > 188 > 189 SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=max(value1)) > 190 FROM t1 > 191 GROUP BY id1; > 192 } > 193 } {12 2 34 4} > 194 do_test aggnested-3.12 { > 195 db eval { > 196 SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=value1) > 197 FROM t1 > 198 GROUP BY id1; > 199 } > 200 } {12 2 34 4} > 201 do_test aggnested-3.13 { > 202 db eval { > 203 SELECT value1, (SELECT sum(value2=value1) FROM t2) > 204 FROM t1; > 205 } > 206 } {12 2 11 0 34 4} > 207 do_test aggnested-3.14 { > 208 db eval { > 209 SELECT value1, (SELECT sum(value2=value1) FROM t2) > 210 FROM t1 > 211 WHERE value1 IN (SELECT max(value1) FROM t1 GROUP BY id1); > 212 } > 213 } {12 2 34 4} > 214 do_test aggnested-3.15 { > 215 # FIXME: If case 3.16 works, then this case really ought to work too... > 216 catchsql { > 217 SELECT max(value1), (SELECT sum(value2=max(value1)) FROM t2) > 218 FROM t1 > 219 GROUP BY id1; > 220 } > 221 } {1 {misuse of aggregate function max()}} > 222 do_test aggnested-3.16 { > 223 db eval { > 224 SELECT max(value1), (SELECT sum(value2=value1) FROM t2) > 225 FROM t1 > 226 GROUP BY id1; > 227 } > 228 } {12 2 34 4} > 229 > 230 71 finish_test 231 finish_test