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

Impossible WHERE a!=a

        Details

        • Type: Task
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Fix Version/s: 10.1
        • Component/s: Optimizer
        • Labels:
          None

          Description

          from 5.5.44

          | gpd | CREATE TABLE `gpd` (
  `recID` int(11) NOT NULL AUTO_INCREMENT,
  `sessionID` int(11) DEFAULT NULL,
  `time` decimal(12,0) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  `angle` int(11) DEFAULT NULL,
  `velocity` decimal(12,6) DEFAULT NULL,
  `acceleration` decimal(12,6) DEFAULT NULL,
  `forcePlate` decimal(12,6) DEFAULT '0.000000',
  PRIMARY KEY (`recID`),
  KEY `sessionID` (`sessionID`)
) ENGINE=InnoDB AUTO_INCREMENT=464380557 DEFAULT CHARSET=latin1 |

          Queries like this should end up as 'Impossible Where' the same as 'WHERE 0' is used. Same with > or < as an operator.

          explain SELECT 1 FROM `gpd` WHERE `gpd`.`sessionID` != `gpd`.`sessionID`;
+------+-------------+---------------+-------+---------------+-----------+---------+------+-----------+--------------------------+
| id   | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows      | Extra                    |
+------+-------------+---------------+-------+---------------+-----------+---------+------+-----------+--------------------------+
|    1 | SIMPLE      | gpd           | index | NULL          | sessionID | 5       | NULL | 461252463 | Using where; Using index |

          I came across this and was potentially generated from the depths of an ORM (sqlalchemy)

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    danblack Daniel Black
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: