SQLite Forum

Column names and aliases in expressions

Column names and aliases in expressions

(1) By anacrolix on 2021-10-17 22:29:09 [link] [source]

The documentation on SELECT, and SQL Language Expressions refer to column-alias, column-name, table-name and schema-name in the syntax. I can't find documentation however on how names are resolved, for example if table-name is not provided, what table will be used for a given column name in an expression.

As an example, if I have I have a subquery that refers to a column that exists in several tables that are in the parent query including itself, which instance will be used? How do I refer to the specific one I want?

(2.1) Originally by Keith Medcalf (kmedcalf) with edits by Stephan Beal (stephan) on 2021-10-18 02:07:47 from 2.0 in reply to 1 [link] [source]

If you reference a "name" in a context in which that use is ambiguous, you will get a message telling you that the "name" is ambiguous.

As an example, if I have I have a subquery that refers to a column that exists in several tables that are in the parent query including itself, which instance will be used?

None. The computer will inform you that the column reference is ambiguous, which is an error condition that will prevent the query from being able to be prepared/compiled.

How do I refer to the specific one I want?

You use a fully qualified name that is not ambiguous. A referent to a table column has the format [[<schema>.]<table>.]<colname> or [<table-alias>.]<colname>. You must specifically identify the column completely and unambiguously. Once you have done so, the query may be prepared/compiled without raising an ambiguity error.

Computers do not guess (unless they have been programmed to do so). SQLite3 is not an IBM PL/1 Level H compiler. It does not try to "guess what you meant", but will rather "do what you say". If you say something ambiguous then you will be informed of your error. If you say something which is syntactically valid (but semantically a bluster-duck) then you will get a bluster-ducking.

Mutatis mutandis for any other "name" which may be ambiguous when specified without sufficient qualifiers.

(4) By Scott Robison (casaderobison) on 2021-10-18 01:01:30 in reply to 2.0 [link] [source]

I know not all people object to "colorful metaphors" and language that is considered by some to be out of place in polite society, but you are a brilliant person, Keith. I personally would applaud you applying your writing skill to choosing more universally acceptable words when interacting in the community. I know I've seen such posts edited in the past, so I know I am not alone in this.

(5) By Keith Medcalf (kmedcalf) on 2021-10-18 01:32:27 in reply to 4 [link] [source]

It is still a "secure the building" problem.

(6) By Stephan Beal (stephan) on 2021-10-18 07:25:56 in reply to 5 [link] [source]

(/me begrudgingly dons his admin hat, at the risk of thereby becoming That Guy. TL;DR: jump to the last couple of paragraphs.)

It is still a "secure the building" problem.

That's a term i'm unfamiliar with, but according to this article about the "secure the build" problem:

Although this adage is, of course, a joke, it also serves as a cautionary tale about the importance of a strong and clear problem statement within successful acquisitions. To "secure the building" barely describes "what" is to be done and leaves out the other two critical elements, "why" and "how."

Applying "secure the building problem" to Scott's request, i can address...

  1. "why": the operators of this forum wish for it to uphold a reputation of being professional, respectful, and "family-friendly" (when in doubt, following the US definition of the term, noting that it is more lax, sometimes considerably so, in many other regions).

  2. "how": if the non-family-friendly language continues, your posts will be forced to go through moderation before approval, with instructions for the moderators to reject, rather than silently continue to edit, the forum-inappropriate language. (Noting that the forum does not provide an option for editing before approving, but that's a feature we can potentially address in Fossil.)

The article continues with:

A problem statement, as defined by Dr. Edward F. Crawley, Ford professor of engineering in the Massachusetts Institute of Technology's Engineering Systems Division, must include:

1. To… (enterprise or stakeholders' intent, or the "why" you are attacking the problem; what value are you trying to create?).

Addressed above.

2. By… (the "how," using solution-neutral verbs such as create, destroy, transport, transform, compare, etc… ).

Addressed above.

3. Using… (the "what," or statement of structure; this introduces cost).

Using the administrative-level tools of this forum and the authority granted to me to apply them in pursuit of point (1).

4. While… (detailing other important goals or constraints).

While respecting that you are an undeniably top-notch expert in this field with a stupendously tremendous[^1] amount of information to impart and are, for that reason, admitted some leeway in your "bedside manner" which would not be afforded to the proverbial mere mortals. That leeway, however, does not (or does not any longer) apply to non-family-friendly language.

FWIW, i think it's fair to say that everyone[^2] here recognizes the tremendous value of your continued participation and would like it to continue unfettered.

(/me removes his admin hat)

[^1] = "stupendously tremendous" is, in this case, an accurate characterization, not hyperbole.

[^2] = Okay, there's always someone in every group who's going to be contrary about any position ostensibly applying to "everyone," but we'll ignore them for this purpose.

(3.1) By Keith Medcalf (kmedcalf) on 2021-10-18 00:14:00 edited from 3.0 in reply to 1 [link] [source]

Note that <tablename> are not ambiguous due to the fact that there is a search-order for finding tables. For a bare (unqualified) table name, the search order is as follows:

  • firstly, look in the temp schema
  • secondly, look in the main schema
  • thirdly, look in all other accessible schema in the order of attachment

and when the table is found, the (first) one found is used.

THe CLI .databases command will show you what databases (schemas) are attached to the current connection. seq=0 is always the main schema (the database opened when the connection was created), seq=1 is always the temp schema and is reserved for use by the temp schema at all times, even if it has not yet been created. seq=2 and up are "other accessible schema/databases in the order of attachment".

(7) By Kevin Youren (KevinYouren) on 2021-10-18 08:06:50 in reply to 1 [link] [source]

Could you possibly give the SQL and table definitions for your example, please?

(8) By L Carl (lcarlp) on 2021-10-21 17:11:28 in reply to 1 [source]

I'm sure this is documented better somewhere, but bare column names are resolved in a manner similar to other scoped programming languages. If for example, you refer to a bare column name in a subquery, it will first try to resolve it using a table in that subquery and if none of those tables have such a column, it will next look at the nearest "enclosing" query... and so on up to the outermost query.

Note that if you add a new column to an existing table, you can, therefore, drastically change the meaning of an existing query. Hence, I would recommend using bare column names only in very simple queries. If the queries start to get more complex, it is better to qualify the column names. Furthermore, since the same table is likely to be referenced more than once in a complex query, it is often necessary to use table aliases when qualifying column names.

As other posters have said, you'll get an error if a bare column name is ambiguous, e.g., if the same column name occurs in more than one table in the same query. However, the real danger is when it is clear to the SQL Engine which column is being referenced, but not clear to you, the developer! I've been burned by this a few times.