test/optfuzz.c

   1 /*
   2 ** 2018-03-21
   3 **
   4 ** The author disclaims copyright to this source code.  In place of
   5 ** a legal notice, here is a blessing:
   6 **
   7 **    May you do good and not evil.
   8 **    May you find forgiveness for yourself and forgive others.
   9 **    May you share freely, never taking more than you give.
  10 **
  11 *************************************************************************
  12 **
  13 ** This program attempts to verify the correctness of the SQLite query
  14 ** optimizer by fuzzing.
  15 **
  16 ** The input is an SQL script, presumably generated by a fuzzer.  The
  17 ** argument is the name of the input.  If no files are named, standard
  18 ** input is read.
  19 **
  20 ** The SQL script is run twice, once with optimization enabled, and again
  21 ** with optimization disabled.  If the output is not equivalent, an error
  22 ** is printed and the program returns non-zero.
  23 */
  24
  25 /* Include the SQLite amalgamation, after making appropriate #defines.
  26 */
  27 #define SQLITE_THREADSAFE 0
  28 #define SQLITE_OMIT_LOAD_EXTENSION 1
  29 #define SQLITE_ENABLE_DESERIALIZE 1
  30 #include "sqlite3.c"
  31
  32 /* Content of the read-only test database */
  33 #include "optfuzz-db01.c"
  34
  35 /*
  36 ** Prepare a single SQL statement.  Panic if anything goes wrong
  37 */
  38 static sqlite3_stmt *prepare_sql(sqlite3 *db, const char *zFormat, ...){
  39   char *zSql;
  40   int rc;
  41   sqlite3_stmt *pStmt = 0;
  42   va_list ap;
  43
  44   va_start(ap, zFormat);
  45   zSql = sqlite3_vmprintf(zFormat, ap);
  46   va_end(ap);
  47   rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  48   if( rc ){
  49     printf("Error: %s\nSQL: %s\n",
  50            sqlite3_errmsg(db), zSql);
  51     exit(1);
  52   }
  53   sqlite3_free(zSql);
  54   return pStmt;
  55 }
  56
  57 /*
  58 ** Run SQL.  Panic if anything goes wrong
  59 */
  60 static void run_sql(sqlite3 *db, const char *zFormat, ...){
  61   char *zSql;
  62   int rc;
  63   char *zErr = 0;
  64   va_list ap;
  65
  66   va_start(ap, zFormat);
  67   zSql = sqlite3_vmprintf(zFormat, ap);
  68   va_end(ap);
  69   rc = sqlite3_exec(db, zSql, 0, 0, &zErr);
  70   if( rc || zErr ){
  71     printf("Error: %s\nsqlite3_errmsg: %s\nSQL: %s\n",
  72            zErr, sqlite3_errmsg(db), zSql);
  73     exit(1);
  74   }
  75   sqlite3_free(zSql);
  76 }
  77
  78 /*
  79 ** Run one or more SQL statements contained in zSql against database dbRun.
  80 ** Store the input in database dbOut.
  81 */
  82 static int optfuzz_exec(
  83   sqlite3 *dbRun,             /* The database on which the SQL executes */
  84   const char *zSql,           /* The SQL to be executed */
  85   sqlite3 *dbOut,             /* Store results in this database */
  86   const char *zOutTab,        /* Store results in this table of dbOut */
  87   int *pnStmt,                /* Write the number of statements here */
  88   int *pnRow,                 /* Write the number of rows here */
  89   int bTrace                  /* Print query results if true */
  90 ){
  91   int rc = SQLITE_OK;         /* Return code */
  92   const char *zLeftover;      /* Tail of unprocessed SQL */
  93   sqlite3_stmt *pStmt = 0;    /* The current SQL statement */
  94   sqlite3_stmt *pIns = 0;     /* Statement to insert into dbOut */
  95   const char *zCol;           /* Single column value */
  96   int nCol;                   /* Number of output columns */
  97   char zLine[4000];           /* Complete row value */
  98
  99   run_sql(dbOut, "BEGIN");
 100   run_sql(dbOut, "CREATE TABLE IF NOT EXISTS staging(x TEXT)");
 101   run_sql(dbOut, "CREATE TABLE IF NOT EXISTS \"%w\"(x TEXT)", zOutTab);
 102   pIns = prepare_sql(dbOut, "INSERT INTO staging(x) VALUES(?1)");
 103   *pnRow = *pnStmt = 0;
 104   while( rc==SQLITE_OK && zSql && zSql[0] ){
 105     zLeftover = 0;
 106     rc = sqlite3_prepare_v2(dbRun, zSql, -1, &pStmt, &zLeftover);
 107     zSql = zLeftover;
 108     assert( rc==SQLITE_OK || pStmt==0 );
 109     if( rc!=SQLITE_OK ){
 110       printf("Error with [%s]\n%s\n", zSql, sqlite3_errmsg(dbRun));
 111       break;
 112     }
 113     if( !pStmt ) continue;
 114     (*pnStmt)++;
 115     nCol = sqlite3_column_count(pStmt);
 116     run_sql(dbOut, "DELETE FROM staging;");
 117     while( sqlite3_step(pStmt)==SQLITE_ROW ){
 118       int i, j;
 119       for(i=j=0; i<nCol && j<sizeof(zLine)-50; i++){
 120         int eType = sqlite3_column_type(pStmt, i);
 121         if( eType==SQLITE_NULL ){
 122           zCol = "NULL";
 123         }else{
 124           zCol = (const char*)sqlite3_column_text(pStmt, i);
 125         }
 126         if( i ) zLine[j++] = ',';
 127         if( eType==SQLITE_TEXT ){
 128           sqlite3_snprintf(sizeof(zLine)-j, zLine+j, "'%q'", zCol);
 129         }else{
 130           sqlite3_snprintf(sizeof(zLine)-j, zLine+j, "%s", zCol);
 131         }
 132         j += (int)strlen(zLine+j);
 133       }
 134       /* Detect if any row is too large and throw an error, because we will
 135       ** want to go back and look more closely at that case */
 136       if( j>=sizeof(zLine)-100 ){
 137         printf("Excessively long output line: %d bytes\n" ,j);
 138         exit(1);
 139       }
 140       if( bTrace ){
 141         printf("%s\n", zLine);
 142       }
 143       (*pnRow)++;
 144       sqlite3_bind_text(pIns, 1, zLine, j, SQLITE_TRANSIENT);
 145       rc = sqlite3_step(pIns);
 146       assert( rc==SQLITE_DONE );
 147       rc = sqlite3_reset(pIns);
 148     }
 149     run_sql(dbOut,
 150       "INSERT INTO \"%w\"(x) VALUES('### %q ###')",
 151       zOutTab, sqlite3_sql(pStmt)
 152     );
 153     run_sql(dbOut,
 154       "INSERT INTO \"%w\"(x) SELECT group_concat(x,char(10))"
 155       "  FROM (SELECT x FROM staging ORDER BY x)",
 156       zOutTab
 157     );
 158     run_sql(dbOut, "COMMIT");
 159     sqlite3_finalize(pStmt);
 160     pStmt = 0;
 161   }
 162   sqlite3_finalize(pStmt);
 163   sqlite3_finalize(pIns);
 164   return rc;
 165 }
 166
 167 /*
 168 ** Read the content of file zName into memory obtained from sqlite3_malloc64()
 169 ** and return a pointer to the buffer. The caller is responsible for freeing
 170 ** the memory.
 171 **
 172 ** If parameter pnByte is not NULL, (*pnByte) is set to the number of bytes
 173 ** read.
 174 **
 175 ** For convenience, a nul-terminator byte is always appended to the data read
 176 ** from the file before the buffer is returned. This byte is not included in
 177 ** the final value of (*pnByte), if applicable.
 178 **
 179 ** NULL is returned if any error is encountered. The final value of *pnByte
 180 ** is undefined in this case.
 181 */
 182 static char *readFile(const char *zName, int *pnByte){
 183   FILE *in = fopen(zName, "rb");
 184   long nIn;
 185   size_t nRead;
 186   char *pBuf;
 187   if( in==0 ) return 0;
 188   fseek(in, 0, SEEK_END);
 189   nIn = ftell(in);
 190   rewind(in);
 191   pBuf = sqlite3_malloc64( nIn+1 );
 192   if( pBuf==0 ) return 0;
 193   nRead = fread(pBuf, nIn, 1, in);
 194   fclose(in);
 195   if( nRead!=1 ){
 196     sqlite3_free(pBuf);
 197     return 0;
 198   }
 199   pBuf[nIn] = 0;
 200   if( pnByte ) *pnByte = nIn;
 201   return pBuf;
 202 }
 203
 204 int main(int argc, char **argv){
 205   int nIn = 0;               /* Number of input files */
 206   char **azIn = 0;           /* Names of input files */
 207   sqlite3 *dbOut = 0;        /* Database to hold results */
 208   sqlite3 *dbRun = 0;        /* Database used for tests */
 209   int bTrace = 0;            /* Show query results */
 210   int bShowValid = 0;        /* Just list inputs that are valid SQL */
 211   int nRow, nStmt;           /* Number of rows and statements */
 212   int i, rc;
 213
 214   for(i=1; i<argc; i++){
 215     const char *z = argv[i];
 216     if( z[0]=='-' && z[1]=='-' ) z++;
 217     if( strcmp(z,"-help")==0 ){
 218       printf("Usage: %s [OPTIONS] FILENAME ...\n", argv[0]);
 219       printf("Options:\n");
 220       printf("  --help               Show his message\n");
 221       printf("  --output-trace       Show each line of SQL output\n");
 222       printf("  --valid-sql          List FILEs that are valid SQL\n");
 223       return 0;
 224     }
 225     else if( strcmp(z,"-output-trace")==0 ){
 226       bTrace = 1;
 227     }
 228     else if( strcmp(z,"-valid-sql")==0 ){
 229       bShowValid = 1;
 230     }
 231     else if( z[0]=='-' ){
 232       printf("unknown option \"%s\".  Use --help for details\n", argv[i]);
 233       return 1;
 234     }
 235     else {
 236       nIn++;
 237       azIn = realloc(azIn, sizeof(azIn[0])*nIn);
 238       if( azIn==0 ){
 239         printf("out of memory\n");
 240         exit(1);
 241       }
 242       azIn[nIn-1] = argv[i];
 243     }
 244   }
 245
 246   sqlite3_open(":memory:", &dbOut);
 247   sqlite3_open(":memory:", &dbRun);
 248   sqlite3_deserialize(dbRun, "main", data001, sizeof(data001),
 249                       sizeof(data001), SQLITE_DESERIALIZE_READONLY);
 250   for(i=0; i<nIn; i++){
 251     char *zSql = readFile(azIn[i], 0);
 252     sqlite3_stmt *pCk;
 253     sqlite3_exec(dbRun, "ROLLBACK", 0, 0, 0);
 254     if( bShowValid ){
 255       rc = sqlite3_exec(dbRun, zSql, 0, 0, 0);
 256       if( rc==SQLITE_OK ) printf("%s\n", azIn[i]);
 257       sqlite3_free(zSql);
 258       continue;
 259     }
 260     sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, dbRun, 0);
 261     if( bTrace ) printf("%s: Optimized\n", azIn[i]);
 262     rc = optfuzz_exec(dbRun, zSql, dbOut, "opt", &nStmt, &nRow, bTrace);
 263     if( rc ){
 264       printf("%s: optimized run failed: %s\n",
 265             azIn[i], sqlite3_errmsg(dbRun));
 266     }else{
 267       sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, dbRun, 0xffff);
 268       if( bTrace ) printf("%s: Non-optimized\n", azIn[i]);
 269       rc = optfuzz_exec(dbRun, zSql, dbOut, "noopt", &nStmt, &nRow, bTrace);
 270       if( rc ){
 271         printf("%s: non-optimized run failed: %s\n",
 272               azIn[i], sqlite3_errmsg(dbRun));
 273         exit(1);
 274       }
 275       pCk = prepare_sql(dbOut,
 276            "SELECT (SELECT group_concat(x,char(10)) FROM opt)=="
 277            "       (SELECT group_concat(x,char(10)) FROM noopt)");
 278       rc = sqlite3_step(pCk);
 279       if( rc!=SQLITE_ROW ){
 280         printf("%s: comparison failed\n", sqlite3_errmsg(dbOut));
 281         exit(1);
 282       }
 283       if( !sqlite3_column_int(pCk, 0) ){
 284         printf("%s: opt/no-opt outputs differ\n", azIn[i]);
 285         pCk = prepare_sql(dbOut,
 286            "SELECT group_concat(x,char(10)) FROM opt "
 287            "UNION ALL "
 288            "SELECT group_concat(x,char(10)) FROM noopt");
 289         sqlite3_step(pCk);
 290         printf("opt:\n%s\n", sqlite3_column_text(pCk,0));
 291         sqlite3_step(pCk);
 292         printf("noopt:\n%s\n", sqlite3_column_text(pCk,0));
 293         exit(1);
 294       }else{
 295         printf("%s: %d stmts %d rows ok\n", azIn[i], nStmt, nRow);
 296       }
 297       sqlite3_finalize(pCk);
 298     }
 299     sqlite3_free(zSql);
 300   }
 301   sqlite3_close(dbRun);
 302   sqlite3_close(dbOut);
 303   free(azIn);
 304   if( sqlite3_memory_used() ){
 305     printf("Memory leak of %lld bytes\n", sqlite3_memory_used());
 306     exit(1);
 307   }
 308   return 0;
 309 }