Re: ExecuteSQL Avro schema: all fields are nullable

[ms...@gmail.com, ms...@gmail.com]

Hi,

I've just bumped into the same issue, so it affects DB2, too. I have a large number of tables to work with, so having the "Honor Non-Nullable Fields" property would be far better than having to define the Avro schemas by hand. 

I couldn't find any existing Jira ticket for it, though, so I went ahead and opened https://issues.apache.org/jira/browse/NIFI-5971


On 2018/09/13 14:11:20, Matt Burgess <ma...@apache.org> wrote: 
> Christophe,
> 
> IIRC the isNullable() method gives inconsistent results across JDBC
> drivers (see [1], [2] for examples) and in such cases might cause more
> harm than good. Having said that, we could perhaps add a property to
> the relevant components such as "Honor Non-Nullable Fields" or
> something like that, where the user could choose "true" if they were
> confident that their JDBC driver supported isNullable() correctly and
> that their SQL queries would return result sets for whose metadata
> (such as isNullable) is correct. Specifically I mean the case from [2]
> where you do a join from a table with a nullable column with another
> having the same column but non-nullable field. For an outer join the
> result column should be nullable, but in [2] they point out that
> PostgreSQL (at least in 2012 at the time of the post), if you select
> the field from the non-nullable table, then the
> ResultSetMetaData.isNullable() will return false although it can
> clearly be null.
> 
> As a workaround, you can use ConvertRecord with an output schema that
> removes the nullable union from the fields. There should be no nulls
> in the data (as the column was supposedly non-nullable) so there
> should be no error there. If for some reason you want to check that
> there are no nulls downstream, you can use ValidateRecord with the
> schema that has the nullable unions removed, then any records with a
> null value in that column will be transferred to invalid, and only the
> non-nulls will be transferred to success.
> 
> If you'd like to have the configurable property to generate
> non-nullable fields in the schema, please feel free to file an
> improvement Jira [3] and we can continue the discussion there :)
> 
> Regards,
> Matt
> 
> [1] https://github.com/confluentinc/kafka-connect-jdbc/issues/197
> [2] https://www.postgresql.org/message-id/63DBC81F-2AB0-4C02-AC08-2B05C31FFCA6%40gmail.com
> [3] https://issues.apache.org/jira/browse/NIFI
> On Thu, Sep 13, 2018 at 7:43 AM <ch...@post.ch> wrote:
> >
> > Hello
> >
> > Is there a reason why JdbcCommon#createSchema creates an Avro schema with nullable types for all fields?
> > Why not check with java.sql.ResultSetMetaData#isNullable ?
> >
> > https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.7.1/org.apache.nifi.processors.standard.ExecuteSQL/index.html
> >
> > https://github.com/apache/nifi/blob/rel/nifi-1.7.1/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/util/JdbcCommon.java#L500
> >
> > Regards,
> > Chris
>