[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15225548#comment-15225548 ] liu ming commented on TRAFODION-1920: - suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value This should NOT be default behavior, Trafodion should not silently change the data without user's awareness. When it is desired (By setting the CQD), HIVE_SCAN will suppress the SQL error, and fill value with null, and dump the error row into HDFS log file if 'log' option is used. > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15233256#comment-15233256 ] liu ming commented on TRAFODION-1920: - The converting is done by applying convertExpr() inside ExHdfsScanTcb::extractAndTransformAsciiSourceToSqlRow(). We need to modify the Pcode runtime behavior by converting an invalid value into null. We also need to modify the slow path of expression evaluation to do same thing, but priority is lower. > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15247105#comment-15247105 ] ASF GitHub Bot commented on TRAFODION-1920: --- GitHub user traflm opened a pull request: https://github.com/apache/incubator-trafodion/pull/441 [TRAFODION-1920] suppress SQL error during HIVE_SCAN when encounter i… …nvalid value, assign null to the invalid value This pull request try to solve the issue when Trafodion try to retrieve data from a HDFS text file which contains invalid data. Like: space for a numeric value non-numeric string for a numeric value this 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. How it does? HIVE_SCAN need to do the data type conversion via expression evaluation. There are two ways to evaluate an expression: pCode and 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. In pCode and eval() function, right after a failure, it 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 in form of "R2" , exploded format, and retry the conversion. The maxium retry time is the number of columns. So this change will only change pCode and normal evaluation a little bit. After an error, try to remember the offset of offending source data. It will not affect any normal evaluation/execution. Only in HIVE_SCAN, in a special mode controlled by CQD, it will retry after the failure. This will not introduce any unknown side effort. It 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. This 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, just retry in case there is converting error. This change only fix from HIVE_SCAN perspective, to fully support transparent Bulkloader, we still need to change the bulkloader work() method to tolerate other source of failure. This change will make sure there will be no failure during HIVE_SCAN. Later, if there is CAST, or TRANSLATE or TRUNCATE issues, we still need to change bulkload code. So this is the first part to fully support TRAFODION-1920, but it should handle a lot of real cases already. Add some sample test cases in hive/TEST005 also, interesting reviewers can check the test for more details. Please help to review, and this will be a help in some real use cases. You can merge this pull request into a Git repository by running: $ git pull https://github.com/traflm/incubator-trafodion TRAFODION-1912 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/incubator-trafodion/pull/441.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #441 commit 8f78b446e9002ff9cd5e0f3ca2b5feead9726859 Author: Liu Ming Date: 2016-04-19T03:02:16Z [TRAFODION-1920] suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15248916#comment-15248916
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r60329781
--- Diff: core/sql/executor/ExHdfsScan.cpp ---
@@ -1378,9 +1423,27 @@ ExWorkProcRetcode ExHdfsScanTcb::work()
return WORK_OK;
}
+void ExHdfsScanTcb::setR2ColumnNull(Int32 colidx)
+{
+ Lng32 neededColIndex = 0;
+ Attributes * attr = NULL;
+ ExpTupleDesc * asciiSourceTD =
hdfsScanTdb().workCriDesc_->getTupleDescriptor(hdfsScanTdb().asciiTuppIndex_);
+ for (Lng32 i = 0; i < hdfsScanTdb().convertSkipListSize_; i++)
+ {
+if (hdfsScanTdb().convertSkipList_[i] > 0)
+{
+ attr = asciiSourceTD->getAttr(neededColIndex);
+ neededColIndex++;
+ if(attr->getOffset() == colidx)
+ {
+ *(short *)&hdfsAsciiSourceData_[attr->getNullIndOffset()] = -1;
+ }
+}
+ }
+}
--- End diff --
I'm still reviewing, and I'm not familiar with this code. I'm trying to
understand how this works. Does hdfsAsciiSourceData_ point to an ASCII string?
Why would putting two bytes of x'ff' into the string make a value look like a
null? How does the move expression parse to the next field?
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15249051#comment-15249051
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user traflm commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r60336260
--- Diff: core/sql/executor/ExHdfsScan.cpp ---
@@ -1378,9 +1423,27 @@ ExWorkProcRetcode ExHdfsScanTcb::work()
return WORK_OK;
}
+void ExHdfsScanTcb::setR2ColumnNull(Int32 colidx)
+{
+ Lng32 neededColIndex = 0;
+ Attributes * attr = NULL;
+ ExpTupleDesc * asciiSourceTD =
hdfsScanTdb().workCriDesc_->getTupleDescriptor(hdfsScanTdb().asciiTuppIndex_);
+ for (Lng32 i = 0; i < hdfsScanTdb().convertSkipListSize_; i++)
+ {
+if (hdfsScanTdb().convertSkipList_[i] > 0)
+{
+ attr = asciiSourceTD->getAttr(neededColIndex);
+ neededColIndex++;
+ if(attr->getOffset() == colidx)
+ {
+ *(short *)&hdfsAsciiSourceData_[attr->getNullIndOffset()] = -1;
+ }
+}
+ }
+}
--- End diff --
thanks Dave,
Please refer to the very excellent comments above src code in
FileScan::codeGenForHive().
The hdfsAsciiSourceData_ is the pointer to row 'R2' referred to in those
comments. It is in a special internal tuple format called 'exploded format
row', each field in this tuple contains at least two parts as far as I know:
first byte is null-indicator, when it is 0, means not null, when it is 0xFF
(-1), it means null. After the one byte null indicator, it follows 8 bytes of
value, but it is not the real value, just a pointer to R1. When need, it needs
to deference the pointer. R1 is a buffer containing raw data read from HDFS
file. R3 is for predicate, R4 is for projection and return rows.
So set 0xff here will set required field in R2 as null, in this case, it is
because it contains invalid data, like 'abc' for an Integer field. And the
moveColsConvertExpr expression failed to convert it into an integer. So the
logic go back here , calling this method to set the invalid data field to null.
And try again. Because the evaluation is now done row by row, not column by
column, so this is the simplest way to change the logic at present . There are
better ways, but without more guidance, I cannot do those, for example: do this
converting column by column, this requires a huge change in compiler to
generate different pCode and expression tree, which beyond my knowledge.
I didn't change any current normal logic.
The move expression typically will generate a piece of pCode instructions,
if the row has 3 columns, the generated pCode will have 3 sets of instructions
like this(instruction name is not valid, just try to explain what I want to
mean)
IFNULL
INST1
ELSE
INST2
I modified INST2 to save the offset of invalid data offset in 'R2', and
everything else keep same.
If pCode is turned off, the expression will be evaluated in normal clause
evaluation path. It is a list of clauses, each clause correspond to an
operation, and each column has a convert clause to do the data converting if
needed. This is generated inside compiler, I don't know the details, parser
should generate a tree of ItemExpr, and gencode will generate correct
expression.
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15251240#comment-15251240
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user selvaganesang commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r60524880
--- Diff: core/sql/executor/ExHdfsScan.cpp ---
@@ -1378,9 +1423,27 @@ ExWorkProcRetcode ExHdfsScanTcb::work()
return WORK_OK;
}
+void ExHdfsScanTcb::setR2ColumnNull(Int32 colidx)
+{
+ Lng32 neededColIndex = 0;
+ Attributes * attr = NULL;
+ ExpTupleDesc * asciiSourceTD =
hdfsScanTdb().workCriDesc_->getTupleDescriptor(hdfsScanTdb().asciiTuppIndex_);
+ for (Lng32 i = 0; i < hdfsScanTdb().convertSkipListSize_; i++)
+ {
+if (hdfsScanTdb().convertSkipList_[i] > 0)
+{
+ attr = asciiSourceTD->getAttr(neededColIndex);
+ neededColIndex++;
+ if(attr->getOffset() == colidx)
+ {
+ *(short *)&hdfsAsciiSourceData_[attr->getNullIndOffset()] = -1;
+ }
+}
+ }
+}
--- End diff --
I wonder if we need to change it to NULL at line no 1585 when convertExpr()
returns an error if the column is nullable and the cqd is set to convert to
null. It is not clear to me the need for other changes.
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15251288#comment-15251288
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user selvaganesang commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r60527463
--- Diff: core/sql/executor/ExHdfsScan.cpp ---
@@ -1378,9 +1423,27 @@ ExWorkProcRetcode ExHdfsScanTcb::work()
return WORK_OK;
}
+void ExHdfsScanTcb::setR2ColumnNull(Int32 colidx)
+{
+ Lng32 neededColIndex = 0;
+ Attributes * attr = NULL;
+ ExpTupleDesc * asciiSourceTD =
hdfsScanTdb().workCriDesc_->getTupleDescriptor(hdfsScanTdb().asciiTuppIndex_);
+ for (Lng32 i = 0; i < hdfsScanTdb().convertSkipListSize_; i++)
+ {
+if (hdfsScanTdb().convertSkipList_[i] > 0)
+{
+ attr = asciiSourceTD->getAttr(neededColIndex);
+ neededColIndex++;
+ if(attr->getOffset() == colidx)
+ {
+ *(short *)&hdfsAsciiSourceData_[attr->getNullIndOffset()] = -1;
+ }
+}
+ }
+}
--- End diff --
I think both convertExpr() and moveColsConvertExpr() deals at a row level.
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15251830#comment-15251830
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user traflm commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r60572080
--- Diff: core/sql/executor/ExHdfsScan.cpp ---
@@ -1378,9 +1423,27 @@ ExWorkProcRetcode ExHdfsScanTcb::work()
return WORK_OK;
}
+void ExHdfsScanTcb::setR2ColumnNull(Int32 colidx)
+{
+ Lng32 neededColIndex = 0;
+ Attributes * attr = NULL;
+ ExpTupleDesc * asciiSourceTD =
hdfsScanTdb().workCriDesc_->getTupleDescriptor(hdfsScanTdb().asciiTuppIndex_);
+ for (Lng32 i = 0; i < hdfsScanTdb().convertSkipListSize_; i++)
+ {
+if (hdfsScanTdb().convertSkipList_[i] > 0)
+{
+ attr = asciiSourceTD->getAttr(neededColIndex);
+ neededColIndex++;
+ if(attr->getOffset() == colidx)
+ {
+ *(short *)&hdfsAsciiSourceData_[attr->getNullIndOffset()] = -1;
+ }
+}
+ }
+}
--- End diff --
Thanks Selva, I agree, will change it as well. It will support more
converting cases. If simply select * from hive, convertExpr() will be null. So
I ignore it. But if one add some cast in the select list, it will be invoked,
so same logic should apply to it as well.
I will have an update asap.
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15265807#comment-15265807
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user anoopsharma00 commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r61689153
--- Diff: core/sql/exp/exp_eval.cpp ---
@@ -862,7 +862,17 @@ ex_expr::exp_return_type
ex_expr::evalClauses(ex_clause *clause,
atp1->setDiagsArea(diagsArea);
if (retcode == ex_expr::EXPR_ERROR)
- return retcode;
+ {
+if((getPCodeMode() & ex_expr::ERROR_CONTINUE) > 0)
+{
+ ExpTupleDesc::setNullValue( op_data[0],
+
clause->getOperand(0)->getNullBitIndex(),
+
clause->getOperand(0)->getTupleFormat() );
+ retcode = ex_expr::EXPR_OK;
+}
+else
+ return retcode;
+ }
--- End diff --
-- This change should be inside of ex_conv_clause::eval when an error
happens and not in the mainline.
-- a new flag bit should be set at code generation time in ex_conv_clause
(something like NullOnError).
This flag bit will be checked in ex_conv_clause::eval method. That way
handling of this case of NullOnError
will be contained within that method. That method should return ok after
moving null value to target in case
of error.
-- No need to have a new pcode mode. The ERROR_CONTINUE setting could be
removed
-- No change will be needed in mainline exp eval code
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15265806#comment-15265806
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user anoopsharma00 commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r61689151
--- Diff: core/sql/exp/exp_eval.cpp ---
@@ -862,7 +862,17 @@ ex_expr::exp_return_type
ex_expr::evalClauses(ex_clause *clause,
atp1->setDiagsArea(diagsArea);
if (retcode == ex_expr::EXPR_ERROR)
- return retcode;
+ {
+if((getPCodeMode() & ex_expr::ERROR_CONTINUE) > 0)
+{
+ ExpTupleDesc::setNullValue( op_data[0],
+
clause->getOperand(0)->getNullBitIndex(),
+
clause->getOperand(0)->getTupleFormat() );
+ retcode = ex_expr::EXPR_OK;
--- End diff --
-- this code also will return back from line #871 on the first conversion
clause error instead
of continuing to the next clause.
-- it will also not work correctly if getPCodeMode() is not set.
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15265808#comment-15265808 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user anoopsharma00 commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/441#discussion_r61689166 --- Diff: core/sql/exp/exp_eval.cpp --- @@ -2953,6 +2963,7 @@ ex_expr::exp_return_type ex_expr::evalPCode(PCodeBinary* pCode32, Int32 srcLen = 0; PTR_DEF_ASSIGN(char, dst, 0); BASE_PTR_DEF_ASSIGN(char, src, 2); + BASE_PTR_DEF_ASSIGN(char, rowstart, 0); --- End diff -- what is rowstart used for? > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15265809#comment-15265809 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user anoopsharma00 commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/441#discussion_r61689196 --- Diff: core/sql/exp/exp_eval.cpp --- @@ -3003,7 +3014,7 @@ ex_expr::exp_return_type ex_expr::evalPCode(PCodeBinary* pCode32, if (diagsArea != atp1->getDiagsArea()) atp1->setDiagsArea(diagsArea); if (er == ex_expr::EXPR_ERROR) - return ex_expr::EXPR_ERROR; + return ex_expr::EXPR_ERROR; --- End diff -- indentation has changed at many places with the new code. Not sure what editor was used. Will be better to keep the older indentation. > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15266106#comment-15266106 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user traflm commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/441#discussion_r61701808 --- Diff: core/sql/exp/exp_eval.cpp --- @@ -3003,7 +3014,7 @@ ex_expr::exp_return_type ex_expr::evalPCode(PCodeBinary* pCode32, if (diagsArea != atp1->getDiagsArea()) atp1->setDiagsArea(diagsArea); if (er == ex_expr::EXPR_ERROR) - return ex_expr::EXPR_ERROR; + return ex_expr::EXPR_ERROR; --- End diff -- yes, will fix this. I use vim, but this line changed a lot in previous modification and changed the indentation, will restore it correctly. > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15266108#comment-15266108 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user traflm commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/441#discussion_r61701830 --- Diff: core/sql/exp/exp_eval.cpp --- @@ -2953,6 +2963,7 @@ ex_expr::exp_return_type ex_expr::evalPCode(PCodeBinary* pCode32, Int32 srcLen = 0; PTR_DEF_ASSIGN(char, dst, 0); BASE_PTR_DEF_ASSIGN(char, src, 2); + BASE_PTR_DEF_ASSIGN(char, rowstart, 0); --- End diff -- left over... Will remove it > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[
https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15266113#comment-15266113
]
ASF GitHub Bot commented on TRAFODION-1920:
---
Github user anoopsharma00 commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/441#discussion_r61702049
--- Diff: core/sql/exp/exp_eval.cpp ---
@@ -862,7 +862,17 @@ ex_expr::exp_return_type
ex_expr::evalClauses(ex_clause *clause,
atp1->setDiagsArea(diagsArea);
if (retcode == ex_expr::EXPR_ERROR)
- return retcode;
+ {
+if((getPCodeMode() & ex_expr::ERROR_CONTINUE) > 0)
+{
+ ExpTupleDesc::setNullValue( op_data[0],
+
clause->getOperand(0)->getNullBitIndex(),
+
clause->getOperand(0)->getTupleFormat() );
+ retcode = ex_expr::EXPR_OK;
--- End diff --
Couple things:
Codegen time: The flag to move null in case of an error should be set only
if target is nullable.
Runtime: Expr code should check to make sure that target is nullable before
moving
the null value. If target is non-nullable, then original error should be
returned.
> suppress SQL error during HIVE_SCAN when encounter invalid value, assign null
> to the invalid value
> --
>
> Key: TRAFODION-1920
> URL: https://issues.apache.org/jira/browse/TRAFODION-1920
> Project: Apache Trafodion
> Issue Type: Sub-task
>Reporter: liu ming
>Assignee: liu ming
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15268927#comment-15268927 ] ASF GitHub Bot commented on TRAFODION-1920: --- GitHub user traflm opened a pull request: https://github.com/apache/incubator-trafodion/pull/461 [TRAFODION-1920] suppress SQL error during HIVE_SCAN when conv error A new PR for JIRA 1920. When read from HDFS in HIVE_SCAN and encounter invalid data, continue and set the offending data to null. Latest implementation details: In code gen of HIVE_SCAN, if the CQD HIVE_SCAN_SPECIAL_MODE is set to '2'. Generate a CAST function setting a special flag. In that CAST codegen, it will set runtime flag based on the special flag. This will create a special ex_conv_clause. Codegen also disable pCode in this mode. At runtime, ex_conv_caluse::eval() will move null into target if convDoIt() failed. And continue. You can merge this pull request into a Git repository by running: $ git pull https://github.com/traflm/incubator-trafodion TRAFODION-1920 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/incubator-trafodion/pull/461.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #461 commit 67b89f8ad6c1dc0b30607d98d6e5a141a79ae3e2 Author: Liu Ming Date: 2016-05-03T15:48:03Z [TRAFODION-1920] suppress SQL error during HIVE_SCAN when conv error > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15268929#comment-15268929 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user traflm closed the pull request at: https://github.com/apache/incubator-trafodion/pull/441 > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270654#comment-15270654 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user selvaganesang commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/461#discussion_r62041333 --- Diff: core/sql/regress/hive/EXPECTED005 --- @@ -534,4 +534,101 @@ C1 C2 >>cqd HIVE_SCAN_SPECIAL_MODE reset; --- SQL operation complete. +>>select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>select * from tbl_bad; + +C1 C2C3 C4 C5 C6 C7 C8 +--- - --- -- -- - -- + + ? ? c ? ? ? ? ? + ? ? c ? ? 2017-01-01 10:10:10.00 1.01000E+000 1 + ? ? ? ? ? ? ? + 1 1 averylongstring -1.000E+000 0 2017-01-01 10:10:10.00 1.00010E+002 1 + 2 2 good 1.100E+000 2 2017-01-01 10:10:10.00 2.0E+002 1000 + 3 3 good 1.000E+000 2 2017-01-01 10:10:10.00 2.1E+002 10 + ?4294967295 good 3.399E+038 ? 2017-01-01 10:10:10.00 1.69968E+308 10 + 099 bad ? ? ? ? ? + +--- 8 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. +>>drop table if exists trafodion.seabase.traf_tbl_bad; + +--- SQL operation complete. +>>create table trafodion.seabase.traf_tbl_bad ( ++>c1 int, ++>c2 largeint, ++>c3 varchar(25), ++>c4 real, ++>c5 smallint, ++>c6 timestamp(6), ++>c7 float(54), ++>c8 smallint ++>); + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) loaded. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) inserted. +>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) inserted. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) loaded. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) inserted. +>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) inserted. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. +>>drop table if exists trafodion.seabase.traf_tbl_bad; + +--- SQL operation complete. +>>create table trafodion.seabase.traf_tbl_bad ( ++>c1 int not null, ++>c2 largeint not null, ++>c3 varchar(25), ++>c4 real, ++>c5 smallint, ++>c6 timestamp(6) not null, ++>c7 float(54) not null, ++>c8 smallint ++>); + +--- SQL operation complete. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + --- End diff -- Good that we are getting this error. Are we supposed to get the original error? Or what error will be seen is non-deterministic because it depends which node the expression gets evaluated? > su
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270668#comment-15270668 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user anoopsharma00 commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/461#discussion_r62043077 --- Diff: core/sql/generator/GenRelScan.cpp --- @@ -880,6 +884,12 @@ short FileScan::codeGenForHive(Generator * generator) } // for (ii = 0; ii < hdfsVals; ii++) + UInt32 hiveScanMode = CmpCommon::getDefaultLong(HIVE_SCAN_SPECIAL_MODE); + //enhance pCode to handle this mode in the future + //this is for JIRA 1920 + if((hiveScanMode & 2 ) > 0) //if HIVE_SCAN_SPECIAL_MODE is 2, disable pCode +exp_gen->setPCodeMode(ex_expr::PCODE_NONE); + --- End diff -- This will disable pcode for the whole expression and all conversions. Better place to disable it is in ex_conv_clause::pcodeGenererate in file exp/ExpPCodeClauseGen.cpp. That code should check for conv_to_null_when_error flag in ex_conv_clause and disable pcode just for that clause. > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270673#comment-15270673 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user anoopsharma00 commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/461#discussion_r62044508 --- Diff: core/sql/regress/hive/EXPECTED005 --- @@ -534,4 +534,101 @@ C1 C2 >>cqd HIVE_SCAN_SPECIAL_MODE reset; --- SQL operation complete. +>>select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>select * from tbl_bad; + +C1 C2C3 C4 C5 C6 C7 C8 +--- - --- -- -- - -- + + ? ? c ? ? ? ? ? + ? ? c ? ? 2017-01-01 10:10:10.00 1.01000E+000 1 + ? ? ? ? ? ? ? + 1 1 averylongstring -1.000E+000 0 2017-01-01 10:10:10.00 1.00010E+002 1 + 2 2 good 1.100E+000 2 2017-01-01 10:10:10.00 2.0E+002 1000 + 3 3 good 1.000E+000 2 2017-01-01 10:10:10.00 2.1E+002 10 + ?4294967295 good 3.399E+038 ? 2017-01-01 10:10:10.00 1.69968E+308 10 + 099 bad ? ? ? ? ? + +--- 8 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. +>>drop table if exists trafodion.seabase.traf_tbl_bad; + +--- SQL operation complete. +>>create table trafodion.seabase.traf_tbl_bad ( ++>c1 int, ++>c2 largeint, ++>c3 varchar(25), ++>c4 real, ++>c5 smallint, ++>c6 timestamp(6), ++>c7 float(54), ++>c8 smallint ++>); + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) loaded. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) inserted. +>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) inserted. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) loaded. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) inserted. +>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) inserted. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. +>>drop table if exists trafodion.seabase.traf_tbl_bad; + +--- SQL operation complete. +>>create table trafodion.seabase.traf_tbl_bad ( ++>c1 int not null, ++>c2 largeint not null, ++>c3 varchar(25), ++>c4 real, ++>c5 smallint, ++>c6 timestamp(6) not null, ++>c7 float(54) not null, ++>c8 smallint ++>); + +--- SQL operation complete. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + --- End diff -- It might be helpful if the original conversion error is added as a warning. Maybe based on some setting(cqd), warnings could be added and returned. One can also check for max number of warnings i
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15271693#comment-15271693 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user traflm commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/461#discussion_r62136119 --- Diff: core/sql/generator/GenRelScan.cpp --- @@ -880,6 +884,12 @@ short FileScan::codeGenForHive(Generator * generator) } // for (ii = 0; ii < hdfsVals; ii++) + UInt32 hiveScanMode = CmpCommon::getDefaultLong(HIVE_SCAN_SPECIAL_MODE); + //enhance pCode to handle this mode in the future + //this is for JIRA 1920 + if((hiveScanMode & 2 ) > 0) //if HIVE_SCAN_SPECIAL_MODE is 2, disable pCode +exp_gen->setPCodeMode(ex_expr::PCODE_NONE); + --- End diff -- I agree that will be much better. I need to understand that part further, a simple change cannot work, I need to debug more on this. > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15271701#comment-15271701 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user traflm commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/461#discussion_r62136601 --- Diff: core/sql/regress/hive/EXPECTED005 --- @@ -534,4 +534,101 @@ C1 C2 >>cqd HIVE_SCAN_SPECIAL_MODE reset; --- SQL operation complete. +>>select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>select * from tbl_bad; + +C1 C2C3 C4 C5 C6 C7 C8 +--- - --- -- -- - -- + + ? ? c ? ? ? ? ? + ? ? c ? ? 2017-01-01 10:10:10.00 1.01000E+000 1 + ? ? ? ? ? ? ? + 1 1 averylongstring -1.000E+000 0 2017-01-01 10:10:10.00 1.00010E+002 1 + 2 2 good 1.100E+000 2 2017-01-01 10:10:10.00 2.0E+002 1000 + 3 3 good 1.000E+000 2 2017-01-01 10:10:10.00 2.1E+002 10 + ?4294967295 good 3.399E+038 ? 2017-01-01 10:10:10.00 1.69968E+308 10 + 099 bad ? ? ? ? ? + +--- 8 row(s) selected. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. +>>drop table if exists trafodion.seabase.traf_tbl_bad; + +--- SQL operation complete. +>>create table trafodion.seabase.traf_tbl_bad ( ++>c1 int, ++>c2 largeint, ++>c3 varchar(25), ++>c4 real, ++>c5 smallint, ++>c6 timestamp(6), ++>c7 float(54), ++>c8 smallint ++>); + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) loaded. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) inserted. +>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +*** ERROR[8413] The string argument contains characters that cannot be converted. + +--- 0 row(s) inserted. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) loaded. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) inserted. +>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + +--- 8 row(s) inserted. +>>cqd HIVE_SCAN_SPECIAL_MODE reset; + +--- SQL operation complete. +>>drop table if exists trafodion.seabase.traf_tbl_bad; + +--- SQL operation complete. +>>create table trafodion.seabase.traf_tbl_bad ( ++>c1 int not null, ++>c2 largeint not null, ++>c3 varchar(25), ++>c4 real, ++>c5 smallint, ++>c6 timestamp(6) not null, ++>c7 float(54) not null, ++>c8 smallint ++>); + +--- SQL operation complete. +>>cqd HIVE_SCAN_SPECIAL_MODE '2'; + +--- SQL operation complete. +>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; + --- End diff -- Yes, the error message will depends on which node the expression get evaluated, so it is better to add converting error as warnings. I need to better understand details of DiagArea, I didn't remove erro
[jira] [Commented] (TRAFODION-1920) suppress SQL error during HIVE_SCAN when encounter invalid value, assign null to the invalid value
[ https://issues.apache.org/jira/browse/TRAFODION-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15275443#comment-15275443 ] ASF GitHub Bot commented on TRAFODION-1920: --- Github user asfgit closed the pull request at: https://github.com/apache/incubator-trafodion/pull/461 > suppress SQL error during HIVE_SCAN when encounter invalid value, assign null > to the invalid value > -- > > Key: TRAFODION-1920 > URL: https://issues.apache.org/jira/browse/TRAFODION-1920 > Project: Apache Trafodion > Issue Type: Sub-task >Reporter: liu ming >Assignee: liu ming > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
