Forum Stats

  • 3,826,883 Users
  • 2,260,721 Discussions
  • 7,897,110 Comments

Discussions

Allow all comparisons for row values

Thorsten Kettner
Thorsten Kettner Member Posts: 42 Red Ribbon
edited Aug 23, 2021 4:31PM in Database Ideas - Ideas

This is allowed:

SELECT * FROM emp WHERE (dept, sal) = ((1, 100));

And in another idea I am proposing to allow row values on the right-hand side instead of a data set:

SELECT * FROM emp WHERE (dept, sal) = (1, 100);

What is not supported is this:

SELECT * FROM emp WHERE (dept, sal) < ((1, 100));

resp.

SELECT * FROM emp WHERE (dept, sal) < (1, 100);

The same applies to <= , >, and >= of course.

The SQL standard allows this and it is very useful when using pagination. So far we must use this to get the next 50 rows:

SELECT * 
FROM programs
WHERE version > :version
  OR (version = :version AND subversion > :subversion)
  OR (version = :version AND subversion = :subversion AND subsubversion > :subsubversion)
ORDER BY version, subversion, subsubversion
FETCH NEXT 50 ROWS ONLY; 

While in standard SQL we would just use:

SELECT * 
FROM programs
WHERE (version, subversion, subsubversion) > (:version, :subversion, :subsubversion)
ORDER BY version, subversion, subsubversion
FETCH NEXT 50 ROWS ONLY;

(And in conclusion it should be possible then to use the comparision operators on data sets, too: WHERE (version, subversion) >= all (select version, subversion from ...).)

Thorsten KettnerStew Ashtonfac586
3 votes

Active · Last Updated

Comments

  • user-jens.bjerg_9122150
    user-jens.bjerg_9122150 Member Posts: 2 Blue Ribbon

    Alternative to a where clause with one or more OR you could convert the list of column values to a string, and compare that to another string - JSON_OBJECT(CHANNEL_ID,CHANNEL_DESC,CHANNEL_CLASS)


  • Stew Ashton
    Stew Ashton Member Posts: 2,900 Bronze Crown

    @user-jens.bjerg_9122150

    Using JSON_OBJECT won't work because the string comparison does not provide the same result: CHANNEL_ID 11 would be less than CHANNEL_ID 9.

  • user-jens.bjerg_9122150
    user-jens.bjerg_9122150 Member Posts: 2 Blue Ribbon

    Hi Stew, as I pointed out in the attached file in my original suggestion, number and other types might need the use of a TO_CHAR(), I think this should work, let me know:

    If column CHANNEL_ID id is number you should use to_char()

    To ensure that the number 30 is > the number 4

      000030 > 000004

    select aaa.* 

    from channels aaa

    where json_object('CHANNEL_ID' value to_char(CHANNEL_ID,'000000'),CHANNEL_DESC,CHANNEL_CLASS)

     > json_object('CHANNEL_ID' value to_char('4','000000')

           ,'CHANNEL_DESC' value 'Catalog' 

           ,'CHANNEL_CLASS' value 'Direct') ;

  • Stew Ashton
    Stew Ashton Member Posts: 2,900 Bronze Crown

    @user-jens.bjerg_9122150 ,

    Suppose CHANNEL_DESC contains 'abcde' compared to 'abcde!' ? Now '!' is going to be compared to '"' and it will come first, which is incorrect.

    Are you going to RPAD(CHANNEL_DESC, ' ', 4000) ?

    What if JSON_OBJECT tries to return more than 4000 bytes? It will fail, and comparing CLOBs is not supported.

    Is this really less complicated than the standard (though tedious) logic?

  • Thorsten Kettner
    Thorsten Kettner Member Posts: 42 Red Ribbon

    Hi @user-jens.bjerg_9122150 and @Stew Ashton,

    while it's always nice to think of alternative approaches, you do know that this is a feature request? I know this is not immediately visible, when Oracle calls this lackadaisically Database Ideas and has come to hide it in a discussion forum with many different topics (not to mention that this is hard to search, e.g. in order to find out if the same feature request has already been posted). But it is, and comments should mainly be used to either support the request or give reasons against it.

    As to your last post: "Is this really less complicated than the standard (though tedious) logic?" The feature I am requesting here is the standard approach. Only Oracle doesn't support this yet. And I think it's just the opposite of "tedious", which is why I made this request.

  • Stew Ashton
    Stew Ashton Member Posts: 2,900 Bronze Crown

    @Thorsten Kettner ,

    You misread my post.

    You quoted me as saying "Is this really less complicated than the standard (though tedious) logic?"

    Did you notice that I was addressing @user-jens.bjerg_9122150 ?

    I was objecting to his alternative approach. I agree that the word "standard" is misleading: I did not mean the SQL standard but the ordinary logic you are tired of using.

    I was supporting your request by dismissing a clumsy and error-prone alternative.

    By the way, are you also a moderator of this forum? Am I supposed to follow your rules?

  • Thorsten Kettner
    Thorsten Kettner Member Posts: 42 Red Ribbon

    @Stew Ashton: No, I am not a moderator, just a sufferer :-)

    Oracle's feature request portal Database Ideas has never been known to many and has hence not been as useful as a feature request site should be. Then they moved their site to this discussion board making it even harder to spot and use. I actually wonder whether Oracle is interested in our requests or not :-(

    It would be great did more people work on these ideas and comment like "A great idea; I've been working on moving a PostgreSQL database to Oracle and had a hell of a time trying to convert Postgre's standard compliant queries to the error-prone queries Oracle can work with" or "While this is standard SQL, I don't consider this readable and would like Oracle to continue dismissing this syntax", etc.

    So, no, you dont have to follow my rules :-) it would just be great, if this would really be seen as Oracle's feature request site, with comments clearly supporting or unsupporting the proposal.

  • Niels Hecker
    Niels Hecker Member Posts: 28 Bronze Badge

    There is also a OFFSET in the row limiting clause, so you can do the

    SELECT * 
    FROM programs
    ORDER BY version, subversion, subsubversion
    FETCH NEXT 50 ROWS ONLY;
    

    save the sql%rowcount to a variable and then in the next call do a

    SELECT * 
    FROM programs
    ORDER BY version, subversion, subsubversion
    OFFSET var_rowcount_from_previous_select
    FETCH NEXT 50 ROWS ONLY;
    

    or more general with two variables page_no and rows_per_page

    SELECT * 
    FROM programs
    ORDER BY version, subversion, subsubversion
    OFFSET ((page_no - 1) * rows_per_page)
    FETCH NEXT rows_per_page ROWS ONLY;
    


  • Thorsten Kettner
    Thorsten Kettner Member Posts: 42 Red Ribbon

    @Niels Hecker: Yes, I know the offset clause. The problem with this - apart from inserts and deletes that may move the window, so you miss rows or get rows twice - is that the same data must be read again and again. With

    SELECT * FROM mytable ORDER BY id OFFSET :off FETCH NEXT 10 ROWS ONLY;
    

    an offset of 9000 and a table of 10000 rows, the DBMS must read 10000 rows, sort them and then skip 9000 rows in order to return the next ten. With

    SELECT * FROM mytable WHERE id :last_id ORDER BY id FETCH NEXT 10 ROWS ONLY;
    

    and an index on ID only the next ten rows must be read. I have yet to see a situation where using OFFSET is a great idea.

    And after all, my original example with pagination on version, subversion, and subsubversion is just that; an example. I am not looking for an alternative pagination approach; I want to see support for "less than" and "greater than" on tuples. 🙂

  • Niels Hecker
    Niels Hecker Member Posts: 28 Bronze Badge
    edited Jan 21, 2022 10:11PM

    An alternate - but complicated way - is to create an object type with an ORDER (or MAP) method to support sorting and then use that type as a replacement for the JSON_OBJECT() mentioned earlier.

    The disadvantage is that you must declare such a type for each different combination of datatypes in the tuple you want to use, e.g.:

    create or replace type t_compare_vnv as object (
      v1 varchar2(100), n1 number, v2 varchar2(100),
      order member function order_by (p_other in t_compare_vnv) return integer
    ) not final instantiable;
    /
    create or replace type body t_compare_vnv as
      order member function order_by (p_other in t_compare_vnv) return integer is
      begin
        return (case
                  when (self.v1 < p_other.v1) then -1
                  when (self.v1 > p_other.v1) then +1
                  when (self.n1 < p_other.n1) then -1
                  when (self.n1 > p_other.n1) then +1
                  when (self.v2 < p_other.v2) then -1
                  when (self.v2 > p_other.v2) then +1
                  else 0
                end);
      end order_by;
    end;
    /