Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27617

HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results

Details

    Description

      create table t (a int, b char(1), primary key(a desc));
      		 
      insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');
      		 
       
      		 
      handler t open;
      		 
      handler t read `PRIMARY` > (0);
      		 
       
      		 
      # Cleanup
      		 
      handler t close;
      		 
      drop table t;

      The read returns an empty result. With an ascending key it returns a row.

      It causes for example wrong results in Spider when it runs in handler mode.

      Example with Spider:

      install soname 'ha_spider';
      		 
       
      		 
      set spider_use_handler=3;
      		 
      set spider_same_server_link= 1;
      		 
       
      		 
      create table t (a int, b char(1), primary key(a desc));
      		 
      insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');
      		 
       
      		 
      eval create server s foreign data wrapper mysql options 
      (host '127.0.0.1', database 'test', user 'root', port $MASTER_MYPORT);
      		 
       
      		 
      create table ts (a int, b char(1), primary key(a desc))
      		 
        engine=Spider COMMENT='wrapper "mysql", srv "s", table "t"';
      		 
       
      		 
      select a, b from ts where a > 0 and b = 'g' order by a;

      SELECT returns an empty result.
      If at least one of PK is not descending, the result is okay (`PRIMARY` > (0) is not used then in the resulting handler statement).

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Summary HANDLER KEY > n does not work with DESC keys, Spider is affected HANDLER KEY > (x) does not work with DESC keys, Spider is affected
            elenst Elena Stepanova made changes -
            Description {code}
            create table t (a int, b char(1), primary key(a desc));
            insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');

            handler t open;
            handler t read `PRIMARY` > (0);

            # Cleanup
            handler t close;
            drop table t;
            {code}

            The read returns an empty result. With an ascending key it returns a row.

            It causes for example wrong results in Spider when it runs in handler mode.             		{code}
            create table t (a int, b char(1), primary key(a desc));
            insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');

            handler t open;
            handler t read `PRIMARY` > (0);

            # Cleanup
            handler t close;
            drop table t;
            {code}

            The read returns an empty result. With an ascending key it returns a row.

            It causes for example wrong results in Spider when it runs in handler mode.

            Example with Spider:
            {code:sql}
            install soname 'ha_spider';

            set spider_use_handler=3;
            set spider_same_server_link= 1;

            create table t (a int, b char(1), primary key(a desc));
            insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');

            eval create server s foreign data wrapper mysql options
            (host '127.0.0.1', database 'test', user 'root', port $MASTER_MYPORT);

            create table ts (a int, b char(1), primary key(a desc))
              engine=Spider COMMENT='wrapper "mysql", srv "s", table "t"';

            select a, b from ts where a > 0 and b = 'g' order by a;
            {code}

            SELECT returns an empty result.
            If at least one of PK is not descending, the result is okay ({{`PRIMARY` > (0)}} is not used then in the resulting handler statement).
            elenst Elena Stepanova made changes -
            Summary HANDLER KEY > (x) does not work with DESC keys, Spider is affected HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results
            serg Sergei Golubchik made changes -
            Description {code}
            create table t (a int, b char(1), primary key(a desc));
            insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');

            handler t open;
            handler t read `PRIMARY` > (0);

            # Cleanup
            handler t close;
            drop table t;
            {code}

            The read returns an empty result. With an ascending key it returns a row.

            It causes for example wrong results in Spider when it runs in handler mode.

            Example with Spider:
            {code:sql}
            install soname 'ha_spider';

            set spider_use_handler=3;
            set spider_same_server_link= 1;

            create table t (a int, b char(1), primary key(a desc));
            insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');

            eval create server s foreign data wrapper mysql options
            (host '127.0.0.1', database 'test', user 'root', port $MASTER_MYPORT);

            create table ts (a int, b char(1), primary key(a desc))
              engine=Spider COMMENT='wrapper "mysql", srv "s", table "t"';

            select a, b from ts where a > 0 and b = 'g' order by a;
            {code}

            SELECT returns an empty result.
            If at least one of PK is not descending, the result is okay ({{`PRIMARY` > (0)}} is not used then in the resulting handler statement).             		{code:sql}
            create table t (a int, b char(1), primary key(a desc));
            insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');

            handler t open;
            handler t read `PRIMARY` > (0);

            # Cleanup
            handler t close;
            drop table t;
            {code}

            The read returns an empty result. With an ascending key it returns a row.

            It causes for example wrong results in Spider when it runs in handler mode.

            Example with Spider:
            {code:sql}
            install soname 'ha_spider';

            set spider_use_handler=3;
            set spider_same_server_link= 1;

            create table t (a int, b char(1), primary key(a desc));
            insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');

            eval create server s foreign data wrapper mysql options
            (host '127.0.0.1', database 'test', user 'root', port $MASTER_MYPORT);

            create table ts (a int, b char(1), primary key(a desc))
              engine=Spider COMMENT='wrapper "mysql", srv "s", table "t"';

            select a, b from ts where a > 0 and b = 'g' order by a;
            {code}

            SELECT returns an empty result.
            If at least one of PK is not descending, the result is okay ({{`PRIMARY` > (0)}} is not used then in the resulting handler statement).
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            Component/s Storage Engine - Spider [ 10132 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.8.1 [ 26815 ]
            Fix Version/s 10.8 [ 26121 ]
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.