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			foreach ($expected as $k => $v) {
121				if (!mysqli_stmt_fetch($stmt)) {
122					break;
123				}
124				if ($result !== $v) {
125					printf("[%03d] Row %d - expecting %s/%s got %s/%s [%s] with %s - %s.\n",
126						$offset + 8,
127						$k,
128						gettype($v), $v,
129						gettype($result), $result,
130						$order_by_col,
131						$format, $sql);
132				}
133			}
134
135		}
136
137		mysqli_stmt_free_result($stmt);
138		mysqli_stmt_close($stmt);
139
140		return true;
141	}
142
143	if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
144		printf("[001] Cannot connect - [%d] %s\n",
145			mysqli_connect_errno(),
146			mysqli_connect_error());
147
148	/* select from dual - pseudo table */
149	test_format($link, 'FORMAT(1.01, 0)', 'DUAL', null, '1', 10);
150	test_format($link, 'FORMAT(1.23, 1)', 'DUAL', null, '1.2', 20);
151	test_format($link, 'FORMAT(1.23, 2)', 'DUAL', null, '1.23', 30);
152	test_format($link, 'FORMAT(1234.567, 3)', 'DUAL', null, '1,234.567', 40);
153	/* no typo! */
154	test_format($link, 'FORMAT(1234.567, 4)', 'DUAL', null, '1,234.5670', 50);
155
156	mysqli_close($link);
157	require_once('table.inc');
158
159	/* select from existing table */
160	test_format($link, 'FORMAT(id, 0)', 'test', null, '1', 60);
161	test_format($link, 'FORMAT(id + 0.1, 1)', 'test', null, '1.1', 70);
162	test_format($link, 'FORMAT(id + 0.01, 2)', 'test', null, '1.01', 80);
163
164	/* create new table and select from it */
165	$expected = create_table($link, 'FLOAT', -10000, 10000, $engine, 90);
166	foreach ($expected as $k => $v)
167		$expected[$k] = number_format(round($v), 0, '.', ',');
168	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 100);
169
170	$expected = create_table($link, 'FLOAT UNSIGNED', 0, 10000, $engine, 110);
171	foreach ($expected as $k => $v)
172		$expected[$k] = number_format(round($v), 0, '.', ',');
173	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 120);
174
175	$expected = create_table($link, 'TINYINT', -128, 127, $engine, 130);
176	foreach ($expected as $k => $v)
177		$expected[$k] = number_format(round($v), 0, '.', ',');
178	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 140);
179
180	$expected = create_table($link, 'SMALLINT UNSIGNED', 0, 65535, $engine, 150);
181	foreach ($expected as $k => $v)
182		$expected[$k] = number_format(round($v), 0, '.', ',');
183	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 160);
184
185	$expected = create_table($link, 'MEDIUMINT', 0, 8388607, $engine, 170);
186	foreach ($expected as $k => $v)
187		$expected[$k] = number_format(round($v), 0, '.', ',');
188	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 180);
189
190	$expected = create_table($link, 'INT UNSIGNED', 0, 1000, $engine, 190);
191	foreach ($expected as $k => $v)
192		$expected[$k] = number_format(round($v), 0, '.', ',');
193	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 200);
194
195	$expected = create_table($link, 'BIGINT', -1000, 1000, $engine, 210);
196	foreach ($expected as $k => $v)
197		$expected[$k] = number_format(round($v), 0, '.', ',');
198	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 220);
199
200	$expected = create_table($link, 'DECIMAL(5,0)', -1000, 1000, $engine, 230);
201	foreach ($expected as $k => $v)
202		$expected[$k] = number_format(round($v), 0, '.', ',');
203	test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 240);
204
205	// http://bugs.php.net/bug.php?id=42378
206	if (!mysqli_query($link, "DROP TABLE IF EXISTS test")) {
207		printf("[300] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
208	}
209
210	if (mysqli_query($link, "CREATE TABLE `test` (
211  `targetport` int(11) NOT NULL default '0',
212  `sources` double(17,4) default NULL,
213  `current_sources` double(17,4) default NULL,
214  `reports` double(17,4) default NULL,
215  `current_reports` double(17,4) default NULL,
216  `targets` double(17,4) default NULL,
217  `current_targets` double(17,4) default NULL,
218  `maxsources` int(11) default NULL,
219  `maxtargets` int(11) default NULL,
220  `maxreports` int(11) default NULL,
221  `trend` float default NULL,
222  PRIMARY KEY  (`targetport`)
223) ENGINE=InnoDB DEFAULT CHARSET=latin1")) {
224
225		do {
226			$values = array();
227			for ($i = 0; $i < 200; $i++) {
228				$current_targets = mt_rand(-100000, 100000) / 10;
229				do {
230					$trend = (mt_rand(0, 3) > 1) ? (mt_rand(-10000, 10000) / 100) : 'NULL';
231				} while (isset($values[$trend]));
232
233				$sql = sprintf('INSERT INTO test(targetport, current_targets, maxreports, trend) VALUES (%d, %f, %s, %s)',
234					$i,
235					$current_targets,
236					(mt_rand(0, 1) > 0) ? mt_rand(0, 1000) : 'NULL',
237					$trend);
238				if (!mysqli_query($link, $sql)) {
239					printf("[301] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
240					break 2;
241				}
242				if ($current_targets > 0 && $trend !== 'NULL')
243					$values[$trend] = $i;
244			}
245			krsort($values);
246
247			if (!$stmt = mysqli_stmt_init($link)) {
248				printf("[302] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
249				break;
250			}
251
252			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')) {
253				printf("[303] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
254				break;
255			}
256
257			if (!mysqli_stmt_execute($stmt)) {
258				printf("[304] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
259				break;
260			}
261
262			if (!mysqli_stmt_store_result($stmt)) {
263				printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
264				break;
265			}
266
267			$trend = $targetport = $format = null;
268			if (!mysqli_stmt_bind_result($stmt, $trend, $targetport, $format)) {
269
270				printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
271				break;
272			}
273
274			foreach ($values as $exp_trend => $exp_targetport) {
275				if (!mysqli_stmt_fetch($stmt)) {
276					break;
277				}
278				if ($targetport != $exp_targetport) {
279					printf("[306] Values fetched from MySQL seem to be wrong, check manually\n");
280					printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
281				}
282			}
283			mysqli_stmt_free_result($stmt);
284			mysqli_stmt_close($stmt);
285
286			// same but OO interface
287			if (!$stmt = mysqli_stmt_init($link)) {
288				printf("[307] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
289				break;
290			}
291
292			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')) {
293				printf("[308] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
294				break;
295			}
296
297			if (!$stmt->execute()) {
298				printf("[309] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
299				break;
300			}
301
302			if (!$stmt->store_result()) {
303				printf("[310] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
304				break;
305			}
306
307			$trend = $targetport = $format = null;
308			if (!$stmt->bind_result($trend, $targetport, $format)) {
309
310				printf("[311] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
311				break;
312			}
313
314			foreach ($values as $exp_trend => $exp_targetport) {
315				if (!$stmt->fetch()) {
316					break;
317				}
318				if ($targetport != $exp_targetport) {
319					printf("[312] Values fetched from MySQL seem to be wrong, check manually\n");
320					printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
321				}
322			}
323			$stmt->free_result();
324			$stmt->close();
325
326		} while (false);
327
328	} else {
329		var_dump(mysqli_error($link));
330	}
331
332
333	mysqli_close($link);
334	print "done!";
335?>
336--CLEAN--
337<?php
338	require_once("clean_table.inc");
339?>
340--EXPECT--
341done!
342