Hmm. Right. Somehow I forgot about the very large result sets - stupid of me.
On Tue, Aug 21, 2018 at 4:43 PM Paul Rogers <par0...@yahoo.com.invalid> wrote: > Hi Chris, > <snip> > Later, when Drill sees the first Varchar, it can change the type from, > say, batch 3 onwards. But, JDBC and ODBC generally require the schema be > known up front, so they would have to predict the future to know that a > Varchar will eventually appear. + > Within Drill, suppose that the query includes a sort, and that memory > limits require spilling. The first two batches with just null will be > spilled to disk in one format. Third and later batches have a different > schema. So, the code that handles spilling must merge the two types. Not a > big problem, but the fix must be applied in multiple places in different > ways. Very difficult to test all the resulting combinations and > permutations. > IF drill is doing a sort, it is likely that Drill would know the types before any rows were returned to JDBC/ODBC, so in this case delaying telling the client what type of columns they are getting could work, right? Of course, many queries will avoid sorts, so this isn't really an answer. Maybe a partial one. Does this make sense? Are we overlooking an alternative solution? > It does make sense. And I can't see a reasonable alternative. The closest I come is telling the client that it is a BLOB of unknown size (or make it roughly 'big'). Then either NULL or a pointer to the data is returned - but this just pushes the actual determination of the type to the user - with much less help context than Drill would have for finding out what it really is. I.e., not really good. IF JDBC/ODBC was enhanced with a new feature that allowed the server to say in essence 'Hey, I know I told you what the columns where, but really, this column has changed to this type' in the middle of the results, that would be nice. ODBC does allow the user to unbind columns and rebind them - so it would be conceivably possible that Drill could raise a warning, the client could not the warning said something like 'Column 17 changed to varchar(2000)', and then the client unbinds column 17, and rebinds it to a new buffer that would hold the actual content. ( https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlbindcol-function?view=sql-server-2017 ) Of course, this would require users to custom code ODBC access to drill to take advantage of this - which I suspect would be pretty uncommon. I also see no reference to this ability in JDBC. Thanks, > - Paul > Thank you for the quick, detailed response! -Chris
