HI Michael, The error message was fixed as part of DRILL-3583 <https://issues.apache.org/jira/browse/DRILL-3583>. The problem is that the query uses the SUM function on a column with string or boolean type.
Thank you, Sudheesh > On Sep 22, 2015, at 3:54 AM, <[email protected]> > <[email protected]> wrote: > > I actually solved this with advice from Leon Clayton at MapR by creating a > new workspace for views alone and keeping it separate from the data. This > obviously makes sense but it would be nice if Drill could be aware of any > created views (to be able to ignore them) in case someone accidently creates > the view in the same area as their data causing erroneous results. > > > > -----Original Message----- > From: England, Michael (IT/UK) > Sent: 22 September 2015 10:14 > To: [email protected] > Subject: RE: Drill view schema returned in results set > > I have noticed that Drill keeps the view definition in the same directory as > the data. Should this not be ignored by default instead of having to specify > what files to query? > > > > -----Original Message----- > From: England, Michael (IT/UK) > Sent: 22 September 2015 09:53 > To: [email protected] > Subject: Drill view schema returned in results set > > Hi, > > I was trying to run a SUM() function on a view but was getting the following > error: > > ERROR [HY000] [MapR][Drill] (1040) Drill failed to execute the query: select > sum(x) from `mike`.`view`.`test_view ` [30027]Query execution error. > Details:[ SYSTEM ERROR: CompileException: Line 33, Column 177: Unknown > variable or type "logger" > > Fragment 0:0 > > [Error Id: 68c7c5b8-8875-4834-8977-c514a08ca8b0 on x.mydomain.com:31010] ] > > I ran a distinct on the column to check all of the data types in the column > were an integer but saw that one row contained no data. I orginally thought > that it was my data causing the issue, but on further analysis I saw the view > schema being returned in the first column which seems to be causing the issue: > > select * from `mike`.`view`.`myview` where isnumeric(x) = 0 > > { > "name" : "test_view ", > "sql" : "SELECT `columns`[0] AS `x`, `columns`[1] AS `x1`, `columns`[2] AS > `x2`, `columns`[3] AS `x3`, `columns`[4] AS `x4`, `columns`[5] AS `x5`, > `columns`[6] AS `severity`, `columns`[7] AS `x6`\nFROM `mike`.`view`.`.`", > "fields" : [ { > "name" : "x", > "type" : "ANY", > "isNullable" : true > }, { > "name" : "x1", > "type" : "ANY", > "isNullable" : true > }, { > "name" : "x2", > "type" : "ANY", > "isNullable" : true > }, { > "name" : "x3", > "type" : "ANY", > "isNullable" : true > }, { > "name" : "x4", > "type" : "ANY", > "isNullable" : true > }, { > "name" : "x5", > "type" : "ANY", > "isNullable" : true > }, { > "name" : "x6", > "type" : "ANY", > "isNullable" : true > }, { > "name" : "x7", > "type" : "ANY", > "isNullable" : true > } ], > "workspaceSchemaPath" : [ ] > } > > Is this expected behaviour or does it look like a bug. Has anyone come across > this before? > > Thanks, > Michael > > > This e-mail (including any attachments) is private and confidential, may > contain proprietary or privileged information and is intended for the named > recipient(s) only. Unintended recipients are strictly prohibited from taking > action on the basis of information in this e-mail and must contact the sender > immediately, delete this e-mail (and all attachments) and destroy any hard > copies. Nomura will not accept responsibility or liability for the accuracy > or completeness of, or the presence of any virus or disabling code in, this > e-mail. If verification is sought please request a hard copy. Any reference > to the terms of executed transactions should be treated as preliminary only > and subject to formal written confirmation by Nomura. Nomura reserves the > right to retain, monitor and intercept e-mail communications through its > networks (subject to and in accordance with applicable laws). No > confidentiality or privilege is waived or lost by Nomura by any > mistransmission of this e-mail. Any reference to "Nomura" is a reference to > any entity in the Nomura Holdings, Inc. group. Please read our Electronic > Communications Legal Notice which forms part of this e-mail: > http://www.Nomura.com/email_disclaimer.htm > > > > This e-mail (including any attachments) is private and confidential, may > contain proprietary or privileged information and is intended for the named > recipient(s) only. Unintended recipients are strictly prohibited from taking > action on the basis of information in this e-mail and must contact the sender > immediately, delete this e-mail (and all attachments) and destroy any hard > copies. Nomura will not accept responsibility or liability for the accuracy > or completeness of, or the presence of any virus or disabling code in, this > e-mail. If verification is sought please request a hard copy. Any reference > to the terms of executed transactions should be treated as preliminary only > and subject to formal written confirmation by Nomura. Nomura reserves the > right to retain, monitor and intercept e-mail communications through its > networks (subject to and in accordance with applicable laws). No > confidentiality or privilege is waived or lost by Nomura by any > mistransmission of this e-mail. Any reference to "Nomura" is a reference to > any entity in the Nomura Holdings, Inc. group. Please read our Electronic > Communications Legal Notice which forms part of this e-mail: > http://www.Nomura.com/email_disclaimer.htm > > > > This e-mail (including any attachments) is private and confidential, may > contain proprietary or privileged information and is intended for the named > recipient(s) only. Unintended recipients are strictly prohibited from taking > action on the basis of information in this e-mail and must contact the sender > immediately, delete this e-mail (and all attachments) and destroy any hard > copies. Nomura will not accept responsibility or liability for the accuracy > or completeness of, or the presence of any virus or disabling code in, this > e-mail. If verification is sought please request a hard copy. Any reference > to the terms of executed transactions should be treated as preliminary only > and subject to formal written confirmation by Nomura. Nomura reserves the > right to retain, monitor and intercept e-mail communications through its > networks (subject to and in accordance with applicable laws). No > confidentiality or privilege is waived or lost by Nomura by any > mistransmission of this e-mail. Any reference to "Nomura" is a reference to > any entity in the Nomura Holdings, Inc. group. Please read our Electronic > Communications Legal Notice which forms part of this e-mail: > http://www.Nomura.com/email_disclaimer.htm >
