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) >> >>
