Thanks Julian. FYI, I submitted a pull request <https://github.com/apache/calcite/pull/283> for CALCITE-1386 <https://issues.apache.org/jira/browse/CALCITE-1386>, and after some testing I found the patch covers CALCITE-1387 <https://issues.apache.org/jira/browse/CALCITE-1387>.
- Jungtaek Lim (HeartSaVioR) 2016년 9월 28일 (수) 오전 1:05, Julian Hyde <[email protected]>님이 작성: > The SQL standard is not available online. Do some creative searching > (including on this list) and you might find it. > > Julian > > > > On Sep 27, 2016, at 12:30 AM, Jungtaek Lim <[email protected]> wrote: > > > > Thanks Julian for detailed explanation. > > I filed issues CALCITE-1386 > > <https://issues.apache.org/jira/browse/CALCITE-1386> and CALCITE-1387 > > <https://issues.apache.org/jira/browse/CALCITE-1387> for 1 and 2 > > respectively. > > > > Btw, could you link which you referred the page? I can't find it. > > > > Thanks, > > Jungtaek Lim (HeartSaVioR) > > > > 2016년 9월 27일 (화) 오후 12:50, Julian Hyde <[email protected]>님이 작성: > > > >> Regarding 1 and 2: these sound like bugs. Can you please log a JIRA case > >> for each? If you can supply a test case, even better. > >> > >> Regarding 3. I checked the SQL standard, and the required behavior is to > >> throw an exception. (Maybe ArrayIndexOutOfBoundsException isn’t the best > >> exception to throw.) > >> > >> Julian > >> > >> > >> > >> From SQL:2014... > >> > >> 6.24 <array element reference> Function > >> > >> Return an element of an array. > >> > >> Format > >> > >> <array element reference> ::= > >> <array value expression> > >> <left bracket or trigraph> <numeric value expression> <right > bracket > >> or trigraph> > >> Syntax Rules > >> > >> 1) The declared type of an <array element reference> is the element > type > >> of the speci ed <array value expression>. > >> > >> 2) The declared type of <numeric value expression> shall be exact > numeric > >> with scale 0 (zero). > >> > >> Access Rules > >> > >> None. > >> > >> General Rules > >> > >> 1) If the value of <array value expression> or <numeric value > expression> > >> is the null value, then the result of <array element reference> is the > null > >> value. > >> > >> 2) Let i be the value of <numeric value expression>. Case: > >> > >> a) If i is greater than zero and less than or equal to the cardinality > of > >> <array value expression>, then the result of <array element reference> > is > >> the value of the i-th element of the value of <array value expression>. > >> > >> b) Otherwise, an exception condition is raised: data exception — array > >> element error. > >> > >> > >> > >> > >>> On Sep 25, 2016, at 5:24 PM, Jungtaek Lim <[email protected]> wrote: > >>> > >>> Hi devs, > >>> > >>> I'm experimenting with ARRAY and MAP type with Calcite's > >> JaninoRexCompiler. > >>> (with Calcite 1.9.0) > >>> > >>> While testing I found some behaviors, so would like to see they're > >>> intentional > >>> or bugs we want to resolve. > >>> > >>> 1. Even though I set map's key/value type or array's type explicitly, > >>> compiler ignores the value type and create assignment to Object. > >>> > >>> This makes extracting value from nested collection not possible. Also > >>> comparing extracted value with numeric constant, too. > >>> (For example, suppose there's a row which MAPFIELD['a'] has 1 as value. > >>> Writing MAPFIELD['a'] = 1 on where statement throwing an Exception > saying > >>> there's no SqlFunctions.eq(Object, int). > >>> > >>> Btw, I took a breakpoint on BinaryImplementor.implement() to see > >> variables > >>> just > >>> before compiler finds SqlFunctions.eq(). > >>> The type of expression is Object which is not generic, but seems like > >>> matching > >>> RexCall operand have value type information. We might create a quick > fix > >>> from > >>> here (handling type conversion) but I'm not sure this can clearly > resolve > >>> this. > >>> > >>> 2. I just replace their type to ANY, and wrap expression with CAST like > >>> MAPFIELD['a'] -> CAST(MAPFIELD['a'] AS INTEGER) = 1 and succeed. > >>> (While using MAP type even I wrap to CAST, above error happens.) > >>> > >>> But it doesn't work when MAPFIELD['a'] is null because CAST AS INTEGER > >>> calls > >>> SqlFunctions.toInt(null) which throws RuntimeException. > >>> (It happens MAPFIELD['a'] doesn't have item which has 'a' as key. I > guess > >>> same > >>> applies to null value. And also same applies to ARRAY, and > >>> ARRAYFIELD[<non integer>].) > >>> > >>> CAST(COALESCE(MAPFIELD['a'], -1) AS INTEGER) also throws Exception > since > >>> MAPFIELD['a'] is Object and -1 is int. > >>> (CompileException : Incompatible expression types "java.lang.Object" > and > >>> "int") > >>> > >>> 3. ArrayOutOfIndexException is thrown when query is trying to access > the > >>> array > >>> with out of index. I don't know there's a standard rule on this, but at > >>> least > >>> PostgreSQL doesn't throw an Exception but just treats it as null. > >>> https://www.postgresql.org/docs/9.1/static/arrays.html > >>> (This means the result of ITEM operator is nullable.) > >>> > >>> Any ideas on these behaviors? > >>> > >>> Thanks, > >>> Jungtaek Lim (HeartSaVioR) > >> > >> > >
