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

Logically, the return of >=ANY should contain =ANY

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 11.4.2
    • N/A
    • Data types
    • None
    • ubuntu 20.04

    Description

      drop table if exists t;
      CREATE TABLE t (c1 FLOAT,c2 VARCHAR(20),key(c1));
      INSERT INTO t VALUES (94.1106,'-0'),(1,'3	'),(0.0001,'-1');
       
       SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)); -- 1
       
       
       SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)); -- empty set
      
      

      Logically, a sentence containing >=ANY should return a greater result than a sentence containing =ANY。

      SELECT c1 AS f1 FROM t;
      result:
       
      +---------+
      | f1      |
      +---------+
      |  0.0001 |
      |       1 |
      | 94.1106 |
      +---------+
      

      So f1 should include 0.0001,1,94.1106.

      SELECT c2 FROM t;
      --return -0,3,-1.
      

      Now ((-f1)>=ANY (SELECT c2 FROM t)) is equivalent to (-f1)>= (-1). The eligible F1s are 0.0001,1.

      So the result of the second sql should be 0.0001,1, not the empty set.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Wangdada HeShan
              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.