1--TEST--
2Playing with SELECT FORMAT(...) AS _format - see also bugs.php.net/42378
3--SKIPIF--
4<?php
5require_once('skipif.inc');
6require_once('skipifemb.inc');
7require_once('skipifconnectfailure.inc');
8?>
9--INI--
10memory_limit=83886080
11--FILE--
12<?php
13	require_once("connect.inc");
14
15	function create_table($link, $column, $min, $max, $engine, $offset) {
16
17		if (!mysqli_query($link, 'DROP TABLE IF EXISTS test')) {
18			printf("[%03d] Cannot drop table test, [%d] %s\n",
19				$offset,
20				mysqli_errno($link), mysqli_error($link));
21			return array();
22		}
23
24		$sql = sprintf("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, col1 %s) ENGINE=%s",
25			$column, $engine);
26		if (!mysqli_query($link, $sql)) {
27			printf("[%03d] Cannot create table test, [%d] %s\n",
28				$offset + 1,
29				mysqli_errno($link), mysqli_error($link));
30			return array();
31		}
32
33		$values = array();
34		for ($i = 1; $i <= 100; $i++) {
35			$col1 = mt_rand($min, $max);
36			$values[$i] = $col1;
37			$sql = sprintf("INSERT INTO test(id, col1) VALUES (%d, %f)",
38				$i, $col1);
39			if (!mysqli_query($link, $sql)) {
40				printf("[%03d] Cannot insert data, [%d] %s\n",
41					$offset + 2,
42					mysqli_errno($link), mysqli_error($link));
43				return array();
44			}
45		}
46
47		return $values;
48	}
49
50	function test_format($link, $format, $from, $order_by, $expected, $offset) {
51
52		if (!$stmt = mysqli_stmt_init($link)) {
53			printf("[%03d] Cannot create PS, [%d] %s\n",
54				$offset,
55				mysqli_errno($link), mysqli_error($link));
56			return false;
57		}
58
59		if ($order_by)
60			$sql = sprintf('SELECT %s AS _format FROM %s ORDER BY %s', $format, $from, $order_by);
61		else
62			$sql = sprintf('SELECT %s AS _format FROM %s', $format, $from);
63
64		if (!mysqli_stmt_prepare($stmt, $sql)) {
65			printf("[%03d] Cannot prepare PS, [%d] %s\n",
66				$offset + 1,
67				mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
68			return false;
69		}
70
71		if (!mysqli_stmt_execute($stmt)) {
72			printf("[%03d] Cannot execute PS, [%d] %s\n",
73				$offset + 2,
74				mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
75			return false;
76		}
77
78		if (!mysqli_stmt_store_result($stmt)) {
79			printf("[%03d] Cannot store result set, [%d] %s\n",
80				$offset + 3,
81				mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
82			return false;
83		}
84
85		if (!is_array($expected)) {
86
87			$result = null;
88			if (!mysqli_stmt_bind_result($stmt, $result)) {
89				printf("[%03d] Cannot bind result, [%d] %s\n",
90					$offset + 4,
91					mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
92				return false;
93			}
94
95			if (!mysqli_stmt_fetch($stmt)) {
96				printf("[%03d] Cannot fetch result,, [%d] %s\n",
97					$offset + 5,
98					mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
99				return false;
100			}
101
102			if ($result !== $expected) {
103				printf("[%03d] Expecting %s/%s got %s/%s with %s - %s.\n",
104					$offset + 6,
105					gettype($expected), $expected,
106					gettype($result), $result,
107					$format, $sql);
108			}
109
110		} else {
111
112			$order_by_col = $result = null;
113			if (!mysqli_stmt_bind_result($stmt, $order_by_col, $result)) {
114				printf("[%03d] Cannot bind result, [%d] %s\n",
115					$offset + 7,
116					mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
117				return false;
118			}
119
120			reset($expected);
121			while ((list($k, $v) = each($expected)) && mysqli_stmt_fetch($stmt)) {
122				if ($result !== $v) {
123					printf("[%03d] Row %d - expecting %s/%s got %s/%s [%s] with %s - %s.\n",
124						$offset + 8,
125						$k,
126						gettype($v), $v,
127						gettype($result), $result,
128						$order_by_col,
129						$format, $sql);
130				}
131			}
132
133		}
134
135		mysqli_stmt_free_result($stmt);
136		mysqli_stmt_close($stmt);
137
138		return true;
139	}
140
141	if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
142		printf("[001] Cannot connect - [%d] %s\n",
143			mysqli_connect_errno(),
144			mysqli_connect_error());
145
146	/* select from dual - pseudo table */
147	test_format($link, 'FORMAT(1.01, 0)', 'DUAL', null, '1', 10);
148	test_format($link, 'FORMAT(1.23, 1)', 'DUAL', null, '1.2', 20);
149	test_format($link, 'FORMAT(1.23, 2)', 'DUAL', null, '1.23', 30);
150	test_format($link, 'FORMAT(1234.567, 3)', 'DUAL', null, '1,234.567', 40);
151	/* no typo! */
152	test_format($link, 'FORMAT(1234.567, 4)', 'DUAL', null, '1,234.5670', 50);
153
154	mysqli_close($link);
155	require_once('table.inc');
156
157	/* select from existing table */
158	test_format($link, 'FORMAT(id, 0)', 'test', null, '1', 60);
159	test_format($link, 'FORMAT(id + 0.1, 1)', 'test', null, '1.1', 70);
160	test_format($link, 'FORMAT(id + 0.01, 2)', 'test', null, '1.01', 80);
161
162	/* create new table and select from it */
163	$expected = create_table($link, 'FLOAT', -10000, 10000, $engine, 90);
164	foreach ($expected as $k => $v)
165		$expected[$k] = number_format(round($v), 0, '.', ',');
166	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 100);
167
168	$expected = create_table($link, 'FLOAT UNSIGNED', 0, 10000, $engine, 110);
169	foreach ($expected as $k => $v)
170		$expected[$k] = number_format(round($v), 0, '.', ',');
171	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 120);
172
173	$expected = create_table($link, 'TINYINT', -128, 127, $engine, 130);
174	foreach ($expected as $k => $v)
175		$expected[$k] = number_format(round($v), 0, '.', ',');
176	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 140);
177
178	$expected = create_table($link, 'SMALLINT UNSIGNED', 0, 65535, $engine, 150);
179	foreach ($expected as $k => $v)
180		$expected[$k] = number_format(round($v), 0, '.', ',');
181	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 160);
182
183	$expected = create_table($link, 'MEDIUMINT', 0, 8388607, $engine, 170);
184	foreach ($expected as $k => $v)
185		$expected[$k] = number_format(round($v), 0, '.', ',');
186	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 180);
187
188	$expected = create_table($link, 'INT UNSIGNED', 0, 1000, $engine, 190);
189	foreach ($expected as $k => $v)
190		$expected[$k] = number_format(round($v), 0, '.', ',');
191	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 200);
192
193	$expected = create_table($link, 'BIGINT', -1000, 1000, $engine, 210);
194	foreach ($expected as $k => $v)
195		$expected[$k] = number_format(round($v), 0, '.', ',');
196	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 220);
197
198	$expected = create_table($link, 'DECIMAL(5,0)', -1000, 1000, $engine, 230);
199	foreach ($expected as $k => $v)
200		$expected[$k] = number_format(round($v), 0, '.', ',');
201	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 240);
202
203	// http://bugs.php.net/bug.php?id=42378
204	if (!mysqli_query($link, "DROP TABLE IF EXISTS test")) {
205		printf("[300] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
206	}
207
208	if (mysqli_query($link, "CREATE TABLE `test` (
209  `targetport` int(11) NOT NULL default '0',
210  `sources` double(17,4) default NULL,
211  `current_sources` double(17,0) default NULL,
212  `reports` double(17,4) default NULL,
213  `current_reports` double(17,0) default NULL,
214  `targets` double(17,4) default NULL,
215  `current_targets` double(17,0) default NULL,
216  `maxsources` int(11) default NULL,
217  `maxtargets` int(11) default NULL,
218  `maxreports` int(11) default NULL,
219  `trend` float default NULL,
220  PRIMARY KEY  (`targetport`)
221) ENGINE=InnoDB DEFAULT CHARSET=latin1")) {
222
223		do {
224			$values = array();
225			for ($i = 0; $i < 200; $i++) {
226				$current_targets = mt_rand(-100000, 100000) / 10;
227				do {
228					$trend = (mt_rand(0, 3) > 1) ? (mt_rand(-10000, 10000) / 100) : 'NULL';
229				} while (isset($values[$trend]));
230
231				$sql = sprintf('INSERT INTO test(targetport, current_targets, maxreports, trend) VALUES (%d, %f, %s, %s)',
232					$i,
233					$current_targets,
234					(mt_rand(0, 1) > 0) ? mt_rand(0, 1000) : 'NULL',
235					$trend);
236				if (!mysqli_query($link, $sql)) {
237					printf("[301] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
238					break 2;
239				}
240				if ($current_targets > 0 && $trend != 'NULL')
241					$values[$trend] = $i;
242			}
243			krsort($values);
244
245			if (!$stmt = mysqli_stmt_init($link)) {
246				printf("[302] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
247				break;
248			}
249
250			if (!mysqli_stmt_prepare($stmt, 'SELECT trend, targetport, FORMAT(trend, 2) FROM test WHERE current_targets > 0 AND trend IS NOT NULL ORDER BY trend DESC LIMIT 100')) {
251				printf("[303] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
252				break;
253			}
254
255			if (!mysqli_stmt_execute($stmt)) {
256				printf("[304] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
257				break;
258			}
259
260			if (!mysqli_stmt_store_result($stmt)) {
261				printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
262				break;
263			}
264
265			$trend = $targetport = $format = null;
266			if (!mysqli_stmt_bind_result($stmt, $trend, $targetport, $format)) {
267
268				printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
269				break;
270			}
271
272			reset($values);
273			while (mysqli_stmt_fetch($stmt)) {
274				list($exp_trend, $exp_targetport) = each($values);
275				if ($targetport != $exp_targetport) {
276					printf("[306] Values fetched from MySQL seem to be wrong, check manually\n");
277					printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
278				}
279			}
280			mysqli_stmt_free_result($stmt);
281			mysqli_stmt_close($stmt);
282
283			// same but OO interface
284			if (!$stmt = mysqli_stmt_init($link)) {
285				printf("[307] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
286				break;
287			}
288
289			if (!$stmt->prepare('SELECT trend, targetport, FORMAT(trend, 2) FROM test WHERE current_targets > 0 AND trend IS NOT NULL ORDER BY trend DESC LIMIT 100')) {
290				printf("[308] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
291				break;
292			}
293
294			if (!$stmt->execute()) {
295				printf("[309] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
296				break;
297			}
298
299			if (!$stmt->store_result()) {
300				printf("[310] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
301				break;
302			}
303
304			$trend = $targetport = $format = null;
305			if (!$stmt->bind_result($trend, $targetport, $format)) {
306
307				printf("[311] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
308				break;
309			}
310
311			reset($values);
312			while ($stmt->fetch()) {
313				list($exp_trend, $exp_targetport) = each($values);
314				if ($targetport != $exp_targetport) {
315					printf("[312] Values fetched from MySQL seem to be wrong, check manually\n");
316					printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
317				}
318			}
319			$stmt->free_result();
320			$stmt->close();
321
322		} while (false);
323
324	} else {
325		var_dump(mysqli_error($link));
326	}
327
328
329	mysqli_close($link);
330	print "done!";
331?>
332--CLEAN--
333<?php
334	require_once("clean_table.inc");
335?>
336--EXPECTF--
337done!