DROP FUNCTION IF EXISTS `f1`;
DELIMITER |
CREATE FUNCTION `f1`(network_id BIGINT) RETURNS BIGINT
DETERMINISTIC—for test
BEGIN
      DECLARE cn BIGINT(20);
      SET cn = (select count(*) from ontime);
      RETURN cn;
END;|
DELIMITER ;

select f1(123) from ontime limit 1;
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.

Hi!
Well, it is not implemented.

It is too difficult just to add it in a free time, and apparently it does not have a high priority in the desired features backlog… I guess you may vote to increase the priority

Here is the code come from ICE 3.5 beta
bool MysqlExpression::SanityAggregationCheck(...)
{
  ...
  if(dynamic_cast<Item_func_sp*>(item) != NULL) {
      Item_func_sp* ifunc = dynamic_cast<Item_func_sp*>(item);
      if(ifunc->argument_count() != 0)
        return false;
      return true;
  }
  ....
}

I comment the argument count check and re-build , do some simple test and could get expected result .

I want to know why need check the parameters count , and if there are some other impact if I comment these code.

Thanks.

Hi !

AFAIK the problem is when the function is used inside a query and it gets arguments from database tables e.g.

Getting values from table t and inserting them as actual function parameters is the problem.

CREATE TABLE `t` (
  `Year` year(4) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1;

mysql> select * from t;
+———+
| Year |
+———+
| 2001 |
| 2002 |
| 2003 |
| 2004 |
| 2005 |
| 2006 |
| 2007 |
| 2008 |
| 2009 |
+———+
9 rows in set (0.00 sec)

DROP FUNCTION IF EXISTS `f4`;
DELIMITER |
CREATE FUNCTION `f4`(network_id BIGINT) RETURNS BIGINT
DETERMINISTIC—for test
BEGIN
      RETURN network_id;
END;|
DELIMITER ;

mysql> select f4(2010) from t limit 1;
+—————+
| f4(2010) |
+—————+
|    2010 |
+—————+
1 row in set (24.39 sec)

mysql> select f4(2010) from t;
+—————+
| f4(2010) |
+—————+
|    2010 |
|    2010 |
|    2010 |
|    2010 |
|    2010 |
|    2010 |
|    2010 |
|    2010 |
|    2010 |
+—————+
9 rows in set (26.66 sec)

It’s seem to have a little discrepancy with your replay, why?

Sorry ,wrong understood .
I means , if there are any problem if i use stored function with constant parameters.
thanks.

Hi Ares,

You should not see any issue with using constant parameters in stored functions.  Let us know if this isn’t the case.

Cheers,

Jeff

In this case it maybe some thing wrong:

select * from t where f6(2001)=Year

But ,In this case it will work well

select f6(2001) from t ;

Why?

Hi !

Please note, that stored function with parameters were never tested as they were not enabled.

What is the f6 definition and Year column definition?

DROP FUNCTION IF EXISTS `f6`;
DELIMITER |
CREATE FUNCTION `f6`(y Year) RETURNS Year
DETERMINISTIC—for test
BEGIN
      RETURN y;
END;|
DELIMITER ;

CREATE TABLE `t` (
  `Year` year(4) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1

thx.

Hi !

In this case it maybe some thing wrong:
select * from t where f6(2001)=Year

What do you mean? With you modifications allowing for stored functions I run the examples successfully:

However, I cannot say anything about the performance. It may be very bad because unoptimized expressions are used, or it may be acceptable because f6(constant) is a constant and therefore optimizable. No one has tried it yet.

Thanks.

Yes. I also notice the performance depend on the data distribute .

Bc the f6(2001) is passed by constant parameter , i think it should be optimized
It should have the same performance with constant expression.

I’m digging into source code and find how to resolve and Can you give me some advice ?

I think MysqlExpression::IsDeterministic is the key function .

So ,I think i can test if the stored function could be evaluate, If it can been evaluated so the stored function should
tread as constant ,is right?

bool IsDeterministic() {return deterministic;}

Change to

bool IsDeterministic() {return deterministic ||  vars.empty();}

is right?

Performance comparsion
Before change
mysql> select event_date,network_id from q_inventory_daily where f6(‘2010-09-01’)=event_date limit 1;
+——————+——————+
| event_date | network_id |
+——————+——————+
| 2010-09-01 |  96313 |
+——————+——————+
1 row in set (0.86 sec)

mysql> select event_date,network_id from q_inventory_daily where f6(‘2010-12-31’)=event_date limit 1;
+——————+——————+
| event_date | network_id |
+——————+——————+
| 2010-12-31 |  40185 |
+——————+——————+
1 row in set (28 min 22.01 sec)

After change
mysql> select event_date,network_id from q_inventory_daily where event_date=f6(‘2010-09-01’) limit 1;
+——————+——————+
| event_date | network_id |
+——————+——————+
| 2010-09-01 |    96313 |
+——————+——————+
1 row in set (0.01 sec)

mysql> select event_date,network_id from q_inventory_daily where event_date=f6(‘2010-12-31’) limit 1;
+——————+——————+
| event_date | network_id |
+——————+——————+
| 2010-12-31 |    40185 |
+——————+——————+
1 row in set (0.01 sec)

Hi,

You’re right - potential nondeterminism is a reason of slower execution of stored functions (even constant). But:

Ares - 26 January 2011 01:30 AM

bool IsDeterministic() {return deterministic;}

Change to

bool IsDeterministic() {return deterministic ||  vars.empty();}

is right?

In general, it is not.
To be deterministic, the function must not depend on e.g. current time, system calls, random number generator etc. Additionally, it must not have any side effects (e.g. setting variables).

On the other hand, a function depending only on its parameters is deterministic.

Regards,