WHERE

You for sure know an SQL-statement like

     SELECT *
     FROM table
     WHERE condition

From a storage-engine point some questions arise:

• can I see this statement ?
• what's in WHERE ?
• can I access the WHERE-clause ?
• can I use this information in my code (=storage engine) ?

The answer is YES. In this post I like to show you how to access this information. Using it in the engine will be a different thing, maybe I will show you in a later post how to do this.

This topic is named condition pushdown. Here you will find more information about this topic: MySQL 5.1 Plugin Development.

Gaining access to the information in the SQL-statement is simple: every engine is derived from a class handler and in this class you will find a function cond_push().
let's look into handler.h:

virtual const COND *cond_push(const COND *cond) { return cond; };

it's defined, but it does nothing.

As a summary: by handling an SQL-statement the server prepares the statement, converts it into an appropriate form and for every table referenced in the statement it calls this routine in the storage-engine. For accessing this information we have to implement this function in our storage-engine.

So let's do it. Please allow me to skip long explanations but simply show you some code. Take the code from MinTempl as a base and extend it by adding via copy and paste:
in the header-file:

const COND *cond_push(const COND *cond);

in the code-file:

/**
 * WHERE
 *
 * in case you want to know something about the WHERE-clause
 * you can use the following code as an example
 */
static void display_functype( int type)
{
    const char *strFuncType[] = { "UNKNOWN_FUNC",
            "EQ_FUNC", "EQUAL_FUNC", "NE_FUNC", "LT_FUNC",
            "LE_FUNC", "GE_FUNC", "GT_FUNC", "FT_FUNC",
            "LIKE_FUNC", "ISNULL_FUNC", "ISNOTNULL_FUNC", "COND_AND_FUNC",
            "COND_OR_FUNC", "COND_XOR_FUNC", "BETWEEN", "IN_FUNC",
            "MULT_EQUAL_FUNC", "INTERVAL_FUNC", "ISNOTNULLTEST_FUNC", "SP_EQUALS_FUNC",
            "SP_DISJOINT_FUNC", "SP_INTERSECTS_FUNC", "SP_TOUCHES_FUNC", "SP_CROSSES_FUNC",
            "SP_WITHIN_FUNC", "SP_CONTAINS_FUNC", "SP_OVERLAPS_FUNC", "SP_STARTPOINT",
            "SP_ENDPOINT", "SP_EXTERIORRING", "SP_POINTN", "SP_GEOMETRYN",
            "SP_INTERIORRINGN", "NOT_FUNC", "NOT_ALL_FUNC", "NOW_FUNC",
            "TRIG_COND_FUNC", "SUSERVAR_FUNC", "GUSERVAR_FUNC", "COLLATE_FUNC",
            "EXTRACT_FUNC", "CHAR_TYPECAST_FUNC", "FUNC_SP", "UDF_FUNC",
            "NEG_FUNC", "GSYSVAR_FUNC" };

    fprintf(stderr, "type=[%s]\t", strFuncType[type]);
}

const COND *ha_mintempl::cond_push(const COND *cond)
{
    COND * tempCond = (COND*)cond;

    if ( tempCond == NULL )
        return NULL;

    fprintf(stderr, "\ntable_name = <%s>\t<%s>\n", table_share->table_name.str, current_thd->query());
    int level = 0;
    fprintf(stderr, "\n");
    while ( tempCond->next != NULL)
    {
        fprintf(stderr, "level=%d ", level);
        switch( tempCond->type() )
        {
            case Item::FIELD_ITEM:
                fprintf(stderr, "FIELD-ITEM");
                fprintf(stderr, "\t[%s] [%s] [%s]",((Item_field*)tempCond)->db_name, ((Item_field*)tempCond)->table_name, ((Item_field*)tempCond)->field_name);
                break;
            case Item::FUNC_ITEM:
                fprintf(stderr, "FUNC-ITEM");
                fprintf(stderr, "=[%s]", ((Item_func*)tempCond)->func_name());
                fprintf(stderr, " args: %d  ", ((Item_func*)tempCond)->argument_count() );
                display_functype( (int)((Item_func*)tempCond)->functype());
                break;
            case Item::SUM_FUNC_ITEM:
                fprintf(stderr, "SUM-FUNC--ITEM");
                break;
            case Item::STRING_ITEM:
                fprintf(stderr, "STRING-ITEM");
                break;
            case Item::INT_ITEM:
                fprintf(stderr, "INT-ITEM");
                fprintf(stderr, "\tval = %lld", ((Item_int*)tempCond)->value);
                fprintf(stderr, " res=%d", ((Item_int*)tempCond)->result_type());
                break;
            case Item::REAL_ITEM:
                fprintf(stderr, "REAL-ITEM");
                break;
            case Item::NULL_ITEM:
                fprintf(stderr, "NULL-ITEM");
                break;
            case Item::VARBIN_ITEM:
                fprintf(stderr, "VAR-BIN");
                 break;
            case Item::COPY_STR_ITEM:
                fprintf(stderr, "COPY-STR-ITEM");
                break;
            case Item::FIELD_AVG_ITEM:
                fprintf(stderr, "FIELD-AVG-ITEM");
                break;
            case Item::DEFAULT_VALUE_ITEM:
                fprintf(stderr, "DEFAULT-VLAUE-ITEM");
                break;
            case Item::PROC_ITEM:
                fprintf(stderr, "PROC-ITEM");
                break;
            case Item::COND_ITEM:
                fprintf(stderr, "COND-ITEM");
                fprintf(stderr, " args: %d  ", ((Item_cond*)tempCond)->argument_count() );
                display_functype( (int)((Item_cond*)tempCond)->functype());
                break;
            case Item::REF_ITEM:
                fprintf(stderr, "REF-ITEM");
                break;
            case Item::FIELD_STD_ITEM:
                fprintf(stderr, "FIELD-STD-ITEM");
                break;
            case Item::FIELD_VARIANCE_ITEM:
                fprintf(stderr, "FIELD-VAIRANCE-ITEM");
                break;
            case Item::INSERT_VALUE_ITEM:
                fprintf(stderr, "INSERT-VALUE-ITEM");
                break;
            case Item::SUBSELECT_ITEM:
                fprintf(stderr, "SUBSELECT-ITEM");
                break;
            case Item::ROW_ITEM:
                fprintf(stderr, "ROW-ITEM");
                break;
            case Item::CACHE_ITEM:
                fprintf(stderr, "CACHE-ITEM");
                break;
            case Item::TYPE_HOLDER:
                fprintf(stderr, "TYPE-HOLDER");
                break;
            case Item::PARAM_ITEM:
                fprintf(stderr, "PARAM-ITEM");
                break;
            case Item::TRIGGER_FIELD_ITEM:
                fprintf(stderr, "TRIGGER-FIELD-ITEM");
                break;
            case Item::DECIMAL_ITEM:
                fprintf(stderr, "DECIMAL-ITEM");
                break;
            case Item::XPATH_NODESET:
                fprintf(stderr, "XPATH-NODESET");
                break;
            case Item::XPATH_NODESET_CMP:
                fprintf(stderr, "XPATH-NODESET-CMP");
                break;
            case Item::VIEW_FIXER_ITEM:
                fprintf(stderr, "VIEW-FIXER-ITEM");
                break;
            default:
                fprintf(stderr, "unknown: %d ",  tempCond->type());
                break;
        }
        if (tempCond->str_value.ptr() != NULL )
            fprintf(stderr, "\tstr_value=<%s>", tempCond->str_value.ptr());
        if ( tempCond->name != NULL )
            fprintf(stderr, "\tname=<%s>", tempCond->name);
        level++;
        tempCond=tempCond->next;
        fprintf(stderr, "\n");
    }
    fprintf(stderr, "\n");
    return cond;
}

hint: please look at the update-section at the end of this text.You will find a ink to an updated-version of this code in this scetion.
Then recompile everything. Now you have a storage-engine MinTempl with a meaning quite different from the one described in the posting that can be found in the archive of the blog (but that's what I used this template for: extending and playing with the code). If you prefer you can create a copy of MinTempl and give it a new name. For this posting I will call the storage-engine with the added code MinTempl.
When you start your fresh compiled server server you should check if MinTempl is there and if it is not you should install it as described in an earlier posting.

MariaDB

For MySQL everything is ready and awaiting the first test but for MariaDB you have to do something. Please look at this output which I reformatted and polished a bit:

MariaDB [(none)]> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 
    index_merge=on,
    index_merge_union=on,
    index_merge_sort_union=on,
    index_merge_intersection=on,
    index_merge_sort_intersection=off,
    engine_condition_pushdown=off,
    index_condition_pushdown=on,
    derived_merge=on,
    derived_with_keys=on,
    firstmatch=on,loosescan=on,
    materialization=on,
    in_to_exists=on,
    semijoin=on,
    partial_match_rowid_merge=on,
    partial_match_table_scan=on,
    subquery_cache=on,
    mrr=off,
    mrr_cost_based=off,
    mrr_sort_keys=off,
    outer_join_with_cache=on,
    semijoin_with_cache=on,
    join_cache_incremental=on,
    join_cache_hashed=on,
    join_cache_bka=on,
    optimize_join_buffer_size=off,
    table_elimination=on,
    extended_keys=off,
    exists_to_in=off
1 row in set (0.00 sec)

What you see is that condition pushdown is disabled by default in MariaDB so we have to enable it:

MariaDB [(none)]> set optimizer_switch='engine_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 
    ....
    engine_condition_pushdown=on,
    ....
1 row in set (0.00 sec)

continuing

Everything is ready and awaiting out first test. So start your preferred frontend, connect to the server and start with these steps: I created a database for our tests and in this database I created a table into which some data is inserted:

MariaDB [(none)]> create database TestCP;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use TestCP;
Database changed

MariaDB [TestCP]> create table Test1 (
    ->            Id   char(8),
    ->            PZN  char(7),
    ->            EVP  char(8),
    ->            HAP  char(8),
    ->            ArtikelBez varchar(40),
    ->            ArtikelText varchar(26),
    ->            Hersteller  char(5) )
    ->            engine=MINTEMPL
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [TestCP]> insert into Test1 select * from TestOK.ABDAOK limit 100;
Query OK, 100 rows affected (0.03 sec)
Records: 100  Duplicates: 0  Warnings: 0

MariaDB [TestCP]> 

So, enough of the introductory work, let's start looking into the SQL-statement. Here is a first and very simple example:
SQL:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234';

And here is the output produced by our cond_push() that you can see in the console of Eclipse:

table_name = <Test1>    <select * from Test1 where Hersteller = '1234'>
level=0 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=1 FUNC-ITEM=[multiple equal] args: 0   type=[MULT_EQUAL_FUNC]
level=2 STRING-ITEM    str_value=<1234>    name=<1234>
level=3 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
level=4 FIELD-ITEM    [TestCP] [Test1] [ArtikelText]    name=<ArtikelText>
level=5 FIELD-ITEM    [TestCP] [Test1] [ArtikelBez]    name=<ArtikelBez>
level=6 FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
level=7 FIELD-ITEM    [TestCP] [Test1] [EVP]    name=<EVP>
level=8 FIELD-ITEM    [TestCP] [Test1] [PZN]    name=<PZN>
level=9 FIELD-ITEM    [TestCP] [Test1] [Id]    name=<Id>
level=10 INT-ITEM    val = -1 res=2
level=11 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=12 STRING-ITEM    str_value=<1234>    name=<1234>
level=13 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>

Well, confusing.

But if you look a bit closer at the lines you will see:

•  the name of the database: TestCP
•  the name of the table: Test1
• the name of the column: Hersteller
• the string-constant: 1234;

and so on.

Please look at the last 3 lines of the output. There you will see the condition (I reformatted it a bit):

FUNC-ITEM    [EQ_FUNC]
STRING-ITEM    <1234>
FIELD-ITEM    [TestCP] [Test1] [Hersteller]

looks better now and this is our WHERE-condition in the form: EQ_FUNC('1234', TestCP.Test1.Hersteller)
In the form of a tree it looks like this:

Let's make our example a bit more complicated:
SQL:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234' and HAP > 10;

and here is the output of the our function cond_push() in the console:

table_name = <Test1>    <select * from Test1 where Hersteller = '1234' and HAP > 10>
level=0 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=1 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=2 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=3 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=4 FUNC-ITEM=[multiple equal] args: 0   type=[MULT_EQUAL_FUNC]
level=5 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=6 CACHE-ITEM
level=7 STRING-ITEM    str_value=<1234>    name=<1234>
level=8 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
level=9 FIELD-ITEM    [TestCP] [Test1] [ArtikelText]    name=<ArtikelText>
level=10 FIELD-ITEM    [TestCP] [Test1] [ArtikelBez]    name=<ArtikelBez>
level=11 FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
level=12 FIELD-ITEM    [TestCP] [Test1] [EVP]    name=<EVP>
level=13 FIELD-ITEM    [TestCP] [Test1] [PZN]    name=<PZN>
level=14 FIELD-ITEM    [TestCP] [Test1] [Id]    name=<Id>
level=15 INT-ITEM    val = -1 res=2
level=16 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=17 FUNC-ITEM=[>] args: 2   type=[GT_FUNC]
level=18 INT-ITEM    val = 10 res=2    name=<10>
level=19 FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
level=20 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=21 STRING-ITEM    str_value=<1234>    name=<1234>
level=22 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>

When you look at the last 7 lines of this output you will see (again, I reformatted this a bit):

COND-ITEM    [COND_AND_FUNC]
FUNC-ITEM    [GT_FUNC]
INT-ITEM    10
FIELD-ITEM    [TestCP].[Test1].[HAP]
FUNC-ITEM=[=]    type=[EQ_FUNC]
STRING-ITEM    <1234>
FIELD-ITEM    [TestCP].[Test1].[Hersteller]

That looks promising: COND_AND_FUNC( GT_FUNC( 10, TestCP.Test1.HAP), EQ_FUNC( '1234'. TestCP.Test1.Hersteller) )
And in the form of a tree:

Looks like we got the information, but it's hidden under a lot of other information. Can't the server deliver better results immediately?

Looking into the code of MariaDB or MySQL you can see some functions whose names suggest that these help in wandering through the condition-tree:
in item.h:

   virtual void traverse_cond(Cond_traverser traverser,
                              void *arg, traverse_order order)
   {
     (*traverser)(this, arg);
   }

in item_func.h:

  void traverse_cond(Cond_traverser traverser,
                     void * arg, traverse_order order);

you will find the implementation of Item_func::traverse_cond() in item_func.cc.
in item_cmpfunc.h:

  void traverse_cond(Cond_traverser, void *arg, traverse_order order);

You will find the implementation of Item_cond::traverse_cond() in item_cmpfunc.cc.

So let's start, in Eclipse set breakpoints on these functions and simply repeat our tests.You will see ..... nothing, these functions are not called. The reason for this: we have to call them.

another try

As before: instead of long explanations I will show you some code:

in the top of the file ha_mintempl.cc please add these two lines:

static void display_functype( int );
static void handle_Item( const Item *item, void *arg);

and later in the file please add these lines (which replace the lines of our first try):

/**
 * WHERE
 *
 * in case you want to know something about the WHERE-clause
 * you can use the following code as an example
 */
static void display_functype( int type)
{
    const char *strFuncType[] = { "UNKNOWN_FUNC",
            "EQ_FUNC", "EQUAL_FUNC", "NE_FUNC", "LT_FUNC",
            "LE_FUNC", "GE_FUNC", "GT_FUNC", "FT_FUNC",
            "LIKE_FUNC", "ISNULL_FUNC", "ISNOTNULL_FUNC", "COND_AND_FUNC",
            "COND_OR_FUNC", "COND_XOR_FUNC", "BETWEEN", "IN_FUNC",
            "MULT_EQUAL_FUNC", "INTERVAL_FUNC", "ISNOTNULLTEST_FUNC", "SP_EQUALS_FUNC",
            "SP_DISJOINT_FUNC", "SP_INTERSECTS_FUNC", "SP_TOUCHES_FUNC", "SP_CROSSES_FUNC",
            "SP_WITHIN_FUNC", "SP_CONTAINS_FUNC", "SP_OVERLAPS_FUNC", "SP_STARTPOINT",
            "SP_ENDPOINT", "SP_EXTERIORRING", "SP_POINTN", "SP_GEOMETRYN",
            "SP_INTERIORRINGN", "NOT_FUNC", "NOT_ALL_FUNC", "NOW_FUNC",
            "TRIG_COND_FUNC", "SUSERVAR_FUNC", "GUSERVAR_FUNC", "COLLATE_FUNC",
            "EXTRACT_FUNC", "CHAR_TYPECAST_FUNC", "FUNC_SP", "UDF_FUNC",
            "NEG_FUNC", "GSYSVAR_FUNC" };

    fprintf(stderr, "type=[%s]\t", strFuncType[type]);
}

const COND * ha_mintempl::cond_push(const COND *cond)
{
  DBUG_ENTER("ha_mintempl::cond_push");

  THD *cThd = current_thd;
  // access the full query:
  fprintf(stderr, "\ntable_name = <%s>\t<%s>\n", table_share->table_name.str, cThd->query());
  
  // the query prepared by MySQL/MariaDB for this table:
  ((Item*)cond)->traverse_cond(&handle_Item, NULL, Item::PREFIX);   // Item::POSTFIX

  // future extension: tell the server that we take over all of the work
  //    DBUG_RETURN(NULL);

  DBUG_RETURN(cond);
};

static void handle_Item(const Item *item, void *arg)
{
    COND * tempCond = (COND*)item;

    if ( tempCond == NULL )
        return;

    switch( tempCond->type() )
    {
        case Item::FIELD_ITEM:
            fprintf(stderr, "FIELD-ITEM\t");
            fprintf(stderr, "[%s] [%s] [%s]",((Item_field*)tempCond)->db_name, ((Item_field*)tempCond)->table_name, ((Item_field*)tempCond)->field_name);
            break;
        case Item::FUNC_ITEM:
            fprintf(stderr, "FUNC-ITEM\t");
            fprintf(stderr, "[%s]\t", ((Item_func*)tempCond)->func_name());
            fprintf(stderr, "args: %d\t", ((Item_func*)tempCond)->argument_count() );
            display_functype( (int)((Item_func*)tempCond)->functype());
            break;
        case Item::SUM_FUNC_ITEM:
            fprintf(stderr, "SUM-FUNC-ITEM\t");
            break;
        case Item::STRING_ITEM:
            fprintf(stderr, "STRING-ITEM\t");
            break;
        case Item::INT_ITEM:
            fprintf(stderr, "INT-ITEM\t");
            fprintf(stderr, "val = %lld\t", ((Item_int*)tempCond)->value);
            fprintf(stderr, "res=%d", ((Item_int*)tempCond)->result_type());
            break;
        case Item::REAL_ITEM:
            fprintf(stderr, "REAL-ITEM\t");
            break;
        case Item::NULL_ITEM:
            fprintf(stderr, "NULL-ITEM\t");
            break;
        case Item::VARBIN_ITEM:
            fprintf(stderr, "VAR-BIN\t");
            break;
        case Item::COPY_STR_ITEM:
            fprintf(stderr, "COPY-STR-ITEM\t");
            break;
        case Item::FIELD_AVG_ITEM:
            fprintf(stderr, "FIELD-AVG-ITEM\t");
            break;
        case Item::DEFAULT_VALUE_ITEM:
            fprintf(stderr, "DEFAULT-VLAUE-ITEM\t");
            break;
        case Item::PROC_ITEM:
            fprintf(stderr, "PROC-ITEM\t");
            break;
        case Item::COND_ITEM:
            fprintf(stderr, "COND-ITEM\t");
            fprintf(stderr, "args: %d\t", ((Item_cond*)tempCond)->argument_count() );
            display_functype( (int)((Item_cond*)tempCond)->functype());
            break;
        case Item::REF_ITEM:
            fprintf(stderr, "REF-ITEM\t");
            break;
        case Item::FIELD_STD_ITEM:
            fprintf(stderr, "FIELD-STD-ITEM\t");
            break;
        case Item::FIELD_VARIANCE_ITEM:
            fprintf(stderr, "FIELD-VAIRANCE-ITEM\t");
            break;
        case Item::INSERT_VALUE_ITEM:
            fprintf(stderr, "INSERT-VALUE-ITEM\t");
            break;
        case Item::SUBSELECT_ITEM:
            fprintf(stderr, "SUBSELECT-ITEM\t");
            break;
        case Item::ROW_ITEM:
            fprintf(stderr, "ROW-ITEM\t");
            break;
        case Item::CACHE_ITEM:
            fprintf(stderr, "CACHE-ITEM\t");
            break;
        case Item::TYPE_HOLDER:
            fprintf(stderr, "TYPE-HOLDER\t");
            break;
        case Item::PARAM_ITEM:
            fprintf(stderr, "PARAM-ITEM\t");
            break;
        case Item::TRIGGER_FIELD_ITEM:
            fprintf(stderr, "TRIGGER-FIELD-ITEM\t");
            break;
        case Item::DECIMAL_ITEM:
            fprintf(stderr, "DECIMAL-ITEM\t");
            break;
        case Item::XPATH_NODESET:
            fprintf(stderr, "XPATH-NODESET\t");
            break;
        case Item::XPATH_NODESET_CMP:
            fprintf(stderr, "XPATH-NODESET-CMP\t");
            break;
        case Item::VIEW_FIXER_ITEM:
            fprintf(stderr, "VIEW-FIXER-ITEM\t");
            break;
        default:
            fprintf(stderr, "unknown: %d\t",  tempCond->type());
            break;
    }
    if (tempCond->str_value.ptr() != NULL )
        fprintf(stderr, "\tstr_value=<%s>", tempCond->str_value.ptr());
    if ( tempCond->name != NULL )
        fprintf(stderr, "\tname=<%s>", tempCond->name);
    fprintf(stderr, "\n");
}

Our new cond_push()-functions looks a bit different now, it contains only some lines of code. Aside some debug-code it contains one line of interest: this line calls the traverse_cond()-function of an Item. The traverse_cond()-function is given the address of a function, a NULL-constant and the constant PREFIX (more on this later).
If you compare the code in the old function cond_push() and the new function handle_Item() you will see that these look very similar. The difference is that the code in the old function starts with the condition and walks through it line by line until it reaches a NULL. The new function does not contain this part because it handles only one line of the conditions (=one Item) and then returns to the caller and the server traverses to the next condition and calls our function again.

So, enough of explanations for the moment, recompile everything, start your server and in the case of MariaDB do not forget to enable condition_pushdown again.

Let's start with the new code and our first SQL-statement, the simple on:
SQL:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234';

the output in the console:

table_name = <Test1>    <select * from Test1 where Hersteller = '1234'>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>

This looks better now so we take the second SQL-statement:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234' and HAP > 10;

the output in the console:

table_name = <Test1>    <select * from Test1 where Hersteller = '1234' and HAP > 10>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [>]    args: 2    type=[GT_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

Looks good, at least for me.

If you look at the code of cond_push() you will see a constant: Item::PREFIX. Replace it with Item::POSTFIX, recompile and do the tests again. These are the results:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234';

table_name = <Test1>    <select * from Test1 where Hersteller = '1234'>
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    

another example:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234' and HAP > 10;

table_name = <Test1>    <select * from Test1 where Hersteller = '1234' and HAP > 10>
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>
FUNC-ITEM    [>]    args: 2    type=[GT_FUNC]    
COND-ITEM    args: 0    type=[COND_AND_FUNC]    

The server delivers the WHERE-condition in the RPN- or PN-format as you wish. In the code I switched back to Item::PREFIX.

Now you can play with the code given here and test some more SQL-statements of your own. And you can modify the code given here or write your own code to get more information out of the condition. MySQL and MariaDB prepare a condition-tree for us that contains much more information than I showed you here.

But wait: what about JOINs?

The examples I showed before where simple statements as they did relate to only one table. So let's try some more complicated statements.

To begin with this we needed at least one more table:

MariaDB [TestCP]> create table Test2 ( 
    ->            Id   char(8),
    ->            PZN  char(7),
    ->            EVP  char(8),
    ->            HAP  char(8),
    ->            ArtikelBez varchar(40),
    ->            ArtikelText varchar(26),
    ->            Hersteller  char(5) )
    ->            engine=MINTEMPL
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [TestCP]> insert into Test2 select * from TestOK.ABDAOK limit 100;
Query OK, 100 rows affected (0.01 sec)
Records: 100  Duplicates: 0  Warnings: 0

MariaDB [TestCP]>

Now we can do some tests. I simplified and polished the output a bit:
SQL:

MariaDB [TestCP]> select A.*, B.* 
    -> from Test1 A, Test2 B 
    -> where A.Hersteller = '1234'
    ->   and B.Hersteller >= '12345'
    ->   and A.Id = B.Id
    -> ;

console-output:

table_name = <Test1>    
<select A.*, B.* 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and B.Hersteller >= '12345'
  and A.Id = B.Id>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>

table_name = <Test2>    
<select A.*, B.* 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and B.Hersteller >= '12345'
  and A.Id = B.Id>
FUNC-ITEM    [>=]    args: 2    type=[GE_FUNC]    
FIELD-ITEM    [TestCP] [B] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<12345>    name=<12345>

As you can see it's a JOIN on 2 tables: Test1 and Test2 (both use our engine MinTempl because this engine contains the code to produce this output). And in the console you see the name of the table followed by the full SQL-statement. Then you'll see the WHERE-part of the SQL-statement but you will see only those conditions relevant to table Test1. The same is true for the table Test2, it also gets the WHERE-condition stripped down to this table.

The function cond_push() is called twice, first for the table Test1. In this call it contains the condition for this table only (something like a WHERE-clause for this table). Then it is called again with the condition for table Test2.

So let's make our statement a bit more complex by adding some AND-conditions to the WHERE-part:
SQL:

MariaDB [TestCP]> select count(*) 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10
  and A.Id = B.Id

console-output:

table_name = <Test1>    


  
    

You for sure know an SQL-statement like

     SELECT *
     FROM table
     WHERE condition

From a storage-engine point some questions arise:


can I see this statement ?
what's in WHERE ?
can I access the WHERE-clause ?
can I use this information in my code (=storage engine) ?



The answer is YES. In this post I like to show you how to access this information. Using it in the engine will be a different thing, maybe I will show you in a later post how to do this.



This topic is named condition pushdown. Here you will find more information about this topic: MySQL 5.1 Plugin Development.



Gaining access to the information in the SQL-statement is simple: every engine is derived from a class handler
 and in this class you will find a function cond_push().

let's look into handler.h:

virtual const COND *cond_push(const COND *cond) { return cond; };
it's defined, but it does nothing.



As a summary: by handling an SQL-statement the server prepares the statement, converts it into an appropriate form and for every table referenced in the statement it calls this routine in the storage-engine. For accessing this information we have to implement this function in our storage-engine.



So let's do it. Please allow me to skip long explanations but simply show you some code. Take the code from MinTempl as a base and extend it by adding via copy and paste:

in the header-file:

const COND *cond_push(const COND *cond);
in the code-file:

/**
 * WHERE
 *
 * in case you want to know something about the WHERE-clause
 * you can use the following code as an example
 */
static void display_functype( int type)
{
    const char *strFuncType[] = { "UNKNOWN_FUNC",
            "EQ_FUNC", "EQUAL_FUNC", "NE_FUNC", "LT_FUNC",
            "LE_FUNC", "GE_FUNC", "GT_FUNC", "FT_FUNC",
            "LIKE_FUNC", "ISNULL_FUNC", "ISNOTNULL_FUNC", "COND_AND_FUNC",
            "COND_OR_FUNC", "COND_XOR_FUNC", "BETWEEN", "IN_FUNC",
            "MULT_EQUAL_FUNC", "INTERVAL_FUNC", "ISNOTNULLTEST_FUNC", "SP_EQUALS_FUNC",
            "SP_DISJOINT_FUNC", "SP_INTERSECTS_FUNC", "SP_TOUCHES_FUNC", "SP_CROSSES_FUNC",
            "SP_WITHIN_FUNC", "SP_CONTAINS_FUNC", "SP_OVERLAPS_FUNC", "SP_STARTPOINT",
            "SP_ENDPOINT", "SP_EXTERIORRING", "SP_POINTN", "SP_GEOMETRYN",
            "SP_INTERIORRINGN", "NOT_FUNC", "NOT_ALL_FUNC", "NOW_FUNC",
            "TRIG_COND_FUNC", "SUSERVAR_FUNC", "GUSERVAR_FUNC", "COLLATE_FUNC",
            "EXTRACT_FUNC", "CHAR_TYPECAST_FUNC", "FUNC_SP", "UDF_FUNC",
            "NEG_FUNC", "GSYSVAR_FUNC" };

    fprintf(stderr, "type=[%s]\t", strFuncType[type]);
}

const COND *ha_mintempl::cond_push(const COND *cond)
{
    COND * tempCond = (COND*)cond;

    if ( tempCond == NULL )
        return NULL;

    fprintf(stderr, "\ntable_name = <%s>\t<%s>\n", table_share->table_name.str, current_thd->query());
    int level = 0;
    fprintf(stderr, "\n");
    while ( tempCond->next != NULL)
    {
        fprintf(stderr, "level=%d ", level);
        switch( tempCond->type() )
        {
            case Item::FIELD_ITEM:
                fprintf(stderr, "FIELD-ITEM");
                fprintf(stderr, "\t[%s] [%s] [%s]",((Item_field*)tempCond)->db_name, ((Item_field*)tempCond)->table_name, ((Item_field*)tempCond)->field_name);
                break;
            case Item::FUNC_ITEM:
                fprintf(stderr, "FUNC-ITEM");
                fprintf(stderr, "=[%s]", ((Item_func*)tempCond)->func_name());
                fprintf(stderr, " args: %d  ", ((Item_func*)tempCond)->argument_count() );
                display_functype( (int)((Item_func*)tempCond)->functype());
                break;
            case Item::SUM_FUNC_ITEM:
                fprintf(stderr, "SUM-FUNC--ITEM");
                break;
            case Item::STRING_ITEM:
                fprintf(stderr, "STRING-ITEM");
                break;
            case Item::INT_ITEM:
                fprintf(stderr, "INT-ITEM");
                fprintf(stderr, "\tval = %lld", ((Item_int*)tempCond)->value);
                fprintf(stderr, " res=%d", ((Item_int*)tempCond)->result_type());
                break;
            case Item::REAL_ITEM:
                fprintf(stderr, "REAL-ITEM");
                break;
            case Item::NULL_ITEM:
                fprintf(stderr, "NULL-ITEM");
                break;
            case Item::VARBIN_ITEM:
                fprintf(stderr, "VAR-BIN");
                 break;
            case Item::COPY_STR_ITEM:
                fprintf(stderr, "COPY-STR-ITEM");
                break;
            case Item::FIELD_AVG_ITEM:
                fprintf(stderr, "FIELD-AVG-ITEM");
                break;
            case Item::DEFAULT_VALUE_ITEM:
                fprintf(stderr, "DEFAULT-VLAUE-ITEM");
                break;
            case Item::PROC_ITEM:
                fprintf(stderr, "PROC-ITEM");
                break;
            case Item::COND_ITEM:
                fprintf(stderr, "COND-ITEM");
                fprintf(stderr, " args: %d  ", ((Item_cond*)tempCond)->argument_count() );
                display_functype( (int)((Item_cond*)tempCond)->functype());
                break;
            case Item::REF_ITEM:
                fprintf(stderr, "REF-ITEM");
                break;
            case Item::FIELD_STD_ITEM:
                fprintf(stderr, "FIELD-STD-ITEM");
                break;
            case Item::FIELD_VARIANCE_ITEM:
                fprintf(stderr, "FIELD-VAIRANCE-ITEM");
                break;
            case Item::INSERT_VALUE_ITEM:
                fprintf(stderr, "INSERT-VALUE-ITEM");
                break;
            case Item::SUBSELECT_ITEM:
                fprintf(stderr, "SUBSELECT-ITEM");
                break;
            case Item::ROW_ITEM:
                fprintf(stderr, "ROW-ITEM");
                break;
            case Item::CACHE_ITEM:
                fprintf(stderr, "CACHE-ITEM");
                break;
            case Item::TYPE_HOLDER:
                fprintf(stderr, "TYPE-HOLDER");
                break;
            case Item::PARAM_ITEM:
                fprintf(stderr, "PARAM-ITEM");
                break;
            case Item::TRIGGER_FIELD_ITEM:
                fprintf(stderr, "TRIGGER-FIELD-ITEM");
                break;
            case Item::DECIMAL_ITEM:
                fprintf(stderr, "DECIMAL-ITEM");
                break;
            case Item::XPATH_NODESET:
                fprintf(stderr, "XPATH-NODESET");
                break;
            case Item::XPATH_NODESET_CMP:
                fprintf(stderr, "XPATH-NODESET-CMP");
                break;
            case Item::VIEW_FIXER_ITEM:
                fprintf(stderr, "VIEW-FIXER-ITEM");
                break;
            default:
                fprintf(stderr, "unknown: %d ",  tempCond->type());
                break;
        }
        if (tempCond->str_value.ptr() != NULL )
            fprintf(stderr, "\tstr_value=<%s>", tempCond->str_value.ptr());
        if ( tempCond->name != NULL )
            fprintf(stderr, "\tname=<%s>", tempCond->name);
        level++;
        tempCond=tempCond->next;
        fprintf(stderr, "\n");
    }
    fprintf(stderr, "\n");
    return cond;
}

hint: please lok atthe update-section at the end.You will find a ink to an updated-version of this code i this scetion


Then recompile everything. Now you have a storage-engine MinTempl with a meaning quite different from the one described in the posting that can be found in the archive of the blog (but that's what I used this template for: extending and playing with the code). If you prefer you can create a copy of MinTempl and give it a new name. For this posting I will call the storage-engine with the added code MinTempl.

When you start your fresh compiled server server you should check if MinTempl is there and if it is not you should install it as described in an earlier posting.




MariaDB
For MySQL everything is ready and awaiting the first test but for MariaDB you have to do something. Please look at this output which I reformatted and polished a bit:

MariaDB [(none)]> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 
    index_merge=on,
    index_merge_union=on,
    index_merge_sort_union=on,
    index_merge_intersection=on,
    index_merge_sort_intersection=off,
    engine_condition_pushdown=off,
    index_condition_pushdown=on,
    derived_merge=on,
    derived_with_keys=on,
    firstmatch=on,loosescan=on,
    materialization=on,
    in_to_exists=on,
    semijoin=on,
    partial_match_rowid_merge=on,
    partial_match_table_scan=on,
    subquery_cache=on,
    mrr=off,
    mrr_cost_based=off,
    mrr_sort_keys=off,
    outer_join_with_cache=on,
    semijoin_with_cache=on,
    join_cache_incremental=on,
    join_cache_hashed=on,
    join_cache_bka=on,
    optimize_join_buffer_size=off,
    table_elimination=on,
    extended_keys=off,
    exists_to_in=off
1 row in set (0.00 sec)



What you see is that condition pushdown is disabled by default in MariaDB so we have to enable it:

MariaDB [(none)]> set optimizer_switch='engine_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 
    ....
    engine_condition_pushdown=on,
    ....
1 row in set (0.00 sec)




continuing
Everything is ready and awaiting out first test. So start your preferred frontend, connect to the server and start with these steps: I created a database for our tests and in this database I created a table into which some data is inserted:

MariaDB [(none)]> create database TestCP;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use TestCP;
Database changed

MariaDB [TestCP]> create table Test1 (
    ->            Id   char(8),
    ->            PZN  char(7),
    ->            EVP  char(8),
    ->            HAP  char(8),
    ->            ArtikelBez varchar(40),
    ->            ArtikelText varchar(26),
    ->            Hersteller  char(5) )
    ->            engine=MINTEMPL
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [TestCP]> insert into Test1 select * from TestOK.ABDAOK limit 100;
Query OK, 100 rows affected (0.03 sec)
Records: 100  Duplicates: 0  Warnings: 0

MariaDB [TestCP]> 


So, enough of the introductory work, let's start looking into the SQL-statement. Here is a first and very simple example:

SQL:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234';

And here is the output produced by our cond_push() that you can see in the console of Eclipse:


table_name = <Test1>    <select * from Test1 where Hersteller = '1234'>
level=0 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=1 FUNC-ITEM=[multiple equal] args: 0   type=[MULT_EQUAL_FUNC]
level=2 STRING-ITEM    str_value=<1234>    name=<1234>
level=3 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
level=4 FIELD-ITEM    [TestCP] [Test1] [ArtikelText]    name=<ArtikelText>
level=5 FIELD-ITEM    [TestCP] [Test1] [ArtikelBez]    name=<ArtikelBez>
level=6 FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
level=7 FIELD-ITEM    [TestCP] [Test1] [EVP]    name=<EVP>
level=8 FIELD-ITEM    [TestCP] [Test1] [PZN]    name=<PZN>
level=9 FIELD-ITEM    [TestCP] [Test1] [Id]    name=<Id>
level=10 INT-ITEM    val = -1 res=2
level=11 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=12 STRING-ITEM    str_value=<1234>    name=<1234>
level=13 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>



Well, confusing.



But if you look a bit closer at the lines you will see:


 the name of the database: TestCP
 the name of the table: Test1
the name of the column: Hersteller
the string-constant: 1234;

and so on.



Please look at the last 3 lines of the output. There you will see the condition (I reformatted it a bit):

FUNC-ITEM    [EQ_FUNC]
STRING-ITEM    <1234>
FIELD-ITEM    [TestCP] [Test1] [Hersteller]

looks better now and this is our WHERE-condition in the form: EQ_FUNC('1234', TestCP.Test1.Hersteller)

In the form of a tree it looks like this:





Let's make our example a bit more complicated:

SQL:


MariaDB [TestCP]> select * from Test1 where Hersteller = '1234' and HAP > 10;

and here is the output of the our function cond_push() in the console:

table_name = <Test1>    <select * from Test1 where Hersteller = '1234' and HAP > 10>
level=0 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=1 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=2 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=3 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=4 FUNC-ITEM=[multiple equal] args: 0   type=[MULT_EQUAL_FUNC]
level=5 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=6 CACHE-ITEM
level=7 STRING-ITEM    str_value=<1234>    name=<1234>
level=8 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
level=9 FIELD-ITEM    [TestCP] [Test1] [ArtikelText]    name=<ArtikelText>
level=10 FIELD-ITEM    [TestCP] [Test1] [ArtikelBez]    name=<ArtikelBez>
level=11 FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
level=12 FIELD-ITEM    [TestCP] [Test1] [EVP]    name=<EVP>
level=13 FIELD-ITEM    [TestCP] [Test1] [PZN]    name=<PZN>
level=14 FIELD-ITEM    [TestCP] [Test1] [Id]    name=<Id>
level=15 INT-ITEM    val = -1 res=2
level=16 COND-ITEM args: 0   type=[COND_AND_FUNC]
level=17 FUNC-ITEM=[>] args: 2   type=[GT_FUNC]
level=18 INT-ITEM    val = 10 res=2    name=<10>
level=19 FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
level=20 FUNC-ITEM=[=] args: 2   type=[EQ_FUNC]
level=21 STRING-ITEM    str_value=<1234>    name=<1234>
level=22 FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>



When you look at the last 7 lines of this output you will see (again, I reformatted this a bit):


COND-ITEM    [COND_AND_FUNC]
FUNC-ITEM    [GT_FUNC]
INT-ITEM    10
FIELD-ITEM    [TestCP].[Test1].[HAP]
FUNC-ITEM=[=]    type=[EQ_FUNC]
STRING-ITEM    <1234>
FIELD-ITEM    [TestCP].[Test1].[Hersteller]

That looks promising: COND_AND_FUNC( GT_FUNC( 10, TestCP.Test1.HAP), EQ_FUNC( '1234'. TestCP.Test1.Hersteller) )

And in the form of a tree:





Looks like we got the information, but it's hidden under a lot of other information. Can't the server deliver better results immediately?



Looking into the code of MariaDB or MySQL you can see some functions whose names suggest that these help in wandering through the condition-tree:

in item.h:

   virtual void traverse_cond(Cond_traverser traverser,
                              void *arg, traverse_order order)
   {
     (*traverser)(this, arg);
   }

in item_func.h:

  void traverse_cond(Cond_traverser traverser,
                     void * arg, traverse_order order);
you will find the implementation of Item_func::traverse_cond() in item_func.cc.

in item_cmpfunc.h:

  void traverse_cond(Cond_traverser, void *arg, traverse_order order);
You will find the implementation of Item_cond::traverse_cond() in item_cmpfunc.cc.



So let's start, in Eclipse set breakpoints on these functions and simply repeat our tests.You will see ..... nothing, these functions are not called. The reason for this: we have to call them.




another try
As before: instead of long explanations I will show you some code:



in the top of the file ha_mintempl.cc please add these two lines:

static void display_functype( int );
static void handle_Item( const Item *item, void *arg);

and later in the file please add these lines (which replace the lines of our first try):

/**
 * WHERE
 *
 * in case you want to know something about the WHERE-clause
 * you can use the following code as an example
 */
static void display_functype( int type)
{
    const char *strFuncType[] = { "UNKNOWN_FUNC",
            "EQ_FUNC", "EQUAL_FUNC", "NE_FUNC", "LT_FUNC",
            "LE_FUNC", "GE_FUNC", "GT_FUNC", "FT_FUNC",
            "LIKE_FUNC", "ISNULL_FUNC", "ISNOTNULL_FUNC", "COND_AND_FUNC",
            "COND_OR_FUNC", "COND_XOR_FUNC", "BETWEEN", "IN_FUNC",
            "MULT_EQUAL_FUNC", "INTERVAL_FUNC", "ISNOTNULLTEST_FUNC", "SP_EQUALS_FUNC",
            "SP_DISJOINT_FUNC", "SP_INTERSECTS_FUNC", "SP_TOUCHES_FUNC", "SP_CROSSES_FUNC",
            "SP_WITHIN_FUNC", "SP_CONTAINS_FUNC", "SP_OVERLAPS_FUNC", "SP_STARTPOINT",
            "SP_ENDPOINT", "SP_EXTERIORRING", "SP_POINTN", "SP_GEOMETRYN",
            "SP_INTERIORRINGN", "NOT_FUNC", "NOT_ALL_FUNC", "NOW_FUNC",
            "TRIG_COND_FUNC", "SUSERVAR_FUNC", "GUSERVAR_FUNC", "COLLATE_FUNC",
            "EXTRACT_FUNC", "CHAR_TYPECAST_FUNC", "FUNC_SP", "UDF_FUNC",
            "NEG_FUNC", "GSYSVAR_FUNC" };

    fprintf(stderr, "type=[%s]\t", strFuncType[type]);
}

const COND * ha_mintempl::cond_push(const COND *cond)
{
  DBUG_ENTER("ha_mintempl::cond_push");

  THD *cThd = current_thd;
  // access the full query:
  fprintf(stderr, "\ntable_name = <%s>\t<%s>\n", table_share->table_name.str, cThd->query());
  
  // the query prepared by MySQL/MariaDB for this table:
  ((Item*)cond)->traverse_cond(&handle_Item, NULL, Item::PREFIX);   // Item::POSTFIX

  // future extension: tell the server that we take over all of the work
  //    DBUG_RETURN(NULL);

  DBUG_RETURN(cond);
};

static void handle_Item(const Item *item, void *arg)
{
    COND * tempCond = (COND*)item;

    if ( tempCond == NULL )
        return;

    switch( tempCond->type() )
    {
        case Item::FIELD_ITEM:
            fprintf(stderr, "FIELD-ITEM\t");
            fprintf(stderr, "[%s] [%s] [%s]",((Item_field*)tempCond)->db_name, ((Item_field*)tempCond)->table_name, ((Item_field*)tempCond)->field_name);
            break;
        case Item::FUNC_ITEM:
            fprintf(stderr, "FUNC-ITEM\t");
            fprintf(stderr, "[%s]\t", ((Item_func*)tempCond)->func_name());
            fprintf(stderr, "args: %d\t", ((Item_func*)tempCond)->argument_count() );
            display_functype( (int)((Item_func*)tempCond)->functype());
            break;
        case Item::SUM_FUNC_ITEM:
            fprintf(stderr, "SUM-FUNC-ITEM\t");
            break;
        case Item::STRING_ITEM:
            fprintf(stderr, "STRING-ITEM\t");
            break;
        case Item::INT_ITEM:
            fprintf(stderr, "INT-ITEM\t");
            fprintf(stderr, "val = %lld\t", ((Item_int*)tempCond)->value);
            fprintf(stderr, "res=%d", ((Item_int*)tempCond)->result_type());
            break;
        case Item::REAL_ITEM:
            fprintf(stderr, "REAL-ITEM\t");
            break;
        case Item::NULL_ITEM:
            fprintf(stderr, "NULL-ITEM\t");
            break;
        case Item::VARBIN_ITEM:
            fprintf(stderr, "VAR-BIN\t");
            break;
        case Item::COPY_STR_ITEM:
            fprintf(stderr, "COPY-STR-ITEM\t");
            break;
        case Item::FIELD_AVG_ITEM:
            fprintf(stderr, "FIELD-AVG-ITEM\t");
            break;
        case Item::DEFAULT_VALUE_ITEM:
            fprintf(stderr, "DEFAULT-VLAUE-ITEM\t");
            break;
        case Item::PROC_ITEM:
            fprintf(stderr, "PROC-ITEM\t");
            break;
        case Item::COND_ITEM:
            fprintf(stderr, "COND-ITEM\t");
            fprintf(stderr, "args: %d\t", ((Item_cond*)tempCond)->argument_count() );
            display_functype( (int)((Item_cond*)tempCond)->functype());
            break;
        case Item::REF_ITEM:
            fprintf(stderr, "REF-ITEM\t");
            break;
        case Item::FIELD_STD_ITEM:
            fprintf(stderr, "FIELD-STD-ITEM\t");
            break;
        case Item::FIELD_VARIANCE_ITEM:
            fprintf(stderr, "FIELD-VAIRANCE-ITEM\t");
            break;
        case Item::INSERT_VALUE_ITEM:
            fprintf(stderr, "INSERT-VALUE-ITEM\t");
            break;
        case Item::SUBSELECT_ITEM:
            fprintf(stderr, "SUBSELECT-ITEM\t");
            break;
        case Item::ROW_ITEM:
            fprintf(stderr, "ROW-ITEM\t");
            break;
        case Item::CACHE_ITEM:
            fprintf(stderr, "CACHE-ITEM\t");
            break;
        case Item::TYPE_HOLDER:
            fprintf(stderr, "TYPE-HOLDER\t");
            break;
        case Item::PARAM_ITEM:
            fprintf(stderr, "PARAM-ITEM\t");
            break;
        case Item::TRIGGER_FIELD_ITEM:
            fprintf(stderr, "TRIGGER-FIELD-ITEM\t");
            break;
        case Item::DECIMAL_ITEM:
            fprintf(stderr, "DECIMAL-ITEM\t");
            break;
        case Item::XPATH_NODESET:
            fprintf(stderr, "XPATH-NODESET\t");
            break;
        case Item::XPATH_NODESET_CMP:
            fprintf(stderr, "XPATH-NODESET-CMP\t");
            break;
        case Item::VIEW_FIXER_ITEM:
            fprintf(stderr, "VIEW-FIXER-ITEM\t");
            break;
        default:
            fprintf(stderr, "unknown: %d\t",  tempCond->type());
            break;
    }
    if (tempCond->str_value.ptr() != NULL )
        fprintf(stderr, "\tstr_value=<%s>", tempCond->str_value.ptr());
    if ( tempCond->name != NULL )
        fprintf(stderr, "\tname=<%s>", tempCond->name);
    fprintf(stderr, "\n");
}



Our new cond_push()-functions looks a bit different now, it contains only some lines of code. Aside some debug-code it contains one line of interest: this line calls the traverse_cond()-function of an Item. The traverse_cond()-function is given the address of a function, a NULL-constant and the constant PREFIX (more on this later).

If you compare the code in the old function cond_push() and the new function handle_Item() you will see that these look very similar. The difference is that the code in the old function starts with the condition and walks through it line by line until it reaches a NULL. The new function does not contain this part because it handles only one line of the conditions (=one Item) and then returns to the caller and the server traverses to the next condition and calls our function again.



So, enough of explanations for the moment, recompile everything, start your server and in the case of MariaDB do not forget to enable condition_pushdown again.



Let's start with the new code and our first SQL-statement, the simple on:

SQL:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234';
the output in the console:

table_name = <Test1>    <select * from Test1 where Hersteller = '1234'>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>



This looks better now so we take the second SQL-statement:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234' and HAP > 10;
the output in the console:

table_name = <Test1>    <select * from Test1 where Hersteller = '1234' and HAP > 10>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [>]    args: 2    type=[GT_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

Looks good, at least for me.



If you look at the code of cond_push() you will see a constant: Item::PREFIX. Replace it with Item::POSTFIX, recompile and do the tests again. These are the results:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234';
table_name = <Test1>    <select * from Test1 where Hersteller = '1234'>
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    

another example:

MariaDB [TestCP]> select * from Test1 where Hersteller = '1234' and HAP > 10;
table_name = <Test1>    <select * from Test1 where Hersteller = '1234' and HAP > 10>
FIELD-ITEM    [TestCP] [Test1] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [Test1] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>
FUNC-ITEM    [>]    args: 2    type=[GT_FUNC]    
COND-ITEM    args: 0    type=[COND_AND_FUNC]    

The server delivers the WHERE-condition in the RPN- or PN-format as you wish. In the code I switched back to Item::PREFIX.



Now you can play with the code given here and test some more SQL-statements of your own. And you can modify the code given here or write your own code to get more information out of the condition. MySQL and MariaDB prepare a condition-tree for us that contains much more information than I showed you here.



But wait: what about JOINs?



The examples I showed before where simple statements as they did relate to only one table. So let's try some more complicated statements.



To begin with this we needed at least one more table:

MariaDB [TestCP]> create table Test2 ( 
    ->            Id   char(8),
    ->            PZN  char(7),
    ->            EVP  char(8),
    ->            HAP  char(8),
    ->            ArtikelBez varchar(40),
    ->            ArtikelText varchar(26),
    ->            Hersteller  char(5) )
    ->            engine=MINTEMPL
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [TestCP]> insert into Test2 select * from TestOK.ABDAOK limit 100;
Query OK, 100 rows affected (0.01 sec)
Records: 100  Duplicates: 0  Warnings: 0

MariaDB [TestCP]>



Now we can do some tests. I simplified and polished the output a bit:

SQL:

MariaDB [TestCP]> select A.*, B.* 
    -> from Test1 A, Test2 B 
    -> where A.Hersteller = '1234'
    ->   and B.Hersteller >= '12345'
    ->   and A.Id = B.Id
    -> ;

console-output:

table_name = <Test1>    
<select A.*, B.* 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and B.Hersteller >= '12345'
  and A.Id = B.Id>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>

table_name = <Test2>    
<select A.*, B.* 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and B.Hersteller >= '12345'
  and A.Id = B.Id>
FUNC-ITEM    [>=]    args: 2    type=[GE_FUNC]    
FIELD-ITEM    [TestCP] [B] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<12345>    name=<12345>



As you can see it's a JOIN on 2 tables: Test1 and Test2 (both use our engine MinTempl because this engine contains the code to produce this output). And in the console you see the 
 name of the table followed by the full SQL-statement. Then you'll see the WHERE-part of the SQL-statement but you will see only those conditions relevant to table Test1.
 The same is true for the table Test2, it also gets the WHERE-condition stripped down to this table.



The function cond_push() is called twice, first for the table Test1. In this call it contains the condition for this table only (something like a WHERE-clause for this table). Then it is called again with the condition for table Test2.



So let's make our statement a bit more complex by adding some AND-conditions to the WHERE-part:

SQL:

MariaDB [TestCP]> select count(*) 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10
  and A.Id = B.Id

console-output:

table_name = <Test1>    
<select count(*) 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10
  and A.Id = B.Id>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

table_name = <Test2>    
<select count(*) 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10
  and A.Id = B.Id>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [>=]    args: 2    type=[GE_FUNC]    
FIELD-ITEM    [TestCP] [B] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<12345>    name=<12345>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [B] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>



Again our function is called twice and in each call it gets the WHERE-part of the SQL-statement but only the part that is relevant to this table.



So let's try a third statement using the usual form of a JOIN:

SQL:

MariaDB [TestCP]> select count(*)
from Test1 A
  JOIN Test2 B ON (A.Id = B.Id)
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10

console-output:

table_name = <Test1>    
<select count(*)
from Test1 A
  JOIN Test2 B ON (A.Id = B.Id)
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

table_name = <Test2>
<select count(*)
from Test1 A
  JOIN Test2 B ON (A.Id = B.Id)
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [>=]    args: 2    type=[GE_FUNC]    
FIELD-ITEM    [TestCP] [B] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<12345>    name=<12345>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [B] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>






and finally
You have seen how to access the WHERE-clause of the SQL-statement. Use this code and play with your own statements. Expand the code or rewrite it. No more code-examples will follow in this post.






some explanations
As I have shown the server takes the text of the SQL-statement and brings it in a form which we can access. For every table referenced in the SQL-statement it calls the function cond_push() of the appropriate storage-engine and gives to it the part of the WHERE-clause relevant to this table.



For this purpose a lot of classes are defined in MySQL and MariaDB. If you want to get an overview please look at this page.

You have to scroll around a bit to see something on this page but don't let this page and the diagrams confuse you. This looks complicated but it's not, so don't panic.



Everything starts with the class Item and the other classes are derived from this. Each class has a function type() which returns a constant that identifies the class. In the code-examples I presented above you will see a big switch-statement that uses this information to jump to the correct branch. All these branches contain an fprintf()-statement. This simply tells what type of condition is found in this position of the tree. In some cases you will see some more lines of code, because I was interested in more details. These case-statements are of the types:


FIELD_ITEM
FUNC_ITEM
COND_ITEM
INT_ITEM
STRING_ITEM

We started with an Item * but in these cases I wanted to access information defined in derived classes so castings are needed in these branches. The corresponding classes are:


Item_field
Item_func
Item_cond
Item_int
Item_string

If you look at the hierarchy-graph you will see that:



Item_field -> Item_ident -> Item
Item_func -> Item_result_field -> Item
Item_cond -> Item_bool_func -> Item_int_func -> Item_func -> Item_result_field -> Item
Item_int -> Item_num -> Item_basic_constant -> Item
Item_string -> Item_basic_constant> -> Item



In the branch representing the type FIELD_ITEM the code prints the members db_name, table_name and field_name of the class Item_ident.



The WHERE-clause contains comparisons and in the branch FUNC_ITEM I've shown the symbol representing the comparison by calling func_name() of the class Item_result_field. Next comes the the number of arguments in this comparison by calling Item_func::->argument_count(). Finally comes the type of comparison by getting a constant from Item_func::functype() and converting this into a readable form.



The class Item_cond is derived from Item_func so by printing data about this object one will use functions from the clas Item_func.



I also used integer-constants in the SQL-statements and printed them in the branch INT_ITEM by accessing the member-variable value of the class Item_int.



And I used string-constants in the SQL-statements and printed them in the branch STRING_ITEM by accessing the function str_value() of the class Item.



There are a lot more classes defined in MySQL and MariaDB and a lot of functions in these classes for dealing with these classes. I don't want to go deeper into the details here but these classes will help us to gain access to the information we are interested in.



And one last hint: the function cond_push() is given the WHERE-clause in the form of an argument of type COND *, which is defined in  handler.h as:


/* Forward declaration for condition pushdown to storage engine */
typedef class Item COND;

so COND is just another name for an Item.



This post got a bit lengthy so that's enough for today.




update
2014/05/13: If you want to see an example how this information can be used please look into this post.

2014/05/24: you will find a similar example in this post.

2017/05/10: and there's one more example: subselect: execution (4)

2017/05/10: you will find a corrected version of the code presented here in this file: WHERE.cc



<select count(*) 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10
  and A.Id = B.Id>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

table_name = <Test2>    
<select count(*) 
from Test1 A, Test2 B 
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10
  and A.Id = B.Id>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [>=]    args: 2    type=[GE_FUNC]    
FIELD-ITEM    [TestCP] [B] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<12345>    name=<12345>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [B] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

Again our function is called twice and in each call it gets the WHERE-part of the SQL-statement but only the part that is relevant to this table.

So let's try a third statement using the usual form of a JOIN:
SQL:

MariaDB [TestCP]> select count(*)
from Test1 A
  JOIN Test2 B ON (A.Id = B.Id)
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10

console-output:

table_name = <Test1>    
<select count(*)
from Test1 A
  JOIN Test2 B ON (A.Id = B.Id)
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<1234>    name=<1234>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [A] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

table_name = <Test2>
<select count(*)
from Test1 A
  JOIN Test2 B ON (A.Id = B.Id)
where A.Hersteller = '1234'
  and A.HAP = 10
  and B.Hersteller >= '12345'
  and B.HAP = 10>
COND-ITEM    args: 0    type=[COND_AND_FUNC]    
FUNC-ITEM    [>=]    args: 2    type=[GE_FUNC]    
FIELD-ITEM    [TestCP] [B] [Hersteller]    name=<Hersteller>
STRING-ITEM        str_value=<12345>    name=<12345>
FUNC-ITEM    [=]    args: 2    type=[EQ_FUNC]    
FIELD-ITEM    [TestCP] [B] [HAP]    name=<HAP>
INT-ITEM    val = 10    res=2    name=<10>

and finally

You have seen how to access the WHERE-clause of the SQL-statement. Use this code and play with your own statements. Expand the code or rewrite it. No more code-examples will follow in this post.

some explanations

As I have shown the server takes the text of the SQL-statement and brings it in a form which we can access. For every table referenced in the SQL-statement it calls the function cond_push() of the appropriate storage-engine and gives to it the part of the WHERE-clause relevant to this table.

For this purpose a lot of classes are defined in MySQL and MariaDB. If you want to get an overview please look at this page.
You have to scroll around a bit to see something on this page but don't let this page and the diagrams confuse you. This looks complicated but it's not, so don't panic.

Everything starts with the class Item and the other classes are derived from this. Each class has a function type() which returns a constant that identifies the class. In the code-examples I presented above you will see a big switch-statement that uses this information to jump to the correct branch. All these branches contain an fprintf()-statement. This simply tells what type of condition is found in this position of the tree. In some cases you will see some more lines of code, because I was interested in more details. These case-statements are of the types:

• FIELD_ITEM
• FUNC_ITEM
• COND_ITEM
• INT_ITEM
• STRING_ITEM

We started with an Item * but in these cases I wanted to access information defined in derived classes so castings are needed in these branches. The corresponding classes are:

• Item_field
• Item_func
• Item_cond
• Item_int
• Item_string

If you look at the hierarchy-graph you will see that:

• Item_field -> Item_ident -> Item
• Item_func -> Item_result_field -> Item
• Item_cond -> Item_bool_func -> Item_int_func -> Item_func -> Item_result_field -> Item
• Item_int -> Item_num -> Item_basic_constant -> Item
• Item_string -> Item_basic_constant> -> Item

In the branch representing the type FIELD_ITEM the code prints the members db_name, table_name and field_name of the class Item_ident.

The WHERE-clause contains comparisons and in the branch FUNC_ITEM I've shown the symbol representing the comparison by calling func_name() of the class Item_result_field. Next comes the the number of arguments in this comparison by calling Item_func::->argument_count(). Finally comes the type of comparison by getting a constant from Item_func::functype() and converting this into a readable form.

The class Item_cond is derived from Item_func so by printing data about this object one will use functions from the clas Item_func.

I also used integer-constants in the SQL-statements and printed them in the branch INT_ITEM by accessing the member-variable value of the class Item_int.

And I used string-constants in the SQL-statements and printed them in the branch STRING_ITEM by accessing the function str_value() of the class Item.

There are a lot more classes defined in MySQL and MariaDB and a lot of functions in these classes for dealing with these classes. I don't want to go deeper into the details here but these classes will help us to gain access to the information we are interested in.

And one last hint: the function cond_push() is given the WHERE-clause in the form of an argument of type COND *, which is defined in handler.h as:

/* Forward declaration for condition pushdown to storage engine */
typedef class Item COND;

so COND is just another name for an Item.

This post got a bit lengthy so that's enough for today.

update

2014/05/13: If you want to see an example how this information can be used please look into this post.
2014/05/24: you will find a similar example in this post.
2017/05/10: and there's one more example: subselect: execution (4)
2017/05/10: you will find a corrected version of the code presented here in this file: WHERE.cc

MAXTEMPL: a maximal template of a storage engine

I like to present another storage engine which I called MaxTemplate, a maximal template for a storage engine. You will find the source-code of MaxTemplate here.

This engine is similar to MinTemplate, and you will find the article about MinTemplate in the archive of this blog.

When you look into the source-code of MaxTemplate you will see that there is a lot more of code in this engine then in MinTemplate. Every function in the base class MyIsam is found in MinTemplate; this explains the amount of code. I will give a reason for this later.

And also we will encounter differences when compiling the code in the two environments: my code compiled unchanged on MySQL (version 5.5.8) and did require some modifications for MariaDB (10.0.4). Some functions have been removed, others are private now, some have different parameters - normal modifications when software develops. For handling the difference between MySQL and MariaDB I needed a define, which exists in MariaDB but not in MySQL (or vice versa),  so I can use this define to handle the different parts of the code. If you look into the code please look for MARIADB_BASE_VERSION.

I did some tests to demonstrate that this engine works. You will find them in the file Skripte/Tests.log in the tar.gz-file. I only included the tests for MariaDB, I did the same tests with MySQL and I hope you will trust me when I write that this code did work in this environment too.

In the end this engine is identical to MyIsam - as you can see when you do your own tests. But this engine needs more time for the same work then MyIsam because it produces a lot of output in the console of Eclipse.

what is it good for?

Do not use this engine in a production-environment! The purpose of this engine is to monitor the activities of a storage engine, e.g.

• which function is called 
• the order functions are called
• what are the parameters on entering a function 
• what are the return-values 

In Eclipse you can set a breakpoint on a function of interest to you and inspect everything. You can even modify the code of a function by duplicating this code and play with it, all without touching the original MyIsam-code.

And that's what I used it for.

Corrections

During my tests I found that I needed a define in the code:

 #define MYSQL_SERVER 1

Please excuse, but you have to add it to dummy.tar.gz and MinTemplate.tar.gz; It's already included in the source-code in MaxTemplate.tar.gz.

MINTEMPL: a minimal template of a storage-engine

some days ago I posted the code of a storage-engine: DUMMY.

This engine was, as the name suggested, a dummy-engine, it did absolutely nothing.

So let's try another engine, which is also silly but contains all the features needed for a storage engine: MINimal TEMPLate.

It contains everything that a storage-engine needs so you can use it in practice (but you shouldn't).

It is silly because it is nothing else than a MyIsam-engine with another name, because the class is derived from MyIsam as you can see in the header-file ha_mintempl.h.

You will find the code of this engine here.

Simply download this file, extract the contents to the storage-folge of MySQL or MariaDB, call cmake and recompile. The steps needed are described in detail here

MariaDB

When I tried to load the storage engine I encountered an error:

MariaDB [(none)]> install plugin mintempl soname 'ha_mintempl.so';
ERROR 1126 (HY000): Can't open shared library '/home/august/MariaDB/pgm/lib/plugin/ha_mintempl.so' (errno: 2 undefined symbol: _ZN9ha_myisam5cloneEPKcP11st_mem_root)
MariaDB [(none)]> 

In this case please look in the folder storage/MinTemplate/CMakeFiles/mintempl.dir into the file link.txt.
In this file you have to add the library libmyisam.a so that the sources will be linked using this library. So I added this text to the contents of this file at the end of the line: -L../myisam/ -lmyisam

When I deleted the file ha_mintempl.so and recompiled everything it worked: I could install this little plugin in MariaDB without an error.

a warning: the file link.txt is generated by cmake. The next time cmake will run it can overwrite this file so you can loose these modifications.

You can find a more detailed explanation of the error-message on the MariaDB-mailing list here.

MySQL

No modifications were needed fpr MySQL.

Tests

I did some tests with this engine to check that it works. You will find the results in the sources in MinTemplate/Skripte/Tests.sql.

What is it good for?

As written before it does not look like a good idea to create another engine with the functionality of an existing one (and giving it a name of my own), but this approach allowed me to play with the code without implementing a full engine or modifying existing code. In a later post I will use it, implement some functions by adding the code of these functions by myself and still have a full engine (with proven code).

differences

If you compare the code of thi engine with the DUMMY-engine presented earlier you will see that this engine needs less code. The reason for this is that it is derived from an existing engine which already contained the code we needed to implement in the DUMMY-engine.