MySQL Forums
Forum List  »  Stored Procedures

Stored function behavior
Posted by: Michael Graffam
Date: August 16, 2019 11:55AM


I have a table of data built from AWS data, with varchar columns like account, region, vpc, instance-id and so on. The code that builds this table takes the JSON representation of the tags, converts it to an XML representation, and puts that into a mediumtext column.

I have a stored function, GetTag which is defined as:
GetTag (xml mediumtext, foo varchar(128)) returns varchar(256)
return ExtractValue(xml,concat("/AWSTags/tag[Key='",foo,"']/Value"));

Just a simple helper function to make things cleaner and give a shorthand syntax.

I've found a difficulty, perhaps even a bug, in MySQL, that I've tracked down to the following example:

select count(awsid) from instances where awsid in
(select awsid from instances where GetTag(tags,'Name') like '%blah%');
This returns the full count of everything in `instances`

However, if I change the GetTag() call and inline the ExtractValue() representation, things work as expected and I get the count of instances with a tag name like 'blah'

Explanations? Help? Insights?

I'm really puzzled.

I am using Amazon Aurora RDS. I have not yet built an instance to try it on a real MySQL server; mostly because I'm thinking that I'm simply missing something subtle.

Options: ReplyQuote

Written By
Stored function behavior
August 16, 2019 11:55AM
August 16, 2019 12:24PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.