Repository navigation
Description
Summary
JsonSerializer.serializeResult() gives the JSONObject the schema's DATE format where serializeDocument() gives it the schema's DATETIME format. A query result value whose Java type is java.util.Date is therefore rendered as yyyy-MM-dd and loses its time of day on the way out over HTTP.
ts.timeBucket() returns a java.util.Date, so every bucket inside one calendar day arrives at an HTTP client as the same string. The engine computed the buckets correctly: the row count and the grouping are right, and the embedded API on the same server, same data, and same SQL returns the distinct bucket values. Only the value on the wire is wrong, which is the shape that gets a wrong answer past a row-count check.
Concretely, a TSBS double-groupby (SELECT host, ts.timeBucket('1h', ts) AS h, avg(uu) AS v ... GROUP BY host, h) over twelve hours of one day returns 1,200 rows with 12 distinct h values embedded, and 1,200 rows with 1 distinct h value over HTTP.
Version: 26.9.1 (build 9cea8e848fa57d275b8d614328245508cd76614a, branch main), from the official arcadedata/arcadedb:26.9.1 image, JDK Temurin 21.0.12. The call site named below is unchanged on main as of d3d7497286a021a3b38332fe2f443e41e950995d.
Reproduction
One self-contained class. It starts ArcadeDBServer in-process, loads the data once through the server's own time-series endpoint, and then asks each query twice: once through the embedded Database API and once over POST /api/v1/query. One JVM, one server, one dataset, so the transport is the only variable.
docker run --rm -v "$PWD":/work -w /work --entrypoint sh arcadedata/arcadedb:26.9.1 -c \
'javac -cp "/home/arcadedb/lib/*" TimeBucketGroupByRepro.java && \
java -cp "/home/arcadedb/lib/*:." TimeBucketGroupByRepro'
Data: a TIMESERIES type, 100 hosts by 12 hourly buckets by 6 samples, loaded through POST /api/v1/ts/{db}/write?precision=s, plus a document type Ev with a DATETIME property holding the same 12 hourly instants. Every timestamp falls inside one calendar day, 2026-01-01. Full source is attached below.
Observed output
The reported case, a TIMESERIES type with two grouping keys:
SELECT host, ts.timeBucket('1h', ts) AS h, avg(uu) AS v FROM Point
WHERE ts >= 1767225600000 AND ts < 1767268800000 GROUP BY host, h ORDER BY host, h
rows distinct first value of `h` java type (embedded)
expected 1200 12
embedded 1200 12 Thu Jan 01 00:00:00 GMT 2026 java.util.Date
HTTP 1200 1 2026-01-01 <-- DISAGREES
The same function on an ordinary document type, with one grouping key and with two:
SELECT ts.timeBucket('1h', occurred) AS h, count(*) AS c FROM Ev GROUP BY h ORDER BY h
expected 12 12
embedded 12 12 Thu Jan 01 00:00:00 GMT 2026 java.util.Date
HTTP 12 1 2026-01-01 <-- DISAGREES
SELECT tag, ts.timeBucket('1h', occurred) AS h, count(*) AS c FROM Ev GROUP BY tag, h ORDER BY tag, h
expected 24 12
embedded 24 12 Thu Jan 01 00:00:00 GMT 2026 java.util.Date
HTTP 24 1 2026-01-01 <-- DISAGREES
GROUP BY is not required at all. A plain projection of the same expression loses the time in the same way:
SELECT ts.timeBucket('1h', occurred) AS h FROM Ev WHERE tag = 'a' ORDER BY occurred
expected 12 12
embedded 12 12 Thu Jan 01 00:00:00 GMT 2026 java.util.Date
HTTP 12 1 2026-01-01 <-- DISAGREES
Everything that is a LocalDateTime rather than a java.util.Date is correct over HTTP, which is what says the problem is the value's Java type and not the HTTP layer in general:
SELECT ts FROM Point WHERE host = 'host_0' AND ts >= ... ORDER BY ts
embedded 72 72 2026-01-01T00:00 java.time.LocalDateTime
HTTP 72 72 2026-01-01 00:00:00 ok
SELECT occurred AS w FROM Ev ORDER BY occurred
embedded 24 12 2026-01-01T00:00 java.time.LocalDateTime
HTTP 24 12 2026-01-01 00:00:00 ok
SELECT FROM Ev ORDER BY occurred (records, not projections)
embedded 24 12 2026-01-01T00:00 java.time.LocalDateTime
HTTP 24 12 2026-01-01 00:00:00.000 ok
Where it comes from
Two adjacent methods in JsonSerializer disagree on which schema pattern fills the dateFormat slot:
// engine/src/main/java/com/arcadedb/serializer/JsonSerializer.java:97 - serializeDocument()
final JSONObject object = new JSONObject().setDateFormat(database.getSchema().getDateTimeFormat())
.setDateTimeFormat(schemaDateTimeFormat);
// engine/src/main/java/com/arcadedb/serializer/JsonSerializer.java:136 - serializeResult()
final JSONObject object = new JSONObject().setDateFormat(database.getSchema().getDateFormat())
.setDateTimeFormat(schemaDateTimeFormat);serializeDocument deliberately passes the DATETIME pattern into the date slot; serializeResult, the method the HTTP query handler uses (AbstractQueryHandler.serializeRows via JsonSerializer.createJsonSerializer()), passes the DATE pattern. That matters because JSONObject.put routes by Java type, and java.util.Date is an instant but takes the dateFormat branch:
// engine/src/main/java/com/arcadedb/serializer/json/JSONObject.java:161
case Date date -> {
if (dateFormatAsString == null)
object.addProperty(name, date.getTime());
else
object.addProperty(name, dateFormat.format(date.toInstant().atZone(ZoneId.systemDefault())));
}
...
case TemporalAccessor temporalAccessor -> { // LocalDateTime lands here, and is correct
...
object.addProperty(name, dateTimeFormat.format(temporalAccessor));
}With the default schema patterns (yyyy-MM-dd and yyyy-MM-dd HH:mm:ss) that turns 2026-01-01T05:00 into "2026-01-01".
The producer is SQLFunctionTimeBucket:
// engine/src/main/java/com/arcadedb/function/sql/time/SQLFunctionTimeBucket.java:76
return new Date(bucketStart);SQLFunctionRate returns a java.util.Date too, so I would expect it to have the same problem, though I did not test it.
A second, smaller inconsistency
On a TIMESERIES type with a single grouping key, the same expression comes back as a java.time.LocalDateTime instead, and is therefore serialized correctly:
SELECT ts.timeBucket('1h', ts) AS h, avg(uu) AS v FROM Point WHERE ... GROUP BY h ORDER BY h
embedded 12 12 2026-01-01T00:00 java.time.LocalDateTime
HTTP 12 12 2026-01-01 00:00:00 ok
So ts.timeBucket() yields two different Java types for the same expression depending on whether the time-series aggregate pushdown handled the query. That is why the defect shows on the two-key grouping and hides on the one-key grouping over a TIMESERIES type, and why it shows on both over a document type. Worth settling on one return type whichever way the serializer is fixed, because a client that type-switches on the value currently has to handle both.
Suggested fix
Make serializeResult() pass getDateTimeFormat() into setDateFormat(), as serializeDocument() already does. A java.util.Date carries a time of day, so formatting it with a date-only pattern is lossy whatever the value came from.
If the DATE pattern is wanted for genuine DATE columns, the distinction has to come from the schema type of the column rather than from the Java class of the value, since java.util.Date is used for both.
A regression test I would find convincing: a timeBucket('1h', ...) grouping over a window inside one day, asserted over HTTP to have as many distinct bucket values as there are buckets, on both a TIMESERIES type and a document type. A row-count assertion alone passes today.
Impact
Any HTTP client reading a bucketed aggregate silently gets one bucket per day. Row counts, group counts, and aggregate values are all correct, so nothing downstream can tell without comparing against a second engine or against the embedded API, which is how this one was found.
Full reproduction source
TimeBucketGroupByRepro.java
/*
* Reproduction: over HTTP, a query result value of type java.util.Date is serialized with the
* schema's DATE format ("yyyy-MM-dd"), so its time of day is dropped. timeBucket() returns a
* java.util.Date, so every bucket of one day collapses onto one string on the wire while the
* grouping itself is correct. The embedded API on the same server, same data, same SQL is right.
*
* One JVM: the server is started in-process, the data is loaded once through the server's own
* time-series endpoint, and each query is asked twice - once through the embedded Database API
* and once over HTTP. The transport is the only variable.
*
* Build and run (no Maven needed, jars from the official image):
* javac -cp '/home/arcadedb/lib/*' TimeBucketGroupByRepro.java
* java -cp '/home/arcadedb/lib/*:.' TimeBucketGroupByRepro
*/
import com.arcadedb.Constants;
import com.arcadedb.ContextConfiguration;
import com.arcadedb.GlobalConfiguration;
import com.arcadedb.database.Database;
import com.arcadedb.query.sql.executor.Result;
import com.arcadedb.query.sql.executor.ResultSet;
import com.arcadedb.server.ArcadeDBServer;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URI;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.Base64;
import java.util.Comparator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import java.util.stream.Stream;
public class TimeBucketGroupByRepro {
private static final String DB = "tsnative";
private static final String PWD = "reprosecret123";
private static final int PORT = 24801;
private static final long T0 = 1767225600L; // 2026-01-01T00:00:00Z, epoch seconds
private static final long T0MS = T0 * 1000;
private static final int HOSTS = 100;
private static final int HOURS = 12;
private static final int PER_HOUR = 6; // one sample every ten minutes
private static final long ENDMS = (T0 + HOURS * 3600L) * 1000;
private static int failures = 0;
public static void main(final String[] args) throws Exception {
final Path root = Files.createTempDirectory("timebucket-groupby-repro");
System.out.println("ArcadeDB " + Constants.getVersion()
+ " (build " + Constants.getBuildNumber() + ", branch " + Constants.getBranch() + ")");
System.out.println("JVM " + System.getProperty("java.vm.name") + " " + System.getProperty("java.version"));
GlobalConfiguration.SERVER_ROOT_PATH.setValue(root.toString());
GlobalConfiguration.SERVER_DATABASE_DIRECTORY.setValue(root.resolve("databases").toString());
GlobalConfiguration.SERVER_ROOT_PASSWORD.setValue(PWD);
GlobalConfiguration.SERVER_HTTP_INCOMING_PORT.setValue(PORT);
GlobalConfiguration.HA_ENABLED.setValue(false);
final ArcadeDBServer server = new ArcadeDBServer(new ContextConfiguration());
server.start();
try {
final Database db = server.getDatabase(DB, true, true);
load(db);
System.out.println();
System.out.println("Data: a TIMESERIES type, " + HOSTS + " hosts x " + HOURS + " hourly buckets x "
+ PER_HOUR + " samples = " + (HOSTS * HOURS * PER_HOUR) + ", loaded through");
System.out.println(" POST /api/v1/ts/" + DB + "/write?precision=s. Plus a document type Ev with a");
System.out.println(" DATETIME property holding the same " + HOURS + " hourly instants.");
System.out.println(" All timestamps fall inside one calendar day, 2026-01-01.");
System.out.println();
// 1. the reported symptom: TSBS double-groupby-1
compare(db, "GROUP BY host + timeBucket('1h')",
"SELECT host, ts.timeBucket('1h', ts) AS h, avg(uu) AS v FROM Point "
+ "WHERE ts >= " + T0MS + " AND ts < " + ENDMS + " GROUP BY host, h ORDER BY host, h",
"h", HOSTS * HOURS, HOURS);
// 2. the same expression with one grouping key
compare(db, "GROUP BY timeBucket('1h')",
"SELECT ts.timeBucket('1h', ts) AS h, avg(uu) AS v FROM Point "
+ "WHERE ts >= " + T0MS + " AND ts < " + ENDMS + " GROUP BY h ORDER BY h",
"h", HOURS, HOURS);
// 3. a finer bucket, one host, one hour: 6 minute buckets, still one day
compare(db, "GROUP BY timeBucket('1m')",
"SELECT ts.timeBucket('1m', ts) AS m, max(uu) AS v FROM Point WHERE host = 'host_0' "
+ "AND ts >= " + T0MS + " AND ts < " + (T0MS + 3600_000) + " GROUP BY m ORDER BY m",
"m", PER_HOUR, PER_HOUR);
// 4. no bucket function: the raw DATETIME column of the same type, projected
compare(db, "projected DATETIME column (no function)",
"SELECT ts FROM Point WHERE host = 'host_0' AND ts >= " + T0MS + " AND ts < " + ENDMS
+ " ORDER BY ts",
"ts", HOURS * PER_HOUR, HOURS * PER_HOUR);
// 4b. the SAME function on an ORDINARY DOCUMENT TYPE: no TIMESERIES storage involved
compare(db, "document type, ONE grouping key",
"SELECT ts.timeBucket('1h', occurred) AS h, count(*) AS c FROM Ev GROUP BY h ORDER BY h",
"h", HOURS, HOURS);
compare(db, "document type, TWO grouping keys",
"SELECT tag, ts.timeBucket('1h', occurred) AS h, count(*) AS c FROM Ev GROUP BY tag, h ORDER BY tag, h",
"h", 2 * HOURS, HOURS);
// 4d. no GROUP BY at all: the same value in a plain projection
compare(db, "plain projection, NO GROUP BY",
"SELECT ts.timeBucket('1h', occurred) AS h FROM Ev WHERE tag = 'a' ORDER BY occurred",
"h", HOURS, HOURS);
// 5. a DATETIME property on an ordinary document type, projected under an alias
compare(db, "projected DATETIME property, aliased",
"SELECT occurred AS w FROM Ev ORDER BY occurred", "w", 2 * HOURS, HOURS);
// 6. the same rows fetched as records rather than projections
compare(db, "same rows as records (SELECT FROM Ev)",
"SELECT FROM Ev ORDER BY occurred", "occurred", 2 * HOURS, HOURS);
System.out.println();
System.out.println(failures == 0
? "NO DISAGREEMENT - the defect did not reproduce."
: failures + " of the queries above disagree between the two transports.");
} finally {
server.stop();
delete(root.toFile());
}
System.exit(failures == 0 ? 0 : 1);
}
// --------------------------------------------------------------------------------------------
/** runs one query through both transports and prints row count, distinct values of one column, and a sample */
private static void compare(final Database db, final String label, final String sql, final String column,
final int expectedRows, final int expectedDistinct) throws Exception {
final Answer e = viaEmbedded(db, sql, column);
final Answer h = viaHttp(sql, column);
final boolean bad = e.distinct.size() != h.distinct.size() || e.rows != h.rows;
if (bad)
failures++;
System.out.println(label);
System.out.println(" " + sql);
System.out.printf(" %-12s %6s %10s %-30s %s%n", "", "rows", "distinct",
"first value of `" + column + "`", "java type (embedded)");
System.out.printf(" %-12s %6d %10d%n", "expected", expectedRows, expectedDistinct);
System.out.printf(" %-12s %6d %10d %-30s %s%n", "embedded", e.rows, e.distinct.size(), e.firstValue,
e.javaClass);
System.out.printf(" %-12s %6d %10d %-30s %s%n", "HTTP", h.rows, h.distinct.size(), h.firstValue,
bad ? "<-- DISAGREES" : "");
System.out.println();
}
private static void load(final Database db) throws Exception {
db.command("sql", "CREATE TIMESERIES TYPE Point TIMESTAMP ts TAGS (host STRING) "
+ "FIELDS (uu DOUBLE, us DOUBLE, ui DOUBLE) SHARDS 4");
final StringBuilder body = new StringBuilder();
for (int hIdx = 0; hIdx < HOSTS; hIdx++)
for (int hour = 0; hour < HOURS; hour++)
for (int p = 0; p < PER_HOUR; p++)
body.append("Point,host=host_").append(hIdx)
.append(" uu=").append((hIdx % 97) + hour + p / 10.0)
.append(",us=1.0,ui=2.0 ").append(T0 + hour * 3600L + p * 600L).append('\n');
post("/api/v1/ts/" + DB + "/write?precision=s", body.toString(), "text/plain");
db.command("sql", "CREATE DOCUMENT TYPE Ev");
db.command("sql", "CREATE PROPERTY Ev.occurred DATETIME");
db.command("sql", "CREATE PROPERTY Ev.tag STRING");
db.transaction(() -> {
for (final String tag : new String[] { "a", "b" })
for (int hour = 0; hour < HOURS; hour++)
db.command("sql", "INSERT INTO Ev SET occurred = :w, tag = :t",
java.util.Map.of("w", java.time.LocalDateTime.ofEpochSecond(T0 + hour * 3600L, 0,
java.time.ZoneOffset.UTC), "t", tag));
});
}
private static Answer viaEmbedded(final Database db, final String sql, final String column) {
final Answer a = new Answer();
try (final ResultSet rs = db.query("sql", sql)) {
while (rs.hasNext()) {
final Result r = rs.next();
final Object v = r.getProperty(column);
if (a.rows == 0)
a.javaClass = v == null ? "-" : v.getClass().getName();
a.add(v == null ? "null" : String.valueOf(v));
}
}
return a;
}
private static Answer viaHttp(final String sql, final String column) throws Exception {
final String json = post("/api/v1/query/" + DB,
"{\"language\":\"sql\",\"command\":" + quote(sql) + ",\"limit\":-1}", "application/json");
final Answer a = new Answer();
for (final String row : splitRows(json)) {
final String v = field(row, column);
a.add(v == null ? "null" : v);
}
return a;
}
private static String post(final String path, final String body, final String contentType) throws Exception {
final HttpURLConnection c = (HttpURLConnection) URI.create("http://localhost:" + PORT + path).toURL().openConnection();
c.setRequestMethod("POST");
c.setDoOutput(true);
c.setRequestProperty("Content-Type", contentType);
c.setRequestProperty("Authorization",
"Basic " + Base64.getEncoder().encodeToString(("root:" + PWD).getBytes(StandardCharsets.UTF_8)));
try (final OutputStream out = c.getOutputStream()) {
out.write(body.getBytes(StandardCharsets.UTF_8));
}
final int code = c.getResponseCode();
try (final InputStream in = code < 400 ? c.getInputStream() : c.getErrorStream()) {
final String text = new String(in.readAllBytes(), StandardCharsets.UTF_8);
if (code >= 400)
throw new IllegalStateException("HTTP " + code + " on " + path + ": " + text);
return text;
}
}
// --------------------------------------------------------------------------------------------
private static final class Answer {
int rows;
final Set<String> distinct = new LinkedHashSet<>();
String firstValue;
String javaClass = "-";
void add(final String v) {
if (rows == 0)
firstValue = v;
rows++;
distinct.add(v);
}
}
/** splits the "result":[ ... ] array into its top-level {...} objects, no JSON library involved */
private static List<String> splitRows(final String json) {
final List<String> out = new ArrayList<>();
final int start = json.indexOf("\"result\"");
if (start < 0)
return out;
int depth = 0, from = -1;
for (int i = json.indexOf('[', start); i >= 0 && i < json.length(); i++) {
final char ch = json.charAt(i);
if (ch == '{') {
if (depth++ == 0)
from = i;
} else if (ch == '}') {
if (--depth == 0 && from >= 0)
out.add(json.substring(from, i + 1));
} else if (ch == ']' && depth == 0)
break;
}
return out;
}
private static String field(final String obj, final String name) {
final int k = obj.indexOf('"' + name + '"');
if (k < 0)
return null;
int i = obj.indexOf(':', k) + 1;
while (i < obj.length() && obj.charAt(i) == ' ')
i++;
if (i >= obj.length())
return null;
if (obj.charAt(i) == '"') {
final int end = obj.indexOf('"', i + 1);
return end < 0 ? null : obj.substring(i + 1, end);
}
int end = i;
while (end < obj.length() && ",}".indexOf(obj.charAt(end)) < 0)
end++;
return obj.substring(i, end).trim();
}
private static String quote(final String s) {
return '"' + s.replace("\\", "\\\\").replace("\"", "\\\"") + '"';
}
private static void delete(final File f) {
if (f.isDirectory())
try (final Stream<Path> s = Files.list(f.toPath())) {
s.sorted(Comparator.reverseOrder()).map(Path::toFile).forEach(TimeBucketGroupByRepro::delete);
} catch (final Exception ignore) {
// best effort
}
f.delete();
}
}
Activity
docs: the three upstream filings, our three defects, and the thermal …
docs: the benchmark suite documented, with the results left on the page
Merge pull request #7635 from ArcadeData/fix/issue-7610-json-date-format
lvca commentedon Sep 15, 2026
Fixed and merged.
What was wrong: when a query result contains a value like the one returned by
ts.timeBucket(), the HTTP/JSON API was formatting it with a date-only pattern (2026-01-01), dropping the time of day. The embedded Java API was not affected. This meant that grouping by hour/minute buckets over HTTP could look like every row landed in the same bucket, even though the underlying grouping and aggregates were computed correctly.Fix:
ts.timeBucket()now returns a proper date-time value, and the JSON serializer now correctly tells apart a realDATEcolumn from a date-time value, instead of guessing from the Java type alone.PR: #7635
While fixing this, a narrower, unrelated edge case was found (a
DATEcolumn explicitly configured with a non-default type, combined with a specific query shape) and tracked separately in #7638 for a future fix - it does not affect the default configuration.❤️ Thanks for helping make ArcadeDB better! ArcadeDB is free and open source, sustained by its community. If it's useful to you or your company, please consider becoming a sponsor to keep development going.
fix(ArcadeData#7610): expose ts.timeBucket() as LocalDateTime, not ja…
Merge branch 'upstream-main'
tae898 commentedon Sep 16, 2026
Verified on
e25de69b4c, the26.10.1-SNAPSHOTimage published at 21:58Z on 2026-09-15 (it carries the #7635 mergedd8087d455), on JDK 21.0.12 (eclipse-temurin 21-alpine), with the reproduction class from the issue: one in-processArcadeDBServer, the same 100 hosts by 12 hourly buckets by 6 samples loaded once throughPOST /api/v1/ts/{db}/write, every query asked through the embeddedDatabaseAPI and overPOST /api/v1/query. Before isarcadedata/arcadedb:26.9.1(build9cea8e848), same class, same JDK, same data.The reported query,
SELECT host, ts.timeBucket('1h', ts) AS h, avg(uu) AS v FROM Point WHERE ... GROUP BY host, h ORDER BY host, h:hon the wirehvalues served, host_0Thu Jan 01 00:00:00 GMT 2026...Thu Jan 01 11:00:00 GMT 2026(java.util.Date)2026-01-01twelve times2026-01-01T00:00...2026-01-01T11:00(java.time.LocalDateTime)2026-01-01 00:00:00...2026-01-01 11:00:00, 100 rows per bucketThe other three shapes that disagreed on 26.9.1 (
timeBucketon a document type with one grouping key, with two grouping keys, and as a plain projection with no GROUP BY) went from 1 distinct value over HTTP to 12, matching the embedded API, and the five shapes that already agreed (timeBucketalone on the time-series type, the'1m'bucket, and the three projected DATETIME columns) still do. Nine queries, zero disagreements between the two transports, from four.The reproductions from #7609 (the decimal literal one and the openCypher comparator one), #7611 (the indexed lower bound one), and #7615 (the async one, three runs at each of parallel level 1, 4, and 16, 9742 of 9742 stored every time) still answer every row correctly on this build, so those fixes are intact after the serializer change.
On my side, this is the defect that made the served per-host hourly query on the time-series table return one bucket per host instead of one per host and hour, so that cell was withheld from my comparison against the other engines. Over HTTP it now returns the same 1200 groups and the same timestamps as the embedded API, which is what the other engines return for the same query.
The bindings suite is green on the same engine, 453 passed, 2 skipped, on the
26.10.1-SNAPSHOTjars carryinge25de69b4c, and the CI matrix on the same wheel build is green.Thank you for the fast fix, and for keeping the DATE and DATETIME distinction in the serializer instead of in the Java type.
fix(#7638): honour a DATE column's type on write, on read and out to …
review(#7638/#7637): pin the undeclared-Date widening, plus two revie…
skeleton: every ArcadeDB row on the October pin, and the served time-…
fix(ArcadeData#8157,ArcadeData#8152,ArcadeData#8156,ArcadeData#8167,A…
docs sweep, benchmarks/: retire what October superseded, fix what no …