LPAD/RPAD Parsing Issues

[#1]

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?

[#2]

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