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