{"action":"created","issue":{"url":"https://api.github.com/repos/apache/incubator-trafodion/issues/441","repository_url":"https://api.github.com/repos/apache/incubator-trafodion","labels_url":"https://api.github.com/repos/apache/incubator-trafodion/issues/441/labels{/name}","comments_url":"https://api.github.com/repos/apache/incubator-trafodion/issues/441/comments","events_url":"https://api.github.com/repos/apache/incubator-trafodion/issues/441/events","html_url":"https://github.com/apache/incubator-trafodion/pull/441","id":149339094,"number":441,"title":"[TRAFODION-1920] suppress SQL error during HIVE_SCAN when encounter i…","user":{"login":"traflm","id":12948514,"avatar_url":"https://avatars.githubusercontent.com/u/12948514?v=3","gravatar_id":"","url":"https://api.github.com/users/traflm","html_url":"https://github.com/traflm","followers_url":"https://api.github.com/users/traflm/followers","following_url":"https://api.github.com/users/traflm/following{/other_user}","gists_url":"https://api.github.com/users/traflm/gists{/gist_id}","starred_url":"https://api.github.com/users/traflm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/traflm/subscriptions","organizations_url":"https://api.github.com/users/traflm/orgs","repos_url":"https://api.github.com/users/traflm/repos","events_url":"https://api.github.com/users/traflm/events{/privacy}","received_events_url":"https://api.github.com/users/traflm/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":16,"created_at":"2016-04-19T03:35:24Z","updated_at":"2016-05-01T04:57:13Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/incubator-trafodion/pulls/441","html_url":"https://github.com/apache/incubator-trafodion/pull/441","diff_url":"https://github.com/apache/incubator-trafodion/pull/441.diff","patch_url":"https://github.com/apache/incubator-trafodion/pull/441.patch"},"body":"…nvalid value, assign null to the invalid value\r\n\r\nThis pull request try to solve the issue when Trafodion try to retrieve data from a HDFS text file which contains invalid data. Like:\r\nspace for a numeric value\r\nnon-numeric string for a numeric value\r\n\r\nthis change will use the HIVE_SCAN_SPECAIL_MODE '2' , when that CQD is set to 2, HIVE_SCAN will automaticlly convert the invalid data into null and continue, instead of error out immediately.\r\n\r\nHow it does?\r\nHIVE_SCAN need to do the data type conversion via expression evaluation. There are two ways to evaluate an expression: pCode and clause's eval() function. Since the current HIVE_SCAN do conversion row by row, and details are encoded in corresponding pCode. So the simplest way I can find out is to fix the invalid data by set it to null and retry the whole evaluation.\r\nIn pCode and eval() function, right after a failure, the new code saves the offset of the offending source data pointer. In HIVE_SCAN work() function, once it got an evaluation error, find and fix the offending data to null , in form of \"R2\" , exploded format,(R2 , refer to comments for FileScan::codeGenForHive) and retry the conversion again. The maximum retry time is the number of columns. If run out of retry time, then raise an error.\r\nTo avoid unexpected side-effect: this change will only modify pCode and normal evaluation a little bit. After an converting error, it tries to remember the offset of offending source data, and that is all. So it will not affect any normal evaluation/execution.\r\nIn HIVE_SCAN, in a special mode controlled by CQD, the code will go into a retry-loop, after the expression evaluation failure.\r\n \r\nIt is easier and maybe faster to change pCode execution to set the target as null and continue, but since pCode is used in many cases, not only HIVE_SCAN move experession. So that change will introduce unknown side effect.\r\n\r\nThis retry logic does introduce overhead, but in normal case the code path is same as before, in special mode, if no invalid data, it will also go through same path as before, the overhead will be there only in case there is converting error.\r\n\r\nThis change only fix from HIVE_SCAN perspective, to fully support transparent Bulkloader during loading, we still need to change the bulkloader work() method to tolerate other source of failure , I suppose. \r\n\r\nHowever, this PR will make sure there will be no failure during HIVE_SCAN due to not-matching data type. Later, if there is CAST, or TRANSLATE or TRUNCATE issues, we still need to change bulkload code.\r\nSo this is the first part to fully support TRAFODION-1920, but it should handle a lot of real cases already.\r\nAdd some sample test cases in hive/TEST005 also, interesting reviewers can check the test for more details.\r\nPlease help to review, and this will be a help in some real use cases."},"comment":{"url":"https://api.github.com/repos/apache/incubator-trafodion/issues/comments/216016521","html_url":"https://github.com/apache/incubator-trafodion/pull/441#issuecomment-216016521","issue_url":"https://api.github.com/repos/apache/incubator-trafodion/issues/441","id":216016521,"user":{"login":"Traf-Jenkins","id":12548626,"avatar_url":"https://avatars.githubusercontent.com/u/12548626?v=3","gravatar_id":"","url":"https://api.github.com/users/Traf-Jenkins","html_url":"https://github.com/Traf-Jenkins","followers_url":"https://api.github.com/users/Traf-Jenkins/followers","following_url":"https://api.github.com/users/Traf-Jenkins/following{/other_user}","gists_url":"https://api.github.com/users/Traf-Jenkins/gists{/gist_id}","starred_url":"https://api.github.com/users/Traf-Jenkins/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Traf-Jenkins/subscriptions","organizations_url":"https://api.github.com/users/Traf-Jenkins/orgs","repos_url":"https://api.github.com/users/Traf-Jenkins/repos","events_url":"https://api.github.com/users/Traf-Jenkins/events{/privacy}","received_events_url":"https://api.github.com/users/Traf-Jenkins/received_events","type":"User","site_admin":false},"created_at":"2016-05-01T04:57:13Z","updated_at":"2016-05-01T04:57:13Z","body":"Check Test Started: https://jenkins.esgyn.com/job/Check-PR-master/689/"}}