boolean datatype in tap_schema

[pdowler]

For historical reasons, the tap_schema.columns table has 3 columns that are intended to be boolean but are currently int: indexed, principal, std (values 0 or 1).

reason: some databases did not have an internal boolean type and we originally wanted to make it possible to implement with lowest-common denominator RDBMS and no extra magic in the service.

today: extra magic in the service layer is all but unavoidable so if some database does not have boolean then that can be hidden from users

complication: TAP-1.0 and 1.1 declared these columns as datatype="int" and while TAP clients could be tweaked to deal with int and boolean, old/unmaintained clients would probably break... we would need to evaluate real world data to find out how much usage would actually be broken by such a change.

[mbtaylor]

Agreed this is ugly, but I'm not persuaded that it's bad enough to justify the backward compatibility issues that a change would cause. The columns in question are not ones that users will be using or looking at very often.

[pdowler]

I agree that it's hard to justify a change on the pain-vs-value scale

The only thing is that DALI and TAP mostly use the VOTable type system wherever possible, but then this sets a bad example of how to do boolean properly. An example I followed when having a boolean column in other tables intended for TAP querying...

This issue is just a note/reminder to think about it and maybe just document this bit of tap_schema a little more carefully.

[msdemlei]

On Tue, Apr 29, 2025 at 01:52:42PM -0700, Patrick Dowler wrote: The only thing is that DALI and TAP mostly use the VOTable type system wherever possible, but then this sets a bad example of how to do boolean properly. An example I followed when having a boolean column in other tables intended for TAP querying...

For the record, at least as long as people would have to say principal='true' if these were boolean (and that's what currently the case, see below), I am pretty sure that using 0 and 1 is a *good* example, and having two competing ways of dealing with boolean columns and functions would be more pain than what we have now. principal=1 at least is not worse, and if we do that consistently and recommend that that's what people should use if they can, that's better <cough> UX than if "legacy" ADQL and many UDFs use 0/1 and half the databases use 'true'/'false'. TRUE and FALSE, by the way, are reserved names in the ADQL grammar but don't turn up anywhere else. At least DaCHS works with the string literals. I've not tried other implementations. Frankly, I think true boolean support would be a subject of ADQL3/TAP2.

[gmantele]

I agree that dealing properly with boolean values is not an easy task. Changing this in TAP may break clients (although as @mbtaylor said, this is not used very often). ADQL, TAP and eventually DALI should make this move together.

Anyway, I agree with you @pdowler that this issue should be reported somewhere so that we don't forget about it.

[Zarquan]

Historical context
ivoa-std/ADQL#32

[pdowler]

I will create a label for this kind of issue.

This was inspired by trying to resolve a DALI issue in ivoa-std/DALI#51 so maybe someone should review that and I can rework the bit about boolean.