GBase 8a查询报错ERROR 1733 (HY000) at line 214: (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS LONG_BLOB
👉 本文共约203个字，系统预计阅读时间或需1分钟。
现象
1 2 3 4 5 | ERROR 1733 (HY000) at line 214: (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS LONG_BLOB ERROR 1733 (HY000) at line 430: (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS MEDIUM_BLOB ERROR 1733 (HY000) at line 519: (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS LONG_BLOB ERROR 1733 (HY000) at line 535: (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS LONG_BLOB ERROR 1733 (HY000) at line 736: (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS LONG_BLOB |
分析
找到脚本得第214行，发现是表SELECT * FROM INFORMATION_SCHEMA.PLUGINS,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | gbase> desc INFORMATION_SCHEMA.PLUGINS; +------------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-------------+------+-----+---------+-------+ | PLUGIN_NAME | varchar(64) | NO | | | | | PLUGIN_VERSION | varchar(20) | NO | | | | | PLUGIN_STATUS | varchar(10) | NO | | | | | PLUGIN_TYPE | varchar(80) | NO | | | | | PLUGIN_TYPE_VERSION | varchar(20) | NO | | | | | PLUGIN_LIBRARY | varchar(64) | YES | | NULL | | | PLUGIN_LIBRARY_VERSION | varchar(20) | YES | | NULL | | | PLUGIN_AUTHOR | varchar(64) | YES | | NULL | | | PLUGIN_DESCRIPTION | longtext | YES | | NULL | | | PLUGIN_LICENSE | varchar(80) | YES | | NULL | | +------------------------+-------------+------+-----+---------+-------+ 10 rows in set (Elapsed: 00:00:00.00) gbase> select * from INFORMATION_SCHEMA.PLUGINS; +-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------------------+--------------------------------------------------------------------------+----------------+ | PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | +-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------------------+--------------------------------------------------------------------------+----------------+ | binlog | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | NULL | NULL | GBase AB | This is a pseudo storage engine to represent the binlog in a transaction | | | partition | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | NULL | NULL | Mikael Ronstrom, GBase AB | Partition Storage Engine Helper | | | CSV | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | NULL | NULL | Brian Aker, GBase AB | CSV storage engine | | | EXPRESS | 0.1 | ACTIVE | STORAGE ENGINE | 80602.0 | NULL | NULL | Express | Express storage engine | | | GsSYS | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | NULL | NULL | GBase AB | GsSYS engine | | | MRG_GSSYS | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | NULL | NULL | GBase AB | Collection of identical GsSYS tables | | | MEMORY | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | NULL | NULL | GBase AB | Hash based, stored in memory, useful for temporary tables | | +-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------------------+--------------------------------------------------------------------------+----------------+ 7 rows in set (Elapsed: 00:00:00.00) |
可以将列PLUGIN_DESCRIPTION修改为PLUGIN_DESCRIPTION+0，就可以将longtext转换成varchar了。
解决
原脚本：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 | SELECT '<table border=1><tr><th>PLUGIN_NAME</th><th>PLUGIN_VERSION</th><th>PLUGIN_STATUS</th><th>PLUGIN_TYPE</th><th>PLUGIN_TYPE_VERSION</th><th>PLUGIN_LIBRARY</th><th>PLUGIN_LIBRARY_VERSION</th><th>PLUGIN_AUTHOR</th><th>PLUGIN_DESCRIPTION</th><th>PLUGIN_LICENSE</th></tr>' UNION ALL SELECT concat('<tr><td>',PLUGIN_NAME,'</td><td>',PLUGIN_VERSION,'</td><td>',PLUGIN_STATUS,'</td><td>',PLUGIN_TYPE,'</td><td>',PLUGIN_TYPE_VERSION,'</td><td>',ifnull(PLUGIN_LIBRARY,''),'</td><td>',ifnull(PLUGIN_LIBRARY_VERSION,''),'</td><td>',PLUGIN_AUTHOR,'</td><td>',PLUGIN_DESCRIPTION+0,'</td><td>',PLUGIN_LICENSE,'</td></tr>') from (SELECT * FROM INFORMATION_SCHEMA.PLUGINS) V UNION ALL SELECT '</table>' ; |
修改后脚本：
1 2 3 4 5 6 7 8 9 | SELECT '<table border=1><tr><th>PLUGIN_NAME</th><th>PLUGIN_VERSION</th><th>PLUGIN_STATUS</th><th>PLUGIN_TYPE</th><th>PLUGIN_TYPE_VERSION</th><th>PLUGIN_LIBRARY</th><th>PLUGIN_LIBRARY_VERSION</th><th>PLUGIN_AUTHOR</th><th>PLUGIN_DESCRIPTION</th><th>PLUGIN_LICENSE</th></tr>' UNION ALL SELECT concat('<tr><td>',PLUGIN_NAME,'</td><td>',PLUGIN_VERSION,'</td><td>',PLUGIN_STATUS,'</td><td>',PLUGIN_TYPE,'</td><td>',PLUGIN_TYPE_VERSION,'</td><td>',ifnull(PLUGIN_LIBRARY,''),'</td><td>',ifnull(PLUGIN_LIBRARY_VERSION,''),'</td><td>',PLUGIN_AUTHOR,'</td><td>',PLUGIN_DESCRIPTION+0,'</td><td>',PLUGIN_LICENSE,'</td></tr>') from (SELECT * FROM INFORMATION_SCHEMA.PLUGINS) V UNION ALL SELECT '</table>' ; |
