SQLite

Check-in [7d9bd22c07]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem causing the affinity of sub-select row-value elements to be ignored in some contextes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 7d9bd22c0715ede2592ee1fa7ebc215aded1ca1b
User & Date: dan 2016-09-03 19:52:12.432
References
2016-09-06
16:33
Simplify the fix in commit [7d9bd22c]. (check-in: bd5a342008 user: dan tags: rowvalue)
Context
2016-09-05
12:02
Catch vector size mismatch problems during name resolution to avoid later problems. (Closed-Leaf check-in: 56562a0346 user: drh tags: early-vector-size-check)
09:44
Fix a crash that could occur under certain circumstances if the vectors on either side of a comparison operator were of a different size. (check-in: 42670935ab user: dan tags: rowvalue)
2016-09-03
19:52
Fix a problem causing the affinity of sub-select row-value elements to be ignored in some contextes. (check-in: 7d9bd22c07 user: dan tags: rowvalue)
16:24
Merge the fuzzershell enhancement from trunk. (check-in: ed20604848 user: drh tags: rowvalue)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
︙︙
56
57
58
59
60
61
62






63
64
65
66
67
68
69

  if( op==TK_CAST ){
    assert( !ExprHasProperty(pExpr, EP_IntValue) );
    return sqlite3AffinityType(pExpr->u.zToken, 0);
  }
#endif
  if( op==TK_AGG_COLUMN || op==TK_COLUMN ){
    return sqlite3TableColumnAffinity(pExpr->pTab, pExpr->iColumn);






  }
  return pExpr->affinity;
}

/*
** Set the collating sequence for expression pExpr to be the collating
** sequence named by pToken.   Return a pointer to a new Expr node that








>
>
>
>
>
>








56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

  if( op==TK_CAST ){
    assert( !ExprHasProperty(pExpr, EP_IntValue) );
    return sqlite3AffinityType(pExpr->u.zToken, 0);
  }
#endif
  if( op==TK_AGG_COLUMN || op==TK_COLUMN ){
    return sqlite3TableColumnAffinity(pExpr->pTab, pExpr->iColumn);
  }
  if( op==TK_SELECT_COLUMN ){
    assert( pExpr->pLeft->flags&EP_xIsSelect );
    return sqlite3ExprAffinity(
        pExpr->pLeft->x.pSelect->pEList->a[pExpr->iColumn].pExpr
    );
  }
  return pExpr->affinity;
}

/*
** Set the collating sequence for expression pExpr to be the collating
** sequence named by pToken.   Return a pointer to a new Expr node that
︙︙
Changes to test/rowvalue9.test.
︙︙
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
144

do_execsql_test 1.5.3 {
  SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a2
} {3 14 15 92}

do_execsql_test 1.6.1 {
  SELECT a1.rowid FROM a1 WHERE (a, b) IN (SELECT x, y FROM a2)
} {15 92}

do_execsql_test 1.6.2 {
  SELECT a1.rowid FROM a1, a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE a=x AND b=y
  )
} {3 14 15 92 3 14 15 92}



do_execsql_test 2.1 {
  CREATE TABLE b1(a TEXT);
  CREATE TABLE b2(x BLOB);

  INSERT INTO b1 VALUES(1);
  INSERT INTO b2 VALUES(1);
}

do_execsql_test 2.2 { SELECT * FROM b1, b2 WHERE a=x; } {}
do_execsql_test 2.3 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {}

do_execsql_test 2.4 {
  CREATE UNIQUE INDEX b1a ON b1(a);
}
do_execsql_test 2.5 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {}



do_execsql_test 3.1 {
  CREATE TABLE c1(a INTEGER, b TEXT);
  INSERT INTO c1 VALUES(1, 1);

  CREATE TABLE c2(x BLOB, y BLOB);
  INSERT INTO c2 VALUES(1, 1);
}
do_execsql_test 3.2 {
  SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2)
} {}
do_execsql_test 3.3 {
  CREATE UNIQUE INDEX c1ab ON c1(a, b);
  SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2)
} {}













































do_execsql_test 4.0 {

  CREATE TABLE d1(a TEXT);


  CREATE TABLE d2(x BLOB);





  INSERT INTO d1 VALUES(1);



  INSERT INTO d2 VALUES(1);



}
do_execsql_test 4.1 { 
  SELECT * FROM d1 WHERE a IN (SELECT x FROM b2) 
} {}

do_execsql_test 4.2 { 




  CREATE UNIQUE INDEX d1a ON d1(a);



}
do_execsql_test 4.3 { 
  SELECT * FROM d1 WHERE a IN (SELECT x FROM d2) 


} {}


finish_test









<






>
>



<



<


|
|
<
|
<
>
>
|



<










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>
|
>
>
|
>
>
>
>
>
|
>
>
>
|
>
>
>
|
|
|
|
>
|
>
>
>
>
|
>
>
>
|
|
|
>
>
|
|




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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210

do_execsql_test 1.5.3 {
  SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a2
} {3 14 15 92}

do_execsql_test 1.6.1 {
  SELECT a1.rowid FROM a1 WHERE (a, b) IN (SELECT x, y FROM a2)
} {15 92}

do_execsql_test 1.6.2 {
  SELECT a1.rowid FROM a1, a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE a=x AND b=y
  )
} {3 14 15 92 3 14 15 92}

# Test that [199df416] is fixed.
#
do_execsql_test 2.1 {
  CREATE TABLE b1(a TEXT);
  CREATE TABLE b2(x BLOB);

  INSERT INTO b1 VALUES(1);
  INSERT INTO b2 VALUES(1);
}

do_execsql_test 2.2 { SELECT * FROM b1, b2 WHERE a=x; } {}
do_execsql_test 2.3 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {}
do_execsql_test 2.4 { CREATE UNIQUE INDEX b1a ON b1(a); }
do_execsql_test 2.5 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {}



# Test that a multi-column version of the query that revealed problem 
# [199df416] also works.
#
do_execsql_test 3.1 {
  CREATE TABLE c1(a INTEGER, b TEXT);
  INSERT INTO c1 VALUES(1, 1);

  CREATE TABLE c2(x BLOB, y BLOB);
  INSERT INTO c2 VALUES(1, 1);
}
do_execsql_test 3.2 {
  SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2)
} {}
do_execsql_test 3.3 {
  CREATE UNIQUE INDEX c1ab ON c1(a, b);
  SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2)
} {}
do_execsql_test 3.4 {
  SELECT * FROM c1 WHERE (a, +b) IN (SELECT x, y FROM c2)
} {}

do_execsql_test 3.5 {
  SELECT c1.rowid FROM c1 WHERE b = (SELECT y FROM c2);
} {}
do_execsql_test 3.6 {
  SELECT c1.rowid FROM c1 WHERE (a, b) = (SELECT x, y FROM c2);
} {}


#-------------------------------------------------------------------------
#
do_execsql_test 4.0 {
  CREATE TABLE d1(a TEXT, b INTEGER, c NUMERIC);
  CREATE TABLE d2(x BLOB, y BLOB);

  INSERT INTO d1 VALUES(1, 1, 1);
  INSERT INTO d1 VALUES(2, 2, 2);
  INSERT INTO d1 VALUES(3, 3, 3);
  INSERT INTO d1 VALUES(4, 4, 4);

  INSERT INTO d2 VALUES (1, 1);
  INSERT INTO d2 VALUES (2, '2');
  INSERT INTO d2 VALUES ('3', 3);
  INSERT INTO d2 VALUES ('4', '4');
}

foreach {tn idx} {
  1 {}
  2 { CREATE INDEX idx ON d1(a) }
  3 { CREATE INDEX idx ON d1(a, c) }
  4 { CREATE INDEX idx ON d1(c) }
  5 { CREATE INDEX idx ON d1(c, a) }

  6 { 
    CREATE INDEX idx ON d1(c, a) ;
    CREATE INDEX idx1 ON d2(x, y);
  }

  7 { 
    CREATE INDEX idx ON d1(c, a) ;
    CREATE UNIQUE INDEX idx2 ON d2(x, y) ;
  }

  8 { 
    CREATE INDEX idx ON d1(c) ;
    CREATE UNIQUE INDEX idx2 ON d2(x);
  }

} {
  execsql { DROP INDEX IF EXISTS idx } 
  execsql { DROP INDEX IF EXISTS idx2 } 
  execsql { DROP INDEX IF EXISTS idx3 } 
  execsql $idx

  do_execsql_test 4.$tn.1 {
    SELECT rowid FROM d1 WHERE (a, c) IN (SELECT x, y FROM d2);
  } {3 4}

  do_execsql_test 4.$tn.2 {
    SELECT rowid FROM d1 WHERE (c, a) IN (SELECT x, y FROM d2);
  } {2 4}

  do_execsql_test 4.$tn.3 {
    SELECT rowid FROM d1 WHERE (+c, a) IN (SELECT x, y FROM d2);
  } {2}

  do_execsql_test 4.$tn.4 {
    SELECT rowid FROM d1 WHERE (c, a) = (
      SELECT x, y FROM d2 WHERE d2.rowid=d1.rowid
    );
  } {2 4}

  do_execsql_test 4.$tn.5 {
    SELECT d1.rowid FROM d1, d2 WHERE a = y;
  } {2 4}

  do_execsql_test 4.$tn.6 {
    SELECT d1.rowid FROM d1 WHERE a = (
      SELECT y FROM d2 where d2.rowid=d1.rowid
    );
  } {2 4}
}

finish_test
Changes to test/types2.test.
︙︙
329
330
331
332
333
334
335
336
337
338
339
340

  test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
  test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
  test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
  test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
  test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
  test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
  test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
  test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
  test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}
}

finish_test








|





329
330
331
332
333
334
335
336
337
338
339
340

  test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
  test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
  test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
  test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
  test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
  test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
  test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
  test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {}
  test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}
}

finish_test