Django2.0.7 generate SQL query function is wrong(REGEXP_LIKE).

[Hiroyuki Yamashita]

My name is Hiroyuki Yamashia.

Create SQL query function is wrong by Django2.0.7.

I using MariaDB and using "__regex".

Django2.0.7 is generate this SQL query.

SELECT `scanner_tmpresponse_07`.`id` FROM `scanner_tmpresponse_07`
WHERE (`scanner_tmpresponse_07`.`audit_history_id` = 225 AND
`scanner_tmpresponse_07`.`deleted` = False AND
`scanner_tmpresponse_07`.`disabled` = False AND
REGEXP_LIKE(`scanner_tmpresponse_07`.`response_body`,
.*192\.168\.\d+\.\d+.*, 'c')) ORDER BY `scanner_tmpresponse_07`.`id`
ASC

I think "REGEXP_LIKE" is Oracle function for Regular expression.

Django2.0.6 is generate this SQL query.
This version is no problem.

SELECT `scanner_tmpresponse_07`.`id` FROM `scanner_tmpresponse_07`
WHERE (`scanner_tmpresponse_07`.`audit_history_id` = 224 AND
`scanner_tmpresponse_07`.`deleted` = False AND
`scanner_tmpresponse_07`.`disabled` = False AND
`scanner_tmpresponse_07`.`response_body` REGEXP BINARY
.*192\.168\.\d+\.\d+.*) ORDER BY `scanner_tmpresponse_07`.`id` ASC


#-------------------------
settings.py

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME' : 'XXXXXXX',
'HOST' : 'localhost',
'USER' : 'XXXXXXXX',
'PASSWORD' : 'XXXXXXXXX',
'PORT' : '3306',
# 'ATOMIC_REQUESTS':True,

},
'scanner': {
'ENGINE': 'django.db.backends.mysql',
'NAME' : 'XXXXXXX',
'HOST' : 'localhost',
'USER' : 'XXXXXXXX',
'PASSWORD' : 'XXXXXXXXX',
'PORT' : '3306',
# 'ATOMIC_REQUESTS':True,

},
'portal': {
'ENGINE': 'django.db.backends.mysql',
'NAME' : 'XXXXXXX',
'HOST' : 'localhost',
'USER' : 'XXXXXXXX',
'PASSWORD' : 'XXXXXXXXX',
'PORT' : '3306',
# 'ATOMIC_REQUESTS':True,
}
}

#-------------------------

Traceback (most recent call last):

File "/opt/virtual_setting/release/lib/python3.6/site-packages/django/db/backends/utils.py",
line 85, in _execute
return self.cursor.execute(sql, params)

File "/opt/virtual_setting/release/lib/python3.6/site-packages/django/db/backends/mysql/base.py",
line 71, in execute
return self.cursor.execute(query, args)

File "/opt/virtual_setting/release/lib/python3.6/site-packages/MySQLdb/cursors.py",
line 250, in execute
self.errorhandler(self, exc, value)

File "/opt/virtual_setting/release/lib/python3.6/site-packages/MySQLdb/connections.py",
line 50, in defaulterrorhandler
raise errorvalue

File "/opt/virtual_setting/release/lib/python3.6/site-packages/MySQLdb/cursors.py",
line 247, in execute
res = self._query(query)

File "/opt/virtual_setting/release/lib/python3.6/site-packages/MySQLdb/cursors.py",
line 412, in _query
rowcount = self._do_query(q)

File "/opt/virtual_setting/release/lib/python3.6/site-packages/MySQLdb/cursors.py",
line 375, in _do_query
db.query(q)

File "/opt/virtual_setting/release/lib/python3.6/site-packages/MySQLdb/connections.py",
line 276, in query
_mysql.connection.query(self, query)

_mysql_exceptions.OperationalError: (1305, 'FUNCTION
scanner.REGEXP_LIKE does not exist')


The above exception was the direct cause of the following exception:


--
==============================
Hiroyuki Yamashita 山下　裕之
==============================

[Jason]

what's the django orm code that generates that query?

[Tim Graham]

This is a regression that will be fixed in Django 2.0.8: https://code.djangoproject.com/ticket/29544

[Hiroyuki Yamashita]

Ohh.It's lacked my confirmation.

Thanks guys.