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(); }