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