hello sir,

I am postgraduate at Dhirubhai Ambani Institute of Information and Communication technology,Ahmedabad.I'd like to contribute to this project in the GSOC 2017. I have a good knowledge about Sql,Postgresql and little bit about mariadb..I want more details about this issue..

Thanks
Jabbar Memon

In SQL terms the condition col1!=col1 will always be false. The same with col1<col1 and col1>col1. Rather than have mariadb engine look through the entire table to validate this, the optimizer can include the code that checks for these impossible conditions and generate the "Impossible Where" warning as well as effectively replace the condition with FALSE.

Like wise a col1=col1 condition is almost always true. Requiring the mariadb server to fetch all rows to validate this would be a waste of time. Replacing this with TRUE would enable the query optimiser to choose a better plan. Using your SQL knowledge, when is col1=col1 false?

Hello sir,
i am running this query select * from gpd where gpd.sessionID!=gpd.sessionID; but it is showing no record.can plz help what to do now i am confused little bit.

Thank You
Jabbar Memon

Points to consider:

• every kind of processing adds some overhead. If we make the optimizer too smart, people start complaining about simple queries having too much overhead.
• how broad should this be? Should it just look for col1!=col1 , or some other forms should be checked for, also?

I suspect the comparison of the two sides of the expression to see if they are the same table/column will be simple leaving the longer case of doing the >, <, and != (to be handled the same) and the = case which requires checking if the column can be NULL checks to determine which simplification is needed.

thank you for the help but if the table is same then we dont have to check == and if the column is null so we can make that column primary key so that will reduce the error i think...

Thank You
Jabbar Memon

Given the number of other proposals being received, the lower importance of this task, and the limited mentor resources I'm removing the gsoc label for this year. Thanks for your interest and apologies for changing my mind on this task.

psergey as it turns out this is very easy and virtually no impact for reasons described on https://github.com/MariaDB/server/pull/1027

Fixed in MySQL-8.0

----------- MYSQLTEST OUTPUT START -----------

CREATE TABLE t1 (a TINYINT NOT NULL, KEY(a));

INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

SELECT VERSION();

VERSION()

8.0.13

EXPLAIN SELECT * FROM t1 WHERE a!=a;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

Warnings:

Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0

EXPLAIN SELECT * FROM t1 WHERE a>a;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

Warnings:

Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0

EXPLAIN SELECT * FROM t1 WHERE a<a;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

Warnings:

Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0

EXPLAIN SELECT * FROM t1 WHERE NOT a=a;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

Daniel,
For the above examples the current 10.4 returns:

MariaDB [test]> CREATE TABLE t1 (a TINYINT NOT NULL, KEY(a));

Query OK, 0 rows affected (0.025 sec)

MariaDB [test]> INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

Query OK, 5 rows affected (0.011 sec)

Records: 5  Duplicates: 0  Warnings: 0

MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE a<a;

+------+-------------+-------+------+---------------+------+---------+------+------+------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------+

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------+

There is no wonder, because the MySQL code that processes a<a is practically the same as in MariaDB

Quite right MySQL and MariaDB are behaving identical. I was getting confused on table definitions too.

Remove the `NOT NULL` from the column definition is where the 'Impossible WHERE' is no longer detected. Adding an index just changes plan to 'Using index'. Also the `WHERE not (a!=a)` is a condition that doesn't result in 'Select tables optimized away'. The updated PR corrects both of these to use the more optimal query plan.

Summary of existing MariaDB behaviour (and can switch to MySQL-8.0.13 quickly):
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f4b57e5eb6b34bd47d2d71a8f9bde3cf

Hi Daniel,
The patch is ok.

PR have been applied.