{"record":{"id":"09893781548c3ade","repo":"apereo/cas","slug":"missing-field-value-total-from-the-query-results","errorCode":null,"errorMessage":"Missing field value 'total' from the query results for [username] or value not parseable as a number","messagePattern":"Missing field value 'total' from the query results for \\[username\\] or value not parseable as a number","errorType":"exception","errorClass":"FailedLoginException","httpStatus":null,"severity":"error","filePath":"support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryDatabaseAuthenticationHandler.java","lineNumber":73,"sourceCode":"        try {\n            val dbFields = query(credential);\n            if (dbFields.containsKey(properties.getFieldPassword())) {\n                val dbPassword = (String) dbFields.get(properties.getFieldPassword());\n\n                val originalPasswordMatchFails = StringUtils.isNotBlank(originalPassword) && !matches(originalPassword, dbPassword);\n                val originalPasswordEquals = StringUtils.isBlank(originalPassword) && !Strings.CI.equals(password, dbPassword);\n                if (originalPasswordMatchFails || originalPasswordEquals) {\n                    throw new FailedLoginException(\"Password does not match value on record.\");\n                }\n            } else {\n                LOGGER.debug(\"Password field is not found in the query results. Checking for result count...\");\n                if (!dbFields.containsKey(\"total\")) {\n                    throw new FailedLoginException(\"Missing field 'total' from the query results for \" + username);\n                }\n\n                val count = dbFields.get(\"total\");\n                if (count == null || !NumberUtils.isCreatable(count.toString())) {\n                    throw new FailedLoginException(\"Missing field value 'total' from the query results for \"\n                        + username + \" or value not parseable as a number\");\n                }\n\n                val number = NumberUtils.createNumber(count.toString());\n                if (number.longValue() != 1) {\n                    throw new FailedLoginException(\"No records found for user \" + username);\n                }\n            }\n\n            if (StringUtils.isNotBlank(properties.getFieldDisabled()) && dbFields.containsKey(properties.getFieldDisabled())) {\n                val dbDisabled = dbFields.get(properties.getFieldDisabled()).toString();\n                if (BooleanUtils.toBoolean(dbDisabled) || \"1\".equals(dbDisabled)) {\n                    throw new AccountDisabledException(\"Account has been disabled\");\n                }\n            }\n            if (StringUtils.isNotBlank(properties.getFieldExpired()) && dbFields.containsKey(properties.getFieldExpired())) {\n                val dbExpired = dbFields.get(properties.getFieldExpired()).toString();\n                if (BooleanUtils.toBoolean(dbExpired) || \"1\".equals(dbExpired)) {","sourceCodeStart":55,"sourceCodeEnd":91,"githubUrl":"https://github.com/apereo/cas/blob/e7288fc434b4f4505b8452e1a57e8fb3111bb863/support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryDatabaseAuthenticationHandler.java#L55-L91","documentation":"After finding the 'total' column, the handler validates it is a parseable number; if the value is null or NumberUtils.isCreatable fails, it throws FailedLoginException('Missing field value 'total' ... or value not parseable as a number'). This guards the count-based authentication contract.","triggerScenarios":"dbFields contains 'total' but its value is null, or its toString() is not numeric (e.g. empty string, 'NULL' literal, a non-numeric placeholder from the query).","commonSituations":"COALESCE/NULL result from an outer query, alias collides with a non-numeric column, driver returns the string 'NULL', SQL bug returns text instead of a count.","solutions":["Ensure the query computes a real numeric count (COUNT(*)) and never returns NULL for total","Wrap with COALESCE: SELECT COALESCE(COUNT(*),0) AS total ...","Inspect the query result manually to see what value 'total' carries","Fix alias collisions where 'total' maps to a text column"],"exampleFix":"// before\n// SELECT total FROM user_counts WHERE username=?  -- text column\n// after\n// SELECT COUNT(*) AS total FROM users WHERE username=?","handlingStrategy":"validation","validationCode":"Object total = jdbc.queryForMap(sql, user).get(\"total\");\nif (total == null || !NumberUtils.isCreatable(total.toString()))\n    throw new IllegalStateException(\"'total' must be a non-null numeric COUNT(*) value, got: \" + total);","typeGuard":"boolean isNumericCount(Object v) { return v != null && NumberUtils.isCreatable(v.toString()); }","tryCatchPattern":"try {\n    authResult = handler.authenticate(credential);\n} catch (FailedLoginException e) {\n    if (e.getMessage().contains(\"not parseable as a number\")) {\n        log.error(\"'total' column is null/non-numeric; fix SQL to COUNT(*) AS total\");\n        throw new ConfigurationException(\"Bad auth sql projection\");\n    }\n    throw e;\n}","preventionTips":["Use COALESCE(COUNT(*),0) AS total so NULL never appears","Do not alias text columns as 'total'","Keep the sql minimal: one numeric count projection","Add a startup integration test asserting the total value is numeric"],"tags":["jdbc","sql-config","type-mismatch"],"backgroundTag":"unexpected-response-shape","analyzedSha":"e7288fc434b4f4505b8452e1a57e8fb3111bb863","analyzedAt":"2026-09-08T15:39:16.015Z","contentChangedAt":"2026-09-08T15:39:16.015Z","schemaVersion":2},"datasetVersion":"2026-09-15T23:17:13.987Z"}