[MDEV-13782] Issue about the operator NOT LIKE on connect-engine Created: 2017-09-11  Updated: 2019-11-27  Resolved: 2019-11-27

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.2.6, 10.2
Fix Version/s: 10.2.30, 10.3.21, 10.4.11

Type: Bug Priority: Major
Reporter: Martin Francois Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows Server 2008, sql server

Issue Links:
Duplicate
duplicates MDEV-21074 NOT LIKE filter request not working Closed
Relates
relates to MDEV-17508 Fix bug for spider when using "not like" Closed

 Description   

The "not like" operator don't work as expected, it don't return any data when it use trought the connect-engine while the "like" works well, and while on sql_server the exactly same sql query return data

MariaDB [test]> create table ressource  (
 
 
 
    ARS_RESSOURCE varchar(10) NULL,
 
    ARS_TYPERESSOURCE varchar(10) NULL,
 
    ARS_LIBELLE  varchar(40) NULL,
 
    ARS_FERME varchar(10) NULL,
 
    ARS_LIBRERES1 varchar(10) NULL
 
 
 
   )
 
ENGINE=CONNECT 
table_type=ODBC
 
DEFAULT CHARSET=latin1 
CONNECTION='Driver=ODBC Driver 11 for SQL Server;Server=******;Database=******;UID=*******;PWD=******';

so the table is on an remote server so when i try

MariaDB [test]> SELECT ARS_RESSOURCE, ARS_LIBELLE FROM ressource WHERE ARS_RESSOURCE  LIKE "00%"; 
 ARS_RESSOURCE look like '00120', '00230' and the result  throught mariadb of the LIKE operator works well
 
 
 
||ARS_RESSOURCE||ARS_LIBELLE||
 
|00120|foo|
 
|00230|bar|

but when i tried even a whatever query with NOT LIKE operator 

MariaDB [test]> SELECT ARS_RESSOURCE, ARS_LIBELLE FROM ressource WHERE ARS_RESSOURCE NOT LIKE "azeraze%";

I don't have any result while this query works well directly on the sqlserver

if you need any more informations, feel free to ask them

 Comments   
Comment by Alice Sherepa [ 2017-09-12 ]

please add a test case.

MariaDB [test]> CREATE TABLE t (d VARCHAR(16))engine=connect;
 
Query OK, 0 rows affected, 2 warnings (0.21 sec)
 
 
 
MariaDB [test]> INSERT INTO t VALUES ("Monday"), ("Tuesday"), ("Wednesday"), ("Thursday"), ("Friday"), ("Saturday"), ("Sunday");
 
Query OK, 7 rows affected (0.02 sec)
 
Records: 7  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT * FROM t WHERE d not LIKE "T%";
 
+-----------+
 
| d         |
 
+-----------+
 
| Monday    |
 
| Wednesday |
 
| Friday    |
 
| Saturday  |
 
| Sunday    |
 
+-----------+
 
5 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 10.2.6-MariaDB |
 
+----------------+

Comment by Elena Stepanova [ 2017-09-18 ] 

install soname 'ha_connect';
 
 
 
create table t1 (a varchar(16), b varchar(16)) engine=MyISAM;
 
eval create table t2 engine=CONNECT table_type=MYSQL connection='mysql://root@localhost:$MASTER_MYPORT/test/t1';
 
 
 
insert into t1 values ('00120','foo'),('00230','bar');
 
 
 
select * from t2 where a like '00%';
 
select * from t2 where a not like '11%';
 
 
 
# Cleanup
 
drop table t2, t1;
 
uninstall soname 'ha_connect';

Actual result on 10.2 (BAD)

 
MariaDB [test]> select * from t2 where a like '00%';
 
+-------+------+
 
| a     | b    |
 
+-------+------+
 
| 00120 | foo  |
 
| 00230 | bar  |
 
+-------+------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t2 where a not like '11%';
 
Empty set (0.01 sec)

Actual result on 10.1 (GOOD)

 
MariaDB [test]> select * from t2 where a like '00%';
 
+-------+------+
 
| a     | b    |
 
+-------+------+
 
| 00120 | foo  |
 
| 00230 | bar  |
 
+-------+------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t2 where a not like '11%';
 
+-------+------+
 
| a     | b    |
 
+-------+------+
 
| 00120 | foo  |
 
| 00230 | bar  |
 
+-------+------+
 
2 rows in set (0.01 sec)

The difference is visible from the general log.

On 10.1 this happens (11 is my client connection, and 12 is what CONNECT establishes):

170918 23:06:07    11 Query     select * from t2 where a not like '11%'
 
                   12 Connect   root@localhost as anonymous on test
 
                   12 Query     SELECT `a`, `b` FROM `t1`

But on 10.2 this happens (30 is my client connection, and 31 is what CONNECT establishes):

170918 23:07:54    30 Query     select * from t2 where a not like '11%'
 
                   31 Connect   root@localhost as anonymous on test
 
                   31 Query     SELECT `a`, `b` FROM `t1` WHERE a LIKE '11%'

CONNECT adds an inverted clause to the query.

Comment by Olivier Bertrand [ 2017-09-18 ]

I add the same results but could not fix it. Indeed CONNECT tries to add a where clause when possible and this is done when the cond_push function is called. This is tested in the code:

    switch (condf->functype()) {
 
        case Item_func::EQUAL_FUNC:
 
	case Item_func::EQ_FUNC:     vop= OP_EQ;   break;
 
	case Item_func::NE_FUNC:     vop= OP_NE;   break;
 
	case Item_func::LT_FUNC:     vop= OP_LT;   break;
 
	case Item_func::LE_FUNC:     vop= OP_LE;   break;
 
	case Item_func::GE_FUNC:     vop= OP_GE;   break;
 
	case Item_func::GT_FUNC:     vop= OP_GT;   break;
 
	case Item_func::LIKE_FUNC:   vop= OP_LIKE; break;
 
	case Item_func::ISNOTNULL_FUNC:
 
		neg = true;	
		// fall through
 
	case Item_func::ISNULL_FUNC: vop= OP_NULL; break;
 
	case Item_func::IN_FUNC:     vop= OP_IN; /* fall through */
 
        case Item_func::BETWEEN:
 
               ismul= true;
 
               neg= ((Item_func_opt_neg *)condf)->negated;
 
               break;
 
        default: return NULL;
 
    } // endswitch functype

For the LIKE clause, condf->functype() returns LIKE_FUNC and a where clause is made.

For the NOT LIKE clause the return of condf->functype() differs:

Version 10.1 returns NOT_FUNC and CONNECT does not add a where clause.
Version 10.2 returns LIKE_FUNC. This is new and so I tried to fix it adding the test:

        neg= ((Item_func_opt_neg *)condf)->negated;

However, this fix fails because the negated value is not set to true in the case of NOT LIKE.

Comment by Sergei Golubchik [ 2017-09-22 ]

negated is set to true, you needed to add

        neg= ((Item_func_like *)condf)->negated;

but that wouldn't work, because negated is private in Item_func_like.

This code needs some refactoring before you'll be able to access Item_func_like::negated

Comment by Olivier Bertrand [ 2017-09-22 ]

Perhaps adding a virtual function isNegated() in Item_func so no casting would be required.

Comment by Sergei Golubchik [ 2019-11-19 ]

negated was made public in MDEV-17508

Now CONNECT can use it to generate the correct condition for pushing.

Comment by Olivier Bertrand [ 2019-11-19 ]

I just pulled the last 10.2.29 and 10.3.20 versions but could not fix this bug because negated is still private in Item_func_like and no get_negated() function exists in it.

Temporarily, a (bad) fix was to comment out the LIKE case and return to what was done in older versions. LIKE queries are not filtered in ODBC (the all result set is returned so performance can be reduced) but the query is successfully filtered by MariaDB WHERE clause so the good result is obtained.

Comment by Sergei Golubchik [ 2019-11-26 ]

Sorry, pushed into 10.2 now. Commit 3551cd32a8df.

Comment by Olivier Bertrand [ 2019-11-27 ]

A true fix this time.

Generated at Thu Feb 08 08:08:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.