Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 10.1.3
-
Fix Version/s: N/A
-
Component/s: Data Manipulation - Subquery
-
Labels:None
-
Environment:Linux
Description
This is a bug that makes it impossible to replace some applications written for MS SQL or Oracle, etc.
The Like operator works only when the column is on the left, and in the right side there is an expression to be matched.
for instance
select * from Table where npanxx like '9544%'
it correctly finds row with value '954444'
But,in all databases that I know of, this also finds that match
select * from Table where '9544447408' like concat(npanxx,'%')
It does not work in MariaDB, and it should. I am not sure about MySQL, but in any case, it is a bug and we need to fix it and make it efficient.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I cannot explain it, look
select * from ratedeck where code like '1954444%'; +------------+--------+--------+ +------------+--------+--------+ +------------+--------+--------+ 1 row in set (0.00 sec) MariaDB [asterisk]> select * from ratedeck where '19544447408' like concat(code,'%'); Empty set (0.00 sec) select @@version; +-----------------+ +-----------------+ +-----------------+ CREATE TABLE ratedeck ( code varchar(10) NOT NULL, inter float NOT NULL DEFAULT 0, intra float NOT NULL DEFAULT 0, PRIMARY KEY (code) )
I can give you access to the box, it is not a production box.
this is a mystery
If this email is private, I can send you the password, or I can create a
user name on the database and you may login directly. It is on the public
internet.
Yours
Philip
On Tue, Mar 31, 2015 at 9:04 AM, Elena Stepanova (JIRA) <
jira@mariadb.atlassian.net> wrote:
Your paste came to JIRA broken.
Anyway, if you want me to look at it at your box, my public SSH key is here https://launchpad.net/~elenst/+sshkeys .
I already added your key
My IP is 8.19.245.233, root
cannot explain it, look
mysql asterisk select * from ratedeck where code like '1954444%'; +------------+--------+--------+ +------------+--------+--------+ +------------+--------+--------+ 1 row in set (0.00 sec) select * from ratedeck where '19544447408' like concat(code,'%'); Empty set (0.00 sec) select @@version; +-----------------+ +-----------------+ +-----------------+ CREATE TABLE ratedeck ( code varchar(10) NOT NULL, inter float NOT NULL DEFAULT 0, intra float NOT NULL DEFAULT 0, PRIMARY KEY (code) )
The Jira system kills any information between plus signs
On Tue, Mar 31, 2015 at 9:55 AM, Philip orleans (JIRA) <
here I go again
select * from ratedeck where code like '1954444%'; +------------+--------+--------+ code inter intra | +------------+--------+--------+ 1954444 0.0007 0.0007 | +------------+--------+--------+
select * from ratedeck where '19544447408' like concat(code,'%');
Empty set (0.00 sec)
On Tue, Mar 31, 2015 at 10:01 AM, Philip orleans (JIRA) <
Here is the problem:
MariaDB [test]> select hex(code) from asterisk.ratedeck where code like '1954444%'; +----------------------+ | hex(code) | +----------------------+ | 31393534343434202020 | +----------------------+ 1 row in set (0.00 sec)
Your value has 3 extra symbols at the end, so naturally it doesn't match the numeric string '19544447408'.
Thanks, please close the case.
Yours
Federico
On Tue, Mar 31, 2015 at 10:29 AM, Elena Stepanova (JIRA) <
Works for me:
Please paste the exact queries and output from the client.