A new hater amongst you



  • When you create a view that joins many tables, you'd expect queries run against that view to actually work if the view is created without so much as a gripe from Oracle.. WRONG.

    What is not obvious is that seemingly all the columns in any of the joined tables require an alias using "AS" to even be referenced in anything other than a "select * ", all you get is "Invalid Identifier"

    So, in eternal gratitude for wasting hours of my afternoon, I invite you, Oracle, to go fuck yourself.



  • Well it would be sooo difficult to just pull the column name from the source table, you know. Imagine all the extra lines of code they'd have to write.




  • Ummmm....what? I use views all the time in Oracle, but I can't figure out what you're talking about here. Are you saying that you can't use the name of the column as defined in the view?

    Right now I hate you, because you're denying me an opportunity to hate Oracle with your nonsense.



  • He's saying, as far as I can make out, that if you create a view without explicitly aliasing all the column names, the columns in the view without an explicit alias are only accessible via "select * from viewname"; other forms of query (or possibly DML) don't work.

    I don't use views that often, so I can't confirm or deny off-hand (and can't be bothered experimenting to find out). If true, it's a good thing to keep in mind when creating views: must explicitly alias all columns.



  • @Scarlet Manuka said:

    He's saying, as far as I can make out, that if you create a view without explicitly aliasing all the column names, the columns in the view without an explicit alias are only accessible via "select * from viewname"; other forms of query (or possibly DML) don't work.

    I don't use views that often, so I can't confirm or deny off-hand (and can't be bothered experimenting to find out). If true, it's a good thing to keep in mind when creating views: must explicitly alias all columns.

    That's exactly what I'm saying. The best thing was I had a calculated column coming from a subselect that is already aliased with AS in the subselect, that required another AS alias in the column definition part of the view before the FROM bit. SQL Server never gives me arseache like this.

     



  • @blakeyrat said:

    Well it would be sooo difficult to just pull the column name from the source table, you know. Imagine all the extra lines of code they'd have to write.
     

    Are you saying there's code in Oracle? I thought it only contained the contents of my bin.



  • @JimLahey said:

    @Scarlet Manuka said:
    He's saying, as far as I can make out, that if you create a view without explicitly aliasing all the column names, the columns in the view without an explicit alias are only accessible via "select * from viewname"; other forms of query (or possibly DML) don't work.

    I don't use views that often, so I can't confirm or deny off-hand (and can't be bothered experimenting to find out). If true, it's a good thing to keep in mind when creating views: must explicitly alias all columns.

    That's exactly what I'm saying. The best thing was I had a calculated column coming from a subselect that is already aliased with AS in the subselect, that required another AS alias in the column definition part of the view before the FROM bit. SQL Server never gives me arseache like this.

    If I use a calculated column in creating a view, without giving it an explicit name, I get "ORA-00998: must name this expression with a column alias," which seems reasonable.

    So I can only conclude that the top level query in your view is "select *". At least, that's the only way I've been able to sneak a bad column name into a view (and putting a column alias in the subquery solves the "problem" for me, as the alias gets used by the view). Seems like Oracle shouldn't let you do that, but I guess since you're the moron who was using "select *" to create your view, it's not unreasonable to think that you'd use "select *" when you actually use the view. Did you really do that?



  • @boomzilla said:

    @JimLahey said:
    @Scarlet Manuka said:
    He's saying, as far as I can make out, that if you create a view without explicitly aliasing all the column names, the columns in the view without an explicit alias are only accessible via "select * from viewname"; other forms of query (or possibly DML) don't work.

     

    I don't use views that often, so I can't confirm or deny off-hand (and can't be bothered experimenting to find out). If true, it's a good thing to keep in mind when creating views: must explicitly alias all columns.

    That's exactly what I'm saying. The best thing was I had a calculated column coming from a subselect that is already aliased with AS in the subselect, that required another AS alias in the column definition part of the view before the FROM bit. SQL Server never gives me arseache like this.

    If I use a calculated column in creating a view, without giving it an explicit name, I get "ORA-00998: must name this expression with a column alias," which seems reasonable.

    So I can only conclude that the top level query in your view is "select *". At least, that's the only way I've been able to sneak a bad column name into a view (and putting a column alias in the subquery solves the "problem" for me, as the alias gets used by the view). Seems like Oracle shouldn't let you do that, but I guess since you're the moron who was using "select *" to create your view, it's not unreasonable to think that you'd use "select *" when you actually use the view. Did you really do that?



    I gave the calculated column an alias in the subquery. I said that in the earlier post. I'm explicitly selecting various columns from various tables in the view, so your conclusion is completely incorrect. Since I'm using NHibernate the resultant SQL never performs a "select * ", instead it also explicitly selects every column from the view.. where you get your "select * " from I don't know. Probably the same place Oracle gets its code.

     



  • @JimLahey said:

    I gave the calculated column an alias in the subquery. I said that in the earlier post. I'm explicitly selecting various columns from various tables in the view, so your conclusion is completely incorrect. Since I'm using NHibernate the resultant SQL never performs a "select * ", instead it also explicitly selects every column from the view.. where you get your "select * " from I don't know. Probably the same place Oracle gets its code.

    I'm just trying to reproduce your claim. But now you've mentioned NHibernate, which greatly complicates the matter. I've honestly tried to recreate what you did based on the details given here, and using "select *" was the only way to get a "bad" column name into the view definition that I couldn't select. So now I suspect that NHibernate is fucking something up for you. Or you've left out something else that's important.

    It would be clear where I got "select *" from, if you took the drastic action of reading my post. It was the only way I could figure out how get a view column that could only be used from the view by doing a "select *" on the view itself. It's OK, though, you'd rather direct the rage at your own incompetence at Oracle (which certainly deserves its share of rage).

    It's possible, of course, that we're using different versions of Oracle, and that something has changed. I was trying this on 11.2.0.3, BTW.



  • First saw the error using NHibernate, reproduced said error using SQLPlus. I'm not questioning how you attempted to reproduce my claim, I'm wondering where you got the idea from that I was creating a view with "select * " in the first place. We're both on the same version, oddly enough. My rage derives from the fact that Oracle allows me to create a view with all the columns present, yet doesn't allow anything other than a "select * " unless I basically alias all the columns. It's not a behaviour I was expecting from such an expensive and widely used product.



  • @JimLahey said:

    First saw the error using NHibernate, reproduced said error using SQLPlus. I'm not questioning how you attempted to reproduce my claim, I'm wondering where you got the idea from that I was creating a view with "select * " in the first place. We're both on the same version, oddly enough. My rage derives from the fact that Oracle allows me to create a view with all the columns present, yet doesn't allow anything other than a "select * " unless I basically alias all the columns. It's not a behaviour I was expecting from such an expensive and widely used product.

    Yeah...there's still some key element missing, because I find that I don't need to alias the names (although for clarity, I tend to create views with an explicit list of names, so the aliasing is purely for query development). And I get an error trying to create the view unless I do the select * thing...so I can't figure out how you're managing this.

    Which is to say, it's not a behavior that I can make happen in this expensive product.



  • @boomzilla said:

    @JimLahey said:
    I gave the calculated column an alias in the subquery. I said that in the earlier post. I'm explicitly selecting various columns from various tables in the view, so your conclusion is completely incorrect. Since I'm using NHibernate the resultant SQL never performs a "select * ", instead it also explicitly selects every column from the view.. where you get your "select * " from I don't know. Probably the same place Oracle gets its code.

    I'm just trying to reproduce your claim. But now you've mentioned NHibernate, which greatly complicates the matter. I've honestly tried to recreate what you did based on the details given here, and using "select *" was the only way to get a "bad" column name into the view definition that I couldn't select. So now I suspect that NHibernate is fucking something up for you. Or you've left out something else that's important.

    It would be clear where I got "select *" from, if you took the drastic action of reading my post. It was the only way I could figure out how get a view column that could only be used from the view by doing a "select *" on the view itself. It's OK, though, you'd rather direct the rage at your own incompetence at Oracle (which certainly deserves its share of rage).

    It's possible, of course, that we're using different versions of Oracle, and that something has changed. I was trying this on 11.2.0.3, BTW.

    NHibernet will change the game. So boomzil is right. I have 10G and also 9i here. I am not expert, but I can test your bug.



  • @JimLahey said:

    My rage derives from the fact that Oracle allows me to create a view with all the columns present, yet doesn't allow anything other than a "select * " unless I basically alias all the columns. It's not a behaviour I was expecting from such an expensive and widely used product.

    I know exactly what you mean, I couldn't believe that such behavior existed in Oracle back when I started on version 7. 10g was the last version I used, and it still lacked such basic functionality. Crazy.



  • @Element said:

    @JimLahey said:

    My rage derives from the fact that Oracle allows me to create a view with all the columns present, yet doesn't allow anything other than a "select * " unless I basically alias all the columns. It's not a behaviour I was expecting from such an expensive and widely used product.

    I know exactly what you mean, I couldn't believe that such behavior existed in Oracle back when I started on version 7. 10g was the last version I used, and it still lacked such basic functionality. Crazy.

    Can someone post an example? I couldn't get this to happen on 11g.



  • The only time I've run into anything remotely similar is if multiple tables/views in the query have columns with identical names and I forget to specify which table/subquery/etc. the column should be pulled from, or if I have a column name which is also an oracle reserved keyword.

    Example Code or it did not happen.



  • First off, if you are using "select *", then you are doing it wrong.

    You should always specify only the columns you need, and not more.

    Second, here is an exemple of what happens :

    create table product_types (
    typ_id integer,
    typ_name varchar(10),
    PRIMARY KEY (typ_id)
    );

    <font face="verdana, sans-serif">insert into product_types values (1, 'type1');</font>
    insert into product_types values (2, 'type2');

    create table products (
    prod_id integer,
    prod_name varchar2(20),
    typ_id integer ,
    PRIMARY KEY (prod_id),

    FOREIGN KEY (typ_id) REFERENCES product_types(typ_id)

    );

    <font face="verdana, sans-serif">insert into products values (1, 'prod1',1);</font>
    <font face="verdana, sans-serif">insert into products values (2, 'prod2',2);</font>


    Using the select * :

    select *

    from  products prod

    inner join product_types typ on typ.typ_id = prod.typ_id ;

    The
    request will work as is (its just for show after all).

    <font face="verdana, sans-serif">But if you use it in a
    view, you will get :</font>
    <font face="verdana, sans-serif">ORA-00957: duplicate column name</font>

    Which is logical, because Oracle cannot differenciate between typ_id from the PRODUCTS table or PROTUCT_TYPES table.

    Instead, your view should look something like that :

    create view viewtest as
    select prod_id, prod_name, prod.typ_id, typ.typ_id typ_id2, typ_name

    from  products prod

    inner join product_types typ on typ.typ_id = prod.typ_id


    There is nothing wrong with this behaviour, its just a sanity check.

    Once again, you should not be using select *

     




  • Wow, what a necro!

    @guigui4242 said:

    Once again, you should not be using select *
     

    Going to disagree with you on that point. There are times when it's handy to use "SELECT *", so I'd not prevent its use.

    But I will agree with the notion "specifying vague requirements often leads to receiving unexpected results".



  • @Cassidy said:

    Wow, what a necro!

    @guigui4242 said:

    Once again, you should not be using select *
     

    Going to disagree with you on that point. There are times when it's handy to use "SELECT *", so I'd not prevent its use.

    But I will agree with the notion "specifying vague requirements often leads to receiving unexpected results".

    Of course , Select * is handy, when debugging, or drafting queries.
    But in an application, I dont see any cases where it's ok to use it.

    The main reason would be that, if a column is added to the table, it would break most applications if they use select *.



  •  How could it break an application? You are totally misguided.



  • @Nagesh said:

     How could it break an application?
     

    .. if the application dev made an assumption that the returned results were ordered in some fashion without specifying an order.

    Admittedly, if the app developer applied defensive coding techniques to make their routines more robust and safeguard against it, then app beakage couldn't happen.



  • Lets say your application calls

    select * from mytable

    And the application expects 3 columns to put in a new array.



    Then we add a new column to our table.



    At least 2 things could happen

    • The app breaks because the array was only made for 3 columns
    • The app continues working but the data is wrong since you don't exactly know which 3 columns of your table you are using.



      Which brings me to another reason for not using select * :

      the order of the columns could change !



      This is a general rule for any database : orders of columns don't matter (and they shouldn't)



      And the only way to avoid being dependent on column order, is to name each of them in your select.



      But then if you like living a risky life, feel free to use select * 😉


  • @Cassidy said:

    @Nagesh said:

     How could it break an application?
     

    .. if the application dev made an assumption that the returned results were ordered in some fashion without specifying an order.

    Admittedly, if the app developer applied defensive coding techniques to make their routines more robust and safeguard against it, then app beakage couldn't happen.




    You are right.

    But I dont believe its the app developper's job to make sure the query returns what it is supposed to do.


    He should just be querying against a known number of column in a specific order, specified in the requirements or any other kinds of documentation. But that would be in a perfect world I know.




  • The only time (except for quick and dirty ad hoc stuff) I use SELECT * is when I build up a big query using WITH queries, and I break up one of those into different subsets. In that case, it's easier to simply update the columns in the superset / parent query and let them trickle down instead of having to keep multiple queries in sync.



  • @guigui4242 said:

    But I dont believe its the app developper's job to make sure the query returns what it is supposed to do.
     

    No - and I didn't state it was. But you'll get developers that make assumptions and place expectations upon untrusted input to the point that variation could cause great instability.

    Not the fault of the app, more of they that built it. First against the wall, Comrades.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.