[
https://issues.apache.org/jira/browse/NIFI-14614?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pierre Villard updated NIFI-14614:
----------------------------------
Affects Version/s: 2.5.0
Status: Patch Available (was: Open)
> 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.5.0, 2.4.0, 2.3.0, 2.2.0
> Reporter: Mahieddine Cherif
> Assignee: Pierre Villard
> Priority: Major
>
> 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": "[email protected]",
> "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" : "[email protected]",
> "elearningStartDate" : "2024-08-25",
> "lastLoginDate" : "2024-12-31T06:00:00",
> "lovCode" : "en",
> "trackingStatus" : "incomplete",
> "timeSpent" : 3874,
> "proficiencyLevel" : null
> }, {
> "employeeId" : "1234",
> "email" : "[email protected]",
> "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" : "[email protected]",
> "registrationDate" : "2024-11-18",
> "lastAccessDate" : "2024-11-18T10:00:00",
> "lovCode" : "fr",
> "trackingStatus" : "incomplete",
> "timeSpent" : 406,
> "score" : null
> }, {
> "candidateLogin" : "1234",
> "candidateEmail" : "[email protected]",
> "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)
