OK, so. On the "execute" command of a prepared statement we have a detection routine to check if ColumnStore tables are involved so that we can use vtable. This routine uses the provided table list and checks in it for the underlying tables for views, but not stored procedures. This effectively means vtable is off (in fact vtable is in an undefined state which fires an assert in a debug build).
Two pull requests open, one for develop-1.0 and one for develop
For testing I used this PHP code with our regression suite, on a debug build it takes 0.6 seconds using vtable correct and 6 seconds when not. It can be compared with calling "call proctest()" directly in the MariaDB client.
<?php
|
$conn = new mysqli("127.0.0.1", "root", "", "tpch1");
|
|
|
$stmt = $conn->prepare("call proctest()");
|
|
|
if ($stmt->execute())
|
{
|
$stmt->bind_result($count);
|
|
|
while ($stmt->fetch()) {
|
printf("%s\n", $count);
|
}
|
}
|
else
|
{
|
printf("Error: %s.\n", $stmt->error);
|
}
|
|
|
$stmt->close();
|
$conn->close();
|
This is the procedure:
CREATE PROCEDURE `proctest`()
|
begin select count(*) from orders group by o_orderpriority; end;
|
Build tested: 1.0.9-1 (branch develop-1.0)
[root@localhost mariadb-columnstore-server]# git show
commit 8ef673b333f79586645321a60e1ee3c1bcfc491c
Merge: e688674 7e877fa
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:51:52 2017 -0500
Merge pull request #39 from mariadb-corporation/MCOL-652-1.0
MCOL-652 Fix certain derived table cases
[root@localhost mariadb-columnstore-engine]# git show
commit 1c2e9bd8281c5ad717820b7ec0bc234c47321b22
Merge: 310f8e5 b60c45a
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:47:57 2017 -0500
Merge pull request #144 from mariadb-corporation/MCOL-653-1.0
MCOL-653 Revert SUM(1) behaviour
Tested with a 10g dbt3 database on a 1um2pm stack
MariaDB [dbt3]> delimiter //
MariaDB [dbt3]> CREATE PROCEDURE `proctest`() begin select count
from orders group by o_orderpriority; end; //
Query OK, 0 rows affected (0.01 sec)
MariaDB [dbt3]> delimiter ;
MariaDB [dbt3]> call proctest();
----------
| count |
----------
| 2997646 |
| 3000061 |
| 3003093 |
| 3000260 |
| 2998940 |
----------
5 rows in set (0.82 sec)
MariaDB [dbt3]> quit
Bye
[root@localhost tests]# php test.php
2997646
3000061
3003093
3000260
2998940
[root@localhost tests]# time php test.php
2997646
3000061
3003093
3000260
2998940
real 0m0.675s
user 0m0.007s
sys 0m0.009s
Build verified: 1.1.0-1 (GitHub develop branch)
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:52:07 2017 -0500
Merge pull request #38 from mariadb-corporation/MCOL-652
MCOL-652 Fix certain derived table cases
[root@localhost mariadb-columnstore-server]# cd mariadb-columnstore-engine/
[root@localhost mariadb-columnstore-engine]# git show
commit 3e85b6ef07ffcfb4edc84e170efcccdb9cd15cc2
Merge: 04bdb60 8baf059
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:48:55 2017 -0500
Merge pull request #143 from mariadb-corporation/MCOL-653
MCOL-653 Revert SUM(1) behaviour
MariaDB [dbt3]> delimiter //
MariaDB [dbt3]> CREATE PROCEDURE `proctest`() begin select count from orders group by o_orderpriority; end; //
Query OK, 0 rows affected (0.01 sec)
MariaDB [dbt3]> delimiter ;
MariaDB [dbt3]> call proctest();
----------
| count |
----------
| 2997646 |
| 3000061 |
| 3003093 |
| 3000260 |
| 2998940 |
----------
5 rows in set (0.76 sec)
[root@localhost tests]# time php test.php
2997646
3000061
3003093
3000260
2998940
real 0m0.559s
user 0m0.008s
sys 0m0.007s
[root@localhost tests]#