You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@nifi.apache.org by "ASF subversion and git services (Jira)" <ji...@apache.org> on 2025/10/17 20:40:00 UTC

[jira] [Commented] (NIFI-14614) QueryRecord returns wrong results in some cases since 2.0

    [ https://issues.apache.org/jira/browse/NIFI-14614?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18030753#comment-18030753 ] 

ASF subversion and git services commented on NIFI-14614:
--------------------------------------------------------

Commit e10eceb6c5f34f5decf03ac1421210842ab7b9fd in nifi's branch refs/heads/main from Pierre Villard
[ https://gitbox.apache.org/repos/asf?p=nifi.git;h=e10eceb6c5 ]

NIFI-14614 Fixed QueryRecord column/value misalignment by enforcing projection pushdown (#10269)

Signed-off-by: David Handermann <ex...@apache.org>

> QueryRecord returns wrong results in some cases since 2.0
> ---------------------------------------------------------
>
>                 Key: NIFI-14614
>                 URL: https://issues.apache.org/jira/browse/NIFI-14614
>             Project: Apache NiFi
>          Issue Type: Bug
>    Affects Versions: 2.2.0, 2.3.0, 2.4.0, 2.5.0
>            Reporter: Mahieddine Cherif
>            Assignee: Pierre Villard
>            Priority: Major
>          Time Spent: 1h 20m
>  Remaining Estimate: 0h
>
> Since NiFi 2.2.0 (at least), the QueryRecord returns wrongs results in some cases.
> *Case 1 (Union not working)* 
> As an input we have this JSON
> {code:java}
> [{
>     "employeeId": "1234",
>     "firstName": "XXXX",
>     "lastName": "YYYY",
>     "email": "xxx.yyyy@zzzz.com",
>     "company": "XYZ",
>     "businessUnit": "123",
>     "customReportField1": "1234",
>     "customReportField2": null,
>     "customReportField3": null,
>     "customReportField4": null,
>     "customReportField5": null,
>     "customReportField6": null,
>     "customReportField7": null,
>     "elearningStartDate": "2024-01-10",
>     "elearningEndDate": "2025-01-31",
>     "lastLoginDate": "2024-12-12T11:00:00",
>     "totalTrainingTime": 182837,
>     "totalActivitiesCompleted": 489,
>     "totalWritingLessonsCompleted": 0,
>     "totalConversationClassesAttended": 3,
>     "totalIndividualLessonsAssigned": 0,
>     "totalIndividualLessonsAttended": 0,
>     "totalIndividualLessonsAbsent": 0,
>     "totalIndividualLessonsLateCancellation": 0,
>     "totalIndividualLessonsRemainingScheduled": 0,
>     "totalIndividualLessonsRemainingUnscheduled": 0,
>     "arabicTrainingTime": 0,
>     "arabicActivitiesCompleted": 0,
>     "arabicLastOverallProficiencyLevel": null,
>     "arabicLastOverallProficiencyLevelDate": null,
>     "chineseTrainingTime": 0,
>     "chineseActivitiesCompleted": 0,
>     "chineseIndividualLessonsStartDate": null,
>     "chineseIndividualLessonsEndDate": null,
>     "chineseIndividualLessonsAssigned": 0,
>     "chineseIndividualLessonsAttended": 0,
>     "chineseIndividualLessonsAbsent": 0,
>     "chineseIndividualLessonsLateCancellation": 0,
>     "chineseIndividualLessonsRemainingScheduled": 0,
>     "chineseIndividualLessonsRemainingUnscheduled": 0,
>     "chineseLastOverallProficiencyLevel": null,
>     "chineseLastOverallProficiencyLevelDate": null,
>     "dutchTrainingTime": 0,
>     "dutchActivitiesCompleted": 0,
>     "dutchLastOverallProficiencyLevel": null,
>     "dutchLastOverallProficiencyLevelDate": null,
>     "englishTrainingTime": 114828,
>     "englishActivitiesCompleted": 289,
>     "englishWritingLessonsCompleted": 0,
>     "englishConversationClassesAttended": 3,
>     "englishIndividualLessonsStartDate": null,
>     "englishIndividualLessonsEndDate": null,
>     "englishIndividualLessonsLastAttendedLessonDate": null,
>     "englishIndividualLessonsAssigned": 0,
>     "englishIndividualLessonsAttended": 0,
>     "englishIndividualLessonsAbsent": 0,
>     "englishIndividualLessonsLateCancellation": 0,
>     "englishIndividualLessonsRemainingScheduled": 0,
>     "englishIndividualLessonsRemainingUnscheduled": 0,
>     "englishLastOverallProficiencyLevel": "A2.4",
>     "englishLastOverallProficiencyLevelDate": "2024-09-26T11:35:14.07",
>     "frenchTrainingTime": 0,
>     "frenchActivitiesCompleted": 0,
>     "frenchWritingLessonsCompleted": 0,
>     "frenchConversationClassesAttended": 0,
>     "frenchIndividualLessonsStartDate": null,
>     "frenchIndividualLessonsEndDate": null,
>     "frenchIndividualLessonsLastAttendedLessonDate": null,
>     "frenchIndividualLessonsAssigned": 0,
>     "frenchIndividualLessonsAttended": 0,
>     "frenchIndividualLessonsAbsent": 0,
>     "frenchIndividualLessonsLateCancellation": 0,
>     "frenchIndividualLessonsRemainingScheduled": 0,
>     "frenchIndividualLessonsRemainingUnscheduled": 0,
>     "frenchLastOverallProficiencyLevel": null,
>     "frenchLastOverallProficiencyLevelDate": null,
>     "germanTrainingTime": 10084,
>     "germanActivitiesCompleted": 32,
>     "germanConversationClassesAttended": 0,
>     "germanIndividualLessonsStartDate": null,
>     "germanIndividualLessonsEndDate": null,
>     "germanIndividualLessonsLastAttendedLessonDate": null,
>     "germanIndividualLessonsAssigned": 0,
>     "germanIndividualLessonsAttended": 0,
>     "germanIndividualLessonsAbsent": 0,
>     "germanIndividualLessonsLateCancellation": 0,
>     "germanIndividualLessonsRemainingScheduled": 0,
>     "germanIndividualLessonsRemainingUnscheduled": 0,
>     "germanLastOverallProficiencyLevel": null,
>     "germanLastOverallProficiencyLevelDate": null,
>     "italianTrainingTime": 55,
>     "italianActivitiesCompleted": 0,
>     "italianConversationClassesAttended": 0,
>     "italianIndividualLessonsStartDate": null,
>     "italianIndividualLessonsEndDate": null,
>     "italianIndividualLessonsLastAttendedLessonDate": null,
>     "italianIndividualLessonsAssigned": 0,
>     "italianIndividualLessonsAttended": 0,
>     "italianIndividualLessonsAbsent": 0,
>     "italianIndividualLessonsLateCancellation": 0,
>     "italianIndividualLessonsRemainingScheduled": 0,
>     "italianIndividualLessonsRemainingUnscheduled": 0,
>     "italianLastOverallProficiencyLevel": null,
>     "italianLastOverallProficiencyLevelDate": null,
>     "japaneseTrainingTime": 0,
>     "japaneseActivitiesCompleted": 0,
>     "japaneseLastOverallProficiencyLevel": null,
>     "japaneseLastOverallProficiencyLevelDate": null,
>     "koreanTrainingTime": 0,
>     "koreanActivitiesCompleted": 0,
>     "koreanLastOverallProficiencyLevel": null,
>     "koreanLastOverallProficiencyLevelDate": null,
>     "portugueseTrainingTime": 0,
>     "portugueseActivitiesCompleted": 0,
>     "portugueseConversationClassesAttended": 0,
>     "portugueseIndividualLessonsStartDate": null,
>     "portugueseIndividualLessonsEndDate": null,
>     "portugueseIndividualLessonsLastAttendedLessonDate": null,
>     "portugueseIndividualLessonsAssigned": 0,
>     "portugueseIndividualLessonsAttended": 0,
>     "portugueseIndividualLessonsAbsent": 0,
>     "portugueseIndividualLessonsLateCancellation": 0,
>     "portugueseIndividualLessonsRemainingScheduled": 0,
>     "portugueseIndividualLessonsRemainingUnscheduled": 0,
>     "portugueseLastOverallProficiencyLevel": null,
>     "portugueseLastOverallProficiencyLevelDate": null,
>     "russianTrainingTime": 0,
>     "russianActivitiesCompleted": 0,
>     "russianLastOverallProficiencyLevel": null,
>     "russianLastOverallProficiencyLevelDate": null,
>     "spanishTrainingTime": 69780,
>     "spanishActivitiesCompleted": 168,
>     "spanishConversationClassesAttended": 0,
>     "spanishIndividualLessonsStartDate": null,
>     "spanishIndividualLessonsEndDate": null,
>     "spanishIndividualLessonsLastAttendedLessonDate": null,
>     "spanishIndividualLessonsAssigned": 0,
>     "spanishIndividualLessonsAttended": 0,
>     "spanishIndividualLessonsAbsent": 0,
>     "spanishIndividualLessonsLateCancellation": 0,
>     "spanishIndividualLessonsRemainingScheduled": 0,
>     "spanishIndividualLessonsRemainingUnscheduled": 0,
>     "spanishLastOverallProficiencyLevel": "A2.3",
>     "spanishLastOverallProficiencyLevelDate": "2024-05-13T14:26:17.181"
> }] {code}
> To reproduce this first issue, one can then place a query record with the following query
> {code:java}
> (SELECT employeeId, email, elearningStartDate, lastLoginDate,
>        'en' AS lovCode, 'incomplete' AS trackingStatus,
>        CAST(englishTrainingTime AS INTEGER) AS timeSpent,
>        englishLastOverallProficiencyLevel AS proficiencyLevel
>  FROM FLOWFILE
>  WHERE CAST(englishTrainingTime AS INTEGER))
> UNION ALL
> (SELECT employeeId, email, elearningStartDate, lastLoginDate,
>        'fr' AS lovCode, 'incomplete' AS trackingStatus,
>        CAST(frenchTrainingTime AS INTEGER) AS timeSpent,
>        frenchLastOverallProficiencyLevel AS proficiencyLevel
>  FROM FLOWFILE
>  WHERE CAST(frenchTrainingTime AS INTEGER) <> 0) {code}
> As a reader you can  put a jsonreader (with inferred schema) and json writer with default settings
> In the output I have is this
> {code:java}
> [ {
>   "employeeId" : "1234",
>   "email" : "xxx.yyyy@zzzz.com",
>   "elearningStartDate" : "2024-08-25",
>   "lastLoginDate" : "2024-12-31T06:00:00",
>   "lovCode" : "en",
>   "trackingStatus" : "incomplete",
>   "timeSpent" : 3874,
>   "proficiencyLevel" : null
> }, {
>   "employeeId" : "1234",
>   "email" : "xxx.yyyy@zzzz.com",
>   "elearningStartDate" : "2024-08-25",
>   "lastLoginDate" : "2024-12-31T06:00:00",
>   "lovCode" : "fr",
>   "trackingStatus" : "incomplete",
>   "timeSpent" : 0,
>   "proficiencyLevel" : null
> } ]{code}
> instead of
> {code:java}
> [ {
>   "candidateLogin" : "1234",
>   "candidateEmail" : "xxx.yyyy@zzzz.com",
>   "registrationDate" : "2024-11-18",
>   "lastAccessDate" : "2024-11-18T10:00:00",
>   "lovCode" : "fr",
>   "trackingStatus" : "incomplete",
>   "timeSpent" : 406,
>   "score" : null
> }, {
>   "candidateLogin" : "1234",
>   "candidateEmail" : "xxx.yyyy@zzzz.com",
>   "registrationDate" : "2024-03-22",
>   "lastAccessDate" : "2024-12-31T13:00:00",
>   "lovCode" : "en",
>   "trackingStatus" : "incomplete",
>   "timeSpent" : 114828,
>   "score" : "B2.1"
> }]{code}
> it's like the QueryRecord just takes the first record and copy it and displays it a second time. 
> There are also some cases where the columns in output are not in the orders of the select query. 
> Our queries didn't change between 1.28.0 and 2.4.0 but the output is simply wrong in some cases now. We thought at the begining that the issue could be related to the Apache Calcite version used but even with the latest one 1.39.0 used in nifi 2.4.0 the problem remains. 
> While running the same query on our own internal processor (Similar to the query record in terms of purpose but not code and uses the same apache calcite version 1.39.0) doesn't have the issue.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)