Insert data exceeding fixed length column.
Hello, seems sql standard regulate situation when insertion (char,
varchar) data exceeds fixed len column as :
If the declared type T is fixed length string with length in characters L
and the
length in characters M of V is larger than L, then
Case:
a) If the right part M–L characters of V are all space`s, then the value
of T is set to the first
L characters of V.
b) If one or more of the right part M–L characters of V are not space`s,
then exception is raised.
But fast test [1] in SqlToRelConverterTest highlights that calcite not
satisfy of such a rule.
I miss something or need to fill the issue ?
Thanks !
[1]
@Test void testInsert1() {
String moreThanVarcharLimit = "a".repeat(30);
final String sql = "insert into emp (empno, ename, job, mgr,
hiredate,\n"
+ " sal, comm, deptno, slacker)\n"
+ "values(1, '" + moreThanVarcharLimit + "', 'job', 0,\n"
+ " timestamp '1970-01-01 00:00:00', 1, 1, 1, false)";
sql(sql).ok();
}
Re: Insert data exceeding fixed length column.
I agree that that query should throw when executed. If it doesn’t, you should
log a bug.
However, that test in SqlToRelConverterTest doesn’t prove that Calcite doesn’t
throw at execute time.
> On Feb 8, 2023, at 11:20 PM, stanilovsky evgeny
> wrote:
>
>
> Hello, seems sql standard regulate situation when insertion (char, varchar)
> data exceeds fixed len column as :
>
> If the declared type T is fixed length string with length in characters L and
> the
> length in characters M of V is larger than L, then
> Case:
> a) If the right part M–L characters of V are all space`s, then the value of T
> is set to the first
> L characters of V.
> b) If one or more of the right part M–L characters of V are not space`s, then
> exception is raised.
>
> But fast test [1] in SqlToRelConverterTest highlights that calcite not
> satisfy of such a rule.
>
> I miss something or need to fill the issue ?
>
> Thanks !
>
>
> [1]
> @Test void testInsert1() {
>String moreThanVarcharLimit = "a".repeat(30);
>
>final String sql = "insert into emp (empno, ename, job, mgr, hiredate,\n"
>+ " sal, comm, deptno, slacker)\n"
>+ "values(1, '" + moreThanVarcharLimit + "', 'job', 0,\n"
>+ " timestamp '1970-01-01 00:00:00', 1, 1, 1, false)";
>sql(sql).ok();
> }
Re: Insert data exceeding fixed length column.
Ok, thanks i understand what you are talking about, i will move the test
and fill issue if it necessary.
I agree that that query should throw when executed. If it doesn’t, you
should log a bug.
However, that test in SqlToRelConverterTest doesn’t prove that Calcite
doesn’t throw at execute time.
On Feb 8, 2023, at 11:20 PM, stanilovsky evgeny
wrote:
Hello, seems sql standard regulate situation when insertion (char,
varchar) data exceeds fixed len column as :
If the declared type T is fixed length string with length in characters
L and the
length in characters M of V is larger than L, then
Case:
a) If the right part M–L characters of V are all space`s, then the
value of T is set to the first
L characters of V.
b) If one or more of the right part M–L characters of V are not
space`s, then exception is raised.
But fast test [1] in SqlToRelConverterTest highlights that calcite not
satisfy of such a rule.
I miss something or need to fill the issue ?
Thanks !
[1]
@Test void testInsert1() {
String moreThanVarcharLimit = "a".repeat(30);
final String sql = "insert into emp (empno, ename, job, mgr,
hiredate,\n"
+ " sal, comm, deptno, slacker)\n"
+ "values(1, '" + moreThanVarcharLimit + "', 'job', 0,\n"
+ " timestamp '1970-01-01 00:00:00', 1, 1, 1, false)";
sql(sql).ok();
}
Re: Insert data exceeding fixed length column.
I rewrite test with end-to-end usage (hope it`s correct) and put it into
blank.iq (test is near) and found that it not follow the sql standard.
Also probably someone can suggest how can i write test with dynamic params
involved ? I found that Quidem has no appropriate functionality.
Thanks !
create table fix_char_col (i int, c char(5));
(0 rows modified)
!update
insert into fix_char_col values (1, 'a'), (2, 'aa'), (3, '
aa'), (4, 'aa'), (5, ' aa');
(1 row modified)
!update
SELECT * FROM fix_char_col ORDER BY i;
+---+---+
| I | C |
+---+---+
| 1 | a |
| 2 | a |
| 3 | |
| 4 | aa|
| 5 | aa |
+---+---+
(2 rows)
!ok
and as a result :
expected:
< +---+---+
< | I | C |
< +---+---+
< | 1 | a |
< | 2 | a |
< | 3 | |
< | 4 | aa|
< | 5 | aa |
< +---+---+
calcite output:
+---+-+
| I | C |
+---+-+
| 1 | a |
| 2 | aa |
| 3 | aa |
| 4 | aa |
| 5 | aa |
+---+-+
Ok, thanks i understand what you are talking about, i will move the test
and fill issue if it necessary.
I agree that that query should throw when executed. If it doesn’t, you
should log a bug.
However, that test in SqlToRelConverterTest doesn’t prove that Calcite
doesn’t throw at execute time.
On Feb 8, 2023, at 11:20 PM, stanilovsky evgeny
wrote:
Hello, seems sql standard regulate situation when insertion (char,
varchar) data exceeds fixed len column as :
If the declared type T is fixed length string with length in
characters L and the
length in characters M of V is larger than L, then
Case:
a) If the right part M–L characters of V are all space`s, then the
value of T is set to the first
L characters of V.
b) If one or more of the right part M–L characters of V are not
space`s, then exception is raised.
But fast test [1] in SqlToRelConverterTest highlights that calcite not
satisfy of such a rule.
I miss something or need to fill the issue ?
Thanks !
[1]
@Test void testInsert1() {
String moreThanVarcharLimit = "a".repeat(30);
final String sql = "insert into emp (empno, ename, job, mgr,
hiredate,\n"
+ " sal, comm, deptno, slacker)\n"
+ "values(1, '" + moreThanVarcharLimit + "', 'job', 0,\n"
+ " timestamp '1970-01-01 00:00:00', 1, 1, 1, false)";
sql(sql).ok();
}
