Repository navigation
Prefix * with table alias #1003
Description
As described in #908 for Oracle and #966 for Teradata, specifying new columns after SELECT * is not possible with Exasol-Database neither. Most likely there are other RDBMS that do not support this.
I like the use of star instead of repeating every columnname, it's very convenient and makes it easier to inspect the generated SQL-query. IMHO adding the alias as a prefix to the star would be a more generic solution to these problems:
Following dplyr-pipe
tbl(db, "my_table") %>%
mutate(
new_col = "any_value"
) %>% show_query()
sould translate to:
<SQL>
SELECT "t1".*, 'any_value' AS new_col
FROM "my_table" as "t1"
Of course, every derived table e.g. select "sub1".* from (select * from "my_table") "sub1" has to be declared with an alias too.
Would it be possible to prefix every star with an table alias?
Thanks!
Activity
hadley commentedon Dec 5, 2022
@namarkus are you saying that you can do
SELECT table.*, x, y, zin Oracle and Teradata?[-]supports_star_without_alias issue for [/-][+]Prefix `*` with table name[/+][-]Prefix `*` with table name[/-][+]Prefix `*` with table alias[/+]namarkus commentedon Dec 5, 2022
No, that's not what I meant. The wording was a little imprecise, I'm sorry.
The prefix for the *-notation should be the table alias not the table name. I have adjusted the name of the issue and also the example.
hadley commentedon Dec 5, 2022
I mean are you implying that adding the prefix fixes the problem with using a bare
*?namarkus commentedon Dec 5, 2022
Yes, definitely. I looked this up in the documentation for 3 different RDBMS:
Oracle
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6
In the section "select_list::=" the following valid possibilities are listed:
select schema.table.* , ...
select t_alias.* , ...
Teradata
https://docs.teradata.com/r/Teradata-VantageTM-SQL-Data-Manipulation-Language/March-2019/Select-Statements/SELECT
According to section "B
SELECT table_name.* , ...
is a valid expression. Unfortunately, it is not quite clear whether table_name means the actual name of the table or an alias.
Exasol
https://docs.exasol.com/db/latest/sql/select.htm
Section "select_list::=" names the following valid expressions:
SELECT table.* , ...
SELECT view.* , ....
(however in my Exasol system only SELECT table_alias.* works...)
In my experience (and contrary to the documentation)
SELECT table_alias.* ,...
actually works in most systems.
What is clear in all 3 syntax definitions, however, is that
SELECT *, ...
is not a valid statement.
hadley commentedon Dec 5, 2022
@mgirlich do you think we could make this an option or maybe even the default?
hadley commentedon Dec 5, 2022
I think this implies it's valid SQL 92?
namarkus commentedon Dec 6, 2022
I agree, it could be interpreted that way. But the diagram for it states otherwise:
A < select list > is either a single asterisk only or a comma-separated sequence of < select sublist >. A < select sublist > in turn can be < qualifier > < period > < asterisk >.
Consequently, if multiple < select sublist > occur in a < query specification >, the asterisk must have < qualifier >-prefix.
hadley commentedon Dec 6, 2022
I think that does imply that
< qualifier > < period > < asterisk >is ok?namarkus commentedon Dec 6, 2022
Yes, I agree with that.
But2ene commentedon Jan 30, 2023
In Teradata, yes it does.
Yes it works in Teradata, both table name and alias work. Qualifier/alias would be the best default for Teradata