LPAD/RPAD Parsing Issues

garb — Wed, 10 Dec 2025 16:32:45 GMT

NiFi Version: 2.5.0

When transporting information from one site to another, I need to prepend a 0 to someones PIN as that site has different requirements. I query the data using a QueryRecord processor, like so:

SELECT MsgSeqNbr, PostTime, SSN, EmployeeID, LName, FName, MName, CAST(TRIM(LCN) AS BIGINT) AS LCN, LPAD(PIN, 5, '0'), EmployeeType, ValidityCode, AgencyOwner, AgencyLocated, BadgeCreatedBy, BadgeCreatedTime, BadgeModifiedBy, BadgeModifiedTime, Clearance, Error, Status FROM FLOWFILE

However I get the following error from Calcite:

QUERY... - Caused by: org.apache.calcite.runtime.CalciteContentException: From line 5, column 5, column X: No match found for function signature LPAD(<CHARACTER>, <NUMERIC>, <CHARACTER>).

I've tried a couple different methods to get this to pass, explicitly defining the type as VARCHAR(5) for both the string and the pattern, using a static value to see if it will pass but the same result occurs.

The incoming Avro data has the PIN like so:

... "PIN" : "111", ...

So I know the data type it's receiving truly is a string. Following the ticket that was created for the original implementation it seems the dev's had the same intent for how to use the function: https://issues.apache.org/jira/browse/CALCITE-5451

Anyone else encounter a similar issue with Calcite?

Re: LPAD/RPAD Parsing Issues

vafs — Wed, 04 Feb 2026 14:39:11 GMT

Hello @garb,

Thanks for being part of our community.

I was reviewing the information and even though Calcite is the engine used for the SQL, looks like not all the queries are supported officially.

Looking in several places, I do not see LPAD used anywhere.
But something that may work for what you need is CONCAT, which should give you the correct format properly and is broadly used in the community:

SELECT MsgSeqNbr, PostTime, SSN, EmployeeID, LName, FName, MName, CAST(TRIM(LCN) AS BIGINT) AS LCN, RIGHT(CONCAT('00000', PIN), 5) AS PIN, EmployeeType, ValidityCode, AgencyOwner, AgencyLocated, BadgeCreatedBy, BadgeCreatedTime, BadgeModifiedBy, BadgeModifiedTime, Clearance, Error, Status FROM FLOWFILE

Based on the error "No match found for function signature LPAD" it looks like the engine configured for NiFi does not support LPAD even when Calcite do support it.
I was trying to find on the code the supported functions, but did not find LPAD.
This looks to be the most accurate reference we have where we do see CONCAT: https://github.com/apache/nifi/blob/main/nifi-docs/src/main/asciidoc/record-path-guide.adoc

question Re: LPAD/RPAD Parsing Issues in Support Questions

LPAD/RPAD Parsing Issues

Re: LPAD/RPAD Parsing Issues