1--TEST--
2API vs. SQL LAST_INSERT_ID()
3--EXTENSIONS--
4mysqli
5--SKIPIF--
6<?php
7  require_once('skipifconnectfailure.inc');
8?>
9--FILE--
10<?php
11    /*
12    CAUTION: the insert_id() API call is not supposed to return
13    the same value as a call to the LAST_INSERT_ID() SQL function.
14    It is not necessarily a bug if API and SQL function return different
15    values. Check the MySQL C API reference manual for details.
16    */
17    require_once("connect.inc");
18
19    function get_sql_id($link) {
20        if (!($res = $link->query("SELECT LAST_INSERT_ID() AS _id"))) {
21            printf("[003] [%d] %s\n", $link->errno, $link->error);
22            return NULL;
23        }
24        $row = $res->fetch_assoc();
25        $res->close();
26
27        return $row['_id'];
28    }
29
30    if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
31        printf("[001] Cannot connect to the server using host=%s, user=%s, passwd=***, dbname=%s, port=%s, socket=%s\n",
32            $host, $user, $db, $port, $socket);
33
34    if (!$link->query("DROP TABLE IF EXISTS test") ||
35        !$link->query("CREATE TABLE test (id INT auto_increment, label varchar(10) not null, PRIMARY KEY (id)) ENGINE=MyISAM") ||
36        !$link->query("INSERT INTO test (id, label) VALUES (null, 'a')")) {
37        printf("[002] [%d] %s\n", $link->errno, $link->error);
38    }
39
40    $api_id = $link->insert_id;
41    $sql_id = get_sql_id($link);
42    printf("API: %d, SQL: %d\n", $api_id, $sql_id);
43
44    if ($api_id < 1)
45        printf("[004] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error)	;
46    if ($api_id != $sql_id)
47        printf("[005] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
48
49    // Not an INSERT, API value must become 0
50    if (!($res = $link->query("SELECT 1 FROM DUAL")))
51        printf("[006] [%d] %s\n", $link->errno, $link->error);
52    else
53        $res->close();
54
55    $api_id = $link->insert_id;
56    $new_sql_id = get_sql_id($link);
57    if (0 !== $api_id) {
58        printf("[007] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n",
59          $api_id, $new_sql_id);
60    }
61    if ($new_sql_id != $sql_id) {
62        printf("[008] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
63    }
64
65    // Insert fails, LAST_INSERT_ID shall not change, API shall return 0
66    if ($link->query("INSERT INTO test (id, label) VALUES (null, null)")) {
67        printf("[009] The INSERT did not fail as planned, [%d] %s\n", $link->errno, $link->error);
68    }
69    $api_id = $link->insert_id;
70    $new_sql_id = get_sql_id($link);
71
72    if (0 !== $api_id) {
73        printf("[010] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n",
74          $api_id, $new_sql_id);
75    }
76    if ($new_sql_id != $sql_id) {
77        printf("[011] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
78    }
79
80    // Sequence counter pattern...
81    if (!$link->query("UPDATE test SET id=LAST_INSERT_ID(id+1)"))
82      printf("[012] [%d] %s\n", $link->errno, $link->error);
83
84    $api_id = $link->insert_id;
85    $new_sql_id = get_sql_id($link);
86    if ($api_id < 1)
87        printf("[013] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error)	;
88    if ($api_id != $new_sql_id)
89        printf("[014] SQL id %d should be equal to API id %d\n", $new_sql_id, $api_id);
90    if ($sql_id == $new_sql_id)
91        printf("[015] SQL id %d should have had changed, got %d\n", $sql_id, $new_sql_id);
92
93    $sql_id = $new_sql_id;
94
95    // Not an INSERT (after UPDATE), API value must become 0
96    if (!$link->query("SET @myvar=1"))
97        printf("[016] [%d] %s\n", $link->errno, $link->error);
98
99    $api_id = $link->insert_id;
100    $new_sql_id = get_sql_id($link);
101    if (0 !== $api_id) {
102        printf("[017] API id should have been reset to 0 because previous query was SET, got API %d, SQL %d\n",
103          $api_id, $new_sql_id);
104    }
105    if ($new_sql_id != $sql_id) {
106        printf("[018] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
107    }
108
109    if (!$link->query("INSERT INTO test(id, label) VALUES (LAST_INSERT_ID(id + 1), 'b')"))
110        printf("[019] [%d] %s\n", $link->errno, $link->error);
111
112    $api_id = $link->insert_id;
113    $sql_id = get_sql_id($link);
114    if ($api_id != $sql_id)
115        printf("[020] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
116
117    if (!$link->query("INSERT INTO test(label) VALUES ('c')"))
118        printf("[021] [%d] %s\n", $link->errno, $link->error);
119
120    $api_id = $link->insert_id;
121    $sql_id = get_sql_id($link);
122    if ($api_id != $sql_id)
123        printf("[022] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
124
125    if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC")))
126        printf("[023] [%d] %s\n", $link->errno, $link->error);
127
128    printf("Dumping table contents before INSERT...SELECT experiments...\n");
129    while ($row = $res->fetch_assoc()) {
130        printf("id = %d, label = '%s'\n", $row['id'], $row['label']);
131    }
132    $res->close();
133
134    if (!$link->query("INSERT INTO test(label) SELECT CONCAT(label, id) FROM test ORDER BY id ASC"))
135      printf("[024] [%d] %s\n", $link->errno, $link->error);
136
137    $api_id = $link->insert_id;
138    $sql_id = get_sql_id($link);
139    if ($api_id != $sql_id)
140        printf("[025] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
141
142    if ($link->query("INSERT INTO test(id, label) SELECT id, CONCAT(label, id) FROM test ORDER BY id ASC"))
143      printf("[026] INSERT should have failed because of duplicate PK value, [%d] %s\n", $link->errno, $link->error);
144
145    $api_id = $link->insert_id;
146    $new_sql_id = get_sql_id($link);
147    if (0 !== $api_id) {
148        printf("[027] API id should have been reset to 0 because previous query failed, got API %d, SQL %d\n",
149          $api_id, $new_sql_id);
150    }
151    if ($new_sql_id != $sql_id) {
152        printf("[028] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
153    }
154
155    /* API insert id will be 101 because of UPDATE, SQL unchanged */
156    if (!$link->query(sprintf("INSERT INTO test(id, label) VALUES (%d, 'z') ON DUPLICATE KEY UPDATE id = 101", $sql_id)	))
157      printf("[029] [%d] %s\n", $link->errno, $link->error);
158
159    $api_id = $link->insert_id;
160    $new_sql_id = get_sql_id($link);
161    if ($api_id != 101)
162        printf("[030] API id should be %d got %d\n", $sql_id, $api_id);
163    if ($new_sql_id != $sql_id) {
164        printf("[031] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
165    }
166
167    if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC")))
168        printf("[032] [%d] %s\n", $link->errno, $link->error);
169
170    printf("Dumping table contents after INSERT...SELECT...\n");
171    while ($row = $res->fetch_assoc()) {
172        printf("id = %d, label = '%s'\n", $row['id'], $row['label']);
173    }
174    $res->close();
175
176    print "done!";
177?>
178--CLEAN--
179<?php
180    require_once("clean_table.inc");
181?>
182--EXPECTF--
183API: %d, SQL: %d
184Dumping table contents before INSERT...SELECT experiments...
185id = %d, label = 'b'
186id = %d, label = 'a'
187id = %d, label = 'c'
188Dumping table contents after INSERT...SELECT...
189id = %d, label = 'b'
190id = %d, label = 'a'
191id = %d, label = 'c'
192id = %d, label = 'a%d'
193id = %d, label = 'c%d'
194id = 101, label = 'b%d'
195done!
196