{"record":{"id":"0caab7c3043d1565","repo":"apache/shardingsphere","slug":"can-not-support-date-format-if-year-month-day-is","errorCode":null,"errorMessage":"Can not support date format if year, month, day is absent.","messagePattern":"Can not support date format if year, month, day is absent\\.","errorType":"exception","errorClass":"SQLFeatureNotSupportedException","httpStatus":null,"severity":"error","filePath":"database/protocol/dialect/mysql/src/main/java/org/apache/shardingsphere/database/protocol/mysql/packet/command/query/binary/execute/protocol/MySQLDateBinaryProtocolValue.java","lineNumber":42,"sourceCode":"import java.sql.SQLFeatureNotSupportedException;\nimport java.sql.Timestamp;\nimport java.time.LocalDate;\nimport java.time.LocalDateTime;\nimport java.util.Date;\n\n/**\n * Binary protocol value for date for MySQL.\n */\npublic final class MySQLDateBinaryProtocolValue implements MySQLBinaryProtocolValue {\n    \n    private static final long NANOS_PER_SECOND = 1_000_000_000L;\n    \n    @Override\n    public Object read(final MySQLPacketPayload payload, final boolean unsigned) throws SQLException {\n        int length = payload.readInt1();\n        switch (length) {\n            case 0:\n                throw new SQLFeatureNotSupportedException(\"Can not support date format if year, month, day is absent.\");\n            case 4:\n                return getTimestampForDate(payload);\n            case 7:\n                return getTimestampForDatetime(payload);\n            case 11:\n                Timestamp result = getTimestampForDatetime(payload);\n                result.setNanos(payload.readInt4() * 1000);\n                return result;\n            default:\n                throw new SQLFeatureNotSupportedException(String.format(\"Wrong length `%d` of MYSQL_TYPE_TIME\", length));\n        }\n    }\n    \n    private Timestamp getTimestampForDate(final MySQLPacketPayload payload) {\n        return Timestamp.valueOf(LocalDate.of(payload.readInt2(), payload.readInt1(), payload.readInt1()).atStartOfDay());\n    }\n    \n    private Timestamp getTimestampForDatetime(final MySQLPacketPayload payload) {","sourceCodeStart":24,"sourceCodeEnd":60,"githubUrl":"https://github.com/apache/shardingsphere/blob/e952770a215630a3659c75d64369168cd3e26b82/database/protocol/dialect/mysql/src/main/java/org/apache/shardingsphere/database/protocol/mysql/packet/command/query/binary/execute/protocol/MySQLDateBinaryProtocolValue.java#L24-L60","documentation":"Thrown when reading a MySQL binary-protocol DATE/DATETIME value whose stored length byte is 0. Length 0 encodes the MySQL 'zero date' 0000-00-00 (or a NULL-ish date); this reader cannot represent it as a java.sql.Timestamp, so it throws SQLFeatureNotSupportedException. It is a checked-style JDBC exception, so callers can branch on it explicitly.","triggerScenarios":"Executing a prepared statement in binary mode where a DATE/DATETIME column contains '0000-00-00' — typical when the server's sql_mode lacks NO_ZERO_DATE/STRICT mode and legacy rows exist. MySQLDateBinaryProtocolValue.read() sees length 0.","commonSituations":"Legacy schemas with zero dates; servers with sql_mode='' ; migrations importing old dumps that re-enable zero dates; test fixtures using '0000-00-00' as a sentinel.","solutions":["Set the server sql_mode to include NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TABLES and repair existing rows: UPDATE t SET d=NULL WHERE d='0000-00-00' (or a real date)","Rewrite zero dates in the data before querying through the proxy","Select NULL instead of zero dates by rewriting the query (e.g. NULLIF(d,'0000-00-00'))","If compatibility with zero dates is required, request text protocol results rather than server-side prepare, or handle SQLFeatureNotSupportedException per column"],"exampleFix":"-- before: server allows zero dates\nSET GLOBAL sql_mode='';\n\n-- after: forbid and clean them\nSET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';\nUPDATE events SET occurred_at = NULL WHERE occurred_at = '0000-00-00';","handlingStrategy":"try-catch","validationCode":"-- prevent at the source: forbid zero dates\nSET SESSION sql_mode = CONCAT(@@session.sql_mode, ',NO_ZERO_DATE,NO_ZERO_IN_DATE');\n\n-- or neutralize them in the query so the binary reader never sees length 0\nSELECT NULLIF(date_col, '0000-00-00') AS date_col FROM t;","typeGuard":null,"tryCatchPattern":"try {\n    rs = preparedStatement.executeQuery();\n} catch (SQLFeatureNotSupportedException ex) {\n    if (ex.getMessage().contains(\"year, month, day is absent\")) {\n        // zero-date row in binary protocol: clean the data or select NULLIF(...) and retry once\n        throw new DataQualityException(\"zero date values present; clean them or use NULLIF\", ex);\n    }\n    throw ex;\n}","preventionTips":["Enable NO_ZERO_DATE in sql_mode before new writes","Migrate legacy '0000-00-00' rows to NULL or real dates","Prefer NULLIF(col,'0000-00-00') in queries against suspect legacy tables"],"tags":["mysql","binary-protocol","date","zero-date","jdbc"],"backgroundTag":null,"analyzedSha":"e952770a215630a3659c75d64369168cd3e26b82","analyzedAt":"2026-08-14T13:54:53.392Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}