AryaWu/sqlite

1#include "sqliteInt.h"2#include "unity.h"3 4#include <stdio.h>5#include <string.h>6#include <math.h>7 8/* The wrapper exposed in func.c for the static avgFinalize() */9extern void test_avgFinalize(sqlite3_context *context);10 11static int double_equal(double a, double b, double eps){12  double diff = fabs(a - b);13  if( diff <= eps ) return 1;14  /* relative comparison for larger magnitudes */15  double largest = fmax(fabs(a), fabs(b));16  if( diff <= largest * eps ) return 1;17  return 0;18}19 20static int query_scalar_double(sqlite3 *db, const char *sql, int *isNull, double *val){21  sqlite3_stmt *pStmt = 0;22  int rc = sqlite3_prepare_v2(db, sql, -1, &pStmt, 0);23  if( rc!=SQLITE_OK ) return rc;24  rc = sqlite3_step(pStmt);25  if( rc==SQLITE_ROW ){26    int t = sqlite3_column_type(pStmt, 0);27    if( t==SQLITE_NULL ){28      if( isNull ) *isNull = 1;29    }else{30      if( isNull ) *isNull = 0;31      if( val ) *val = sqlite3_column_double(pStmt, 0);32    }33    rc = SQLITE_OK;34  }else if( rc==SQLITE_DONE ){35    /* No row produced */36    if( isNull ) *isNull = 1;37    rc = SQLITE_OK;38  }39  sqlite3_finalize(pStmt);40  return rc;41}42 43/* Aggregate function harness to invoke test_avgFinalize with a valid sqlite3_context */44 45/* Variant A: Do not allocate aggregate context in step (p==NULL at finalize) */46static void aggA_step(sqlite3_context *ctx, int argc, sqlite3_value **argv){47  (void)ctx; (void)argc; (void)argv;48  /* Intentionally do nothing so sqlite3_aggregate_context(ctx,0) will return NULL */49}50static void aggA_final(sqlite3_context *ctx){51  test_avgFinalize(ctx);52}53 54/* Variant B: Allocate and zero aggregate context in step (p!=NULL but cnt==0) */55static void aggB_step(sqlite3_context *ctx, int argc, sqlite3_value **argv){56  (void)argc; (void)argv;57  void *p = sqlite3_aggregate_context(ctx, 64); /* Allocate some space */58  if( p ) memset(p, 0, 64);                     /* Ensure zeroed (cnt==0) */59}60static void aggB_final(sqlite3_context *ctx){61  test_avgFinalize(ctx);62}63 64void setUp(void) {65  /* empty */66}67 68void tearDown(void) {69  /* empty */70}71 72static sqlite3* open_test_db(void){73  sqlite3 *db = 0;74  int rc = sqlite3_open(":memory:", &db);75  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_OK, rc, "sqlite3_open failed");76  return db;77}78 79static void close_test_db(sqlite3 *db){80  if( db ){81    int rc = sqlite3_close(db);82    TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_OK, rc, "sqlite3_close failed");83  }84}85 86void test_avgFinalize_wrapper_no_context(void){87  sqlite3 *db = open_test_db();88 89  int rc = sqlite3_create_function(db, "aggA", 1, SQLITE_UTF8, 0, 0, aggA_step, aggA_final);90  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);91 92  int isNull = 0;93  double v = 0.0;94  rc = query_scalar_double(db, "SELECT aggA(1)", &isNull, &v);95  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);96  TEST_ASSERT_TRUE_MESSAGE(isNull, "Expected NULL result when no aggregate context was allocated");97 98  close_test_db(db);99}100 101void test_avgFinalize_wrapper_zero_count(void){102  sqlite3 *db = open_test_db();103 104  int rc = sqlite3_create_function(db, "aggB", 1, SQLITE_UTF8, 0, 0, aggB_step, aggB_final);105  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);106 107  int isNull = 0;108  double v = 0.0;109  rc = query_scalar_double(db, "SELECT aggB(1)", &isNull, &v);110  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);111  TEST_ASSERT_TRUE_MESSAGE(isNull, "Expected NULL result when aggregate context exists but cnt==0");112 113  close_test_db(db);114}115 116/* The remaining tests exercise avgFinalize via the built-in AVG() aggregate. */117 118void test_avgFinalize_sql_empty_set(void){119  sqlite3 *db = open_test_db();120 121  int isNull = 0;122  double v = 0.0;123  int rc = query_scalar_double(db, "SELECT avg(x) FROM (SELECT NULL AS x)", &isNull, &v);124  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);125  TEST_ASSERT_TRUE(isNull);126 127  close_test_db(db);128}129 130void test_avgFinalize_sql_simple_integers(void){131  sqlite3 *db = open_test_db();132  int rc = sqlite3_exec(db,133    "CREATE TABLE t(x);\n"134    "INSERT INTO t VALUES(1),(2),(3);",135    0, 0, 0136  );137  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);138 139  int isNull = 1;140  double v = 0.0;141  rc = query_scalar_double(db, "SELECT avg(x) FROM t", &isNull, &v);142  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);143  TEST_ASSERT_FALSE(isNull);144  TEST_ASSERT_TRUE(double_equal(v, 2.0, 1e-12));145 146  close_test_db(db);147}148 149void test_avgFinalize_sql_mixed_int_float(void){150  sqlite3 *db = open_test_db();151  int rc = sqlite3_exec(db,152    "CREATE TABLE t(x);\n"153    "INSERT INTO t VALUES(1),(2.5),(3);",154    0, 0, 0155  );156  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);157 158  int isNull = 1;159  double v = 0.0;160  rc = query_scalar_double(db, "SELECT avg(x) FROM t", &isNull, &v);161  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);162  TEST_ASSERT_FALSE(isNull);163  TEST_ASSERT_TRUE(double_equal(v, (1.0+2.5+3.0)/3.0, 1e-12));164 165  close_test_db(db);166}167 168void test_avgFinalize_sql_nulls_ignored(void){169  sqlite3 *db = open_test_db();170  int rc = sqlite3_exec(db,171    "CREATE TABLE t(x);\n"172    "INSERT INTO t VALUES(1),(NULL),(3);",173    0, 0, 0174  );175  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);176 177  int isNull = 1;178  double v = 0.0;179  rc = query_scalar_double(db, "SELECT avg(x) FROM t", &isNull, &v);180  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);181  TEST_ASSERT_FALSE(isNull);182  TEST_ASSERT_TRUE(double_equal(v, (1.0+3.0)/2.0, 1e-12));183 184  close_test_db(db);185}186 187/* Values chosen to exercise numerically large magnitudes; AVG should be stable. */188void test_avgFinalize_sql_large_values_stability(void){189  sqlite3 *db = open_test_db();190  /* Use values around 2^52 where doubles are dense enough to be exact; the KBN summation191     in SQLite should maintain good accuracy even when mixing magnitudes. */192  long long a = 4503599627370496LL;   /* 2^52, exactly representable in double */193  long long b = a + 16384;            /* +2^14 */194  long long c = a - 16384;            /* -2^14 */195  char sql[256];196  sqlite3_snprintf(sizeof(sql), sql,197    "WITH t(x) AS (VALUES(%lld),(%lld),(%lld)) SELECT avg(x) FROM t;", a, b, c);198 199  int isNull = 1;200  double v = 0.0;201  int rc = query_scalar_double(db, sql, &isNull, &v);202  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);203  TEST_ASSERT_FALSE(isNull);204  /* Expected average is a (since a + (a+16384) + (a-16384) = 3a) */205  TEST_ASSERT_TRUE(double_equal(v, (double)a, 1e-9));206 207  close_test_db(db);208}209 210void test_avgFinalize_sql_empty_table(void){211  sqlite3 *db = open_test_db();212  int rc = sqlite3_exec(db, "CREATE TABLE t(x);", 0, 0, 0);213  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);214 215  int isNull = 0;216  double v = 123.0;217  rc = query_scalar_double(db, "SELECT avg(x) FROM t", &isNull, &v);218  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);219  TEST_ASSERT_TRUE(isNull);220 221  close_test_db(db);222}223 224int main(void) {225  UNITY_BEGIN();226 227  RUN_TEST(test_avgFinalize_wrapper_no_context);228  RUN_TEST(test_avgFinalize_wrapper_zero_count);229 230  RUN_TEST(test_avgFinalize_sql_empty_set);231  RUN_TEST(test_avgFinalize_sql_simple_integers);232  RUN_TEST(test_avgFinalize_sql_mixed_int_float);233  RUN_TEST(test_avgFinalize_sql_nulls_ignored);234  RUN_TEST(test_avgFinalize_sql_large_values_stability);235  RUN_TEST(test_avgFinalize_sql_empty_table);236 237  return UNITY_END();238}