TEST_F(SQLiteUtilTests, test_query_planner) {
  using TypeList = std::vector<ColumnType>;

  auto dbc = getTestDBC();
  TableColumns columns;

  std::string query = "select path, path from file";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());

  query = "select path, path from file where path in ('osquery', 'noquery')";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE, TEXT_TYPE}));

  query = "select path, seconds from file, time where path LIKE 'osquery'";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE, INTEGER_TYPE}));

  query = "select path || path from file";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());

  query = "select path || path from file where path = 'osquery'";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE}));

  query = "select seconds, path || path from file, time ";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());

  query =
      "select seconds, path || path from file, time where path in ('osquery')";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({INTEGER_TYPE, TEXT_TYPE}));

  query = "select seconds, seconds from time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({INTEGER_TYPE, INTEGER_TYPE}));

  query = "select count(*) from time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({BIGINT_TYPE}));

  query = "select count(*), count(seconds), seconds from time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns),
            TypeList({BIGINT_TYPE, BIGINT_TYPE, INTEGER_TYPE}));

  query = "select 1, 'path', path from file";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());

  query = "select 1, 'path', path from file where path = 'os'";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({INTEGER_TYPE, TEXT_TYPE, TEXT_TYPE}));

  query = "select weekday, day, count(*), seconds from time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns),
            TypeList({TEXT_TYPE, INTEGER_TYPE, BIGINT_TYPE, INTEGER_TYPE}));

  query = "select seconds + 1 from time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({BIGINT_TYPE}));

  query = "select seconds * seconds from time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({BIGINT_TYPE}));

  query = "select seconds > 1, seconds, count(seconds) from time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns),
            TypeList({INTEGER_TYPE, INTEGER_TYPE, BIGINT_TYPE}));

  query =
      "select f1.*, seconds, f2.directory from (select path || path from file) "
      "f1, file as f2, time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());

  query =
      "select f1.*, seconds, f2.directory from (select path || path from file) "
      "f1, file as f2, time where path in ('query', 'query')";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());

  query =
      "select f1.*, seconds, f2.directory from (select path || path from file "
      "where path = 'query') "
      "f1, file as f2, time where path in ('query', 'query')";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE, INTEGER_TYPE, TEXT_TYPE}));

  query = "select CAST(seconds AS INTEGER) FROM time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({BIGINT_TYPE}));

  query = "select CAST(seconds AS TEXT) FROM time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE}));

  query = "select CAST(seconds AS REAL) FROM time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({DOUBLE_TYPE}));

  query = "select CAST(seconds AS BOOLEAN) FROM time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({UNKNOWN_TYPE}));

  query = "select CAST(seconds AS DATETIME) FROM time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({UNKNOWN_TYPE}));

  query = "select CAST(seconds AS BLOB) FROM time";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns), TypeList({BLOB_TYPE}));

  query = "select url, round_trip_time, response_code from curl";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());

  query =
      "select url, round_trip_time, response_code from curl where url = "
      "'https://github.com/osquery/osquery'";
  EXPECT_TRUE(getQueryColumnsInternal(query, columns, dbc).ok());
  EXPECT_EQ(getTypes(columns),
            TypeList({TEXT_TYPE, BIGINT_TYPE, INTEGER_TYPE}));
}