Uploaded image for project: 'CUBRID APIs'
  1. CUBRID APIs
  2. APIS-586

[PHP] cubrid_bind() doesn't work properly when insert bit data

    Details

    • Type: Bug
    • Status: Reopened
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: PHP 9.1.0.0003
    • Fix Version/s: PHP 9.1.0 0004
    • Component/s: PHP Driver
    • Labels:
    • Environment:
      • OS: Linux 64bit
      • CUBRID Server: CUBRID 9.1 (9.1.0.0212) (64bit release build for linux_gnu) (Mar 14 2013 00:16:33)
      • PHP Version: PHP 5.5.0, PHP 5.4.15
      • CUBRID-PHP: PHP 9.1.0.0003

      Description

      • Brief: cubrid_bind() doesn't work correctly if insert bit data
      • Test Code: {code}
        <?php
        $conn = cubrid_connect("localhost", 33367, "demodb", "dba");

        // Test Point 1: test insert bit value by cubrid_execute
        cubrid_execute($conn, 'DROP TABLE IF EXISTS bit1_tb');
        cubrid_execute($conn,"create table bit1_tb(a1 bit(8))");
        cubrid_execute($conn,"insert into bit1_tb values(B'1010')");

        printf("#####select from bit1_tb #####\n");
        $req1=cubrid_execute($conn,"select * from bit1_tb");
        if ($req1) {
        $res=cubrid_fetch($req1);
        print_r($res);
        cubrid_close_prepare($req1);
        }

        // Test Point 2: test insert bit value by cubrid_bind
        cubrid_execute($conn, 'DROP TABLE IF EXISTS bit2_tb');
        cubrid_execute($conn,"create table bit2_tb(a2 bit(8))");

        printf("\n\n#####select from bit2_tb #####\n");
        $req2 = cubrid_prepare($conn, 'INSERT INTO bit2_tb VALUES');
        if(!$tmp=cubrid_bind($req2, 1,"B'1010'",'bit')){
        printf("[%d] %s\n", cubrid_errno($conn), cubrid_error($conn));
        }
        cubrid_execute($req2);
        $req2 = cubrid_execute($conn, "SELECT * FROM bit2_tb");
        $result = cubrid_fetch_assoc($req2);
        var_dump($result);
        cubrid_close_prepare($req2);

        ?>{code}
      • Result
        • Expected output: #####select from bit1_tb ##### Array ( [0] => A0 [a1] => A0 ) #####select from bit2_tb ##### array(1) { ["a2"]=> string(2) "A0" }
        • Actual output: #####select from bit1_tb ##### Array ( [0] => A0 [a1] => A0 ) #####select from bit2_tb ##### array(1) { ["a2"]=> string(2) "42" }
      • Summary: when we insert a bit value to the table, cubrid_execute() returns a correct result but cubrid_bind() doesn't. It may be caused by CCI problem, if so, we may need to remove "BIT" from supported data type in cubrid_bind() manual http://www.php.net/manual/en/function.cubrid-bind.php

        Activity

        Hide
        cn15800 谢韦华[Bert] added a comment -

        a cci bug:
        insert into bit1_tb values(B'00001010') ----> data in cubrid: 0x0a right
        insert into bit1_tb values(B'10100000') ----> data in cubrid: 0xa0 right
        insert into bit1_tb values(B'1010') ----> data in cubrid: 0xa0 wrong
        1010 is 00001010,not 10100000.

        Show
        cn15800 谢韦华[Bert] added a comment - a cci bug: insert into bit1_tb values(B'00001010') ----> data in cubrid: 0x0a right insert into bit1_tb values(B'10100000') ----> data in cubrid: 0xa0 right insert into bit1_tb values(B'1010') ----> data in cubrid: 0xa0 wrong 1010 is 00001010,not 10100000.
        Hide
        cn15800 谢韦华[Bert] added a comment -

        I have modify the issue,but there is a bug of bit in cci, so i modify the test case:

        {code}
        <?php
        $conn = cubrid_connect("localhost", 33000, "demodb", "dba");

        // Test Point 1: test insert bit value by cubrid_execute
        cubrid_execute($conn, 'DROP TABLE IF EXISTS bit1_tb');
        cubrid_execute($conn,"create table bit1_tb(a1 bit(8))");
        cubrid_execute($conn,"insert into bit1_tb values(B'00001010')");

        printf("#####select from bit1_tb #####\n");
        $req1=cubrid_execute($conn,"select * from bit1_tb");
        if ($req1) {
        $res=cubrid_fetch($req1);
        print_r($res);
        cubrid_close_prepare($req1);
        }

        // Test Point 2: test insert bit value by cubrid_bind
        cubrid_execute($conn, 'DROP TABLE IF EXISTS bit2_tb');
        cubrid_execute($conn,"create table bit2_tb(a2 bit(8))");

        printf("\n\n#####select from bit2_tb #####\n");
        $req2 = cubrid_prepare($conn, 'INSERT INTO bit2_tb VALUES');
        if(!$tmp=cubrid_bind($req2, 1,'1010','bit')){
        printf("[%d] %s\n", cubrid_errno($conn), cubrid_error($conn));
        }
        cubrid_execute($req2);
        $req2 = cubrid_execute($conn, "SELECT * FROM bit2_tb");
        $result = cubrid_fetch_assoc($req2);
        var_dump($result);
        cubrid_close_prepare($req2);

        ?>{code}
        Show
        cn15800 谢韦华[Bert] added a comment - I have modify the issue,but there is a bug of bit in cci, so i modify the test case: {code} <?php $conn = cubrid_connect("localhost", 33000, "demodb", "dba"); // Test Point 1: test insert bit value by cubrid_execute cubrid_execute($conn, 'DROP TABLE IF EXISTS bit1_tb'); cubrid_execute($conn,"create table bit1_tb(a1 bit(8))"); cubrid_execute($conn,"insert into bit1_tb values(B'00001010')"); printf("#####select from bit1_tb #####\n"); $req1=cubrid_execute($conn,"select * from bit1_tb"); if ($req1) { $res=cubrid_fetch($req1); print_r($res); cubrid_close_prepare($req1); } // Test Point 2: test insert bit value by cubrid_bind cubrid_execute($conn, 'DROP TABLE IF EXISTS bit2_tb'); cubrid_execute($conn,"create table bit2_tb(a2 bit(8))"); printf("\n\n#####select from bit2_tb #####\n"); $req2 = cubrid_prepare($conn, 'INSERT INTO bit2_tb VALUES '); if(!$tmp=cubrid_bind($req2, 1,'1010','bit')){ printf(" [%d] %s\n", cubrid_errno($conn), cubrid_error($conn)); } cubrid_execute($req2); $req2 = cubrid_execute($conn, "SELECT * FROM bit2_tb"); $result = cubrid_fetch_assoc($req2); var_dump($result); cubrid_close_prepare($req2); ?>{code}
        Hide
        ryin005 Ray Yin added a comment - - edited

        According to the current Bit introduction on the document http://www.cubrid.org/manual/91/en/sql/datatype.html#bit-n, we can see:
        "The bit string is filled with 4-bit values from the left side. If a bit string smaller than n is stored, the remainder of the string is filled with 0s"
        which means CUBRID always shift data to the left side if the data length is smaller than n.

        For example in csql, we insert a bit string B'1010' to a bit(8) column, CUBRID will treat the data as B'10100000' (i.e. shift to left)

        csql> create table bit_test(b bit(8));
        csql> insert into bit_test values(B'1010');
        
        1 row affected.
        SQL statement execution time:     0.000351 sec
        
        csql> select * from bit_test;
        
        === <Result of SELECT Command in Line 1> ===
        
          b                   
        ======================
          X'a0'               
        
        
        1 row selected.
        SQL statement execution time:     0.005144 sec
        

        In PHP 9.1.0.0004, cubrid_bind() will shift the data to right if its length is shorter than n, (i.e '1010' => '00001010')

        In order to keep the consistency with CUBRID, I suggest to update cubrid_bind() function to make sure that the data is filled with values from the left side

        Reopen the issue

        Show
        ryin005 Ray Yin added a comment - - edited According to the current Bit introduction on the document http://www.cubrid.org/manual/91/en/sql/datatype.html#bit-n , we can see: "The bit string is filled with 4-bit values from the left side. If a bit string smaller than n is stored, the remainder of the string is filled with 0s" which means CUBRID always shift data to the left side if the data length is smaller than n. For example in csql, we insert a bit string B'1010' to a bit(8) column, CUBRID will treat the data as B'10100000' (i.e. shift to left) csql> create table bit_test(b bit(8)); csql> insert into bit_test values(B'1010'); 1 row affected. SQL statement execution time: 0.000351 sec csql> select * from bit_test; === <Result of SELECT Command in Line 1> === b ====================== X'a0' 1 row selected. SQL statement execution time: 0.005144 sec In PHP 9.1.0.0004, cubrid_bind() will shift the data to right if its length is shorter than n, (i.e '1010' => '00001010') In order to keep the consistency with CUBRID, I suggest to update cubrid_bind() function to make sure that the data is filled with values from the left side Reopen the issue
        Hide
        cn15800 谢韦华[Bert] added a comment - - edited

        This is cubrid's issue.

        1)sql
        insert into bit1_tb values(B'1010');
        The value in cubrid 0xa0.

        2)cci function
        Because cci and cubrid can not support convert string to bit,so we need to insert data as T_CCI_BIT.

        When data is 1010 (Binary).

        T_CCI_BIT.len=1
        T_CCI_BIT.data=1010 (Binary)

        Cubrid should store the data as 1010 0000, not 0000 1010.

        If driver modify the issue:

        1 get the data length in cubrid, impact on performance
        2 If data length is very big,for example 1024, the data is:
        T_CCI_BIT.len=1024
        T_CCI_BIT.data=1000 000................0000

        Users only modified 1 bit!!!

        Driver only need to sent:

        T_CCI_BIT.len=1
        T_CCI_BIT.data=1

        Show
        cn15800 谢韦华[Bert] added a comment - - edited This is cubrid's issue. 1)sql insert into bit1_tb values(B'1010'); The value in cubrid 0xa0. 2)cci function Because cci and cubrid can not support convert string to bit,so we need to insert data as T_CCI_BIT. When data is 1010 (Binary). T_CCI_BIT.len=1 T_CCI_BIT.data=1010 (Binary) Cubrid should store the data as 1010 0000, not 0000 1010. If driver modify the issue: 1 get the data length in cubrid, impact on performance 2 If data length is very big,for example 1024, the data is: T_CCI_BIT.len=1024 T_CCI_BIT.data=1000 000................0000 Users only modified 1 bit!!! Driver only need to sent: T_CCI_BIT.len=1 T_CCI_BIT.data=1
        Hide
        cn15800 谢韦华[Bert] added a comment - - edited

        If you think this is driver problem, please commit new issue to cci. Cci need to offer a new function:

        cci_prepare(con,sql,0,err)
        cci_get_col_property(req,index) // This function has two parameter:req and index, cci need to parase sql statement to get table's name and col's name, then query
        // col property from cubrid.
        // example: table bit_test(b bit(8)); in the example,we can get b's max length from the function.
        cci_bind(...)

        Now, all driver have the same problem, even if we use cci to insert bit data to cubrid, we need to get data's max length,too.

        Show
        cn15800 谢韦华[Bert] added a comment - - edited If you think this is driver problem, please commit new issue to cci. Cci need to offer a new function: cci_prepare(con,sql,0,err) cci_get_col_property(req,index) // This function has two parameter:req and index, cci need to parase sql statement to get table's name and col's name, then query // col property from cubrid. // example: table bit_test(b bit(8)); in the example,we can get b's max length from the function. cci_bind(...) Now, all driver have the same problem, even if we use cci to insert bit data to cubrid, we need to get data's max length,too.
        Hide
        cn15800 谢韦华[Bert] added a comment - - edited

        Notice:
        //table bit_test(b bit(8));
        cci_prepare(con, "INSERT INTO bit_test(b) VALUES",0,err)
        cci_get_col_property(req,index) // The function need to return b's property, not all col's property.

        //table bit_test(a bit(8), b bit(16));
        cci_prepare(con, "INSERT INTO bit_test(a,b) VALUES(?,?)",0,err)
        cci_get_col_property(req,1)
        cci_bind(1,..) // Driver need to know a's max legth
        cci_get_col_property(req,2)
        cci_bind(2,..) // Driver need to know b's max legth

        Show
        cn15800 谢韦华[Bert] added a comment - - edited Notice: //table bit_test(b bit(8)); cci_prepare(con, "INSERT INTO bit_test(b) VALUES ",0,err) cci_get_col_property(req,index) // The function need to return b's property, not all col's property. //table bit_test(a bit(8), b bit(16)); cci_prepare(con, "INSERT INTO bit_test(a,b) VALUES(?,?)",0,err) cci_get_col_property(req,1) cci_bind(1,..) // Driver need to know a's max legth cci_get_col_property(req,2) cci_bind(2,..) // Driver need to know b's max legth
        Hide
        ryin005 Ray Yin added a comment -

        This kind of issues include APIS-587 are caused by that cubrid BIT type make users confused because it acts very differently(left shifting rule) with other DBMS(right shifting rule).

        Actually we may have two ways to solve the problem:
        1) Modify the CUBRID bit data type strategy, i.e. change the left shifting rule to the right shifting rule, which is just like how mysql does.
        2) Modify the driver to add a sql parser to get schema of the table, and then implement the left shift rule to the data. This modification will be time-cost and should be done by CCI, since the problem is also existed in other drivers. Also, this modification may cause other performance issue since we always need to insert a full length data even though the data is quite small.

        Engine team however is planning to support a new BIT type, maybe BIT2 type which exactly behaves as we expect in later versions.

        So currently we can wait until we have a correct BIT2 type

        Show
        ryin005 Ray Yin added a comment - This kind of issues include APIS-587 are caused by that cubrid BIT type make users confused because it acts very differently(left shifting rule) with other DBMS(right shifting rule). Actually we may have two ways to solve the problem: 1) Modify the CUBRID bit data type strategy, i.e. change the left shifting rule to the right shifting rule, which is just like how mysql does. 2) Modify the driver to add a sql parser to get schema of the table, and then implement the left shift rule to the data. This modification will be time-cost and should be done by CCI, since the problem is also existed in other drivers. Also, this modification may cause other performance issue since we always need to insert a full length data even though the data is quite small. Engine team however is planning to support a new BIT type, maybe BIT2 type which exactly behaves as we expect in later versions. So currently we can wait until we have a correct BIT2 type

          People

          • Assignee:
            cn15800 谢韦华[Bert]
            Reporter:
            ryin005 Ray Yin
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: