Gary Pendergast wrote:
Hi folks,
I'm trying to to move CONCAT() and CONCAT_WS() to a plugin, but I'm
encountering a weird problem in testing. CONCAT_WS seems to work
correctly, except for when it's being used as part of a WHERE clause.
It's a bit hard to explain, if someone has some time to review, could
you pull
https://code.launchpad.net/~pento/drizzle/drizzle-string-functions , and
run the main.str_func test case?
I've been staring at the code for ages, but I'm not quite sure where I
should be looking to figure this one out.
Some more information. I check out the MD5 UDF to check if the same
problem exists for that plugin, and it does not:
drizzle> create table t1 (a varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)
drizzle> insert into t1 values ("aaa");
Query OK, 1 row affected (0 sec)
drizzle> select md5(a) from t1;
+----------------------------------+
| md5(a) |
+----------------------------------+
| 47bce5c74f589f4867dbd57e9ca9f808 |
+----------------------------------+
1 row in set (0 sec)
drizzle> select * from t1 where md5(a) = '47bce5c74f589f4867dbd57e9ca9f808';
+-----+
| a |
+-----+
| aaa |
+-----+
1 row in set (0 sec)
Then I thought it might have something to do with the LEFT JOIN in the
test case, so I tried a simpler non-join test of CONCAT():
drizzle> select concat('x',a,'x') from t1;
+-------------------+
| concat('x',a,'x') |
+-------------------+
| xaaax |
+-------------------+
1 row in set (0 sec)
drizzle> select * from t1 where concat('x',a,'x') = 'xaaax';
+-----+
| a |
+-----+
| aaa |
+-----+
1 row in set (0 sec)
It works.
Then CONCAT_WS:
drizzle> select concat_ws('-','x',a,'x') from t1;
+--------------------------+
| concat_ws('-','x',a,'x') |
+--------------------------+
| x-aaa-x |
+--------------------------+
1 row in set (0 sec)
drizzle> select * from t1 where concat_ws('-','x',a,'x') = 'x-aaa-x';
+-----+
| a |
+-----+
| aaa |
+-----+
1 row in set (0 sec)
Also works.
So, test with a simple inner join:
drizzle> create table t2 (a varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)
drizzle> insert into t2 values ("aaa");
Query OK, 1 row affected (0 sec)
drizzle> select t1.a as t1a, t2.a as t2a from t1 join t2 on t1.a = t2.a;
+-----+-----+
| t1a | t2a |
+-----+-----+
| aaa | aaa |
+-----+-----+
1 row in set (0.01 sec)
drizzle> select t1.a as t1a, t2.a as t2a from t1 join t2 on t1.a = t2.a
-> where concat_ws('-','x',t1.a,'x') = 'x-aaa-x';
+-----+-----+
| t1a | t2a |
+-----+-----+
| aaa | aaa |
+-----+-----+
1 row in set (0 sec)
drizzle> select t1.a as t1a, t2.a as t2a from t1 join t2 on t1.a = t2.a
-> where concat_ws('-','x',t2.a,'x') = 'x-aaa-x';
+-----+-----+
| t1a | t2a |
+-----+-----+
| aaa | aaa |
+-----+-----+
1 row in set (0 sec)
Everything works.
Now, let's try an outer join:
drizzle> select t1.a as t1a, t2.a as t2a from t1 left join t2 on t1.a = t2.a
-> where concat_ws('-','x',t2.a,'x') = 'x-aaa-x';
+-----+------+
| t1a | t2a |
+-----+------+
| aaa | aaa |
+-----+------+
1 row in set (0 sec)
drizzle> select t1.a as t1a, t2.a as t2a from t1 left join t2 on t1.a = t2.a
-> where concat_ws('-','x',t1.a,'x') = 'x-aaa-x';
+-----+------+
| t1a | t2a |
+-----+------+
| aaa | aaa |
+-----+------+
1 row in set (0 sec)
Works fine. Hrrmph.
Perhaps something to do with the LIKE clause in the test?
drizzle> select t1.a as t1a, t2.a as t2a from t1 left join t2 on t1.a = t2.a
-> where concat_ws('-','x',t1.a,'x') LIKE '%aaa%';
+-----+------+
| t1a | t2a |
+-----+------+
| aaa | aaa |
+-----+------+
1 row in set (0 sec)
Nope.
At this point, I went back to the original test case SQL and started
playing around with it. Check this out:
drizzle> drop table t1;
Query OK, 0 rows affected (0.02 sec)
drizzle> drop table t2;
Query OK, 0 rows affected (0.03 sec)
drizzle> CREATE TABLE t1(
-> id int NOT NULL auto_increment,
-> pc int NOT NULL default '0',
-> title varchar(20) default NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0 sec)
drizzle>
drizzle> INSERT INTO t1 VALUES
-> (1, 0, 'Main'),
-> (2, 1, 'Toys'),
-> (3, 1, 'Games');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
drizzle> SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
-> FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
-> LEFT JOIN t1 AS t3 ON t2.pc=t3.id;
+----+-------------+
| id | col1 |
+----+-------------+
| 1 | Main |
| 2 | Main->Toys |
| 3 | Main->Games |
+----+-------------+
3 rows in set (0 sec)
drizzle> SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
-> FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
-> LEFT JOIN t1 AS t3 ON t2.pc=t3.id
-> WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE
'%Toys%';
Empty set (0 sec)
drizzle> SELECT t1.id, CONCAT_WS('->', t2.title, t1.title) as col1
-> FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
-> WHERE CONCAT_WS('->', t2.title, t1.title) LIKE '%Toys%';
+----+------------+
| id | col1 |
+----+------------+
| 2 | Main->Toys |
+----+------------+
1 row in set (0 sec)
Oops! Turns out it is the third outer join which is causing the issues.
As you can see above, removing the outer join to t3 produces the
correct result...so it must have something to do with the order in which
the ConcatwsFunction objects are being created by the
plugin::Create_function<> factory...
I checked to see if it happens with the md5 function too, and indeed it
does (notice that clearly t3.title is not being properly passed to the
md5 function, but t1.title is...):
drizzle> SELECT t1.id, md5(t3.title) as c
-> FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
-> LEFT JOIN t1 AS t3 ON t2.pc=t3.id;
+----+------+
| id | c |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+----+------+
3 rows in set (0 sec)
drizzle> SELECT t1.id, md5(t1.title) as c
-> FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
-> LEFT JOIN t1 AS t3 ON t2.pc=t3.id;
+----+----------------------------------+
| id | c |
+----+----------------------------------+
| 1 | a02c83a7dbd96295beaefb72c2bee2de |
| 2 | 66d0af2d5da0109dc2aae67829f7d4d4 |
| 3 | 251bd8143891238ecedc306508e29017 |
+----+----------------------------------+
3 rows in set (0 sec)
I'll pursue this further and let you know my findings.
Cheers,
Jay
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
