MySQL Bugs: #42376: Query with SQL variables behaves different on DBI than on CLI

Bug #42376	Query with SQL variables behaves different on DBI than on CLI
Submitted:	27 Jan 2009 14:51	Modified:	30 Jan 2009 9:36
Reporter:	Werner Schram	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:	4.010	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	variables DBI

Description:
If I execute the following query using DBI, it behaves different then when I use it from the CLI:

INSERT INTO t1 (f1) SELECT @x:=2 FROM DUAL WHERE NOT EXISTS ( SELECT * FROM t1 WHERE f1=@x)

Table t1 is created as follows:

CREATE TABLE t1 ( f1 INTEGER );

After running the query more than once from the CLI there will be one row in the database (as expected). If the query is run more than once from the DBI interface, there will be more than one row in the database. 

The entries in the binary query give some pointers to the cause of the problem. These are the entries for DBI:

# at 151821
#090127 14:15:27 server id 1  end_log_pos 151986        Query   thread_id=3778  exec_time=0     error_code=0
SET TIMESTAMP=1233062127/*!*/;
INSERT INTO t1 (f1) SELECT @x:='2' FROM DUAL WHERE NOT EXISTS ( SELECT * FROM t1 WHERE f1=@x)/*!*/;

These are the entries for the CLI:

# at 151986
#090127 14:15:33 server id 1  end_log_pos 152028        User_var
SET @`x`:=2/*!*/;
# at 152028
#090127 14:15:33 server id 1  end_log_pos 152191        Query   thread_id=3773  exec_time=0     error_code=0
SET TIMESTAMP=1233062133/*!*/;
INSERT INTO t1 (f1) SELECT @x:=2 FROM DUAL WHERE NOT EXISTS ( SELECT * FROM t1 WHERE f1=@x)/*!*/;
DELIMITER ;

The 'SET @x:=2' command doesn't seem to be included in the DBI version. Is this intended behaviour or a bug?

How to repeat:
From the CLI:

CREATE TABLE t1 ( f1 INTEGER );
INSERT INTO t1 (f1) SELECT @x:=2 FROM DUAL WHERE NOT EXISTS ( SELECT * FROM t1 WHERE f1=@x)

From DBI see attachment

script with insert command using DBI

Attachment: test_insert.pl (application/x-perl, text), 378 bytes.

Thank you for the report.

I can not repeat described behavior with DBD::mysql 4.010 Please upgrade and try in your environment, so we can be sure this was fixed.

I have upgraded two systems, but the behaviour stays the same. These are the versions I am testing with:

> perl -MDBI -e 'DBI->installed_versions'
  Perl            : 5.008008    (i386-freebsd-64int)
  OS              : freebsd     (6.2-release-p1)
  DBI             : 1.607
  DBD::mysql      : 4.010
  DBD::Sponge     : 12.010002
  DBD::Proxy      : install_driver(Proxy) failed: Can't locate RPC/PlClient.pm in @INC
  DBD::Gofer      : 0.011565
  DBD::File       : 0.35
  DBD::ExampleP   : 12.010007
  DBD::DBM        : 0.03

> perl -MDBI -e 'DBI->installed_versions'
  Perl            : 5.008008    (x86_64-linux-thread-multi)
  OS              : linux       (2.6.16.49-xen-domu)
  DBI             : 1.607
  DBD::mysql      : 4.010
  DBD::Sponge     : 12.010002
  DBD::SQLite     : 1.14
  DBD::Proxy      : 0.2004
  DBD::Gofer      : 0.011565
  DBD::File       : 0.35
  DBD::ExampleP   : 12.010007
  DBD::DBM        : 0.03

The FreeBSD system has mysql-5.1.30 the linux system has mysql-5.0.70. 

On both systems the behaviour is the same: If I run the query multiple times from the CLI, only 1 row shows up. If I run the script multiple times multiple rows show up.

Thank you for the feedback.

In the Perl code provided I see single call of $distinct_query_handle->execute($f1). Please provide full example with more than 1 call.

Hmm, I see why you couldn't duplicate the behaviour. If you run the query twice from the same DBI connection, the second query does behave correctly. I modified the script to run the query twice and reset the connection inbetween. On my system, this results in two records in the database, where only one is expected.

updated test script

Attachment: test_insert.pl (application/x-perl, text), 482 bytes.

Thank you for the feedback.

You issue query: INSERT INTO t1 (f1) SELECT @x:=? FROM DUAL WHERE NOT EXISTS ( SELECT * FROM t1 WHERE f1=@x) where you assign value to the @x in the outer query while search by condition evaluated in the subquery. Currently MySQL evaluates subquery before outer query, so when you reconnect @x=NULL, but not '2' as you expect. So behavior is expected. Try connect/reconnect from mysql command line client and you get same result. See also bug #32074 for additional explanations.