/ Check-in [14dfd96f]
Login

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

Overview
Comment:Fix a bug causing spurious "sub-select returns N columns expected 1" errors in join queries with a term like "(a, b) IN (SELECT ...)" in the WHERE clause. Ticket [7310e2fb3d046a5f5].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:14dfd96f9bca2df5033b2d894bf63cc8bf450a45ca11df5e3bbb814fdf96b656
User & Date: dan 2018-01-23 16:38:57
Original Comment: Fix a bug causing spurious "sub-select returns N columns expected 1" errors in join queries with a term like "(a, b) IN (SELECT ...)" in the WHERE clause.
Context
2018-01-23
17:33
Work around a problem with GCC on 32-bit machines that cause the CAST operator to generate a floating-point result for strings that could be represented as very large integers. check-in: 1b027319 user: drh tags: trunk
16:38
Fix a bug causing spurious "sub-select returns N columns expected 1" errors in join queries with a term like "(a, b) IN (SELECT ...)" in the WHERE clause. Ticket [7310e2fb3d046a5f5]. check-in: 14dfd96f user: dan tags: trunk
15:26
Fix the modification-time setting logic in the fileio.c extension on Windows so that it works with utf8 filenames. check-in: f785b904 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

  2166   2166       pE = pTerm->pExpr;
  2167   2167       assert( !ExprHasProperty(pE, EP_FromJoin) );
  2168   2168       assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
  2169   2169       pAlt = sqlite3WhereFindTerm(pWC, iCur, pTerm->u.leftColumn, notReady,
  2170   2170                       WO_EQ|WO_IN|WO_IS, 0);
  2171   2171       if( pAlt==0 ) continue;
  2172   2172       if( pAlt->wtFlags & (TERM_CODED) ) continue;
         2173  +    if( (pAlt->eOperator & WO_IN) 
         2174  +     && (pAlt->pExpr->flags & EP_xIsSelect)
         2175  +     && (pAlt->pExpr->x.pSelect->pEList->nExpr>1)
         2176  +    ){
         2177  +      continue;
         2178  +    }
  2173   2179       testcase( pAlt->eOperator & WO_EQ );
  2174   2180       testcase( pAlt->eOperator & WO_IS );
  2175   2181       testcase( pAlt->eOperator & WO_IN );
  2176   2182       VdbeModuleComment((v, "begin transitive constraint"));
  2177   2183       sEAlt = *pAlt->pExpr;
  2178   2184       sEAlt.pLeft = pE->pLeft;
  2179   2185       sqlite3ExprIfFalse(pParse, &sEAlt, addrCont, SQLITE_JUMPIFNULL);

Changes to test/rowvalue.test.

   389    389     UPDATE t16c SET a=a WHERE a=3;
   390    390     SELECT * FROM t16c;
   391    391   } {
   392    392     1 C B A D
   393    393     2 z y x w
   394    394     3 i ii iii iv
   395    395   }
          396  +
          397  +do_execsql_test 17.0 {
          398  +  CREATE TABLE b1(a, b);
          399  +  CREATE TABLE b2(x);
          400  +}
          401  +
          402  +do_execsql_test 17.1 {
          403  +  SELECT * FROM b2 CROSS JOIN b1 
          404  +  WHERE b2.x=b1.a AND (b1.a, 2) 
          405  +  IN (VALUES(1, 2));
          406  +} {}
          407  +
          408  +do_execsql_test 18.0 {
          409  +  CREATE TABLE b3 ( a, b, PRIMARY KEY (a, b) );
          410  +  CREATE TABLE b4 ( a );
          411  +  CREATE TABLE b5 ( a, b );
          412  +  INSERT INTO b3 VALUES (1, 1), (1, 2);
          413  +  INSERT INTO b4 VALUES (1);
          414  +  INSERT INTO b5 VALUES (1, 1), (1, 2);
          415  +}
          416  +
          417  +do_execsql_test 18.1 {
          418  +  SELECT * FROM b3 WHERE (SELECT b3.a, b3.b) IN ( SELECT a, b FROM b5 )
          419  +} {1 1 1 2}
          420  +do_execsql_test 18.2 {
          421  +  SELECT * FROM b3 WHERE (VALUES(b3.a, b3.b)) IN ( SELECT a, b FROM b5 );
          422  +} {1 1 1 2}
          423  +do_execsql_test 18.3 {
          424  +  SELECT * FROM b3 WHERE (b3.a, b3.b) IN ( SELECT a, b FROM b5 ); 
          425  +} {1 1 1 2}
          426  +do_execsql_test 18.4 {
          427  +  SELECT * FROM b3 JOIN b4 ON b4.a = b3.a
          428  +  WHERE (SELECT b3.a, b3.b) IN ( SELECT a, b FROM b5 ); 
          429  +} {1 1 1 1 2 1}
          430  +do_execsql_test 18.5 {
          431  +  SELECT * FROM b3 JOIN b4 ON b4.a = b3.a
          432  +  WHERE (VALUES(b3.a, b3.b)) IN ( SELECT a, b FROM b5 ); 
          433  +} {1 1 1 1 2 1}
          434  +do_execsql_test 18.6 {
          435  +  SELECT * FROM b3 JOIN b4 ON b4.a = b3.a
          436  +  WHERE (b3.a, b3.b) IN ( SELECT a, b FROM b5 ); 
          437  +} {1 1 1 1 2 1}
          438  +
          439  + 
          440  +finish_test
   396    441   
   397    442   finish_test