Complex query with ABS, !=, REGEXP, and CONVERT fails.

[joel-rieke]

I created a query that does something like the following:
Table atable c1 = long. c2 = int, c3=int
Table btable c1 = long

select a.c1, a.c2, a.c3 from atable a JOIN btable b on a.c1=b.c1 WHERE (ABS(a.c2) = 1) AND a.c3 != 10 AND a.c1 REGEXP '^[-]?[0-9]+$' AND CONVERT(a.c1, SIGNED) != 0

Expected:
1 row in the result.

Actual:
no results.

Note: If I remove the ABS, the above query works! Also, if I do a.c2 = 1 OR a.c2 = -1, it also works in a standard mysql client.

I'm a little puzzled because I don't see that the CONVERT is officially supported, however the ABS is supported?

[fulghum]

Hey Joel, thanks for opening an issue. ABS is supported and should be working. Could you share the results of show create table atable and select * from atable with us if you can? We'll be happy to try and repro.

[joel-rieke]

Another area I'm having problems with is the following:
update atable set c2=ABS(c2) WHERE c1 in (1,2)

But I get the following:
column name 'c2' is non-nullable but attempted to set a value of null

c2 is not null, but is negative.

[timsehn]

Pop your schema and data in here and we'll repro/fix.

[joel-rieke]

Actually, I think this is going to be auto-closed... sigh... abs(string) == nil. cqtm. Sorry for the trouble. Fun looking at your database code though.

[timsehn]

Glad you figured it out :-) Thanks for poking at our code.