Temporary tables are created with invalid names

[lucyb]

When constructing a study definition, creating variables with dates (e.g '2020-01-01') or snomed codes as names results in temporary table names that are invalid in t-sql.

t-sql specifies that names cannot start with a number or contain dashes, unless surrounded by square brackets.

So, this is invalid

SELECT * INTO #14336007 FROM (

But this should work (from memory/google, haven't tested)

SELECT * INTO [#14336007] FROM (

See this change and this one for context.

[evansd]

Ah yes, we've been sort of implicitly relying on Python's syntax to enforce column name rules here as column names are usually supplied as keyword arguments which will automatically prevent numeric only or dash separated names. So I think we should add some explicit validation here to enforce the same rules for dynamically constructed study defs as well.

You're quite right that there's also a quoting issue here which should be fixed (and is fixed naturally in databuilder through using SQLAlchemy). But I don't think we should be supporting using arbitrary strings as column names as I can imagine those causing unexpected problems in other contexts and — unless I'm missing something — I don't think there's really a need for them.