API tools faq
paste
Advertisement
Guest User

MySQL 8.0-8.0.22 main.cast failure log

a guest
Jan 15th, 2021
77
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 93.90 KB | None | 0 0
raw download clone embed print report
  1. select CAST(1-2 AS UNSIGNED);
  2. CAST(1-2 AS UNSIGNED)
  3. 18446744073709551615
  4. select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
  5. CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER)
  6. -1
  7. select CAST('10 ' as unsigned integer);
  8. CAST('10 ' as unsigned integer)
  9. 10
  10. select CAST('10x' as unsigned integer);
  11. CAST('10x' as unsigned integer)
  12. 10
  13. Warnings:
  14. Warning 1292 Truncated incorrect INTEGER value: '10x'
  15. select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
  16. cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1
  17. 18446744073709551611 18446744073709551611
  18. select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;
  19. cast(-5 as unsigned) -1 cast(-5 as unsigned) + 1
  20. 18446744073709551610 18446744073709551612
  21. select ~5, cast(~5 as signed);
  22. ~5 cast(~5 as signed)
  23. 18446744073709551610 -6
  24. explain select ~5, cast(~5 as signed);
  25. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  26. 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
  27. Warnings:
  28. Note 1003 /* select#1 */ select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
  29. select cast(5 as unsigned) -6.0;
  30. cast(5 as unsigned) -6.0
  31. -1.0
  32. select cast(NULL as signed), cast(1/0 as signed);
  33. cast(NULL as signed) cast(1/0 as signed)
  34. NULL NULL
  35. Warnings:
  36. Warning 1365 Division by 0
  37. select cast(NULL as unsigned), cast(1/0 as unsigned);
  38. cast(NULL as unsigned) cast(1/0 as unsigned)
  39. NULL NULL
  40. Warnings:
  41. Warning 1365 Division by 0
  42. select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
  43. cast("A" as binary) = "a" cast(BINARY "a" as CHAR) = "A"
  44. 0 1
  45. select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
  46. cast("2001-1-1" as DATE) cast("2001-1-1" as DATETIME)
  47. 2001-01-01 2001-01-01 00:00:00
  48. select cast("1:2:3" as TIME);
  49. cast("1:2:3" as TIME)
  50. 01:02:03
  51. select CONVERT("2004-01-22 21:45:33",DATE);
  52. CONVERT("2004-01-22 21:45:33",DATE)
  53. 2004-01-22
  54. select 10+'10';
  55. 10+'10'
  56. 20
  57. select 10.0+'10';
  58. 10.0+'10'
  59. 20
  60. select 10E+0+'10';
  61. 10E+0+'10'
  62. 20
  63. SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33" USING latin1);
  64. CONVERT(TIMESTAMP "2004-01-22 21:45:33" USING latin1)
  65. 2004-01-22 21:45:33
  66. SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR);
  67. CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR)
  68. 2004-01-22 21:45:33
  69. SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR(4));
  70. CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR(4))
  71. 2004
  72. Warnings:
  73. Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'
  74. SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", BINARY(4));
  75. CONVERT(TIMESTAMP "2004-01-22 21:45:33", BINARY(4))
  76. 2004
  77. Warnings:
  78. Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
  79. select CAST(TIMESTAMP "2004-01-22 21:45:33" AS BINARY(4));
  80. CAST(TIMESTAMP "2004-01-22 21:45:33" AS BINARY(4))
  81. 2004
  82. Warnings:
  83. Warning 1292 Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'
  84. select CAST(0xb3 as signed);
  85. CAST(0xb3 as signed)
  86. 179
  87. select CAST(0x8fffffffffffffff as signed);
  88. CAST(0x8fffffffffffffff as signed)
  89. -8070450532247928833
  90. select CAST(0xffffffffffffffff as unsigned);
  91. CAST(0xffffffffffffffff as unsigned)
  92. 18446744073709551615
  93. select CAST(0xfffffffffffffffe as signed);
  94. CAST(0xfffffffffffffffe as signed)
  95. -2
  96. select cast('-10a' as signed integer);
  97. cast('-10a' as signed integer)
  98. -10
  99. Warnings:
  100. Warning 1292 Truncated incorrect INTEGER value: '-10a'
  101. select cast('a10' as unsigned integer);
  102. cast('a10' as unsigned integer)
  103. 0
  104. Warnings:
  105. Warning 1292 Truncated incorrect INTEGER value: 'a10'
  106. select 10+'a';
  107. 10+'a'
  108. 10
  109. Warnings:
  110. Warning 1292 Truncated incorrect DOUBLE value: 'a'
  111. select 10.0+cast('a' as decimal);
  112. 10.0+cast('a' as decimal)
  113. 10.0
  114. Warnings:
  115. Warning 1292 Truncated incorrect DECIMAL value: 'a'
  116. select 10E+0+'a';
  117. 10E+0+'a'
  118. 10
  119. Warnings:
  120. Warning 1292 Truncated incorrect DOUBLE value: 'a'
  121. select cast('18446744073709551616' as unsigned);
  122. cast('18446744073709551616' as unsigned)
  123. 18446744073709551615
  124. Warnings:
  125. Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616'
  126. select cast('18446744073709551616' as signed);
  127. cast('18446744073709551616' as signed)
  128. -1
  129. Warnings:
  130. Warning 1292 Truncated incorrect INTEGER value: '18446744073709551616'
  131. select cast('9223372036854775809' as signed);
  132. cast('9223372036854775809' as signed)
  133. -9223372036854775807
  134. Warnings:
  135. Warning 1105 Cast to signed converted positive out-of-range integer to its negative complement
  136. select cast('-1' as unsigned);
  137. cast('-1' as unsigned)
  138. 18446744073709551615
  139. Warnings:
  140. Warning 1105 Cast to unsigned converted negative integer to its positive complement
  141. select cast('abc' as signed);
  142. cast('abc' as signed)
  143. 0
  144. Warnings:
  145. Warning 1292 Truncated incorrect INTEGER value: 'abc'
  146. select cast('1a' as signed);
  147. cast('1a' as signed)
  148. 1
  149. Warnings:
  150. Warning 1292 Truncated incorrect INTEGER value: '1a'
  151. select cast('' as signed);
  152. cast('' as signed)
  153. 0
  154. Warnings:
  155. Warning 1292 Truncated incorrect INTEGER value: ''
  156. set names binary;
  157. select cast(_latin1'test' as char character set latin2);
  158. cast(_latin1'test' as char character set latin2)
  159. test
  160. select cast(_koi8r'����' as char character set cp1251);
  161. cast(_koi8r'����' as char character set cp1251)
  162. ����
  163. create table t1 select cast(_koi8r'����' as char character set cp1251) as t;
  164. show create table t1;
  165. Table Create Table
  166. t1 CREATE TABLE `t1` (
  167. `t` varchar(4) CHARACTER SET cp1251 DEFAULT NULL
  168. ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  169. drop table t1;
  170. select
  171. cast(_latin1'ab' AS char) as c1,
  172. cast(_latin1'a ' AS char) as c2,
  173. cast(_latin1'abc' AS char(2)) as c3,
  174. cast(_latin1'a ' AS char(2)) as c4,
  175. hex(cast(_latin1'a' AS char(2))) as c5;
  176. c1 c2 c3 c4 c5
  177. ab a ab a 6100
  178. Warnings:
  179. Warning 1292 Truncated incorrect BINARY(2) value: 'abc'
  180. Warning 1292 Truncated incorrect BINARY(2) value: 'a '
  181. select cast(1000 as CHAR(3));
  182. cast(1000 as CHAR(3))
  183. 100
  184. Warnings:
  185. Warning 1292 Truncated incorrect BINARY(3) value: '1000'
  186. SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
  187. create table t1 select
  188. cast(_latin1'ab' AS char) as c1,
  189. cast(_latin1'a ' AS char) as c2,
  190. cast(_latin1'abc' AS char(2)) as c3,
  191. cast(_latin1'a ' AS char(2)) as c4,
  192. cast(_latin1'a' AS char(2)) as c5;
  193. Warnings:
  194. Warning 1292 Truncated incorrect BINARY(2) value: 'abc'
  195. Warning 1292 Truncated incorrect BINARY(2) value: 'a '
  196. select c1,c2,c3,c4,hex(c5) from t1;
  197. c1 c2 c3 c4 hex(c5)
  198. ab a ab a 6100
  199. show create table t1;
  200. Table Create Table
  201. t1 CREATE TABLE `t1` (
  202. `c1` varbinary(2) NOT NULL DEFAULT '',
  203. `c2` varbinary(2) NOT NULL DEFAULT '',
  204. `c3` varbinary(2) NOT NULL DEFAULT '',
  205. `c4` varbinary(2) NOT NULL DEFAULT '',
  206. `c5` varbinary(2) NOT NULL DEFAULT ''
  207. ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  208. drop table t1;
  209. select
  210. cast(_koi8r'��' AS nchar) as c1,
  211. cast(_koi8r'� ' AS nchar) as c2,
  212. cast(_koi8r'���' AS nchar(2)) as c3,
  213. cast(_koi8r'� ' AS nchar(2)) as c4,
  214. cast(_koi8r'�' AS nchar(2)) as c5;
  215. c1 c2 c3 c4 c5
  216. фг ф фг ф ф
  217. Warnings:
  218. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  219. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  220. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  221. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  222. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  223. Warning 1292 Truncated incorrect CHAR(4) value: 'фгх'
  224. Warning 1292 Truncated incorrect CHAR(3) value: 'ф '
  225. create table t1 select
  226. cast(_koi8r'��' AS nchar) as c1,
  227. cast(_koi8r'� ' AS nchar) as c2,
  228. cast(_koi8r'���' AS nchar(2)) as c3,
  229. cast(_koi8r'� ' AS nchar(2)) as c4,
  230. cast(_koi8r'�' AS nchar(2)) as c5;
  231. Warnings:
  232. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  233. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  234. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  235. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  236. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  237. Warning 1292 Truncated incorrect CHAR(4) value: 'фгх'
  238. Warning 1292 Truncated incorrect CHAR(3) value: 'ф '
  239. select * from t1;
  240. c1 c2 c3 c4 c5
  241. фг ф фг ф ф
  242. show create table t1;
  243. Table Create Table
  244. t1 CREATE TABLE `t1` (
  245. `c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  246. `c2` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  247. `c3` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  248. `c4` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  249. `c5` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
  250. ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  251. drop table t1;
  252. #
  253. # Bug #24934161: FAILURE OF SYNONYMY OF NCHAR AND NATIONAL CHAR
  254. #
  255. SELECT
  256. CAST(_gb2312 x'CAFDBEDD' AS NATIONAL CHAR) AS c1,
  257. CAST(_gb2312 x'CAFD20' AS NATIONAL CHAR) AS c2,
  258. CAST(_gb2312 x'CAFDBEDDBFE2' AS NATIONAL CHAR(2)) AS c3,
  259. CAST(_gb2312 x'CAFD2020' AS NATIONAL CHAR(2)) AS c4,
  260. CAST(_gb2312 x'CAFD' AS NATIONAL CHAR(2)) AS c5;
  261. c1 c2 c3 c4 c5
  262. 数据 数 数据 数 数
  263. Warnings:
  264. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  265. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  266. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  267. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  268. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  269. Warning 1292 Truncated incorrect CHAR(6) value: '数据库'
  270. Warning 1292 Truncated incorrect CHAR(4) value: '数 '
  271. CREATE TABLE t1 SELECT
  272. CAST(_gb2312 x'CAFDBEDD' AS NATIONAL CHAR) AS c1,
  273. CAST(_gb2312 x'CAFD20' AS NATIONAL CHAR) AS c2,
  274. CAST(_gb2312 x'CAFDBEDDBFE2' AS NATIONAL CHAR(2)) AS c3,
  275. CAST(_gb2312 x'CAFD2020' AS NATIONAL CHAR(2)) AS c4,
  276. CAST(_gb2312 x'CAFD' AS NATIONAL CHAR(2)) AS c5;
  277. Warnings:
  278. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  279. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  280. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  281. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  282. Warning 3720 NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
  283. Warning 1292 Truncated incorrect CHAR(6) value: '数据库'
  284. Warning 1292 Truncated incorrect CHAR(4) value: '数 '
  285. SELECT * FROM t1;
  286. c1 c2 c3 c4 c5
  287. 数据 数 数据 数 数
  288. SHOW CREATE TABLE t1;
  289. Table Create Table
  290. t1 CREATE TABLE `t1` (
  291. `c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  292. `c2` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  293. `c3` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  294. `c4` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT '',
  295. `c5` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
  296. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  297. DROP TABLE t1;
  298. SET sql_mode = default;
  299. create table t1 (a binary(4), b char(4) character set koi8r);
  300. insert into t1 values (_binary'����',_binary'����');
  301. select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
  302. a b cast(a as char character set cp1251) cast(b as binary)
  303. ���� ���� ���� ����
  304. set names koi8r;
  305. select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
  306. a b cast(a as char character set cp1251) cast(b as binary)
  307. ���� ���� ���� ����
  308. set names cp1251;
  309. select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;
  310. a b cast(a as char character set cp1251) cast(b as binary)
  311. ���� ���� ���� ����
  312. drop table t1;
  313. set names binary;
  314. select cast("2001-1-1" as date) = "2001-01-01";
  315. cast("2001-1-1" as date) = "2001-01-01"
  316. 1
  317. select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
  318. cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"
  319. 1
  320. select cast("1:2:3" as TIME) = "1:02:03";
  321. cast("1:2:3" as TIME) = "1:02:03"
  322. 0
  323. select cast(NULL as DATE);
  324. cast(NULL as DATE)
  325. NULL
  326. select cast(NULL as BINARY);
  327. cast(NULL as BINARY)
  328. NULL
  329. CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
  330. INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
  331. SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
  332. a CAST(a AS CHAR)
  333. aac aac
  334. aab aab
  335. aaa aaa
  336. SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
  337. a CAST(a AS CHAR(3))
  338. aac aac
  339. aab aab
  340. aaa aaa
  341. Warnings:
  342. Warning 1292 Truncated incorrect BINARY(2) value: 'aaa'
  343. Warning 1292 Truncated incorrect BINARY(2) value: 'aab'
  344. Warning 1292 Truncated incorrect BINARY(2) value: 'aac'
  345. SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
  346. a CAST(a AS UNSIGNED)
  347. aaa 3
  348. aab 2
  349. aac 1
  350. SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
  351. a CAST(a AS CHAR(2))
  352. aaa aa
  353. aab aa
  354. aac aa
  355. Warnings:
  356. Warning 1292 Truncated incorrect BINARY(2) value: 'aaa'
  357. Warning 1292 Truncated incorrect BINARY(2) value: 'aab'
  358. Warning 1292 Truncated incorrect BINARY(2) value: 'aac'
  359. DROP TABLE t1;
  360. select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
  361. date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour)
  362. 2004-12-30 00:00:00
  363. select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
  364. timediff(cast('2004-12-30 12:00:00' as time), '12:00:00')
  365. 00:00:00
  366. select timediff(cast('1 12:00:00' as time), '12:00:00');
  367. timediff(cast('1 12:00:00' as time), '12:00:00')
  368. 24:00:00
  369. select cast(18446744073709551615 as unsigned);
  370. cast(18446744073709551615 as unsigned)
  371. 18446744073709551615
  372. select cast(18446744073709551615 as signed);
  373. cast(18446744073709551615 as signed)
  374. -1
  375. select cast('18446744073709551615' as unsigned);
  376. cast('18446744073709551615' as unsigned)
  377. 18446744073709551615
  378. select cast('18446744073709551615' as signed);
  379. cast('18446744073709551615' as signed)
  380. -1
  381. Warnings:
  382. Warning 1105 Cast to signed converted positive out-of-range integer to its negative complement
  383. select cast('9223372036854775807' as signed);
  384. cast('9223372036854775807' as signed)
  385. 9223372036854775807
  386. select cast(concat('184467440','73709551615') as unsigned);
  387. cast(concat('184467440','73709551615') as unsigned)
  388. 18446744073709551615
  389. select cast(concat('184467440','73709551615') as signed);
  390. cast(concat('184467440','73709551615') as signed)
  391. -1
  392. Warnings:
  393. Warning 1105 Cast to signed converted positive out-of-range integer to its negative complement
  394. select cast(repeat('1',20) as unsigned);
  395. cast(repeat('1',20) as unsigned)
  396. 11111111111111111111
  397. select cast(repeat('1',20) as signed);
  398. cast(repeat('1',20) as signed)
  399. -7335632962598440505
  400. Warnings:
  401. Warning 1105 Cast to signed converted positive out-of-range integer to its negative complement
  402. select cast(1.0e+300 as signed int);
  403. cast(1.0e+300 as signed int)
  404. 9223372036854775807
  405. CREATE TABLE t1 (f1 double);
  406. INSERT INTO t1 SET f1 = -1.0e+30 ;
  407. INSERT INTO t1 SET f1 = +1.0e+30 ;
  408. SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1;
  409. double_val cast_val
  410. -1e30 -9223372036854775808
  411. 1e30 9223372036854775807
  412. Warnings:
  413. Warning 1292 Truncated incorrect INTEGER value: '-1e30'
  414. Warning 1292 Truncated incorrect INTEGER value: '1e30'
  415. DROP TABLE t1;
  416. select isnull(date(NULL)), isnull(cast(NULL as DATE));
  417. isnull(date(NULL)) isnull(cast(NULL as DATE))
  418. 1 1
  419. SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
  420. CAST(cast('01-01-01' as date) AS UNSIGNED)
  421. 20010101
  422. SELECT CAST(cast('01-01-01' as date) AS SIGNED);
  423. CAST(cast('01-01-01' as date) AS SIGNED)
  424. 20010101
  425. End of 4.1 tests
  426. select cast('1.2' as decimal(3,2));
  427. cast('1.2' as decimal(3,2))
  428. 1.20
  429. select 1e18 * cast('1.2' as decimal(3,2));
  430. 1e18 * cast('1.2' as decimal(3,2))
  431. 1.2e18
  432. select cast(cast('1.2' as decimal(3,2)) as signed);
  433. cast(cast('1.2' as decimal(3,2)) as signed)
  434. 1
  435. set @v1=1e18;
  436. select cast(@v1 as decimal(22, 2));
  437. cast(@v1 as decimal(22, 2))
  438. 1000000000000000000.00
  439. select cast(-1e18 as decimal(22,2));
  440. cast(-1e18 as decimal(22,2))
  441. -1000000000000000000.00
  442. create table t1(s1 time);
  443. insert into t1 values ('11:11:11');
  444. select cast(s1 as decimal(7,2)) from t1;
  445. cast(s1 as decimal(7,2))
  446. 99999.99
  447. Warnings:
  448. Warning 1264 Out of range value for column 'cast(s1 as decimal(7,2))' at row 1
  449. drop table t1;
  450. CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
  451. mt mediumtext, lt longtext);
  452. INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
  453. SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
  454. CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
  455. CAST(v AS DECIMAL) CAST(tt AS DECIMAL) CAST(t AS DECIMAL) CAST(mt AS DECIMAL) CAST(lt AS DECIMAL)
  456. 1 2 3 4 5
  457. DROP TABLE t1;
  458. select cast(NULL as decimal(6)) as t1;
  459. t1
  460. NULL
  461. set names latin1;
  462. select hex(cast('a' as char(2) binary));
  463. hex(cast('a' as char(2) binary))
  464. 61
  465. Warnings:
  466. Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
  467. select hex(cast('a' as binary(2)));
  468. hex(cast('a' as binary(2)))
  469. 6100
  470. select hex(cast('a' as char(2) binary));
  471. hex(cast('a' as char(2) binary))
  472. 61
  473. Warnings:
  474. Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
  475. CREATE TABLE t1 (d1 datetime);
  476. INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL),
  477. ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00');
  478. SELECT cast(date(d1) as signed) FROM t1;
  479. cast(date(d1) as signed)
  480. 20070719
  481. NULL
  482. 20070719
  483. NULL
  484. 20070719
  485. drop table t1;
  486. CREATE TABLE t1 (f1 DATE);
  487. INSERT INTO t1 VALUES ('2007-07-19'), (NULL);
  488. SELECT HOUR(f1),
  489. MINUTE(f1),
  490. SECOND(f1) FROM t1;
  491. HOUR(f1) MINUTE(f1) SECOND(f1)
  492. 0 0 0
  493. NULL NULL NULL
  494. SELECT HOUR(CAST('2007-07-19' AS DATE)),
  495. MINUTE(CAST('2007-07-19' AS DATE)),
  496. SECOND(CAST('2007-07-19' AS DATE));
  497. HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE))
  498. 0 0 0
  499. SELECT HOUR(CAST(NULL AS DATE)),
  500. MINUTE(CAST(NULL AS DATE)),
  501. SECOND(CAST(NULL AS DATE));
  502. HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE))
  503. NULL NULL NULL
  504. SELECT HOUR(NULL),
  505. MINUTE(NULL),
  506. SECOND(NULL);
  507. HOUR(NULL) MINUTE(NULL) SECOND(NULL)
  508. NULL NULL NULL
  509. DROP TABLE t1;
  510. End of 5.0 tests
  511. #
  512. # Bug #44766: valgrind error when using convert() in a subquery
  513. #
  514. CREATE TABLE t1(a tinyint);
  515. INSERT INTO t1 VALUES (127);
  516. SELECT 1 FROM
  517. (
  518. SELECT CONVERT(t2.a USING UTF8) FROM t1, t1 t2 LIMIT 1
  519. ) AS s LIMIT 1;
  520. 1
  521. 1
  522. Warnings:
  523. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
  524. DROP TABLE t1;
  525. #
  526. # Bug #11765023: 57934: DOS POSSIBLE SINCE BINARY CASTING
  527. # DOESN'T ADHERE TO MAX_ALLOWED_PACKET
  528. SET @@GLOBAL.max_allowed_packet=2048;
  529. Warnings:
  530. Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
  531. SELECT CONVERT('a', BINARY(2049));
  532. CONVERT('a', BINARY(2049))
  533. NULL
  534. Warnings:
  535. Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (2048) - truncated
  536. SELECT CONVERT('a', CHAR(2049));
  537. CONVERT('a', CHAR(2049))
  538. NULL
  539. Warnings:
  540. Warning 1301 Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated
  541. SET @@GLOBAL.max_allowed_packet=default;
  542. #
  543. # Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET)
  544. #
  545. CREATE TABLE t1 (a VARCHAR(50));
  546. SELECT a FROM t1
  547. WHERE CAST(a as BINARY)=x'62736D697468'
  548. AND CAST(a AS BINARY)=x'65736D697468';
  549. a
  550. DROP TABLE t1;
  551. End of 5.1 tests
  552. #
  553. # Bug#22885819: CAST( .. AS BINARY(N)) GETS UNEXPECTED NULL
  554. #
  555. SELECT CAST( 'a' AS BINARY(429496729));
  556. CAST( 'a' AS BINARY(429496729))
  557. NULL
  558. Warnings:
  559. Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
  560. SELECT CAST( 'a' AS BINARY(4294967294));
  561. CAST( 'a' AS BINARY(4294967294))
  562. NULL
  563. Warnings:
  564. Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
  565. SELECT CAST( 'a' AS BINARY(4294967295));
  566. CAST( 'a' AS BINARY(4294967295))
  567. NULL
  568. Warnings:
  569. Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (67108864) - truncated
  570. SELECT CAST( 'a' AS BINARY(4294967296));
  571. ERROR 42000: Display width out of range for column 'cast as char' (max = 4294967295)
  572. SELECT CAST( 'a' AS BINARY(4294967296784564));
  573. ERROR 42000: Display width out of range for column 'cast as char' (max = 4294967295)
  574. #
  575. # Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH LONGTEXT, UNION, USER VARIABLE
  576. #
  577. SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
  578. CREATE TABLE t1 AS SELECT CONCAT(CAST(REPEAT('9', 1000) AS SIGNED)),
  579. CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED));
  580. Warnings:
  581. Warning 1292 Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'
  582. Warning 1292 Truncated incorrect INTEGER value: '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999'
  583. SHOW CREATE TABLE t1;
  584. Table Create Table
  585. t1 CREATE TABLE `t1` (
  586. `CONCAT(CAST(REPEAT('9', 1000) AS SIGNED))` varchar(21) CHARACTER SET latin1 NOT NULL DEFAULT '',
  587. `CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED))` varchar(21) CHARACTER SET latin1 NOT NULL DEFAULT ''
  588. ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  589. DROP TABLE t1;
  590. SET sql_mode = default;
  591. End of 5.5 tests
  592. #
  593. # Bug#28547906 ENUM TYPE CASTING WORKS WRONG WITH SUBQUERIES
  594. #
  595. CREATE TABLE t (c1 ENUM('a','b','c'));
  596. INSERT INTO t VALUES ('a'), ('b'), ('c');
  597. SELECT CAST(c1 AS UNSIGNED) AS c5 FROM t;
  598. c5
  599. 1
  600. 2
  601. 3
  602. SELECT CAST(c1 AS UNSIGNED) AS c5 FROM (SELECT c1 FROM t) t;
  603. c5
  604. 1
  605. 2
  606. 3
  607. DROP TABLE t;
  608. #
  609. # CAST as DOUBLE/FLOAT/REAL
  610. #
  611. SELECT CAST(1/3 AS FLOAT) as float_col,
  612. CAST(1/3 AS DOUBLE) as double_col,
  613. CAST(1/3 AS DOUBLE PRECISION) as double_prec_col,
  614. CAST(1/3 AS REAL) as real_col;
  615. float_col double_col double_prec_col real_col
  616. 0.333333 0.333333333 0.333333333 0.333333333
  617. SELECT CAST(1/3 AS FLOAT(10)), CAST(1/3 AS FLOAT(53));
  618. CAST(1/3 AS FLOAT(10)) CAST(1/3 AS FLOAT(53))
  619. 0.333333 0.333333333
  620. SELECT CAST(1/3 AS FLOAT(-1));
  621. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1))' at line 1
  622. SELECT CAST(1/3 AS FLOAT(54));
  623. ERROR 42000: Too-big precision 54 specified for 'CAST'. Maximum is 53.
  624. SELECT CAST(1/3 AS DOUBLE(52));
  625. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(52))' at line 1
  626. SELECT CAST(1/3 AS REAL(34));
  627. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(34))' at line 1
  628. SELECT CAST(999.00009 AS FLOAT(7,4)) as float_col;
  629. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',4)) as float_col' at line 1
  630. SELECT CAST(999.00009 AS DOUBLE(7,4)) as double_col;
  631. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(7,4)) as double_col' at line 1
  632. SELECT CAST(999.00009 AS REAL(7,4)) as real_col;
  633. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(7,4)) as real_col' at line 1
  634. SELECT ADDDATE(CAST("20010101235959.9" AS DOUBLE), INTERVAL 1 DAY);
  635. ADDDATE(CAST("20010101235959.9" AS DOUBLE), INTERVAL 1 DAY)
  636. 2001-01-02 23:59:59.898438
  637. SELECT TIMEDIFF(CAST("101112" AS DOUBLE), TIME'101010');
  638. TIMEDIFF(CAST("101112" AS DOUBLE), TIME'101010')
  639. 00:01:02.000000
  640. SELECT CAST(DATE'2000-01-01' AS FLOAT), CAST(DATE'2000-01-01' AS DOUBLE);
  641. CAST(DATE'2000-01-01' AS FLOAT) CAST(DATE'2000-01-01' AS DOUBLE)
  642. 20000100 20000101
  643. SELECT CAST(TIME'23:59:59' AS FLOAT), CAST(TIME'23:59:59' AS DOUBLE);
  644. CAST(TIME'23:59:59' AS FLOAT) CAST(TIME'23:59:59' AS DOUBLE)
  645. 235959 235959
  646. SELECT CAST(TIME'23:59:59.123456' AS FLOAT),
  647. CAST(TIME'23:59:59.123456' AS DOUBLE);
  648. CAST(TIME'23:59:59.123456' AS FLOAT) CAST(TIME'23:59:59.123456' AS DOUBLE)
  649. 235959 235959.123456
  650. SELECT CAST(TIMESTAMP'2000-01-01 23:59:59' AS FLOAT),
  651. CAST(TIMESTAMP'2000-01-01 23:59:59' AS DOUBLE);
  652. CAST(TIMESTAMP'2000-01-01 23:59:59' AS FLOAT) CAST(TIMESTAMP'2000-01-01 23:59:59' AS DOUBLE)
  653. 20000100000000 20000101235959
  654. SELECT CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS FLOAT),
  655. CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS DOUBLE);
  656. CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS FLOAT) CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS DOUBLE)
  657. 20000100000000 20000101235959.125
  658. CREATE TABLE t1 as SELECT CAST(1/3 AS FLOAT) as float_col,
  659. CAST(1/3 AS DOUBLE) as double_col,
  660. CAST(CAST(999.00009 AS DECIMAL(7,4)) AS DOUBLE) as d2;
  661. SHOW CREATE TABLE t1;
  662. Table Create Table
  663. t1 CREATE TABLE `t1` (
  664. `float_col` float DEFAULT NULL,
  665. `double_col` double DEFAULT NULL,
  666. `d2` double NOT NULL DEFAULT '0'
  667. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  668. DROP TABLE t1;
  669. SELECT PERIOD_ADD(200905, CAST(3.14e19 AS DOUBLE));
  670. ERROR 22003: BIGINT value is out of range in 'cast(3.14e19 as double)'
  671. SELECT -1.0 * CAST(3.14e19 AS DOUBLE);
  672. -1.0 * CAST(3.14e19 AS DOUBLE)
  673. -3.14e19
  674. SELECT CAST("3.14e100" AS FLOAT);
  675. ERROR 22003: DOUBLE value is out of range in 'cast('3.14e100' as float)'
  676. SELECT CAST(-1e308 as FLOAT);
  677. ERROR 22003: DOUBLE value is out of range in 'cast(-(1e308) as float)'
  678. SELECT CONCAT("value=", CAST("3.4e5" AS FLOAT));
  679. CONCAT("value=", CAST("3.4e5" AS FLOAT))
  680. value=340000
  681. CREATE VIEW v1 AS SELECT CAST(1/3 AS REAL), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50));
  682. SHOW CREATE VIEW v1;
  683. View Create View character_set_client collation_connection
  684. v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast((1 / 3) as double) AS `CAST(1/3 AS REAL)`,cast((1 / 3) as float) AS `CAST(1/3 AS FLOAT(2))`,cast((1 / 3) as double) AS `CAST(1/3 AS FLOAT(50))` latin1 latin1_swedish_ci
  685. DROP VIEW v1;
  686. SELECT CAST(NULL AS REAL), CAST(NULL AS FLOAT), CAST(NULL AS DOUBLE);
  687. CAST(NULL AS REAL) CAST(NULL AS FLOAT) CAST(NULL AS DOUBLE)
  688. NULL NULL NULL
  689. SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=REAL_AS_FLOAT;
  690. CREATE TABLE t AS SELECT CAST(34 AS REAL);
  691. SHOW CREATE TABLE t;
  692. Table Create Table
  693. t CREATE TABLE `t` (
  694. `CAST(34 AS REAL)` float NOT NULL DEFAULT '0'
  695. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  696. DROP TABLE t;
  697. SET @@SQL_MODE=@OLD_SQL_MODE;
  698. CREATE TABLE t AS SELECT CAST(34 AS REAL);
  699. SHOW CREATE TABLE t;
  700. Table Create Table
  701. t CREATE TABLE `t` (
  702. `CAST(34 AS REAL)` double NOT NULL DEFAULT '0'
  703. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  704. DROP TABLE t;
  705. SELECT MAKETIME(1, 2, CAST("1.6" AS FLOAT));
  706. MAKETIME(1, 2, CAST("1.6" AS FLOAT))
  707. 01:02:01.600000
  708. #
  709. # WL#12108: Inject type cast nodes into the item tree to avoid data
  710. # type mismatches.
  711. #
  712. CREATE TABLE dt_t (dt DATETIME, d DATE, t TIME);
  713. CREATE TABLE n_t (i INT, d DECIMAL, f FLOAT, dc DECIMAL);
  714. #
  715. # DATETIME + NUMERICS
  716. #
  717. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.i;
  718. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  719. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  720. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  721. Warnings:
  722. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`dt` as double) = cast(`test`.`n_t`.`i` as double))
  723. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.d;
  724. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  725. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  726. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  727. Warnings:
  728. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`dt` as double) = cast(`test`.`n_t`.`d` as double))
  729. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.f;
  730. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  731. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  732. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  733. Warnings:
  734. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`dt` as double) = `test`.`n_t`.`f`)
  735. #
  736. # DATE + NUMERICS
  737. #
  738. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.i;
  739. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  740. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  741. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  742. Warnings:
  743. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = cast(`test`.`n_t`.`i` as double))
  744. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.d;
  745. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  746. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  747. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  748. Warnings:
  749. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = cast(`test`.`n_t`.`d` as double))
  750. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.f;
  751. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  752. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  753. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  754. Warnings:
  755. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = `test`.`n_t`.`f`)
  756. EXPLAIN SELECT * from dt_t JOIN n_t on dt_t.d = n_t.dc;
  757. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  758. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  759. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  760. Warnings:
  761. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`d` as double) = cast(`test`.`n_t`.`dc` as double))
  762. #
  763. # TIME + NUMERICS
  764. #
  765. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.i;
  766. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  767. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  768. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  769. Warnings:
  770. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`t` as double) = cast(`test`.`n_t`.`i` as double))
  771. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.d;
  772. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  773. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  774. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  775. Warnings:
  776. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`t` as double) = cast(`test`.`n_t`.`d` as double))
  777. EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.f;
  778. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  779. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  780. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  781. Warnings:
  782. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t`,`test`.`n_t`.`i` AS `i`,`test`.`n_t`.`d` AS `d`,`test`.`n_t`.`f` AS `f`,`test`.`n_t`.`dc` AS `dc` from `test`.`dt_t` join `test`.`n_t` where (cast(`test`.`dt_t`.`t` as double) = `test`.`n_t`.`f`)
  783. #
  784. # DATETIME + DATE
  785. #
  786. EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.dt = dt2.d;
  787. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  788. 1 SIMPLE dt1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  789. 1 SIMPLE dt2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  790. Warnings:
  791. Note 1003 /* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (`test`.`dt1`.`dt` = cast(`test`.`dt2`.`d` as datetime))
  792. #
  793. # DATETIME + TIME
  794. #
  795. EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.dt = dt2.t;
  796. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  797. 1 SIMPLE dt1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  798. 1 SIMPLE dt2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  799. Warnings:
  800. Note 1003 /* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (`test`.`dt1`.`dt` = cast(`test`.`dt2`.`t` as datetime))
  801. #
  802. # DATE + TIME
  803. #
  804. EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.d = dt2.t;
  805. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  806. 1 SIMPLE dt1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  807. 1 SIMPLE dt2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  808. Warnings:
  809. Note 1003 /* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (cast(`test`.`dt1`.`d` as datetime) = cast(`test`.`dt2`.`t` as datetime))
  810. EXPLAIN SELECT * FROM dt_t dt1 JOIN dt_t dt2 ON dt1.d = dt2.d;
  811. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  812. 1 SIMPLE dt1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  813. 1 SIMPLE dt2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
  814. Warnings:
  815. Note 1003 /* select#1 */ select `test`.`dt1`.`dt` AS `dt`,`test`.`dt1`.`d` AS `d`,`test`.`dt1`.`t` AS `t`,`test`.`dt2`.`dt` AS `dt`,`test`.`dt2`.`d` AS `d`,`test`.`dt2`.`t` AS `t` from `test`.`dt_t` `dt1` join `test`.`dt_t` `dt2` where (`test`.`dt2`.`d` = `test`.`dt1`.`d`)
  816. EXPLAIN SELECT dt_t.dt = n_t.i from dt_t, n_t;
  817. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  818. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  819. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using join buffer (hash join)
  820. Warnings:
  821. Note 1003 /* select#1 */ select (cast(`test`.`dt_t`.`dt` as double) = cast(`test`.`n_t`.`i` as double)) AS `dt_t.dt = n_t.i` from `test`.`dt_t` join `test`.`n_t`
  822. EXPLAIN SELECT MAX(dt_t.d) AS max_d, MAX(n_t.i) AS max_i
  823. FROM dt_t, n_t HAVING max_d = max_i;
  824. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  825. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  826. 1 SIMPLE n_t NULL ALL NULL NULL NULL NULL 1 100.00 Using join buffer (hash join)
  827. Warnings:
  828. Note 1003 /* select#1 */ select max(`test`.`dt_t`.`d`) AS `max_d`,max(`test`.`n_t`.`i`) AS `max_i` from `test`.`dt_t` join `test`.`n_t` having (cast(`max_d` as double) = cast(`max_i` as double))
  829. EXPLAIN SELECT dt=d from dt_t ORDER BY dt = d;
  830. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  831. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 Using filesort
  832. Warnings:
  833. Note 1003 /* select#1 */ select (`test`.`dt_t`.`dt` = cast(`test`.`dt_t`.`d` as datetime)) AS `dt=d` from `test`.`dt_t` order by (`test`.`dt_t`.`dt` = cast(`test`.`dt_t`.`d` as datetime))
  834. EXPLAIN SELECT * from dt_t ORDER BY dt = d;
  835. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  836. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 Using filesort
  837. Warnings:
  838. Note 1003 /* select#1 */ select `test`.`dt_t`.`dt` AS `dt`,`test`.`dt_t`.`d` AS `d`,`test`.`dt_t`.`t` AS `t` from `test`.`dt_t` order by (`test`.`dt_t`.`dt` = cast(`test`.`dt_t`.`d` as datetime))
  839. EXPLAIN SELECT d=t, LEAD(d,1) OVER w FROM dt_t WINDOW w AS (ORDER BY d=t);
  840. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  841. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 Using filesort
  842. Warnings:
  843. Note 3598 To get information about window functions use EXPLAIN FORMAT=JSON
  844. Note 1003 /* select#1 */ select (cast(`test`.`dt_t`.`d` as datetime) = cast(`test`.`dt_t`.`t` as datetime)) AS `d=t`,lead(`test`.`dt_t`.`d`,1) OVER `w` AS `LEAD(d,1) OVER w` from `test`.`dt_t` window `w` AS (ORDER BY (cast(`test`.`dt_t`.`d` as datetime) = cast(`test`.`dt_t`.`t` as datetime)) )
  845. EXPLAIN SELECT LEAD(d,1) OVER w FROM dt_t WINDOW w AS (ORDER BY d=t);
  846. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  847. 1 SIMPLE dt_t NULL ALL NULL NULL NULL NULL 1 100.00 Using filesort
  848. Warnings:
  849. Note 3598 To get information about window functions use EXPLAIN FORMAT=JSON
  850. Note 1003 /* select#1 */ select lead(`test`.`dt_t`.`d`,1) OVER `w` AS `LEAD(d,1) OVER w` from `test`.`dt_t` window `w` AS (ORDER BY (cast(`test`.`dt_t`.`d` as datetime) = cast(`test`.`dt_t`.`t` as datetime)) )
  851. DROP TABLE dt_t, n_t;
  852. CREATE TABLE t1 (spID int, userID int, date date);
  853. INSERT INTO t1 VALUES (1,1,'1998-01-01');
  854. INSERT INTO t1 VALUES (2,2,'2001-02-03');
  855. INSERT INTO t1 VALUES (3,1,'1988-12-20');
  856. INSERT INTO t1 VALUES (4,2,'1972-12-12');
  857. EXPLAIN SELECT MIN(t1.userID) = MIN(date) FROM t1 GROUP BY userid;
  858. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  859. 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using temporary
  860. Warnings:
  861. Note 1003 /* select#1 */ select (cast(min(`test`.`t1`.`userID`) as double) = cast(min(`test`.`t1`.`date`) as double)) AS `MIN(t1.userID) = MIN(date)` from `test`.`t1` group by `test`.`t1`.`userID`
  862. EXPLAIN SELECT FIRST_VALUE(date) OVER (ORDER BY spID = date) FROM t1;
  863. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  864. 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using filesort
  865. Warnings:
  866. Note 3598 To get information about window functions use EXPLAIN FORMAT=JSON
  867. Note 1003 /* select#1 */ select first_value(`test`.`t1`.`date`) OVER (ORDER BY (cast(`test`.`t1`.`spID` as double) = cast(`test`.`t1`.`date` as double)) ) AS `FIRST_VALUE(date) OVER (ORDER BY spID = date)` from `test`.`t1`
  868. EXPLAIN SELECT date, spid = FIRST_VALUE(date) OVER (ORDER BY date ) FROM t1;
  869. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  870. 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using filesort
  871. Warnings:
  872. Note 3598 To get information about window functions use EXPLAIN FORMAT=JSON
  873. Note 1003 /* select#1 */ select `test`.`t1`.`date` AS `date`,(cast(`test`.`t1`.`spID` as double) = cast(first_value(`test`.`t1`.`date`) OVER (ORDER BY `test`.`t1`.`date` ) as double)) AS `spid = FIRST_VALUE(date) OVER (ORDER BY date )` from `test`.`t1`
  874. #
  875. # No casts when constants or constant functions are involved
  876. #
  877. EXPLAIN SELECT * from t1 WHERE userID = DATE'2012-02-20';
  878. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  879. 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
  880. Warnings:
  881. Note 1003 /* select#1 */ select `test`.`t1`.`spID` AS `spID`,`test`.`t1`.`userID` AS `userID`,`test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`userID` = DATE'2012-02-20')
  882. EXPLAIN SELECT * FROM t1 WHERE date = NULL;
  883. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  884. 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
  885. Warnings:
  886. Note 1003 /* select#1 */ select `test`.`t1`.`spID` AS `spID`,`test`.`t1`.`userID` AS `userID`,`test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = NULL)
  887. EXPLAIN SELECT * FROM t1 WHERE date = CAST('20:21:22' AS TIME);
  888. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  889. 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
  890. Warnings:
  891. Note 1003 /* select#1 */ select `test`.`t1`.`spID` AS `spID`,`test`.`t1`.`userID` AS `userID`,`test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = <cache>(cast('20:21:22' as time)))
  892. DROP TABLE t1;
  893. #
  894. # WL#13456: Inject CAST nodes for comparisons with STRING non-const expressions.
  895. #
  896. CREATE TABLE t(c CHAR(64), v VARCHAR(256), txt TEXT, b BINARY(64), vb VARBINARY(32),
  897. e ENUM ("v1", "v2"), set1 SET('101', '102'), bl BLOB, i INT,
  898. si SMALLINT, ti TINYINT, mi MEDIUMINT, bi BIGINT, bt BIT,
  899. d DECIMAL, f FLOAT, dbl DOUBLE, dt DATETIME, dd DATE, t TIME,
  900. y YEAR);
  901. INSERT INTO t
  902. VALUES ("char", "vchar","text", "binary", "varbinary", "v1", '101,102', "blob",
  903. 2001, 2, 3, 4, 200000002, 0x01, 2001.0, 2001.0, 2001.2,
  904. "2001-01-02 22:00", "2001-01-02", "20:01", 2010);
  905. # String vs INT
  906. EXPLAIN SELECT v = i FROM t;
  907. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  908. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  909. Warnings:
  910. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`i` as double)) AS `v = i` from `test`.`t`
  911. # String vs SMALLINT
  912. EXPLAIN SELECT v = si FROM t;
  913. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  914. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  915. Warnings:
  916. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`si` as double)) AS `v = si` from `test`.`t`
  917. # String vs TINYINT
  918. EXPLAIN SELECT v = ti FROM t;
  919. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  920. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  921. Warnings:
  922. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`ti` as double)) AS `v = ti` from `test`.`t`
  923. # String vs MEDIUMINT
  924. EXPLAIN SELECT v = mi FROM t;
  925. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  926. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  927. Warnings:
  928. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`mi` as double)) AS `v = mi` from `test`.`t`
  929. # String vs BIGINT
  930. EXPLAIN SELECT v = bi FROM t;
  931. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  932. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  933. Warnings:
  934. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`bi` as double)) AS `v = bi` from `test`.`t`
  935. # String vs BIT
  936. EXPLAIN SELECT v = bt FROM t;
  937. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  938. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  939. Warnings:
  940. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`bt` as double)) AS `v = bt` from `test`.`t`
  941. # String vs YEAR
  942. EXPLAIN SELECT v = y FROM t;
  943. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  944. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  945. Warnings:
  946. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`y` as double)) AS `v = y` from `test`.`t`
  947. # String vs DECIMAL
  948. EXPLAIN SELECT v = d FROM t;
  949. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  950. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  951. Warnings:
  952. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`d` as double)) AS `v = d` from `test`.`t`
  953. # String vs FLOAT
  954. EXPLAIN SELECT v = f FROM t;
  955. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  956. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  957. Warnings:
  958. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = `test`.`t`.`f`) AS `v = f` from `test`.`t`
  959. # String vs DOUBLE
  960. EXPLAIN SELECT v = dbl FROM t;
  961. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  962. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  963. Warnings:
  964. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = `test`.`t`.`dbl`) AS `v = dbl` from `test`.`t`
  965. # String vs DATETIME
  966. EXPLAIN SELECT v = dt FROM t;
  967. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  968. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  969. Warnings:
  970. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as datetime(6)) = `test`.`t`.`dt`) AS `v = dt` from `test`.`t`
  971. # String vs DATE
  972. EXPLAIN SELECT v = dd FROM t;
  973. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  974. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  975. Warnings:
  976. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as date) = `test`.`t`.`dd`) AS `v = dd` from `test`.`t`
  977. # String vs TIME
  978. EXPLAIN SELECT v = t FROM t;
  979. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  980. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  981. Warnings:
  982. Note 1003 /* select#1 */ select (`test`.`t`.`v` = `test`.`t`.`t`) AS `v = t` from `test`.`t`
  983. # String vs another equality
  984. EXPLAIN SELECT v = (v = y) FROM t;
  985. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  986. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  987. Warnings:
  988. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast((cast(`test`.`t`.`v` as double) = cast(`test`.`t`.`y` as double)) as double)) AS `v = (v = y)` from `test`.`t`
  989. # String vs expression
  990. EXPLAIN SELECT v = 1 * i FROM t;
  991. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  992. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  993. Warnings:
  994. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) = cast((1 * `test`.`t`.`i`) as double)) AS `v = 1 * i` from `test`.`t`
  995. # Other operators
  996. EXPLAIN SELECT v <=> i FROM t;
  997. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  998. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  999. Warnings:
  1000. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) <=> cast(`test`.`t`.`i` as double)) AS `v <=> i` from `test`.`t`
  1001. EXPLAIN SELECT v <> i FROM t;
  1002. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1003. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1004. Warnings:
  1005. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) <> cast(`test`.`t`.`i` as double)) AS `v <> i` from `test`.`t`
  1006. EXPLAIN SELECT v > i FROM t;
  1007. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1008. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1009. Warnings:
  1010. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) > cast(`test`.`t`.`i` as double)) AS `v > i` from `test`.`t`
  1011. EXPLAIN SELECT v >= i FROM t;
  1012. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1013. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1014. Warnings:
  1015. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) >= cast(`test`.`t`.`i` as double)) AS `v >= i` from `test`.`t`
  1016. EXPLAIN SELECT v <= i FROM t;
  1017. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1018. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1019. Warnings:
  1020. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) <= cast(`test`.`t`.`i` as double)) AS `v <= i` from `test`.`t`
  1021. EXPLAIN SELECT v < i FROM t;
  1022. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1023. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1024. Warnings:
  1025. Note 1003 /* select#1 */ select (cast(`test`.`t`.`v` as double) < cast(`test`.`t`.`i` as double)) AS `v < i` from `test`.`t`
  1026. # Test data type combinations
  1027. EXPLAIN SELECT c = i FROM t;
  1028. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1029. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1030. Warnings:
  1031. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`i` as double)) AS `c = i` from `test`.`t`
  1032. EXPLAIN SELECT c = si FROM t;
  1033. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1034. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1035. Warnings:
  1036. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`si` as double)) AS `c = si` from `test`.`t`
  1037. EXPLAIN SELECT c = ti FROM t;
  1038. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1039. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1040. Warnings:
  1041. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`ti` as double)) AS `c = ti` from `test`.`t`
  1042. EXPLAIN SELECT c = mi FROM t;
  1043. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1044. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1045. Warnings:
  1046. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`mi` as double)) AS `c = mi` from `test`.`t`
  1047. EXPLAIN SELECT c = bi FROM t;
  1048. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1049. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1050. Warnings:
  1051. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`bi` as double)) AS `c = bi` from `test`.`t`
  1052. EXPLAIN SELECT c = bt FROM t;
  1053. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1054. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1055. Warnings:
  1056. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`bt` as double)) AS `c = bt` from `test`.`t`
  1057. EXPLAIN SELECT c = y FROM t;
  1058. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1059. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1060. Warnings:
  1061. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`y` as double)) AS `c = y` from `test`.`t`
  1062. EXPLAIN SELECT c = d FROM t;
  1063. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1064. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1065. Warnings:
  1066. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = cast(`test`.`t`.`d` as double)) AS `c = d` from `test`.`t`
  1067. EXPLAIN SELECT c = f FROM t;
  1068. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1069. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1070. Warnings:
  1071. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = `test`.`t`.`f`) AS `c = f` from `test`.`t`
  1072. EXPLAIN SELECT c = dbl FROM t;
  1073. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1074. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1075. Warnings:
  1076. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as double) = `test`.`t`.`dbl`) AS `c = dbl` from `test`.`t`
  1077. EXPLAIN SELECT c = dt FROM t;
  1078. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1079. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1080. Warnings:
  1081. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as datetime(6)) = `test`.`t`.`dt`) AS `c = dt` from `test`.`t`
  1082. EXPLAIN SELECT c = dd FROM t;
  1083. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1084. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1085. Warnings:
  1086. Note 1003 /* select#1 */ select (cast(`test`.`t`.`c` as date) = `test`.`t`.`dd`) AS `c = dd` from `test`.`t`
  1087. EXPLAIN SELECT c = t FROM t;
  1088. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1089. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1090. Warnings:
  1091. Note 1003 /* select#1 */ select (`test`.`t`.`c` = `test`.`t`.`t`) AS `c = t` from `test`.`t`
  1092. EXPLAIN SELECT txt = i FROM t;
  1093. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1094. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1095. Warnings:
  1096. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`i` as double)) AS `txt = i` from `test`.`t`
  1097. EXPLAIN SELECT txt = si FROM t;
  1098. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1099. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1100. Warnings:
  1101. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`si` as double)) AS `txt = si` from `test`.`t`
  1102. EXPLAIN SELECT txt = ti FROM t;
  1103. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1104. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1105. Warnings:
  1106. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`ti` as double)) AS `txt = ti` from `test`.`t`
  1107. EXPLAIN SELECT txt = mi FROM t;
  1108. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1109. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1110. Warnings:
  1111. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`mi` as double)) AS `txt = mi` from `test`.`t`
  1112. EXPLAIN SELECT txt = bi FROM t;
  1113. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1114. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1115. Warnings:
  1116. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`bi` as double)) AS `txt = bi` from `test`.`t`
  1117. EXPLAIN SELECT txt = bt FROM t;
  1118. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1119. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1120. Warnings:
  1121. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`bt` as double)) AS `txt = bt` from `test`.`t`
  1122. EXPLAIN SELECT txt = y FROM t;
  1123. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1124. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1125. Warnings:
  1126. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`y` as double)) AS `txt = y` from `test`.`t`
  1127. EXPLAIN SELECT txt = d FROM t;
  1128. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1129. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1130. Warnings:
  1131. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = cast(`test`.`t`.`d` as double)) AS `txt = d` from `test`.`t`
  1132. EXPLAIN SELECT txt = f FROM t;
  1133. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1134. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1135. Warnings:
  1136. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = `test`.`t`.`f`) AS `txt = f` from `test`.`t`
  1137. EXPLAIN SELECT txt = dbl FROM t;
  1138. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1139. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1140. Warnings:
  1141. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as double) = `test`.`t`.`dbl`) AS `txt = dbl` from `test`.`t`
  1142. EXPLAIN SELECT txt = dt FROM t;
  1143. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1144. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1145. Warnings:
  1146. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as datetime(6)) = `test`.`t`.`dt`) AS `txt = dt` from `test`.`t`
  1147. EXPLAIN SELECT txt = dd FROM t;
  1148. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1149. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1150. Warnings:
  1151. Note 1003 /* select#1 */ select (cast(`test`.`t`.`txt` as date) = `test`.`t`.`dd`) AS `txt = dd` from `test`.`t`
  1152. EXPLAIN SELECT txt = t FROM t;
  1153. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1154. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1155. Warnings:
  1156. Note 1003 /* select#1 */ select (`test`.`t`.`txt` = `test`.`t`.`t`) AS `txt = t` from `test`.`t`
  1157. EXPLAIN SELECT b = i FROM t;
  1158. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1159. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1160. Warnings:
  1161. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`i` as double)) AS `b = i` from `test`.`t`
  1162. EXPLAIN SELECT b = si FROM t;
  1163. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1164. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1165. Warnings:
  1166. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`si` as double)) AS `b = si` from `test`.`t`
  1167. EXPLAIN SELECT b = ti FROM t;
  1168. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1169. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1170. Warnings:
  1171. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`ti` as double)) AS `b = ti` from `test`.`t`
  1172. EXPLAIN SELECT b = mi FROM t;
  1173. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1174. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1175. Warnings:
  1176. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`mi` as double)) AS `b = mi` from `test`.`t`
  1177. EXPLAIN SELECT b = bi FROM t;
  1178. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1179. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1180. Warnings:
  1181. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`bi` as double)) AS `b = bi` from `test`.`t`
  1182. EXPLAIN SELECT b = bt FROM t;
  1183. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1184. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1185. Warnings:
  1186. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`bt` as double)) AS `b = bt` from `test`.`t`
  1187. EXPLAIN SELECT b = y FROM t;
  1188. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1189. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1190. Warnings:
  1191. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`y` as double)) AS `b = y` from `test`.`t`
  1192. EXPLAIN SELECT b = d FROM t;
  1193. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1194. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1195. Warnings:
  1196. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = cast(`test`.`t`.`d` as double)) AS `b = d` from `test`.`t`
  1197. EXPLAIN SELECT b = f FROM t;
  1198. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1199. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1200. Warnings:
  1201. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = `test`.`t`.`f`) AS `b = f` from `test`.`t`
  1202. EXPLAIN SELECT b = dbl FROM t;
  1203. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1204. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1205. Warnings:
  1206. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as double) = `test`.`t`.`dbl`) AS `b = dbl` from `test`.`t`
  1207. EXPLAIN SELECT b = dt FROM t;
  1208. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1209. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1210. Warnings:
  1211. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as datetime(6)) = `test`.`t`.`dt`) AS `b = dt` from `test`.`t`
  1212. EXPLAIN SELECT b = dd FROM t;
  1213. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1214. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1215. Warnings:
  1216. Note 1003 /* select#1 */ select (cast(`test`.`t`.`b` as date) = `test`.`t`.`dd`) AS `b = dd` from `test`.`t`
  1217. EXPLAIN SELECT b = t FROM t;
  1218. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1219. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1220. Warnings:
  1221. Note 1003 /* select#1 */ select (`test`.`t`.`b` = `test`.`t`.`t`) AS `b = t` from `test`.`t`
  1222. EXPLAIN SELECT vb = i FROM t;
  1223. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1224. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1225. Warnings:
  1226. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`i` as double)) AS `vb = i` from `test`.`t`
  1227. EXPLAIN SELECT vb = si FROM t;
  1228. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1229. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1230. Warnings:
  1231. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`si` as double)) AS `vb = si` from `test`.`t`
  1232. EXPLAIN SELECT vb = ti FROM t;
  1233. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1234. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1235. Warnings:
  1236. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`ti` as double)) AS `vb = ti` from `test`.`t`
  1237. EXPLAIN SELECT vb = mi FROM t;
  1238. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1239. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1240. Warnings:
  1241. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`mi` as double)) AS `vb = mi` from `test`.`t`
  1242. EXPLAIN SELECT vb = bi FROM t;
  1243. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1244. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1245. Warnings:
  1246. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`bi` as double)) AS `vb = bi` from `test`.`t`
  1247. EXPLAIN SELECT vb = bt FROM t;
  1248. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1249. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1250. Warnings:
  1251. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`bt` as double)) AS `vb = bt` from `test`.`t`
  1252. EXPLAIN SELECT vb = y FROM t;
  1253. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1254. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1255. Warnings:
  1256. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`y` as double)) AS `vb = y` from `test`.`t`
  1257. EXPLAIN SELECT vb = d FROM t;
  1258. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1259. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1260. Warnings:
  1261. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = cast(`test`.`t`.`d` as double)) AS `vb = d` from `test`.`t`
  1262. EXPLAIN SELECT vb = f FROM t;
  1263. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1264. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1265. Warnings:
  1266. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = `test`.`t`.`f`) AS `vb = f` from `test`.`t`
  1267. EXPLAIN SELECT vb = dbl FROM t;
  1268. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1269. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1270. Warnings:
  1271. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as double) = `test`.`t`.`dbl`) AS `vb = dbl` from `test`.`t`
  1272. EXPLAIN SELECT vb = dt FROM t;
  1273. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1274. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1275. Warnings:
  1276. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as datetime(6)) = `test`.`t`.`dt`) AS `vb = dt` from `test`.`t`
  1277. EXPLAIN SELECT vb = dd FROM t;
  1278. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1279. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1280. Warnings:
  1281. Note 1003 /* select#1 */ select (cast(`test`.`t`.`vb` as date) = `test`.`t`.`dd`) AS `vb = dd` from `test`.`t`
  1282. EXPLAIN SELECT vb = t FROM t;
  1283. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1284. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1285. Warnings:
  1286. Note 1003 /* select#1 */ select (`test`.`t`.`vb` = `test`.`t`.`t`) AS `vb = t` from `test`.`t`
  1287. EXPLAIN SELECT e = i FROM t;
  1288. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1289. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1290. Warnings:
  1291. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`i` as double)) AS `e = i` from `test`.`t`
  1292. EXPLAIN SELECT e = si FROM t;
  1293. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1294. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1295. Warnings:
  1296. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`si` as double)) AS `e = si` from `test`.`t`
  1297. EXPLAIN SELECT e = ti FROM t;
  1298. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1299. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1300. Warnings:
  1301. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`ti` as double)) AS `e = ti` from `test`.`t`
  1302. EXPLAIN SELECT e = mi FROM t;
  1303. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1304. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1305. Warnings:
  1306. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`mi` as double)) AS `e = mi` from `test`.`t`
  1307. EXPLAIN SELECT e = bi FROM t;
  1308. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1309. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1310. Warnings:
  1311. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`bi` as double)) AS `e = bi` from `test`.`t`
  1312. EXPLAIN SELECT e = bt FROM t;
  1313. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1314. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1315. Warnings:
  1316. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`bt` as double)) AS `e = bt` from `test`.`t`
  1317. EXPLAIN SELECT e = y FROM t;
  1318. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1319. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1320. Warnings:
  1321. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`y` as double)) AS `e = y` from `test`.`t`
  1322. EXPLAIN SELECT e = d FROM t;
  1323. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1324. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1325. Warnings:
  1326. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = cast(`test`.`t`.`d` as double)) AS `e = d` from `test`.`t`
  1327. EXPLAIN SELECT e = f FROM t;
  1328. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1329. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1330. Warnings:
  1331. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = `test`.`t`.`f`) AS `e = f` from `test`.`t`
  1332. EXPLAIN SELECT e = dbl FROM t;
  1333. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1334. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1335. Warnings:
  1336. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as double) = `test`.`t`.`dbl`) AS `e = dbl` from `test`.`t`
  1337. EXPLAIN SELECT e = dt FROM t;
  1338. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1339. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1340. Warnings:
  1341. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as datetime(6)) = `test`.`t`.`dt`) AS `e = dt` from `test`.`t`
  1342. EXPLAIN SELECT e = dd FROM t;
  1343. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1344. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1345. Warnings:
  1346. Note 1003 /* select#1 */ select (cast(`test`.`t`.`e` as date) = `test`.`t`.`dd`) AS `e = dd` from `test`.`t`
  1347. EXPLAIN SELECT e = t FROM t;
  1348. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1349. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1350. Warnings:
  1351. Note 1003 /* select#1 */ select (`test`.`t`.`e` = `test`.`t`.`t`) AS `e = t` from `test`.`t`
  1352. EXPLAIN SELECT set1 = i FROM t;
  1353. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1354. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1355. Warnings:
  1356. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`i` as double)) AS `set1 = i` from `test`.`t`
  1357. EXPLAIN SELECT set1 = si FROM t;
  1358. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1359. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1360. Warnings:
  1361. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`si` as double)) AS `set1 = si` from `test`.`t`
  1362. EXPLAIN SELECT set1 = ti FROM t;
  1363. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1364. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1365. Warnings:
  1366. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`ti` as double)) AS `set1 = ti` from `test`.`t`
  1367. EXPLAIN SELECT set1 = mi FROM t;
  1368. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1369. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1370. Warnings:
  1371. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`mi` as double)) AS `set1 = mi` from `test`.`t`
  1372. EXPLAIN SELECT set1 = bi FROM t;
  1373. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1374. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1375. Warnings:
  1376. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`bi` as double)) AS `set1 = bi` from `test`.`t`
  1377. EXPLAIN SELECT set1 = bt FROM t;
  1378. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1379. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1380. Warnings:
  1381. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`bt` as double)) AS `set1 = bt` from `test`.`t`
  1382. EXPLAIN SELECT set1 = y FROM t;
  1383. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1384. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1385. Warnings:
  1386. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`y` as double)) AS `set1 = y` from `test`.`t`
  1387. EXPLAIN SELECT set1 = d FROM t;
  1388. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1389. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1390. Warnings:
  1391. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = cast(`test`.`t`.`d` as double)) AS `set1 = d` from `test`.`t`
  1392. EXPLAIN SELECT set1 = f FROM t;
  1393. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1394. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1395. Warnings:
  1396. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = `test`.`t`.`f`) AS `set1 = f` from `test`.`t`
  1397. EXPLAIN SELECT set1 = dbl FROM t;
  1398. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1399. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1400. Warnings:
  1401. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as double) = `test`.`t`.`dbl`) AS `set1 = dbl` from `test`.`t`
  1402. EXPLAIN SELECT set1 = dt FROM t;
  1403. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1404. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1405. Warnings:
  1406. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as datetime(6)) = `test`.`t`.`dt`) AS `set1 = dt` from `test`.`t`
  1407. EXPLAIN SELECT set1 = dd FROM t;
  1408. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1409. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1410. Warnings:
  1411. Note 1003 /* select#1 */ select (cast(`test`.`t`.`set1` as date) = `test`.`t`.`dd`) AS `set1 = dd` from `test`.`t`
  1412. EXPLAIN SELECT set1 = t FROM t;
  1413. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1414. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1415. Warnings:
  1416. Note 1003 /* select#1 */ select (`test`.`t`.`set1` = `test`.`t`.`t`) AS `set1 = t` from `test`.`t`
  1417. EXPLAIN SELECT bl = i FROM t;
  1418. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1419. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1420. Warnings:
  1421. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`i` as double)) AS `bl = i` from `test`.`t`
  1422. EXPLAIN SELECT bl = si FROM t;
  1423. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1424. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1425. Warnings:
  1426. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`si` as double)) AS `bl = si` from `test`.`t`
  1427. EXPLAIN SELECT bl = ti FROM t;
  1428. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1429. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1430. Warnings:
  1431. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`ti` as double)) AS `bl = ti` from `test`.`t`
  1432. EXPLAIN SELECT bl = mi FROM t;
  1433. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1434. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1435. Warnings:
  1436. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`mi` as double)) AS `bl = mi` from `test`.`t`
  1437. EXPLAIN SELECT bl = bi FROM t;
  1438. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1439. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1440. Warnings:
  1441. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`bi` as double)) AS `bl = bi` from `test`.`t`
  1442. EXPLAIN SELECT bl = bt FROM t;
  1443. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1444. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1445. Warnings:
  1446. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`bt` as double)) AS `bl = bt` from `test`.`t`
  1447. EXPLAIN SELECT bl = y FROM t;
  1448. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1449. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1450. Warnings:
  1451. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`y` as double)) AS `bl = y` from `test`.`t`
  1452. EXPLAIN SELECT bl = d FROM t;
  1453. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1454. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1455. Warnings:
  1456. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = cast(`test`.`t`.`d` as double)) AS `bl = d` from `test`.`t`
  1457. EXPLAIN SELECT bl = f FROM t;
  1458. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1459. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1460. Warnings:
  1461. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = `test`.`t`.`f`) AS `bl = f` from `test`.`t`
  1462. EXPLAIN SELECT bl = dbl FROM t;
  1463. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1464. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1465. Warnings:
  1466. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as double) = `test`.`t`.`dbl`) AS `bl = dbl` from `test`.`t`
  1467. EXPLAIN SELECT bl = dt FROM t;
  1468. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1469. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1470. Warnings:
  1471. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as datetime(6)) = `test`.`t`.`dt`) AS `bl = dt` from `test`.`t`
  1472. EXPLAIN SELECT bl = dd FROM t;
  1473. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1474. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1475. Warnings:
  1476. Note 1003 /* select#1 */ select (cast(`test`.`t`.`bl` as date) = `test`.`t`.`dd`) AS `bl = dd` from `test`.`t`
  1477. EXPLAIN SELECT bl = t FROM t;
  1478. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1479. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1480. Warnings:
  1481. Note 1003 /* select#1 */ select (`test`.`t`.`bl` = `test`.`t`.`t`) AS `bl = t` from `test`.`t`
  1482. # YEAR vs data types
  1483. EXPLAIN SELECT y = i FROM t;
  1484. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1485. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1486. Warnings:
  1487. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`i`) AS `y = i` from `test`.`t`
  1488. EXPLAIN SELECT y = si FROM t;
  1489. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1490. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1491. Warnings:
  1492. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`si`) AS `y = si` from `test`.`t`
  1493. EXPLAIN SELECT y = ti FROM t;
  1494. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1495. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1496. Warnings:
  1497. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`ti`) AS `y = ti` from `test`.`t`
  1498. EXPLAIN SELECT y = mi FROM t;
  1499. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1500. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1501. Warnings:
  1502. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`mi`) AS `y = mi` from `test`.`t`
  1503. EXPLAIN SELECT y = bi FROM t;
  1504. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1505. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1506. Warnings:
  1507. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`bi`) AS `y = bi` from `test`.`t`
  1508. EXPLAIN SELECT y = bt FROM t;
  1509. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1510. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1511. Warnings:
  1512. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`bt`) AS `y = bt` from `test`.`t`
  1513. EXPLAIN SELECT y = y FROM t;
  1514. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1515. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1516. Warnings:
  1517. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`y`) AS `y = y` from `test`.`t`
  1518. EXPLAIN SELECT y = d FROM t;
  1519. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1520. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1521. Warnings:
  1522. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`d`) AS `y = d` from `test`.`t`
  1523. EXPLAIN SELECT y = f FROM t;
  1524. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1525. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1526. Warnings:
  1527. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`f`) AS `y = f` from `test`.`t`
  1528. EXPLAIN SELECT y = dbl FROM t;
  1529. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1530. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1531. Warnings:
  1532. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`dbl`) AS `y = dbl` from `test`.`t`
  1533. EXPLAIN SELECT y = dt FROM t;
  1534. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1535. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1536. Warnings:
  1537. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`dt`) AS `y = dt` from `test`.`t`
  1538. EXPLAIN SELECT y = dd FROM t;
  1539. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1540. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1541. Warnings:
  1542. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`dd`) AS `y = dd` from `test`.`t`
  1543. EXPLAIN SELECT y = t FROM t;
  1544. id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  1545. 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
  1546. Warnings:
  1547. Note 1003 /* select#1 */ select (`test`.`t`.`y` = `test`.`t`.`t`) AS `y = t` from `test`.`t`
  1548. # cleanup
  1549. DROP TABLE t;
  1550. #
  1551. # Bug#31023252: RESULTSET MISMATCH USING STRCMP() WITH DATE AND STRING DATATYPE
  1552. #
  1553. CREATE TABLE t1(a YEAR, b VARCHAR(10));
  1554. INSERT INTO t1 VALUES ('1997','random_str');
  1555. SELECT STRCMP(a, b) FROM t1;
  1556. STRCMP(a, b)
  1557. -1
  1558. DROP TABLE t1;
  1559. #
  1560. # Bug#30626100: WL13456 RESULTSET DISTINCT DIFFERENCE
  1561. #
  1562. CREATE TABLE t (col_datetime datetime, col_date date, col_time time, col_char char);
  1563. insert into t values ('2013-03-15 18:35:20', '2013-03-15', '18:35:20','L'),
  1564. ('2003-01-10 00:00:23', '2003-01-10', '00:00:23', NULL);
  1565. SELECT CAST(col_char AS DATETIME) FROM t;
  1566. CAST(col_char AS DATETIME)
  1567. NULL
  1568. NULL
  1569. Warnings:
  1570. Warning 1292 Incorrect datetime value: 'L'
  1571. SELECT col_char /*CAST(col_char as datetime)*/ <> col_datetime FROM t;
  1572. col_char /*CAST(col_char as datetime)*/ <> col_datetime
  1573. 1
  1574. NULL
  1575. Warnings:
  1576. Warning 1292 Incorrect datetime value: 'L'
  1577. SELECT CAST(col_char AS DATE) FROM t;
  1578. CAST(col_char AS DATE)
  1579. NULL
  1580. NULL
  1581. Warnings:
  1582. Warning 1292 Incorrect datetime value: 'L'
  1583. SELECT col_char <> col_date FROM t;
  1584. col_char <> col_date
  1585. 1
  1586. NULL
  1587. Warnings:
  1588. Warning 1292 Incorrect datetime value: 'L'
  1589. SELECT CAST(col_char as TIME) FROM t;
  1590. CAST(col_char as TIME)
  1591. NULL
  1592. NULL
  1593. Warnings:
  1594. Warning 1292 Truncated incorrect time value: 'L'
  1595. SELECT col_char <> col_time FROM t;
  1596. col_char <> col_time
  1597. 1
  1598. NULL
  1599. DROP TABLE t;
  1600. #
  1601. # Bug#31095719 WL13456 RESULT SET COMPARISON DIFFERENCE WITH JOINS
  1602. #
  1603. CREATE TABLE `BB` (`col_char_key` char(1));
  1604. CREATE TABLE `CC` ( `pk` int, `col_datetime_key` datetime,
  1605. KEY `idx_CC_col_datetime_key` (`col_datetime_key`));
  1606. INSERT INTO `BB` VALUES ('X');
  1607. INSERT INTO `CC` VALUES (1,'2027-03-17 00:10:00'), (2,'2004-11-14 12:46:43');
  1608. SELECT COUNT(table1.pk) FROM CC table1 JOIN BB table3 JOIN CC table2
  1609. WHERE (table3.col_char_key < table2.col_datetime_key);
  1610. COUNT(table1.pk)
  1611. 4
  1612. Warnings:
  1613. Warning 1292 Incorrect datetime value: 'X'
  1614. Warning 1292 Incorrect datetime value: 'X'
  1615. Warning 1292 Incorrect datetime value: 'X'
  1616. Warning 1292 Incorrect datetime value: 'X'
  1617. DROP TABLE `BB`;
  1618. DROP TABLE `CC`;
  1619. #
  1620. # CAST AS YEAR
  1621. #
  1622. SELECT CAST(CAST(-1 AS SIGNED) AS YEAR);
  1623. CAST(CAST(-1 AS SIGNED) AS YEAR)
  1624. NULL
  1625. Warnings:
  1626. Warning 1292 Truncated incorrect YEAR value: '-1'
  1627. SELECT CAST(CAST(-99 AS SIGNED) AS YEAR);
  1628. CAST(CAST(-99 AS SIGNED) AS YEAR)
  1629. NULL
  1630. Warnings:
  1631. Warning 1292 Truncated incorrect YEAR value: '-99'
  1632. SELECT CAST(CAST(0 AS SIGNED) AS YEAR);
  1633. CAST(CAST(0 AS SIGNED) AS YEAR)
  1634. 0
  1635. SELECT CAST(CAST(69 AS SIGNED) AS YEAR);
  1636. CAST(CAST(69 AS SIGNED) AS YEAR)
  1637. 2069
  1638. SELECT CAST(CAST(70 AS SIGNED) AS YEAR);
  1639. CAST(CAST(70 AS SIGNED) AS YEAR)
  1640. 1970
  1641. SELECT CAST(CAST(99 AS SIGNED) AS YEAR);
  1642. CAST(CAST(99 AS SIGNED) AS YEAR)
  1643. 1999
  1644. SELECT CAST(CAST(100 AS SIGNED) AS YEAR);
  1645. CAST(CAST(100 AS SIGNED) AS YEAR)
  1646. NULL
  1647. Warnings:
  1648. Warning 1292 Truncated incorrect YEAR value: '100'
  1649. SELECT CAST(CAST(2010 AS SIGNED) AS YEAR);
  1650. CAST(CAST(2010 AS SIGNED) AS YEAR)
  1651. 2010
  1652. SELECT CAST(-1.1 AS YEAR);
  1653. CAST(-1.1 AS YEAR)
  1654. NULL
  1655. Warnings:
  1656. Warning 1292 Truncated incorrect YEAR value: '-1'
  1657. SELECT CAST(1.1 AS YEAR);
  1658. CAST(1.1 AS YEAR)
  1659. 2001
  1660. SELECT CAST(0.0 AS YEAR);
  1661. CAST(0.0 AS YEAR)
  1662. 0
  1663. SELECT CAST(69.1 AS YEAR);
  1664. CAST(69.1 AS YEAR)
  1665. 2069
  1666. SELECT CAST(70.1 AS YEAR);
  1667. CAST(70.1 AS YEAR)
  1668. 1970
  1669. SELECT CAST(100.1 AS YEAR);
  1670. CAST(100.1 AS YEAR)
  1671. NULL
  1672. Warnings:
  1673. Warning 1292 Truncated incorrect YEAR value: '100'
  1674. SELECT CAST(2010.9 AS YEAR);
  1675. CAST(2010.9 AS YEAR)
  1676. 2011
  1677. SELECT CAST(CAST(-1.1 AS DECIMAL) AS YEAR);
  1678. CAST(CAST(-1.1 AS DECIMAL) AS YEAR)
  1679. NULL
  1680. Warnings:
  1681. Warning 1292 Truncated incorrect YEAR value: '-1'
  1682. SELECT CAST(CAST(1.1 AS DECIMAL) AS YEAR);
  1683. CAST(CAST(1.1 AS DECIMAL) AS YEAR)
  1684. 2001
  1685. SELECT CAST(CAST(0.0 AS DECIMAL) AS YEAR);
  1686. CAST(CAST(0.0 AS DECIMAL) AS YEAR)
  1687. 0
  1688. SELECT CAST(CAST(69.1 AS DECIMAL) AS YEAR);
  1689. CAST(CAST(69.1 AS DECIMAL) AS YEAR)
  1690. 2069
  1691. SELECT CAST(CAST(70.1 AS DECIMAL) AS YEAR);
  1692. CAST(CAST(70.1 AS DECIMAL) AS YEAR)
  1693. 1970
  1694. SELECT CAST(CAST(100.1 AS DECIMAL) AS YEAR);
  1695. CAST(CAST(100.1 AS DECIMAL) AS YEAR)
  1696. NULL
  1697. Warnings:
  1698. Warning 1292 Truncated incorrect YEAR value: '100'
  1699. SELECT CAST(CAST(2010.9 AS DECIMAL) AS YEAR);
  1700. CAST(CAST(2010.9 AS DECIMAL) AS YEAR)
  1701. 2011
  1702. SELECT CAST("-1" AS YEAR);
  1703. CAST("-1" AS YEAR)
  1704. NULL
  1705. Warnings:
  1706. Warning 1525 Incorrect YEAR value: '-1'
  1707. SELECT CAST("-99" AS YEAR);
  1708. CAST("-99" AS YEAR)
  1709. NULL
  1710. Warnings:
  1711. Warning 1525 Incorrect YEAR value: '-99'
  1712. SELECT CAST("0" AS YEAR);
  1713. CAST("0" AS YEAR)
  1714. 2000
  1715. SELECT CAST("69" AS YEAR);
  1716. CAST("69" AS YEAR)
  1717. 2069
  1718. SELECT CAST("70" AS YEAR);
  1719. CAST("70" AS YEAR)
  1720. 1970
  1721. SELECT CAST("99" AS YEAR);
  1722. CAST("99" AS YEAR)
  1723. 1999
  1724. SELECT CAST("100" AS YEAR);
  1725. CAST("100" AS YEAR)
  1726. NULL
  1727. Warnings:
  1728. Warning 1292 Truncated incorrect YEAR value: '100'
  1729. SELECT CAST("2010" AS YEAR);
  1730. CAST("2010" AS YEAR)
  1731. 2010
  1732. SELECT CAST("extra" AS YEAR);
  1733. CAST("extra" AS YEAR)
  1734. NULL
  1735. Warnings:
  1736. Warning 1525 Incorrect YEAR value: 'extra'
  1737. SELECT CAST("22extra" AS YEAR);
  1738. CAST("22extra" AS YEAR)
  1739. 2022
  1740. Warnings:
  1741. Warning 1292 Truncated incorrect YEAR value: '22extra'
  1742. SELECT CAST("2020extra" AS YEAR);
  1743. CAST("2020extra" AS YEAR)
  1744. 2020
  1745. Warnings:
  1746. Warning 1292 Truncated incorrect YEAR value: '2020extra'
  1747. SELECT CAST(TIMESTAMP'2010-01-01 00:00' AS YEAR);
  1748. CAST(TIMESTAMP'2010-01-01 00:00' AS YEAR)
  1749. 2010
  1750. SET SQL_MODE = "";
  1751. SELECT CAST(TIMESTAMP'0000-00-00 00:00' AS YEAR);
  1752. CAST(TIMESTAMP'0000-00-00 00:00' AS YEAR)
  1753. 0
  1754. SET SQL_MODE = default;
  1755. SELECT CAST(TIMESTAMP'2010-01-01 08:09:10' AS YEAR);
  1756. CAST(TIMESTAMP'2010-01-01 08:09:10' AS YEAR)
  1757. 2010
  1758. SELECT CAST(TIME'08:09:10' AS YEAR);
  1759. CAST(TIME'08:09:10' AS YEAR)
  1760. 2021
  1761. SELECT CAST(TIME'00:00:00' AS YEAR);
  1762. CAST(TIME'00:00:00' AS YEAR)
  1763. 2021
  1764. SELECT CAST(ST_PointFromText('POINT(10 10)') AS YEAR);
  1765. ERROR HY000: Incorrect arguments to cast_as_year
  1766. CREATE TABLE t AS SELECT CAST("2010" AS YEAR);
  1767. SHOW CREATE TABLE t;
  1768. Table Create Table
  1769. t CREATE TABLE `t` (
  1770. `CAST("2010" AS YEAR)` year DEFAULT NULL
  1771. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1772. DROP TABLE t;
  1773. SELECT CAST(JSON_EXTRACT('{"key_year":1934}', '$.key_year') AS YEAR);
  1774. CAST(JSON_EXTRACT('{"key_year":1934}', '$.key_year') AS YEAR)
  1775. 1934
  1776. SELECT CAST(CAST('{"_id":"192312412512"}' AS JSON) AS YEAR);
  1777. CAST(CAST('{"_id":"192312412512"}' AS JSON) AS YEAR)
  1778. 0
  1779. Warnings:
  1780. Warning 3156 Invalid JSON value for CAST to INTEGER from column cast_as_json at row 1
  1781. CREATE TABLE t1 (i INT, j JSON) CHARSET utf8mb4;
  1782. INSERT INTO t1 VALUES (0, NULL);
  1783. INSERT INTO t1 VALUES (1, '"1901"');
  1784. INSERT INTO t1 VALUES (2, 'true');
  1785. INSERT INTO t1 VALUES (3, 'false');
  1786. INSERT INTO t1 VALUES (4, 'null');
  1787. INSERT INTO t1 VALUES (5, '-1');
  1788. INSERT INTO t1 VALUES (6, CAST(CAST(1 AS UNSIGNED) AS JSON));
  1789. INSERT INTO t1 VALUES (7, '1901');
  1790. INSERT INTO t1 VALUES (8, '-1901');
  1791. INSERT INTO t1 VALUES (9, '2147483647');
  1792. INSERT INTO t1 VALUES (10, '2147483648');
  1793. INSERT INTO t1 VALUES (11, '-2147483648');
  1794. INSERT INTO t1 VALUES (12, '-2147483649');
  1795. INSERT INTO t1 VALUES (13, '3.14');
  1796. INSERT INTO t1 VALUES (14, '{}');
  1797. INSERT INTO t1 VALUES (15, '[]');
  1798. INSERT INTO t1 VALUES (16, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON));
  1799. INSERT INTO t1 VALUES (17, CAST(CAST('23:24:25' AS TIME) AS JSON));
  1800. INSERT INTO t1 VALUES (18, CAST(CAST('2015-01-15' AS DATE) AS JSON));
  1801. INSERT INTO t1 VALUES (19, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON));
  1802. INSERT INTO t1 VALUES (20, CAST(ST_GeomFromText('POINT(1 1)') AS JSON));
  1803. INSERT INTO t1 VALUES (21, CAST('1988' AS CHAR CHARACTER SET 'ascii'));
  1804. INSERT INTO t1 VALUES (22, CAST(x'07C4' AS JSON));
  1805. INSERT INTO t1 VALUES (23, CAST(x'07C407C4' AS JSON));
  1806. SELECT i, CAST(j AS YEAR), CAST(j AS SIGNED) FROM t1 ORDER BY i;
  1807. i CAST(j AS YEAR) CAST(j AS SIGNED)
  1808. 0 NULL NULL
  1809. 1 1901 1901
  1810. 2 2001 1
  1811. 3 0 0
  1812. 4 0 0
  1813. 5 NULL -1
  1814. 6 2001 1
  1815. 7 1901 1901
  1816. 8 NULL -1901
  1817. 9 NULL 2147483647
  1818. 10 NULL 2147483648
  1819. 11 NULL -2147483648
  1820. 12 NULL -2147483649
  1821. 13 2003 3
  1822. 14 0 0
  1823. 15 0 0
  1824. 16 0 0
  1825. 17 0 0
  1826. 18 0 0
  1827. 19 0 0
  1828. 20 0 0
  1829. 21 1988 1988
  1830. 22 0 0
  1831. 23 0 0
  1832. Warnings:
  1833. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 5
  1834. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 5
  1835. Warning 1292 Truncated incorrect YEAR value: '-1'
  1836. Warning 1292 Truncated incorrect YEAR value: '-1901'
  1837. Warning 1292 Truncated incorrect YEAR value: '2147483647'
  1838. Warning 1292 Truncated incorrect YEAR value: '2147483648'
  1839. Warning 1292 Truncated incorrect YEAR value: '-2147483648'
  1840. Warning 1292 Truncated incorrect YEAR value: '-2147483649'
  1841. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 15
  1842. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 15
  1843. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 16
  1844. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 16
  1845. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 17
  1846. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 17
  1847. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 18
  1848. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 18
  1849. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 19
  1850. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 19
  1851. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 20
  1852. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 20
  1853. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 21
  1854. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 21
  1855. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 23
  1856. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 23
  1857. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 24
  1858. Warning 3156 Invalid JSON value for CAST to INTEGER from column j at row 24
  1859. DROP TABLE t1;
  1860. CREATE TABLE t(numbers ENUM('0','1','2020'), colors ENUM('red', 'green', 'blue'));
  1861. INSERT INTO t values('2020', 'blue');
  1862. SELECT CAST(numbers AS YEAR), CAST(colors AS YEAR) FROM t;
  1863. CAST(numbers AS YEAR) CAST(colors AS YEAR)
  1864. 2003 2003
  1865. DROP TABLE t;
  1866. CREATE TABLE t(y YEAR);
  1867. INSERT INTO t values(CAST("2020extra" AS YEAR));
  1868. ERROR 22007: Truncated incorrect YEAR value: '2020extra'
  1869. INSERT INTO t values(CAST(20201 AS YEAR));
  1870. ERROR 22007: Truncated incorrect YEAR value: '20201'
  1871. SET SQL_MODE = "";
  1872. INSERT INTO t values(CAST("2020extra" AS YEAR));
  1873. Warnings:
  1874. Warning 1292 Truncated incorrect YEAR value: '2020extra'
  1875. INSERT INTO t values(CAST(20201 AS YEAR));
  1876. Warnings:
  1877. Warning 1292 Truncated incorrect YEAR value: '20201'
  1878. SELECT * FROM t;
  1879. y
  1880. 2020
  1881. NULL
  1882. SET SQL_MODE = default;
  1883. DROP TABLE t;
  1884. SELECT CAST(1988 AS YEAR), REPEAT(CAST(1988 AS YEAR), 3) AS c2;
  1885. CAST(1988 AS YEAR) c2
  1886. 1988 198819881988
  1887. SELECT CONCAT('x', CAST(1988 AS YEAR));
  1888. CONCAT('x', CAST(1988 AS YEAR))
  1889. x1988
  1890. SELECT CAST(1988 AS YEAR) + 1.5e0;
  1891. CAST(1988 AS YEAR) + 1.5e0
  1892. 1989.5
  1893. SELECT CAST(CAST(1988 AS YEAR) AS DECIMAL);
  1894. CAST(CAST(1988 AS YEAR) AS DECIMAL)
  1895. 1988
  1896. SELECT DATE_ADD(CAST(1988 AS YEAR), INTERVAL 1 DAY);
  1897. DATE_ADD(CAST(1988 AS YEAR), INTERVAL 1 DAY)
  1898. NULL
  1899. Warnings:
  1900. Warning 1292 Incorrect datetime value: '1988'
  1901. SELECT TIME_TO_SEC(CAST('2030' AS YEAR));
  1902. TIME_TO_SEC(CAST('2030' AS YEAR))
  1903. 1230
  1904. SELECT TIMESTAMPADD(MINUTE, 1, CAST(1988 AS YEAR));
  1905. TIMESTAMPADD(MINUTE, 1, CAST(1988 AS YEAR))
  1906. NULL
  1907. Warnings:
  1908. Warning 1292 Incorrect datetime value: '1988'
  1909. SELECT CAST(CAST(1988 AS YEAR) AS SIGNED);
  1910. CAST(CAST(1988 AS YEAR) AS SIGNED)
  1911. 1988
  1912. SELECT CAST(CAST(1988 AS YEAR) AS UNSIGNED);
  1913. CAST(CAST(1988 AS YEAR) AS UNSIGNED)
  1914. 1988
  1915. SELECT CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR);
  1916. CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR)
  1917. NULL
  1918. Warnings:
  1919. Warning 1292 Truncated incorrect YEAR value: '579'
  1920. SELECT CAST(STR_TO_DATE('nope','%d-%m-%Y') AS YEAR);
  1921. CAST(STR_TO_DATE('nope','%d-%m-%Y') AS YEAR)
  1922. NULL
  1923. Warnings:
  1924. Warning 1411 Incorrect datetime value: 'nope' for function str_to_date
  1925.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!